以上测试：
01-索引的创建.sql
# 第一种：CREATE TABLE 部分
# 01-索引的创建 # 第一种：CREATE TABLE #隐式的方式创建索引。在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引。 CREATE DATABASE dbtest2; USE dbtest2; CREATE TABLE dept( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(20) ); CREATE TABLE emp( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(20) UNIQUE, dept_id INT, CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ); #显示的方式创建： #①创建普通的索引 CREATE TABLE book( book_id INT , book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100), year_publication YEAR, # 声明索引 INDEX idx_bane(book_name) ); #图形化界面查看，或命令行输出 #通过命令查看索引 #方式一 SHOW CREATE TABLE book; #方式二 SHOW INDEX FROM book; #查询索引效率提高 #性能分析工具 EXPLAIN SELECT * FROM book WHERE year_publication ='...'; #②创建唯一索引 # 声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加null CREATE TABLE book1( book_id INT , book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100), year_publication YEAR, # 声明索引 UNIQUE INDEX uk_idx_cmt(COMMENT) ); SHOW INDEX FROM book1; INSERT INTO book1(book_id,book_name,COMMENT) VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习'); SELECT * FROM book1; INSERT INTO book1(book_id,book_name,COMMENT) VALUES(2,'Mysql高级',NULL); #再去执行，会报错 #INSERT INTO book1(book_id,book_name,COMMENT) #VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习'); #③ 主键索引 #通过定义主键约束的方式定义主键索引 CREATE TABLE book2( book_id INT PRIMARY KEY, book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100), year_publication YEAR ); SHOW INDEX FROM book2; #通过删除主键约束的方式删除主键索引 ALTER TABLE book2 DROP PRIMARY KEY; # ④ 创建单列索引 CREATE TABLE book3( book_id INT , book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100), year_publication YEAR, # 声明索引 UNIQUE INDEX idx_bname(book_name) ); SHOW INDEX FROM book3; # ⑤ 创建联合索引 CREATE TABLE book4( book_id INT , book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100),`book4` year_publication YEAR, # 声明索引 INDEX mul_bid_bname_info(book_id,book_name,info) ); SHOW INDEX FROM book4; #分析 最左前缀原则 EXPLAIN SELECT * FROM book4 WHERE book_id=1001 AND book_name='mysql'; #左边要有索引 EXPLAIN SELECT * FROM book4 WHERE book_name='mysql'; #⑥ 创建全文索引 CREATE TABLE test4( id INT NOT NULL, NAME CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX futxt_idx_info(info(50)) ) ENGINE=MYISAM; SHOW INDEX FROM test4;
2.在已经存在的表上创建索引
在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句
1.使用ALTER TABLE语句
创建索引 ALTER TABLE语句创建索引的基本语法如下：
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
与创建表时创建索引的语法不同的是，在这里使用了ALTER TABLE和ADD关键字，ADD表示向表中添加索引。
举例1:在book表中的book_name字段上建立名为BkNameldx的普通索引。
ALTER TABLE book ADD INDEX idx_bkname( book_name(30) );
使用SHOW INDEX语句查看表中的索引:
SHOW INDEX FROM book \G
举例2:在book表的book_id字段上建立名称为uk_idx_bid的唯一索引，SQL语句如下:
ALTER TABLE book ADD UNIQUE INDEX uk_idx_bid ( book_id );
使用SHOW INDEX语句查看表中的索引:
SHOW INDEX FROM book \G
可以看到Non_unique的属性值为o，表示名称为Uniqidldx的索引为唯一索引，创建唯一索引成功。
举例3:在book表的comment字段上建立单列索引，SQL语句如下:
ALTER TABLE book ADD INDEX idx_cmt ( comment ( 50) );
举例4:在book表的authors和info字段上建立组合索引，sQL语句如下:
ALTER TABLE book ADD INDEX idx_author_info ( authors(30 ) , info(50) );
使用SHOW INDEX语句查看表中的索引:
SHOW INDEX FROM book lG
举例5:给customer2表的id字段声明主键索引:
CREATE TABLE customer2 ( id INT ( 18) UNSIGNED, customer_no VARCHAR( 200) , customer _name VARCHAR(200) ); ALTER TABLE customer2 add PRIMARY KEY customer2(id);
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]
测试代码：01-索引的创建.sql
以上测试：
01-索引的创建.sql
# 第2种：表已经创建成功 部分
#第2种：表已经创建成功 #① ALTER TABLE ... ADD ... CREATE TABLE book5( book_id INT , book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100), year_publication YEAR ); SHOW INDEX FROM book5; ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);#创建普通索引 ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);#创建唯一性索引 ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);#创建联合索引 #② CREATE INDEX ... ON ... CREATE TABLE book6( book_id INT , book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100), year_publication YEAR ); SHOW INDEX FROM book6; CREATE INDEX idx_cmt ON book6(COMMENT); CREATE UNIQUE INDEX uk_idx_bname ON book6(book_name); CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);
2022/8/2 21:12
1.3 删除索引
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 lG
可以看到，book表中已经没有名称为idx_aut_info的组合索引，删除索引成功。
提示
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
测试代码：02-索引的删除.sql
测试代码
# 02-索引的删除 USE dbtest2; SHOW INDEX FROM book5; #方式1：ALTER TABLE .... DROP INDEX .... ALTER TABLE book5 DROP INDEX idx_cmt; #方式2：DROP INDEX ... ON ... DROP INDEX uk_idx_bname ON book5; #测试：删除联合索引中的相关字段，索引的变化 ALTER TABLE book5 DROP COLUMN book_name; ALTER TABLE book5 DROP COLUMN book_id; ALTER TABLE book5 DROP COLUMN info;
2. Mysql8.0索引新特性
2.1 支持降序索引
降序索引以降序存储键值。虽然在语法上，从MysQL4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MySQL 8.x版本才开始真正支持降序索引〔仅限于InnoDB存储引擎)。
MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能
举例：分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1，结果如下：
CREATE TABLE ts1(a int,b int,index idx_a_b(a asc,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就表明效率较低）
提示
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后，下面来对比不同版本中执行计划的效果。 在MySQL 5.7版本中查看数据表ts1的执行计划，结果如下：
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;
在MySQL 8.0版本中查看数据表ts1的执行计划。
从结果可以看出，修改后MySQL 5.7的执行计划要明显好于MySQL 8.0
2.2 隐藏索引
在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。
从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。
同时，如果你想验证某个索引删除之后的查询性能影响，就可以暂时先隐藏该索引
注意:
主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。
索引默认是可见的，在使用CREATE TABLE，CREATE INDEX或者ALTER TABLE等语句时可以通过VISIBLE或INVISIBLE关键词设置索引的可见性。
1. 创建表时直接创建
在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：
CREATE TABLE tablename( propname1 type1[CONSTRAINT1], propname2 type2[CONSTRAINT2], …… propnamen typen, INDEX [indexname](propname1 [(length)]) INVISIBLE );
上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。
测试
#2.隐藏索引 #创建表时，隐藏索引 CREATE TABLE book7( book_id INT , book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100), year_publication YEAR, #创建不可见的索引 INDEX idx_cmt(COMMENT) invisible ); SHOW INDEX FROM book7; EXPLAIN SELECT * FROM book7 WHERE COMMENT = 'mysql....';
2. 在已经存在的表上创建
可以为已经存在的表设置隐藏索引，其语法形式如下：
CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE;
测试
#② 创建表以后 ALTER TABLE book7 ADD UNIQUE INDEX uk_idx_bname(book_name) invisible; SHOW INDEX FROM book7; CREATE INDEX idx_year_pub ON book7(year_publication); SHOW INDEX FROM book7; #没有加invisible EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022'; SHOW INDEX FROM book7;
3. 通过ALTER TABLE语句创建
语法形式如下：
ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE;
测试
#修改索引的可见性 ALTER TABLE book7 ALTER INDEX idx_year_pub invisible; #可见--->不可见 ALTER TABLE book7 ALTER INDEX idx_cmt visible; #不可见 ---> 可见
4. 切换索引可见状态
已存在的索引可通过如下语句切换可见状态
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引 ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引
如果将index_cname索引切换成可见状态，通过explain查看执行计划，发现优化器选择了index_cname索引
注意
当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能
通过设置隐藏索引的可见性可以查看索引对调优的帮助
5. 使隐藏索引对查询优化器可见
在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关（use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes设置为off(默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引
（1）在MySQL命令行执行如下命令查看查询优化器的开关设置
select @@optimizer_switch \G
在输出的结果信息中找到如下属性配置
use_invisible_indexes=off
此属性配置值为off，说明隐藏索引默认对查询优化器不可见
（2）使隐藏索引对查询优化器可见，需要在MySQL命令行执行如下命令：
set session optimizer_switch="use_invisible_indexes=on";
SQL语句执行成功，再次查看查询优化器的开关设置
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 */
此时，在输出结果中可以看到如下属性配置
use_invisible_indexes=on
use_invisible_indexes属性的值为on，说明此时隐藏索引对查询优化器可见
（3）使用EXPLAIN查看以字段invisible_column作为查询条件时的索引使用情况
explain select * from classes where cname = '高一2班';
查询优化器会使用隐藏索引来查询数据
（4）如果需要使隐藏索引对查询优化器不可见，则只需要执行如下命令即可
set session optimizer_switch="use_invisible_indexes=off";
再次查看查询优化器的开关设置
select @@optimizer_switch \G
此时，use_invisible_indexes属性的值已经被设置为“off”
测试
#了解：使隐藏索引对查询优化器可见 SELECT @@optimizer_switch \G SET SESSION optimizer_switch="use_invisible_indexes=on"; EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';
