当需要过滤的字段上既没有索引也没有直方图时，优化器会根据MySQL代码中内置的默认规则估计过滤的比率，实际很大程度上是瞎猜，部分常用的默认规则如下：
■ 表18.1 过滤类型的默认过滤比率
下面是SQL语句的执行计划中默认过滤比率的几个例子，首先设置pager，使执行计划只显示过滤比率：
mysql> pager grep filtered
PAGER set to 'grep filtered'
等于检索字段的默认过滤比率为10%：
mysql> explain select * from actor where first_name='lisa'\G
filtered: 10.00
1 row in set, 1 warning (0.00 sec)
大于或小于检索字段的默认过滤比率为三分之一：
mysql> explain select * from actor where first_name>'lisa'\G
filtered: 33.33
1 row in set, 1 warning (0.00 sec)
不等于检索字段的默认过滤比率为90%：
mysql> explain select * from actor where first_name<>'lisa'\G
filtered: 90.00
1 row in set, 1 warning (0.00 sec)
between的默认过滤比率为11.11%：
mysql> explain select * from actor where last_update between '2006-02-15' and '2006-02-16'\G
filtered: 11.11
1 row in set, 1 warning (0.00 sec)
下面SQL中in的过滤比率为20%：
mysql> explain select * from actor where first_name in ('lisa','THORA')\G
filtered: 20.00
1 row in set, 1 warning (0.00 sec)
使用默认规则估计过滤比率往往是不准的，因此很多时候会生成错误的执行计划，这个时候在字段上收集直方图统计信息可以解决这个问题。而且直方图即使不用于改变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 from customer inner join payment using (customer_id) where amount>10 and store_id=1\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=3100.48 rows=2918) (actual time=0.443..14.853 rows=68 loops=1)
-> Index lookup on customer using idx_fk_store_id (store_id=1) (cost=36.35 rows=326) (actual time=0.310..0.707 rows=326 loops=1)
-> Filter: (payment.amount > 10.00) (cost=6.72 rows=9) (actual time=0.042..0.043 rows=0 loops=326)
-> Index lookup on payment using idx_fk_customer_id (customer_id=customer.customer_id) (cost=6.72 rows=27) (actual time=0.031..0.038 rows=27 loops=326)
1 row in set (0.01 sec)
可以看到优化器先对符合在第一个店进行消费的条件进行过滤，然后再过滤消费金额大于10元的条件。在字段amount上有直方图统计信息之后，再次生成这个SQL语句的执行计划如下：
mysql> explain analyze select first_name,last_name from customer inner join payment using (customer_id) where amount>10 and store_id=1\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=1672.84 rows=62) (actual time=0.328..9.507 rows=68 loops=1)
-> Filter: (payment.amount > 10.00) (cost=1632.85 rows=114) (actual time=0.224..8.421 rows=114 loops=1)
-> Table scan on payment (cost=1632.85 rows=16086) (actual time=0.191..6.482 rows=16049 loops=1)
-> Filter: (customer.store_id = 1) (cost=0.25 rows=1) (actual time=0.009..0.009 rows=1 loops=114)
-> Single-row index lookup on customer using PRIMARY (customer_id=payment.customer_id) (cost=0.25 rows=1) (actual time=0.009..0.009 rows=1 loops=114)
1 row in set (0.02 sec)
可以看到优化器将这两个过滤条件的先后次序反转过来了，因为借助直方图统计信息，优化器知道消费金额大于10元这个条件的选择性更高。从估计成本和实际执行时间都可以看出，有直方图的执行计划效率要好很多！
直方图在某些场景下可以帮助优化器生成更优的执行计划，那么在什么样的字段上考虑使用直方图呢，这里建议符合下面4个条件字段可以考虑建立直方图统计信息：
（1）值分布不均匀，优化器很难估计值的分布的字段。
（2）选择性差的字段，否则索引更适合。
（3）用于where子句中过滤的字段或用于连接的字段。
（4）字段值分布规律不随时间变化的字段。因为直方图统计信息不会自动收集，如果字段值分布规律发生大的变化，统计信息会失真。
实际工作中，可以使用explain analyze分析SQL语句的执行计划，如果估算的rows和实际的rows相差过大，可以考虑在过滤字段上创建直方图统计信息。
实例讲解
MySQL 8.0运维与优化
精彩回顾
参考书籍
《MySQL 8.0运维与优化（微课视频版）》
ISBN：9787302602682
姚远 著
定价：99.8元
扫码京东优惠购书
本书全面介绍MySQL数据库的管理、监控、备份恢复和高可用等方面的知识，并在此基础上讨论如何优化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考试的备考书。
目录
向上滑动阅览
精彩推荐
微信小程序游戏开发│猜数字小游戏（附源码+视频） Flink编程基础│Scala编程初级实践 Flink编程基础│FlinkCEP编程实践 Flink编程基础│DataStream API编程实践 Flink编程基础│DataSet API编程实践 数据分析实战│客户价值分析 数据分析实战│价格预测挑战 数据分析实战│时间序列预测 数据分析实战│KaggleTitanic生存预测
