MySQL慢查询以及解决方案详解

目录

• 一、前言
• 二、慢查询
• 2.1 什么是慢查询？
• 2.2 慢查询配置
• 1、慢查询日志
• 2、未使用索引是否开启日志
• 3、慢查询时间设置
• 4、慢查询路径
• 三、慢查询日志分析
• 3.1 mysqldumpslow工具
• 四、慢查询解决方案
• 4.1 索引失效
• 4.2 SQL语句优化
• 4.3 表结构优化
• 五、总结

一、前言

对于生产业务系统来说，慢查询也是一种故障和风险，一旦出现故障将会造成系统不可用影响到生产业务。当有大量慢查询并且SQL执行得越慢，消耗的CPU资源或IO资源也会越大，因此，要解决和避免这类故障，关注慢查询本身是关键。

二、慢查询

2.1 什么是慢查询？

慢查询，顾名思义，执行很慢的查询。当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个SQL语句就是需要优化的。慢查询被记录在慢查询日志里。慢查询日志默认是不开启的。如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

2.2 慢查询配置

以MySQL数据库为例，默认慢查询功能是关闭的，当慢查询开关打开后，并且执行的SQL语句达到参数设定的阈值后，就会触发慢查询功能打印出日志。

1、慢查询日志

查询是否开启慢查询日志：show variables like ‘slow_query_log’;

• 开启慢查询sql：set global slow_query_log = 1/on;
• 关闭慢查询sql：set global slow_query_log = 0/off;

如图所示已是开启状态 ON

2、未使用索引是否开启日志

查询未使用索引是否开启记录慢查询日志： show variables like ‘log_queries_not_using_indexes’;

• 开启记录未使用索引sql：set global log_queries_not_using_indexes=1/on
• 关闭记录未使用索引sql：set global log_queries_not_using_indexes=0/off

如图所示是关闭状态OFF

3、慢查询时间设置

查询超过多少秒的记录到慢查询日志中：show variables like ‘long_query_time’;

设置超X秒就记录慢查询sql：set global long_query_time= X;

如下图所示，设置的慢查询时间为0.3秒

注：上述这些参数设置都是在当前数据库生效，当MySQL重启后则会失效。

如果要永久生效，就必须修改配置文件my.cnf

4、慢查询路径

查询MySQL慢查询日志的路径：show variables like ‘slow_query_log_file%’;

如下为查询出的路径在：/apps/log/mysql/slow3306.log

三、慢查询日志分析

3.1 mysqldumpslow工具

以MySQL为例，一般使用mysqldumpslow工具分析慢查询日志，使用命令查询慢SQL语句。

–查询用时最多的10条慢：

sql mysqldumpslow -s t -t 10 -g "select" /data/mysql/data/dcbi-3306/log/slow.log

得到其中一条如下图所示的结果：

• Count：代表这个 SQL 语句执行了多少次
• Time：代表执行的时间，括号是累计时间
• Lock：表示锁定的时间，括号是累计时间
• Rows：表示返回的记录数，括号是累计记录数

有了这样清晰的慢查询日志分析之后，我们可以更加有针对性和更快捷的处理出现慢查询SQL语句的问题，直接找到对应程序位置优化代码从而避免慢查询出现。

四、慢查询解决方案

4.1 索引失效

之所以会出现慢查询，无疑是SQL语句的问题，一般都是扫描数据量过大、没有使用索引、索引失效等导致。如下是一些索引失效的情况：

使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

使用多列索引的查询语句

MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用，也就是左匹配原则。

4.2 SQL语句优化

1） 查询语句应该尽量避免全表扫描，首先应该考虑在Where子句以及OrderBy子句上建立索引，但是每一条SQL语句最多只会走一条索引，而建立过多的索引会带来插入和更新时的开销，同时对于区分度不大的字段，应该尽量避免建立索引，可以在查询语句前使用explain关键字，查看SQL语句的执行计划，判断该查询语句是否使用了索引；

2）应尽量使用EXIST和NOT EXIST代替 IN和NOT IN，因为后者很有可能导致全表扫描放弃使用索引；

3）应尽量避免在Where子句中对字段进行NULL判断，因为NULL判断会导致全表扫描；

4）应尽量避免在Where子句中使用or作为连接条件，因为同样会导致全表扫描；

5）应尽量避免在Where子句中使用！=或者<>操作符，同样会导致全表扫描；

6）使用like “%abc%” 或者like “%abc” 同样也会导致全表扫描，而like “abc%”会使用索引。

7）在使用Union操作符时，应该考虑是否可以使用Union ALL来代替，因为Union操作符在进行结果合并时，会对产生的结果进行排序运算，删除重复记录，对于没有该需求的应用应使用Union ALL，后者仅仅只是将结果合并返回，能大幅度提高性能；

8）应尽量避免在Where子句中使用表达式操作符，因为会导致全表扫描；

9）应尽量避免在Where子句中对字段使用函数，因为同样会导致全表扫描

10）Select语句中尽量 避免使用“*”，因为在SQL语句在解析的过程中，会将“”转换成所有列的列名，而这个工作是通过查询数据字典完成的，有一定的开销；

11）Where子句中，表连接条件应该写在其他条件之前，因为Where子句的解析是从后向前的，所以尽量把能够过滤到多数记录的限制条件放在Where子句的末尾；

12）若数据库表上存在诸如index(a,b,c)之类的联合索引，则Where子句中条件字段的出现顺序应该与索引字段的出现顺序一致，否则将无法使用该联合索引；

13）From子句中表的出现顺序同样会对SQL语句的执行性能造成影响，From子句在解析时是从后向前的，即写在末尾的表将被优先处理，应该选择记录较少的表作为基表放在后面，同时如果出现3个及3个以上的表连接查询时，应该将交叉表作为基表；

14）尽量使用>=操作符代替>操作符，例如，如下SQL语句，select dbInstanceIdentifier from DBInstance where id > 3，该语句应该替换成 select dbInstanceIdentifier from DBInstance where id >=4 ，两个语句的执行结果是一样的，但是性能却不同，后者更加 高效，因为前者在执行时，首先会去找等于3的记录，然后向前扫描，而后者直接定位到等于4的记录。

4.3 表结构优化

这里主要指如何正确的建立索引，因为不合理的索引会导致查询全表扫描，同时过多的索引会带来插入和更新的性能开销；

1）首先要明确每一条SQL语句最多只可能使用一个索引，如果出现多个可以使用的索引，系统会根据执行代价，选择一个索引执行；

2）对于Innodb表，虽然如果用户不指定主键，系统会自动生成一个主键列，但是自动产生的主键列有多个问题1. 性能不足，无法使用cache读取；2. 并发不足，系统所有无主键表，共用一个全局的Auto_Increment列。因此，InnoDB的所有表，在建表同时必须指定主键。

3）对于区分度不大的字段，不要建立索引；

4）一个字段只需建一种索引即可，无需建立了唯一索引，又建立INDEX索引。

5）对于大的文本字段或者BLOB字段，不要建立索引；

6）连接查询的连接字段应该建立索引；

7）排序字段一般要建立索引；

8）分组统计字段一般要建立索引；

9）正确使用联合索引，联合索引的第一个字段是可以被单独使用的，例如有如下联合索引index(userID,dbInstanceID),一下查询语句是可以使用该索引的，select dbInstanceIdentifier from DBInstance where userID=? ，但是语句select dbInstanceIdentifier from DBInstance where dbInstanceID=?就不可以使用该索引；

10）索引一般用于记录比较多的表，假如有表DBInstance，所有查询都有userID条件字段，目前已知该字段已经能够很好的区分记录，即每一个userID下记录数量不多，所以该表只需在userID上建立一个索引即可，即使有使用其他条件字段，由于每一个userID对应的记录数据不多，所以其他字段使用不用索引基本无影响，同时也可以避免建立过多的索引带来的插入和更新的性能开销；

五、总结

在日常写SQL和写程序的时候多关注基本的SQL语句，在业务复杂的系统中，除了上述基本的点外，尽管使用了索引，也还需要从业务本身出发，如：当查询的数量过大时，时间索引已经不满足了，可以改为分批次来查询控制数量等。

参考文章地址：

到此这篇关于MySQL慢查询以及解决方案详解的文章就介绍到这了,更多相关MySQL慢查询解决内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！