數據量增加導致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> 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)
我們發現如果使用這個索引，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優化器執行的依據。
