MySQL高级篇知识点——索引的创建与设计原则

本文笔记整理来自尚硅谷视频https://www.bilibili.com/video/BV1iq4y1u7vj?p=128，相关资料可在视频评论区进行获取。

1.索引的声明与使用 1.1.索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。 ① 按照功能逻辑来说，索引主要有 4 种：普通索引、唯一索引、主键索引、全文索引。 ② 按照物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。 ③ 按照作用字段个数进行划分，分成单列索引和联合索引。

（1）普通索引 在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表 student 的字段 name 上建立一个普通索引，查询记录时就可以根据该索引进行查询。

（2）唯一性索引 使用 UNIQUE 参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。例如，在表 student 的字段 email 中创建唯一性索引，那么字段 email 的值就必须是唯一的。通过唯一性索引，可以更快速地确定某条记录。

（3）主键索引 主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是 NOTNULL + UNIQUE，一张表里最多只有一个主键索引。这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。

（4）单列索引 在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

（5）多列（组合、联合）索引 多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段 id、name 和 gender 上建立一个多列索引 idx_id_name_gender，只有在查询条件中使用了字段 id 时该索引才会被使用。使用组合索引时遵循最左前缀集合。

（6）全文索引 ① 全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

② 使用参数 FULLTEXT 可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在 CHAR、VARCHAR 或 TEXT 类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，表 student 的字段 information 是 TEXT 类型，该字段包含了很多文字信息。在字段 information 上建立全文索引后，可以提高查询字段 information 的速度。

③ 全文索引典型的有两种类型：自然语言的全文索引和布尔全文索引。 自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

④ MySQL数据库从 3.23.3 版开始支持全文索引，但 MySQ L5.6.4 以前只有 MyISAM 支持，5.6.4 版本以后 InnoDB 才支持，但是官方版本不支持中文分词，需要第三方分词插件。在 5.7.6 版本，MySQL 内置了 ngram 全文解析器，用来支持亚洲语种的分词。测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。而随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr、ElasticSearch 等专门的搜索引擎所替代。

（7）空间索引 使用参数 SPATIAL 可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL 中的空间数据类型包括 GEOMETRY、POINT、LINESTRING 和 POLYGON 等。目前只有 MyISAM 存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

（8）小结，不同的存储引擎支持的索引类型也不一样 ：

存储引擎 \ 索引B+treeHashFull-textInnoDB支持支持支持MyISAM支持支持不支持Memory支持支持不支持NDB不支持支持不支持Archive不支持不支持不支持 1.2.创建索引

MySQL 支持多种方法在单个或多个列上创建索引：在创建表的定义语句 CREATE TABLE 中指定索引列，使用 ALTER TABLE 语句在存在的表上创建索引，或者使用 CREATE INDEX 语句在已存在的表上添加索引。

1.2.1.创建表的时候创建索引

使用 CREATE TABLE 创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。

（1）隐式创建索引

CREATE DATABASE dbtest2;

use dbtest2;

# 在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引
CREATE TABLE dept ( 
dept_id INT PRIMARY KEY AUTO_INCREMENT, 
dept_name VARCHAR (20) 
);

CREATE TABLE emp( 
emp_id INT PRIMARY KEY AUTO_INCREMENT, 
emp_name VARCHAR(20) UNIQUE, 
dept_id INT, 
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) 
);

（2）显示创建索引

显式创建表时创建索引的基本语法格式如下：

CREATE TABLE table_name [col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
• INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
• index_name 指定索引的名称，为可选参数，如果不指定，那么 MySQL 默认 col_name 为索引名；
• col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC 或 DESC 指定升序或者降序的索引值存储。

① 创建普通索引

CREATE TABLE book(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
# 声明索引
INDEX idx_bname(book_name)
);

通过命令查看索引

#方式 1
SHOW CREATE TABLE book;
#方式 2
SHOW INDEX FROM book;

② 创建唯一索引

# 声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加 null
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX uk_idx_cmt(COMMENT)
);


INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习');

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(2,'Mysql高级',NULL);

SELECT * FROM book1;

如果此时再插入一条数据，且字段 COMMENT 的内容与已有的重复，则会报错！

③ 创建主键索引

# 通过定义主键约束的方式定义主键索引（隐式创建索引）
CREATE TABLE book2(
book_id INT PRIMARY KEY,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

SHOW INDEX FROM book2;

通过删除主键约束的方式删除主键索引

ALTER TABLE book2
DROP PRIMARY KEY;

④ 创建单列索引

CREATE TABLE book3(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX idx_bname(book_name)
);

SHOW INDEX FROM book3;

⑤ 创建联合索引

CREATE TABLE book4(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX mul_bid_bname_info(book_id,book_name,info)
);

SHOW INDEX FROM book4;

⑥ 创建全文索引 举例 1：创建表 test4，在表中的 info 字段上建立全文索引。

CREATE TABLE test4(
id INT NOT NULL,
NAME CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info(50))
) ENGINE = MYISAM;

SHOW INDEX FROM test4;

在 MySQL5.7 及之后版本中可以不指定最后的 ENGINE 了，因为在此版本中 InnoDB 支持全文索引。

举例 2：创建了一个给 title 和 body 字段添加全文索引的表。

CREATE TABLE articles ( 
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
title VARCHAR (200), 
body TEXT, 
FULLTEXT INDEX (title, body) 
) ENGINE = INNODB ;

举例 3：

CREATE TABLE `papers` ( 
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
`title` VARCHAR(200) DEFAULT NULL, 
`content` TEXT, PRIMARY KEY (`id`), 
FULLTEXT KEY `title` (`title`,`content`) 
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

不同于 like 方式的的查询：

SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;

全文索引用 match + against 方式查询效率更高：

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’)

注意点： 1）使用全文索引前，搞清楚版本支持情况； 2）全文索引比 like + % 快 N 倍，但是可能存在精度问题； 3）如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

⑦ 创建空间索引 空间索引创建中，要求空间类型的字段必须为非空。 举例：创建表 test5，在空间类型为 GEOMETRY 的字段上创建空间索引，SQL语句如下：

CREATE TABLE test5( 
geo GEOMETRY NOT NULL, 
SPATIAL INDEX spa_idx_geo(geo) 
) ENGINE = MyISAM;

SHOW INDEX FROM test5;

1.2.2.在已经存在的表上创建索引 （1）使用 ALTER TABLE 语句创建索引

CREATE TABLE book5(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);

ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);

ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);

SHOW INDEX FROM book5;

（2）使用 CREATE INDEX 创建索引

CREATE TABLE book6(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

CREATE INDEX idx_cmt ON book6(COMMENT);

CREATE UNIQUE INDEX  uk_idx_bname ON book6(book_name);

CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

SHOW INDEX FROM book6;

1.3.删除索引

MySQL中删除索引使用 ALTER TABLE 或者 DROP INDEX 语句，两者可实现相同的功能，DROP INDEX 语句在内部被映射到一个 ALTER TABLE 语句中。

1.3.1.使用 ALTER TABLE 删除索引

ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

1.3.2.使用 DROP INDEX 删除索引

DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

提示： ① 添加 AUTO_INCREMENT 约束字段的唯一索引不能被删除。 ② 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。 ③ 删除语句执行完毕后，可以使用索引查看语句来进行查看。

2. MySQL8.0 索引新特性 2.1.支持降序索引

（1）降序索引以降序存储键值。虽然在语法上，从 MySQL 4 版本开始就已经支持降序索引的语法了，但实际上该 DESC 定义是被忽略的，直到 MySQL 8.x 版本才开始真正支持降序索引（仅限于 InnoDB 存储引擎）。MysQL在 8.0 版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

（2）举例：分别在 MySQL 5.7 版本和 MySQL 8.0 版本中创建数据表 ts1，SQL 语句如下：

CREATE TABLE ts1(a int, b int, index idx_a_b(a ASC, b desc));

① 在 MySQL 5.7 版本中查看数据表 ts1 的结构，结果如下图所示。从结果可以看出，索引仍然是默认的升序。

② 在MySQL 8.0 版本中查看数据表 ts1 的结构，结果如下图所示。从结果可以看出，索引已经是降序了。

（3）下面测试降序索引在执行计划中的表现。 ① 分别在 MySQL 5.7 版本和 MySQL 8.0 版本的数据表 ts1 中插入 799 条随机数据，执行语句如下：

DELIMITER // 
CREATE PROCEDURE ts_insert() 
BEGIN
    DECLARE i INT DEFAULT 1; 
    WHILE i 不可见
ALTER TABLE book7 ALTER INDEX idx_year_pub invisible; 
# 不可见 ---> 可见
ALTER TABLE book7 ALTER INDEX idx_cmt visible;

注意：当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。通过设置隐藏索引的可见性可以查看索引对调优的帮助。

2.2.4.使隐藏索引对查询优化器可见

在 MySQL 8.x 版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关 (use_invisible_indexes) 来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes 设置为 off（默认），优化器会忽略隐藏索引。如果设置为 on，即使隐藏索引可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

（1）在MySQL命令行执行如下命令查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G

在输出的结果信息中找到如下属性配置。

use_invisible_indexes=off

此属性配置值为 off，说明隐藏索引默认对查询优化器不可见。

（2）使隐藏索引对查询优化器可见，需要在MySQL命令行执行如下命令：

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

SQL语句执行成功，再次查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G 
*************************** 1. row *************************** 
@@optimizer_switch: 
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,
engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,
firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on,use_invisible_ind exes=on,skip_scan=on,hash_join=on 1 row in set (0.00 sec)

此时，在输出结果中可以看到如下属性配置：

use_invisible_indexes=on

use_invisible_indexes 属性的值为 on，说明此时隐藏索引对查询优化器可见。

（3）如果需要使隐藏索引对查询优化器不可见，则只需要执行如下命令即可：

mysql> set session optimizer_switch="use_invisible_indexes=off"; 
Query OK, 0 rows affected (0.00 sec)

3.索引的设计原则 3.1.数据准备 3.1.1.创建数据库、创建表

CREATE DATABASE atguigudb1;

USE atguigudb1;

# 创建学生表和课程表
CREATE TABLE `student_info` (
 `id` INT(11) AUTO_INCREMENT,
 `student_id` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `course_id` INT NOT NULL ,
 `class_id` INT(11) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `create_time` DATETIME DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`course_id` INT NOT NULL ,
`course_name` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

需要注意的是如果 MySQL 的版本是 5.7 以下的，那么在创建 student_info 表时会提示如下错误：

Invalid default value for 'create_time'

因为低版本的 MySQL 中不支持 CURRENT_TIMESTAMP 默认值，所以可以将改行语句改为：

 `create_time` DATETIME DEFAULT NULL,

3.1.2.创建模拟数据必需的存储函数

（1）创建随机产生字符串函数和随机数函数

# 函数 1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT) 
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN 
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i