MySQL 性能-查询

优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。
查询的生命周期大致可以按照：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。

优化数据访问

不要请求不需要的数据

1. 查询不需要的记录

   例如在处理分页时，应该使用 limit 限制 MySQL 只返回一页的数据，而不是向应用程序返回全部数据后，再由应用程序过滤不需要的行。

2. 多表关联时返回全部列

   只取需要的列。

3. 总是取出全部列

   select * 会让优化器无法完成索引覆盖扫描这类优化，带来额外的 I/O 内存 CPU 消耗。

4. 重复查询相同的数据

   当一行数据被多次使用时可以考虑缓存起来，避免每次使用都要到MySql查询。

避免扫描额外的记录

响应时间：数据库处理查询所用的服务时间，和服务器等待资源的排队时间。并无规律和公式，尝试上限估计法。

扫描的行数：不是所有行的访问代价都相同；较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。

访问类型：explain 中的 type 列反应了访问类型，全表扫描，索引扫描，范围扫描，唯一索引扫描，常数引用，速度从慢到快；通常增加合适的索引。

where 条件从好到坏：

• 在索引中使用 where 条件来过滤不匹配的记录，在存储引擎层完成；
• 使用索引覆盖扫描返回记录，直接从索引中过滤不需要的记录并返回命中结果，在服务器层完成但无需回表查询；
• 从数据表中返回数据，然后过滤不满足条件的记录（Extra 列中出现 Using Where）

若发现查询需要扫描大量数据但只返回少数的行：

• 使用索引覆盖扫描（见索引）；
• 改变库表结构，如使用单独的汇总表（见库表）；
• 重写这个查询以适应优化器

---

重构查询的方式

一个复杂查询 or 多个简单查询

一些情况下分解很有必要，但不要对能够胜任的查询下手。

切分查询

分治，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果，可以避免锁住很多数据、占满事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

分解关联查询

对每个要关联的表进行单表查询，然后将结果在应用程序中进行关联：

select * from tag
->	join tag_post on tag_post.tag_id=tag.id
->	join post on tag_post.post_id=post.id
where tag.tag='mysql';

可以分解成：

select * from tag where tag='mysql';
select * from tag_post where tag_id=1234;
select * from post where post.id in(123,456,789);

优势：

• 让缓存的效率更高。如果缓存的是关联查询的结果，那么其中的一个表发生变化，整个缓存就失效了。而拆分后，如果只是某个表很少的改动，并不会破坏所有的缓存；
• 可以减少锁的竞争；
• 应用层做关联，更容易对数据库进行拆分，做到高性能和可扩展；
• 查询本身的效率也有可能会有所提升。例如用 in() 代替关联查询比随机的关联更加高效；
• 可以减少冗余记录的查询，对于某条记录应用只需要查询一次；、、
• 相当于在应用中实现了哈希关联，而不是使用 MySQL 的嵌套循环关联。

场景：

• 应用能够方便地缓存单个查询的结果的时候；
• 数据分布到不同的 MySQL 服务器上的时候；
• 能够使用 IN() 的方式代替关联查询的时候；
• 查询中使用同一个数据表的时候。

---

查询执行基础

1. 客户端发送一条查询给服务器；

2. 服务器先检查查询缓存，如果命中则立刻返回结果，否则进入下一阶段；

3. 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划；

4. MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询；

5. 将结果返回给客户端。

MySQL CS间的通信

半双工，无法进行流量控制，一旦一端开始发生消息，另一端要接收完整个消息才能响应它。

多数连接 MySQL 的库函数从 MySQL 获取数据时，实际是从这个库函数的缓存获取数据，这样可以尽早释放服务器的资源。

查询连接/线程状态，SHOW FULL PROCESSLIST 命令 Command 列：

• Sleep，线程正在等待客户端发送新的请求；
• Query，线程正在执行查询或者正在将结果发送给客户端；
• Locked，在 MySQL 服务器层，该线程正在等待表锁。存储引擎级别的锁如 InnoDB 的行锁，不会体现在线程状态；
• Analyzing and statistics，线程正在收集存储引擎的统计信息，并生成查询的执行计划；
• Copying to tmp table [on disk]，线程正在执行查询，并且将其结果集都复制到一个临时表中，要么是在做 group by 操作，要么是文件排序操作，或者是 union 操作。on disk 则正在将内存临时表放到磁盘上；
• Sorting result，线程正在对结果集进行排序；
• Sending data，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

查询缓存

若查询缓存是打开的，MySQL 通过一个对大小写敏感的哈希查找检查是否命中。若恰好命中，返回查询结果之前 MySQL 会检查一次用户权限。

查询不会被解析，不用生成执行计划，不会被执行。

查询优化处理

未匹配缓存时，查询的生命周期的下一步是将 SQL 转换成一个执行计划，MySQL 再依照执行计划和存储引擎交互。这些包括：解析 SQL，预处理，优化执行计划。

语法解析器和预处理

MySQL 解析器使用语法规则验证和解析查询，是否使用错误的关键字，其顺序是否正确，引号能否前后匹配等。

预处理器则根据一些 MySQL 规则进一步检查是否合法。检查数据表和数据列是否存在，名字和别名是否有歧义等，然后验证权限。

查询优化器

一条查询可以有多种执行方式，优化器从中找到最好的执行计划。

MySQL 使用基于成本的优化器，其尝试预测一个查询使用某种执行计划时的成本（不考虑任何缓存），并选择其中成本最小的一个。使用show status like 'Last_query_cost'; 查看需要多少个数据页的随机查找。

导致优化器选择错误的执行计划的原因：

• 统计信息不准确，依赖存储引擎提供的统计信息进行评估，如 InnoDB 引擎不能维护一个数据表的行数的精确统计信息（因为 MVCC）；
• 执行计划中的成本估算不等同于实际执行的成本，无法得知真实的物理 I/O，比如顺序读或者在内存中；
• MySQL 的最优基于成本模型，而不是时间尽可能短；
• MySQL 从不考虑其他并发执行的查询；
• MySQL 也并不是任何时候都是基于成本的优化，一些固定规则有更高优先级，如 match() 子句（即使别的索引或 where 条件更快，仍使用对应的全文索引）；
• MySQL 不会考虑不受其控制的操作的成本，如执行存储过程或用户自定义函数；
• 优化器无法去估算所有可能的执行计划。

查询优化器的策略可以简单分为静态优化和动态优化。静态优化不依赖于特别的值，在第一次完成后就一直有效即使用不同的参数重复执行查询也不会发生变化，比如通过一些简单的代数变化将条件转换成另一种等价形式。动态优化和查询的上下文有关，每次执行都要重新评估，比如 where 条件中的取值，索引中条目对应的数据行数等。MySQL 可以处理：

• 重新定义关联表的顺序；

• 将外连接转化成内连接，where 条件，库表结构等因素可能让外连接等价于一个内连接；

• 使用等价变换规则，合并和减少一些比较，移除一些恒成立和恒不成立的判断，比如 (a<b and b=c) and a=5 会改写成 b>5 and b=c and a=5 。

• 优化 count() min() max() ，要找到某一列的最小值，只需要查找对应 B-Tree 索引的最左端即直接获取第一行记录，查找最大值则是最后一条记录，在 explain 中可以看到 “Select tables optimized away”，表示优化器已经从执行计划中移除了该表，并以一个常数取而代之。类似的， count(*) 查询通常可以使用存储引擎提供的数据。

• 预估并转化为常数表达式，如一个用户自定义变量在查询中没有发生变化时就转换为一个常数。如果 where 子句中使用了索引的常数条件，MySQL 可以在查询开始阶段就先查找到这些值，优化器将其转换为常数表达式：

     select film.film_id, film_actor.actor_id
     -> from film
  ->     inner join film_actor using(film_id)
     -> where film.film_id=1;

> MySQL 会分成两步来执行这个查询，也就是上面的两行输出。第一步先从 film 表找到需要的行。因为在 film_id 字段上有主键索引，所以 MySQL 优化器知道这只会返回一行数据，优化器在生成执行计划的时候，就已经通过索引信息知道将返回多少行数据。因为优化器已经明确知道有多少个值（ WHERE 条件中的值）需要做索引查询，所以这里的表访问类型是 const 。
在执行计划的第二步，MySQL 将第一步中返回的 film_id 列当作一个已知取值的列来处理。因为优化器清楚在第一步执行完后，该值就是明确的了。注意到正如第一步一样，使用film_actor字段对表的访问类型也是 coonst 。
另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表，这可以通过 WHERE、USING 或者 ON 语句来限制某列数的值为常数。在上面的例子中，因为使用了 USING 子句，优化器知道这也限制了 film_id 在整个查询过程中都始终是一个常量——因为他必须等于 WHERE 子句中的那个取值。

• 覆盖索引扫描，当索引中的列包含所有查询中需要使用的列的时候，就可以使用索引返回需要的数据，而无须查询对应的数据行；

• 子查询优化，某些情况下 MySQL 将子查询转换为效率更高的形式，以减少多个查询多次对数据访问；

• 提前终止查询，在发现已经满足查询需求的时候，MySQL 能够立刻终止查询，如 limit 子句。若发现了一个不成立的条件，MySQL 会立刻返回一个空结果。当存储引擎需要检索不同取值或判断存在性时，会提前终止查询判断下一个，类似这种 不同值 不存在 的优化一般可用于 DISTINCT、NOT EXIST() 或者 LEFT JOIN 类型的查询；

• 等值传播，如果两个列的值通过等式关联，那么MySQL能够把其中一列的WHERE条件传递到另一列上：

  select film.film_id
  -> from sakila.film
  ->     inner join sakila.film_actor using(film_id)
  -> where film.film_id > 500;

  因为这里使用了film_id字段进行等值关联，MySQL知道这里的WHERE子句不仅适用于film表，而且对于film_actor表同样适用。

• 列表 in() 的比较，在 MySQL 中，in() 先将自己列表中的数据进行排序，然后通过二分查找的方式确定列的值是否在 in() 的列表中，这个时间复杂度是 O(logn) 。如果换成 or 操作，则时间复杂度是 O(n) 。所以，对于 in() 的列表中有大量取值的时候，用 in() 替换 or 操作将会更快。

数据和索引的统计信息

服务器层有查询优化器，但不保存数据和索引的统计信息，统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（甚至没有）。查询优化器在生成查询的执行计划时需要向存储引擎获取。

MySQL 执行关联查询 (join)

在 MySQL 中，任何一个查询都是一次关联，即使只有一个表的查询也是如此。

对于 union 查询，MySQL 先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成 union 查询。MySQL 读取结果临时表和普通表一样，也是采用的关联方式。

当遇到子查询时，先执行子查询并将结果放到一个临时表中，然后再将这个临时表当做一个普通表对待。MySQL 的临时表是没有任何索引的，在编写复杂的子查询和关联查询的时候需要注意这一点，临时表也叫派生表。

MySQL 对任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，直到找到所有表中匹配的行为止。例如对于 SQL 语句：

SELECT tbl1.col1,tbl2.col2
-> FROM tbl1 INNER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6);

伪代码表示：

out_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = out_iter.next
while outer_row
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    while inner_row
        output[outer_row.col1, inner_row.col2]
        inner_row = inner_iter.next
    end
    outer_row = outer_iter.next
end

对于单表查询，那么只需要完成外层的基本操作。

关联查询优化器

尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树（MySQL 不生成查询字节码而是指令树）。

如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行树的成本，最后返回一个最优的执行计划，如果有超过 n 个表的关联，那么需要检查 n 的阶乘关联顺序，此时搜索空间的增长速度非常快。一旦超过 optimizer_search_depth 的限制，优化器会选择贪婪搜索模式。

有时各个查询的顺序不能随意安排，比如左连接等需要依赖前表的结果。这会大大减少搜索空间。

排序优化

应该尽量让 MySQL 使用索引进行排序。当不能使用索引生成排序结果的时候，如果数据量小于“排序缓冲区”的大小，则 MySQL 使用内存进行“快速排序”操作。如果数据量太大超过“排序缓冲区”的大小，那么 MySQL 会将数据分块，对每个块“快速排序”并将结果存放在磁盘上，最后合并返回。

MySQL 文件排序操作使用的临时存储空间可能相当大，因为每一个排序记录都会分配一个足够长的定长空间，此空间必须能容纳其中最长的字符串（varchar 列需要分配完整长度，UTF-8 每个字符预留三字节）。

返回结果

经查询执行引擎逐步执行指令后（并非字节码），将结果增量、逐步的返回客户端，比如服务器处理完最后一个关联表开始生成第一条结果时，就可以逐步返回结果集了。

即使不需要返回结果集，MySQL 仍会返回查询的一些信息，比如影响到的行数。

---

查询优化器的局限性

关联子查询

MySQL 的子查询实现的非常糟糕。最糟糕的一类查询是 where 条件中包含 in() 的子查询语句。MySQL 会将外层表压到子查询中，导致关联子查询，比如：

select * from film
-> where film_id in(
->     select film_id from film_actor where actor_id=1);

-- 预想的处理
select * from film
-> where film_id
->     in(1,23,25,213,344,546,788,907);

-- 实际的处理
select * from film
-> where exists(
->     select * from film_actor where actor_id=1
->     and film_actor.film_id = film.film_id);

MySQL 先对 film 表全表扫描，然后根据返回的 film_id 逐个执行子查询。

应该尽可能用关联替换子查询，可以提高查询效率（如果使用MySQL5.6以上或MariaDB则可以忽略）。当然，子查询不一定是最慢的，应该用测试来验证。

union 的限制

如果希望 union 的各个子句能根据 limit 只取部分结果集，或先排好序再合并结果集的话，则需要在各个子句中分别使用 ：

-- 将 actor 和 customer 的所有记录存放在临时表
(select first_name, last_name from actor order by last_name)
-> union all
-> (select first_name, last_name from customer order by last_name)
-> limit 20;

-- 临时表只包含 40 条记录
(select first_name, last_name from actor order by last_name limit 20)
-> union all
-> (select first_name, last_name from customer order by last_name limit 20)
-> limit 20;

最大值最小值优化

比如：

-- first_name 字段没有索引，将会全表扫描
select min(actor_id) from actor where first_name='xxx';

MySQL 读到第一个满足条件的记录时就是最小值了，因为 actor_id 上主键索引是按大小顺序排列的。曲线优化：

select actor_id from actor use index(primary)
-> where first_name='xxx' limit 1;

在同一张表上查询和更新

MySQL 不允许同时进行：

-- 将表中相似行的数量记录到字段里
update tb1 as outer_tb1
->     set cnt = (
->         select count(*) from tb1 as inner_tb1
->         where inner_tb1.type = outer_tb1.type
->     );

使用临时表来处理：

update tb1
->     inner join(
->         select type, count(*) as cnt from tb1
->         group by type
->     ) as der using(type)
-> set tb1.cnt = der.cnt;

子查询在 update 打开表之前完成，update 关联的表是一个临时表。

摘要

MySQL 无法利用多核特性来并行执行查询。

MySQL 不支持哈希关联，其关联都是嵌套循环关联。

MySQL 不支持松散索引扫描，5.0 后在分组查询中需要找到分组的最大值和最小值时可以使用。5.6 后通过索引条件下推的方式解决。

---

优化特定类型的查询

count()

count() 有两个不同的作用：

1. 统计某个列值的数量，即统计某列值不为 null 的个数。
2. 统计行数。

当使用 count(*) 时，统计的是行数，它会忽略所有的列而直接统计所有的行数。而在括号中指定了一个列的话，则统计的是这个列上值不为 null 的个数。

比如将条件反转：

-- 扫描记录较多
select count(*) from city where id>5;
-- 用总数减去
-- 子查询会直接作为常数处理
select (select count(*) from city) - count(*) from city where id<=5;

关联查询

确保 on 或者 using 子句中的列上有索引。

确保任何的 group by 和 order by 中的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引优化这个过程。

limit 分页

处理分页会使用到 limit ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时 limit 的效率会非常差。例如对于 LIMIT 10000，20 这样的查询，MySQL 需要查询 10020 条记录，将前面 10000 条记录抛弃，只返回最后的 20 条。这样的代价非常高，如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要与原表做一次关联操作返回所需的列。对于偏移量很大的时候，这样的效率会提升非常大。考虑下面的查询：

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果这个表非常大，那么这个查询最好改写成下面的这样子：

SELECT film.film_id, film.description FROM film
-> INNER JOIN 
-> (SELECT film_id FROM film ORDER BY title LIMIT 50,5) AS lim
-> USING(film_id);

注意优化中关联的子查询，因为只查询 film_id 一个列，数据量小，使得一个内存页可以容纳更多的数据，这让 MySQL 扫描尽可能少的页面。在获取到所需要的所有行之后再与原表进行关联以获得需要的全部列。
limit 的优化问题，其实是 offset 的问题，它会导致 MySQL 扫描大量不需要的行然后再抛弃掉。可以借助书签的思想记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就避免了使用 offset 。可以把主键当做书签使用，例如下面的查询：

SELECT * FROM rental ORDER BY rental_id DESC LIMIT 20;

假设上面的查询返回的是主键为 16049 到 16030 的租借记录，那么下一页查询就可以直接从 16030 这个点开始：

SELECT * FROM rental WHERE rental_id < 16030
ORDER BY rental_id DESC LIMIT 20;

该技术的好处是无论翻页到多么后面，其性能都会很好。此外，也可以用关联到一个冗余表的方式提高 limit 的性能，冗余表只包含主键列和需要做排序的数据列。

union 查询

除非确实需要服务器消除重复的行，否则一定要使用 union all 。如果没有 all 关键字，MySQL 会给临时表加上 distinct 选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。