MySQL高级 第8章单表使用索引常见的索引失效
1. 全值匹配我最爱
1.1 有以下SQL语句
3. 不要在索引列上做任何计算
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
3.1 在查询列上使用了函数
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;
3.2 在查询列上做了转换
|
create index idx_name on emp(name); |
|
explain select sql_no_cache * from emp where name='30000'; |
|
explain select sql_no_cache * from emp where name=30000; |
字符串不加单引号，则会在name列上做一次转换！
4. 索引列上不能有范围查询
|
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd'; |
|
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd'; |
5. 尽量使用覆盖索引
即查询列和索引列一直，不要写select *!
|
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt'; |
|
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt'; |
7. 字段的is not null 和 is null
8. like的前后模糊匹配
10. 练习
假设index(a,b,c)；
|
Where语句 |
索引是否被使用 |
|
where a = 3 |
Y,使用到a |
|
where a = 3 and b = 5 |
Y,使用到a，b |
|
where a = 3 and b = 5 and c = 4 |
Y,使用到a,b,c |
|
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 |
N |
|
where a = 3 and c = 5 |
使用到a， 但是c不可以，b中间断了 |
|
where a = 3 and b > 4 and c = 5 |
使用到a和b， c不能用在范围之后，b断了 |
|
where a is null and b is not null |
is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用 |
|
where a <> 3 |
不能使用索引 |
|
where abs(a) =3 |
不能使用 索引 |
|
where a = 3 and b like 'kk%' and c = 4 |
Y,使用到a,b,c |
|
where a = 3 and b like '%kk' and c = 4 |
Y,只用到a |
|
where a = 3 and b like '%kk%' and c = 4 |
Y,只用到a |
|
where a = 3 and b like 'k%kk%' and c = 4 |
Y,使用到a,b,c |
11. 口诀
全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写*；
不等空值还有OR，索引影响要注意；
VAR引号不可丢，SQL优化有诀窍。
