MySQL慢查询以及解决方案详解-数据库

MySQL的慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句,下面这篇文章主要给大家介绍了关于MySQL慢查询以及解决方案的相关资料,需要的朋友可以参考下

一、前言

对于生产业务系统来说，慢查询也是一种故障和风险，一旦出现故障将会造成系统不可用影响到生产业务。当有大量慢查询并且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

IiBzcmM9

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

IiBzcmM9

3、慢查询时间设置

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

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

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

IiBzcmM9

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

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

4、慢查询路径

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

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

IiBzcmM9

三、慢查询日志分析

3.1 mysqldumpslow工具

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

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

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

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

IiBzcmM9

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对应的记录数据不多，所以其他字段使用不用索引基本无影响，同时也可以避免建立过多的索引带来的插入和更新的性能开销；