mysql in查询走索引么_Mysql使用In查询不走索引总结

表结构

--- 用户表

CREATE TABLE `user` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`phone` char(11) NOT NULL,

`name` varchar(255) NOT NULL,

`address` varchar(255) NOT NULL,

`email` varchar(50) NOT NULL,

`sex` tinyint(1) NOT NULL,

`birthday` char(10) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `phone` (`phone`)

) ENGINE=InnoDB AUTO_INCREMENT=202001 DEFAULT CHARSET=utf8mb4;

---用户资产表

CREATE TABLE `user_assets` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`money` decimal(10,2) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=202001 DEFAULT CHARSET=utf8mb4;

1、in里面值的类型，和查询列定义类型不一致的情况，不走索引

mysql> DESC SELECT * FROM `user` where phone in (15370225886);

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

| 1 | SIMPLE | user | NULL | ALL | phone | NULL | NULL | NULL | 201057 | 10.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

1 row in set (0.03 sec)

mysql> DESC SELECT * FROM `user` where phone in ("15370225886");

+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+

| 1 | SIMPLE | user | NULL | const | phone | phone | 44 | const | 1 | 100.00 | NULL |

+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+

1 row in set (0.04 sec)

2、in查询更新，更新操作不走索引

mysql> DESC UPDATE `user_assets` SET money = 0 where id in (SELECT id FROM `user` where sex = 1);

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+-------------+

| 1 | UPDATE | user_assets | NULL | index | NULL | PRIMARY | 4 | NULL | 202032 | 100.00 | Using where |

| 2 | DEPENDENT SUBQUERY | user | NULL | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | 10.00 | Using where |

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+-------------+

2 rows in set (0.04 sec)

mysql> DESC UPDATE `user_assets` SET money = 0 where id in (SELECT id FROM `user` where id < 100);

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+

| 1 | UPDATE | user_assets | NULL | index | NULL | PRIMARY | 4 | NULL | 202032 | 100.00 | Using where |

| 2 | DEPENDENT SUBQUERY | user | NULL | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where; Using index |

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+

2 rows in set (0.05 sec)

mysql> DESC UPDATE `user_assets` SET money = 0 where id in (SELECT id FROM `user` where id < 1000);

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+

| 1 | UPDATE | user_assets | NULL | index | NULL | PRIMARY | 4 | NULL | 202032 | 100.00 | Using where |

| 2 | DEPENDENT SUBQUERY | user | NULL | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where; Using index |

+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+