2. 使用 CREATE INDEX 创建索引

CREATE INDEX 语句可以在已经存在的表上添加索引，在 MySQL 中， CREATE INDEX 被映射到一个 ALTER TABLE 语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

举例1：在book表的comment字段上建立名为 的普通索引

create index idx_cmt on book(comment);

举例2：在book表中的book_id字段上建立名为uk_idx_bid的唯一索引，SQL语句如下：

CREATE UNIQUE INDEX uk_idx_bid ON book(book_id);

举例3：在book表的book_id、book_name、info字段上建立联合索引，SQL语句如下：

CREATE INDEX mul_bid_bname_info ON book(book_id,book_name,info);

1.3 删除索引

MySQL中删除索引使用ALTER TABLE或DROP INDEX语句，两者可实现相同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中

1. 使用 ALTER TABLE 删除索引：

ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

练习：删除book表中名称为idx_bk_id的唯一索引

首先查看book表中是否名称为idx_bk_id的索引，输入SHOW语句如下：

SHOW INDEX FROM book\G;

下面删除该索引，输入删除语句如下：

ALTER TABLE book DROP INDEX idx_bk_id;

提示

添加AUTO_INCREMENT约束字段的唯一索引不能被删除（）

2. 使用 DROP INDEX 语句删除索引：

DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

练习：删除book表中名称为idx_aut_info的组合索引，SQL语句如下：

DROP INDEX idx_aut_info ON book;

语句执行完毕，使用SHOW查看索引是否删除：

SHOW CREATE TABLE book\G;

可以看到，book表中已经没有名称为idx_aut_info的组合索引，删除索引成功。

提示 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

2. MySQL 8.0 索引新特性

2.1 支持降序索引

举例：分别在 MySQL 5.7 版本和 MySQL 8.0 版本中创建数据表 ts1，结果如下:

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

在 MySQL 5.7 版本中查看数据表 ts1 的结构，结果如下:

从结果可以看出，索引仍然是默认的升序。

在MySQL 8.0版本中查看数据表ts1的结构，结果如下:

从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。

分别在 MySQL 5.7 版本和 MySQL 8.0 版本的数据表 ts1 中插入 800 条随机数据，执行语句如下:

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

在 MySQL 5.7 版本中查看数据表 ts1 的执行计划，结果如下:

# 优化测试
EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

从结果可以看出，执行计划中扫描数为 799，而且使用了 Using filesort。

提示：Using filesort 是MySQL中一种速度比较慢的外部排序，能避免是最好的。多数情况下，管理员可以通过优化索引来尽量避免出现 Using filesort，从而提高数据库执行速度。

在 MySQL 8.0 版本中查看数据表 ts1 的执行计划

从结果可以看出，执行计划中扫描数为 5，而且没有使用 Using filesort。

注意：降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。例如，上述查询排序条件改为 order by a desc, b desc，MySQL 5.7 的执行计划要明显好于 MySQL 8.0。

将排序条件改为order by a desc,b desc后，下面来对比不同版本中执行计划的效果。

在MySQL5.7版本中查看数据表ts1的执行计划，结果如下：

# 优化测试
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;

在 MySQL 8.0 版本中查看数据表 ts1 的执行计划

从结果可以看出，修改后MySQL5.7的执行计划明显好于MySQL8.0

2.2 隐藏索引（invisible indexes）

在 MySQL 5.7 版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x 开始支持 隐藏索引(invisible indexes)，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用 force index（强制使用索引），优化器也不会使用该索引）， 确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

1. 创建表时直接创建

在 MySQL 中创建隐藏索引通过 SQL 语句 INVISIBLE 来实现，其语法形式如下：

 CREATE TABLE tablename(
    propname1 type1[CONSTRAINT1],
    propname2 type2[CONSTRAINT2],
    ......
    propnamen typen,
    INDEX [indexname](propname1 [(length)]) INVISIBLE
);

上述语句比普通索引多了一个关键字 INVISIBLE，用来标记索引为不可见索引。

练习：在创建书籍表book时，在字段idx_cmt上创建隐藏索引

#① 创建表时，隐藏索引
create table book(
    book_id INT,
    book_name VARCHAR(100),
    AUTHORS VARCHAR (100),
    info VARCHAR (100),
    COMMENT VARCHAR (100),
    year_publication YEAR,
    # 创建不可见的索引
    index idx_cmt(comment) invisible
);

通过explain查看发现，优化器并没有使用索引，而是使用的全表扫描

explain select * from book7 where comment = 'mysql...';

2. 在已经存在的表上创建

可以为已经存在的表设置隐藏索引，其语法形式如下：

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

举例：

CREATE INDEX idx_year_pub ON book(year_publication) INVISIBLE;

3. 通过 ALTER TABLE 语句创建

语法形式如下：

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

举例：

ALTER TABLE book
ADD UNIQUE INDEX uk_idx_bname(book_name) INVISIBLE;

4. 切换索引可见状态

已存在的索引可通过如下语句切换可见状态:

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

举例：

# 修改索引的可见性
ALTER TABLE book ALTER INDEX idx_year_pub invisible;#可见--->不可见
ALTER TABLE book ALTER INDEX idx_cmt visible;#不可见---》可见

如果将 idx_cmt 索引切换成可见状态，通过 explain 查看执行计划，发现优化器选择了idx_cmt索引

**注意：**当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

通过设置隐藏索引的可见性可以查看索引对调优的帮助。

5. 使隐藏索引对查询优化器可见(了解)

在 MySQL 8.x 版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关 （use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes 设置为 off（默认），优化器会忽略隐藏索引。如果设置为 on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

（1）在 MySQL 命令行执行如下命令查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G

在输出的结果信息中找到如下属性配置。

use_invisible_indexes=off

此属性配置值为off，说明隐藏索引默认对查询优化器不可见。

（2）使隐藏索引对查询优化器可见，需要在 MySQL 命令行执行如下命令:

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

SQL 语句执行成功，再次查看查询优化器的开关设置。

mysql>  select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_
intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_co
st_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on
,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on
,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_ind
exes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)

此时，在输出结果中可以看到如下属性配置。

use_invisible_indexes=on

use_invisible_indexes 属性的值为 on，说明此时隐藏索引对查询优化器可见。

（3）使用 EXPLAIN 查看以字段 invisible_column 作为查询条件时的索引使用情况。

explain select * from classes where cname = '高一2班';

查询优化器会使用隐藏索引来查询数据。

（4）如果需要使隐藏索引对查询优化器不可见，则只需要执行如下命令即可。

mysql> set session optimizer_switch="use_invisible_indexes=off";
Query OK, 0 rows affected (0.00 sec)

再次查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G;

此时，use_invisible_indexes 属性的值已经被设置为“off