小编给大家分享一下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因数据类型转换导致执行计划使用低效索引的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！
