mysql索引失效

1. 模糊匹配查询 通配符字符串 %多个任意字符,%在条件左侧

mysql> select * from user where id like '%234567';
+---------+---------------+----------------------------------+------+-----------------------+
| id      | username      | password                         | sex  | email                 |
+---------+---------------+----------------------------------+------+-----------------------+
|  234567 | sandul234567  | 508df4cb2f4d8f80519256258cfb975f | male | jack234567@sandul.cn  |
| 1234567 | sandul1234567 | fcea920f7412b5da7be0cf42b8c93759 | male | jack1234567@sandul.cn |
| 2234567 | sandul2234567 | d1999a2caf49c63987d19e2ee981ead8 | male | jack2234567@sandul.cn |
| 3234567 | sandul3234567 | 5059437b8a54d05c09884405ebaf7fb1 | male | jack3234567@sandul.cn |
| 4234567 | sandul4234567 | 01ebc8fbcf093ab638b0f80816dbb11e | male | jack4234567@sandul.cn |
| 5234567 | sandul5234567 | f87713ae6450bb9e81fb683376a17699 | male | jack5234567@sandul.cn |
| 6234567 | sandul6234567 | da4702f331d0bffe02a8e6d0720a11bb | male | jack6234567@sandul.cn |
| 7234567 | sandul7234567 | ce394992d7111daf6710362b306d0b3c | male | jack7234567@sandul.cn |
| 8234567 | sandul8234567 | 57e1ddb5354a5f4d7b6ef793d56e1327 | male | jack8234567@sandul.cn |
| 9234567 | sandul9234567 | 0408294f7adc0d49465fe20823d00c4f | male | jack9234567@sandul.cn |
+---------+---------------+----------------------------------+------+-----------------------+
10 rows in set (44.10 sec)

2.尽量避免使用or or的条件如果没有索引 采用全表扫描

3.在有索引的列上进行计算会导致索引失效

select * from user where id+1=123456;

4.使用is null 或者is not null 导致索引失效

select * from user where id is not null;

5.使用!= 或者 <> 导致索引失效

6.使用not in语法 导致索引失效

select * from user where id not in (1,2,3);

7.尽量省略隐式类型转换

# email 字段有索引，类型是varchar（32）
select * from user where email = 123;//走全表
select * from user where email = cast(123 as char(32));//走索引