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: refpossible_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 condition1 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: rangepossible_keys: PRIMARY,idx_del_sale_cat_gid,idx_del_sale_cat_update          key: PRIMARY      key_len: 3          ref: NULL         rows: 14        Extra: Using where1 row in set (0.00 sec)

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