Remove tables that should not be connected

When the database received an alarm, the load soared to 60. When I went up, the cpu usage soared to 3000%, with 32 cores running low! First, the SQL statement got stuck. Log on to mysql and showprocesslist to find a large number of duplicate SQL statements

When the database received an alarm, the load soared to 60. When I went up, the cpu usage soared to 3000%, with 32 cores running low! First, the SQL statement got stuck. Log on to mysql and showprocesslist to find a large number of duplicate SQL statements

When the database received an alarm, the load soared to 60. When I went up, the cpu usage soared to 3000%, with 32 cores running low!

First, the SQL statement got stuck.

Log on to mysql and show processlist to find a large number of duplicate SQL statements.

SELECT cv. filter_value_id, cv. filter_id, cv. value, COUNT (1) quantityFROM e_category_filter_value AS cvINNER JOIN e_product_to_filter_value AS p_v ON cv. filter_value_id = p_v.filter_value_idINNER JOIN products AS p ON p_v.products_id = p. products_idINNER JOIN products_to_categories AS p_c ON p_c.products_id = p_v.products_idWHERE cv. filter_id IN (575,576,568,572,569,570,571,573,574) group by cv. filter_value_id;

Someone has responded that the website cannot be opened. To solve the problem as soon as possible, use your own kill connection script to kill these SQL statements. For the script, see SQL statements.

SELECT * FROM information_schema.processlist where time> = 5 and user like 'banggood % 'AND (state LIKE 'copying %' OR state LIKE 'sending % 'OR state LIKE 'sorting %'

After several consecutive kill attempts, I found that the cpu is stable and the website is also stable!

Next, we will optimize this SQL statement!

Step 1: Find the corresponding developer to understand the general functions.

Step 2, 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; + ---- + ------------- + ------- + -------- + hour + ------------- + --------- + hour + ------- + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + -------- + upper + ------------- + --------- + ------------------------------ + ------- + lower + | 1 | SIMPLE | p_c | index | PRIMARY | 8 | NULL | 98109 | Using index; using temporary; Using filesort | 1 | SIMPLE | p | eq_ref | 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 | + ---- + ------------- + ------- + -------- + upper + --------------- + -------------------------------- + ------- + upper +

It was found that the index cv. filter_id was not used (the index exists), but the primary key of the p_c table was selected, resulting in full index scanning and high cpu consumption.

Step 3: Try to use 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; + ---- + ------------- + ------- + -------- + hour + --------------------- + --------- + hour + ------ + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + -------- + upper + ------------------- + --------- + upper + ------ + upper + | 1 | SIMPLE | cv | range | filter_id | 4 | NULL | 75 | Using where; using temporary; Using filesort | 1 | SIMPLE | p_v | ref | products_id, ix_eptfv_fvid_proid | 4 | banggood. cv. filter_value_id | 1495 | Using index | 1 | SIMPLE | p_c | ref | PRIMARY | 4 | banggood. p_v.products_id | 981 | Using index | 1 | SIMPLE | p | eq_ref | PRIMARY | 4 | banggood. p_c.products_id | 1 | Using where; Using index | + ---- + --------------- + ------- + -------- + certificate + --------------------- + --------- + ----------------------------- + ------ + quota +

Compare the two execution times. When force index is used, the execution takes 2 s. after use, the execution time is changed to 0.03 s.

You think this optimization is over, and it is inconsistent with the question I gave!

Part 4:

Observe this SQL statement carefully and find that both the where clause and the select clause are designed

Inner join products AS p ON p_v.products_id = p. products_idINNER JOIN products_to_categories AS p_c ON p_c.products_id = p_v.products_id

For any parameters of these two join table clauses, we asked about development and found that we need to perform table join filtering with the products table, because the products_id may exist in the e_category_filter_value table, but may not exist in the products table. As for the products_to_categories table (a product corresponds to multiple categories for a category table, and a table that can enlarge the result set multiple times), the reason cannot be found.

Remove the products_to_categories table.

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; + ---- + ------------- + ------- + -------- + hour + --------------------- + --------- + hour + ------ + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + -------- + upper + --------------------- + --------- + --------------------------- + ------ + upper + | 1 | SIMPLE | cv | range | PRIMARY, filter_id | 4 | NULL | 75 | Using where; Using temporary; Using filesort | 1 | SIMPLE | p_v | ref | products_id, ix_eptfv_fvid_proid | usage | 4 | banggood. cv. filter_value_id | 1495 | Using index | 1 | SIMPLE | p | eq_ref | PRIMARY | 4 | banggood. p_v.products_id | 1 | Using index | + ---- + ------------- + ------- + -------- + upper + --------------------- + --------- + --------------------------- + ------ + rows + 3 rows in set (0.00 sec)

It is found that the index is correctly used and executed again. The execution time is changed to 0.03 s.

Conclusion: 1. Try not to connect some dispensable tables. This example is a blood lesson.

2. Do not trust mysql indexes too much. Sometimes you need to use the force index Command for optimization!

This article is from the "original" blog. Please keep this source