您现在的位置是：网站首页> 编程资料编程资料
MySQL 8中新增的这三大索引 隐藏、降序、函数_Mysql_
2023-05-26
334人已围观
简介 MySQL 8中新增的这三大索引 隐藏、降序、函数_Mysql_
MySQL 8中的隐藏、降序、函数索引
一、隐藏索引
1.隐藏索引概述
- MySQL 8.0开始支持隐藏索引（invisible index），不可见索引。
- 隐藏索引不会被优化器使用，但仍然需要进行维护。
- 应用场景：软删除、灰度发布。
在之前MySQL的版本中，只能通过显式的方式删除索引，如果删除后发现索引删错了，又只能通过创建索引的方式将删除的索引添加回来，如果数据库中的数据量非常大，或者表比较大，这种操作的成本非常高。
在MySQL 8.0中，只需要将这个索引先设置为隐藏索引，使查询优化器不再使用这个索引，但是，此时这个索引还是需要MySQL后台进行维护，当确认将这个索引设置为隐藏索引系统不会受到影响时，再将索引彻底删除。这就是软删除功能。
灰度发布，就是说创建索引时，首先将索引设置为隐藏索引，通过修改查询优化器的开关，使隐藏索引对查询优化器可见，通过explain对索引进行测试，确认这个索引有效，某些查询可以使用到这个索引，就可以将其设置为可见索引，完成灰度发布的效果。
2.隐藏索引操作
（1）登录MySQL，创建testdb数据库，并在数据库中创建一张测试表t1
mysql> create database if not exists testdb; Query OK, 1 row affected (0.58 sec) mysql> use testdb; Database changed mysql> create table if not exists t1(i int, j int); Query OK, 0 rows affected (0.05 sec)
（2）在字段i上创建索引，如下所示。
mysql> create index i_idx on t1(i); Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0
（3）在字段j上创建隐藏索引，创建隐藏索引时，只需要在创建索引的语句后面加上invisible关键字，如下所示
mysql> create index j_idx on t1(j) invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
（4）查看t1表中的索引情况，如下所示
mysql> show index from t1 \G *************************** 1. row *************************** Table: t1 Non_unique: 1 Key_name: i_idx Seq_in_index: 1 Column_name: i Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: t1 Non_unique: 1 Key_name: j_idx Seq_in_index: 1 Column_name: j Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: NO Expression: NULL 2 rows in set (0.02 sec)
可以看到t1表中有两个索引，一个是i_idx，一个是j_idx，i_idx的Visible属性为YES，表示这个索引可见； j_idx的Visibles属性为NO,表示这个索引不可见。
（5）查看查询优化器对这两个索引的使用情况。
首先，使用字段i进行查询，如下所示。
mysql> explain select * from t1 where i = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: i_idx key: i_idx key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.02 sec)
可以看到，查询优化器会使用i字段的索引进行优化。
接下来，使用字段j进行查询，如下所示。
mysql> explain select * from t1 where j = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
可以看到，查询优化器并没有使用j字段上的隐藏索引，会使用全表扫描的方式查询数据。
（6）使隐藏索引对优化器可见
在MySQL 8.x 中提供了一种新的测试方式，可以通过优化器的一个开关来打开某个设置，使隐藏索引对查询优化器可见。
查看查询优化器的开关，如下所示。
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 1 row in set (0.00 sec)
这里，可以看到如下一个属性值：
use_invisible_indexes=off
表示优化器是否使用不可见索引，默认为off不使用。
接下来，在MySQL的会话级别使查询优化器使用不可见索引，如下所示。
mysql> set session optimizer_switch="use_invisible_indexes=on"; Query OK, 0 rows affected (0.00 sec)
接下来，再次查看查询优化器的开关设置，如下所示
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=on,skip_scan=on,hash_join=on 1 row in set (0.00 sec)
此时，可以看到use_invisible_indexes=on，说明隐藏索引对查询优化器可见了。
再次分析使用t1表的j字段查询数据，如下所示。
mysql> explain select * from t1 where j = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: j_idx key: j_idx key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
可以看到，此时查询优化器使用j字段上的隐藏索引来优化查询了。
（7）设置索引的可见与不可见
将字段j上的隐藏索引设置为可见，如下所示。
mysql> alter table t1 alter index j_idx visible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
将字段j上的索引设置为不可见，如下所示。
mysql> alter table t1 alter index j_idx invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
（8）MySQL中主键不能设置为不可见索引
值得注意的是：在MySQL中，主键是不可以设置为不可见的。
在testdb数据库中创建一张测试表t2，如下所示。
mysql> create table t2(i int not null); Query OK, 0 rows affected (0.01 sec)
接下来，在t2表中创建一个不可见主键，如下所示
mysql> alter table t2 add primary key pk_t2(i) invisible; ERROR 3522 (HY000): A primary key index cannot be invisible
可以看到，此时SQL语句报错，主键不能被设置为不可见索引。
二、降序索引
1.降序索引概述
- MySQL 8.0开始真正支持降序索引（descending index）。
- 只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引。
- MySQL 8.0不再对GROUP BY操作进行隐式排序
2.降序索引操作
（1）MySQL 5.7中支持的语法
首先，在MySQL 5.7中创建测试数据库testdb，在数据库testdb中创建测试表t2,如下所示。
mysql> create database if not exists testdb; Query OK, 0 rows affected (0.71 sec) mysql> use testdb; Database changed mysql> create table if not exists t2(c1 int, c2 int, index idx1(c1 asc, c2 desc)); Query OK, 0 rows affected (0.71 sec)
其中，在t2表中创建了名为idx1的索引，索引中c1字段升序排序，c2字段降序排序。
接下来，查看t2表的创建信息，如下所示
mysql> show create table t2 \G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx1` (`c1`,`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.16 sec)
可以看到，MySQL 5.7版本在创建表的信息中，没有字段c1和c2的排序信息，默认都是升序。
（2）MySQL 8.0中支持的语法
在MySQL 8.x中同样创建t2表，如下所示
mysql> create table if not exists t2(c1 int, c2 int, index idx1(c1 asc, c2 desc)); Query OK, 0 rows affected, 1 warning (0.00 sec)
接下来，查看t2表的创建信息，如下所示
mysql> show create table t2 \G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx1` (`c1`,`c2` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
可以看到，在MySQL 8.x中，创建的索引中存在字段的排序信息。
（3）MySQL 5.7中查询优化器对索引的使用情况
首先，在表t2中插入一些数据，如下所示。
mysql> insert into t2(c1, c2) values(1, 100), (2, 200), (3, 150), (4, 50); Query OK, 4 rows affected (0.19 sec) Records: 4 Duplicates: 0 Warnings: 0
接下来，查询t2表中的数据，如下所示。
mysql> select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | 100 | | 2 | 200 | | 3 | 150 | | 4 | 50 | +------+------+ 4 rows in set (0.00 sec)
可以看到，t2表中的数据插入成功。
接下来，查看查询优化器对索引的使用情况，这里，查询语句按照c1字段升序，按照c2字段降序，如下所示。
mysql> explain select * from t2 order by c1, c2 desc \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: index possible_keys: NULL key: idx1 key_len: 10 ref: NULL rows: 4 filtered: 100.00 Extra: Using index; Using filesort 1 row in set, 1 warning (0.12 sec)
可以看到，在MySQL 5.7中，按照c2字段进行降序排序，并没有使用索引。
（4）MySQL 8.x中查询优化器对降序索引的使用情况。
查看查询优化器对降序索引的使用情况。
首先，在表t2中插入一些数据，如下所示。提示：
本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
