MySQL因数据类型转换导致执行计划使用低效索引的示例分析

小编给大家分享一下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)

看完了这篇文章，相信你对“MySQL因数据类型转换导致执行计划使用低效索引的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！