去掉不该连接的表

数据库收到报警，负载飙至60，上去一看，cpu飙至3000%，32个核心所剩无几！第一反应，sql语句卡住了。登录mysql，showprocesslist，发现大量重复的sql语句SELECT

数据库收到报警，负载飙至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命令来进行调优！

本文出自 “原下” 博客，请务必保留此出处