An example Analysis of MySQL's use of inefficient Indexes in execution Plan due to data Type conversion
Editor to share with you the MySQL due to data type conversion led to the use of inefficient index example analysis, I hope you will learn something after reading this article, let's discuss it!
Check the index of the table
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)
View the execution plan of the statement
It was found that SQL did not take the primary key index, but an inefficient federated index.
Mysql > explain-> SELECT-> `promote_end_ 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_on_ stock`,-> `is_ hot`,-> `is_ stock`,-> `is_ presale`,-> `goods_ weight`,-> `cat_ id`,-> `is_on_ sale`,-> `last_sold_out_ Reason` -> `who_ 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\ 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)
Change the string in IN to a number, and the execution plan leaves the primary key.
Mysql > explain-> SELECT-> `promote_end_ 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_on_ stock`,-> `is_ hot`,-> `is_ stock`,-> `is_ presale`,-> `goods_ weight`,-> `cat_ id`,-> `is_on_ sale`,-> `last_sold_out_ Reason` -> `who_ 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\ 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) finished reading this article I believe you have a certain understanding of the "sample analysis of the use of inefficient indexes in MySQL execution plans due to data type conversion". If you want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
