MySQL 性能优化：SQL 慢查询优化，索引优化，表优化
1. MySQL 优化概述
MySQL 优化是一个综合性的技术，在优化上存在着一个调优金字塔的说法，如下：
很明显从图上可以看出，越往上走，难度越来越高，收益却是越来越小的。比如硬件和 OS 调优，需要对硬件和 OS 有着非常深刻的了解，仅仅就磁盘一项来说，一般非 DBA 能想到的调整就是 SSD 盘比用机械硬盘更好，但其实它至少包括 了，使用什么样的磁盘阵列（RAID）级别、是否可以分散磁盘 IO、是否使用裸设备存放数据，使用哪种文件系统（目前比较推荐的是 XFS），操作系统的磁盘调度算法（目前比较推荐 deadline，对机械硬盘和 SSD 都比较合适。从内核 2.5 开始，默认的 IO 调度算法是 Deadline，之后默认 IO 调度算法为 Anticipatory，直到内核 2.6.17 为止，从内核 2.6.18 开始，CFQ 成为默认的 IO 调度算法，但 CFQ 并不推荐作为数据库服务器的磁盘调度算法。）选择，是否需要调整操作系统文件管理方面比如 atime 属性等等。
所以在进行优化时，首先需要关注和优化的应该是架构，如果架构不合理，即使是 DBA 能做的事情其实是也是比较有限的。
对于架构调优，在系统设计时首先需要充分考虑业务的实际情况，是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做；然后考虑写的并发量有多大，是否需要采用分布式；最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失，是否需要采用 Galera 或者 MGR。
对于 MySQL 调优，需要确认业务表结构设计是否合理，SQL 语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引，数据库的参数优化是否足够。
最后确定系统、硬件有哪些地方需要优化，系统瓶颈在哪里，哪些系统参数需要调整优化，进程资源限制是否提到足够高；在硬件方面是否需要更换为具有更高 IO 性能的存储硬件，是否需要升级内存、CPU、网络等。如果在设计之初架构就不合理，比如没有进行读写分离，那么后期的 MySQL 和硬件、系统优化的成本就会很高，并且还不一定能最终解决问题。如果业务性能的瓶颈是由于索引等 MySQL 层的优化不够导致的，那么即使配置再高性能的 IO 存储硬件或者 CPU 也无法支撑业务的全表扫描。
以上部分内容选自：《千金良方：MySQL 性能优化金字塔法则》
因此，MySQL 数据库常见的两个瓶颈是：CPU 和 IO 的瓶颈。
CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。
磁盘 IO 瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么瓶颈就会出现在网络上。
除去硬件和架构选择方面，归根结底调优方式还是对 MySQL 本身调优，一般为以下几种：
索引的优化（索引的设计和使用）
SQL 优化（慢查询）
表优化（分库分表，读写分离）
而对于前面两种在之前的文章中已经讲过了，因此本文主要是分析索引的优化，SQL 语句的优化，表的优化。
2. SQL 优化
SQL 优化一般是指对执行的 SQL 语句进行优化，比如为什么这条 SQL 查询语句执行缓慢且低效，这就是所谓的慢查询。
2.1 什么是慢查询
慢查询一般指慢查询日志，顾名思义，就是查询花费大量时间的日志，是指 MySQL 记录所有执行超过 long_query_time 参数设定的时间阈值的 SQL 语句的时候，就会被记录到叫slow_query_log_file的日志中。
该日志能为 SQL 语句的优化带来很好的帮助。
2.2 为什么存在慢查询
查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，一般通过下面两个步骤来分析总是很有效：
确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列；
确认 MySQL 服务器层是否在分析大量超过需要的数据行。
有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给 MySQL 服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的 CPU 和内存资源。
1️⃣ 查询不需要的记录
对于页面展示的数据，比如新闻条数页面只展示了 10 条，但实际上可能查出了全部数据，对于订单列表数据，可能把订单详情表的数据也带着查出来了。
2️⃣ 总是取出全部列
每次看到 SELECT *的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的 I/O、内存和 CPU 的消耗。
3️⃣ 重复查询相同的数据
不断地重复执行相同的查询，然后每次都返回完全相同的数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。
2.3 慢查询配置
1、slow_query_log
默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，需要开启慢查询日志功能。
开启慢查询：
注：开启慢查询日志对 MySQL 性能会有一定影响，一般情况只是在 SQL 调优阶段开启慢查询日志。
2、long_query_time
所谓慢查询，多慢为慢呢？MySQL 中可以设定一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询日志中。
long_query_time 参数就是这个阈值。默认值为 10，代表 10 秒。
对于这个时间，一般来说不会超过，因此这里为了演示效果把阈值改为 0。
注：设置之后需要关闭数据库连接，再重新连接再次查询即可。
3、log_queries_not_using_indexes
这个参数设置为 ON，可以捕获到所有未使用索引的 SQL 语句，尽管这个 SQL 语句有可能执行得挺快。
4、slow_query_log_file
指定慢查询日志得存储路径及文件（默认和数据文件放一起）。
2.4 慢查询日志
打开慢查询日志，我这里是 windos 下的 MySQL，linux 通过上面的一样也是可以查看的。
选出其中一条：
Time，查询执行时间User@Host: root[root] @ localhost [::1] Id: 59，用户名 、用户的 IP 信息、线程 ID 号Query_time，执行花费的时长，单位：毫秒Lock_time，执行获得锁的时长Rows_sent，获得的结果行数Rows_examined，扫描的数据行数SET timestamp，SQL 执行的具体时间
尽管 MySQL 提供了日志文件给我们查看，但实际上慢查询的日志记录非常多，要从里面找寻一条查询慢的日志并不是很容易的事情，一般来说都需要一些工具辅助才能快速定位到需要优化的 SQL 语句。因此 MySQL 提供了日志分析工具**mysqldumpslow，**帮助使用者自动化分析慢查询日志，将分析结果按照参数所指定的顺序输出。
2.5 mysqldumpslow
常见参数：
r：返回记录
t： 查询时间
c： 访问次数
-t：即为返回前面多少条的数据
-s： 是表示按照何种方式排序
-g：后边搭配一个正则匹配模式，大小写不敏感的
通常，命令可以结合 | 和 less、more 等其他命令使用，便于参看。
常见使用：
获取访问次数最多的前 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log | less获取返回记录集最多前 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log | less获取按照时间排序的前 10 条含有左连接的 SQL
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xxx-slow.log | more
2.6 Explain 执行计划
有了慢查询语句后，就要对语句进行分析。一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。
EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划，我们需要搞懂执行计划的各个输出项都是干嘛 使的，从而可以有针对性的提升我们查询语句的性能。
通过使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析查询语句或是表结构的性能瓶颈，总的来说通过 EXPLAIN 我们可以知道：
表的读取顺序；
数据读取操作的操作类型；
哪些索引可以使用；
哪些索引被实际使用；
表之间的引用；
每张表有多少行被优化器查询。
2.6.1 语法
执行计划的语法其实非常简单： 在 SQL 查询的前面加上 EXPLAIN 关键字就行。比如：
当然，除了 SELECT 开头的查询语句，其余的 DELETE、INSERT、REPLACE 以及 UPOATE 语句前边都可以加上 EXPLAIN，用来查看这些语句的执行计划，只不过一般的应用系统，读写比例在 10：1 左右，而且插入操作和一般的更新操作很少出现性能问题，所以这里只分析 SELECT 语句。
当我们执行上面的语句之后，我们可以看到：
它显示了 12 个关键字，它们分表表示：
id： 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id；select_type：SELECT 关键字对应的那个查询的类型；table：表名 partitions，匹配的分区信息；type：针对单表的访问方法；possible_keys：可能用到的索引；key：实际上使用的索引；key_len：实际使用到的索引长度 ；ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息 ；rows：预估的需要读取的记录条数 ；filtered：某个表经过搜索条件过滤后剩余记录条数的百分比 ；Extra：—些额外的信息 。
2.6.2 关键字解析
首先看这样一张表：
这是之前自己博客网站的一张文章表，对于里面的索引主要是为了演示一下关键字使用。
1、table
不论我们的查询语句有多复杂，里边包含了多少个表，到最后也是需要对每个表进行单表访问的，MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名。
单表查询
连表查询
2、id
查询语句中每出现一个 SELECT 关键字，MySQL 就会为它分配一个唯一的 id 值。
我们知道我们写的查询语句一般都以 SELECT 关键字开头，比较简单的查询语句里只有一个 SELECT 关键字，稍微复杂一点的连接查询中也只有一个 SELECT 关键字，比如上面的：
但是下边两种情况下在一条查询语句中会出现多个 SELECT 关键字：
查询中包含子查询的情况
查询中包含 UNION 语句的情况
如果 explain 中的有多个 id 对应的数据项，则倒叙进行执行：
数字越大的，越先执行分析
数字编号相同，从上到下进行分析
这里就不具体演示了，感兴趣的可自行操作。
3、select_type
通过上边的内容我们知道，一条大的查询语句里边可以包含若干个 SELECT 关键字，每个 SELECT 关键字代表着一个小的查询语句，而每个 SELECT 关键字的 From 子句中都可以包含若干张表（这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的。
MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。
SIMPLE：简单的 select 查询，不使用 union 及子查询；PRIMARY：最外层的 select 查询；UNION：UNION 中的第二个或随后的 select 查询，不 依赖于外部查询的结果集；UNION RESULT：UNION 结果集；SUBQUERY：子查询中的第一个 select 查询，不依赖于外部查询的结果集；DEPENDENT UNION：UNION 中的第二个或随后的 select 查询，依赖于外部查询的结果集；DEPENDENT SUBQUERY：子查询中的第一个 select 查询，依赖于外部查询的结果集；DERIVED： 用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询，把结果放在临时表里；MATERIALIZED：物化子查询，即子查询的结果通常缓存在内存或临时表中；UNCACHEABLE SUBQUERY：结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估，出现极少。UNCACHEABLE UNION：UNION 中的第二个或随后的 select 查询，属于不可缓存的子查询，出现极少。
4、partitions
和分区表有关，一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL。
*5、type
执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法/访问类型，其中的 type 列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：
出现比较多的是：
一般来说，得保证查询至少达到 range 级别，最好能达到 ref。
system当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，system 是 const 类型的特例。
const就是当我们根据主键或者唯一级索引列与常数进行等值匹配时，对单表的访问方法就是 const。因为只匹配一行数据，所以很快。
eq_ref在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。
PS：驱动表与被驱动表
A 表和 B 表 join 连接查询，如果通过 A 表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到 B 表中查询数据，然后合并结果。那么我们称 A 表为驱动表，B 表为被驱动表。
ref当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref。
ref_or_null有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为 NULL 的记录也找出来。
index_merge一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用索引合并的方式来执行查询。
unique_subquery该类型和 eq_ref 类似，但是使用了 IN 查询，且子查询是主键或者唯一索引。
index_subquery和 unique_subquery 类似，只是子查询使用的是非唯一索引。
range如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法，一般就是在你的 where 语句中出现了 >、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN() 等的查询。
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。
ALL全表扫描，遍历全表以找到匹配的行，性能最差。
6、possible_keys
可能用到的索引，展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。
7、key
MySQL 实际选择的索引。
8、key_len
表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度。
9、ref
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery 其中之一时，ref 列展示的就是与索引列作等值匹配的是谁。
10、rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。因此，该数值越小越好。
11、filtered
查询优化器预测有多少条记录满⾜其余的搜索条件。
12、Extra
顾名思义，Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。MySQL 提供的额外信息很多，几十个，无法一一介绍，挑一些平时常见的或者比较重要的额外信息。
Impossible WHERE查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息。
No matching min/max row当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息。
Using index当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在 Extra 列将会提示该额外信息。比方说下边这个查询中只需要用到 create_time 而不需要回表操作。
Using index condition有的时候尽管 where 条件中使用了索引列，但实际上并没有完全用到索引，如下：
其中对于title >'z'是可以用到索引的，但是 title LIKE '%a'却无法使用到索引，在 MySQL 5.6 以前，是按照下边步骤来执行这个查询的：
先根据
title >'z'这个条件，从二级索引idx_title中获取到对应的二级索引记录。根据上一步骤得到的二级索引记录中的主键值进行回表（因为是 select *），找到完整的用户记录再检测该记录是否符合
title LIKE '%a'这个条件，将符合条件的记录加入到最后的结果集。
索引下推
索引下推（index condition pushdown ）简称 ICP，在 MySQL 5.6 的版本上推出，用于优化查询。
还是上面的查询，步骤如下：
先根据
title >'z'这个条件，从二级索引idx_title中获取到对应的二级索引记录。对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足
title LIKE '%a'这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。对于满足
title LIKE '%a'这个条件的二级索引记录执行回表操作。
如果在查询语句的执行过程中将要使用索引条件下推这个特性，在 Extra 列中将会显示 Using index condition。
使用场景：
非聚簇索引，聚簇索引已经有整行数据，不需要回表；
减少回表次数，特别对于 LIKE 通配符来说，减少效果明显。
Using where当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。
当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述信息。
需要注意的是，出现了 Using where，只是表示 MySQL 根据 where 条件进行了过滤，和是否全表扫描或读取了索引文件没有关系，网上有不少文章把 Using where 和是否读取索引进行关联，是不正确的，也有文章把 Using where 和回表进行了关联，这也是不对的。
除了上面的一些额外参数，还有其他的就不一一列举了，感兴趣的可以自行搜索查看。
3. 索引优化
可以看到，对于 SQL 的调优大部分情况下都是基于索引的优化，所以创建良好的索引是非常重要的，对于索引的创建策略，在之前的文章中也讲过：MySQL中的索引。
在使用过程中遵循以下原则：
1、不在索引列上做任何操作
比如在索引列上进行计算，在索引列上使用 MySQL 的函数。
2、尽量全值匹配
对于联合索引列，如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配。
3、最佳左前缀法则
对于联合索引列，如果不能满足全值匹配，尽量遵循最佳左前缀法则。
4、范围条件放最后
对于联合索引列，创建索引时是按照索引的顺序进行分组排序的，按照最左原则，如果存在最左边的列是精确查找，它是能使用到索引的。
5、覆盖索引尽量用
覆盖索引之前就已经介绍过，可以减少回表次数。
6、不等于要慎用
MySQL 在使用不等于（!= 或者<>）的时候无法使用索引会导致全表扫描 。
7、Null/Not 有影响
is not null 容易导致索引失效，is null 则会区分被检索的列是否为 null，如果是 null 则会走 ref 类型的索引访问，如果不为 null，也是全表扫描。
所以一般不要在允许 NULL 的例设置索引。
8、LIKE 查询要当心
对于 LIKE 语句，以 % 或者-开头的不会使用索引，以%结尾会使用索引，但你可以通过覆盖索引来解决这个问题。
9、字符类型加引号
对于字符串类型，不加单引号会导致索引失效。
当然，这种情况没有十年脑血栓也写不出来。
10、ASC 和 DESC 别混用
对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是 ASC 规则排序，要么都是 DESC 规则排序。
11、优化 LIMIT
在系统中需要进行分页操作的时候，我们通常会使用 LIMIT 加上偏移量的办法实现。
一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如：
这样的查询，这时 MySQL 需要查询 10010 条记录然后只返回最后 10 条，前面 10000 条记录都将被抛弃，这样的代价非常高。
优化此类分页查询的一个最简单的办法如下：
它会先查询翻页中需要的 N 条数据的主键值，然后根据主键值回表查询所需要的 N 条数据，在此过程中查询 N 条数据的主键 id 在索引中完成，所以效率会高一些。
4. 表优化
对于数据库表的优化，除了在涉及表的时候建立合适的索引，数据类型，在数据量大的时候可以对表进行拆分，主要就是垂直拆分和水平拆分（分库分表，读写分离）。
水平切分：将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询， 提高效率。
垂直切分：将表中大字段单独拆分到另外一张表，形成一对一的关系。
5. 总结
总之，MysSQL 的优化主要就在于：索引的优化，SQL 语句的优化，表的优化，并且对于一些变化比较少的数据，我们可以借助缓存，如 Redis 等，对于索引的优化，最好能了解其索引的数据结构，也能大大提高我们的优化能力。
版权声明: 本文为 InfoQ 作者【Ayue、】的原创文章。
原文链接:【http://xie.infoq.cn/article/91c45f38df8b585a3254cb50f】。文章转载请联系作者。
🏆 InfoQ写作平台-签约作者 🏆 2019.10.16 加入
个人站点：javatv.net | 学习知识，目光坚毅
评论