索引的声明和使用
- 索引的分类
- 索引的创建
MySQL8.0索引新特性
- 支持降序索引
- 隐藏索引
索引的设计原则
1.创建学生表和课程表
默认情况下，mysql不开启创建函数设置。需要设置为允许创建
函数1：创建随机产生字符串函数
函数2：创建随机数函数
存储过程1：创建插入课程表存储过程
存储过程2：创建插入学生信息表存储过程
调用存储过程插入100门课
小结

索引的声明和使用

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照 作用字段个数 进行划分，分成单列索引和联合索引。

普通索引：可以创建在任何数据类型中，其值是否唯一和非空，是由字段本身的完整性约束条件决定。
唯一性索引：使用UNIQUE参数可以设置索引为唯一性索引。创建唯一性索引的值必须是唯一的，但是允许有空值。一张表中可以有多个唯一性索引。
主键索引：主键索引是特殊的唯一性索引，在唯一性索引的基础之上添加了非空的约束，一张表最多只有一个主键索引。
单列索引：在表的单个字段上创建索引。
多列（组合、联合）索引：在表的多个字段组合上创建一个索引。
全文索引：使用FULLTEXT可以设置索引为全文索引。目前搜索引擎的关键技术。适用于数据量较大的字符串类型的字段。

索引的创建

创建表的时候

比如，显式创建唯一索引

隐式的话，对于主键和唯一约束的列，会自动创建索引。
建表后添加：alter table 表名 add [unique] index 索引名(字段名)，create [unique] index 索引名 on 表名(字段名)
删除索引
- 对于主键索引来说，删除主键即可：alter table 表名 drop primary key ;
- 删除普通索引：alter table index_demo drop index 索引名;
- 对于有AUTO_INCREMENT约束的唯一索引和主键索引不能被删除，因为自增需要依赖唯一性
- 删除联合索引中的字段时，该字段也会从索引中删除。如果索引字段全部被删除，那么索引也将被删除。

MySQL8.0索引新特性

支持降序索引

MySQL8.0之前创建的索引都是升序索引，当使用反向扫描时，效率较差。8.0之后就开始支持了降序索引了

比如，某查询需要对多个列进行排序，并且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作。

创建索引时指明

这样，在生成B+树的时候，当a值一样的时候，先排列的是b较大的主键记录。
使用

在上面创建的表中添加随机的记录

在order by的时候按照索引顺序进行的话，可以避免额外的文件排序

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

隐藏索引

MySQL5.7版本及以前，索引删除之后，如果发现删错了，只能通过重新创建的方式来恢复。这就会导致消耗过多的系统资源。
从MySQL8.x开始，支持隐藏索引（invisible index）。将索引设置为隐藏索引后，即使使用force index也不会使用到该索引。当确认可以删除之后，就可以彻底删除该索引。
同时，还可以通过隐藏索引来检测索引的性能，对比设置隐藏索引之前与之后的性能对比。
主键不能设置为隐藏索引。同时没有显式主键时，表中的第一个唯一非空约束的字段会成为隐式主键，也不能设置为隐藏索引。
注意：当索引被隐藏的时候，其内容和正常的索引一样会实时更新。当确定索引长期隐藏的时候，需要删除，否则会影响增删改性能。
创建隐藏索引：

索引默认是可见的，可以通过设置VISIBLE（默认）或者INVISIBLE设置索引的可见性

CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE;

操作和之前的索引类似

切换索引状态

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

优化器默认不可见隐藏索引

索引的设计原则

数据准备：100门课和100万学生
```mysql
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;

默认情况下，mysql不开启创建函数设置。需要设置为允许创建

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 ;

函数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 ;

调用存储过程插入100门课

CALL insert_course(100);

调用存储过程插入100万个学生

CALL insert_stu(1000000);


-  查看表数据  
> 学生表
>  
> ![](https://blog.chasingwind.top/Hv85Yh.png#crop=0&crop=0&crop=1&crop=1&id=ScaX7&originHeight=1298&originWidth=1956&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
>  
> 课程表
>  
> ![](https://blog.chasingwind.top/KITzNU.png#crop=0&crop=0&crop=1&crop=1&id=l6sQS&originHeight=992&originWidth=1100&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)


<a name="2880b3eb"></a>
### 适合创建索引的情况

<a name="cdb5c431"></a>
#### 字段的数值有唯一性限制

-  索引本身可以起到约束的作用，比如主键索引和唯一性索引都可以起到唯一性约束。也即：**如果表中某个字段是唯一的，就可以直接创建唯一性索引或者主键索引以达到通过该索引快速确定某条记录。**  
> Alibaba开发规范建议：业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
>  
> 说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。


<a name="19442e92"></a>
#### 频繁作为where查询条件的字段

-  某字段经常作为查询条件出现，可以为其创建一个普通索引  
> 比如，上面`student_id`经常作为查询条件出现，创建一个普通索引。
>  
> 未创建索引前和创建索引后同一个查询SQL的对比
>  
> ![](https://blog.chasingwind.top/0W6yfI.png#crop=0&crop=0&crop=1&crop=1&id=m2btu&originHeight=656&originWidth=1532&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)


<a name="5b82b8df"></a>
#### 经常Group By和Order By的字段

-  **索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引** 。如果待排序的列有多个，那么可以在这些列上建立 组合索引 。 
-  当SQL语句既有group by还有order by，并且使用的不是同一个字段  
> 演示
>  
> ![](https://blog.chasingwind.top/YFuFf5.png#crop=0&crop=0&crop=1&crop=1&id=YB4WM&originHeight=856&originWidth=2750&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
>  
> 当为student_id和create_time**分别创建单列索引**，耗时居然增加了
>  
> ![](https://blog.chasingwind.top/rrPXze.png#crop=0&crop=0&crop=1&crop=1&id=EPgwz&originHeight=1464&originWidth=2518&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
>  
> 创建联合索引student_id,create_time之后对比
>  
> ![](https://blog.chasingwind.top/oFgQ3A.png#crop=0&crop=0&crop=1&crop=1&id=FFMm9&originHeight=1208&originWidth=2156&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
>  
> 创建联合索引create_time,student_id之后对比
>  
> 一定需要注意**SQL语句和索引的顺序**问题，先group by再order by
>  
> ![](https://blog.chasingwind.top/T6x4QX.png#crop=0&crop=0&crop=1&crop=1&id=HpnMU&originHeight=1372&originWidth=2374&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)


<a name="22458efe"></a>
#### update、delete的where条件列

- 因为在update和delete之前，需要先**根据where条件查询出这些记录**，然后再进行update或者delete操作。
- 如果更新的字段为非索引字段，效率更高，不需要对索引进行维护。

<a name="a9d56b4c"></a>
#### DISTINCT字段需要创建索引

- 去重操作，如果没有索引，需要找到所有的记录，然后分组取出来一个
- **添加索引之后，由于相同字段的记录都在一起，所以去重的时候可以提升很多。**

<a name="673dbb2d"></a>
#### 多表JOIN操作

- 来自Alibaba开发规范： 
   - 超过三个表禁止 join。
   - **需要 join 的字段，数据类型保持绝对一致；**（数据类型不一致，数据库使用函数进行隐式转换，导致用不到索引）
   - **多表关联查询时，保证被关联的字段需要有索引**。

<a name="1359fada"></a>
#### 使用类型小的字段创建索引

- 尽量让索引建立在占用空间更小的数据类型字段上。原因如下： 
   - 数据类型越小，查询时候的比较操作更快
   - 数据类型越小，**索引占用的空间就越小。一个页可以存放更多的记录，从而减小磁盘IO次数**
- 对于主键来说更加适用！因为主键还存在非聚簇索引中，能用小数据类型就是用小数据类型！

<a name="d3c783e7"></a>
#### 使用字符串前缀创建索引

-  当需要为某个字段创建索引，这个字段长度很长的时候。可以使用其前缀创建索引，称为**前缀索引**。**避免索引中的结点存储过长的字段占用很多空间，同时，比较的时候也更快。** 
-  创建语句：`alter table 表名 add index 索引名(字段名(前缀长度));` 
-  假设两个记录前100个字符相等，后面不相等。我们创建的前缀索引长度为100。那么查找的时候会找到两个，拿到主键，后面通过回表操作，到聚簇索引中去找真正需要的记录。 
-  前缀的长度多少合适？过长浪费存储空间，过短重复内容太多 
   -  查看字段在全部数据中的选择度：`select count(distinct name) / count(*) from student_info;` 
   -  通过不同长度计算，与全表的选择度进行对比：  
```mysql
select count(distinct left(name,1)) / count(*) as sub10, -- 截取前1个字符的选择度
count(distinct left(name,2)) / count(*) as sub11, -- 截取前2个字符的选择度
count(distinct left(name,3)) / count(*) as sub12, -- 截取前3个字符的选择度
count(distinct left(name,4)) / count(*) as sub13, -- 截取前4个字符的选择度
count(distinct left(name,5)) / count(*) as sub14, -- 截取前5个字符的选择度
count(distinct left(name,6)) / count(*) as sub15 -- 截取前6个字符的选择度
from student_info;

值越接近1表示效果越好

由于name只有6个字符长度，这里演示效果不太好

当字符长度很长的时候，截取的长度计算结果越接近1越好

Alibaba开发规范：在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，**一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count()的区分度来确定。
存在的问题：前缀索引对排序的影响
假设姓名很长（100个字符），前缀索引长度为10。因为非聚簇索引中不包含name的完整信息，所以无法对name字段前10个长度相同，后面不同的记录进行排序。也就是使用前缀索引的方式无法支持使用索引排序，只能使用文件排序。
```
select *
from student_info
order by name
limit 20;
```

区分度高的字段创建索引

当某个字段中存在重复的记录越少，就越适合创建索引。试想某个字段的所有记录值都一样，就没必要创建索引。
可以使用select count(distinct a)/count(*) from t1计算区分度，一般超过33%就算比较高效的索引了。越接近1越好

使用最频繁的字段放在联合索引的左侧

可以使用”最佳左前缀原则“，提高联合索引的使用率

多个字段都需要创建索引的情况下，优先创建联合索引

由于最佳左前缀的原则，当我们需要使用单个字段时，可以使用联合索引（当然，联合索引中存在此字段）
同时避免多个单值索引的庞大的维护工作

索引的个数限制

索引的个数并不是越多越好，建议单表索引个数不要超过6个。原因如下：
1. 每个索引都会占用磁盘空间
2. 对于增删改操作需要进行索引的维护（影响到的索引）
3. 优化器会从每一个可以用到的索引中选择一条最好的索引生成一个执行计划交给存储引擎。过多的索引会增加MySQL优化器生成执行计划花费的时间。

不适合创建索引的情况

where中使用不到的字段

where中使用不到的字段没必要创建索引，包括group by和order by

数据量小的表不要创建索引

数据量不到直接全表查询即可。创建索引还需要占用空间，完全没必要

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

比如100万条个人信息中，性别有50万为男性，50万为女性。在性别上创建索引，查找50万男性数据，需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

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

频繁更新的字段不一定要创建索引
避免对经常更新的表创建过多的索引，并且索引中的列尽可能的少。经常更新的表可能影响到过多的索引。

不建议无序字段作为索引

因为索引是排好序的数据结构。当无序值进来时，需要变为有序，就需要进行数据的移动操作。

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

减少增删改的影响

不要定义冗余或重复索引

冗余索引：比如有了index_mixed(a,b,c)索引，就不需要创建index_a(a)，index_b(b)，index_c(c)了
重复索引：比如我们给某字段添加Primary Key主键约束，就没必要再为其创建普通索引，唯一索引了

小结

索引是一把双刃剑，可以提高查询效率，但也会降低增删改的速度并占用磁盘空间。

索引的创建与设计原则