mysql索引模型_MySQL索引的原理及使用

最新推荐文章于 2024-08-26 05:00:00 发布

富聰

最新推荐文章于 2024-08-26 05:00:00 发布

阅读量137

点赞数

文章标签： mysql索引模型

本文链接：https://blog.csdn.net/weixin_34582773/article/details/113194948

版权

从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。

节点的分裂和合并，其实就是 InnoDB 页的分裂和合并。

2.5. B+树(加强版多路平衡查找树)

B Tree 的效率已经很高了，为什么 MySQL 还要对 B Tree 进行改良，最终使用了B+Tree 呢？总体上来说，这个 B 树的改良版本解决的问题比 B Tree 更全面。

我们来看一下 InnoDB 里面的 B+树的存储结构：

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

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

2、B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一

层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。

举个例子：假设一条记录是 1K，一个叶子节点(一页)可以存储 16 条记录。非叶子节点可以存储多少个指针？假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为6 字节，这样一共 14 字节。非叶子节点(一页)可以存16384/14=1170 个这样的单元(键值+指针)，代表有 1170 个指针。树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为1170*1170*16=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

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

4、它是根据左闭右开的区间 [ )来检索数据。

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

1)比如我们要查找 28，在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下搜寻，28 是[28,66)的左闭右开的区间的临界值，所以会走中间的子节点，然

后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后在叶子节点上找到了需要的数据。

2)第二个，如果是范围查询，比如要查询从 22 到 60 的数据，当找到 22 之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高

了区间查询效率(不需要返回上层父节点重复遍历查找)。

总结一下，InnoDB 中的 B+Tree 的特点：

1)它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题是什么？(每个节点存储更多关键字；路数更多)

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

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

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

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

2.6. 为什么不用红黑树？

红黑树也是 BST 树，但是不是严格平衡的。

必须满足 5 个约束：

1、节点分为红色或者黑色。

2、根节点必须是黑色的。

3、叶子节点都是黑色的 NULL 节点。

4、红色节点的两个子节点都是黑色(不允许两个相邻的红色节点)。

5、从任意节点出发，到其每个叶子节点的路径中包含相同数量的黑色节点。

插入：60、56、68、45、64、58、72、43、49

基于以上规则，可以推导出：

从根节点到叶子节点的最长路径(红黑相间的路径)不大于最短路径(全部是黑色节点)的 2 倍。

为什么不用红黑树？1、只有两路；2、不够平衡。

红黑树一般只放在内存里面用。例如 Java 的 TreeMap。

2.7. 索引方式：真的是用的 B+Tree 吗？

在 Navicat 的工具中，创建索引，索引方式有两种，Hash 和 B Tree。

HASH：以 KV 的形式检索数据，也就是说，它会根据索引字段生成哈希码和指针，指针指向数据。

哈希索引有什么特点呢？

第一个，它的时间复杂度是 O(1)，查询速度比较快。因为哈希索引里面的数据不是按顺序存储的，所以不能用于排序。

第二个，我们在查询数据的时候要根据键值计算哈希码，所以它只能支持等值查询(= IN)，不支持范围查询(> < >= <= between and)。另外一个就是如果字段重复值很多的时候，会出现大量的哈希冲突(采用拉链法解决)，效率会降低。

InnoDB 可以在客户端创建一个索引，使用哈希索引吗？

官网：https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html

InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature

直接翻译过来就是：InnoDB 内部使用哈希索引来实现自适应哈希索引特性。

这句话的意思是 InnoDB 只支持显式创建 B+Tree 索引，对于一些热点数据页，InnoDB 会自动建立自适应 Hash 索引，也就是在 B+Tree 索引基础上建立 Hash 索引，

这个过程对于客户端是不可控制的，隐式的。我们在 Navicat 工具里面选择索引方法是哈希，但是它创建的还是 B+Tree 索引，这个不是我们可以手动控制的。

因为B Tree 和B+Tree 的特性，它们广泛地用在文件系统和数据库中，例如Windows的 HPFS 文件系统，Oracel、MySQL、SQLServer 数据库。

3. B+Tree 落地形式

3.1. MySQL 架构

MySQL 是一个支持插件式存储引擎的数据库。在 MySQL 里面，每个表在创建的时候都可以指定它所使用的存储引擎。这里我们主要关注一下最常用的两个存储引擎，MyISAM 和 InnoDB 的索引的实现。

3.2. MySQL 数据存储文件

首先，MySQL 的数据都是文件的形式存放在磁盘中的，我们可以找到这个数据目录的地址。在 MySQL 中有这么一个参数，我们来看一下：

show VARIABLES LIKE 'datadir';

每个数据库有一个目录，我们新建了一个叫做 gupao 的数据库，那么这里就有一个gupao 的文件夹。

这个数据库里面我们又建了 5 张表：archive、innodb、memory、myisam、csv。

我们进入 gupao 的目录，发现这里面有一些跟我们创建的表名对应的文件。

在这里我们能看到，每张 InnoDB 的表有两个文件(.frm 和.ibd)，MyISAM 的表有三个文件(.frm、.MYD、.MYI)。

有一个是相同的文件，.frm。 .frm 是 MySQL 里面表结构定义的文件，不管你建表的时候选用任何一个存储引擎都会生成。我们主要看一下其他两个文件是怎么实现 MySQL 不同的存储引擎的索引的。

4.2.1.MyISAM

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

一个是.MYD 文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录，比如我们的 user_myisam 表的所有的表数据。

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

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

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

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

这里是主键索引，如果是辅助索引，有什么不一样呢？

在 MyISAM 里面，辅助索引也在这个.MYI 文件里面。

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

4.2.2.InnoDB

InnoDB 只有一个文件(.ibd 文件)，那索引放在哪里呢？

在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd 文件里面。

在 InnoDB 的主键索引的叶子节点上，它直接存储了我们的数据。

什么叫做聚集索引(聚簇索引)？

就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。(比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引)。

在 InnoDB 里面，它组织数据的方式叫做叫做(聚集)索引组织表(clustered indexorganize table)，所以主键索引是聚集索引，非主键都是非聚集索引。如果 InnoDB 里面主键是这样存储的，那主键之外的索引，比如我们在 name 字段上面建的普通索引，又是怎么存储和检索数据的呢？

InnoDB 中，主键索引和辅助索引是有一个主次之分的。

辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。比如我们用 name 索引查询 name= '青山'，它会在叶子节点找到主键值，也就是id=1，然后再到主键索引的叶子节点拿到数据。

为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢？如果主键的数据类型比较大，是不是比存地址更消耗空间呢？

我们前面说到 B Tree 是怎么实现一个节点存储多个关键字，还保持平衡的呢？是因为有分叉和合并的操作，这个时候键值的地址会发生变化，所以在辅助索引里

面不能存储地址。

另一个问题，如果一张表没有主键怎么办？

1、如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。

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

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

select _rowid name from t2;

4. 索引使用原则

我们容易有以一个误区，就是在经常使用的查询条件上都建立索引，索引越多越好，那到底是不是这样呢？

4.1. 列的离散(sàn)度

第一个叫做列的离散度，我们先来看一下列的离散度的公式：count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。

简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。了解了离散度的概念之后，我们再来思考一个问题，我们在 name 上面建立索引和

在 gender 上面建立索引有什么区别。当我们用在 gender 上建立的索引去检索数据的时候，由于重复值太多，需要扫描的行数就更多。例如，我们现在在 gender 列上面创建一个索引，然后看一下执行计划。

ALTER TABLE user_innodb DROP INDEX idx_user_gender;

ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); --耗时比较久

EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0;

而 name 的离散度更高，比如“青山”的这名字，只需要扫描一行。

查看表上的索引，Cardinality [kɑ:dɪ'nælɪtɪ] 代表基数，代表预估的不重复的值

show indexes from user_innodb;

如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引

4.2. 联合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引。单列索引可以看成是特殊的联合索引。比如我们在 user 表上面，给 name 和 phone 建立了一个联合索引。

ALTER TABLE user_innodb DROP INDEX comidx_name_phone; ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的(name 在左边，phone 在右边)。从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有序的。这个时候我们使用 where name= '青山' and phone = '136xx '去查询数据的时候，B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name相同的时候再比较 phone。但是如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

5.2.1.什么时候用到联合索引

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

比如下面的三条语句，能用到联合索引吗？

1)使用两个字段，可以用到联合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮' AND phone = '15204661800';