在mysql 调优的方案中，许多小伙伴都知道通过explain 查看mysql 语句的执行计划。通过type 类型等定位问题进行优化。同一个查询语句可以使用多种不同的访问方法来执行，但是时间成本可能差距甚大。那么，你知道执行mysql 语句的访问类型具体有何不同，以及为何会选用这些类型呢。
type：表的连接类型，其值，性能由高到底排列如下：system：表只有一行记录，相当于系统表
const：通过索引一次就找到，只匹配一行数据
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描ref：非唯一性索引扫描，返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列range：只检索给定范围的行，使用一个索引来选择行。一般使用between、>、<情况
index：只遍历索引树ALL：全表扫描，性能最差
注：前5种情况都是理想情况的索引使用情况。通常优化至少到range级别，最好能优化到 refextra：包含不合适在其他列中显示但十分重要的额外信息，常见的值如下：using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值，应该优化 SQLusing temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 orderby 和分组查询 groupby。出现该值，应该优化 SQLusing index：表示相应的 select 操作使用了覆盖索引，避免了访问表的数据行，效率不错usingwhere：where 子句用于限制哪一行distinct：发现第一个匹配后，停止为当前的行组合搜索更多的行
注意：出现前 2 个值，SQL 语句必须要优化。我们回顾一下我们平时使用查询语句，或者使用索引时的场景。索引使用有个七字口诀就是：
模 型 数 空 运 最 快
比如：
SELECT * FROM `user` WHERE height= 180;
height为varchar类型导致索引失效。
这种情况下应该建立基于函数的索引。比如：
SELECT * FROM `user` WHERE DATE(create_time) = '2022-08-26';
create_time字段设置索引，那就无法使用函数，否则索引失效。
空：是Null的意思。索引不存储空值，如果不限制索引列是not null，数据库会认为索引列有可能存在空值，所以不会按照索引进行计算。
运：是运算的意思。对索引列进行（+，-，*，/，!, !=, <>）等运算，会导致索引失效。
最：是最左原则。在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
快：全表扫描更快的意思。如果数据库预计使用全表扫描要比使用索引快，则不使用索引。
mysql执行查询语句有 const、ref、range、index、all等方式，他们又有怎样的查询效率呢？（本文所有的B+树都是简化后的，只展示了叶子节点的图）通过主键或者唯一二级索引列与常数值进行等值比较来定位一条记录的访问方式就是const。凡是单点查询（x = 'xxx'）只命中一条记录才是const类型。使用const访问类型，MySQL 会直接在聚簇索引中利用主键值定位对应的用户记录。SELECT * from single_table WHERE id = 1438;
SELECT * from single_table WHERE key2= 3841;
const 访问类型很快，是因为主键和唯一键的单值查询只会命中1条记录，最多只会发生1次回表，而二级索引的单值查询可能会命中多条，从而引发多次回表。如果主键或唯一二级索引的索引列由多个列构成，则只有在索引列中的每个列都与常数都进行等值比较时才算使用了 const访问方法。对于唯一二级索引列，在查询列为 NULL 值时，不算是 const 而算ref，因为它可能命中多条null的记录。SELECT * from single_table WHERE key2 is null;
通过非唯一二级索引列与常数值进行等值比较来定位记录的访问方式就是ref（单点查询）。SELECT * from single_table WHERE key1 = 'abc';
- 二级索引执行查询时， 每获取到一条二级索引记录就会立刻对其进行回表操作，而不是将所有二级索引记录的主键值都收集起来后再统一执行回表操作。而且收集起来再回表是有几个id就回几次表。ref 比 const 的性能差在可能需要多次回表。
- 二级索引列允许存储 NULL 值时，不论是普通的二级索引 ，或者是唯一二级索引 ，在执行 "key IS NULL" 形式的搜索时，最多只能使用 ref 访问方法。
- 对于联合索引而言，只要满足最左前缀的单值查询才算是 ref 类型的查询。
select * from single_table where key_part1='god like';select * from single_table where key_part1='god like' and key_part2='legend';select * from single_table where key_part1='god like' and key_part2='legend' and key_part3='penta kill';
和ref 类似的还有一种 ref_or_null 的访问类型，表示单点查询不仅要找到某个常数值，还要找到null。
SELECT * from single_table WHERE key1 = 'abc' or key1 is null;
使用索引执行查询时， 扫描区间为若干个单点扫描区间 或者 范围扫描区间 的访问方法称为 range（范围扫描）。SELECT * FROM single_table WHERE key2 IN (1438 ， 6328) OR (key2 >= 38 AND key2 <= 79);
扫描区间是 [1438， 1438] 、[6328， 6328] 和 [38， 79]；如果一个范围查询的where没命中索引，而且无需回表（这要求搜索的列全在索引列范围内），就是index访问方式，一般我们把它称为覆盖索引。SELECT key_part1, key_part2, key_part3, id from single_table WHERE key_part2 = ' abc';
where key_part2 = 'abc' 并没有用到索引，因为where 条件是不遵循最左前缀原则的。但由于select 的字段全部都是idx_key_part这个索引所涵盖的字段，因此算用了覆盖索引。另外当通过全表扫描查询时 如果添加 order by id 语句，那么该语句在执行时也会被认定为使用的是 index 访问方法。*问题1：为什么扫描全部记录，二级索引的index访问方式的性能比 all 访问方式性能高？因为二级索引的叶子节点只存了索引字段，没有存记录上的其他列，这意味着二级索引的叶子节点能存储的记录数量比主键索引的叶子节点多。这样，相同记录数下，一个表的二级索引的叶子节点页数肯定比主键索引的叶子节点页数少很多。所以二级索引扫描全部记录 执行的磁盘IO次数 比 在主键索引扫描花的IO次数少。*问题2：为什么有时候优化器选择全表扫描也不用二级索引+回表？如果搜索得到的结果范围很大，从二级索引叶子节点查到的每一个主键值都要回表，此时优化器会使用全表扫描，而不是索引+回表的查询方式。1、一次回表会在主键索引（从根节点到叶子节点的过程）发生多次磁盘IO。全表扫描则是直接从叶子节点沿着链表指针往后遍历，不用每次都从根节点到叶子节点，相比于回表节省了多次IO次数，而且沿着链表指针的磁盘IO会有很多次顺序IO（也会有随机IO），速度会比回表的随机IO快。注意：mysql提出 MRR （多范围读取）的优化方法，为避免二级索引回表发生多次随机IO。该优化的做法是先读取一部分二级索引记录，将其主键排好序进行统一回表，这样可以节省一些IO次数（原理是利用相邻的ID可能位于同一个页中，这样一来多个ID统一回表就不会发生重复的磁盘IO）。二、索引合并的类型及使用场景
如果一个sql的where条件涉及两个独立的普通索引字段，mysql一般只会使用一个索引。但使用 索引合并 技术可以同时使用多个索引B+树。where条件涉及多个索引，且多个条件间的关系为 AND，此时可以同时根据这些条件查询多个索引B+树，并对得到的id取交集回表，这叫intersection索引合并例7：intersection索引合并使用多个索引SELECT * FROM single_table where key1 = ' a ' AND key3 = ' b';
4. 同时使用key1 和 key3 索引（索引合并）。使用索引合并时，innodb会在 key1索引 的B+树查找 ['a', 'a']区间的记录对应的id，也会在 key3索引 的B+树查找 ['b', 'b']区间的记录对应的id。再将这个结果集的id求交集得到同时满足 key1='a' 和 key3='b'的id，再根据这些id回表。使用索引合并要求从二级索引获取到的二级索引记录都是按照主键记录排好序的，也就是说两个索引字段只能单点查询不能范围查询。1、从两个有序集合取交集，比从两个无序集合取交集容易的多（举个例子，两个集合长度是m和n，如果两个集合都是有序的，那么取交集的复杂度为O(n+m)，如果是无序的则复杂度为O((m+n)logm) 或 O((m+n)logn) 或 O(mlogm + nlogn)））说说取交集算法。有序集合取交集：使用双指针i，j，比较 arr1[i] 和 arr2[j]。如果 arr1[i] > arr2[j]，则 arr2[j]不可能是结果集的元素，所以指针往后移，j++;如果 arr1[i] < arr2[j]，则 arr1[i]不可能是结果集的元素，i++;如果 arr1[i] == arr2[j]，则arr1[i]和arr2[j]就是结果集的元素，i++ ，j++；复杂度为O(n+m)。
无序集合取交集：如果对两个集合分别排序再求交集，则复杂度为 O(mlogm + nlogn);如果对集合m排序，再遍历集合n，让n的每个元素对集合m二分查找，则复杂度为 O((m+n)logm);如果对集合n排序，再遍历集合m，让m的每个元素对集合n二分查找，则复杂度为 O((m+n)logn);因此无序集合取交集用那种方式取决于 集合m和集合n的长度谁长。
2、从二级索引获得的回表id是有序的，那么回表操作可能包含顺序IO从而提高效率例8：不符合intersection索引合并的SQLSELECT * FROM single_table where key1 > ' a ' AND key3 = ' b';
不符合是因为 key1 > 'a' 时不能保证记录中的id是有序的。SELECT * FROM single_table where key1 > ' a ' AND key_part1 = ' b';
不符合是因为key_part1 是联合索引，所以 key_part1 = ' b' 的情况下不能保证记录中的id是有序的（因为联合索引先根据key_part1排序，再根据key_part2排，再根据key_part3排，最后才是根据id排。key_part1 = ’b'的情况下， key_part2 的值可能是多个不同的值，这样id字段就可能无序）。索引合并的优点是可以通过求交集减少满足where条件的id的个数，从而减少回表次数，即减少在主键索引发生的磁盘IO次数。缺点是需要在多个二级索引的B+树中查找，会增加在二级索引发生的磁盘IO次数。所以如果两个集合求交集后的结果集没有比原集合少多少，那么索引合并的性能就会比较差。是否使用索引合并需要衡量 减少的回表id的个数所减少的IO次数 带来的优化是否比得上 在二级索引增加的IO次数 带来的损耗。如果where条件涉及多个索引，并且多个条件间的关系为 OR，此时可以同时根据这些条件查询多个索引B+树，并对得到的id取并集回表。SELECT * FROM single_table WHERE key1 = ' a ' OR key3 = 'b';
单独用key1 或 key3 的任何一个索引都无法得到完整的目标记录，此时可以用全表扫描 或者 union索引合并这两种查询方案。union 索引合并是同时使用 key1 和 key3 这两个索引得到两个id集合，再将两个id集合求并集，为结果集的这些id回表。使用Union索引合并好处是通过求并集在二级索引回表前缩小搜索范围，减少了在主键索引的IO次数；坏处是要在每个索引发生多次随机IO。在二级索引得到多个id集合后，id集合的并集得到的结果集能缩小多少搜索范围，在决定是否使用Union索引合并例10：同时使用intersection索引合并和union索引合并SELECT * FROH single_table WHERE (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c') OR (key1 = ' a' AND key3 = 'b');
该例子可以通过 key1 和 key3 执行 intersection索引合并，得到的合并结果再与 key_part 这个联合索引执行Union索引合并。Union索引合并的使用要求各个索引中扫描到的主键值得是有序的（即只能单点查询），这和Intersection索引合并一样。Union索引合并要求各个索引使用单点查询才能触发Union索引合并，但这个条件太苛刻。因此提出了Sort-Union索引合并，它可以允许各个索引使用范围查询的时候就触发union索引合并。SELECT * FROH single_table WHERE key1 < ' a ' OR key3 > 'z';
该例不满足使用 Union 索引合并的条件，但可以使用Sort-Union索引合并来避免全表扫描。在 key1 索引中找到满足 key1<'a'的二级索引记录，将获得的id排序；在 key3 索引中找到满足 key3>'z'的二级索引记录，将获得的id排序；将在二级索引得到的两个主键集合求并集，再对结果集id进行回表。减少在主键索引的搜索范围也是Sort-Union的目的。凡是使用了索引合并的sql，其查询类型为：index_merge。Union和Sort-Union适用于“从单个二级索引获取的记录数较少的场景”，即 key1 <'a' 和 key2 > 'z' 在各自的二级索引中命中的id数较少。Intersection索引合并适用于“仅从单个二级索引获取的记录数太多，导致回表次数太多，因此需要使用多个二级索引”的场景。注意：Mysql没有实现 Sort-Intersecton 索引合并，但是MariaDB却实现了Sort-Intersecton 。SELECT * from t1, t2WHERE t1.m1 > 1 AND t1.m1 = t2. m2 AND t2.n2 < 'd';
- 确认第一个要查询的表。第一个要查询的表称为驱动表，假设以 t1 为驱动表。在驱动表中查询满足 m1 > 1的记录（有m1=2和m1=3这2条）；
- 根据从驱动表获取的每条记录，一次次的到被驱动表 t2 表进行匹配。由于 t1 得到 m1=2和m1=3这2条记录，因此需要在t2发生两次查询：第一次 t2.m2 = 2 and t2.n2 <'d'，第二次查询 t2.m2 = 3 and t2.n2 <'d'。而且每次在被驱动表t2发生的查询都是全表查询（因为没建立索引），即t2发生了2次全表扫描。
结论：驱动表只需查询1次，而被驱动表需要根据从驱动表筛选出来的记录进行多次查询。注意：并不是先将满足 t1 条件的驱动表记录先收集起来再去被驱动表中查，而是每获得一条驱动表记录就立即到被驱动表查询并将查询到的结果记录立刻发送客户端，然后重复。这样做是为了节省内存（如果满足条件的驱动表记录很多，把他们收集起来就会浪费一大片内存空间）。连接查询可分为内连接和外连接，对于内连接的两个表，若驱动表中的记录在被驱动表找不到匹配的记录，则这些驱动表的记录 不会加入到最后的结果集。左外连接（left join）选取left join左侧的表为驱动表。右外连接（right join）选取right join右侧的表为驱动表。不论是内连接还是外连接，凡是不符合 WHERE 子句中过滤条件的记录都不会被加入到最后的结果集。外连接的驱动表中的记录，如果无法在被驱动表中找到匹配 ON 子句中过滤条件的记录，那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL 值填充。我们专门为"外连接驱动表中的记录在被驱动表找不到匹配记录时 是否应该把该驱动表记录也加入结果集中"这个场景提出的ON 子句。所以在内连接中的 ON 子句 和 WHERE 子句是等效的，在外连接中则是不等效的。student 表只有一个主键索引 number，score有一个联合主键索引 number,subject。SELECT * FROM student, score WHERE student.number = score.number;
如果涉及到3个表的连接，则先把第1个表作为驱动表，第二个表作为被驱动表得到查询结果，再把第1、2个表连接查询的结果作为驱动表，第三个表作为被驱动表。用了索引，每次在被驱动表的查询都会走索引。如果不用索引，则每次在被驱动表的查询就是全表扫描。SELECT * from t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2. m2 AND t2.n2 < 'd';
假设 t1.m1 > 1的记录有2条，那么该连接查询相当于对 t2 进行2次查询：SELECT * from t2 WHERE t2. m2 =2 AND t2.n2 < 'd';SELECT * from t2 WHERE t2. m2 =3 AND t2.n2 < 'd';
a. 给 t2.m2 加索引，每次对t2的每次查询都是ref访问方式的查询（如果 m2 是t2的主键或者不允许存NULL的唯一键，那么这种访问方式在连接查询叫做 eq_ref）而不是全表扫描。b. 给 t2.n2 加索引，每次对t2的每次查询都是range访问方式的查询而不是全表扫描。对没有用到索引的嵌套循环连接的优化，减少重复磁盘IO。SELECT * from t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2. m2 AND t2.n2 < 'd';
假设 满足 m1 > 1有100个记录，t2的主键索引的叶子节点有1000个页，且t1和t2表没有任何索引。注：不考虑 buffer pool的缓存。就算考虑缓存，buffer pool 也没办法把所有页都缓存，所以每次扫描到 t2 后面的页，t2前面的页可能就失效了，等到第二次扫描 t2的时候，又要从磁盘读取。因此需要 t2 发生100次全表扫描，每次全表扫描会发生1000次磁盘IO，总共100 * 1000次磁盘IO。为减少重复的磁盘IO，可以将驱动表的查询结果放到 Join Buffer（连接缓冲区）的内存空间缓存起来，只在t2发生一轮全表扫描，就对 join buffer 中的所有记录的连接字段进行比较。例如满足 t1.m1 > 1 有100个记录，join buffer只能缓存 50条，需两次t2的全表扫描，第一次从t2全表扫描过程中，t2的第一个叶子页读到内存，将 join buffer 中的 50个 m1字段和第一个叶子节点的m2字段一个个比对（这里用不了二分查找，因为m2不是索引字段），再将 50 个 m1 和第二个叶子页的m2一个个比对，依次类推。这样只需要发生 2*1000 = 2000次磁盘IO。join buffer默认大小 256K，用到了索引的连接查询是不会用到 join buffer 的。join buffer中并不会存放驱动表记录的所有列，只有查询列表(select 的字段)中t1的列 和where条件中t1的列才会被放到 Join Buffer 中。加入了 join Buffer 的嵌套循环连接算法称为基于块的嵌套循环连接 (Block Nested-Loop join）算法。所以，不要把*作为查询条件，这样可以让join buffer 放更多记录。四、计算sql查询成本
MySQL读取一个页面花费的成本默认是1.0；内存中读取以及检测一条记录是否符合搜索条件的成本默认是 0.2。成本常数1 和 0.2 可以通过修改mysql配置来修改。IO成本 = 本次sql读取的总页数 * 1 CPU成本 = 本次sql读取到内存的所有叶子页的记录数总和 * 0.2。
IO 成本：把sql涉及的所有页从磁盘到内存的加载过程花费的时间，包括IO次数和每IO的时间。CPU成本：CPU成本是发生在内存中的。页读取到内存后，从页中（包括叶子页和非叶子页）筛选记录（页内的二分查找或直接遍历）、搜索条件比对、排序和分组等操作损耗的时间称为 CPU 成本。假设 key1和key3是普通索引，key2是唯一索引，key_part1是联合索引的第一个列，common_field不是索引。SELECT * from single_table WHEREkey1 IN ( ' a' , 'b' , ' c ' ) ANDkey2 > 10 AND key2 < 1000 ANDkey3 > key2 ANDkey_part1 LlKE '%hello%' ANDcorrrnon_field = '123';
1、explain + 语句，查看possible keys列，找出所有可能使用的索引。key3 > key2 这个条件的索引列由于没有与常数比较 因此不能产生合适的扫描区间，不能用到key3索引。key_part1 的like不是以字符串开头的通配符匹配，用不到索引。假设 single_table 的总页数为 97 页，9693条记录。全表扫描查询成本 = IO 成本 + CPU 成本 = 磁盘IO读取的页数 * 1.0 + 扫描的记录数 * 0.2。（97 * 1.0 + 1.1） + （9693*0.2 + 1.0）= 2037.7我们用 show table status like '表名' 获取表的总字节数（Data_length)和行数(Rows)。页数 = Data_length / 1024 / 16 = 97页。对于Innodb，Data_length 是聚簇索引占的存储空间，对于MyISAM，Data_length 是数据文件（MYD）的大小。需要注意，全表扫描的页数其实只读取了所有叶子页和从根节点到最左侧叶子页之间的几个非叶子页。但 MySQL在计算全表扫描成本时直接使用聚簇索引占用的所有页数作为计算 IO 成本的依据。
Mysql会分析单独使用key1和key2索引的成本以及使用索引合并的成本。使用索引查询的总成本分为在二级索引上的成本（扫描区间的个数（IO成本） 和 回表记录数（CPU成本）） + 在聚簇索引上的成本（回表次数（IO成本） 和 叶子节点扫描的记录数（CPU成本））。查询优化器粗暴地认为读取索引的 一个扫描区间的 IO 成本与读取一个页面的IO 成本是相同的，无论一个扫描区间在B+树上占用了多少个页。另外mysql 在评估回表操作的 IO成本时是很豪放的：他们认为每次回表操作都相当于访问一个页面的耗时。二级索引上的成本 = 扫描区间的个数 + 回表记录数 * 0.2聚簇索引上的成本 = 回表记录数（一条记录就需要回表1次） + 扫描主键索引叶子节点页的记录数 *0.2其中 扫描主键索引叶子节点页的记录数 可近似看做 回表记录数（但实际上前者肯定大于等于后者）。聚簇索引上的成本 = 回表记录数 + 回表记录数 *0.2先通过普通索引扫描出数据所在的行，再通过行主键ID 取出索引中未包含的数据。我们自己建的索引不管是单列索引还是联合索引，都称为普通索引，主键索引是聚簇索引，也就是索引的叶子节点存的是整个单条记录的所有字段值。每个普通索引就对应着一颗独立的索引B+树，索引 B+ 树的节点仅仅包含了索引里的几个字段的值以及主键值。当你执行一条sql语句时，需要从两个b+索引中去取数据。表tbl有a,b,c三个字段，其中 a是主键，b上建了索引，然后编写sql语句SELECT * FROM tbl WHERE a=1这样不会产生回表，因为所有的数据在a的索引树中均能找到；如果是SELECT * FROM tbl WHERE b=1这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，就出现了回表操作。简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。就是索引覆盖。可以不用去进行回表操作。*问：为什么设置了命中了索引但还是造成了全表扫描？就是虽然命中了索引，但在叶子节点查询到记录后还要大量的回表，优化器认为不如直接去扫描全表。那么key1 和 key2 的SQL成本的计算方式是怎样的：key2只有一个区间：(10, 1000)，所以在二级索引的IO成本 = 1.0。
回表记录数需要从key2索引的B+树读取页计算 n 和 p。平均一个叶子页的记录数 n，以及 key2 = 10 到key2=1000之间有多少个页 p，回表记录数=n * p。也就是说，计算两点之间的记录数，需要在二级索引从根节点往下找到区间左边界（10）的叶子节点以及它对应的记录，以及从根节点往下找到区间右边界（1000）的叶子节点以及它对应的记录（在二级索引定位一条记录的时间可以忽略不计），我们简称为b记录和c记录，他们所在的页叫做页b和页c。如果b和c记录不在同一个页，需要沿 b记录向右读10个页，算出每个页的平均记录数 n（也很快）；如果b和c记录在同一个页，则可直接算出b~c间的记录数，也就是需要回表的记录数。计算p还要从b和c记录所对应的（父节点）目录页a得到页b和页c之间的页数 p，而不用真的沿着叶子节点从页b读到页c。为了计算成本，mysql可能会到B+树读取页，损耗可忽略。假设回表记录数是95，则CPU成本 = 95*0.2 = 19。CPU成本 = (10, 1000)之间的记录数 * 0.2 = 95 * 0.2 = 19key1 in ('a', 'b', 'c')，是3个单点区间。假设需要回表的记录数为 35条a + 44条b + 39条c = 118。二级索引成本 = 3 + 118 * 0.2 = 26.6主键索引成本 = 118 + 118 * 0.2 = 141.6综上，总成本 = 26.6 + 141.6= 168.2。问：上例是否有可能使用索引合并（Index Merge）范围查询，不满足使用 Intersection 合并的条件，所以并不会使用索引合并。index dive方式是指，mysql评估二级索引 + 回表方式的成本时，直接访问索引对应的 B+ 树来计算某个扫描区间内对应的索引记录条数（回表记录数）。index dive 是在二级索引中分别从根节点往下找到区间的最左边记录和最右边记录（例如 一个区间是 ['n', 'n']，那么需要找到n 的最左记录 和 n 的最右记录），然后再计算这两条记录之间的记录数的结果就是回表记录数。index dive次数取决于区间的个数，例如 key2 > 10 and key2 <1000，只有一个区间，只会发生1次index dive。key2 in ('a', 'b', 'c')，产生了3个区间：[a, a],[b, b],[c, c]，会发生3次 index dive。索引条件中的扫描区间个数越多，优化器在评估成本时发生的IO次数越多。几个单点扫描区间可以忽略不记。如果in子句中有N个值，会发生N次 index dive，成本甚至比直接扫描全表的成本大。所以，一个sql语句的条件区间超过系统变量 eq_range_index _ div_limit 规定的值（mysql 5.7 之前是10个区间，mysql 5.7及之后是200）就不进行index dive，而是直接根据系统表中的索引统计数据来计算成本。索引统计数据可以通过命令 “ SHOW INDEX FROM 表名 ”查询得到。
这个数据是如何得到的呢？索引统计数据中有一个属性是索引的基数，也就是不重复值的数量，用表的行数 rows 除以基数 c 得到该字段的一个值平均会重复多少次。这种方式计算出来的成本误差可能很大，但不用访问B+树，节省了评估SQL成本所带来的的耗时。in中的参数个数 * (rows / c) = 需要回表的记录数
五、连接查询的成本
连接查询本质上是用驱动表的条件对驱动表查询一次得到结果集A，再根据结果A在被驱动表查询多次的过程（嵌套循环连接）。所以连接查询的成本：单次查询驱动表的成本+多次查询被驱动表的成本；我们把查询驱动表后得到的记录条数称为驱动表的扇出，扇出值越小，被驱动表的查询次数也就越少。连接查询总成本 = 单次访问驱动表的SQL成本 + 驱动表扇出值 × 单次访问被驱动表的成本。
上文已讨论单次访问表的成本，其中关键在于如何计算驱动表扇出值。SELECT * FROM s1 INNER JOIN s2 WHERE s1.key2 >10 AND s1.key2 < 1000;
那么满足 key2 > 10 且 key2 < 1000的记录数就是扇出值，而这个记录数可以在二级索引key2的B+树通过 index dive 操作得出。SELECT * FROM s1 INNER JOIN s2WHERE s1.key2 >10 AND s1.key2 < 1000AND s1.common_field = 'xyz';
在计算成本的阶段，因为common_field在主键索引的叶子节点中，需要遍历主键索引B+树的叶子节点，所以我们无法得出在 key2 > 10 且 key2 < 1000 下，common_field = 'xyz'的记录数有多少，这么干消耗过大，未免小题大做。内连接的驱动表是不固定的，不同的表作为驱动表，查询成本不同，也就是说优化器还需要考虑最优的表连接顺序。优化器会计算出所有不同表作为驱动表的成本情况，排列组合，如果对4个表进行连接，会有 4*3*2*1种成本。假设连接的排列组合顺序有很多，mysql有一个连接数阈值，如果一个sql的连接表数超过了这个阈值，mysql只会对数量等于该阈值的表进行穷举分析。此外，mysql会在穷举过程中记录当前最小成本，在XYZ这种连接顺序下，它的成本是穷举到目前为止最小的成本：10。在计算下一种情况YZX的成本时，如果Y和Z的连接成本已经大于10，就不会再计算 result(YZ) 与X的连接成本。知识点：
对需要查询和排序的字段要加索引。
尽量少地连接表。left join 比普通连接查询效率要高，注意观察索引是否起了作用。
排序尽量对第一个表的索引字段进行，可以避免mysql创建临时表，这是非常耗资源的。
对where条件里涉及到的字段，应适当地添加索引，这样会对排序操作有优化的作用。
如果说在分页时我们能先得到主键，再根据主键查询相关内容，也能得到查询的优化效果。
避免使用order by rand()。在执行过程中用show processlist查看，会发现第(3)条有Copying to tmp table on disk。
Slow queries 检查一下是哪些语句降低的Mysql 的执行效率，并进行定期优化。
适合使用索引的场景
主键自动创建唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段
查询中排序的字段
查询中统计或分组字段
不适合使用索引的场景
频繁更新的字段
where 条件中用不到的字段
表记录太少
经常增删改的表
字段的值的差异性不大或重复性高
文：一只阿木木
欢迎转发、交流小数据。
松花酿酒，春水煎茶。
眉上风止，见字如晤。
一只阿木木
一只阿木木约 25 分钟