MySQL 索引的使用

哈希表存储

​ 哈希表将Key通过一个固定的算法函数（哈希函数）转换成一个整型数字，然后就将该数字对数组长度进行取余，取余结果就当作数组的下标，将value存储在以该数字为下标的数组空间里。

index = HashCode (Key) % Array.length

哈希表访问

计算哈希值取余在相应位置进行寻找

哈希碰撞

通俗的说，可能有很多的值进行取余运算后结果相同，他们计算的hash值是相同的。所以它们的映射在同一个位置。

解决办法：

在相同的位置用链表进行存储，

MySQL中的hash索引

Hash 索引比较的是进行 Hash 运算之后的 Hash 值，只能用于等值的过滤，不能用于基于范围的过滤。经过相应的 Hash 算法处理之后的Hash 值的大小关系，并不能保证和Hash运算前完全一样，数据库自然也无法利用索引的数据来避免任何排序运算

优点：

Hash效率高，查找快

缺点：

• Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。

• Hash 索引无法被用来避免数据的排序操作。

• Hash 索引不能利用部分索引键（组合索引）查询。

• Hash 索引在任何时候都不能避免表扫描。

• Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

B-Tree索引

---

BTree 索引是 MySQL 数据库中使用最为频繁的索引类型。大多数索引（如 PRIMARYKEY,UNIQUE,INDEX和FULLTEXT）都是 以BTREE形式存储。

B-Tree索引可以被用在像=,>,>=,<,<=和BETWEEN这些比较操作符上,而且还可以用于LIKE操作符,只要它的查询条件是一个不以通配符开头的常量。

BTree平衡多路查找树

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

数据存储页的概念

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，

在MySQL中可通过如下命令查看页的大小

show variables like ‘innodb_page_size’;

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。

B-Tree结构特点

一棵m阶的B-Tree有如下特性：

阶数 表示 此树的节点 最多 有多少个孩子结点（子树），一般用字母 M 表示阶数

• 每个节点最多有m个孩子。

• 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。

• 若根节点不是叶子节点，则至少有2个孩子

• 所有叶子节点都在同一层，且不包含其它关键字信息

• 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）

• 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1

• ki(i=1,…n)为关键字，且关键字升序排序。

• Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)。

BTree用作数据库索引优点：

1. 优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；

2. 尽可能少的磁盘 IO，加快了检索速度；

3. 可以支持范围查找

如下就是阶数为3的B树

BTree分析

为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的BTree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。

如上图查找29只需要三次磁盘IO操作

从上图可以看出：

BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

所以我们引出了B+TREE

B+TREE

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+Tree优化思想

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于BTree有几点不同：

1. 非叶子节点只存储键值信息

2. 所有叶子节点之间都有一个链指针

3. 数据记录都存放在叶子节点中

B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

B+Tree的优点

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。 也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

数据库中，B+Tree的高度一般都在2 ~ 4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作

数据库中的B+Tree索引分类：

• 聚集索引（clustered index）：叶子节点存放的是整张表的行记录数据

• 辅助索引（secondary index）：叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键，当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据

索引最终选择B+树的原因

• hash很快，但每次IO只能取一个数

• AVL和红黑树，在大量数据的情况下，IO操作还是太多

• B树每个节点内存储的是数据，因此每个节点存储的分支太少

• B+节点存储的是索引+指针(引用指向下一个节点)，可以存储大量索引，同时最终数据存储在叶子

• 节点，并且有引用横向链接，可以在2-3次的IO操作内完成千万级别的表操作。

• 建议索引是是自增长数字，这样适合范围查找

索引分类

---

MySql中索引可以分为以下几类：

​ 普通索引（单列索引）

​ 复合索引（组合索引）

​ 唯一索引

​ 主键索引

​ 全文索引

查看索引

Show index|keys from 表名；

创建索引

---

普通索引（单列索引）：

单列索引是最基本的索引，它没有任何限制。

直接创建索引

CREATE INDEX index_name ON table_name(col_name);

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

创建表的时候同时创建索引

CREATE TABLE news (

id int(11) NOT NULL AUTO_INCREMENT ,

title varchar(255) NOT NULL ,

PRIMARY KEY (id),

INDEX index_name (title(255))

)

删除索引

DROP INDEX index_name ON table_name;

或者

alter table 表名 drop index 索引名;

复合索引：

复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引

的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

创建一个复合索引

create index index_name on table_name(col_name1,col_name2,…);

修改表结构的方式添加索引

alter table table_name add index index_name(col_name,col_name2,…);

基于(列Ａ，列Ｂ)两列创建索引：

• 可应用索引的情况：A;AB：BA两列结合;

• 不应用索引的情况：Ｂ

唯一索引：

唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值。

创建单个索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);

创建多个索引

CREATE UNIQUE INDEX index_name on table_name(col_name,…);

修改表结构

单个

ALTER TABLE table_name ADD UNIQUE index index_name(col_name);

多个

ALTER TABLE table_name ADD UNIQUE index index_name(col_name,…);

如果在已经有数据的表上添加唯一性索引的话：

如果添加索引的列的值存在两个或者两个以上的空值，则不能创建唯一性索引会失败。（一般在创建表的时候，要对自动设置唯一性索引，需要在字段上加上 not null）如果添加索引的列的值存在两个或者两个以上的null值，还是可以创建唯一性索引，只是后面创建的数据不能再插入null值 ，并且严格意义上此列并不是唯一的，因为存在多个null值。

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

创建表时添加

CREATE TABLE news (

id int(11) NOT NULL AUTO_INCREMENT ,

title varchar(255) NOT NULL ,

content varchar(255) NULL ,

time varchar(20) NULL DEFAULT NULL ,

PRIMARY KEY (id)

)

创建表后添加

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Java开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。**

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

[外链图片转存中…(img-GC5iNoRt-1715844017566)]

[外链图片转存中…(img-vbOdBgrp-1715844017567)]

[外链图片转存中…(img-kAlQ2HUh-1715844017567)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Java开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！