1.where语句中or左右两边有非索引列,索引失效
使用or并不是一定会使索引失效,这需要看or左右两边的查询列都是索引列。
or左右两边的字段都有索引,索引不失效
explain select * from t_user_info where account = 'LeeJanic';
+----+-------------+-------------+-------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------------+---------+-------+------+-------+
| 1 | SIMPLE | t_user_info | const | index_account | index_account | 99 | const | 1 | NULL |
explain select * from t_user_info where device = '123123123';
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t_user_info | ref | index_device_devicetype | index_device_devicetype | 768 | const | 1 | Using index condition |
explain select * from t_user_info where device = '123123123' or account = 'LeeJanic';
+----+-------------+-------------+-------------+---------------------------------------+---------------------------------------+---------+------+------+----------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------------+---------------------------------------+---------------------------------------+---------+------+------+----------------------------------------------------------------------+
| 1 | SIMPLE | t_user_info | index_merge | index_account,index_device_devicetype | index_device_devicetype,index_account | 768,99 | NULL | 2 | Using sort_union(index_device_devicetype,index_account); Using where |
or左右两边有非索引列,索引失效
explain select * from t_user_info where account = 'LeeJanic';
+----+-------------+-------------+-------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------------+---------+-------+------+-------+
| 1 | SIMPLE | t_user_info | const | index_account | index_account | 99 | const | 1 | NULL |
explain select * from t_user_info where ip = '10.1.1.1';
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | t_user_info | ALL | NULL | NULL | NULL | NULL | 3925707 | Using where |
explain select * from t_user_info where account = 'LeeJanic' or ip = '10.1.1.1';
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | t_user_info | ALL | index_account | NULL | NULL | NULL | 3925709 | Using where |
结论
- 对于索引列account+or+非索引ip情况,假设它走了account的索引,但是走到ip的时候,它还得全表扫描,也就是需要全表扫描+索引扫描+合并。那还不如直接全表扫描,一遍完事。
- 如果两边都走索引,那么就可能会走索引
2.where语句中索引使用了负向查询,可能会导致索引
负向查询包括:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等
<>、!=等运算符号
explain select * from t_user_info where user_id != 100000;
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | t_user_info | range | PRIMARY | PRIMARY | 4 | NULL | 1962899 | Using where |
explain select * from t_user_info where user_id <> 1000000;
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | t_user_info | range | PRIMARY | PRIMARY | 4 | NULL | 1962907 | Using where |
explain select * from t_user_info where account != '123';
+----+-------------+-------------+-------+---