MySQL创建索引的方式详解、索引的删除、MySQL8.0降序索引

左手的ㄟ右手 2023-10-13 16:46 17阅读 0赞

### 1、索引创建的方式 ###

*  方式1：创建表时指定索引列
 *  方式2：使用ALTER TABLE创建索引
 *  方式3：使用CREATE TABLE创建索引

### 2、表中索引查看的方式 ###

*  方式1：使用语句 **SHOW INDEX FROM 表名;** 语句查看表中的索引

如：![8ed40ec467ca4aca9a52491d3156638f.png][]

*  方式2：使用语句 **SHOW CREATE TABLE STUDENT01;** 语句查看表的DDL语句，可以显式的看到索引的创建语句

如：

![8bc93ae9466546fa859913226000405f.png][]

### 3、创建表时指定索引列 ###

##### 3.1、隐式的创建索引 #####

*  在声明有主键约束、唯一约束、外键约束的字段上，会自动的添加相关的索引

--创建学生表01，指定ID为主键，此时会自动添加主键索引
    CREATE TABLE STUDENT01(
    	ID INT PRIMARY KEY AUTO_INCREMENT
    	,NAME VARCHAR(20) UNIQUE 
    	,AGE INT
    )

*  查看表中索引方式1：

--查看当前表的索引
    SHOW INDEX FROM STUDENT01;

索引结果如下：

![8ed40ec467ca4aca9a52491d3156638f.png][]

*  查看表中索引方式2：

SHOW CREATE TABLE STUDENT01;

结果如下：

![8bc93ae9466546fa859913226000405f.png][]

**注意：外键约束是一种表与表之间的约束，不是索引**

外键案例：

--案例：
    --创建TEACHER01表
    CREATE TABLE TEACHER01(
    	ID INT PRIMARY KEY AUTO_INCREMENT
    	,NAME VARCHAR(20) UNIQUE 
    )
    --创建STUDENT18表，ID与TEACHER01表中的ID关联
    CREATE TABLE STUDENT18(
    	ID INT PRIMARY KEY AUTO_INCREMENT
    	,NAME VARCHAR(20) UNIQUE 
    	,AGE INT
    	,CONSTRAINT STUDENT18_ID_FK FOREIGN KEY(ID) REFERENCES TEACHER01(ID)  --创建外键
    )
    --查看索引
    SHOW INDEX FROM STUDENT18;

STUDENT18表中的索引结果：

![614908224be049fd98709520cecfbb11.png][]

--------------------

##### 3.2、显式的创建索引 #####

语法格式：

> **CREATE TABLE 表名 (**
> 
>  **列名1 列类型1 ,**
> 
>  **列名2 列类型2 ,**
> 
>  **...**
> 
>  **\[UNIQUE | FULLTEXT | SPATIAL\] \[INDEX | KEY\] 索引名称(索引作用的列名\[索引长度\]) \[DESC | AESC\]**
> 
> **)**
> 
>  *  UNIQUE、FULLTEXT、SPATIAL：可选参数，分别表示唯一索引、全文索引、空间索引
>  *  INDEX与KEY是同义词：两者作用相同，用于指定创建索引
>  *  索引名称：可选参数，如果省略，MySQL默认使用列名作为索引名称
>  *  索引长度：可选参数，只有字符串类型的字段才能指定索引长度
>  *  ASC/DESC：指定索引值存储值是否降序还是升序

###### 创建普通索引 ######

--创建学生表02，指定NAME为普通索引
    CREATE TABLE STUDENT02(
    	ID INT
    	,NAME VARCHAR(20)
    	,AGE INT
    	,INDEX idx_name(name)
    )
    --查看索引
    SHOW INDEX FROM STUDENT02;

索引查询结果如下：

![8f9ad6eee4734a47a5bb08ea31555b3b.png][]

###### 创建唯一索引（可以添加多个null值） ######

--创建学生表03，指定ID为唯一索引
    CREATE TABLE STUDENT03(
    	ID INT
    	,NAME VARCHAR(20)
    	,AGE INT
    	,UNIQUE INDEX idx_name(ID)
    )

测试唯一索引：

--给STUDENT03表插入3条数据
    INSERT INTO STUDENT03(id,name,age)
    VALUES(1,'张三',18);
    INSERT INTO STUDENT03(id,name,age)
    VALUES(null,'张三',18);
    INSERT INTO STUDENT03(id,name,age)
    VALUES(null,'张三',18);
    
    --查看所有数据
    SELECT * FROM STUDENT03;

结果如下：

![aef1716c786b49b1af9ef10d4046df41.png][]

总结：唯一索引不能重复插入相同数据，但是可以添加多个null值

###### 创建主键索引 ######

*  主键索引就是通过主键约束进行创建的，就是隐式创建索引的方式

**删除主键索引：**

ALTER TABLE 表名 DROP PRIMARY KEY;

注意：当主键是自增情况时，需要先删除自增，再删除主键

###### 创建联合索引 ######

--创建学生表04，指定NAME、AGE和SCORE为联合索引
    CREATE TABLE STUDENT04(
    	ID INT
    	,NAME VARCHAR(20)
    	,AGE INT
    	,SCORE decimal
    	,INDEX idx_name_age(NAME,AGE,SCORE)
    );
    --查看索引
    SHOW INDEX FROM STUDENT04;

结果如下：

![845f2a8c32504ebe9dddd4474e6efb14.png][]

注意：

*  由于索引创建时，字段顺序是NAME,AGE,SCORE ，因此**B+树建立的顺序也是先按NAME安排，再按AGE排序，最后按SCORE排序**
 *  **最左前缀原则：如果想使用该联合索引，如果where条件中没有最左字段name，那么该索引失效**

我们可以使用关键字**EXPLAIN**测试：

*  测试1：where条件中包含了联合索引中的所有字段

--EXPLAIN 测试索引，where条件中使用了NAME,AGE,SCORE
    EXPLAIN 
    SELECT 
    	* 
    FROM STUDENT04 
    WHERE
    	NAME = '张三'
    	AND AGE = 18
    	AND SCORE = 85

结果如下：**上述SQL语句使用了索引**

![3e8311cfc2bf40d1b48b97e793c1d1c6.png][]

*  测试2：where条件中不包含了联合索引中的中间字段AGE

--EXPLAIN 测试索引，where条件中使用了NAME,SCORE，没有使用中间的字段AGE
    EXPLAIN 
    SELECT 
    	* 
    FROM STUDENT04 
    WHERE
    	NAME = '张三'
    	AND SCORE = 85

结果如下：**上述SQL语句使用了索引**

![a471ecc78d96450f9b627827d84e19d2.png][]

*  测试3：where条件中不包含了联合索引中最左字段name

EXPLAIN 
    SELECT 
    	* 
    FROM STUDENT04 
    WHERE
    	AGE = 18
    	AND SCORE = 85

结果如下：**上述SQL语句索引失效**

![0711e53672f347af9d3e255c04b93797.png][]

*  测试4：给最左字段name，再创建唯一索引，测试使用的是单列索引还是联合索引

注意：这个测试必须保证表中存在数据，否则无法查看效果！

--给STUDENT04表插入3条数据
    INSERT INTO STUDENT04(id,name,age,score)
    VALUES(1,'张三',18,85);
    
    INSERT INTO STUDENT04(id,name,age,score)
    VALUES(1,'李四',18,85);
    
    INSERT INTO STUDENT04(id,name,age,score)
    VALUES(1,'王五',18,85);
    
    --测试索引执行情况
    EXPLAIN 
    SELECT 
    	* 
    FROM STUDENT04 
    WHERE
    	NAME = '张三'
    	AND AGE = 18
    	AND SCORE = 85

结果如下：**SQL语句使用了唯一索引，而不是联合索引**

![5a10dd025de042ed9bba32c5a3df590a.png][]

###### 联合索引使用总结 ######

*  遵循最左前缀原则：当where条件中没有使用联合索引最左字段，那么索引失效

**根据B+树建立的原理：节点中的数据记录是按最左边的字段优先进行排序，再根据索引中其他字段的先后顺序，再进行排序，因此，如果where中没有使用最左边的字段作为条件，那么B+树节点的数据记录最开始的排序（入口）都没有使用，那后面排序的字段也就无法生效，从而导致索引失效**

*  where条件中的字段的先后顺序不会影响联合索引
 *  where条件中，只要使用了最左边的字段，那么联合索引生效
 *  当一个表有多条索引可走，那么会根据优化器的查询成本来选择走哪个索引

### 4、表创建后，使用ALTER TABLE添加索引 ###

语法：

> **ALTER TABLE 表名 ADD \[索引类型\] INDEX 索引名称(作用的字段名称);**
> 
>  *  索引名称可以省略
>  *  \[索引类型\] ：可以省略，省略就是普通索引，**如果指定索引类型，那么INDEX也可以省略**

如：

CREATE TABLE STUDENT05(
    	ID INT
    	,NAME VARCHAR(20) 
    	,AGE INT
    	,SCORE decimal
    );
    
    --给STUDENT05表SCORE字段创建普通索引
    ALTER TABLE STUDENT05 ADD INDEX (score);
    --给STUDENT05表NAME字段创建唯一索引
    ALTER TABLE STUDENT05 ADD UNIQUE IDX_NAME(NAME);
    
    --给STUDENT05表NAME，AGE,SCORE字段创建联合索引
    ALTER TABLE STUDENT05 ADD INDEX IDX_NAME_AGE_SCORE(NAME,AGE,SCORE);

### 5、表创建后，使用CREATE INDEX添加索引 ###

语法：

> **CREATE \[索引类型\] INDEX 索引名称 ON 表名(作用的字段名称);**
> 
>  *  索引类型可省略
>  *  索引名称不能省略
>  *  INDEX关键字不可省略

如：

CREATE TABLE STUDENT06(
    	ID INT
    	,NAME VARCHAR(20) 
    	,AGE INT
    	,SCORE decimal
    );
    
    --给STUDENT06表SCORE字段创建普通索引
    CREATE INDEX idx_score ON STUDENT06(SCORE);
    
    --给STUDENT06表NAME字段创建唯一索引
    CREATE UNIQUE INDEX idx_name ON STUDENT06(NAME);
    
    --给STUDENT05表NAME，AGE,SCORE字段创建联合索引
    CREATE UNIQUE INDEX IDX_NAME_AGE_SCORE ON STUDENT06(NAME,AGE,SCORE);

### 6、ALTER TABLE方式与CREATE INDEX方式的区别 ###

*  ALTER方式创建非普通索引时，INDEX关键字可以省略，而**CREATE方式INDEX不可省略**
 *  ALTER方式可以不指定索引名称，默认使用字段名作为索引名称，而C**REATE方式索引名称不可省略**

### 7、索引的删除 ###

索引删除场景：

*  表索引数量多，当需要大量增删改时，可以先删除索引，再删除数据

方式1：

> ALTER TABLE 表名称 DROP 索引名称;

方式2：

> DROP INDEX 索引名称 ON 表名称;

注意：

*  当单列索引的字段删除后，索引会自动删除
 *  当联合索引的字段删除一个后，联合索引会自动删除该字段

### 8、降序索引 ###

*  索引默认是使用升序的方式存储键值的
 *  在MySQL语法上。从4.0版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的
 *  MySQL8.0版本才开始真正支持降序索引（仅InnoDB支持）

MySQL在8.0之前创建的仍然时升序索引，使用时进行反向扫描，这大大降低了数据库的效率。

在某些场景下，降序索引意义重大。例如：

如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的**文件排序**操作，从而提高性能。

案例：

1>在MySQL8中，创建表时，创建联合索引，指定索引中a升序，b降序

CREATE TABLE STUDENT07(
    	a INT
    	,b INT  
    	,INDEX IDX_A_B(a ASC,b DESC)
    );

2>使用存储过程插入799条模拟数据

-- 创建存储过程
    DELIMITER //
    CREATE PROCEDURE STUDENT07_insert() 
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	WHILE i<800
    	DO
    		insert into STUDENT07 select rand()*80000,rand()*80000; 
    		SET i=i+1;
    	END WHILE;
    	commit; 
    END //
    DELIMITER ;
    
    -- 调用存储过程
    CALL STUDENT07_insert();

3>测试使用降序索引

EXPLAIN SELECT * FROM STUDENT07 ORDER BY a,b DESC LIMIT 5;

结果：**证明排序使用了索引，性能好**

![4404e7293b7d4533925f9982a1877052.png][]

4>删除上述联合索引，创建新的联合索引，a升序，b也升序（模拟MySQL8以下的版本）

-- 删除原来的索引
    DROP INDEX IDX_A_B ON STUDENT07;
    
    -- 创建新的联合索引，a升序，b也升序（模拟MySQL8以下的版本）
    CREATE INDEX IDX_A_B ON STUDENT07(a,b);	--默认就是升序的

5>测试不使用降序索引

EXPLAIN SELECT * FROM STUDENT07 ORDER BY a,b DESC LIMIT 5;

结果： **使用了filesort（文件排序，性能不好）**

![d28ce96c4285447c9735b6a4d5901bc5.png][]

[8ed40ec467ca4aca9a52491d3156638f.png]: https://img-blog.csdnimg.cn/8ed40ec467ca4aca9a52491d3156638f.png
[8bc93ae9466546fa859913226000405f.png]: https://img-blog.csdnimg.cn/8bc93ae9466546fa859913226000405f.png
[614908224be049fd98709520cecfbb11.png]: https://img-blog.csdnimg.cn/614908224be049fd98709520cecfbb11.png
[8f9ad6eee4734a47a5bb08ea31555b3b.png]: https://img-blog.csdnimg.cn/8f9ad6eee4734a47a5bb08ea31555b3b.png
[aef1716c786b49b1af9ef10d4046df41.png]: https://img-blog.csdnimg.cn/aef1716c786b49b1af9ef10d4046df41.png
[845f2a8c32504ebe9dddd4474e6efb14.png]: https://img-blog.csdnimg.cn/845f2a8c32504ebe9dddd4474e6efb14.png
[3e8311cfc2bf40d1b48b97e793c1d1c6.png]: https://img-blog.csdnimg.cn/3e8311cfc2bf40d1b48b97e793c1d1c6.png
[a471ecc78d96450f9b627827d84e19d2.png]: https://img-blog.csdnimg.cn/a471ecc78d96450f9b627827d84e19d2.png
[0711e53672f347af9d3e255c04b93797.png]: https://img-blog.csdnimg.cn/0711e53672f347af9d3e255c04b93797.png
[5a10dd025de042ed9bba32c5a3df590a.png]: https://img-blog.csdnimg.cn/5a10dd025de042ed9bba32c5a3df590a.png
[4404e7293b7d4533925f9982a1877052.png]: https://img-blog.csdnimg.cn/4404e7293b7d4533925f9982a1877052.png
[d28ce96c4285447c9735b6a4d5901bc5.png]: https://img-blog.csdnimg.cn/d28ce96c4285447c9735b6a4d5901bc5.png