一文教你搞懂数据库索引，再也不怕面试官问MySQL索引了

公告

1，索引介绍

1.1 索引的概念

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引的创建条件：创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，而不是在select的字段中，实际上，索引也是一张“表”，该表保存了主键与索引字段，并指向实体表的记录，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。说白了索引就是用来提高速度的，但是就需要维护索引造成资源的浪费，所以合理的创建索引是必要的。

1.2 索引的优缺点

1.2.1 优点

索引的存在减小了服务器查询数据时需要扫描的数据量，大大加快数据的检索速度，降低了数据库的IO成本。
索引可以帮助服务器对数据进行排序，降低了CPU的消耗
索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性。
关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
在使用分组和排序子句进行数据检索时（如order by），可以显著减少查询中分组和排序的时间。

例子：我们可以在数据库中插入100万条数据进行测试索引的作用

--创建表
CREATE TABLE app_user (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(50)  DEFAULT '' COMMENT '用户昵称',
  `email` VARCHAR(50)  NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20)  DEFAULT '' COMMENT '手机号',
  `gender` TINYINT(4)  UNSIGNED DEFAULT '0' COMMENT '性别（0：男  1：女）',
  `password` VARCHAR(100)  NOT NULL COMMENT '密码',
  `age` TINYINT(4)  DEFAULT '0' COMMENT '年龄',
  `create_time` DATETIME  DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 插入100万数据b (函数)

DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
    INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
    VALUES(CONCAT('用户',i),'123345@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    SET i = i+1;
  END WHILE;
  RETURN i;
END;

-- 执行函数
SELECT mock_data();

SELECT * FROM app_user;

-- 函数中间的插入脚本
INSERT INTO app_user(`name`,
`email`,
`phone`,
`gender`,
`password`,
`age`)
VALUES(CONCAT('用户X'),
'123345@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100));

测试

-- 加索引前
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.440 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

-- 创建索引
-- id_表名_字段名  索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`);
CREATE INDEX id_app_user_name ON app_user(`name`);
 -- 加索引后
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.002 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

加索引前

加索引后

可以看出执行成功时间从0.795s加快到了0.002s。

1.2.2 缺点

创建索引和维护索引要耗费时间和磁盘空间，这种消耗随着数据量的增加而增加。
对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了更新表的效率。
如果某个数据列包含许多重复的内容，或者数据量很小，则会造成资源的浪费。

2 ，索引的类型

2.1 逻辑分类

主键索引：一张表只能有一个主键索引，且索引列中的值必须是唯一的，不允许有空值。

唯一索引：一张表可以有多个唯一索引，但是索引列不允许重复，可以为空

普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许为空

全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。

单例索引：一张表可以有多个单例索引，但是索引只能包含一个列。

组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

2.2 物理分类

聚簇索引和非聚簇索引（有时也称辅助索引或二级索引）：

2.2.1 聚簇索引

不是单独的一种索引类型，而是一种数据存储方式

目的：为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

实现：是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。

聚簇索引的顺序就是数据的物理存储顺序，并且索引与数据放在一块，通过索引可以直接获取数据，一个数据表中仅有一个聚簇索引。

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，找到索引也就找到了数据。
聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高

2.2.2 非聚簇索引

非聚簇索引：索引顺序与数据物理排列顺序无关，索引文件与数据是分开存放。

注：InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。

2.2.3 聚簇索引和非聚簇索引的区别

聚簇索引的叶子节点存放的是数据行（主键值也是行内数据），支持覆盖索引；而非聚簇索引的叶子节点存放的是主键值或指向数据行的指针。
由于叶子节点(数据页)只能按照一棵B+树排序，故一张表只能有一个聚簇索引。非聚簇索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引。

3，索引的数据结构

3.1 Hash表

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

3.2 B-TREE

B树是在二叉树的基础出现的，MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，B树的出现就是要尽量减少磁盘 IO 操作。正常访问二叉树的每一层就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。

B树，又叫多路搜索树，树高一层意味着多一次的磁盘I/O，下图是3阶B树，将每个节点存储多个元素以降低树的高度。

特征：

B树的每个节点存储不止俩个元素，每个节点有多个分叉
节点中的元素包含键值和数据，节点中的键值从大到小排列。
任何一个关键字出现且只出现在一个结点中，也就是父节点当中的元素不会出现在子节点中。
所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接

3.3 B+TREE

B+树是对B树的进一步优化，同样是一种多路搜索树。B+树和B树最主要的区别在于非叶子节点是否存储数据

B+树特征：

只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
不可能在非叶子结点命中，因为非叶子节点只存键值
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

4，InnoDB和MyISAM的索引实现

创建表：

REATE 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 = InnoDB;

--插入数据
insert into `user` values('15','BOb','34');
insert into `user` values('18','Ailce','24');
insert into `user` values('20','Jim','5');

4.1 InnoDB索引实现

InnoDB使用B+TREE存储数据，每个InnoDB表都有一个聚簇索引，其它索引均为非聚簇索引。当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB表的索引和数据是存储在一起的，其全部放在.idb中

在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

4.1.1 聚簇索引（主键索引）

叶子节点包含了完整的数据记录，这就是聚簇索引。因为InnoDB的数据文件（.idb）按主键聚集，所以InnoDB必须有主键（MyISAM可以没有）。

分析：

B+树单个叶子节点内的行数据按主键顺序排列，物理空间是连续的（聚簇索引的数据的物理存放顺序与索引顺序是一致的）；
叶子节点之间是通过指针连接，相邻叶子节点的数据在逻辑上也是连续的（根据主键值排序）。

4.1.2 非聚簇索引（辅助索引）

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。辅助索引访问数据总是需要二次查找，首先通过辅助索引找到主键值，然后到主键索引树中通过主键值找到数据行。

注：InnoDB中主键不宜定义太大，因为辅助索引也会包含主键列，如果主键定义的比较大，其他索引也将很大。

4.2 MyIsam索引实现

MyISAM也使用B+Tree作为索引结构，但具体实现方式却与InnoDB截然不同。MyISAM使用的都是非聚簇索引。其数据文件和索引文件是分开存储的，.MYD表数据文件 .MYI`表索引文件。叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

4.2.1 主键索引

从索引原理图可以看出叶子节点的存放的是数据记录的地址，索引和行数据记录是没有保存在一起的，所以进一步验证了MyISAM的主键索引是非聚簇索引。

注：在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

5，重要知识点

5.1 最左前缀原理

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先（查询条件精确匹配索引的左边连续一列或几列，则构建对应列的组合索引树），在检索数据时也从联合索引的最左边开始匹配，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

5.2 回表

查询的列数据作为索引树的键值，直接在索引树中得到反馈（存在于索引节点），不用遍历如InnoDB中的叶子节点（存放数据表各行数据）就可得到查询的数据（不用回表）。如果未在索引树中得到反馈，就需要进行回表操作，从InnoDB中的叶子节点得到查询的数据，即为回表。

5.3 覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。比如在InnoDB中的主键索引就是聚簇索，辅助索引是非聚簇索引。如果我们使用辅助索引时，当我们拿到主键值时，仍需要根据主键值到主键索引中再次获得完整的数据。但是当搜索的索引键中的字段恰好是查询的字段（或是组合索引键中的其它字段）时，我们就可以直接返回，不需要回表了，这就是覆盖索引。

5.4 IO操作

在我们读取数据时，需要先把磁盘中的数据加载到内存中，这就需要进行磁盘IO 操作，磁盘IO 操作非常耗时，因此索引的出现就是为了尽量减少磁盘 IO 操作。
下面以MyISAM的主键查询为例子，介绍一下具体的磁盘操作：

作者：王二黑_Leon

欢迎任何形式的转载，但请务必注明出处。
限于本人水平，如果文章和代码有表述不当之处，还请不吝赐教。
本文章仅作为自学所用。

posted on 2022-03-26 20:28 王二黑_Leon 阅读(123) 评论(0) 编辑收藏举报

刷新页面返回顶部

wjq-9082

公告