接下来进行查询操作
EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5; • 1
在mysql5.7中，执行结果如下。可以发现，它使用了Using filesort，这是一种外部排序方法，会降低查询的效率。并且它扫描的rows是799条。
再在8.0中执行下，可以看到8.0中使用的是索引Using Index，并且它扫描的rows是5条。
当然，你要是在8.0中执行下面语句，性能就不佳了(a,b的升降序与索引中存储的升降序相反
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b LIMIT 5;
因此，如果你频繁需要使用查询a降序，b升序的语句，就建议在建索引时也按照同样的顺序哦。
2.2 隐藏索引
Mysql8.x开始支持把索引设置为隐藏状态。在之前我们多次提到，如果需要频繁对数据进行增、删、改操作，可以先将索引删除。当我们因为删除索引出现了错误，就需要把索引重新创建回来。如果数据表本身较大，这就会造成较大的性能损耗。
因此我们可以通过隐藏索引实现对于索引的软删除。同时，如果你想验证删除索引对于性能的影响，也可以选择隐藏索引。
💁 注意 ：
主键不可以设置成为隐藏索引(这样相当于主键失效了)。如果没有显示主键，唯一的非空字段会被隐式的设置为主键，因此这种情况也不能将其设置为隐藏索引。
🎃执行如下sql。创建表时创建一个隐藏索引。
CREATE TABLE book3( book_id INT , book_name VARCHAR(100), authors VARCHAR(100), info VARCHAR(100) , comment VARCHAR(100), year_publication YEAR, INDEX idx_cmt(comment) INVISIBLE ); mysql> SHOW INDEX FROM book3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | book3 | 1 | idx_cmt | 1 | comment | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.01 sec)
执行下查询操作。可以看到possible_keys是Null，说明没有使用索引。
mysql> EXPLAIN SELECT * FROM book3 WHERE COMMENT="XXX"; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | book3 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
🎄 可以在已经存在的表上创建一个隐藏索引。
CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE;
🎀 还可以使用Alter在已经存在的表上创建一个隐藏索引。
ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE;
🎁可以切换索引可见状态 已存在的索引可通过如下语句切换可见状态：
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引 ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引
👩🏫注意：
隐藏索引在表中数据发生变化时也需要对应进行索引维护，如果一个索引需要长期隐藏，最好还是将其删除，从而降低性能上的损耗。
2.3 使隐藏索引对优化器可见(了解)
隐藏索引对优化器默认部可见，查看下优化器的配置。
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_cost_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_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on 1 row in set (0.01 sec)
在上面的输出可以看到use_invisible_indexes=off.说明隐藏索引默认对于查询优化器不可见。
可以更改
mysql> set session optimizer_switch="use_invisible_indexes=on"; Query OK, 0 rows affected (0.00 sec)
更改以后执行查询操作，possible_keys 是idx_cmt ,而且我们还将其设置为隐藏索引了。这就说明隐藏索引对于查询优化器可见了
mysql> Alter table book3 alter index idx_cmt invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM book3 WHERE COMMENT="XXX"; +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | book3 | NULL | ref | idx_cmt | idx_cmt | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
3.适合创建索引的11种情况
3.1 数据准备
准备下数据。由于需要使用函数与存储过程，这里建议使用图形化的操作界面，比如navicat
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;
准备生成随机数的函数
#函数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...... • 1
如果出现报错是因为开启了慢查询日志bin-log,主从复制时，主机会将写操作记录在bin-log日志中，从机从bin-log中读取记录执行同步操作，因为使用函数可能导致主机与从机操作时间不一致，会默认关闭函数的创建。通过如下语句来查看下
mysql> SELECT @@log_bin_trust_function_creators; +-----------------------------------+ | @@log_bin_trust_function_creators | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (0.62 sec)
默认是不允许函数创建。改下,再重新去创建函数就可以了。
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。 • 1
mysqld重启，上述参数又会消失。永久方法
#windows下：my.ini[mysqld]加上： log_bin_trust_function_creators=1 #linux下：/etc/my.cnf下my.cnf[mysqld]加上： log_bin_trust_function_creators=1
创建插入模拟数据的存储过程
#存储过程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 ;
调用存储过程插入数据,因为数据量比较大，所以需要等会(约两三分钟)。
CALL insert_course(100); CALL insert_stu(1000000);
插完数据可以验证下
mysql> select count(*) from course; +----------+ | count(*) | +----------+ | 100 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from student_info; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.09 sec)
3.2 适合创建索引的11种情况
下面适合创建索引的情况都是从B+树数据结构上来考虑的，该专栏前面的文章，已经介绍过B+树。MySql进阶索引篇01——深度讲解索引的数据结构：B+树
