共计 2965 个字符，预计需要花费 8 分钟才能阅读完成。
什么是成本
- I/O成本
当查询表中的记录时， 需要先把数据或者索引加载到内存中然后再操作。 这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
数据从磁盘读取到内存，读一页的成本约定为1.0
- CPU成本
读取以及检测记录是否满足对应的搜索条件、 对结果集进行排序等这些操作损耗的时间称之为CPU成本。
验证是否符合搜索条件，排序分组等，一条数据的成本约定为0.2
单表查询的成本
基于成本的优化步骤
根据搜索条件， 找出所有可能使用的索引
对于B+树索引来说， 只要索引列和常数使用=、 <=>、 IN、 NOT IN、 IS NULL、 IS NOT NULL、 >、 <、 >=、 <=、 BETWEEN、 !=（ 或者LIKE操作符连接起来， 就可以产生一个所谓的范围区间（LIKE匹配字符串前缀也行） ， 也就是说这些搜索条件都可能使用到索引， MySQL把一个查询中可能使用到的索引称之为possible keys。
计算全表扫描的代价
全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较， 把符合搜索条件的记录加入到结果集， 所以需要将聚簇索引对应的页面加载到内存中， 然后再检测记录是否符合搜索条件。 查询成本=I/O成本+CPU成本， 所以计算全表扫描的代价需要两个信息：
- 聚簇索引占用的页面数
- 该表中的记录数
执行show table status like '表名'
MySQL教程
重点关注Rows和Data_length
- Rows
表示表中的记录条数 ，该值是一个估计值
- Data_length
表示表占用的存储空间字节数，该值就相当于聚簇索引占用的存储空间大小Data_length = 聚簇索引的页面数量 x 每个页面的大小 （16k）
计算使用不同索引执行查询的代价
一个查询可能使用到多个索引，MySQL为使用不同的索引计算成本。如果是二级索引，带区间的按照下面的规则计算
- 区间成本
I/O 成本：二级索引一个范围区间算一个数据页成本为1.0CPU 成本：二级索引内存里面估算数据量，每行数据cpu成本为0.2
- 回表成本
回表的I/O成本：认为一条数据为一个数据页即成本为1.0
CPU成本为：记录数*0.2
- 总成本
范围区间 * 1.0 + 估算记录数 * (1+0.2+0.2)
- 公式
范围区间数量的I/O成本 + 二级索引记录的CPU成本 + 据这些记录里的主键值到聚簇索引中做回表操作的I/O成本 + 回表操作后得到的完整用户记录再检测其他搜索条件是否成立的CPU成本
对比各种执行方案的代价
对计算出来的不同成本，找出一个成本最低的一个
基于索引统计数据的成本计算
有时候使用索引执行查询时会有许多单点区间， 比如使用IN语句就很容易产生非常多的单点区间，例如使用IN关键字，SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');如果key1不是唯一二级索引，就不能确定有多少条记录。所以MySQL会先获取索引对应的B+树的区间最左记录和区间最右记录， 然后再计算这两条记录之间有多少记录（计算方式使用到了B+数索引特性） 。MySQL把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。
index dive
MySQL教程
区间最左记录在页b中， 区间最右记录在页c中， 那么区间记录数量就相当于计算页b和页c之间有多少页面， 而每一条目录项记录都对应一个数据页， 所以计算页b和页c之间有多少页面就相当于计算它们父节点（也就是页a） 中对应的目录项记录之间隔着几条记录。 如果页b和页c之间的页面实在太多， 以至于页b和页c对应的目录项记录都不在一个页面中那么久继续递归，继续统计页b和页c对应的目录项记录所在页之间有多少个页面。
索引统计
如果区间数量过于庞大，这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数， 要进行而很多次index dive操作， 这性能损耗可就大了 ，所以MySQL提供了一个系统变量eq_range_index_dive_limit，也就是说如果我们的IN语句中的参数个数小于200个的话， 将使用index dive的方式计算各个单点区间对应的记录条数， 如果大于或等于200个的话， 可就不能使用index dive了， 要使用索引统计数据来进行估算
MySQL教程
MySQL也会为表中的每一个索引维护一份统计数据， 查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法
MySQL教程
其中Cardinality表示索引列中不重复值的数量，他只是一个估计值，不是准确值
- 使用SHOW TABLE STATUS展示出的Rows值， 也就是一个表中有多少条记录。
- 使用SHOW INDEX语句展示出的Cardinality属性。
结合上一个Rows统计数据， 我们可以针对索引列， 计算出平均一个值重复多少次。
**一个值的重复次数 ≈ Rows ÷ Cardinality **
使用统计数据来估算这些参数范围区间对应的记录条数为 =** 区间数 * 一个值的重复次数**
成本 = 范围区间数 * 1.0 + 范围区间记录条数 * 1.0 + 范围区间记录数 * 0.2 + 范围区间完整记录数 * 0.2
连接查询的成本
Condition filtering
MySQL中连接查询采用的是嵌套循环连接算法， 驱动表会被访问一次， 被驱动表可能会被访问多次， 所以对于两表连接查询来说， 它的查询成本由下边两个部分构成：
- 单次查询驱动表的成本
- 多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）
把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名： fanout） 。 驱动表的扇出值越小， 对被驱动表的查询次数也就越少， 连接查询的总成本也就越低
- 如果使用的是全表扫描的方式执行的单表查询， 那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
- 如果使用的是索引执行的单表扫描， 那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
MySQL的把这个猜的过程称之为condition filtering。 这个过程可能会使用到索引， 也可能使用到统计数据， 也可能就是MySQL单纯的瞎猜
连接的成本分析
连接查询的成本计算公式
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
连接查询优化原则
- 尽量减少驱动表的扇出
- 对被驱动表的访问成本尽量低
实际书写连接查询语句时们需要尽量在被驱动表的连接列上建立索引， 这样就可以使用ref访问方法来降低访问被驱动表的成本了。 如果可以， 被驱动表
的连接列最好是该表的主键或者唯一二级索引列， 这样就可以把访问被驱动表的成本降到更低了。
