# 索引规约


红色 * 代表强制,必须遵循!
看到一条分割线,即代表一个点结束

# 先贴上索引口诀:

  • 全值匹配我最爱,最左前缀要遵守
  • 带头大哥不能死,中间兄弟不能断
  • 索引列上少计算,范围之后全失效
  • LIKE符号写最右,覆盖索引不写星
  • 不等空值还有or,索引失效要少用
  • var引号不能丢,SQL高级也不难
  • 分组之前必排序,一定要上索引啊

# IndexStatute

  • * 业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引

说明: 不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。

  • * 超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引。

说明: 即使双表 join 也要注意表索引、SQL 性能。
注意: 若查询出来的数据条数只是整个表的少部分数据,请使用嵌套查询(子查询);若查询出来条数可能占整个表的70%以上,请使用join关联表

* 正例: 
如果我只要找某几个学生的数据,我只需要用子查询逐个逐个找出来。而不是将全部学生的数据join一遍
  • * 在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据 实际文本区分度决定索引长度

说明: 索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

# 索引长度与区分度矛盾关系

  • 索引长度太短,那么区分度就很低,索引占内存越低,
  • 索引长度太长,区分度就高,索引占内存越多,
  • 所以需要找到一个平衡点;
  • 那么怎么找整个平衡点呢?
  • 首先你要知道 mysql的索引都是排好序的。如果区分度高排序越快,区分度越低,排序慢;
* 正例: 
(张,张三,张三哥),如果索引长度取1的话,那么每一行的索引都是 张 这个字,完全没有区分度,你让他怎么排序?
结果这样三行完全是随机排的,因为索引都一样;

如果长度取2,那么排序的时候至少前两个是排对了的,如果取3,区分度达到100%,排序完全正确;

等等,那你说是不是索引越长越好?  答案肯定是错的,比如 (,,)  和 (张三啦啦啦,张三呵呵呵,张三呼呼呼);
前者在内存中排序占得空间少,排序也快,后者明显更慢更占内存,在大数据应用中这一点点都是很恐怖的;

所以要做一个取舍;这个取舍不是没有一个固定的量;需要跟你自己的数据库里面的数据来判断;比较常规的公式是:
name是要加索引的字段,5是索引长度
select count(distinct left(name,5))/count(*) from table; 

求出一个浮点数,这个浮点数是逐渐趋向1的,网上找了个图片来分析下;

这个地方观察到,当索引长度达到4的时候就已经趋向1了,所以长度设为4是最佳的,在大点增加的索引效果已经很小了,这个地方不是说必须接近1才行;

其实这个值达到0.1就已经可以接受了;总之要找一个平衡点;

  • 还有一些特殊的字段常规方法用起不太顺畅,比如有一个url字段,绝大部分的url都是 http://www. 开头的
  • 这种情况下索引长度取取到11都是无效的,需要更长的索引,那么有没有优雅的方式来解决呢;
  • 方法一:可以将数据倒序存入数据库;
  • 方法二:对字符串进行crc32哈希处理;

  • * 页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。

说明: 索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引,全模糊也会失效。全模糊使用elasticsearch才能解决这个问题

既然用了左模糊就无法走索引,那么我即想左模糊也想走索引?

现有user_info表数据结构如下:

ID USER_NAME AGE
1 易杰 70
100 李荣杰 70
110 张杰 17
2 钱杰 38

此表有一个USER_NAME字段的索引 idx_user_name

有个业务需求,需要模糊搜索出用户名后几位有这个词的所有用户信息,这时候不可能说为了一个搜索就引入ES,但是如果sql使用左模糊查询的话,根据索引的最左匹配原则,该sql语句是不可能使用到idx_user_name索引的,如下:

SELECT * from USER_INFO where USER_NAME like '%杰'

发现是用不到索引的。需要做模糊匹配,又要用到索引,索引的最左匹配原则更是不能被打破,这时可增加一个字段,这个字段的内容等于USER_NAME字段内容的反转,同时加上这个字段的相关索引,如下:

ID USER_NAME AGE REVERSE_USER_NAME
1 易杰 70 杰易
100 李荣杰 70 杰荣李
110 张杰 17 杰张
2 钱杰 38 杰钱

此时如果是要模糊搜索出用户名后几位有杰这个词的所有用户信息,可对REVERSE_USER_NAME字段做右模糊查询,效果其实就是和对USER_NAME字段做左模糊查询是一样的,因为二者的内容是相反的,如下:

SELECT * from USER_INFO where REVERSE_USER_NAME like '杰%'

说明: 索引的最左匹配原则不能打破,那么要让左匹配也走索引的话,换个思路,让右匹配的效果和左匹配一样就好了,同时右匹配又能走索引,间接达到了左模糊查询也能走索引的目的。


  • 如果有 order by 的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort(外部排序,就是把select的数据,按照order by的列 ,在内存中进行一次排序,然后返回结果) 的情况,影响查询性能
* 正例: where a=? and b=? order by c; 索引:a_b_c(先order by a排序,再按b,再按c排序;组合索引本来就是排序过的)

* 反例: 索引如果存在范围查询,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b; 索引 a_b 无法排序。

  • 利用覆盖索引来进行查询操作,避免回表。(SQL优化)

说明: 如果一本书需要知道第 11 章是什么标题,会翻开第 11 章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用。

* 正例: 能够建立索引的种类分为主键索引、唯一索引、普通索引三种,而覆盖索引只是一种查询的一种效
果,用 explain 的结果,extra 列会出现:using index。

什么是回表查询?

这先要从InnoDB的索引实现说起,InnoDB有两大类索引:

  • 聚集索引(clustered index)
  • 普通索引(secondary index)

# InnoDB聚集索引和普通索引有什么差异?

  • InnoDB聚集索引的叶子节点存储行记录,因此, InnoDB必须要有,且只有一个聚集索引:

    如果表定义了PK,则PK就是聚集索引;
    如果表没有定义PK,则第一个not NULL unique列是聚集索引;
    否则,InnoDB会创建一个隐藏的row-id作为聚集索引;
    所以PK查询非常快,直接定位行记录。

  • InnoDB普通索引的叶子节点存储主键值

    注意,不是存储行记录头指针,MyISAM的索引叶子节点存储记录指针。

举个栗子,不妨设有表: t(id PK, name KEY, sex, flag);

id是聚集索引,name是普通索引。

表中有四条记录:

ID USER_NAME M TYPE
1 shenjian m A
3 zhangsan m A
5 lisi m A
9 wangwu f B

  • 两个B+树索引分别如上图:
    • id为PK,聚集索引,叶子节点存储行记录;
    • name为KEY,普通索引,叶子节点存储PK值,即id;

既然从普通索引无法直接定位行记录,那普通索引的查询过程是怎么样的呢?

通常情况下,需要扫码两遍索引树。

例如:select * from t where name='lisi';

是如何执行的呢?

  • 如粉红色路径,需要扫码两遍索引树:
    • 先通过普通索引定位到主键值id=5;
    • 在通过聚集索引定位到行记录;

这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。

什么是索引覆盖(Covering index)?

MySQL官网,类似的说法出现在explain查询计划优化章节,即explain的输出结果Extra字段为Using index时,能够触发索引覆盖。

不管是SQL-Server官网,还是MySQL官网,都表达了:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

如何实现索引覆盖?

将被查询的字段,建立到联合索引里去。

create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name)
)engine=innodb;

请看第一条sql:
select id,name from user where name='shenjian'; 
能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。
ExtraUsing index。聚集索引

请看第二条sql:
select id,name,sex from user where name='shenjian';
能够命中name索引,索引叶子节点存储了主键id,但sex字段必须回表查询才能获取到,不符合索引覆盖,
需要再次通过id值扫码聚集索引获取sex字段,效率会降低。
ExtraUsing index condition。普通索引

如果把(name)单列索引升级为联合索引(name, sex)就不同了。
create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name, sex)
)engine=innodb;

select id,name ... where name='shenjian';
select id,name,sex ... where name='shenjian';
都能够命中索引覆盖,无需回表。
ExtraUsing index。

哪些场景可以利用索引覆盖来优化SQL?

场景1:全表count查询优化

原表:user(PK id, name, sex);

select count(name) from user;
不能利用索引覆盖。

添加索引:alter table user add key(name);
就能够利用索引覆盖提效。

场景2:列查询回表优化

select id,name,sex ... where name='shenjian';

将单列索引(name)升级为联合索引(name, sex),即可避免回表。

场景3:分页查询

select id,name,sex ... order by name limit 500,100;

将单列索引(name)升级为联合索引(name, sex),也可以避免回表。

InnoDB聚集索引普通索引,回表,索引覆盖


  • 利用延迟关联或者子查询优化超多分页场景

说明: MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL改写
注意: 延迟关联:使用索引查询出来数据,之后把查询结果和同一张表中数据进行连接查询,进而提高查询速度!

* 正例: 先快速定位需要获取的 id 段,然后再关联:
子查询通过id主键索引查出来,在和同一张表的数据进行连接

SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 
where t1.id=t2.id
  • SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好

说明:
consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
ref 指的是使用普通的索引(normal index)。
range 对索引进行范围检索

* 反例: explain 表的结果,type=index,索引物理文件全扫描,速度非常慢,这个 index 级别比较 range
还低,与全表扫描是小巫见大巫。
  • 建组合索引的时候,区分度最高的在最左边
* 正例: 如果 where a=? and b=?,a 列的几乎接近于唯一值,那么只需要单建 idx_a 索引即可。

说明: 存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更高,也必须把 d 放在索引的最前列,即建立组合索引 idx_d_c。

  • 创建索引时避免有如下极端误解:

索引宁滥勿缺。认为一个查询就需要建一个索引。
吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。


参考文档