索引的创建与设计原则（2）（适合创建索引情况 ）

索引的设计原则

数据准备

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

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

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

哪些情况适合创建索引 

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

 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba） 说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

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

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

3. 经常 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. 使用列的类型小的创建索引 

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

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

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(*)

例如： 

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;

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

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

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

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

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