MySQL中使用IN（）查询到底走不走索引？

女爷i 2024-03-23 10:44 72阅读 0赞

## MySQL中使用IN（）查询到底走不走索引？ ##

### **看数据量** ###

*      EXPLAIN
        SELECT * from users WHERE is_doctor in (0,1);
 *  ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F46PnJDg-1681257665213)(C:%5CUsers%5Cquyanliang%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1681257531822.png)\]][img-F46PnJDg-1681257665213_C_5CUsers_5Cquyanliang_5CAppData_5CRoaming_5CTypora_5Ctypora-user-images_5C1681257531822.png]
 *  很明显没走索引，下面再看一个sql。
 *      EXPLAIN
        SELECT * from users WHERE is_doctor in (2,1);
 *  ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CecVqr47-1681257665214)(C:%5CUsers%5Cquyanliang%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1681257554058.png)\]][img-CecVqr47-1681257665214_C_5CUsers_5Cquyanliang_5CAppData_5CRoaming_5CTypora_5Ctypora-user-images_5C1681257554058.png]
 *  又走索引了，所以IN查询走不走索引需要看rows的数据量，in (0,1)时查询出52万多数据量，全表才54万数据量，in (2,1)时只有1万多数据量。
 *      EXPLAIN
        SELECT * from users WHERE (is_doctor=0 or is_doctor=1); 
        
        与 
        
        EXPLAIN
        SELECT * from users WHERE (is_doctor=2 or is_doctor=1);

### **IN后面是不是有子查询** ###

*      EXPLAIN
        SELECT * from users WHERE is_doctor in (SELECT is_doctor from test_in_subselect);
 *  ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ntJpMJnu-1681257665215)(C:%5CUsers%5Cquyanliang%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1681257606234.png)\]][img-ntJpMJnu-1681257665215_C_5CUsers_5Cquyanliang_5CAppData_5CRoaming_5CTypora_5Ctypora-user-images_5C1681257606234.png]

### 总结 ###

*  IN通常是走索引的，当IN后面的数据在数据表中超过\*30%\*的匹配时是全表扫描，不走索引，因此IN走不走索引和后面的数据量有关系。
 *  根据实际的情况，需要控制IN查询的范围。原因有以下几点
 *  IN 的条件过多，会导致[索引失效][Link 1]，走索引扫描
 *  IN 的条件过多，返回的数据会很多，可能会导致应用堆内[内存溢出][Link 2]。所以必须要控制好IN的查询个数

[img-F46PnJDg-1681257665213_C_5CUsers_5Cquyanliang_5CAppData_5CRoaming_5CTypora_5Ctypora-user-images_5C1681257531822.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/1c5f99e25a964e8c94d75087157066f5.png
[img-CecVqr47-1681257665214_C_5CUsers_5Cquyanliang_5CAppData_5CRoaming_5CTypora_5Ctypora-user-images_5C1681257554058.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/7409b0e891884de6b8d0ab0459aeedd6.png
[img-ntJpMJnu-1681257665215_C_5CUsers_5Cquyanliang_5CAppData_5CRoaming_5CTypora_5Ctypora-user-images_5C1681257606234.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/3a65bd20d32142a9b321c7f676f2c24f.png
[Link 1]: https://so.csdn.net/so/search?q=%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88&spm=1001.2101.3001.7020
[Link 2]: https://so.csdn.net/so/search?q=%E5%86%85%E5%AD%98%E6%BA%A2%E5%87%BA&spm=1001.2101.3001.7020