MySQL慢查询优化

前言

应用系统性能测试过程中，性能优化是绕不开的话题，对测试人员而言，性能优化的第一站就是SQL语句的优化与分析。因此本文主要以MySQL数据库为例，介绍常见的慢查询SQL语句执行效率分析与优化方法和简单示例，为致力于应用系统性能优化的从业人员提供一定参考和借鉴。

1慢查询定位

(1)慢查询

慢查询SQL语句，即在数据库执行耗时超过一定阈值的SQL语句，常见阈值为500~2000ms，可根据业务需求适当调整。如存在大量慢查询语句会直接导致系统响应时间变长，降低用户体验感，因此慢查询的定位与优化是SQL语句优化的主要内容。

慢查询调优的第一步是准确定位慢查询语句，需要数据库开启慢查询日志记录功能，然后借助工具对日志进行分析实现慢查询SQL语句的准确定位。

--慢查询开启状态、日志位置

show variables like`slow_query%`;

--慢查询命中时长

show variables like`long_query_time`;

(2)mysqldumpslow慢查询日志分析

MySql数据库的慢查询SQL语句，可以借助mysqldumpslow工具进行分析；其他类型数据库，可根据官方提供的技术文档采用对应的工具开展慢查询日志分析。

慢查询日志分析的常用参数说明如下：

例：用时最多的10条慢SQL(后半部分为slow_query_log_file地址)

sql mysqldumpslow-s t-t 10-g‘ｓｅｌｅｃｔ‘/data/mysql/data/dcbi-3306/log/slow.log

2 SQL语句执行分析

(1)SQL执行顺序

分析SQL语句执行效率的第一步，需要了解一条SQL语句的执行顺序，从而为语句优化提供依据。一般而言，执行顺序为：

from->where->group by->聚合函数(sum、avg)->having->计算公式->ｓｅｌｅｃｔ字段->order by->limit

(2)explain关键字

SQL语句执行分析可通过在SQL语句前添加“explain”关键字后，在数据库编辑器中执行查看语句具体的执行情况。

explain ｓｅｌｅｃｔ*from table_name where columns_1=value_1 and columns_2=vales_2

(3)SQL执行计划返回结果说明

返回结果各列说明可按需查询相关资料，重点关注【type】、【ref】、【extra】反映查询效率的3列，以【type】为主即可。

(4)SQL执行效率分析

explain语句根据【type】列的值判断SQL执行效率，效率从低到高依次为：

all