mysql>select @@version;+------------+| @@version |+------------+|5.6.37-log |+------------+
mysql>select @@have_profiling;+------------------+| @@have_profiling |+------------------+| YES |+------------------+
mysql>select @@profiling_history_size;+--------------------------+| @@profiling_history_size |+--------------------------+|15|+--------------------------+
mysql>select @@profiling;+-------------+| @@profiling |+-------------+|0|+-------------+
mysql> set profiling =1;
mysql>select @@profiling;+-------------+| @@profiling |+-------------+|1|+-------------+
mysql> show profiles;+----------+------------+---------------------------+| Query_ID | Duration | Query |+----------+------------+---------------------------+|1|0.03954925| SELECT @@profiling ||2|0.01086300| SELECT COUNT(1) FROM XXXX |+----------+------------+---------------------------+
mysql> show profile for query 2;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting |0.000032|| checking permissions |0.000007|| Opening tables |0.000012|| init |0.000009|| System lock |0.000009|| optimizing |0.000014|| statistics |0.000013|| preparing |0.000012|| executing |0.000008|| Sending data |0.010665|| end |0.000009|| query end |0.000008|| closing tables |0.000038|| freeing items |0.000016|| cleaning up |0.000012|+----------------------+----------+
SQL优化
优化分页查询
经常使用LIMIT M,N+ORDER BY来实现分页查询
在没有任何索引条件支持的情况下,需要做大量的文件排序操作(file sort),性能很差
即便有对应的索引,也只是在刚开始时效率比较理想,越往后,性能越差
使用LIMIT M,N时,偏移量M越大,数据库检索的数据也会越多
例如LIMIT 10000,10,数据库需要检索10010条记录,但最后只返回10条记录
优化方案:子查询 + 覆盖索引
-- 使用了索引,扫描了100010行
mysql> explain select* from prop_action_reward order by create_time limit 100000,10;+----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+| id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+|1| SIMPLE | prop_action_reward | index | NULL | idx_create_time |5| NULL |100010| NULL |+----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+-- 耗费了0.19S,性能不太理想
mysql>select* from prop_action_reward order by create_time limit 100000,10;....10 rows in set (0.19 sec)-- 查询获取到的100010条记录都返回给客户端了,耗时主要集中在Sending data阶段
mysql> show profile for query 21;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting |0.000037|| checking permissions |0.000007|| Opening tables |0.000016|| init |0.000028|| System lock |0.000008|| optimizing |0.000006|| statistics |0.000010|| preparing |0.000011|| Sorting result |0.000005|| executing |0.000004|| Sending data |0.192705|| end |0.000018|| query end |0.000008|| closing tables |0.000010|| freeing items |0.000029|| cleaning up |0.000085|+----------------------+----------+
-- 子查询用到了覆盖索引(Using index),无需回表
mysql> explain select* from prop_action_reward where id >(select id from prop_action_reward order by create_time limit 100000,1) limit 10;+----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+| id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+|1| PRIMARY | prop_action_reward |range| PRIMARY | PRIMARY |8| NULL |47244120| Using where ||2| SUBQUERY | prop_action_reward | index | NULL | idx_create_time |5| NULL |94488240| Using index |+----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+-- 耗费了0.03S,提升很大
mysql>select* from prop_action_reward where id >(select id from prop_action_reward order by create_time limit 100000,1) limit 10;...10 rows in set (0.03 sec)-- 只会返回10条记录给客户端,所以快很多
mysql> show profile for query 24;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting |0.000064|| checking permissions |0.000007|| checking permissions |0.000007|| Opening tables |0.000019|| init |0.000030|| System lock |0.000009|| optimizing |0.000008|| statistics |0.000022|| optimizing |0.000007|| statistics |0.000011|| preparing |0.000015|| Sorting result |0.000005|| executing |0.000004|| Sending data |0.028916|| preparing |0.000013|| executing |0.000005|| Sending data |0.000055|| end |0.000006|| query end |0.000007|| closing tables |0.000009|| freeing items |0.000022|| cleaning up |0.000013|+----------------------+----------+
mysql> show variables like '%slow_query%';+---------------------+-----------------------------------------------------+| Variable_name | Value |+---------------------+-----------------------------------------------------+| slow_query_log | ON || slow_query_log_file |/data_db3/mysql/3323/slowlog/slowlog_2019102209.log |+---------------------+-----------------------------------------------------+
mysql> show variables like 'long_query_time';+-----------------+----------+| Variable_name | Value |+-----------------+----------+| long_query_time |1.000000|+-----------------+----------+