去掉不应衔接的表51CTO博客 - 千亿集团

去掉不应衔接的表51CTO博客

2019年04月26日08时02分33秒 | 作者: 鸿煊 | 标签: 发现,运用,句子 | 浏览: 1713

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

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表千亿集团立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章