Mysql索引失效原因（lonn,隐联直最）

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 |

结论

1. 对于索引列account+or+非索引ip情况，假设它走了account的索引，但是走到ip的时候，它还得全表扫描，也就是需要全表扫描+索引扫描+合并。那还不如直接全表扫描，一遍完事。
2. 如果两边都走索引，那么就可能会走索引

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';
+----+-------------+-------------+-------+---------------+--------