乐居财经讯11月26日，“乐见新生——2020乐居创新峰会”在广州举行。活动现场，“2020（第四届）中国地产经理人评选”大湾区30强结果揭晓。富力集团副总经理兼广州公司董事长肖明荣获“2020中傻女不愁嫁,倍乐威,韩影阁,

MySQL 8.0运维实例｜直方图的使用傻女不愁嫁,倍乐威,韩影阁,

当需要过滤的字段上既没有索引也没有直方图时，优化器会根据MySQL代码中内置的默认规则估计过滤的比率，实际很大程度上是瞎猜，部分常用的默认规则如下：

■ 表18.1 过滤类型的默认过滤比率

下面是SQL语句的执行计划中默认过滤比率的几个例子，首先设置pager，使执行计划只显示过滤比率：

mysql> pager grepfiltered

PAGER setto'grep filtered'

等于检索字段的默认过滤比率为10%：

mysql> explain select* fromactor wherefirst_name= 'lisa'/G

filtered: 10.00

1row inset, 1warning ( 0.00sec)

大于或小于检索字段的默认过滤比率为三分之一：

mysql> explain select* fromactor wherefirst_name> 'lisa'/G

filtered: 33.33

1row inset, 1warning ( 0.00sec)

不等于检索字段的默认过滤比率为90%：

mysql> explain select* fromactor wherefirst_name<> 'lisa'/G

filtered: 90.00

1row inset, 1warning ( 0.00sec)

between的默认过滤比率为11.11%：

mysql> explain select* fromactor wherelast_update between '2006-02-15'and '2006-02-16'/G

filtered: 11.11

1row inset, 1warning ( 0.00sec)

下面SQL中in的过滤比率为20%：

mysql> explain select* fromactor wherefirst_name in( 'lisa', 'THORA')/G

filtered: 20.00

1 row inset, 1 warning( 0.00sec )

使用默认规则估计过滤比率往往是不准的，因此很多时候会生成错误的执行计划，这个时候在字段上收集直方图统计信息可以解决这个问题。而且直方图即使不用于改变SQL语句的执行计划，也可以用于在执行计划中的filt列显示正确的过滤的比例。

编写一个简单的SQL语句，查询在payment表里面支付的金额大于10元的客户号，生成这个SQL的执行计划如下：

mysql> explain select customer_id from payment where amount>10/G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: payment

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 16086

filtered: 33.33

Extra: Using where

1 row in set, 1 warning (0.00 sec)

判断amount字段大于10的记录，由于这个字段上没有直方图的统计信息，优化器根据代码中内置的默认值估计有三分之一的记录属于这个范围。再判断amount大于100的记录的执行计划如下

mysql> explain select customer_id from payment where amount>100/G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: payment

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 16086

filtered: 33.33

Extra: Using where

1 row in set, 1 warning (0.00 sec)

优化器仍然估计有三分之一的记录属于这个范围，显然优化器在瞎猜。为了解决这个问题，现在在amount字段上创建直方图的统计信息的命令和输出结果如下：

mysql> analyze table payment update histogram on amount with 256 buckets/G

*************************** 1. row ***************************

Table: sakila.payment

Op: histogram

Msg_type: status

Msg_text: Histogram statistics created for column 'amount'.

1 row in set (0.31 sec)

再重新生成这个SQL语句的执行计划如下：

mysql> explain select customer_id from payment where amount>10/G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: payment

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 16086

filtered: 0.71

Extra: Using where

1 row in set, 1 warning (0.00 sec)

优化器根据直方图的统计信息估计符合这个条件的记录只占总数0.71%。

删除这个直方图的命令如下：

mysql> analyze table payment drop histogram on amount/G

*************************** 1. row ***************************

Table: sakila.payment

Op: histogram

Msg_type: status

Msg_text: Histogram statistics removed for column 'amount'.

1 row in set (0.01 sec)

下面的SQL语句查询单词消费金额大于10元和在第一个店进行消费的顾客的姓名，在没有直方图时的生成的执行计划如下：

mysql> explain analyze select first_name,last_name fromcustomer inner join payment using (customer_id) where amount> 10andstore_id= 1/G

*************************** 1.row ***************************

EXPLAIN: ->Nested loopinner join (cost= 3100.48rows= 2918) (actual time= 0.443. .14.853rows= 68loops= 1)

-> Index lookup oncustomer using idx_fk_store_id (store_id= 1) (cost= 36.35rows= 326) (actual time= 0.310. .0.707rows= 326loops= 1)

-> Filter: (payment.amount > 10.00) (cost= 6.72rows= 9) (actual time= 0.042. .0.043rows= 0loops= 326)

-> Index lookup onpayment using idx_fk_customer_id (customer_id=customer.customer_id) (cost= 6.72rows= 27) (actual time= 0.031. .0.038rows= 27loops= 326)

1row inset ( 0.01sec)

可以看到优化器先对符合在第一个店进行消费的条件进行过滤，然后再过滤消费金额大于10元的条件。在字段amount上有直方图统计信息之后，再次生成这个SQL语句的执行计划如下：

mysql> explain analyze select first_name,last_name fromcustomer inner join payment using (customer_id) where amount> 10andstore_id= 1/G

*************************** 1.row ***************************

EXPLAIN: ->Nested loopinner join (cost= 1672.84rows= 62) (actual time= 0.328. .9.507rows= 68loops= 1)

-> Filter: (payment.amount > 10.00) (cost= 1632.85rows= 114) (actual time= 0.224. .8.421rows= 114loops= 1)

-> Table scan onpayment (cost= 1632.85rows= 16086) (actual time= 0.191. .6.482rows= 16049loops= 1)

-> Filter: (customer.store_id = 1) (cost= 0.25rows= 1) (actual time= 0.009. .0.009rows= 1loops= 114)

-> Single-row index lookup oncustomer using PRIMARY (customer_id=payment.customer_id) (cost= 0.25rows= 1) (actual time= 0.009. .0.009rows= 1loops= 114)

1row inset ( 0.02sec)

可以看到优化器将这两个过滤条件的先后次序反转过来了，因为借助直方图统计信息，优化器知道消费金额大于10元这个条件的选择性更高。从估计成本和实际执行时间都可以看出，有直方图的执行计划效率要好很多！

直方图在某些场景下可以帮助优化器生成更优的执行计划，那么在什么样的字段上考虑使用直方图呢，这里建议符合下面4个条件字段可以考虑建立直方图统计信息：

（1）值分布不均匀，优化器很难估计值的分布的字段。

（2）选择性差的字段，否则索引更适合。

（3）用于where子句中过滤的字段或用于连接的字段。

（4）字段值分布规律不随时间变化的字段。因为直方图统计信息不会自动收集，如果字段值分布规律发生大的变化，统计信息会失真。

实际工作中，可以使用explain analyze分析SQL语句的执行计划，如果估算的rows和实际的rows相差过大，可以考虑在过滤字段上创建直方图统计信息。

实例讲解

MySQL 8.0运维与优化

精彩回顾

忘记root用户密码怎么办？

01

参考书籍

扫码优惠购书

《MySQL 8.0运维与优化（微课视频版）》

姚远 著

定价：99.8元

扫码京东优惠购书

内容简介

本书全面介绍MySQL数据库的管理、监控、备份恢复和高可用等方面的知识，并在此基础上讨论如何优化MySQL的实例和SQL语句，书中还包括大量的实战案例。

全书分五部分：第一部分（第1～4章）为管理部分，包括安装和运行、账号和权限、日志和安全等内容；第二部分（第5～7章）为监控部分，介绍通过MySQL自带的3个系统数据进行监控的方法；第三部分（第8～11章）为备份恢复部分，介绍常用的逻辑备份和物理备份工具，还介绍在没有备份的情况下如何进行数据救援；第四部分（第12～14章）为高可用部分，包括MySQL Shell、复制和InnoDB集群；第五部分（第15～18章）为优化部分，介绍基准测试工具和优化MySQL实例和SQL方法。

作者简介

姚远，Oracle ACE，华为云MVP，现任鼎甲科技首席数据库专家，曾任IBM公司数据库部门经理。拥有 Oracle 10g OCM和12c OCM,以及 MySQL 5.6、5.7、8.0 OCP，并在数据库相关领域拥有20多项技术认证，两次获得省部级科学技术进步奖，获两项国家发明专利授权。

名人推荐语

This book seems very detailed with many examples on how to administrate a MySQL server. It covers MySQL 8.0 new features and Scott illustrates how to benefit from them. By reading the book, it’s obvious that the author is an experienced MySQL DBA and he shares his experience from which many junior DBA will certainly benefit. This is a very technical book I would recommend and I hope to see it translated to English too.

——Frédéric Descamps，知名MySQL布道师，MySQL社区经理

非常感谢姚远老师为MySQL生态做出的贡献。本书通过大量的示例，结合作者多年来的实战经验，全方位、多角度、系统地介绍了MySQL 数据库。推荐MySQL DBA及IT相关技术人员仔细阅读，相信读者一定能够从中获益。

——徐轶韬，Oracle公司MySQL解决方案首席工程师 ，公众号“MySQL解决方案工程师”的运营者及内容作者

姚远兄是数据库领域的资深专家、MySQL ACE荣誉获得者和长期耕耘在一线的技术极客。本书不仅面面俱到地介绍运维的基础知识，还分享了优化的实战经验，是全面学习和掌握MySQL 8.0的必备佳作。

——周彦伟，极数云舟创始人，DTark总架构师，中国计算机行业协会数据库专委会会长

我认真拜读了姚远兄的新书章节，有两点感受：书中有不少MySQL新版本的特性，通过“穿针引线”的方式和运维场景有效结合，具有实战意义；书中包含大量案例，这些案例源自作者多年的经验和沉淀，不禁赞叹姚远兄在无数个忙碌的夜晚探索技术的苦与乐。

——杨建荣，Oracle ACE，dbaplus社群发起人,《Oracle DBA工作笔记》和《MySQL DBA工作笔记》作者

本书以MySQL 8.0为基础，不仅全面地介绍MySQL运维的操作方法，还分享了作者从业多年的学习方法和心得，是一本非常适合MySQL爱好者的好书。

——洪斌，Oracle ACE，爱可生技术服务总监

姚远兄对技术细节的钻研精神让人钦佩。本书总结了各种在一线工作中经常遇到的问题，以问题复现、分析、解决的方式，帮助读者快速掌握实战技能。书中介绍了很多MySQL 8.0的新特性，市场上尚未有中文版书籍对这些特性进行详细讲解，这有助于MySQL 8.0在中国的推广。

——白瑞钧，Oracle ACE，新炬数据库高级专家

读者人群

本书适合具有一定IT基础知识的MySQL数据库爱好者阅读，也可以作为准备MySQL OCP考试的备考书。

目录

向上滑动阅览

第一部分管理

第1章安装和运行

1．1MySQL 8.0社区版的安装

1．2Percona Server for MySQL的安装

1．3安装Sakila示例数据库

1．4检查MySQL服务

1．5升级到MySQL 8.0

1．6Linux对MySQL的强制访问控制

1．7启动排错

1．8实验

第2章账号和权限

2．1账号

2．2权限

2．3访问控制

2．4角色

2．5代理用户

2．6无密码登录

2．7重置root用户密码

2．8实验

第3章日志

3．1错误日志

3．2通用查询日志

3．3慢查询日志

3．4二进制日志

3．5实验

第4章安全

4．1密码验证组件

4．2连接控制插件

4．3连接加密

4．4数据加密

4．5审计插件

4．6实验

第二部分监控

第5章information_schema数据库

5．1数据组成

5．2MySQL 8.0中的优化

5．3权限

5．4视图说明

5．5实验

第6章performance_schema数据库

6．1作用和特点

6．2配置

6．3性能计量配置

6．4消费者配置

6．5执行者配置

6．6对象配置

6．7典型用例

6．8实验

第7章sys数据库

7．1简介

7．2配置参数

7．3存储过程

7．4函数

7．5视图

7．6实验

第三部分备份恢复

第8章逻辑备份

8．1逻辑备份和物理备份的区别

8．2mysqldump

8．3mysqlpump

8．4mydumper

8．5MySQL Shell中的备份恢复工具

8．6四种逻辑备份工具的对比

8．7备份集的一致性

8．8提高恢复的速度

8．9实验

第9章XtraBackup

9．1特点介绍

9．2安装

9．3工作原理

9．4典型用例

9．5高级功能

9．6实验

第10章MySQL Enterprise Backup

10．1简介

10．2工作原理

10．3典型用例

10．4高级功能

10．5实验

第11章数据救援

11．1InnoDB强制恢复

11．2迁移MyISAM表

11．3只有表空间文件时批量恢复InnoDB表

11．4使用ibd2sdi恢复表结构

11．5TwinDB数据恢复工具

11．6实验

第四部分高可用

第12章MySQL Shell

12．1简介

12．2通用命令

12．3客户化MySQL Shell

12．4全局对象

12．5报告架构

12．6实验

第13章复制

13．1简介

13．2克隆插件

13．3配置复制

13．4GTID

13．5排错

13．6使用MySQL Shell的AdminAPI管理InnoDB复制

13．7实验

第14章InnoDB集群

14．1架构

14．2组复制

14．3MySQL Router

14．4管理InnoDB集群

14．5实验

第五部分优化

第15章基准测试工具

15．1mysqlslap

15．2Sysbench

15．3TPCCMySQL

15．4实验

第16章实例优化

16．1数据库优化的重要性

16．2系统参数的修改

16．3内存的分配

16．4InnoDB日志

16．5硬盘读写参数

16．6资源组

16．7实验

第17章SQL优化基础

17．1SQL语句的执行计划

17．2优化器

17．3索引

17．4表连接

17．5统计信息

17．6直方图

17．7CTE

17．8实验

第18章SQL优化实战

18．1找出需要优化的SQL

18．2优化方法

18．3优化索引

18．4准确的统计信息

18．5直方图的使用

18．6连接优化

18．7优化排序

18．8表空间碎片整理

18．9实验

参考文献

02

精彩推荐

• 微信小程序游戏开发│猜数字小游戏（附源码+视频）

• Flink编程基础│Scala编程初级实践

• Flink编程基础│FlinkCEP编程实践

• Flink编程基础│DataStream API编程实践

• Flink编程基础│DataSet API编程实践

• 数 据分析实战│客户价值分析

• 数据分析实战│价格预测挑战

• 数据分析实战│时间序列预测

• 数据分析实战│KaggleTitanic生存预测

傻女不愁嫁,倍乐威,韩影阁, 首先，不要指望现有的雅阁混动和ES混动可以上新能源车牌，虽然它们实际效率很高，也很省油跟环保，但却不属于我国新能源规定中的“插电式混动（含增程式）”、纯电动以及燃料电池汽车