原文:https://www.2cto.com/database/201712/702834.html
索引失效原因
一.准备工作
创建student表,id是主键
创建复合索引
1
|
create
index
idx_name_age
on
student(
name
,age);
|
查看索引
1
|
show
index
from
student;
|
二.索引失效原因
1.全值匹配我最爱
1
|
explain
select
*
from
student
where
name
=
'张三'
and
age = 1;
|
2.最佳左前缀法则,带头大哥不能死,中间兄弟不能少。
1
|
explain
select
*
from
student
where
age = 1;
|
3.不要在索引列上做任何操作
1
|
explain
select
*
from
student
where
left
(
name
,1) =
'张'
and
age = 1;
|
4.范围条件后列上索引失效
1
|
explain
select
*
from
student
where
age > 1
and
name
=
'王五'
;
|
查找级别是范围,name上的索引失效。
5.尽量使用覆盖索引减少使用select *
1
|
explain
select
*
from
student
where
name
=
'zhangsan'
;
|
1
|
explain
select
name
from
student
where
name
=
'zhangsan'
;
|
6.使用不等于(!= 或者<>)不能使用索引
1
|
explain
select
*
from
student
where
name
!=
'张三'
;
|
7.使用 is null 或者 is not null 也不能使用索引
1
|
explain
select
*
from
student
where
name
is
not
null
;
|
8.like 已通配符开头(%abc)导致索引失效 (解决方法:使用覆盖索引)
1
|
explain
select
*
from
student
where
name
like
'%张%'
;
|
想用的话解决方法,使用覆盖索引
1
|
explain
select
name
from
student
where
name
like
'%张%'
;
|
9.少用or,用它来连接索引会失效
1
|
explain
select
*
from
student
where
name
=
'张三'
or
age = 2;
|