数据库收到报警，负载飙至60，上去一看，cpu飙至3000%，32个核心所剩无几！
第一反应，sql语句卡住了。
登录mysql，show processlist，发现大量重复的sql语句
SELECT cv.filter_value_id,cv.filter_id,cv.value,COUNT(1) quantity
FROM e_category_filter_value AS cv
INNER JOIN e_product_to_filter_value AS p_v ON cv.filter_value_id=p_v.filter_value_id
INNER JOIN products AS p ON p_v.products_id = p.products_id
INNER JOIN products_to_categories AS p_c ON p_c.products_id=p_v.products_id
WHERE cv.filter_id IN(575,576,568,572,569,570,571,573,574)
GROUP BY cv.filter_value_id;
已经有人反应网站开始打不开，为了尽快解决问题，使用自己写的快速杀连接脚本杀掉这些sql语句.脚本大致可以参考sql语句
SELECT * FROM information_schema.processlist WHERE TIME >=5 AND USER LIKE 'banggood%' AND (state LIKE 'Copying%' OR state LIKE 'Sending%' OR state LIKE 'Sorting%'
连续杀了几次，发现cpu稳定，网站也趋于稳定！
接下来着手优化这条sql语句！
第一步，找到对应的开发人员，了解大致作用。
第二步,explian
mysql> explain SELECT SQL_NO_CACHE cv.filter_value_id,cv.filter_id,cv.value,COUNT(1) quantity FROM e_category_filter_value AS cv INNER JOIN e_product_to_filter_value AS p_v ON cv.filter_value_id=p_v.filter_value_id INNER JOIN products AS p ON p_v.products_id = p.products_id INNER JOIN products_to_categories AS p_c ON p_c.products_id=p_v.products_id WHERE cv.filter_id IN(575,576,568,572,569,570,571,573,574) GROUP BY cv.filter_value_id;
+----+-------------+-------+--------+---------------------------------+-------------+---------+------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+-------------+---------+------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | p_c | index | PRIMARY | PRIMARY | 8 | NULL | 98109 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | banggood.p_c.products_id | 1 | Using index |
| 1 | SIMPLE | p_v | ref | products_id,ix_eptfv_fvid_proid | products_id | 4 | banggood.p_c.products_id | 16 | |
| 1 | SIMPLE | cv | eq_ref | PRIMARY,filter_id | PRIMARY | 4 | banggood.p_v.filter_value_id | 1 | Using where |
+----+-------------+-------+--------+---------------------------------+-------------+---------+------------------------------+-------+----------------------------------------------+
发现并没有使用索引cv.filter_id(该索引是存在的)，而是选择了p_c表的主键,导致了全索引扫描，大量损耗cpu。
第三步，尝试使用force index
mysql> EXPLAIN
-> SELECT SQL_NO_CACHE cv.filter_value_id,cv.filter_id,cv.value,COUNT(1) quantity
-> FROM e_category_filter_value AS cv FORCE INDEX(filter_id)
-> INNER JOIN e_product_to_filter_value AS p_v ON cv.filter_value_id=p_v.filter_value_id
-> INNER JOIN products AS p ON p_v.products_id = p.products_id
-> INNER JOIN products_to_categories AS p_c ON p_c.products_id=p_v.products_id
-> WHERE cv.filter_id IN(575,576,568,572,569,570,571,573,574)
-> GROUP BY cv.filter_value_id;
+----+-------------+-------+--------+---------------------------------+---------------------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+---------------------+---------+-----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | cv | range | filter_id | filter_id | 4 | NULL | 75 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p_v | ref | products_id,ix_eptfv_fvid_proid | ix_eptfv_fvid_proid | 4 | banggood.cv.filter_value_id | 1495 | Using index |
| 1 | SIMPLE | p_c | ref | PRIMARY | PRIMARY | 4 | banggood.p_v.products_id | 981 | Using index |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | banggood.p_c.products_id | 1 | Using where; Using index |
+----+-------------+-------+--------+---------------------------------+---------------------+---------+-----------------------------+------+----------------------------------------------+
对比两次执行时间,为使用force index时，执行需要2s；使用后,执行时间变为0.03s。
你以为这次优化就这样结束了,那就和我给的题目不符了！
第四部：
仔细观察这个sql语句,发现where里面和select子句里面都么有设计到
INNER JOIN products AS p ON p_v.products_id = p.products_id
INNER JOIN products_to_categories AS p_c ON p_c.products_id=p_v.products_id
这两个连表子句的任何参数,询问开发,发现需要和products表做连表过滤，因为可能在e_category_filter_value表中存在的products_id，但是可能在products表中不存在。而至于products_to_categories表(产品对类别表，一个产品对应了多个类别，是一个可以将结果集放大非常多倍的表)，他找不到加上的理由。
现在去掉products_to_categories表
mysql> EXPLAIN
-> SELECT SQL_NO_CACHE cv.filter_value_id,cv.filter_id,cv.value,COUNT(1) quantity
-> FROM e_category_filter_value AS cv
-> INNER JOIN e_product_to_filter_value AS p_v ON cv.filter_value_id=p_v.filter_value_id
-> INNER JOIN products AS p ON p_v.products_id = p.products_id
-> WHERE cv.filter_id IN(575,576,568,572,569,570,571,573,574)
-> GROUP BY cv.filter_value_id;
+----+-------------+-------+--------+---------------------------------+---------------------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+---------------------+---------+-----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | cv | range | PRIMARY,filter_id | filter_id | 4 | NULL | 75 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p_v | ref | products_id,ix_eptfv_fvid_proid | ix_eptfv_fvid_proid | 4 | banggood.cv.filter_value_id | 1495 | Using index |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | banggood.p_v.products_id | 1 | Using index |
+----+-------------+-------+--------+---------------------------------+---------------------+---------+-----------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)
发现索引使用正确，再次执行，发现执行时间变为0.03s。
总结：1.尽量不要连接一些可有可无的表，这个例子就是血的教训
2.不要太相信mysql的索引使用，有的时候需要自己借助于force index命令来进行调优！
©著作权归作者所有：来自51CTO博客作者justforqdj的原创作品，如需转载，请注明出处，否则将追究法律责任
mysql force indexmysql 连接表mysql sql调优记录
共同学习，写下你的评论
评论加载中...
作者其他优质文章
