前言
SQL优化对于一枚程序员来说是至关重要的,并且大部分面试中,都会问道有关sql优化的一 些问题,这里将带着大家学会如何分析sql执行效率,首先要想优化一条sql语句,前提是我们要能够定位到查询慢的sql语句,并对其进行分析,找到慢查询的圆心,然后进行优化。
定位慢 SQL
大家在工作中测试的时候偶尔会碰到查询结果,超过一定时间才返回,这时我们就应该考虑是不是慢查询导致的,接下来让我们看看如果定位慢sql语句
一 通过慢查询日志
如果需要定位到慢查询,一般的方法是通过慢查询日志来查询的,MySQL 的慢查询日志它会记录在 MySQL 中响应时间超过参数 long_query_time(单位秒,默认值 10)设置的值并且扫描记录数不小于min_examined_row_limit(默认值位0)的语句,所以它会帮我们找出查询慢的sql语句。
默认情况下,慢查询日志中并不会记录管理语句,可通过设置参数 log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询的日志中。
默认情况下,也不会记录查询时间不超过 long_query_time 但是不使用索引的语句,可通过配置 log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中(即使查询时间没超过 long_query_time 配置的值)。
通常我们开启慢查询日志一般分为4给步骤:
第一步 开启慢查询日志
首先开启慢查询日志,由参数 slow_query_log 决定是否开启,在 MySQL 的命令行下输入下面的命令:
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)
第二步 设置慢查询阀值
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)
MySQL 中 long_query_time 的值如何确定呢?
线上业务一般建议把 long_query_time 设置为 1 秒,如果某个业务的 MySQL 要求比较高的 QPS,可设置慢查询为 0.1
秒。发现慢查询及时优化或者提醒开发改写。一般测试环境建议 long_query_time 设置的阀值比生产环境的小,比如生产环境是 1 秒,则测试环境建议配置成 0.5
秒。便于在测试环境及时发现一些效率低的 SQL。甚至某些重要业务测试环境 long_query_time 可以设置为
0,以便记录所有语句。并留意慢查询日志的输出,上线前的功能测试完成后,分析慢查询日志每类语句的输出,重点关注
Rows_examined(语句执行期间从存储引擎读取的行数),提前优化。
第三步 确定慢查询日志文件路径
慢查询日志的路径默认是 MySQL 的数据目录
mysql> show global variables like "datadir";
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /data/mysql/data/3306/ |
+---------------+------------------------+
1 row in set (0.00 sec)
第四步 确定慢查询日志的文件名
mysql> show global variables like "slow_query_log_file";
+---------------------+----------------+
| Variable_name