文章目录
一、索引分类
-
聚集索引(Clustered Index)
聚集索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据。 -
主键索引
索引列中的值必须是唯一的,不允许有空值。 -
普通索引
MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值。 -
唯一索引
索引列中的值必须是唯一的,但是允许为空值。 -
全文索引
只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。 -
空间索引
MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。 -
前缀索引
在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时,可以指定索引列的长度,但是数值类型不能指定。 -
辅助索引
辅助索引,也叫非聚集索引。和聚集索引相比,叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点的索引行还包含了一个书签(bookmark),该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。
二、为什么使用B+树作为索引结构?
B-树
B+树
B+树相对于B-树来说,叶子结点存放数据,这么做的好处是为了提高范围查找的效率,除此之外,一个磁盘块的大小是固定的,如果像B-树那样,在非叶子结点放数据,磁盘放不了多少指向,树会很深,I/O效率就会低。
使用Hash作为索引结构怎么样?
可以直接对‘鸡蛋’按哈希算法算出来一个数组下标,然后可以直接从数据中取出数据并拿到所对应那一行数据的地址,进而查询那一行数据, 那么如果现在执行下面的sql语句:
select * from sanguo where name>‘鸡蛋’
则无能为力,因为哈希表的特点就是可以快速的精确查询,但是不支持范围查询。
所以哈希表是适合于查询的场景,就只有KV(Key,Value)的情况,例如Redis、Memcached等这些NoSQL的中间件。
使用二叉树作为索引结构怎么样?
索引也不只是在内存里面存储的,还是要落盘持久化的,可以看到图中才这么一点数据,如果数据多了,树高会很高,查询的成本就会随着树高的增加而增加。
为了节约成本很多公司的磁盘还是采用的机械硬盘,这样一次千万级别的查询差不多就要10秒了,这谁顶得住啊?
三、聚集索引与非聚集索引
- 聚簇索引:将数据存储的顺序与索引顺序相同,找到索引也就找到了数据
- 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,本质上非聚簇索引存储的是聚簇索引的值,比如主键ID
假设一张表有主键和age列,都建了索引,那么在Innodb引擎中,就对应了两颗B+树。主键对应的那颗树存了记录的全部数据,而age列索引的叶子结点中只存了age->主键的关系。
每个InnoDB表具有一个特殊的索引称为聚簇索引(也叫聚集索引,聚类索引,簇集索引)。
- 如果表上定义有主键,该主键索引就是聚簇索引。
- 如果未定义主键,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚簇索引。如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。
表中的聚簇索引(clustered index )就是一级索引,除此之外,表上的其他非聚簇索引都是二级索引,又叫辅助索引(secondary indexes)。
主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。
聚簇索引( 主键索引)和非聚簇索引(非主键索引)有什么区别?
- 非主键索引的叶子节点存放的是主键的值,而主键索引的叶子节点存放的是整行数据,其中非主键索引也被称为二级索引,而主键索引也被称为聚簇索引。
四、什么是回表?
上面我介绍了两个索引对应了两颗B+树,对应非聚簇索引的那颗树,其实叶子结点没有存所有的记录,存的是到ID的映射。
回表是我们有个主键为ID的索引,和一个普通name字段的索引,我们在普通字段上搜索:
select * from table where name = ‘hello’
执行的流程是先查询到name索引上的“hello”,然后找到他的id是2,最后去主键索引,找到id为2对应的值。
回到主键索引树搜索的过程,就是回表。
归根到底是因为,普通索引无法直接定位行记录。
覆盖索引如何避免回表?
用覆盖索引,实现覆盖索引的方法是:将被查询的字段,建立到联合索引里去。
读取索引,而无需读表,极大减少数据访问量。
联合索引事示意如下:
五、非聚簇索引一定会回表查询吗?
不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询。
这个好理解,如果全部命中了索引,你就拿到了所有你想要的字段数据,还回什么表?
举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20
的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询。
六、什么是最左匹配原则?
-
MySQL会一直向右匹配直到遇到范围查询 (>,<,BETWEEN,LIKE)就停止匹配。
-
如有联合索引 (a,b,c,d),查询条件
a=1 and b=2 and c>3 and d=4
,则会在每个节点依次命中a、b、c,无法命中d。(c已经是范围查询了,d肯定是排不了序了) -
如有联合索引 (a,b,c,d),对于下面,这两条都不会命中索引,因为不是从最左开始匹配的,
select * from table_name where b = 1
select * from table_name where b = 1 and c = 2
- 对于下面,只有a列使用索引,c列没有用到索引,因为中间跳过了b列,不是从最左开始连续匹配的。
select * from table_name where a = 1 and c = 2
- 对于下面列为字符串的情况,只有前缀匹配会用到索引,即只有在%在右边的情况下才会生效,中缀匹配和后缀匹配只能进行全表扫描。
select * from table_name where a like 'ab%';// 前缀匹配
select * from table_name where a like '%ab' // 中缀匹配
select * from table_name where a like '%ab%' // 后缀匹配
七、索引失效的情况
- 有or必全有索引;
- 联合索引未用左列字段,这对应着索引的最左匹配原则;
- like以%开头,即不是前缀匹配;
- 需要类型转换;
- where中索引列有运算或者使用了函数;
- 如果mysql觉得全表扫描更快时(数据少);
八、为什么建议使用主键自增的索引?
- 自增是有序的对吧,那么你插入ID的时候不用查询了,直接插入就行了
- 不然你为了插这条数据,又需要保证B+树的规则性,你肯定是有花销的
九、索引下推
最左前缀可以用于在索引中定位记录,那么,那些不符合最左前缀的部分,会怎么样呢?
以用户表的**联合索引(name, age)**为例,假设现在有一个需求,找出所有姓 “张” 并且 20 岁的男性:
select * from tuser where name like '张%' and age = 20 and sex = male
《高性能 MySQL》 书中提到:对于联合索引,如果查询中有某个列的范围查询,则其右边所有列都无法使用索引进行快速定位。
这是因为联合索引底层实际上是从左到右优先级排序的,即只有name相同时,age才有效
所以对于这条语句来说,其实并不能完全踩中 (name, age) 这个联合索引,他只能踩到 name。
具体来说,这个语句在搜索(name,age)的联合索引树的时候,并不会去看 age 的值,只是按顺序把 “name 第一个字是张” 的记录一条条取出来,然后开始回表,到主键索引上找出数据行,再一个一个判断其他条件是否满足。
-
MySQL 5.6 之前,当进行索引查询时,首先根据索引来查找记录,然后再根据 where 条件来过滤记录
-
MySQL 5.6 开始,数据库在取出索引的同时,会根据 where 条件直接过滤掉不满足条件的记录,减少回表次数,比如上面的例子(name,age),那么其实可以根据age再过滤一遍。这就是 索引下推 (Index Condition Pushdown,ICP) ,一种根据索引进行查询的优化方式。
十、关于联合索引的一些注意点
- 联合索引建立的时候,会默认在索引树上带上主键索引
参考
https://www.zhihu.com/question/52536048/answer/2264727222