数据量增加导致
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消耗
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)
不走索引，那我们强制使用索引
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)
嗯，加上索引了，那么sql消耗怎么样呢？
mysql> S