数据量增加导致mysql执行计划改变解决(二)
ELECT 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)
我们发现如果使用这个索引，sql消耗174432.609000>52626.599000,mysql优化器认为使用这个id_promo_gift索引，sql消耗是非常大的，这就是mysql执行不使用这个索引的原因。
后来开发人员说，昨天晚上这个表增加了11万多的数据，嗯，数据量增加，mysql执行计划改变。那好吧，单独product_id列再加一个索引。
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: 0 mysql> 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)
使用了刚才新加的索引 product_id
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消耗降到了2.399000，ok，问题解决。连接数很快从1000多降到100以内。
数据量的增加导致了mysql执行计划的改变，那么mysql的cost是怎么计算的呢？
cost=io_cost+cpu_cost
cpu_cost位于mysql上层，处理返回的记录所花开销，io_cost存储引擎层，读取也没的IO开销。最直接的方式last_query_cost记录sql的cost。查看last_query_cost可以初步判断sql的cost，明白mysql优化器执行的依据。