Mysql execution plan changes due to an increase in data volume

As the data volume increases, the mysql execution plan changes. After receiving a call from the O & M personnel, the number of connections to the mysql server is full. log on to the server and check that the number is full. Well, first increase the number of connections to 2500 to provide external services temporarily. The connection continues to rise and reaches 2500 again. It is found that a large number of queries have taken nearly 1200 seconds, and a large number of persistent connections have accumulated, resulting in the increase in the number of connections. It seems that the problem is still SQL. In these persistent connections, such SQL statements are found

SELECT product_id,gift_id,gift_original_price,gift_count, FROM promo_xxx WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;mysql> explain  SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift  WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra       |+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+|  1 | SIMPLE      | promo_gift_list | ALL  | id_promo_gift | NULL | NULL    | NULL | 249188 | Using where | +----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+1 row in set (0.04 sec)mysql> show index from promo_gift;+-----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table           | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| promo_gift_list |          0 | PRIMARY         |            1 | id              | A         |      261184 |     NULL | NULL   |      | BTREE      |         |               | | promo_gift_list |          0 | id_promo_gift   |            1 | promotion_id    | A         |        1140 |     NULL | NULL   | YES  | BTREE      |         |               |  | promo_gift_list |          0 | id_promo_gift   |            4 | product_id      | A         |      261184 |     NULL | NULL   | YES  | BTREE      |         |               | 

 

Why didn't I go through the full table scan with the promotion_id index? In addition, the previously created indexes are well performed. How can this problem occur today? This is a problem. You can use last_query_cost to check SQL consumption.

mysql>SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift  WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1; mysql>show status like 'last_query_cost';+-----------------+--------------+| Variable_name   | Value        |+-----------------+--------------+| Last_query_cost | 52626.599000 | +-----------------+--------------+1 row in set (0.00 sec)

 

If we do not leave the index, we will use the index forcibly.

mysql> explain SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift force index(id_promo_gift) WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;         +----+-------------+-----------------+-------+---------------+---------------+---------+------+--------+-------------+| id | select_type | table           | type  | possible_keys | key           | key_len | ref  | rows   | Extra       |+----+-------------+-----------------+-------+---------------+---------------+---------+------+--------+-------------+|  1 | SIMPLE      | promo_gift_list | range | id_promo_gift | id_promo_gift | 5       | NULL | 124594 | Using where | +----+-------------+-----------------+-------+---------------+---------------+---------+------+--------+-------------+1 row in set (0.02 sec)

 

Well, what about SQL consumption when I add an index?

mysql> SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift force index(id_promo_gift) WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;         +------------+----------+---------------------+------------+| product_id | gift_id  | gift_original_price | gift_count |+------------+----------+---------------------+------------+|   22569455 | 23230046 |              147.00 |          1 | +------------+----------+---------------------+------------+1 row in set (0.40 sec)mysql> show status like 'last_query_cost';+-----------------+---------------+| Variable_name   | Value         |+-----------------+---------------+| Last_query_cost | 174432.609000 | +-----------------+---------------+1 row in set (0.00 sec)

 

We found that if this index is used, the SQL consumption is 174432.609000> 52626.599000. The mysql optimizer considers that the id_promo_gift index is used, and the SQL consumption is very large. This is why mysql does not use this index for execution. Later, the developer said that the table added more than 0.11 million data last night. Well, the data volume increased and the mysql execution plan changed. Well, add an index to the separate product_id column.

mysql> alter table promo_gift_list add index  product_id(product_id);Query OK, 0 rows affected (6.45 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain  SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift_list  WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1 AND IFNULL(is_delete,0)!=1;+----+-------------+-----------------+------+--------------------------+------------+---------+-------+------+-------------+| id | select_type | table           | type | possible_keys            | key        | key_len | ref   | rows | Extra       |+----+-------------+-----------------+------+--------------------------+------------+---------+-------+------+-------------+|  1 | SIMPLE      | promo_gift_list | ref  | id_promo_gift,product_id | product_id | 5       | const |    2 | Using where |+----+-------------+-----------------+------+--------------------------+------------+---------+-------+------+-------------+1 row in set (0.00 sec)

 

The Newly Added Index product_id is used.

mysql>  SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift_list  WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1 AND IFNULL(is_delete,0)!=1;         +------------+----------+---------------------+------------+| product_id | gift_id  | gift_original_price | gift_count |+------------+----------+---------------------+------------+|   22569455 | 23230046 |              147.00 |          1 | +------------+----------+---------------------+------------+1 row in set (0.00 sec)mysql> show status like 'last_query_cost';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| Last_query_cost | 2.399000 | +-----------------+----------+1 row in set (0.01 sec)

 

SQL consumption reduced to 2.399000, OK, problem solving. The number of connections quickly dropped from more than 1000 to less than 100. The increase in data volume leads to a change in the mysql execution plan. How is the mysql cost calculated? Cost = io_cost + cpu_cost is located at the upper layer of mysql. The overhead for processing returned records is included in the io_cost storage engine layer, and there is no IO overhead for reading. Last_query_cost is the most direct way to record SQL cost. Check last_query_cost To preliminarily judge the SQL cost and understand the basis for executing the mysql optimizer.