Mysql原理之索引

Mysql原理之索引

索引简介

数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，我们要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。

但是我们有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

索引就相当于一本书的目录。

索引类型

在InnoDB里面，索引类型有三种，普通索引、唯一索引（主键索引是特殊的唯一索引）、全文索引。

普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。

唯一 （Unique）：唯一索引要求键值不能重复。另外需要注意的是、主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用primay key创建。

全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容、一篇文章，有几KB的数据的这种情况，如果要解决like查询在全文匹配的时候效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如char、varchar、text。

索引存储结构

lnnoDB使用B+树来存储索引：

MySQL中的B+Tree有几个特点：

1、它的关键字的数量是跟度数相等的；

2、B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据， 存放的是完整记录的地址。

搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索id=28公虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。

3、B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

我们来看一下B+Tree的数据搜寻过程：

1）比如我们要查找28，在节点就找到了键值，但是因为它不是叶子节点，所以会继续往下搜寻，28是[28，66）的左闭右开的区间的临界值，所以会走中间的子节点，然后继续搜索，它又是[28，34）的左闭右开的区间的临界值，所以会走左边的子节点，最后在叶子节点上找到了需要的数据。

2）第二个，如果是范围查询，比如要查询从22到60的数据，当找到22之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。

总结一下，InnoDB中的B+Tree特性带来的优势

1）它是BTree的变种，BTree能解决的问题，它都能解决。BTree解决的两大问题一是每个节点存储更多关键字，二是路数更多

2）扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵B+Tree拿到所有的数据）

3）B+Tree的磁盘读写能力相对于BTree来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）

4）排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）

5）效率更加稳定（B+Tree永远是在叶子节点拿到数据，所以IO次数是稳定的）

B+树的落地形式

MySQL数据存储文件

每张InnoDB的表有两个文件（.frm和.ibd），MylSAM的表有三个文件（.frm、.MYD、.MYI）。

.frm是 MySQL里面表结构定义的文件，不管建表的时候选用任何一个存储引擎都会生成。

MylSAM

在MyISAM里面，另外有两个文件：

个是.MYD文件，D代表Data，是MyISAM的数据文件，存放数据记录。

一个是.MYI文件，I代表Index，是MyISAM的索引文件，存放索引，比如我们在id字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。一个索引就会有一棵B+Tree，所有的B+Tree都在这个myi文件里面。

也就是说，在MyISAM里面，索引和数据是两个独立的文件。

那怎么根据索引找到数据呢？

MyISAM的B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。

非主键索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据，都存储在.MYI文件中。

InnoDB

在InnoDB的某个索引的叶子节点上，它直接存储了我们的数据。所以，为什么说在InnoDB中索引即数据，数据即索引，就是这个原因。

介绍一个叫做聚集索引（聚簇索引）的概念，就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。

InnoDB组织数据的方式就是（聚集）索引组织表（clustered index organize table）。如果说一张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的物理存储顺序。

比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引

那主键索引之外的索引，会不会也把完整记录在叶子节点放一份呢？

并不会，因为这会带来额外的存储空间浪费和计算消耗。

他们的叶子节点上没有数据怎么检索完整数据？

比如我们在name字段上面建的普通索引

InnoDB中，主键索引和辅助索引是有一个主次之分的。如果有主键索引，那么主键索引就是聚集索引，其他的索引统一叫做”二级索引”（secondary index）。

二级索引存储的是二级索引的键值，例如在name上建立索引，节点上存的是name的值，很明显，它的键值逻辑顺序跟物理行的顺序不一致。

而二级索引的叶子节点存的是这条记录对应的主键的值。比如id=1，id=4

所以，二级索引检索数据的流程是这样的：
当我们用name索引查询一条记录，它会在二级索引的叶子节点找到 name=q，拿到主键值，也就是id=1，然后再到主键索引的叶子节点拿到数据。

为什么不存地址而是存键值？因为地址会变化。

从这个角度来说，因为主键索引比二级索引少扫描了一棵B+Tree（避免了回表），它的速度相对会快一些。

但是，如果一张表没有主键怎么办？那完整的记录放在哪个索引的叶子节点？或者，这张表根本没有索引呢？数据放在哪里？

1、如果我们定义了主键（PRIMARYKEY），那么InnoDB会选择主键作为聚集索引。

2、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

3、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

索引使用原则

不建议在离散度低的数据上建立索引，如性别

联合索引最左匹配

有的时候我们的多条件查询的时候，也会建立联合索引，单列索引可以看成是特殊的联合索引。

联合索引在B+Tree中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的。

比如在user表上面，给name和phone建立了一个联合索引。

ALTER TABLE user innodb add INDEX comidx name_phone (name,phone);
ALTER TABLE user innodb DROP NDEX comidx name_phone

从这张图可以看出来，name是有序的，phone是无序的，当name相等的时候，phone才是有序的。

这个时候我们使用 where name=’q’ and phone =’136xx’去查询数据的时候，B+Tree会优先比较name来确定下一步应该搜索的方向，往左还是往右。如果name 相同的时候再比较phone。但是如果查询条件没有name，就不知道第一步应该查哪个节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。

所以，我们在建立联合索引的时候，一定要把最常用的列放在最左边。

覆盖索引

回表，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

在二级索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

索引条件下推（ICP）

索引条件下推（IndexCondition Pushdown），5.6以后完善的功能。只适用于二级索引。ICP的目标是减少访问表的完整行的读数量从而减少I/O操作。

这里说的下推，其实是意思是把过滤的动作在存储引擎做完，而不需要到Server层过滤。

ICP是默认开启的，也就是说针对于二级索引，只要能够把条件下推给存储引擎，它就会下推，不需要我们干预

set optimizer_switch='index_condition_ pushdown=on';

索引的创建与使用

索引的创建因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽最使用索引。

创建

1、在用于where判断order排序和join的（on）、group by的字段上创建索引

2、索引的个数不要过多，容易造成浪费空间，更新变慢。

3、过长的字段，建立前缀索引。

CREATE TABLE pre_test ( 
`content` varchar(20) DEFAULT NULL;
KEY `pre_idx' (`content`(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4、区分度低的字段，例如性别，不要建索引。

5、 频繁更新的值，不要作为主键或者索引，容易造成页分裂。

6、随机无序的值，不建议作为索引，例如身份证、UUID。 容易造成无序，分裂

7、组合索引把散列性高（区分度高）的值放在前面

8、创建复合索引，而不是修改单列索引

什么时候用不到索引

1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式计算（+-*/）

2、字符串不加引号，出现隐式转换

3、like条件中前面带% ，where条件中 like %2673%，like%888都用不到索引。

4、负向查询NOT LIKE 不能，!=、<>和NOT IN在某些情况下可以。

其实，用不用索引，最终都是优化器说了算。

优化器是基于什么的优化器？

基于cost开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer），也不是基于语义。

使用索引有基本原则，但是没有具体细则，没有什么情况一定用索引，什么情况一定不用索引的规则。

打赏