MySQL中索引详解

倾城璧

于 2024-08-03 01:31:03 发布

阅读量765

点赞数 22

分类专栏： MySQL基础知识文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2302_79993788/article/details/140600814

版权

MySQL基础知识专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.索引的概念

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

2.索引的优缺点

优点：

提高查询效率：索引可以显著减少查询时需要扫描的数据行数，从而加快查询速度
支持排序和分组：索引可以加快ORDER BY和GROUP BY的操作速度，因此MySQL可以利用索引来快速定位到排序和分组的数据。
加快表的连接：索引可以加快表与表之间的连接，尤其是在实现数据的参考完整性方面
查询优化：使用索引可以在查询过程中使用优化器，提高系统的性能

缺点：

时间成本：创建和维护索引需要时间，且随着数据量的增加，这种时间成本也会增加。
空间成本：索引也需要占用物理空间，数据量越大，所需的空间也越大。
降低更新效率：索引在表的增删改操作时需要动态维护，这可能会降低数据的维护速度。

3.索引的分类和使用方法

3.1 索引分类

单列索引是在表的一个列上创建索引。它可以提高查询性能，尤其是在对该列进行搜索，排序和分组时。

单列索引有下面三个类型：

普通索引：最基本的索引类型，没有唯一性限制
唯一索引：索引的值必须是唯一的，但允许有空值
主键索引：主键自动创建一个唯一索引

普通索引

下面是普通索引的一些操作：

create table empleyees (
  id int auto_increment PRIMARY KEY,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(100),
	department VARCHAR(50),
	salary DECIMAL(10,2)
	);
	
	insert into empleyees (first_name,last_name,email,department,salary) VALUES
	('张三', '李四', 'zhangsan@company.com', '技术部', 8000),
  ('王五', '赵六', 'wangwu@company.com', '市场部', 9000);
	
-- 	创建单列索引
CREATE INDEX idx_email on empleyees(email);
-- 查看索引
show index from empleyees;
-- 使用索引查询
SELECT* FROM empleyees where email ='zhangsan@company';

都知道索引查询能提高速度，那到底提高了多少呢？我们这里举个例子：

DELIMITER $$

CREATE PROCEDURE populate_employees()
BEGIN
   DECLARE i INT DEFAULT 0;

   WHILE i < 1000 DO
       INSERT INTO empleyees(id, first_name, last_name, email, department, salary)
       VALUES
           (NULL,  -- id is auto-increment so we leave it as NULL
            CONCAT('First', i),
            CONCAT('Last', i),
            CONCAT('email', i, '@example.com'),
            CONCAT('Department', i),
            50000.00); -- 假设一个默认薪水，你可以根据需要调整

       SET i = i + 1;
   END WHILE;

END$$

CALL populate_employees();

-- 无索引查询
SELECT* from empleyees where first_name=30;

-- 创建索引
CREATE INDEX index_text on empleyees(first_name);

 EXPLAIN SELECT* from empleyees where first_name=30;

在`EXPLAIN`输出中，“Extra”列将告诉你是否使用了索引。如果看到“Using index”，则表示使用了索引。同时，`rows`列显示了MySQL估计需要扫描的行数，通常有索引时这个数字会明显降低，从而加快查询速度。

唯一索引

唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

演示一下过程：

CREATE table user1(
   id int auto_increment primary key,
	 name VARCHAR(50) not null,
	 email VARCHAR(100) not null UNIQUE
	 );
	 
-- 	 插入数据
INSERT into user1(name,email)
VALUES('Alice','xxxx.com');

INSERT into user1(name,email)
VALUES('Bob','bob.com');

向这个表中插入数据，运行一下

当我们向其中插入相同的数据会报错

主键索引

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

CREATE table user1(
   id int auto_increment primary key,-- 添加主键索引
	 name VARCHAR(50) not null,
	 email VARCHAR(100) not null UNIQUE
	 );

组合索引

CREATE table user2(
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    ProductName VARCHAR(50)
	 );
	 
-- 创建组合索引
CREATE INDEX idx_customer_orderdate ON user2(CustomerID,OrderDate);

作用：

假设，如果我们经常需要查询某个客户在特定日期下的订单，我们可以创建一个组合索引在 CustomerID，OrderDate上。用组合索引可以加快速度。

示例：

假设我们需要找到客户编号为12345的所有2024年6月份的订单，使用组合索引可以使查询更快速。

SELECT * FROM user2
WHERE CustomerID = 12345 AND OrderDate BETWEEN '2024-06-01' AND '2024-06-30';

如果没有组合索引，数据库系统可能会进行全表扫描或者多次单列索引查找，这在数据量大的情况下效率特别低下。但是有了组合索引，数据库可以直接利用索引来定位。

全文索引

全文索引的作用

文本搜索：全文索引支持对文本字段进行关键字搜索。
提高搜索性能：全文索引可以显著提高搜索文本的速度。
支持复杂查询：全文索引通常支持复杂的查询语法，如布尔搜索（AND, OR, NOT等）、短语搜索等。

CREATE TABLE Articles (
    ArticleID INT PRIMARY KEY,
    Title VARCHAR(255),
    Content TEXT
);
为了支持全文搜索功能，我们需要在`Title`和`Content`字段上创建一个全文索引。

ALTER TABLE Articles ADD FULLTEXT (Title, Content);

我们可以使用全文索引来进行简单的关键词搜索，例如搜索包含单词 "technology" 的所有文章。

SELECT  FROM Articles WHERE MATCH(Title, Content) AGAINST ('technology');

全文索引也支持更复杂的搜索语法。例如，我们可以搜索同时包含 "technology" 和 "innovation" 的文章。

SELECT  FROM Articles WHERE MATCH(Title, Content) AGAINST ('technology innovation' IN BOOLEAN MODE);

如果你想精确匹配一个短语而不是单独的单词，你可以使用双引号来指定一个短语。

SELECT  FROM Articles WHERE MATCH(Title, Content) AGAINST('"future of technology"');

4.索引的结构

1.Hash表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

为何能够通过 key 快速取出 value 呢？ 原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

hash = hashfunc(key)
index = hash % array_size

但是！哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？ 主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。

试想一种情况:

SELECT * FROM tb1 WHERE id < 500;

在这种范围查询中，优势非常大，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了。

显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。

2.二叉查找树

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

这个特点就是为了保证每次查找都可以这折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。

显然这种情况不稳定的我们再选择设计上必然会避免这种情况的

3.平衡二叉树

平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。查询id=6，只需要两次IO。

AVL 树采用了旋转操作来保持平衡。主要有四种旋转操作：LL 旋转、RR 旋转、LR 旋转和 RL 旋转。其中 LL 旋转和 RR 旋转分别用于处理左左和右右失衡，而 LR 旋转和 RL 旋转则用于处理左右和右左失衡。

由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了数据库写操作的性能。并且，在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。 磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差。（1百万的数据量，log2n约等于20次磁盘IO，时间20*10=0.2s）

平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

实际应用中，AVL 树使用的并不多。

4.红黑树

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

每个节点非红即黑；
根节点总是黑色的；
每个叶子节点都是黑色的空节点（NIL 节点）；
如果节点是红色的，则它的子节点必须是黑色的（反之不一定）；
从任意节点到它的叶子节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）。

和 AVL 树不同的是，红黑树并不追求严格的平衡，而是大致的平衡。正因如此，红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。也正因如此，红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除节点时只需进行 O(1) 次数的旋转和变色操作，即可保持基本平衡状态，而不需要像 AVL 树一样进行 O(logn) 次数的旋转操作。

红黑树的应用还是比较广泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。

5.B树

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

B树的节点中存储着多个元素，每个内节点有多个分叉。

节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。

父节点当中的元素不会出现在子节点中。

所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

看到这里一定觉得B树就很理想了，但是前辈们会告诉你依然存在可以优化的地方：

B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

6.B+树

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟这增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

举个例子：

范围查询：
假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。

查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。

第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。

主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

5.mysql索引的实现

介绍完了索引数据结构，那肯定是要带入到Mysql里面看看真实的使用场景的，所以这里分析Mysql的两种存储引擎的索引实现：MyISAM索引和InnoDB索引

MyIsam索引

MyISAM是MySQL早期非常流行的一个存储引擎，但它不支持事务处理（transactions），也不支持外键（foreign keys）。MyISAM使用B+树作为其索引结构，并且它为每张表提供了三种类型的索引：

1. 主键索引：如果定义了一个主键，则MyISAM会自动创建一个B+树索引来加速主键的查找。

2. 唯一索引：可以创建一个或多个唯一索引，保证索引字段值的唯一性。

3. 普通索引：可以创建多个普通索引，这些索引同样基于B+树。

CREATE TABLE `user`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = MyISAM
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

主键索引

表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。

简单分析下查询时的磁盘IO情况：

根据主键等值查询数据：

select * from user where id = 28;

先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于30的索引项。（1次磁盘IO）
从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。（1次磁盘IO）
将记录返给客户端。

磁盘IO次数：3次索引检索+记录数据检索。

根据主键范围查询数据：

select * from user where id between 28 and 47;

先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历比较16<28，18<28，28=28<47。查找到值等于28的索引项。
根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）
我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）
最后得到两条符合筛选条件，将查询结果集返给客户端。

InnoDB索引

InnoDB 是 MySQL 中最常用的一个事务安全的存储引擎，它支持行级锁定、外键约束以及事务处理等功能。InnoDB 使用 B+ 树作为其索引结构，并且具有聚簇索引（Clustered Index）和二级索引（Secondary Index）的概念。

主键索引（聚簇索引）

每个InnoDB表都有一个聚簇索引，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。
聚簇索引的顺序决定了数据在磁盘上的物理存储顺序，这有助于减少磁盘 I/O 和提高查询性能。
更新聚簇索引通常比更新非聚簇索引成本更高，因为涉及到移动实际的数据行。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

这里以user_innodb为例，user_innodb的id列为主键，age列为普通索引。

CREATE TABLE `user_innodb`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

等值查询数据：

select * from user_innodb where id = 28;

先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）
磁盘IO数量：3次。

辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

以表user_innodb的age列为例，age索引的索引结果如下图。

底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。

画图分析等值查询的情况：

select * from t_user_innodb where age=19;

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

磁盘IO数：辅助索引3次+获取记录回表3次。

组合索引

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

select * from abc_innodb order by a, b, c, id;

组合索引的数据结构：

组合索引的查询过程：

select * from abc_innodb where a = 13 and b = 16 and c = 4;

倾城璧

关注

22
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL中索引详解

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。这里以user_innodb为例，user_innodb的id列为主键，age列为普通索引。
复制链接

扫一扫

专栏目录