mysql-查询优化

查询优化

当sql可以使用两个索引时，查询优化器只会走一个索引(优化器内部会粗略评估各种方式的成本，然后走成本最小的)。

尽量写成inner join，这样会交给优化器去选择驱动表

基本成本：一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器会选择其中成本最低的方案去执行查询。

I/O成本：InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本

CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本

InnoDB存储引擎规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2

基于成本的优化步骤
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询。

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

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

MySQL为每个表维护了一系列的统计信息， SHOW TABLE STATUS 语句来查看表的统计信息。
SHOW TABLE STATUS LIKE ‘titles’;

Rows：
表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。

Data_length：
表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：
Data_length = 聚簇索引的页面数量 * 每页的大小
我们的titles使用默认16KB的页面大小，而上边查询结果显示Data_length的值是20512768，所以我们可以反向来推导出聚簇索引的页面数量
聚簇索引的页面数量= Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252

我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。但是MySQL在真实计算成本时会进行一些微调

计算PrimaryKey需要的成本
计算PRIMARY需要多少成本的关键问题是：需要预估出根据对应的where条件在主键索引B+树中存在多少条符合条件的记录。

范围区间数
当我们从索引中查询记录时，不管是=、in、>、<这些操作都需要从索引中确定一个范围，不论这个范围区间的索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。本例中使用PRIMARY的范围区间只有一个：(10101, 20000)，所以相当于访问这个范围区间的索引付出的I/O成本
就是：

优化器需要计算索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算PRIMARY在(10101, 20000)这个范围区间中包含多少条数据记录，计算过程是这样的：
* 步骤1：先根据emp_no > 10101这个条件访问一下PRIMARY对应的B+树索引，找到满足emp_no > 10101这个条件的第一条记录，我们把这条记录称之为区间最左记录。
* 步骤2：然后再根据emp_no < 20000这个条件继续从PRIMARY对应的B+树索引中找出第一条满足这个条件的记录，我们把这条记录称之为区间最右记录。
* 步骤3：如果区间最左记录和区间最右记录相隔不太远（只要相隔不大于10个页面即可），那就可以精确统计出满足emp_no > ‘10101’ and emp_no < '20000’条件的记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？计算它们父节点中对应的目录项记录之间隔着几条记录就可以了。根据上面的步骤可以算出来PRIMARY索引的记录条数，所以读取记录的CPU成本为：26808*0.2=5361.6，其中26808是预估的需要读取的数据记录条数，0.2是读取一条记录成本常数。

链接查询的原理：
不管是内连接还是左右连接，都需要一个驱动表和一个被驱动表，对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左连接的驱动表就是左边的那个表，右连接的驱动表就是右边的那个表。
连接的大致原理是：

1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的访问形式来执行对驱动表的单表查询。
2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。
   对应伪代码就是：

for each row in t1 { //此处遍历满足对t1表单查询的每一条记录
	for each row in t2 { //此处表示对于某条t1表的记录来说，遍历满足对	t2单表查询结果集中的每一条记录
	// 判断是否符合join的条件
	}
}

嵌套循环连接：
上面的过程就像是一个嵌套的循环,所以这种驱动表只访问一次时,但被驱动表却可能被多次访问,访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接(Nested-Loop Join),这是最简单,也是最笨拙的一种连接查询算法。
比如对于下面这个sql:

mysql> select * from t1 join t2 on t1.a = t2.a where t1.b in (1,2);

先会执行：

mysql> select * from t1 where t1.b in (1,2);
+---+------+------+------+------+
| a | b | c | d | e |
+---+------+------+------+------+
| 1 | 1 | 1 | 1 | a |
| 2 | 2 | 2 | 2 | b |
| 5 | 2 | 3 | 5 | e |
+---+------+------+------+------+
3 rows in set (0.00 sec)

得到三条记录，然后分别执行：

mysql> select * from t2 where t2.a = 1;
mysql> select * from t2 where t2.a = 2;
mysql> select * from t2 where t2.a = 5;

所以实际上对于上面的步骤，实际上都是针对单表的查询，所以都可以使用索引来帮助查询。

基于块的连接查询：
扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。现实生活中的表可不像t1、t2这种只有几条记录，可能会有成千上万的数据。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边又说过，采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。
Mysql中有一个叫做join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和joinbuffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样可以在join buffer中放置更多的记录。

外连接消除
内连接的驱动表和被驱动表的位置可以相互转换，而左连接和右连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。

外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃

In 子查询优化：
mysql对IN子查询进行了优化。
比如：

mysql> select * from t1 where a in (select a from t2);
1,1,2
liang

对于不相关的IN子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：
* 结果集太多，可能内存中都放不下
* 对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这会导致：

	无法有效的使用索引，只能对外层查询进行全表扫描。
	在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。

在mysql中，不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：
该临时表的列就是子查询结果集中的列。写入临时表的记录会被去重。IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对
整个IN语句的结果并不影响，所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得更小。临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引就可以进行去重。一般情况下子查询结果集不会特别大，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。IN语句的本质就是判断某个操作数在不在某个集合里，如果集合中的数据建立了哈希索引，那么这个匹配的过程就是很快的。如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

这个将子查询结果集中的记录保存到临时表的过程称之为物化（Materialize）。那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

还是对于上面的那个sql：

mysql> select * from t1 where a in (select a from t2);

当我们把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val，那么这个查询其实可以从下边两种角度来看待：
* 从表t1的角度来看待，整个查询的意思其实是：对于t1表中的每条记录来说，如果该记录的a列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。
* 从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在t1表中找到对应的a列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。

也就是说其实上边的查询就相当于表s1和子查询物化表materialized_table进行内连接：
select * from t1 inner join materialized_table on t1.a = m_val;
转化成内连接之后，查询优化器就可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。

虽然将子查询进行物化之后再执行查询会有建立临时表的成本，但是可以将子查询转换为JOIN还是会更有效率一点的。那能不能不进行物化操作直接把子查询转换为连接呢。
我们对比下面两个sql：

mysql> select * from t1 where a in (select a from t2);
mysql> select t1.* from t1 inner join t2 on t1.a = t2.a;

这两个sql的查询结果其实很像，只是说对于第二个sql的结果集没有去重，所以IN子查询和两表连接之间并不完全等价，但是将子查询转换为连接又真的可以充分发挥优化器的作用，所以MySQL提出了一个新概念半连接（semi-join），将t1表和t2表进行半连接的意思就是：对于t1表的某条记录来说，我们只关心在t2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留t1表的记录。semi-join只是在MySQL内部采用的一种执行子查询的方式，MySQL并没有提供面向用户的semi-join语法 。
那么怎么实现semi-join呢？