第八章,索引的创建与设计原则（3）

第八章,索引的创建与设计原则（2）https://developer.aliyun.com/article/1530676

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步：创建模拟数据必需的存储函数

因为SELECT @log_bin_trust_function_creators;的值为0所以会报错需要先设置SET GLOBAL log_bin_trust_function_creators = 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 < 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 ;

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 ;

第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 ;

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

3.2 哪些情况适合创建索引

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

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

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

业务上具有唯一特性的字段,即使是组合字段,也必须构建唯一索引

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

SELECT *from student_info where student_id = 123110;
ALTER TABLE student_info 
ADD INDEX idx_sid(student_id);

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

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

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

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

多列单向索引在使用的时候只有第一个会起作用,所以我们在堆多个列进行group by 和order by 操作时需要使用联合索引,

索引是最左匹配原则,按字段名顺序匹配相应的索引

4. UPDATE、DELETE 的 WHERE 条件列
   对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就
   能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或
   删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更
   新不需要对索引进行维护
   
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 还是按照 递增的顺序 进行展示的。这是因

为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

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 。

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

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

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

能使用MEDIUMINT就不要使用INT,

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

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

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

假设我们的字符串很长,那存储一个字符串就需要占用很大的存储空间.在我们需要为这个字符串建立索引时,就意味着对应的B+树中有两个问题

• B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
• 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

第八章,索引的创建与设计原则（4）https://developer.aliyun.com/article/1530682