去掉不该连接的表

Mike

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