第08章索引的创建与设计原则【2.索引及调优篇】【MySQL高级】3-阿里云开发者社区

测试代码：03-mysql8.0 新特性.sql

完整测试代码
03-mysql8.0 新特性.sql

#03-mysql8.0 新特性
#1.支持降序索引
USE dbtest2;
CREATE TABLE ts1(a INT,b INT,INDEX idx_a_b(a ASC,b DESC));
SHOW CREATE TABLE ts1;
DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 800
DO
INSERT INTO ts1 SELECT RAND()*80000,RAND()*80000;
SET i = i + 1;
END WHILE;
COMMIT;
END //
DELIMITER ;
#调用
CALL ts_insert();
SELECT COUNT(*) FROM ts1;
#优化测试
EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;
#不推荐
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;
#2.隐藏索引
#创建表时，隐藏索引
CREATE TABLE book7(
  book_id INT ,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR,
  #创建不可见的索引
  INDEX idx_cmt(COMMENT) invisible
);
SHOW INDEX FROM book7;
EXPLAIN SELECT * FROM book7 WHERE COMMENT = 'mysql....';
#② 创建表以后
ALTER TABLE book7
ADD UNIQUE INDEX uk_idx_bname(book_name) invisible;
SHOW INDEX FROM book7;
CREATE INDEX idx_year_pub ON book7(year_publication);
SHOW INDEX FROM book7;
#没有加invisible
EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';
SHOW INDEX FROM book7;
#修改索引的可见性
ALTER TABLE book7 ALTER INDEX idx_year_pub invisible; #可见--->不可见
SHOW INDEX FROM book7;
ALTER TABLE book7 ALTER INDEX idx_cmt visible; #不可见 ---> 可见
SHOW INDEX FROM book7;
#了解：使隐藏索引对查询优化器可见
SELECT @@optimizer_switch \G
SET SESSION optimizer_switch="use_invisible_indexes=on";
EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';

3. 索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑相应准则

3.1 数据准备

第1步：创建数据库、创建表

CREATE DATABASE atguigudb1;
USE atguigudb1;
#1.创建学生表和课程表
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
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;

第2步：创建模拟数据必需的存储函数

#函数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 < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;

创建函数，假如报错：

This function has none of DETERMINISTIC......

由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。

主从复制，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设置

查看mysql是否允许创建函数：

show variables like 'log_bin_trust_function_creators';

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

mysqld重启，上述参数又会消失。永久方法：

windows下：my.ini[mysqld]加上：

log_bin_trust_function_creators=1

linux下：/etc/my.cnf下my.cnf[mysqld]加上：

log_bin_trust_function_creators=1

第3步：创建插入模拟数据的存储过程

# 存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO course (course_id, course_name ) VALUES
(rand_num(10000,10100),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

# 存储过程2：创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

第4步：调用存储过程

CALL insert_course(100);
CALL insert_stu(1000000);

#测试
SELECT COUNT(*) FROM course;    #100
SELECT COUNT(*) FROM student_info;  #1000000

3.2 哪些情况适合创建索引

1.字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引（来源:Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的

#2.哪些情况适合创建索引
#① 字段的数值有唯一性的限制  
#与②类似

2.频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息

#② 频繁作为 WHERE 查询条件的字段
#查看当前stduent_info表中的索引
SHOW INDEX FROM student_info;
#student_id字段上没有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #276ms
#给student_id字段添加索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
#student_id字段上有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #43ms
#将作为where查询条件的字段student_id设为索引后查询效率提高了

3.经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引

比如，按照student_id对学生选修的课程进行分组，显示不同的student_id和课程数目，显示100个:

#③ 经常 GROUP BY 和 ORDER BY 的列
#student_id字段上有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #41ms
#删除idx_sid索引
DROP INDEX idx_sid ON student_info;
#student_id字段上没有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #866ms

同样，如果是ORDER BY，也需要对字段创建索引。

#略

如果同时有GROUPBY和ORDER BY的情况:

比如我们按照student_id进行分组，同时按照创建时间降序的方式进行排序，这时我们就需要同时进行GROUP BY和ORDER BY，那么是不是需要单独创建student_id的索引和create_time的索引呢?

当我们对student_id和create_time分别创运索引，执行下面的SQL查询:

#再测试：
SHOW INDEX FROM student_info;
#添加单列索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);
# 报错1055 sql_mode  性能分析explain 只使用idx_sid索引
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #5.212s
#解决1055
#修改sql_mode
SELECT @@sql_mode;
SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
#添加联合索引
ALTER TABLE student_info
ADD INDEX idx_sid_cre_time(student_id,create_time DESC);
# 性能分析explain 使用联合索引
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #0.257s
#再进一步：
ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC,student_id);
#两个联合索引，删除一个
DROP INDEX idx_sid_cre_time ON student_info;
#possible_keys: idx_sid,idx_cre_time_sid key:idx_sid
#使用了idx_sid索引 没有使用idx_cre_time_sid
EXPLAIN SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #3.790s

如果创建联合索引的顺序为(create_.time, student_id)呢?运行时间为2.164s，因为在进行SELECT查询的时候，先进行GROUP BY，再对数据进行ORDER BY的操作，所以按照(student_id, create_time)这个联合索引的顺疗效率是最高的。

结论：

1.如果单独用GROUP BY，就针对对应字段建立索引，若对多个字段进行GROUP BY，可以建立联合索引。

2.如果既有GROUP BY又有ORDER BY可以考虑联合索引，此联合索引中要把GROUP BY 的字段写在前面，ORDER BY的字段写在后面，且8.0中若是降序的话加上DESC后效果更好

4.UPDATE、DELETE 的 WHERE 条件列

当我们对某条数据进行UPDATE或者DELETE操作的时候，是否也需要对WHERE的条件列仓]建索引呢?

我们先看一下对数据进行UPDATE的情况:我们想要把name为462eed7ac6e791292a79对应的student_id 修改为10002，当没有对name进行索引的时候，执行SQL语句:

UPDATE student_info SET student_id = 19392 
WHERE name ='462eed7ac5e791292a79'

运行时间为0.578s。

能看到效率不高，但如果对name字段创建了索引，然后执行类似的 SQL语句:

UPDATE student_info SET stucent_id = 190a1 WHERE name='462eed7ac6e791292a79'

运行时间仅为0.001s。效率有了大幅的提升。如果我们对某条数据进行 DELETE，效率如何呢?

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。

如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

测试

#④ UPDATE、DELETE 的 WHERE 条件列
#查看，没有关于name的索引
SHOW INDEX FROM student_info;
UPDATE student_info SET student_id = 10002 
WHERE NAME = '462eed7ac6e791292a79';  #0.633s
#添加索引
ALTER TABLE student_info
ADD INDEX idx_name(NAME);
UPDATE student_info SET student_id = 10001 
WHERE NAME = '462eed7ac6e791292a79'; #0.001s

5.DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

比如，我们想要查询课程表中不同的student_id 都有哪些，如果我们没有对 student_id 创建索引，执行SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（600637 条记录，运行时间 0.683s ）：

如果我们对 student_id 创建索引，再执行 SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（600637 条记录，运行时间 0.010s ）：

能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
测试

# ⑤ DISTINCT 字段需要创建索引
# 略

6. 多表 JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

举个例子，如果我们只对 student_id 创建索引，执行 SQL 语句：

SELECT course_id, name, student_info.student_id, course_name
FROM student_info JOIN course
ON student_info.course_id = course.course_id
WHERE name = '462eed7ac6e791292a79';

运行结果（1 条数据，运行时间 0.189s ）：

这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s。

测试

# ⑥ 多表 JOIN 连接操作时，创建索引注意事项
#首先，`连接表的数量尽量不要超过 3 张`，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
#其次，`对 WHERE 条件创建索引`，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
#最后，`对用于连接的字段创建索引`，并且该字段在多张表中的`类型必须一致`。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。
#有索引idx_name
SHOW INDEX FROM student_info;
SELECT s.course_id, NAME, s.student_id, c.course_name 
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #0.001s
DROP INDEX idx_name ON student_info;
SELECT s.course_id, NAME, s.student_id, c.course_name 
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #0.227s

7. 使用列的类型小的创建索引

这里所说的类型大小指的就是该类型表示的数据范围的大小。

在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUNINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果想要对某个整数列建立索引的话。在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如能使用INT就不要使BIGINT，能使用MEDIUMINT就不要使用INT。这是因为:

数据类型越小，在查询时进行的比较操作越快

数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

测试

# ⑦使用列的类型小的创建索引
# 略

8. 使用字符串前缀创建索引

假设字符串很长，那存储一个字符串就需要占用很大的存储空间。在需要为这个字符串列建立索引时，那就意味若在对应的B+树中有这么两个问题:
B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。

如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

create table shop(address varchar(120) not null);
alter table shop add index(address(12));

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度：

select count(distinct address) / count(*) from shop;

通过不同长度去计算，与全表的选择性对比：

公式：

count(distinct left(列名, 索引长度))/count(*)

例如：

结果越接近1，越好，短优先

select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;

引申另一个问题：索引列前缀对排序的影响：

如果使用了索引列前缀，比方说前边只把address列的前12个字符放到了二级索引中，下边这个查询可能就有点儿尴尬了:

SELECT FROM shop 
ORDER BY address
LIMIT 12;

因为二级索引中不包含完整的address列信息，所以无法对前12个字符相同、后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。
拓展：Alibaba《Java开发手册》

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

测试

# ⑧使用字符串前缀创建索引
# 略

第08章索引的创建与设计原则【2.索引及调优篇】【MySQL高级】3

测试代码：03-mysql8.0 新特性.sql

3. 索引的设计原则

3.1 数据准备

3.2 哪些情况适合创建索引

1.字段的数值有唯一性的限制

2.频繁作为 WHERE 查询条件的字段

3.经常 GROUP BY 和 ORDER BY 的列

4.UPDATE、DELETE 的 WHERE 条件列

5.DISTINCT 字段需要创建索引

6. 多表 JOIN 连接操作时，创建索引注意事项

7. 使用列的类型小的创建索引

8. 使用字符串前缀创建索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

第08章 索引的创建与设计原则【2.索引及调优篇】【MySQL高级】3

测试代码：03-mysql8.0 新特性.sql

3. 索引的设计原则

3.1 数据准备

3.2 哪些情况适合创建索引

1.字段的数值有唯一性的限制

2.频繁作为 WHERE 查询条件的字段

3.经常 GROUP BY 和 ORDER BY 的列

4.UPDATE、DELETE 的 WHERE 条件列

5.DISTINCT 字段需要创建索引

6. 多表 JOIN 连接操作时，创建索引注意事项

7. 使用列的类型小的创建索引

8. 使用字符串前缀创建索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

第08章索引的创建与设计原则【2.索引及调优篇】【MySQL高级】3