线上的老系统在高峰时期卡顿,有的时候甚至页面都打不开,通过监控发现是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)