Mysql是如何执行查询

本篇文章会按照以下思路来对mysql查询的原理进行讲解，首先讲解mysql是如何在单表上执行查询，在此基础上，讲解多表查询的原理，也就是join时，mysql是如何执行查询的，最后讲解mysql是如何计算查询的成本。

为了方便讲解定义一张表，具体定义如下

CREATE TABLE single_table ( 
    id INT NOT NULL AUTO_INCREMENT, 
    key1 VARCHAR(100) , 
    key2 INT, 
    key3 VARCHAR(100) , 
    key-part1 VARCHAR (100) , 
    key-part2 VARCHAR (100) , 
    key-part3 VARCHAR(100) , 
    common_field VARCHAR(100) , 
    PRlMARY KEY (id) , 
    KEY idx_key1 (key1) , 
    UNlQUE uk_key2 (key2)，
    KEY idx_key3 (key3) , 
    KEY idx_key-part(key-part1, key-part2, key-part3) 
) Engine=InnoDB charset=utf8;

单表访问原理

在Mysql数据库中，我们平时所写的那些查询语句本质上是一种声明式的语法。只是告诉MySQL要获取的数据符合哪些规则，至于MySQL背地里是如何把查询结果搞出来的则是MySQL自己的事儿。设计MySQL的大叔把MySQ执行查询语句的方式称为访问方法或者访问类型。.同一个查询语句可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是不同的执行方式花费的时间成本可能差距甚大。下面来看下具体的范文方法，如果之前使用过explain命令的话，应该比较熟悉下面的方法。

const

通过主键或者唯一二级索引列来定位某条记录的访问方法定义为const (意思是常数级别的，代价是可以忽略不计的) 。如果唯一索引由多列构成，则需要每一列都与常数值进行比较时才能使用const方法进行查询。如果唯一索引可以为NULL，由于NULL值可以有多条，这样的查询语句不算做const方法访问。

比如下面这俩条语句都是使用const方法来进行查询

1 2	SELECT * from single_table WHERE id = 1438; select * from single_table where key2 = "test";

具体的查找过程如下：

通过索引查找到对应的id值，如果查找的是主键，则不用执行第二步。
在聚簇索引上找到对应id值的完整用户记录。

ref

ref是搜索条件为二级索引列，并且与常数进行等值比较，形成的扫描区间为单点扫描区间采用二级索引来执行查询的访问方法。

比如下面这条语句

1	select * from single_table where key1="12"

具体的查找过程如下：

通过索引查找对应的id值
在聚簇索引上找到对应id值的完整用户记录

有以下俩点需要说明

由于普通二级索引以及唯一索引都不限制NULL值的数量，所以对于KEY IS NULL这种查找语句，最好的情况是使用ref，而不会使用const
对于索引列中包含多个列的二级索引来说，只要最左边连续的列是与常数进行等值进行比较，则就是ref查询

-- 下面三个是ref查询
SELECT  * from single_table WBERE key-part1 = 'god like'; 
SELECT * from single_table WHERE key-part1 = 'good like ' AND keY-part2 = ' legendary'; 
SELECT * FROM single_table WHERE key-part1 =  'good like' and key-part2 = ' legendary' AND 
key-part3 = 'penta kill' ;

-- 这个查询不属于ref查询
SELECT *  FROM single_table WHERE key-part1 = ' god like' AND key-part2 > 'legendary';

ref_or_null

在ref的基础上，还要查找出所有的NULL值记录，这种叫做ref_or_null。具体的查找过程和上面的ref类似

例子如下

1	SELECT * from single_table WHERE key1 = ' abc' OR key1 is NULL;

range

range是使用索引执行查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间的访问方法，仅含一个单点扫描间的方法不能
称为 range 访问方法，扫描区间为(-∞，+∞)的访问方法也不能称为range访问方法。

比如下面的例子

1	SELECT * FROM single_table WHERE key2 IN (1438 , 6328) OR (key2 >38 AND key2 <79);

index

符合下面俩个条件的查询语句称之为index

查询的列表全部包含在某一个联合索引中。
搜索条件也包含在联合索引中

比如下面这个例子

1	SELECT key-part1,key-part2,keY-Part3 from single_table WHERE key-part2 = 'abc';

这样的原因是因为联合索引的记录要比聚簇索引的记录小，同时不用回表进行查询，整体的查询成本要比扫描聚簇索引小。

在Innodb存储引擎中，如果添加了order by 主键,也会被认为是index

all

直接扫描全部记录的方式。

索引合并

mysql一般情况下只会为单个索引生成扫描区间，不过在特殊情况下，也可能会为多个索引生成扫描区间，这种使用多个索引来完成一次查询的方法叫做索引合并。目前有三种，分别是Intersection索引合并，Union索引合并和Sort-Union索引合并。

Intersection索引合并

查询多个索引的交集，及and操作，并且所有索引中的主键值都是按照主键值的顺序进行排列，则会进行Intersection索引合并查询，具体的查询过程如下

先使用各个索引查询符合要求的主键id
对查询的出来的主键取交集
根据最后的id结果执行回表操作。

例子如下

-- 使用索引合并
SELECT *  FROM single_table from  key1='a' AND key3 = 'b';

-- 由于key1,筛选出来的主键id不是按照主键id进行排序，不能使用索引合并
SELECT * FROM single_table WHERE key1 > ' a ' AND key3 = 'b'

Union索引合并

查询多个索引的并集，及or操作，并且所有索引中的主键值都是按照主键值的顺序进行排列，则会进行Union索引合并查询，具体的查询过程如下

先使用各个索引查询符合要求的主键id
对查询的出来的主键取并集
根据最后的id结果执行回表操作。

Sort-Union索引合并

这是对上面的Union索引合并条件进一步放宽，不要求主键值按照顺序进行排列。会在使用索引进行查找的过程中，对主键id进行排列，具体的过程如下

先使用各个索引查询符合要求的主键id并按顺序排列
对查询的出来的主键取并集
根据最后的id结果执行回表操作。

多表访问原理

对于Mysql支持的连接这里就不在进行介绍，直接介绍连接的查询过程，大体上分为以下俩步

首先确定第一个需要查询的表，这个表称为驱动表。确定的依据是查询代价最小的表作为驱动表，下面会介绍如何评估查询的代价。对于外连接则是已经确定的，如果是内连接，则才需要进行选取。
上一步每获取一条记录，都会到第二张表中查找匹配的记录。被匹配的表称其为被驱动表。

如果有3个表进行连接，那么步骤2中得到的结果集就像是新的驱动表，然后第3个表就成为了被驱动表，然后重复上面的过程。

上面所介绍的都是查询到一条记录就会到连接的表中进行查找，如果按照这种方式进行查询，会导致大量的随机IO。因此Mysql采取了下面这种策略。提出了一个连接缓冲区(Join buffer)的概念,Join Buffer就是在执行连接查询前申请的一块固定大小的内存。先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每条被驱动表的记录一次性地与Join Buffer中的多条驱动表记录进行匹配。由于匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的IO代价。查询过程如下图

Join Buffer的大小可以通过启动选项或者系统变量join_buffer_size进行配置，默认大小为256KB ，最小可以设置为128字节。当然，在我们优化对被驱动表的查询时，最好是为被驱动表加上高效率的索引。如果实在不能使用索引，并且自己机器的内存也比较大，则可以尝试调大join_buffer_size的值来对连接查询进行优化。另外需要注意的是，buffer中并不会存放驱动表记录的所有列，只有查询列表中的列和过滤条件中的列才会被放到Join Buffer 中，所以这也再次提醒我们，最好不要把*作为查询列表，只需要把关心的列放到查询列表就好了，这样可以在Join buffer中放置更多的记录。

基于成本的优化

一直在说MySQL执行一个查询时可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正地执行查询。下面对mysql如何计算成本的过程进行详细的描述。计算成本的前提是知道有哪些成本，主要包含下面俩个方面：

IO成本：我们的表经常使用的MYISAM和InnoDB存储引擎都是将数据和索引存储到磁盘上。当查询表中的记录时，需要先把数据或者索引加载到内存中，然后再进行操作。这个从磁盘到内存的加载过程损耗的时间称为IO成本.
CPU成本：读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称为CPU成本.

对InnoDB存储引擎来说，页是磁盘和内存之间进行交互的基本单位。MySQL规定读取一个页面花费的成本默认是1.0; 读取以及检测一条记录是否符合搜索条件的成本默认是 0.2。

基于成本优化的步骤

在真正执行一条单表查询语句之前MySQL的优化器会找出所有可以用来执行该语句的方案，并在对比这些方案之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。之后才会调用存储引擎提供的接口真正地执行查询。这个过程总结一下就是下面这样：

根据搜索条件找出所有可能使用的索引.
计算全表扫描的代价。
计算使用不同索引执行查询的代价.
对比各种执行方案的代价，找出成本最低的那个方案.

下面以一个实例来进行分析，表的具体定义在上面：

SELECT * FROM single_table WHERE 
  key1 IN ( ' a' , 'b' , ' c ' ) AND 
  key2 > 10 AND
  key2 < 1000 AND 
  key3 > key2 AND 
  key_part1 LlKE '%hello%'  AND 
  commnon_field = '123';

根据搜索条件找出所有可能使用的索引
下面来一步步的分析上面能使用到的索引

key1 IN ( ‘ a’ , ‘b’ , ‘ c ‘ ) ，这个可以使用key-part1 索引
key2 > 10 AND key2 < 1000，这个可以使用到uk_key2索引
key_part1 LlKE ‘%hello%’ ，这个由于不是左值匹配和等值匹配，不能使用索引
commnon_field = ‘123’，这个字段未建立索引，不能使用索引

通过上面的分析可知，能够使用到的索引有idx_key_part1和uk_key2。

计算全表扫描代价

对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中记录都依次与给定的搜索条件进行比较，并把符合搜索条件的记录加入到结果集中。所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=IO成本 +CPU成本，所以在计算全表扫锚的代价时需要两个信息.

聚簇索引占用的页面数
该表中的记录数.

在mysql中，默认会存储每张表的一些统计信息，可以通过SHOW TABLE STATUS来查看，比如查看single_table表

上面的字段比较多，我们主要关注俩个字段rows和data_length。

rows ：表示总共的行数，对于MyISAM存储引擎来说，上面的值是准确的，对于Innodb引擎来说，这个值是预估值。
data_length：表示占用的存储空间字节。对于使 MyISAM存储引擎的表来说该值就是数据文件的大小；对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小。也就是说可以按照下面公式来计算该值的大小:Data_Iength= 聚簇索引的页面数量×每个页面的大小。反过来也可以通过这个值来计算页面的数量。

有了上面俩个值就可以计算全表扫描的代价，计算公式如下

IO成本： data_length / 页面大小 X 1.0 + 1.1，1.0是成本值，1.1是微调参数，可以不用太在意。
CPU成本： rows*0.2 + 1.0 ，0.2是成本值，一样1.0是一个微调参数，不用太在意。
成本 = IO成本 + CPU成本。
计算不同索引的成本

由第一步分析，能够得到使用的索引，这里来看下如何计算索引的成本。计算索引的成本主要考虑的俩个方面：扫描的区间数和总的行数。计算的步骤如下

区间数量：根据查询条件，确定扫描区间的数量，在Mysql中，默认获取每个区间的的IO成本与获取一个页面的IO成本相同，也就是1.0
区间总的行数：获取行数的方式分为俩种
- 区间间隔的页数小于10，这种情况下，会将所有页面总的记录路进行统计，得到精确地行数。
- 区间间隔的页数大于10，这种情况下，先统计从最左边区间开始往右的10个页面行数的平均值，然后找到区间总的页数，将这俩个值相乘得到近似的总的行数。区间总的页数获取方式如下，根据B+树的结构可知，父节点的记录数就是页面数，可以统计所有父节点的记录数得到所有的页面数。
根据这些记录的主键值到聚簇索引中执行回表操作，默认获取一条记录的IO成本等于读取一个页面的成本，也就是IO成本=行数*1.0
回表操作后得到完整的用户记录然后再检测其他搜索条件是否成立。因此 cpu成本=行数*0.2

由上面还可以计算得出索引成本的计算公式
IO成本 = 区间数量 * 1.0 + 行数 * 1.0 (区间数量读取成本+ 回表的成本)
CPU成本 = 行数 * 0.2+ 0.01+ 行数 * 0.2 (查询二级索引的成本 + 读取并检测回表操作后聚簇索引记录的成本))

这里的计算索引成本的方式和真实计算成本的算法有一点出入，但大体上是相似的。另外如果不需要回表的索引的查询，可以只计算区间数量的成本和区间总的行数的成本。

对比各种执行方案的代价，找出成本最低的那个方案。前面已经得到了所有的成本值，比较大小就可以得到最小代价的方案。

基于索引统计数据的成本计算

对于下面这种查询语句：

1	SELECT * from single_table WHERE key1 IN ("aa1" "aa2", "aa3" , ...., "zzz" ) ;

可以看出扫描的区间比较多，并且不重复，假设按照前面介绍的索引成本计算方式来计算的话，是可以得到所有的成本，但是如果数量过多的话，计算索引成本本身的成本也是很大的。因此mysql设定了一个限制值eq_range_index_div_limit，来限制当索引区间大于这个值的时候，不使用上面的方式计算成本，而是使用基于索引统计数据的方式来估算索引成本。

前面我们介绍过表有统计数据，对应的索引也有统计数据，可以使用
SHOW INDEX FROM命令来查看，具体有以下字段

需要关注的是上面的Cardinality字段，表示不重复的数量，大体的意思就是，如果有1000条计算，这个值为1，则表示没有重复的值，索引的数量由1000个，如果值为1000，则表示所有的值都是重复的，只有一个索引。根据这个值以及总的行数，可以预估出每个索引大概对应的行数。在乘以单点索引区间的数量就可以预估得到区间总的数量。

多表连接的成本

对于多表连接来说，查询成本主要有以下俩部分构成

查询驱动表的成本
多次查询被驱动表的成本，次数取决于驱动表查询的结果集

先来分析俩表的连接成本分析
连接查询总成本=单次访问驱动表的成本 +驱动表扇出值 ×单次访问被驱动表的成本

对于外连接来说，驱动表和被驱动表是明确的。

先计算驱动表的查询成本
获取驱动表查询结果的数量
计算单次访问被驱动表的成本
最后计算总的查询成本

可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，因此需要考虑两个方面的问题

当不同的表作为驱动表时最终的查询成本可能不同，也就是需要考虑最优的表连接顺序
然后分别为驱动表和被驱动表选择成本最低的访方法.

因此整个的计算连接成本的方式变成：

获取所有的连接顺序
按照不同的顺序计算连接成本，计算成本的方法和上面的外连接计算方式相同
比较选取最小的成本，即确定对应的驱动表与被驱动表

由于内连接表的数量不确定，连接顺序是N的阶乘种，如果n过多的话，则计算多个成本，这个会带来一定的性能损耗。因此Mysql有了以下的优化措施

提前结束某种连接顺序的成本评估：会维护一个全局变量，这个表示当前最小的连接查询成本。如果分析某个连接成本时，在某个阶段已经大于这个最小的查询成本，则结束分析。
维护了系统变量optimizer_search_depth，如果连接的数量小于这个值，则穷举所有的连接进行成本分析，如果大于这个值，只对optimizer_search_depth数量相同的连接进行枚举。
启发式规则：在分析多个表的不同连接成本所花费的成本进行分析时，会根据一些经验设定了一些规则，只有满足这些规则的链接才进行分析，可以通过变量optimizer_prune_level来是否开启。

从上面可以看出，优化多连接的查询时，从俩个方面来进行，首先减少驱动表的扇出，本驱动表的成本要尽量低。

参考

Mysql是怎样运行的