【宋红康 MySQL数据库 】【高级篇】【11】索引的设计原则

---

持续学习&持续更新中…

学习态度：守破离

---

数据准备

CREATE DATABASE atguigudb1;

USE atguigudb1;

#1.创建学生表和课程表
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 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;


#函数1：创建随机产生字符串函数

SELECT @@log_bin_trust_function_creators;

SET GLOBAL log_bin_trust_function_creators = 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 ;


# 存储过程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 ;

#调用存储过程：
CALL insert_course(100);

SELECT COUNT(*) FROM course;

CALL insert_stu(1000000);

SELECT COUNT(*) FROM student_info;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

哪些情况适合创建索引

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

频繁作为 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
1
2
3
4
5
6
7
8
9
10
11
12
13

经常 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;

SHOW INDEX FROM student_info;

#student_id字段上没有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #866ms
1
2
3
4
5
6
7
8
9
10
11
12
13
14

SHOW INDEX FROM student_info;

#先修改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(student_id);

ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);

#EXPLAIN #发现没有使用到create_time字段的索引【因为group by语句先于order by语句执行】
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #5.212s
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 添加联合索引：
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
1
2
3
4
5
6
7
8
9

DROP INDEX idx_sid_cre_time ON student_info;

ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC,student_id);

#EXPLAIN #发现使用的还是group by语句的字段（student_id）的索引，并没有使用到idx_cre_time_sid这个联合索引
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;
1
2
3
4
5
6
7
8
9
10

UPDATE、DELETE 的 WHERE 条件列

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
1
2
3
4
5
6
7
8
9
10
11
12

DISTINCT 字段需要创建索引

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

PS：数据类型一旦转换就会用到函数，一旦用到函数索引就失效了，故数据类型必须一致。

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

使用字符串前缀创建索引

PS：查询出来的值越接近于1越好一些。都接近于1那么截取的越少越好。

区分度高(散列性高)的列适合作为索引

比如：

• gender字段只有’男’、'女’两个值，所以gender字段就不适合创建索引
• id字段适合创建索引

使用最频繁的列放到联合索引的左侧

注意

• 在多个字段都要创建索引的情况下，联合索引优于单值索引

哪些情况不适合创建索引

在where中使用不到的字段，不要设置索引

数据量小的表最好不要使用索引

有大量重复数据的列上不要建立索引

避免对经常更新的表创建过多的索引

不建议用无序的值作为索引

删除不再使用或者很少使用的索引

不要定义冗余或重复的索引

总结

小结

限制索引的数目

参考

尚硅谷宋红康: MySQL数据库（入门到高级，菜鸟到大牛）.

---

本文完，感谢您的关注支持！

---