这篇主要介绍对慢 SQL 优化的一些手段，而在讲解具体的优化措施之前，我想先对 进行介绍，它是我们在分析查询时必要的操作，理解了它输出结果的内容更有利于我们优化 SQL。为了方便大家的阅读，在下文中规定类似 key1 的表示二级索引， 表示联合索引的第一部分， 则表示唯一二级索引， 表示主键索引。和高性能MySQL实战（二）：索引是本文的前置知识，欢迎大家阅读。
一、 详解
是我们在对慢 SQL 进行优化前常用语句，它能分析具体的查询计划，从而让我们有目的地去进行优化。本节则主要是让大家看懂 查询结果的每一列是干啥用的，我们先简要的来看一下各个列的作用：
列名
描述
id
在一个大的查询语句中，每个 关键字都对应一个唯一的 id。在连接查询中，记录的 id 值都是相同的；在多个 关键字的查询中，查询优化器可能会对子查询进行优化，使得多条 记录的 id 值相同
查询类型
table
表名
匹配的分区信息
type
针对单表的访问方法
可能用到的索引
key
实际使用的索引
实际使用的索引长度
ref
当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows
预估的需要读取的记录条数
针对预估的需要读取的记录，经过搜索条件过滤后剩余记录条数的百分比。在单表查询中没什么意义，在连表查询中可以计算出在驱动表执行完查询后，还需要对被驱动表执行多少次查询
Extra
额外的备注信息
其中大部分列在描述信息中已经解释的足够清楚，下面我们主要对一些必要的列进行详述：
1.1 .2 type
在外连接中，ON 语句是专门为“驱动表中的记录在被驱动表中找不到匹配记录时，对应的被驱动表记录的各个字段使用 NULL 来填充”场景提出的；在内连接中，ON 和 WHERE 的作用一致
1.3 ref
当访问方法是 const、ref、、、 和 其中之一时，ref 列展示的是与索引列进行等值匹配的东西是啥：
1.4 Extra
索引条件下推：它是针对二级索引查询条件做的优化，在对二级索引条件进行判断时，会将所有该索引相关列的条件都判断完成后，符合条件再执行回表操作，不符合条件则不再执行回表，这样做减少了回表操作的次数，从而减少了 I/O。
如下例子：
* from where key1 >'a' and key1 like '%b';
索引条件下推会将 key1 所有条件判断完而不是只判断完 key1 >'a'就去回表。
二、优化考虑点
基于访问类型优化
在前文中我们已经详细介绍了 语句中的访问类型（type），如果一个查询的访问类型并不是我们预期的，那么最简单直接的解决办法是为搜索条件列增加合适的索引。
减少扫描行数的优化
在有些情况下，简单地增加索引并不能解决问题，比如执行如下 SQL：
select name, count(name) from specific_table group by key1;这条 SQL 执行完毕后可能只返回几行数据，但是因为有 COUNT 聚合函数，需要扫描的数据可能会有成千上万行，这取决于表中数据量总数。对于这种扫描大量数据却只返回少数行的情况，通常可以通过增加单独的汇总表进行优化，当然这需要在应用层增加相应的逻辑对汇总表的数据进行维护。
除此之外，还可以通过重写复杂查询的方式来优化，下面我们对重写查询时需要考虑的方向进行介绍：
一个复杂查询还是多个简单查询？
这是一个值得考虑的问题。将复杂查询拆成多个简单查询，尽可能地减少数据库的工作，并将一些处理逻辑拿到应用层处理，因为 MySQL 处理简单查询很高效，所以通常情况下这么做能够提高效率。
切分处理
在实际工作中，对数据量较大的数据库表进行结转（或删除）时通常会采用切分处理的方法，将一个大查询分成小查询，每个查询的作用是一样的高性能mysql，只不过操作的数据量不同，各个小查询执行完毕后，大查询的任务也就处理完成了。
一次性结转大量数据可能会锁住很多数据、占满整个事务日志、耗尽系统资源和阻塞很多小的查询等，为了避免这种情况，通常在一次数据结转任务中只操作一万条左右数据，这样对服务器影响最小，而且可以在每次结转完成时，都暂停一会儿再去执行下一次任务，这样做可以将压力分散到一个比较长的时间段中，大大降低对服务器的影响和减少持有锁的时间。
优化联结查询
如果联表过多，我们需要将其拆成多个查询或多个单表查询（单表查询的缓存效率会更高），查询被分解后，查询间的锁竞争会减少。除此之外，联表查询还需要注意以下两点：
IN() 条件与 OR 条件
一般情况下我们认为 IN() 完全等价于多个 OR 条件，但是在 MySQL 中这两者是有区别的。MySQL 在处理 IN() 条件时，会将列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个时间复杂度为 O(logn) 的操作，如果等价地转换成 OR 查询，它的时间复杂度为 O(n)，所以在 IN() 条件中有大量取值时，MySQL 的处理速度会更快。
查询时索引是否失效
select key_part1, key_part2, key_part3 from specific_tablewhere key_part1 = 1 and key_part3 = 3;在查询条件中略过了 ，那么只能使用到索引的第一列，如果略过的是 那么就无法使用到这个联合索引了
select * from specific_table where key1 * 2 > 4;排序时索引是否失效
有如下 SQL，其中 和 是按顺序的联合索引，执行时不能使用索引
select key_part1, key_part2 from specific_tableorder by key_part1, key_part2 desc;在.0版本，可以支持ASC和DESC混用使用索引
select id, key1, key2 from specific_table order by key1, key2;因为它们非同一索引，在 key1 相同的情况下，是不会按照 key2 列进行排序的，所以用不到索引
select key_part1, key_part3from specific_tableorder by key_part1, key_part3;select id, key1, key2 from specific_table order by upper(key1)索引列不为空的优化
当需要 Min() 和 Max() 操作时，索引列不为空可以让它们更高效。比如要找到某一列的最小值，只需要查询对应 B-Tree 索引的最左端记录，查询优化器会将这个表达式看做一个常数对待，而且能够在 结果的 Extra 列中发现 “ away”。
重复索引和冗余索引
重复索引指的是在相同的列上按照相同顺序创建的相同类型的索引，如下 SQL 所示：
create table specific_table (id int not null primary key,unique key(id))engine=InnoDB;它在 id 列上创建了两个相同的索引，需要将其中的唯一索引移除。
冗余索引通常发生在为表添加新的索引时，比如在已有索引（），再添加一个索引（, ），这就是发生了冗余索引的情况，因为第二个联合索引能够发挥和单列索引一样的作用。
大多数情况下都不需要冗余索引，我们应该尽量扩展已有的索引而不是创建新的索引。
是否存在索引合并
在多列上独立地创建多个单列索引，大部分情况下并不能提高 MySQL 的查询性能。
MySQL 中有一种 “索引合并” 的策略，它可以使用表中的多个单列索引来定位指定的数据行，并将扫描结果进行合并。索引合并的策略有时候非常不错，但更多的时候，它说明了表中的索引建的比较糟糕：
通常来说，我们需要考虑重建索引或者使用 UNION改写查询。除此之外，可以通过修改 参数来关闭索引合并功能，如下 SQL：
SELECT @@optimizer_switch;-- 改成 index_merge=offset optimizer_switch = 'index_merge=off, ...';还可以使用 INDEX 语法让优化器来忽略到某些索引，从而避免优化器使用包含该索引的索引合并执行计划：
select * from specific_table ignore index(index_name)where column_name = #{value};除了在发生索引合并时考虑忽略索引，也需要在执行查询时因无法形成合适的扫描区间，达不到减少扫描记录的数量的目的时，考虑忽略索引而使用全表扫描。
下面我们介绍三种索引合并的类型，让大家对索引合并有一个更加充分的了解：它们分别是 索引合并、Union 索引合并和 Sort-Union 索引合并。
索引合并
我们看如下查询：
select * from specific_table where key1 = 'a' and key2 = 'b';我们都能清楚的是：在索引列值相同的情况下，二级索引记录是按照主键值的大小排序的，那么可以将 key1 筛选出的主键值和 key2 筛选出的主键值取交集，根据结果再去执行回表操作，这相比于分别对 key1 和 key2 筛选出的主键值都去做回表的开销要低，这种情况使用的是 索引合并策略。
Union 索引合并 我们看如下查询：
select * from specific_table where key1 = 'a' or key2 = 'b';将 key1 筛选出的主键值和 key2 筛选出的主键值取并集，再根据结果去做回表操作，这种做法被称为 Union 索引合并，它可能相比于直接做全表扫描的开销要低。需要注意的是：Union 索引合并要求二级索引筛选出的主键值是有序的，如果主键值无序则需要考虑 Sort-Union 索引合并。Sort-Union 索引合并
有如下查询：
select * from specific_table where key1 < 'a' or key2 > 'b';我们将上述查询条件更改成了范围查询条件，现在各索引筛选出的主键值是无序的，所以无法使用 Union 索引合并，而 Sort-Union 索引合并正是在 Union 索引合并的基础上添加了排序操作：将 key1 筛选出的主键值和 key2 筛选出的主键值进行排序，这样就能够继续使用 Union 索引合并了。
优化 COUNT()
当我们需要统计有值的结果时，需要在 COUNT() 条件内指定列名或 COUNT(0)；当我们需要统计所有的行数时，需要指定为 COUNT(*)，它会忽略所有列而直接统计所有行数。明白了这两点之后，我们做数据统计能够更清晰地传达意图。
通常来说，COUNT() 查询需要扫描大量的数据行才能获得精确的结果，所以比较难优化。如果业务场景不要求完全精确，我们可以使用 估算的行数 rows来代替；或者，我们去掉一些查询条件中的约束，删除 来避免排序操作，这些做法都可能使统计查询性能提高。
优化 UNION 查询
在我们使用 UNION 查询时，如果不需要消除重复的行，一定要使用 UNION ALL，因为如果没有 ALL 关键字，MySQL 会给临时表加上 ，这会对数据做去重，代价比较高。此外，我们可以将 WHERE、LIMIT 和 ORDER BY 语句应用到每个查询中，这样能够让 MySQL 对它们更好地进行优化。
优化
在分页查询中， 会导致 MySQL 扫描大量不需要的行然后再抛弃掉，比如 LIMIT 1000, 20 这个表达式，它会查询 1020 条数据然后将前 1000 条抛弃掉，这样做的代价非常高。
我们可以通过采用书签的方式记录上次读取数据的“位置”，那么下次查询就能直接从该位置开始扫描，避免使用 。比如说，每页展示 20 条数据，我们记录下来当前所在页面的数据 ID 值为 200，那么我们看下一页的数据时，查询 SQL 如下：
select * from specific_tablewhere id <= 180limit 20;不过，这种情况也有不足，它没有办法指定页码进行查询，比如说我现在想看第 5 页的数据高性能mysql，我们没办法计算对应页具体的 ID 值范围。除非我们能够保证 ID 值是单调递增且没有删除过数据的，这样的话， ID 值是连续的，我们就能轻易的计算出第 5 页的数据的 ID 值是从 120 开始的。这样做的好处是无论翻页到多么靠后，它的性能都很好。
使用 WITH 优化 GROUP BY
我们通常使用 GROUP BY 做分组聚合查询，如果还要对分组后的结果再次求和，可以使用 WITH 操作，但是更好的办法还是将 WITH 的处理拿到应用层去做。
TABLE
如果我们删除了很多数据，或者在插入数据时，不是按照主键的递增顺序插入的，很可能会因此产生很多内存碎片，影响数据查询的效率。这是因为在删除数据时，MySQL 并不会立即将它们清除并整理空间，而是将它们标记为删除，通过 TABLE 可以将空间进行整理，减少内存碎片。
引擎并不支持 TABLE 操作，它会提示如下信息：
OPTIMIZE TABLE specific_table;-- Table does not support optimize, doing recreate + analyze instead我们可以通过不做任何操作的 ALTER 命令来重建表达到以上目的：
alter table specific_table engine=InnoDB;执行完成后，我们通过如下 SQL 查看执行情况，如果 列为 0，说明我们空间碎片整理成功
show table status from specific_db like specific_table;不过，多数情况下不需要执行该操作。
找到并修复损坏的表
可能因硬件问题、MySQL 本身的缺陷或者操作系统的问题导致索引损坏，当然这种问题非常少见，我们可以通过如下 SQL 来检查大多数表和索引的错误：
check table specific_table;如果发现异常的话，可以通过如下 SQL 进行修复：
repair table specific_table;-- 如果存储引擎不支持上述操作的话，也可通过表重建来完成alter table specific_table engine=InnoDB;参考资料:
[1]《高性能MySQL 第四版》：第七、八章
[2]《MySQL 是怎样运行的》：第七、十、十一、十四、十五章
[3]MySQL：
[4]8.9. Hints
[5] mysql进阶： table命令
限时特惠:本站持续每日更新海量各大内部创业课程，一年会员仅需要98元，全站资源免费下载
点击查看详情
站长微信:Jiucxh声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。
