107 MySQL_12 _索引【简单总结】-优快云博客

本文深入解析MySQL中的索引概念，包括索引的定义、作用、分类及创建语法，探讨索引的原则与数据结构，如B-Tree、B+Tree和Hash表，并分析不同存储引擎如InnoDB和MyISAM对索引的支持。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

107 MySQL_12 _索引

1.索引定义

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

2.索引的作用

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
全文检索字段进行搜索优化.

3.索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引(PRIMARY KEY )
- 唯一的标识，主键不可重复，只能有一一个列作为主键

唯一索引(UNIQUE KEY)

避免重复的列出现，唯-索引可以重复，多个列都可以标识位唯一索引

CREATE TABLE `Grade`(
    `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
    `GradeName` VARCHAR(32) NOT NULL UNIQUE
    -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

常规索引 (KEY/INDEX)

快速定位特定数据
默认的，index, key 关键字来设置
应加在查询找条件的字段

不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result`(
    -- 省略一些代码
    INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)

-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引 (FullText)
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

基础语法：

-- 索引的使用

-- 1.在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student


-- 增加一个全文索引 索引名 (列名)  ,括号里的是列名，前面是索引名
ALTER TABLE schodl.student ADD FULLTEXT INDEX  'studentname' ( studentName ) ;

-- EXPLAIN 分析sq1执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引，没有加全文索引的查询
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');

4.创建索引与基础语法

/*
#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC])
                );
#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                            
                            
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
 
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
 
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
 
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
 
/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

5.索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段

6.索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
 
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

案例

1.建表app_user

CREATE TABLE `app_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

2.批量插入数据：100w

DROP FUNCTION IF EXISTS mock_data;
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), '24736743@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();

3.索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时 0.993 sec
SELECT * FROM app_user WHERE name = '用户9999'; -- 1.098 sec
SELECT * FROM app_user WHERE name = '用户9999'; -- 0.798 sec
 
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: app_user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 992759
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);  -- 常规(普通)索引

测试常规(普通)索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: app_user
   partitions: NULL
         type: ref
possible_keys: idx_app_user_name
          key: idx_app_user_name
      key_len: 203
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
 
mysql> SELECT * FROM app_user WHERE name = '用户9999'; -- 0.001 sec
1 row in set (0.00 sec)
 
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
 
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

索引数据结构

数据结构

在这里插入图片描述

二叉树：二叉查找树
红黑数：平衡二叉树

在这里插入图片描述

Hash表
B-Tree：多路平衡查找树
B+Tree：B+树

B-Tree：B树

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

在这里插入图片描述

B+Tree：B+树

非叶子节点不存储data，只存储索引（冗余），可以放更多的索引（增大度）
叶子节点包含所有索引字段
叶子节点用指针（双向）连接，提高区间访问的性能（B-Tree 没有这样的双向指针）

在这里插入图片描述

B-Tree与B+Tress的区别：

先说一下B树比之前的红黑树好的原因（改进点）： B树和之前的红黑树相比是每一个节点（将一个节点的空间分配大一点，一般为16kb）可以放多个索引，这样保证树的高度不大（因为树的高度越小，查找的次数越少，效率越高），但可以存放更多的数据
data 的存放B-Tree是和索引放在一起，而B+Tree是只放在叶节点上（这就导致了B-Tree，每个节点可以存放的索引很少）
B-Tree索引是不重复的，B+Tree索引是冗余的
B+Tree叶节点上每个节点之间都有双向指针，而B-Tree没有
综上，不同之处就在于当高度相同时，B+Tree能存储的元素要远远大于B-Tree

hash表：

对索引的key进行一次hash运算就可以定位出数据存储的位置
很多时候Hash索引要比B+树索引更高效
仅能满足 “=” ，“IN”，不支持范围查询（这是关键）
存在hash冲突问题

存储引擎

存储引擎是形容数据库中的表的

在这里插入图片描述

-- 我们可以在创建索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
	hash类型索引是通过对查询条件的hash运算，来得到一个hash散列值，通过这个散列值快速得到要查询数据所在	   的磁盘文件地址 
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
两种索引类型中常用的还是btree类型索引，这是因为虽然hash类型索引查找单个数据的效率要比btree类型索引要高，但是在范围查询中如：(select * from Student where stuid > 20;) ,b+tree类型索引要更好；通过分析两种类型的索引组织结构可以看出，hash类型索引组织的数据无顺序，查找数据时，每一个数据都要进行一次hash运算，而btree类型索引组织的数据是有顺序的（排好序的），并且是双向链表，可以快速的定位一个数据的左右节点数据


-- 不同的存储引擎支持的索引类型也不一样
（默认支持）InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

MyISAM存储引擎

本机MySQL版本为5.7 数据存储在C:\ProgramData\MySQL\MySQL Server 5.7\Data 文件夹下

使用MyISAM存储引擎的表在磁盘中存储时会产生大概三种文件：.frm文件、.MYD文件、.MYI文件
frm、MYD、MYI分别是myisam引擎表的结构文件，数据文件，索引文件；
CSM、CSV是和csv引擎表相关的文件， .csv是可以直接查看的，
MyISAM索引文件和数据文件是分离的（非聚集）

在这里插入图片描述

如上图，是以表中的列Col1为主键索引建立的 b+tree数据结构，MyIsam引擎表的查询过程是先在.MYI索引文件中按照b+tree这个数据结构来查询，例如查询30，最后查询到的是0xF3，代表的是磁盘文件地址，再根据这个磁盘文件地址到 .MYD文件中快速地定位到我们要查询的数据。MyIsam引擎表是非聚集索引，而InnoDB引擎表是聚集索引，差别就在于聚集索引是将索引和数据放在了一起，在查找到目标索引之后，直接就可以取数据，而非聚集索引还要再到.MYD文件中去找数据。

InnoDB存储引擎

InnoDB索引实现（聚集）
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录（聚集的意思是指，索引与数据放在了一起，如下图的叶节点）
为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
- 如果表没有设置主键，InnoDB索引引擎会自动查找数据不重复的一个列作为主键来组织B+Tree结构
  
  如果找不到这样的一个列，则它会自动生成一个隐藏列来组织
- 因为定位索引的过程中，我们要做多次的比大小来确定树的走向，而（bigint类型）整型数据比大小的速度很快，并且整型数据最大也就8个字节，节约空间
- 自增是在于B+Tree是按顺序来存放数据的，如果不按照自增，当我们在7和9之后再插入8，它会将8这个主键索引插入到7和9之间，加入在这之前已经插入了很多数据，这个操作就会导致B+Tree中的（节点）数据存放位置发生很大的变化，导致效率比较低
为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
使用InnoDB存储引擎的表在磁盘文件中大致会产生两种文件：.frm文件 .ibd文件
.frm文件 (表结构文件) .ibd文件(索引和表数据文件，将表中的数据按B+Tree来组织)，如下图
下图展示的是主键索引：

在这里插入图片描述

非主键索引：（也称二级索引，是一种非聚集索引）

在这里插入图片描述

在这个索引中，叶子节点只存储对应的主键，查找到这个主键之后，根据这个主键，再去主键索引树中查找相对应的数据，多做了一次索引树的扫描

联合索引底层数据结构

在这里插入图片描述

上图显示的是三个字段name age position 的联合索引，对于顺序的安排是按照三个字段的先后顺序逐个字段来进行匹配，此处就是先比较name字段，如果name字段已经可以比较出大小，就按这个大小顺序来排，如果name字段比较不出，就比较age字段，依次往下。

索引最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索
引中的列。（条件严格，必须从索引的第一个字段开始，才能使用该联合索引）

EXPLAIN SELECT * FROM employees WHERE name = ‘Bill’ and age = 30;
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = ‘dev’ :
EXPLAIN SELECT * FROM employees WHERE position = ‘manager’ :

上面三条查询语句只有第一条会用到上图中的索引
这是为什么呢？
我们可以看联合索引的底层数据结构，是按照字段的先后顺序来排序的，如果跳过了某个字段，那下面的那些字段不是按顺序来排列的，例如第二条语句，忽略了name字段，直接从age字段来查询，我们发现直接看age字段，整个B+Tree中的节点数据并不是按age字段来排好序的
索引（Index）是帮助MySQL高效获取数据的 排好序 的数据结构。 ----这是核心