索引优化规则

一、索引失败

①、前导模糊查询

select * from doc where title like '%XX'

②、负向条件!=、<>、not in、not exists、not like 等。

③、在字段上进行计算不能命中索引。

select * from doc where YEAR(create_time) <= '2016'

④、字段类型不对

#phone是varchar
select * from user where phone=13800001234

⑤、or、on

2边没有全索引

#user_id 无索引
select * from order where id=100 or user_id=1

⑥、不符合复合索引前缀原则

#联合索引 (a、b、c)
b=1 and c=1 失败
c=2 失败

⑦、范围列<、<=、>、>=、between、like等。

范围列后面的列无法用到索引

假如有联合索引 (a、b、c)，那么下面的 SQL 中 a可以用到索引，而 b和 c则使用不到索引。

select * from table where a< 100 and b=1 and c >1

⑧、mysql判断不需要索引更快

二、建立索引原则

①更新十分频繁、数据区分度不高的字段不建立索引

②一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

③频繁查询的字段优先考虑使用覆盖索引

被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

例如登录业务需求，代码如下。

select id, login_time from user where login_name=? and passwd=?

可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 id 和 login_time 就不用去 row 上获取数据了，从而加速查询。

三、SQL 性能优化explain

 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

①、consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据

②、ref：使用普通的索引

③、range：对索引进行范围检索

④、index：索引物理文件全扫，速度非常慢