mysql的in会不会让索引失效?

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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排序， 如下：

?

1 2 3 4 5 6 7 8 9 10

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>

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对服务器之家的支持。如果你想了解更多相关内容请查看下面相关链接

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