MySQL in不走索引

SELECT*
 FROM erp_helei mg WHERE mg.num = 602 
 AND mg.pid 
 IN 
 (10002559,10002561,10002562,10002563,10002564,10002598,10002599,10002600,10002601,10002602,10002603,10002604,10002648,10002649,10002667,10002668,10002676,10002558)

优化后：

SELECT*
 FROM erp_helei mg WHERE mg.num = 602 
 AND mg.pid 
 IN 
 ('10002559','10002561','10002562','10002563','10002564','10002598','10002599','10002600','10002601','10002602','10002603','10002604','10002648','10002649','10002667','10002668','10002676','10002558')

原因

pid列是char类型，必须加''号才可走索引，否则导致全表扫描，会随着表的增大而变得更慢。

 

方式二：

给字段 name 建立了索引，当 in 有两个值的时候索引有效，当 in 里面有三个值的时候索引就失效了

mysql> explain select * from tbl_student where name in ('Pogeba', 'Rooney');
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table       | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tbl_student | NULL       | range | name          | name | 51      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tbl_student where name in ('Pogeba', 'Rooney', 'Paul');
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | ALL  | name          | NULL | NULL    | NULL |   15 |    20.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

方式三：

MySQL当中in 内如果跟上子查询,竟然会让索引失效

SELECT *
FROM `orders`
WHERE `id` IN (SELECT max(id) FROM `orders` WHERE `user_id` = 1 GROUP BY product_id);

优化后：

select o.*
from (select max(id) id from orders where user_id = 1 group by product_id) as o
       inner join orders on o.id = orders.id