环境说明:
版本
' Ver 8.42 Distrib 5.5.27, for Linux on x86_64
' Ver 8.42 Distrib 5.5.27, for Linux on x86_64
Table 为 innodb 引擎
1.原始sql的执行计划
(这条sql导致了mysql的性能问题,当它并发多条执行的时候影响其它正常sql的执行,大量sending data以及copying to tmp table等待)
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show profiles;
Empty set (0.00 sec)
mysql> explain
-> SELECT a.tid, a.subject,a.dateline,a.fid,b.message
-> FROM forum_thread AS a
-> LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
-> WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
-> ORDER BY a.dateline DESC
-> LIMIT 0,5;
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
| 1 | SIMPLE | b | ref | invisible,displayorder,first | invisible | 1 | const | 97042 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY,displayorder,typeid | PRIMARY | 8 | forum.b.tid | 1 | Using where |
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
2 rows in set (1.29 sec)
mysql> SELECT a.tid, a.subject,a.dateline,a.fid,b.message
-> FROM forum_thread AS a
-> LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
-> WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
-> ORDER BY a.dateline DESC
-> LIMIT 0,5;
执行这条SQL后,总耗时为 52s 查看具体步骤的耗时
mysql> show profiles;
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 1.28875000 | explain
SELECT a.tid, a.subject,a.dateline,a.fid,b.message
FROM forum_thread AS a
LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
ORDER BY a.dateline DESC
LIMIT 0,5 |
| 2 | 52.027014 | SELECT a.tid, a.subject,a.dateline,a.fid,b.message
FROM forum_thread AS a
LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
ORDER BY a.dateline DESC
LIMIT 0,5 | |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show profile cpu,block io,memory,swaps for query 2;
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| starting | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking query cache for query | 0.000083 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Opening tables | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| init | 0.000038 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| optimizing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| statistics | 0.000113 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| preparing | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Creating tmp table | 0.000162 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Copying to tmp table | 52.027014 | 4.992241 | 1.324799 | 0 | 0 | 0 |
| Sorting result | 0.000066 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Sending data | 0.000218 | 0.000000 | 0.000999 | 0 | 0 | 0 |
| end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| removing tmp table | 0.000093 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| query end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| closing tables | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| storing result in query cache | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
31 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show profiles;
Empty set (0.00 sec)
mysql> explain
-> SELECT a.tid, a.subject,a.dateline,a.fid,b.message
-> FROM forum_thread AS a
-> LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
-> WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
-> ORDER BY a.dateline DESC
-> LIMIT 0,5;
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
| 1 | SIMPLE | b | ref | invisible,displayorder,first | invisible | 1 | const | 97042 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY,displayorder,typeid | PRIMARY | 8 | forum.b.tid | 1 | Using where |
+----+-------------+-------+--------+------------------------------+-----------+---------+-------------+-------+----------------------------------------------+
2 rows in set (1.29 sec)
mysql> SELECT a.tid, a.subject,a.dateline,a.fid,b.message
-> FROM forum_thread AS a
-> LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
-> WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
-> ORDER BY a.dateline DESC
-> LIMIT 0,5;
执行这条SQL后,总耗时为 52s 查看具体步骤的耗时
mysql> show profiles;
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 1.28875000 | explain
SELECT a.tid, a.subject,a.dateline,a.fid,b.message
FROM forum_thread AS a
LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
ORDER BY a.dateline DESC
LIMIT 0,5 |
| 2 | 52.027014 | SELECT a.tid, a.subject,a.dateline,a.fid,b.message
FROM forum_thread AS a
LEFT JOIN forum_post_8 AS b ON a.tid = b.tid
WHERE a.highlight!=0 AND b.first=1 AND a.fid=113 AND b.invisible=0
ORDER BY a.dateline DESC
LIMIT 0,5 | |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show profile cpu,block io,memory,swaps for query 2;
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
| starting | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking query cache for query | 0.000083 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Opening tables | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| init | 0.000038 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| optimizing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| statistics | 0.000113 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| preparing | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Creating tmp table | 0.000162 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Copying to tmp table | 52.027014 | 4.992241 | 1.324799 | 0 | 0 | 0 |
| Sorting result | 0.000066 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Sending data | 0.000218 | 0.000000 | 0.000999 | 0 | 0 | 0 |
| end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| removing tmp table | 0.000093 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| query end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| closing tables | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| storing result in query cache | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
+--------------------------------+-----------+----------+------------+--------------+---------------+-------+
31 rows in set (0.00 sec)
mysql> show profile cpu,CONTEXT SWITCHES,PAGE FAULTS for query 2;
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Page_faults_major | Page_faults_minor |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| starting | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| checking query cache for query | 0.000083 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Opening tables | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| init | 0.000038 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| optimizing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| statistics | 0.000113 | 0.000000 | 0.000000 | 0 | 0 | 0 | 1 |
| preparing | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Creating tmp table | 0.000162 | 0.000000 | 0.000000 | 0 | 0 | 0 | 5 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Copying to tmp table | 52.027014 | 4.992241 | 1.324799 | 17969 | 1828 | 1 | 251431 |
| Sorting result | 0.000066 | 0.000000 | 0.000000 | 0 | 0 | 0 | 14 |
| Sending data | 0.0
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Page_faults_major | Page_faults_minor |
+--------------------------------+-----------+----------+------------+-------------------+---------------------+-------------------+-------------------+
| starting | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| checking query cache for query | 0.000083 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Opening tables | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Waiting for query cache lock | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| init | 0.000038 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| optimizing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| statistics | 0.000113 | 0.000000 | 0.000000 | 0 | 0 | 0 | 1 |
| preparing | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Creating tmp table | 0.000162 | 0.000000 | 0.000000 | 0 | 0 | 0 | 5 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |
| Copying to tmp table | 52.027014 | 4.992241 | 1.324799 | 17969 | 1828 | 1 | 251431 |
| Sorting result | 0.000066 | 0.000000 | 0.000000 | 0 | 0 | 0 | 14 |
| Sending data | 0.0