初识mysql索引(二)

-------------------------------------------------------------------索引失效情况-------------------------------------------------

1.没有最左匹配原则

使用索引是没有匹配“最左匹配原则”，这里指的不是顺序，

例如我们根据user表中的A,B,C创建一个符合索引
比如建立了bcd的联合索引，但是where语句中只有 c,d，是用不了这个索引的，
跳过某一列，索引将部分失效(后面的字段索引失效) 例如下面的(b,d)

换句话说 如果要想用这个创建的联合索引，那么就要保证最左边的列存在，并且中间没有跳过字段

而（b,c,d）索引又可以看成创建了三个索引

(b),
(b,c),
(b,c,d)，
失效：(b,d),(c,d)

同时在where语句中b,c,d,的顺序没有要求，sql在执行时会进行重排优化
假如你从c 或者d开始查 就没有用到索引

select C from user while c = ‘c’

2、联合索引内范围查找

在联合索引中出现范围查询( > < ) 范围查询右侧的索引列失效
例如 下面的联合索引中的age使用了范围查询 那么status没有走索引，但是如果使用>= 或者<= 就能规避

//这种情况就没有使用到索引
select *
form tmp
where e>1 and b=1 and c=1

3、使用or关键字用不了索引

用or分开的条件 如果or左侧的条件有索引，or后面没索引 那么涉及到的索引都不生效，除非两次都用上了索引

//用不了索引的情况
select *
from tmp
where b=1 or a=1

//修改为
select * from tmp where b=1
union
select * from tmp where a=1

4、索引列有运算

select *
form tmp
where  b+1=2 and c=1

5.模糊查询 like

尾部模糊匹配，like ‘某某值 %’ ，索引不失效
头部模糊匹配，like ‘% 某某值’ ，索引失效

6.字符串类型没有加 ’ ’

在查询中，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转化，造成索引失效

7、select * 可能导致失效

查询全部的字段，有一些字段可能没有建立索引，导致需要去表中查询(回表查询)，所以会导致索引失效

8.in ， no in ，null ，not null 有时候

因为我们索引的目的是为了快速定位，如果值全是null 这个时候，索引就没有意义，直接走全表查询，如果只有一个null ，那就可以快速的定位出来

9.避免在 where 子句中对字段进行表达式操作

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100 
应改为: 
select id from t where num=100*2 

索引优化

覆盖索引

尽量使用覆盖索引(查询使用到的索引列 结果返回的字段中 在该索引中已经全部能够找到 减少使用select*)

例如使用 select * 就一般要走回表查询

思考：

建立username 和password的联合索引 如果只是username单列索引那么查询password这个字段时需要回表
而建立username 和password的联合索引 就能拿到id 不用再回表

单列索引和联合索引