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因数据类型转换导致执行计划使用低效索引的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注创新互联行业资讯频道，感谢各位的阅读！
本文题目：MySQL因数据类型转换导致执行计划使用低效索引的示例分析
文章分享：http://6mz.cn/article/iieipe.html