记一次线上老系统Mysql慢查询优化

线上的老系统在高峰时期卡顿，有的时候甚至页面都打不开，通过监控发现是Mysql慢查询引起的，因此找到这些慢查询，来分别做优化。

1、第一条

mysql> SELECT COUNT(*) FROM `questions`  WHERE `questions`.`status` = 1 AND `questions`.`subject_id` = 0;
+----------+
| COUNT(*) |
+----------+
|   147586 |
+----------+
1 row in set (0.54 sec)

mysql> explain SELECT COUNT(*) FROM `questions`  WHERE `questions`.`status` = 1 AND `questions`.`subject_id` = 0;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | questions | ALL  | NULL          | NULL | NULL    | NULL | 177537 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

优化方法，添加联合索引：

alter table questions add index subject_id_status(subject_id,status);

优化结果，查询从0.54秒缩短到0.04秒：

mysql> explain SELECT COUNT(*) FROM `questions`  WHERE `questions`.`status` = 1 AND `questions`.`subject_id` = 0;
+----+-------------+-----------+------+-------------------+-------------------+---------+-------------+-------+-------------+
| id | select_type | table     | type | possible_keys     | key               | key_len | ref         | rows  | Extra       |
+----+-------------+-----------+------+-------------------+-------------------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | questions | ref  | subject_id_status | subject_id_status | 6       | const,const | 88768 | Using index |
+----+-------------+-----------+------+-------------------+-------------------+---------+-------------+-------+-------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM `questions`  WHERE `questions`.`status` = 1 AND `questions`.`subject_id` = 0;
+----------+
| COUNT(*) |
+----------+
|   147586 |
+----------+
1 row in set (0.04 sec)

2、第二条

mysql> SELECT COUNT(*) FROM `answer_votes`  WHERE `answer_votes`.`votable_id` = 53961 AND (up=0);
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

mysql> explain SELECT COUNT(*) FROM `answer_votes`  WHERE `answer_votes`.`votable_id` = 53961 AND (up=0);
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | answer_votes | ALL  | NULL          | NULL | NULL    | NULL | 96390 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

优化方法，添加联合索引：

mysql> alter table answer_votes add index votable_id__up(votable_id,up);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(*) FROM `answer_votes`  WHERE `answer_votes`.`votable_id` = 53961 AND (up=0);
+----+-------------+--------------+------+----------------+----------------+---------+-------------+------+-------------+
| id | select_type | table        | type | possible_keys  | key            | key_len | ref         | rows | Extra       |
+----+-------------+--------------+------+----------------+----------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | answer_votes | ref  | votable_id__up | votable_id__up | 5       | const,const |    1 | Using index |
+----+-------------+--------------+------+----------------+----------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

3、第三条

mysql> SELECT `recommend_questions`.* FROM `recommend_questions` WHERE `recommend_questions`.`user_id` = 2309357 AND `recommend_questions`.`question_id` = 23156 LIMIT 1;
Empty set (0.20 sec)

mysql> EXPLAIN SELECT `recommend_questions`.* FROM `recommend_questions` WHERE `recommend_questions`.`user_id` = 2309357 AND `recommend_questions`.`question_id` = 23156 LIMIT 1;
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | recommend_questions | ALL  | NULL          | NULL | NULL    | NULL | 691335 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

优化方法，添加联合索引：

mysql> alter table recommend_questions add index user_id_question_id(user_id,question_id);
Query OK, 0 rows affected (1.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

优化结果，查询从0.2秒缩短到0.01秒：

mysql> EXPLAIN SELECT `recommend_questions`.* FROM `recommend_questions` WHERE `recommend_questions`.`user_id` = 2309357 AND `recommend_questions`.`question_id` = 23156 LIMIT 1;
+----+-------------+---------------------+------+---------------------+---------------------+---------+-------------+------+-------+
| id | select_type | table               | type | possible_keys       | key                 | key_len | ref         | rows | Extra |
+----+-------------+---------------------+------+---------------------+---------------------+---------+-------------+------+-------+
|  1 | SIMPLE      | recommend_questions | ref  | user_id_question_id | user_id_question_id | 8       | const,const |    1 | NULL  |
+----+-------------+---------------------+------+---------------------+---------------------+---------+-------------+------+-------+
1 row in set (0.00 sec)