性能测试优化必会技能-定位MySQL慢查询

引言

性能测试最重要的环节是性能优化。当响应时间比较长，或者tps上不去时可能因为有sql慢查询。

开户慢查询日志

mysql> set global slow_query_log = on;Query OK, 0 rows affected (0.01 sec)

设置慢查询时间限制（查询时间只要大于这个值都将记录到慢查询日志中，单位：秒）

mysql> show global variables like "slow_query_log_file";

确定慢查询日志路径

tail -n5 E:softmysql-5.7.23-winx64dataliangf-slow.log

确定慢查询日志文件名

tail -n5 E:softmysql-5.7.23-winx64dataliangf-slow.log

查看日志命令

tail -n5 E:softmysql-5.7.23-winx64dataliangf-slow.log

通过 show processlist定位慢查询

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

知识扩展：如果不使用 FULL 关键字，在 info 字段中只显示每个语句的前 100 个字符，如果想看语句的全部内容可以使用 full 修饰（show full processlist）。

这里对上面结果重点参数解释一下：

Time：表示执行时间

Info：表示 SQL 语句

我们这里可以通过它的执行时间（Time）来判断是否是慢 SQL。

EXLPAIN分析慢查询

分析 SQL 执行效率是优化 SQL 的重要手段，通过上面讲的两种方法，定位到慢查询语句后，我们就要开始分析 SQL 执行效率了，我们可以通过 explain、show profile 和 trace 等诊断工具来分析慢查询。

Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用方法：在查询语句前面加上 explain 运行就可以了。

创建一个测试表并且插入部分数据用于测试：

#create tableCREATE TABLE `test_table` (`id` int(11) NOT NULL AUTO_INCREMENT,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,`c` int(11) DEFAULT NULL,`d` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_a` (`a`),KEY `idx_b_c` (`b`,`c`)) ENGINE=InnoDB CHARSET=utf8mb4;# insert data procDELIMITER //CREATE PROCEDURE insert_test_data()BEGIN DECLARE i INT; SET i=1; WHILE(i<=160000) DO INSERT INTO test_table (a,b,c,d) VALUES (i,i,i,i); SET i=i+1;END WHILE;END //# exec procCALL insert_test_data();

我们创建了3个索引 : PRIMARY KEY (`id`), 聚集索引 KEY `idx_a` (`a`),非聚集索引 KEY `idx_b_c` (`b`,`c`)非聚集索引 d列没有创建索引

结语

很多时候没有数据库权限，也没有关系，可以找运维或开发来帮忙看一下。