MySQL知识汇总（第五篇）索引—索引的分类+全文索引实例测试与效率对比+索引原则+索引的数据结构

前言：学习自https://www.bilibili.com/video/BV1NJ411J79W?p=30

索引

（1）定义：索引是帮助MySQL高效获取数据的数据结构。

（2）查看表中索引：SHOW INDEX FROM 表名;

1.索引的分类

主键索引（PRIMARY KEY）

某一个属性或属性组能唯一标识一条记录。

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

例子：

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

唯一索引（UNIQUE KEY）

作用 : 避免同一个表中某数据列中的值重复。

与主键索引的区别：

• 主键索引只能有一个
• 唯一索引可能有多个

例子：

CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT,
`GradeName` VARCHAR(32) NOT NULL,
 PRIMARY KEY (`GradeID`)
 UNIQUE KEY `GradeIDIndex` (`GradeID`)
 UNIQUE KEY `GradeNameIndex` (`GradeName`)
)

常规索引（KEY 或 INDEX)

作用 : 快速定位特定数据。

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引，影响数据的插入，删除和修改操作

例子：

创建表时添加索引

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

常见表后添加索引

ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引（FULLTEXT）

作用 : 快速定位特定数据。

注意 :

• 只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
  适合大型数据集

ALTER TABLE student ADD FULLTEXT INDEX `studentNameIndex` (`studentName`);

2.测试索引

（0）建立测试数据表

创建测试表，一开始先不建立索引（除主键外）

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用户表'

批量插入数据：1w行

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 10000;
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();

（1）建立索引前—查询速度测试

目前并未建立索引，进行查询，测试查询时间

SELECT * FROM app_user WHERE name = '用户6666'; -- 查看耗时

利用EXPLAIN进行分析
（关于EXPLAIN的一篇优秀文章：https://blog.csdn.net/jiadajing267/article/details/81269067）

EXPLAIN SELECT * FROM app_user WHERE name = '用户6666'

---

（2）建立索引后—查询速度测试

创建索引：

CREATE INDEX idx_app_user_name ON app_user(`name`);

已经建立索引，进行查询，测试查询时间

SELECT * FROM app_user WHERE name = '用户6666'; -- 查看耗时

利用EXPLAIN进行分析

EXPLAIN SELECT * FROM app_user WHERE name = '用户6666'

数据越多，索引的查询优势越明显。

3.索引原则

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

4.索引的数据结构

• Hash类型的索引：查询单条快，范围查询慢

• B-tree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

附：一篇大佬写的非常优秀的博文：MySQL索引背后的数据结构及算法原理