MySql查询性能优化

一些语句

查询性能预估

select * from xxx; show status like 'last_query_cost';

开启慢查询

set global slow_query_log=ON;//开启慢查询 
set long_query_time=1;//设置慢查询, 1表示比1秒长算慢查询 
show status like 'slow_queries';//查询慢查询

查询状态

show full processlist;

查询重构后的查询

explain SELECT a.id from b LEFT JOIN a ON a.xxx= b.xxx WHERE b.name LIKE 'liu%' LIMIT 1; 
SHOW WARNINGS;

上一次查询的代价

show status like 'last_query_cost';

一、慢查询介绍

查询任务由无数个子任务组成，需要优化或者消除其中的一些子任务，或者减少执行次数。这就需要我们了解查询的生命周期、清楚查询的时间消耗。这其中的时间消耗可能在网络、CPU计算、统计信息和执行计划的生成、锁等待，需要对查询进行剖析才能定位到优化点。通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务端，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。

二、慢查询基础

慢查询基础：优化数据访问

分析低效查询从两个角度来看：

1. 应用程序是否检索了大量的超过需要的数据量；
2. MySQL服务器是否分析了大量的超过需要的数据量。

1.是否向数据库请求了不需要的数据：

• 查询不需要的记录（可以尽量在查询后面加上limit限制）；
• 多表关联查询时返回全部的列；
• 总是去除全部列（减少使用SELECT *，让优化器能够实现索引覆盖扫描，减少修改带来的问题）；
• 重复查询相同的数据（可以在应用中将数据缓存起来）

2.MySQL是否扫描了额外的数据

1. 响应时间 = 服务时间 + 排队时间；
2. 最理想的情况是扫描行数和返回行数相同。EXPLAIN语句中的type反映了访问类型，有全表扫描、扫描索引、范围访问、单值访问等（速度从慢到快）；rows反映了预估访问行数。
3. MySQL应用WHERE的三种方式，从好到坏：①存储引擎层过滤不匹配的记录 ②使用索引覆盖扫描 ③在MySQL服务层先从数据表读出记录然后过滤

三、重构查询的方式

目标应该时找到一个更优的方法获得实际需要的结果。方案如下：

1.切分查询

可以将一个大查询切分成多个小查询执行，每个小查询只完成整个查询任务的一小部分，每次只返回一小部分结果。比如定期清理log表，可以分解成多次执行，避免一次性锁住太多的数据。

2.分解关联查询

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

比如：

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,567,9098,8904);

优点如下：

1. 让缓存的效率更高。如果缓存的是关联查询的结果，那么其中的一个表发生变化，整个缓存就失效了。而拆分后，如果只是某个表很少的改动，并不会破坏所有的缓存。
2. 可以减少锁的竞争
3. 更容易对数据库进行拆分，更容易做到高性能和可扩展。
4. 查询本身的效率也有可能会有所提升。例如上面用IN()代替关联查询比随机的关联更加高效。
5. 关联查询会有冗余记录的查询（在数据库关联，可能需要重复访问一部分数据）。

四、查询执行的基础

MySQL执行一个查询的过程：

1. 客户端发送查询给服务器
2. 服务器先查询缓存，命中则返回，未命中继续
3. 解析SQL、预处理，送至查询优化器生成执行计划
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5. 结果返回客户端结果

 

1. 客户端/服务器通信协议

通信是半双工的，双方都可以发送数据，但同一时刻只有一方可以发送。没法进行流量控制，因此会涉及到数据包很大必须全部传完才行的问题。

2.查询缓存

查询缓存若打开会优先检查是否命中缓存中的数据，检查是通过一个大小写敏感的哈希查找实现。在缓存命中的情况下，查询的SQL语句都没有被解析，也不会生成执行计划，缓存中的结果直接返回给了客户端

3.查询优化处理

这一步主要将SQL转换成一个执行计划，包含了几个子阶段：解析SQL、预处理、优化SQL的执行计划。

4.1 语法解析器和预处理

通过关键字将SQL解析成一颗“解析树”，这一部主要检查解析树是否合法，例如表列是否存在等等。

4.2 查询优化器

基于成本，预测多种执行计划的成本，并选择成本最小的那一个，计算依据有：表或索引的页面数、索引基数、索引和数据行的长度、索引分布情况；

优化策略又分为静态、动态优化，静态直接对解析树进行分析，只需要做一次，动态优化和上下文有关，每次执行时都需要评估。

MySQL能够处理的一些优化类型：

• 重新定义关联表顺序；

• 将外连接转化为内连接；

• 等价条件变化，

• 比较的合并和减少；

• MIN、MAX等可以依靠B-Tree索引特点快速获取，

• COUNT根据存储引擎单独存储的值直接获取

• 预估并参数化表达式；

• 覆盖索引返回；

• 条件得到满足就立即停止查询；

• 关联查询的条件可以扩展到多个表上；

• IN条件进行二分查找优化。

4.3 数据和索引的统计信息

查询优化器在MySQL架构中的服务器层，但是服务器层没有保存数据和索引统计信息，存储引擎会提供给优化器表或索引的页面数、索引基数、数据行、索引长度和索引分布等等。

4.4 MySQL如何关联查询

关联查询不一定要两个表，每个查询都是一次关联，读取结果临时表也是一次关联。MySQL对任何关联都执行嵌套循环关联操作，也就是先从一个表中取出单条数据，嵌套到下一个表中寻找匹配的，直到找出所有的行，然后返回查询中需要的各个列，整体上都是一个”嵌套循环关联“

可以通过如下很好的了解：

 

例如如上查询，MySQL实际执行时总是从一个表开始一直嵌套循环、回溯所有关联：

实际表之间的关联顺序也可以被MySQL优化，表的关联顺序不一定按照自己定的顺序决定，MySQL会根据索引、扫描的行数选出最合适的顺序来进行更少的嵌套循环和回溯操作。

4.5 关联查询优化

这样以不同的表作为驱动表, 性能是不同的. 数据库会自动调优, 通过不同顺序时的成本来选择一个代价最小的进行查询. 也可以用straight join以手动指定的join顺序来查询.

4.6 排序优化

排序成本很高, 尽量不要排序或者不要对大量数据进行排序. 当排序列没有索引时, MySQL需要自己排序, 数据量小就在内存排序, 数据量大就在磁盘排序, 统一都用"filesort"。

关联查询需要排序有2种情况

• "Using filesort", 如果Order by子句中的所有列都来自关联的第一个表, 那么在关联处理第一个表时就进行文件排序.
• "Using temporary; Using filesort", 排序列不是全部来自第一个表, 就将结果存放到一个临时表, 等关联结束后再进行排序.

4.7 查询执行引擎

MySQL的查询计划是一个数据结构，不是其他关系型数据库那样的字节码。

4.8 返回结果给客户端

如果查询可以被缓存那么MySQL这个阶段也会将结果放到查询缓存中，返回过程是一个增量、逐步的过程。

五、查询优化的局限性

• 关联子查询

对于Exists和In中的子查询采用如下策略：

Exists执行顺序如下：①首先执行一次外部查询 ②对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。 ③使用子查询的结果来确定外部查询的结果集。

IN的执行过程如下： ①首先运行子查询，获取子结果集 ②主查询再去结果集里去找符合要求的字段列表，符合要求的输出,反之则不输出。

一般情况下，自己在选择的时候子查询表相对较大时选择exist，相对小时选择in。但这肯定不是绝对的，在实际中要测试验证执行计划和相应时间的假设。

• UNION的限制

MySQL无法将限制条件从“外层”推到“内层”，例如两表UNION操作，外层有一个limit操作，实际运行两表读取的行数不受limit影响，再两表组合成一个巨大的临时表后才会进行limit操作。需要在UNION的两个子查询分别加上limit来解决。

• 索引合并优化

合并索引的特性在于查询一个表时，AND和OR条件两边对应的是两个不同的索引，MySQL会分别利用两个索引查出结果，然后相应的做交集并集操作。

当两个索引的值选择性较高时，各自返回的数据较少，交集并集的操作成本也低，但是如果选择性不高，各自数据量大，合并索引也会带来效率的负增长。实际工作上，多数会选择关闭合并索引功能，在实际需要使用时改写SQL，将需要合并索引的地方写为UNION操作。

• 等值传递

如果有一个非常大的IN()列表，当优化器发现存在WHERE、ON或者USING子句时，会将这个列表的值和另一个表的某个列相关联，会将IN()列表都复制应用到关联的各个表中，列表很大时会导致优化和执行都变慢。（问题出现很少见）

• 并行执行

MySQL无法利用多核并行执行查询

• 哈希关联

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

• 松散索引

MySQL不支持松散索引扫描，无法按照不连续的方式扫描一个索引。简单来说也就是不支持跳过组合索引的前部分，必须使用全表扫描来获取数据。

• 最大值和最小值化

例如在查询一个主键的最小值时（有其他条件），在全表扫描的情况下，理论上读到的第一个满足条件的数据就是主键的最小值，因为主键是严格按照大小顺序排列，但是MySQL这时只会做全表扫描（可以使用limit 1）。

• 在同一个表上查询和更新

MySQL不允许同一张表同时做查询和更新，但有一种情况可以解决这样的问题，将此表作为一个临时表，在查询之后和需要更新的表做关联操作，使得更新到表只是一个临时表。

六、 查询优化器的提示

这部分介绍了若干的执行计划调整方法，如果对优化器给出的执行计划不满意的话可以在SQL语句上加上一些提示语句，通过提示提供给优化器一些特殊情况下选择某种查询计划的指导，规划一个比优化器自己选择的更好的执行计划。

七、优化特定类型的查询

1 优化COUNT()

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

• 统计某个列值的数量，即统计某列值不为NULL的个数。
• 统计行数。

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

偶尔也可以考虑使用近似值，例如统计COUNT值时采用EXPLAIN的优化器扫描值，或者去掉一些DISTINCT之列的约束来大大减少运行时间，得到几乎相同的运行结果。

更好的优化点还有选择索引覆盖扫描，新增加一个汇总表，增加一个外部缓存等等。

2 优化关联查询

• 表A，B关联时，如果优化器的关联顺序是B、A，那么在ON或者USING上的列就只需要在A表上创建索引。
• 确保GROUP BY和ORDER BY尽可能只使用一个列，这样才有可能使用索引优化。

3 优化GROUP BY和DISTINCT

当GROUP BY无法使用索引时会使用临时表或者文件排序来完成分组。

如果GROUP BY后没有通过ORDER BY显示指定排序列，默认排序会使用文件排序自动按照分组的字段进行排序，如果不需要排序可以使用ORDER BY NULL不使用文件排序。

4 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 sakila.film 
INNER JOIN (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5) AS lim 
USING(film_id);

注意优化中关联的子查询，因为只查询film_id一个列，数据量小，使得一个内存页可以容纳更多的数据，这让MySQL扫描尽可能少的页面。在获取到所需要的所有行之后再与原表进行关联以获得需要的全部列。

LIMIT的优化问题，其实是OFFSET的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。可以借助书签的思想记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就避免了使用OFFSET。可以把主键当做书签使用，例如下面的查询：

SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;

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

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

该技术的好处是无论翻页到多么后面，其性能都会很好。

此外，也可以用关联到一个冗余表的方式提高LIMIT的性能，冗余表只包含主键列和需要做排序的数据列。

5 优化UNION操作

UNION操作都是创建并填充临时表的方式来操作，如果不是确定要取出重复的行话可以使用UNION ALL代替，因为没有ALL的话MySQL会给临时表加上DISTINCT操作，代价会非常高。

6 语句中的自定义变量

有这些场景不能使用自定义变量：

• 使用了自定义变量不能查询缓存
• 不能在常量或者标示符的地方使用自定义变量（表、列名，LIMIT子句中）
• 自定义变量的生命周期是一个数据库连接中
• 自定义变量在MySQL 5.0之前对大小写敏感
• 如果用MySQL构建队列表，历史已完成消息归档、和未处理的消息分表。