mysql的in会让索引失效吗？

最新推荐文章于 2024-08-21 15:37:43 发布

涛歌依旧

最新推荐文章于 2024-08-21 15:37:43 发布

阅读量6.1w

点赞数 6

分类专栏： s2: 软件进阶 s4: 数据库

本文链接：https://blog.csdn.net/stpeace/article/details/79143987

版权

s2: 软件进阶同时被 2 个专栏收录

1053 篇文章 76 订阅

订阅专栏

s4: 数据库

101 篇文章 23 订阅

订阅专栏

mysql的in会让索引失效吗？不会！看结果：

mysql> desc select * from tb_province where name in ('lily3', 'lily2', 'lily1');
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_province | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 108780 |    30.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table tb_province add index g(name);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select * from tb_province where name in ('lily3', 'lily2', 'lily1');
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table       | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_province | NULL       | range | g             | g    | 34      | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

顺便说下， in查出的结果，不一定按in排序，如下：

mysql> select * from tb_province where name in ('lily3', 'lily2', 'lily1');
+----+-------+-------+------+------+------+------+------+------+------+------+------+------+------+
| id | name  | score | x    | x1   | x2   | x3   | x4   | x5   | x6   | x7   | x8   | x9   | x10  |
+----+-------+-------+------+------+------+------+------+------+------+------+------+------+------+
|  1 | lily1 |     1 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
|  2 | lily2 |     2 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
|  3 | lily3 |     3 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
+----+-------+-------+------+------+------+------+------+------+------+------+------+------+------+
3 rows in set (0.00 sec)

mysql>

最后，我们来看看in的一个常用功能，即联表操作：

select * from a where id in (select id from b);

不多说。