数据库优化——慢查询MySQL定位优化流程

继前篇博文看了这么多，终于理解了MySQL索引之后，给大家再来一篇日常开发需要用到的优化技巧。

1.如何定位并优化慢查询SQL？

一般有3个思考方向

第一种：SQL优化

1. 我们可以通过慢查询日志和慢查询日志分析工具得到有问题的SQL列表。检查所查字段是否都是必须的，是否查询了过多字段，查出了多余字段。
2. 针对慢SQL，我们可以使用关键字explain来查看当前sql的执行计划.可以重点关注type key rows filterd等字段，从而定位该SQL执行慢的根本原因。再有的放矢的进行优化
3. ShowProfile是MySQL提供的可以用来分析当前会话中，SQL语句资源消耗情况的工具，可用于SQL调优的测量。在当前会话中.默认情况下处于showprofile 是关闭状态，打开之后保存最近15次的运行结果
4. 针对运行慢的SQL，通过profile工具进行详细分析.可以得到SQL执行过程中所有的资源开销情况.如IO开销,CPU开销,内存开销等.

第二种：MySQL 程序配置优化

MySQL是一个经过互联网大厂验证过的生产级别的成熟数据库，对于Mysql数
据库本身的优化，一般是通过Mysql中的配置文件my.cnf来完成的，比如。

Mysql5.7版本默认的最大连接数是151个，这个值可以在my.cnf中修改。
binlog日志，默认是不开启
缓存池bufferpoll的默认大小配置等。

由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置
官方只会提供一个默认值，具体情况还得由使用者来修改。
关于配置项的修改，需要关注两个方面。

• 配置的作用域，分为会话级别和全局
• 是否支持热加载
  因此，针对这两个点，我们需要注意的是：
• 全局参数的设定对于已经存在的会话无法生效
• 会话参数的设定随着会话的销毁而失效
• 全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

第三种：架构设计层面的优化
MySQL是一个磁盘IO访问量非常频繁的关系型数据库，在高并发和高性能的场景中.MySQL数据库必然会承受巨大的并发压力，而此时，我们的优化方式可以分为几个部分

• 搭建MySQL主从集群，单个服务容易单点故障，一旦服务器宕机，将会导致依赖Mysql数据库的应用全部无法响应。主从集群或者主主集群可以保证服务的高可用性。
• 读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能影响
• 引入分库分表机制，通过分库可以降低单个服务器节点的IO压力，通过分表
  的方式可以降低单表数据量，从而提升sql查询的效率。
• 针对热点数据，可以引入更为高效的分布式数据库，比如Redis、MongoDB
  等，他们可以很好的缓解Mysql的访问压力，同时还能提升数据检索性能。

第四种：硬件和操作系统层面的优化

• 从硬件层面来说，影响MySQL性能的因素有，CPU，可用内存大小，磁盘读写速度，网络带宽。
• 从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到Mysql 性能。
• 这部分的优化一般由DBA或者运维工程师去完成。
• 在硬件基础资源的优化中，我们重点应该关注服务本身承载的体量，然后提出合理的指标要求，避免出现资源浪费！

这里我们注意介绍第一种：索引优化。

2.如何使用慢查询日志？

先给出步骤，后面再详细说明，有3个步骤

1. 开启慢查询日志

首先开启慢查询日志，由参数slow_query_log决定是否开启，在MySQL命令行下输入下面的命令：

set global slow_query_log=on;

默认环境下，慢查询日志是关闭的，所以这里要开启。

2. 设置慢查询阈值

set global long_query_time=1;

只要你的SQL实际执行时间超过了这个阈值，就会被记录到慢查询日志里面。这个阈值默认是10s，线上业务一般建议把long_query_time设置为1s，如果某个业务的MySQL要求比较高的QPS，可设置慢查询为0.1s。发现慢查询及时优化或者提醒开发改写。一般测试环境建议long_query_time设置的阀值比生产环境的小，比如生产环境是1s，则测试环境建议配置成0.5s。便于在测试环境及时发现一些效率的SQL。

甚至某些重要业务测试环境long_query_time可以设置为0，以便记录所有语句。并留意慢查询日志的输出，上线前的功能测试完成后，分析慢查询日志每类语句的输出，重点关注Rows_examined（语句执行期间从存储引擎读取的行数），提前优化。

3. 确定慢查询日志的文件名和路径

show global variables like 'slow_query_log_file'

结果会发现慢日志默认路径就是MySQL的数据目录，我们可以来看一下MySQL数据目录

不用关注这里为什么不是MySQL 8.0，这和版本没什么关系的。

来，直接上菜，干巴巴的定义我自己都看不下去

我们先来查看一下变量，我框出了需要注意的点

查询带有query的相关变量

show global variables like '%query%';

查看有多少条慢sql

show status  like '%slow_queries%';

这里设置慢查询阈值为1s

set global long_query_time=1;

可以看到已经修改过来了

但是重启MySQL客户端设置和统计慢查询日志条数就会清零，即所有配置修改会还原。

命令修改配置之后，在命令行net stop mysql关闭MySQL服务，再net start mysql开启mysql服务，接着执行show global variables like '%quer%';会发现配置还原了。

在配置文件修改才能永久改变，否则重启数据库就还原了

3.慢查询例子演示，新手都能看懂

数据表结构，偷懒没写comment

CREATE TABLE `person_info_large` (  
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  
    `account` VARCHAR (10),   
    `name` VARCHAR (20),  
    `area` VARCHAR (20),  
    `title` VARCHAR (20), 
    `motto` VARCHAR (50),
    PRIMARY KEY (`id`),  
    UNIQUE(`account`),
    KEY `index_area_title`(`area`,`title`) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  

这里的数据是200W条。请注意表结构，记住哪几个字段有索引即可，后续围绕这个表进行分析。

这个3.36s并不是实际执行时间，实际执行时间得去慢查询日志去看Query_time参数

可以看到Query_time: 6.337729s，超过了1s，所以会被记录，一个select语句查询这么久，简直无法忍受。

图中其他的参数解释如下：

• Time ：慢查询发生的时间
• Query_time ：查询时间
• Lock_time : 等待锁表的时间
• Rows_sent :语句返回的行数
• Rows_examined: 语句执行期间从存储引擎读取的行数

上面这种方式是用系统自带的慢查询日志查看的，如果觉得系统自带的慢查询日志不方便查看，可以使用pt-query-digest或者mysqldumpslow等工具对慢查询日志进行分析。

注意：有的慢查询正在执行，结果已经导致数据库负载过高，而由于慢查询没有指向完，因此慢查询日志看不到任何语句，此时可以使用show processlist命令查看正在执行的慢查询，show processlist显示那些现场正在运行，如果有PROCESS权限，则可以看到所有线程。否则，只能看到当前会话线程。

4.查询语句慢怎么办？explain带你分析sql执行计划

根据上一节的表结构可以知道，account是添加了唯一索引的字段。explain分析一下执行计划。

 我们重点需要关注select_type、type、possible_keys、key、Extra这些列，我们来一一说明，

4.1 id

是一个有顺序的编号，是查询的顺序号，有几个select就显示几行，id的顺序是按照select出现的顺序增长的，id列的值越大执行优先级越高，就越先执行，id列的值相同则从上而下执行，id列的值为NULL最后执行。

4.2 select_type

select_type列：表示查询中每个select子句的类型，这里是SIMPLE简单查询，其他值下面给大家列出。

4.3 table

table：表示该语句查询的表。

4.4 type

type列，这里是index，表示全索引扫描。

type：优化sql的重要手段，也是我们判断sql性能和优化程度重要指标，它的取值范围：

表格从上到下代表了sql查询性能从最优到最差，如果是type类型是all，说明sql语句需要优化。

注意：如果type = NULL，则表明MySQL不用访问表或者索引，直接就能得到结果，比如explain select sum(1+2);

4.5 possible_keys

possible_keys：它表示MySQl在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。

4.6 key

key表示实际用到的索引列，以实际用到的索引列为准，这是查询优化器优化过后选择的，然后我们也可以根据实际情况强制使用我们自己的索引列来查询。它是possible_keys的子集。

4.7 key_len

key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标。

4.8 rows

rows：mysql查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大。

4.9 filtered

filtered：返回结果的行占需要读到的行（rows列的值）的百分比，就是百分比越高，说明需要查询到数据越准确，百分比越小，说明查询到的数据量大，而结果集很少。

4.10 Extra

Extra列，这里是Using index


一定要注意，Extra中出现Using filesort、Using temporary代表MySQL根本不能使用索引，效率会受到严重影响，应当尽可能的去优化。

出现Using filesort说明MySQL对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容，有索引就维护了B+树，数据本来就已经排好序了，这说明根本没有用到索引，而是数据读完之后再排序，可能在内存或者磁盘上排序。也有人将MySQL中无法利用索引的排序操作称为“文件排序”。

出现Using temporary表示MySQL在对查询结果排序时使用临时表，常见于order by和分组查询group by

回到上一个话题，我们看到account是添加了唯一索引的字段。explain分析了执行计划后

直接按照account降序来查

查看慢查询日志发现，使用索引之后，查询200W条数据的速度快了2s

接着我们分析一下查询name的sql执行计划

然后给name字段加上索引

加上索引之后，继续看看查询name的sql执行计划

对比一下前面name不加索引时的执行计划就会发现，加了索引后，type由ALL全表扫描变成index索引扫描。order by并没有 using filesort，而是using index，这里B+树已经将这个非聚集索引的索引字段的值排好序了，而不是等到查询的时候再去排序。

接着我们继续执行查询语句，此时name已经是添加了索引的。

结果发现，name添加索引之前，降序查询name是花费6.337729s，添加索引之后，降序查询name花费了3.479827s，原因就是B+树的结果集已经是有序的了。

5.当主键索引、唯一索引、普通索引都存在，查询优化器如何选择？

查询一下数据的条数，这里count(id)，分析一下sql执行计划

这里实际使用的索引是account唯一索引。

分析一下：实际使用哪个索引是查询优化器决定的，B+树的叶子结点就是链表结构，遍历链表就可以统计数量，但是这张表，有主键索引、唯一索引、普通索引，优化器选择了account这个唯一索引，这肯定不会使用主键索引，因为主键索引是聚集索引，每个叶子包含具体的一个行记录（很多列的数据都在里面），而非聚集索引每个叶子只包含下一个主键索引的指针，很显然叶子结点包含的数据是越少越好，查询优化器就不会选择主键索引。

当然，也可以强制使用主键索引，然后分析sql执行计划

我们看一下优化器默认使用唯一索引大致执行时间676ms

强制使用主键索引大致执行时间779ms

我们可以用force index强制指定索引，然后去分析执行计划看看哪个索引是更好的，因为查询优化器选择索引不一定是百分百准确的，具体情况可以根据实际场景分析来确定是否使用查询优化器选择的索引。

总结

最后我在给各位总结一下常见的SQL优化规则：

1. lSQL的查询一定要基于索引来进行数据扫描
2. 避免索引列上使用函数或者运算,这样会导致索引失效
3. where字句中like%号,尽量放置在右边
4. 使用索引扫描,联合索引中的列从左往右,命中越多越好.
5. 尽可能使用SQL语句用到的索引完成排序,避免使用文件排序的方式
6. 查询有效的列信息即可.少用*代替列信息
7. 永远用小结果集驱动大结果集。