数据量增加导致MySQL执行计划改变(一)

收到运维同学电话，mysql服务器连接数满了，登录服务器查看，确实满了，好吧，首先增加连接数到2500，暂时提供对外服务。连接继续升高，又快达到2500。发现有大量的查询时间将近到了1200秒，大量的长连接堆积，导致连接数攀升，看来还是sql的问题。在这些长连接中，发现这样的sql

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 | | |

狗血的sql，竟然走全表扫描，但是promotion_id有索引啊，为什么没有走索引呢？而且以前建立的索引，走的好好的，今天怎么就出现问题了，这是一个问题
那我们可以通过last_query_cost 查看sql消耗