回忆查询成本。对于一个查询，有时候可以通过不同的索引或者全表扫描来执行。MySQL优化器会通过事先生成的统计数据，或者对B+树索引的少量访问，进行分析和使用。每个索引需要扫描多少条记录，然后计算使用不同索引的查询成本，最后选择成本最低的执行查询。Tips：我们以前把通过少量访问B+树索引来分析扫描记录条数的方法叫做indexdive。不知道大家还有没有印象。一个很简单的想法是：当使用索引进行查询时，需要扫描的记录越少，就越有可能使用这个索引来进行查询。创建一个场景如果我们现在有一个表t，它的表结构如下：该表包含3列：id列为自增主键key1列，用于存储字符串。我们为key1列建立了一个公共二级索引common_field列来存储字符串。现在表中有10000条记录：mysql>SELECTCOUNT(*)FROMt;+------------+|COUNT(*)|+----------+|10000|+----------+1rowinset(2.65sec)有2310条记录，其中key1列为'a'：mysql>SELECTCOUNT(*)FROMtWHEREkey1='a';+----------+|COUNT(*)|+----------+|2310|+------------+1rowinset(0.83sec)key1在'a'和'i'之间有2310条记录：mysql>SELECTCOUNT(*)FROMtWHEREkey1>'a'ANDkey1<'i';+--------+|COUNT(*)|+--------+|2310|+------------+1rowinset(1.31sec)现在我们有以下两个查询：查询1：SELECT*FROMtWHEREkey1='a';查询2：SELECT*FROMtWHEREkey1>'a'ANDkey1<'i';按理说，以上两个查询要扫描的记录条数是一样的，MySQL查询优化器对它们的态度应该是一样的，即要么使用二级索引idx_key1来执行它们，或者使用全表扫描来执行它们。但实际情况是，查询优化器似乎更喜欢查询1而更讨厌查询2。查询1的执行计划如下：#查询1的执行计划mysql>EXPLAINSELECT*FROMtWHEREkey1='a'\G*******************************1.row******************************id:1select_type:SIMPLEtable:tpartitions:NULLtype:refpossible_keys:idx_key1key:idx_key1key_len:303ref:constrows:2310filtered:100.00Extra:NULL1rowinset,1warning(0.04sec)查询2的执行计划如下：#查询2的执行计划mysql>EXPLAINSELECT*FROMtWHEREkey1>'a'ANDkey1<'i'\G********************************1.行*****************************id:1select_type:SIMPLEtable:tpartitions:NULLtype:ALLpossible_keys:idx_key1key:NULLkey_len:NULLref:NULLrows:9912filtered:23.31Extra:Usingwhere1rowinset,1warning(0.03sec)显然，查询优化器决定使用idx_key1二级索引执行查询1，而查询2使用全表扫描执行。为什么？为什么？我还扫描了相同数量的记录。为什么我的范围访问方法应该低于您的参考？设计MySQL的大叔，你为什么这么偏心……解密偏心的原因世上没有无缘无故的爱，没有无缘无故的恨。这个东西还得从索引结构说起。比如idx_key1二级索引结构是这样的：原谅我们把索引对应的B+树结构做了一个极其简化的版本。我们忽略页面结构，只保留叶节点记录。虽然极度简化，但我们仍然保留了一个极其重要的特性：B+树的叶子节点中的记录是按照索引列的值从小到大排序的。对于二级索引idx_key1：只保留key1列和id列作为二级索引叶子节点的记录。二级索引记录首先按照key1列的值从小到大排序。如果key1列的值相同，则按照主键值排序，即id列的值升序排列。也就是说，对于所有key1值为'a'的二级索引记录，都按照id列的值进行排序。对于查询1：查询1：SELECT*FROMtWHEREkey1='a';由于查询列表是*，也就是说我们需要通过读取二级索引记录的id值进行回表操作，在聚簇索引中找到完整的key。用户记录（为了获取common_field列的值）可以将记录发送给客户端。对于所有key1列值等于'a'的二级索引记录，由于是按照id列的值排序的，因此：返回上表id值所属的聚簇索引记录和id值下一张表返回的id值所属的聚集索引记录很可能在同一个数据页，即使前一张表返回的id值所属的聚集索引记录和下一张表的id值所属的聚集索引记录返回表所属的不在同一个数据页，因为返回表的id值是递增的，所以我们很可能通过顺序I/O找到下一个数据页，也就是说很有可能是下一个在此过程中无需大幅度移动磁头即可找到数据页Page。这样可以减少大量随机I/O带来的性能开销。综上所述，语句1执行时，回表操作带来的性能开销很小。对于查询2：查询2:SELECT*FROMtWHEREkey1>'a'ANDkey1<'i';由于需要扫描的二级索引记录对应的id值是乱序的，在执行返回操作时，id值很可能是需要访问的聚簇索引记录所在的数据页乱序，会造成大量的随机I/O。所以如果使用idx_key1来执行query1和query2，执行query1的成本明显会低于query2，这也是为什么设计MySQL的大叔更喜欢ref而不是range的原因。MySQL内部实现，MySQL优化器在计算回表成本时，在使用二级索引执行查询需要回表的情况下，会明确区分ref和range：forrange,howmuch扫描一条二级索引记录就相当于需要访问多少页。每访问一页，返回表的I/O成本增加1。例如查询2，需要返回表的记录数为2310，I/O成本因为返表操作计算出来的是2310。对于ref，由于回表开销导致的I/O成本是有上限的，即定义了一个上限：doubleworst_seeks；这个上限的值取下面两个值中较小的那个：比如查询1，回表记录数是2310，按理说回表操作带来的I/O开销应该也是2310。但是由于ref访问方式，在计算返表操作带来的I/O成本时有一个上限，会从全表的十分之一开始记录（即9912/10=991，9912是估计值）和聚簇索引占用页数的三倍（本例中聚簇索引占用页数为97，乘以3为291）。选择较小的一个，在本例中为291。全表记录数的十分之一（这里的全表记录数属于统计数据，是一个估计值）3倍聚簇索引占用页提示：成本分析代码中，范围和index，都归为一类，ref是自己的儿子，单独分析。但是，我们也可以看到，设计MySQL的大叔在计算rangeaccess方式的成本时，直接认为每次返回表都需要一次pageI/O，这是很粗鲁的，更何况我们的实际聚集索引总共只有97个。页面，它计算退货成本为2310，这也很不精确。当然，由于目前的算法无法预测哪些页面在内存中，哪些不在内存中，所以就用吧~
