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

编程学习 2021-07-05 14:37www.dzhlxh.cn编程入门
今天长沙网络推广就为大家分享一篇关于mysql的in会不会让索引失效?,长沙网络推广觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随长沙网络推广来看看吧

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> 

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对狼蚁SEO的支持。如果你想了解更多相关内容请查看狼蚁网站SEO优化相关链接

Copyright © 2016-2025 www.dzhlxh.cn 金源码 版权所有 Power by

网站模板下载|网络推广|微博营销|seo优化|视频营销|网络营销|微信营销|网站建设|织梦模板|小程序模板