小编给大家分享一下MySQL因数据类型转换导致执行计划使用低效索引的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

查看表的索引情况

mysql>showkeysfromwho_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|||+-----------+------------+-------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+14rowsinset(0.00sec)

查看语句的执行计划

发现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:1select_type:SIMPLEtable:who_goodstype:refpossible_keys:PRIMARY,idx_del_sale_cat_gid,idx_del_sale_cat_updatekey:idx_del_sale_cat_gidkey_len:2ref:const,constrows:34221Extra:Usingindexcondition1rowinset(0.00sec)

将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:1select_type:SIMPLEtable:who_goodstype:rangepossible_keys:PRIMARY,idx_del_sale_cat_gid,idx_del_sale_cat_updatekey:PRIMARYkey_len:3ref:NULLrows:14Extra:Usingwhere1rowinset(0.00sec)

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