MySQL索引及其实现原理_mysql索引的原理和实现-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42453582/article/details/116120159

1、索引是帮助MySQL高效获取数据的排好序的数据结构
点击访问：索引动态数据结构网页版（谷歌浏览器）
在这里插入图片描述
2、索引的数据结构
（1）二叉树

– 查询 select * from t where t.col2 = 89; 语句时，原始的查询方式如图左部分从第一行开始取数据对比，直到查询到第六行才找到目标数据。
– 当给表Col2列添加索引后，数据的存储方式为二叉树（左孩子<根节点<右孩子）时，数据从根节点开始查找对比，只需要两次就能够查到K=89的节点，然后存储为K-V的方式通过V找到此条数据地址。

（2）红黑树：是一种平衡二叉查找树的变体，它的左右子树高差有可能大于 1，所以红黑树不是严格意义上的平衡二叉树
当采用二叉树存储Col1列时，其存储方式为在这里插入图片描述
而采用红黑树其存储方式为
显然原二叉树深度为7，而采用红黑树存储其深度为4。当再次查找K=6时，查找次数由原来的6次变为3次，显然此种方式对二叉树查找进行了一定程度的优化。但是它也具有明显的缺点：当数据量很大时，红黑树所具有的层数太多(二叉树深度太大)，造成每一次从根节点往下搜索到叶子节点依旧很耗时。解决此问题的方案就是B-Tree数据结构，即通过横向增加节点的存储来对二叉树“扩容”。

（3）Hash表
A、哈希索引
哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
对于hash相同的，采用链表的方式解决冲突。类似于hashmap。因为索引的结构是十分紧凑的，所以hash索引的查询很快。

B、hash索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
哈希索引只支持等值比较查询，包括=、IN()、<>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100。
访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

C、自适应哈希
在Mysql中InnoDB引擎有一个特殊的功能叫做自适应哈希索引，它会在内存中基于B-Tree索引的基础上面创建一个哈希索引，这让B-Tree索引具备了一些哈希索引的优点。

（4）B-Tree

叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左往右递增排列

图为最大程度为3的B-Tree存储1-10数据二叉树的存储方式：在这里插入图片描述
MySQL底层索引所使用的存储形式为B+Tree（B-Tree变种）：

非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
叶子结点包含所有索引字段
叶子结点用指针连接，提高区间访问的性能

图为最大程度为3的B+Tree存储1-10数据二叉树的存储方式：在这里插入图片描述
MySQL索引底层存储分析：

非叶子节点（根节点+非根非叶子节点。其中根节点是存储在内存中，非根非叶子节点需要去磁盘实时查找）：
叶子结点

(叶子结点之间的箭头为双向指针,且从头指针到尾指针数据都是顺序从小到大维护好的)
可见B+Tree存储三层树就可以存储2000多万数据大小为1KB的索引元素，效率非常高效。

3、MySQL存储引擎
存储引擎是数据库的核心，对于mysql来说，存储引擎是以插件的形式运行的。默认支持的也是InnoDB。在这里插入图片描述
（1）MyISAM(目前很少用)
使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

frm文件：存储表的定义数据
MYD文件：存放表具体记录的数据
MYI文件：存储索引’

frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，B+Tree结构来查找记录页，再根据记录页查找记录。并且支持全文索引、B树索引和数据压缩。

☆ MyISAM索引文件和数据文件是分离的(非聚集)：
在这里插入图片描述
（2）InnoDB(目前流行)
InnoDB是默认的数据库存储引擎，主要特点有：

可以通过自动增长列，方法是auto_increment
支持事务。默认的事务隔离级别为可重复读，通过MVCC（并发版本控制）来实现的
使用的锁粒度为行级锁，可以支持更高的并发
支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度
配合一些热备工具可以支持在线热备份
在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+Tree的叶子节点上

当然InnoDB的存储表和索引也有下面两种形式：

使用共享表空间存储：所有的表和索引存放在同一个表空间中
使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率

对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。

☆ InnoDB索引实现聚集(叶子节点包含了完整的数据记录)：在这里插入图片描述
另外InnoDB表必须有主键(其数据表通过B+Tree维护，而B+Tree非叶子结点需要的数据列具有唯一性更有利于存储索引)，并且推荐使用整型的自增主键。因为这样对表执行插入操作时防止底层数据节点分列再平衡帮助是很大的。

（3）Memory
将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联，文件是frm。

支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型
支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈
由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失
查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低
默认使用hash索引
如果一个内部表很大，会转化为磁盘表

在这里只是给出3个常见的存储引擎。使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

4、索引最左前缀原理
（1）联合索引
底层实现依然是B+Tree：
在这里插入图片描述
当联合索引作为查询条件时只带了后两个字段name和date，而未带id，则此时不会走索引。因为索引之间的排序是按照联合索引子字段从左到右比较排序的，当查询条件不带第一个字段，直接从第二个字段开始，则B+Tree无法根据顺序进行搜索，需要进行全表扫描搜索条件，故不会再触发索引。
当查询条件为id和date，查询会走索引，不过索引只查id不会查date，date依然需要对id过滤后的数据进行全部扫描。

5、索引的类型
MySQL目前主要有以下几种索引类型：

（1）普通索引(INDEX)
是最基本的索引，它没有任何限制。
A、直接创建索引

CREATE INDEX index_name ON table(column(length))

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

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

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

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

D、删除索引

DROP INDEX index_name ON table

（2）唯一索引( UNIQUE INDEX)
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
A、创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

B、修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

C、创建表的时候直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

（3）主键索引(PRIMARY KEY)
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

（4）组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

（5）全文索引(FULLTEXT INDEX)
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

A、创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

B、修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

C、直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)