小编给大家分享一下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因数据类型转换导致执行计划使用低效索引的示例分析”有了一定的了解,如果想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。