Mysql单表访问方法，索引合并，多表连接原理，基于规则的优化，子查询优化 - Cuzzz

参考书籍《mysql是怎样运行的》
非常推荐这本书，通俗易懂，但是没有讲mysql主从等内容
书中还讲解了本文没有提到的子查询优化内容，
本文只总结了常见的子查询是如何优化的

系列文章目录和关于我

一丶单表访问方法

mysql执行查询语句的方法叫做访问方法，同一语句使用不同的访问方法执行，查询结果都是一样的，但是不同的查询方法效率差距很大，mysql优化器会选择成本最低的访问方法，理解访问方法对我们理解索引有益处

1.const

查询可以通过主键或者唯一索引与常数进行等值比较来定位一条记录，这种访问方法被定位为const，如果唯一索引存在多列，那么需要多列都进行等值比较。（唯一索引不限制null元素的个数，所以is null并不会使用const访问方法）

2.ref

搜索条件为二级索引与常数进行等值比较的，形成的扫描区间为单点扫描区间（key='a' key是二级索引，需要扫描的区间是[a,a],这称为单点扫描区间）只需要定位到满足条件的第一条记录，然后沿着B+树叶子节点的指针向右查找直到不满足条件即可（也许需要回表，并不是将所有满足的数据从二级索引上拿到主键然后一起回表，而是每获取一条便立即回表）。这种访问方法称为ref.

同样二级索引允许存储null值，且不限制个数（唯一二级索引也不限制）但是为null的值放在B+树的最左侧，查找的流程任然一致，即使是key is null 也必须要进行单点扫描、

如果二级索引存在多列，并不需要多列都进行等值比较，但是要求最左连续的列进行等值比较（比如联合索引a,b,c,a=1 and c=2 可以使用ref，找到a=1向右并且索引下推过滤掉不满足c=2的记录，减少回表，但是如果是b=1 and c=2 这时候是无法使用ref的，因为联合索引是先按照a排序，再依次b，c。）

3.ref_or_null

查询条件是二级索引等值查询 or 二级索引 is null,可以使用ref_or_null,这其实涉及到两个扫描区间[null,null],[等值,等值]执行流程和ref一样。

4.range

使用索引执行查询时，对应的扫描区间是若干单点区间，或者范围扫描区间，那么可以使用range(全表扫描不能算作range，单个单点扫描区间是ref而不是range）

5.index

我们知道二级索引需要存储索引列和主键，聚簇索引需要存储所有列和主键（以及隐藏列）所以二级索引大小远小于聚簇索引，且如果一个查询不需要进行回表，那么将直接利用二级索引进行全表扫描（索引小，意味着IO次数小）这种访问方法叫index

比如select 主键 from table where 无法走索引的条件，那么这时候不如扫描二级索引，其B+树叶子节点保存的是主键和索引列，每一页可以存放更多数据，减少IO次数，其中的主键也可以覆盖需要查询的主键

6.all

直接扫描所有的聚簇索引记录

二丶多范围读取MRR

上面我们说到回表，是每从二级索引中获取一条符合的数据都会到聚簇索引根据主键进行回表，但是二级索引中的主键是无需的，这导致每次执行回表操作都是随机IO，导致性能开销巨大，mysql为了优化这种随机IO，使用了MRR多范围读取，即先读取一部分二级索引，然后将主键值排序后再统一执行回标，将随机IO优化为顺序IO。

三丶索引合并

通常情况下，mysql只会为单个索引生成扫描区间，但是存在特殊情况，mysql可以为多个索引生成扫描区间，这种多个索引生成扫描区间来完成依次查询的方法称为索引合并

1.交集索引合并

select xxx ,xxx from table where key1=1 and key2=2 （key1和key2都是二级索引）mysql可以选择使用key1，也可以使用key2索引，获取符合的主键然后回表并过滤不符合的记录。也可以分别从key1索引中获取满足key1=1，从key2索引中获取 key2=2的主键值，再获取二者交集最后进行回表，这样可与减少不必要的回表操作。

使用交集索引合并的话，要求通过二级索引查到的主键本身就是有序的，这样获取交集效率更高，并且减少了随机IO。

• 如果具有a,b两个普通索引,执行查询select * from table where a>1 and b=2 那么是无法进行交集索引合并的,因为a>1得到的主键并不是有序的，
• 同样联合索引q,w,e 普通索引r 执行select * from table where q=1 and w=2 and r=3也不可以使用交集索引合并，因为联合索引是依次根据q,w,e排序的，满足q=1 and w=2的数据主键并不是有序的。
• 普通索引a，主键为id，select * from a=1 and id>100这样的查询理论上是主键有序可与使用的，但是mysql会找到满足a=1且id>100的第一条记录，然后向右直到不符合条件的数据出现，这种情况也不需要使用交集索引合并

2.并集索引合并

select * from table where a>1 or b>2 a,b均为普通索引，无法只使用a或者b索引进行查询，但是可分别从a和b中获取满足条件的主键，然后取二者并集后回表即可。这称之为并集索引合并，同样也是要求从单个索引获取到的主键值是有序的，

3.排序并集索引合并

并集索引合并要求根据单个索引获取到的主键是有序的，然后取并集回表，条件比较苛刻。mysql支持分别从各个索引中扫描得到记录的主键让排序，再取并集进行回标查询，这种称为排序并集索引合并

四丶连表查询的原理

上面我们研究了单表查询，下面我们学习一下多表连接查询

1.连接的本质

连接的本质就是将各个表中的数据都取出来进行依次匹配，并且将匹配后的结果发送回客户端。无论那个表作为驱动表产生的笛卡尔积肯定是一样的，而对于内连接来说凡是不符合on和where的查询都不会加入到结果集，内连接的驱动表和被驱动表可以交换，但是对于外连接来说，驱动表中的记录即使在被驱动表中找不到符合on的记录，也会被加入到结果集，驱动表和被驱动表不可随意交换

2.连接中的过滤条件

在连接时过滤掉特定的记录是非常必要的（如果不进行过滤那么就是多表的笛卡尔积，条数是多个表条数的乘积）多表连接查询中的过滤条件分为一下两种

• 单表查询的搜索条件

  比如select * from a left join b where a>1 ,这里的单表查询搜索条件就是a>1,如果a是索引那么将使用单表查询的range访问方法

• 涉及两表的条件

  比如select * from a , b where a.k1 = b.k2 and a.k3>b.k4，这里涉及的条件便是a.k1 = b.k2 and a.k3>b.k4，对于这种擦好像执行过程如下

  1. 首先确定第一个需要查询的表,称为驱动表

     mysql会选择代价最小的访问方法对驱动表进行查询

  2. 1中的驱动表获取到的每一条记录，都需要去另外一张表中查询匹配的记录

     比如在a作为驱动表获取到了a.k1=1,a.k3=2 那么接下来需要去表b中查询满足1=b.k2 and 2>b.k4的记录（并不是将在驱动表中获取到的记录缓存起来，再一起到被驱动表中进行查询，而是每在驱动表中获取到一条记录都会到被驱动表中查询满足的数据）

3.内连接和外连接

• 对于内连接的两个表，如果驱动表中的记录在被驱动表中找不到匹配的记录，那么该记录不会加入到最后的结果集。

  如select * from user_detail d ,user u where u.id = d.id查询结果如下

  

  user_detail 中具备非常多的记录，但是和user 中id匹配的记录只有id=3，这里也只能查出一条记录

• 对于外连接的表，即使驱动表中的记录在被驱动表中找不到匹配的记录，那么也会加入到结果集中

  如select * from user_detail d left join user u on u.id = d.id查询结果如下

  

  即使在on u.id = d.id的限制下，只有id=3满足，其他id不满足，也会将其他记录加入到结果集，被驱动的字段使用null填充

4.外连接的分类

外连接分为左外连接 即左连接，右外连接 即右连接，二者的区别在于左连接是左边的表作为驱动表，右连接使用右边的表作为驱动表。

5.where 和 on

在外连接中存在where 和 on两种过滤条件。

1. where

   无论是内连接，还是外连接，只要不符合where条件的记录都不会加入到最后的结果集中

2. 对于外连接，如果无法在被驱动表中找到符合on的记录，驱动表的记录还是会加入结果集中，对应的被驱动表的各个字段使用null填充。

   如select * from user_detail d left join user u on u.id = d.id where d.detail like '%Dz%'查询结果如下

   

   最后只有满足d.detail like '%Dz%'的记录才会加入到结果集。

   对于下面的两个内连接查询语句

   select * from user_detail d inner join  user u on u.id = d.id and d.detail like '%Dz%'
   select * from user_detail d inner join  user u on u.id = d.id where d.detail like '%Dz%'
   

   查询结果如下：

​ 说明在内连接中 on 等同于where

6.连接查询的原理

上面我们复习了内连接外连接的基础知识，下面我们开始学习下mysql是如何进行连接查询的

6.1嵌套循环连接

• 选取驱动表，使用驱动表相关的过滤条件，选择代价最低的单表访问方法对驱动表进行单表查询
• 对上述查询中获取的每一条记录，都分别到被驱动表中查找匹配的记录。

这样就将夺多表的连接查询，转化为多次单表查询，其中驱动表只需要访问依次，但是被驱动表需要访问多次，访问的次数取决于驱动表执行单表查询后的结果集具备多少条记录。

这或许就是为什么外连接建议使用小表join大表

6.2 使用索引加载连接速度

在获取到驱动表的单表查询后的一条记录后，需要在被驱动中找到符合条件的记录

select * from a left join b on a.id=b.id where a.name like '%陈%' and b.age>10

首先是执行select * from a where a.name like '%陈%' ,在a表中得到a.id=1和a.id=2的记录，然后去b中执行select * from b where b.id=1 and b.age>10 和select * from b where b.id=2 and b.age>10

如果id是b表的索引，这时候就可以使用到索引，或者age是b表的索引也同样可以使用到索引,由于查询的是*如果索引B+树叶子节点的内容无法覆盖，那么将进行回表

6.3 基于块的嵌套连接查询

上面我们说到嵌套循环连接，每次在驱动表中获取到一条记录都需要去被驱动表中进行查询，如果驱动表查询到了很多条数据，被驱动表数据量很大，且无法使用到被驱动表的索引，那么需要对被驱动表进行多次全表扫描，导致IO代价非常大，所以需要减少被驱动表的访问次数

解决的办法便是，将被驱动表的记录加载到内存，一次性和驱动表中的多条记录进行匹配。mysql有一个join buffer,在执行连接查询的时候申请一块固定大小的内存，先把若干条驱动表结果集，转载join buffer中，然后开始扫描被驱动表，然后被驱动表的记录一次性与join buffer进行匹配，由于匹配是在内存中进行的，这样可以显著减少被驱动表的io代价。

Join buffer的大小可以通过join_buffer_size,如果实在无法在被驱动表上使用到较好的索引，mysql服务所在的机器内存比较大，可以调大对连接查询进行优化。

Join Buffer并不会存储驱动表的所有字段，只会存储涉及到查询条件的字段和查询列表中的字段，所以尽量不要使用select * 可以让join buffer存放更多的数据

五丶基于规则的优化&子查询优化

1条件化简

• 移除不必要的括号，虽然我们sql里面写了无用的括号（方便人阅读）但是mysql会将括号移除掉

• select * from a where a.age=10 and a.b>a.age 会被优化为select * from a where a.age=10 and a.b>10

• 移除无用的条件

  恒成立的条件会被移除

• 表达式计算

  对于select * from a=5+1会被优化为a=6

  但是对于abs(a)>5则不会优化，且无法使用索引

• having子句和where 子句合并

  查询语句中没用sum，max等聚集函数以及group by子句，那么having会和where子句合并起来

  select * from user u group by u.name having sum(u.age)>4 比如这条将无法和where 合并

  但是select * from user u having u.age>4等同于select * from user u where u.age>4

• 常量表替换

  mysql innodb 认为使用主键等值查询，或者唯一索引等值查询的搜索成本，这种查询称为常量表查询（const访问方法）。查询优化器在分析一个查询语句的时候，优先使用常量表查询，然后把查询中涉及的条件替换成常数，然后再去分析其余表的查询成本。如:

  select * from table1 inner join table2 on table1.column1 = table2.column2 where table1.primarykey = 1

  其中我们可以看到table1.primarykey=1使用主键进行等值查询，可以先不分析table2表查询成本，先执行select * from table1 where table1.primarykey = 1 然后将语句转换成如下

  select table1满足where的记录中各个字段，table2.* from table1 inner join table2 on table1表记录中column1列常量值 = table2.column2

2.外连接消除

上面我们说过，对于外连接，即使被驱动表的记录不满足on子句，还是被加入到结果集，但是无论什么记录只要不满足那么都无法加入到最终结果集。如果我们在where子句中指定了被驱动中的列不为空，这时候外连接的驱动表和被驱动表顺序就可以交换了，mysql优化器可以通过评估不同的连接顺序的成本，来选择成本最小的顺序来执行查询

这种指定被驱动表，或者隐含被驱动表列不为空的查询条件称为——空值拒绝

3.子查询优化

3.1相关子查询和不相关子查询

• 相关子查询：子查询的执行依赖于外层查询的值

  如:select * from t1 where t1.m1 in (select t2.m2 where t2 where t2.n2 = t1.n1)

  这里面的 t2.n2 = t1.n1,t2.n2的筛选依赖于外层查询得到的t1.n1

• 不相关子查询：

  子查询可以独立运行，不依赖于外层

3.2 子查询在mysql中是如何执行的

3.2.1 标量子查询，行子查询的执行方式

• 标量子查询：返回单一值的子查询

  1. 对于select * from s1 where key1 =(select f from s2 where key3='a' limit 1)其中select f from s2 where key3='a' limit 1是一个标量子查询。

     mysql会先执行select f from s2 where key3='a' limit 1得到 f的值为1 然后再替换执行select * from s1 where key1=1。也就是说对于不相干标量子查询，可以视作两个查询。

  2. 对于select * from s1 where key1 = (select f from s2 where s1.key3 = s2.key3 limit 1)其中select f from s2 where s1.key3 = s2.key3 limit 1是一个相关标量子查询。

     mysq会先从外层查询中获取一条记录，从s1表查询一条记录。然后从这条记录中找到子查询中涉及到的值，即找到key3的值(假设是3)，然后执行子查询select f from s2 where 3 = s2.key3，假设返回的f=4，再返回到校验外层查询key1 = 4是否成立，继续执行直到遍历完s1中所有记录

• 行子查询：返回一行记录的子查询（包含多个列）

  同标量子查询

3.2.2 in子查询优化

1. 对于不相关子查询select * from s1 where key1 in (select f from s2 where key3='a')

   如果子查询中记录条数很少，那么当作两个查询即可，但是如果子查询记录非常多，导致内存放不下。mysql可以将in子句的查询结果放入临时表，并对记录进行去重（根据查询结果列建立主键，唯一索引，或者联合唯一索引），in子句的结果去重并不影响执行结果，如果子查询结果集并不是很大那么会使用memory赢球，并且建立hash索引（in子句相当于判断列是否在临时表中，这时候hash索引可以起到很好的作用），如果很大那么会使用基于磁盘的存储引擎来保存结果集中的记录，并使用B+树。

   这种将子查询结果集中的记录保存到临时表的过程称作物化表

2. 物化表转连接

   假如上面物化表名称为tmp，并且为查询结果建立了索引pk，select * from s1 where key1 in (select f from s2 where key3='a')查询就是判断s1中key1是否在tmp表的主键中，建立了索引那么判断是非常块的。但是mysql还会继续优化为内连接，如

   select s1.* from s1 inner join tmp on s1.key1 = tmp.pk 转换为内连接后，查询优化器，可以评估使用不同连接顺序的成本，并选择成本最小的查询方式

3. 子查询优化为半连接

   • 转换为物化表，再执行查询具备建立临时表的成本。

     select * from s1 where key1 in (select f from s2 where key3='a')可以被优化为

     select s1.* from s1 inner join s2 on s1.key1 = s2.f where s2.key3 = 'a'

     但是这存在s2中满足s2.key3 = 'a' 存在多个重复值f，导致s1中一条记录连接相同f值多条记录的情况，失去了in子句去重的特性

     为了解决这个问题，引入了半连接的操作，将s1和s2进行半连接意思就是：对于s1中某条记录来说，只关心在s2表中是否存在匹配的记录，而不关心具备有多少个记录与之匹配，最终结果集只保留s1表中的记录

   • 那么如何实现半连接

     1. Table pullout 子查询中表上拉

        如果in中的子查询查询的是被驱动表的唯一索引，或者主键列时，直接将in子句优化为内连接

        select * from s1 where key1 in (select f from s2 where key3='a')如果f是主键或者唯一索引，那么直接优化为select s1.* from s1 inner join s2 on s1.key1 = s2.f where s2.key3 = 'a'，因为f列中的值本身就是不重复的

     2. Duplicate weedout 重复值消除

        还是优化为内连接，即使f列存在重复值，也直接执行查询，但是执行结果使用临时表存储驱动表的主键id，消除在子查询中查询到的重复值导致内连接产生重复行的问题

     3. LooseScan 松散扫描

        首先将in优化成select s1.* from s1 inner join s2 on s1.key1 = s2.f where s2.key3 = 'a'内连接，然后将s2作为驱动表，这时候先执行select f from s2 where s2.key3= 'a' ，可能得到多个重复的f，但是只取第一个去被驱动表s1中匹配，虽然是扫描索引，但是只取第一条记录执行匹配操作的方式称为松散扫描

     4. Semi-join Materialzation 半连接物化

        就是上面提到的，将in中子查询查询结果物化成一张表，然后执行连接查询

     5. FirstMatch 首次匹配

        这个也很好理解，相当于在嵌套循环连接中，先从外层查询中取一条记录，然后到子查询表中找到符合的记录，如果能找到一条那么加入到结果集并停止寻找多条，循环往复直到结束。