MySQL 因資料型別轉換導致執行計劃使用低效索引

檢視錶的索引情況

mysql> show keys from who_goods;
+-----------+------------+-------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| who_goods |          0 | PRIMARY                 |            1 | goods_id          | A         |       68442 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_del_sale_cat_gid    |            1 | is_delete         | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_del_sale_cat_gid    |            2 | is_on_sale        | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_del_sale_cat_gid    |            3 | cat_id            | A         |         551 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_del_sale_cat_gid    |            4 | goods_id          | A         |       68442 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_del_sale_cat_update |            1 | is_delete         | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_del_sale_cat_update |            2 | is_on_sale        | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_del_sale_cat_update |            3 | cat_id            | A         |         551 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_del_sale_cat_update |            4 | last_update       | A         |       68442 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | goods_sn                |            1 | goods_sn          | A         |        4888 |        7 | NULL   |      | BTREE      |         |               |
| who_goods |          1 | add_time                |            1 | add_time          | A         |       68442 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | last_update             |            1 | last_update       | A         |       68442 |     NULL | NULL   |      | BTREE      |         |               |
| who_goods |          1 | idx_provider_code       |            1 | provider_code     | A         |         786 |        3 | NULL   |      | BTREE      |         |               |
| who_goods |          1 | inx_code                |            1 | goods_search_code | A         |        1801 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+-------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 rows in set (0.00 sec)

檢視語句的執行計劃

發現SQL沒有走主鍵索引，而是走了一個低效的聯合索引

mysql> explain
    -> SELECT
    ->   `goods_id`,
    ->   `goods_sn`,
    ->   `goods_name`,
    ->   `market_price`,
    ->   `shop_price`,
    ->   `promote_price`,
    ->   `promote_start_date`,
    ->   `promote_end_date`,
    ->   `goods_thumb`,
    ->   `goods_thumb_small`,
    ->   `goods_img`,
    ->   `goods_brief`,
    ->   `is_new`,
    ->   `is_best`,
    ->   `is_stock`,
    ->   `is_hot`,
    ->   `is_promote`,
    ->   `is_presale`,
    ->   `goods_weight`,
    ->   `cat_id`,
    ->   `is_on_sale`,
    ->   `last_sold_out_reason`,
    ->   `is_forever_offsale`
    -> FROM
    ->   (`who_goods`)
    -> WHERE
    ->   `goods_id` IN (
    ->     1120872,
    ->     2875488,
    ->     2562654,
    ->     697450,
    ->     2776492,
    ->     663476,
    ->     629658,
    ->     549306,
    ->     '312946',
    ->     '845004',
    ->     '3103382',
    ->     '3368908',
    ->     '929186',
    ->     '697454'
    ->   )
    ->   AND `is_delete` = 0
    ->   AND `is_on_sale` = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: who_goods
         type: ref
possible_keys: PRIMARY,idx_del_sale_cat_gid,idx_del_sale_cat_update
          key: idx_del_sale_cat_gid
      key_len: 2
          ref: const,const
         rows: 34221
        Extra: Using index condition
1 row in set (0.00 sec)

將IN中的字串改成數字，執行計劃走了主鍵

mysql> explain
    -> SELECT
    ->   `goods_id`,
    ->   `goods_sn`,
    ->   `goods_name`,
    ->   `market_price`,
    ->   `shop_price`,
    ->   `promote_price`,
    ->   `promote_start_date`,
    ->   `promote_end_date`,
    ->   `goods_thumb`,
    ->   `goods_thumb_small`,
    ->   `goods_img`,
    ->   `goods_brief`,
    ->   `is_new`,
    ->   `is_best`,
    ->   `is_stock`,
    ->   `is_hot`,
    ->   `is_promote`,
    ->   `is_presale`,
    ->   `goods_weight`,
    ->   `cat_id`,
    ->   `is_on_sale`,
    ->   `last_sold_out_reason`,
    ->   `is_forever_offsale`
    -> FROM
    ->   (`who_goods`)
    -> WHERE
    ->   `goods_id` IN (
    ->     1120872,
    ->     2875488,
    ->     2562654,
    ->     697450,
    ->     2776492,
    ->     663476,
    ->     629658,
    ->     549306,
    ->     312946,
    ->     845004,
    ->     3103382,
    ->     3368908,
    ->     929186,
    ->     697454
    ->   )
    ->   AND `is_delete` = 0
    ->   AND `is_on_sale` = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: who_goods
         type: range
possible_keys: PRIMARY,idx_del_sale_cat_gid,idx_del_sale_cat_update
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 14
        Extra: Using where
1 row in set (0.00 sec)