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

文章目录

• 索引

• 1.索引的分类

• 主键索引（PRIMARY KEY）
• 唯一索引（UNIQUE KEY）
• 常规索引（KEY 或 INDEX)
• 全文索引（FULLTEXT）

• 2.测试索引

• （0）建立测试数据表
• （1）建立索引前—查询速度测试
• （2）建立索引后—查询速度测试

• 3.索引原则
• 4.索引的数据结构

索引

（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), '[email protected]', CONCAT('18', FLOOR(RAND()*
(999999999-100000000)[phone])),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 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默认支持它）