This article brings you knowledge about the three new indexes added in MySQL 8.x version. There are three new index methods in MySQL 8.x: hidden index, descending index and function index. I hope it will be helpful to everyone.
In previous versions of MySQL, indexes could only be deleted explicitly. If the wrong index was found after deletion, the deleted index could only be added back by creating an index. If the amount of data in the database is very large, or the table is relatively large, the cost of this operation is very high.
In MySQL 8.0, you only need to set this index as a hidden index first so that the query optimizer no longer uses this index. However, at this time, this index still needs to be maintained by the MySQL background. When confirming that this index When the indexing system is set to be hidden and will not be affected, delete the index completely. This is the soft delete feature.
Grayscale publishing means that when creating an index, first set the index to a hidden index, modify the switch of the query optimizer to make the hidden index visible to the query optimizer, and test the index through explain to confirm this. If the index is valid and certain queries can use this index, you can set it as a visible index to achieve the effect of grayscale publishing.
(1) Log in to MySQL, create the testdb database, and create a test table t1 in the database
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) In field i Create an index as shown below.
mysql> create index i_idx on t1(i); Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0
(3) Create a hidden index on field j. When creating a hidden index, you only need to add the invisible keyword after the statement that creates the index, as shown below
mysql> create index j_idx on t1(j) invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
(4) Check the index situation in the t1 table, as shown below
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)
You can see that there are two indexes in the t1 table, one is i_idx and the other is j_idx. The Visible attribute of i_idx is YES, indicating that this index is visible; j_idx The Visibles attribute is NO, indicating that this index is not visible.
(5) Check the usage of these two indexes by the query optimizer.
First, use field i to query, as shown below.
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)
It can be seen that the query optimizer does not use the hidden index on the j field, but uses a full table scan to query the data.
(6) Make the hidden index visible to the optimizer
In MySQL 8.x, a new testing method is provided. You can turn on a certain setting through a switch of the optimizer to make the hidden index visible. Visible to the query optimizer.
Check the query optimizer switch, as shown below.
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)
Here, you can see the following attribute value:
use_invisible_indexes=off
Indicates whether the optimizer uses invisible indexes. The default is off and not used.
Next, enable the query optimizer to use invisible indexes at the MySQL session level as follows.
mysql> set session optimizer_switch="use_invisible_indexes=on"; Query OK, 0 rows affected (0.00 sec)
Next, check the switch settings of the query optimizer again, as shown below
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)
At this time, you can see use_invisible_indexes=on, indicating that the hidden index is visible to the query optimizer.
Analyze the query data using the j field of the t1 table again, as shown below.
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)
You can see that the query optimizer uses the hidden index on the j field to optimize the query.
(7) Set the visible and invisible indexes
Set the hidden index on field j to visible, as shown below.
mysql> alter table t1 alter index j_idx visible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
Set the index on field j to invisible as shown below.
mysql> alter table t1 alter index j_idx invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
(8) The primary key in MySQL cannot be set as an invisible index
It is worth noting that in MySQL, the primary key cannot be set as invisible.
Create a test table t2 in the testdb database, as shown below.
mysql> create table t2(i int not null); Query OK, 0 rows affected (0.01 sec)
Next, create an invisible primary key in the t2 table, as shown below
mysql> alter table t2 add primary key pk_t2(i) invisible; ERROR 3522 (HY000): A primary key index cannot be invisible
You can see that the SQL statement reports an error at this time, and the primary key cannot be set as an invisible index.
(1)Syntax supported in MySQL 5.7
First, create the test database testdb in MySQL 5.7, and create the test table t2 in the database testdb, as shown below.
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)
Among them, an index named idx1 is created in the t2 table. The c1 field in the index is sorted in ascending order, and the c2 field is sorted in descending order.
Next, check the creation information of the t2 table, as shown below
mysql> show create table t2 \G*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx1` (`c1`,`c2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.16 sec)
You can see that MySQL 5.7 version does not have sorting information for fields c1 and c2 in the table creation information. By default All in ascending order.
(2) Syntax supported in MySQL 8.0
Also create the t2 table in MySQL 8.x, as shown below
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)
Next, check the creation information of the t2 table, as follows As shown in
mysql> show create table t2 \G*************************** 1. row *************************** Table: t2Create 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_ci1 row in set (0.00 sec)
, you can see that in MySQL 8.x, the sorting information of the fields exists in the created index.
(3) The use of indexes by the query optimizer in MySQL 5.7
First, insert some data into table t2, as shown below.
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: indexpossible_keys: NULL key: idx1 key_len: 10 ref: NULL rows: 4 filtered: 100.00 Extra: Using index; Using filesort1 row in set, 1 warning (0.12 sec)
可以看到，在MySQL 5.7中，按照c2字段进行降序排序，并没有使用索引。
（4）MySQL 8.x中查询优化器对降序索引的使用情况。
查看查询优化器对降序索引的使用情况。
首先，在表t2中插入一些数据，如下所示。
mysql> insert into t2(c1, c2) values(1, 100), (2, 200), (3, 150), (4, 50); Query OK, 4 rows affected (0.00 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表中的数据插入成功。
在MySQL中如果创建的是升序索引，则指定查询的时候，只能按照升序索引的方式指定查询，这样才能使用升序索引。
接下来，查看查询优化器对索引的使用情况，这里，查询语句按照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: indexpossible_keys: NULL key: idx1 key_len: 10 ref: NULL rows: 4 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)
可以看到，在MySQL 8.x中，按照c2字段进行降序排序，使用了索引。
使用c1字段降序，c2字段升序排序，如下所示。
mysql> explain select * from t2 order by c1 desc, c2 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: indexpossible_keys: NULL key: idx1 key_len: 10 ref: NULL rows: 4 filtered: 100.00 Extra: Backward index scan; Using index1 row in set, 1 warning (0.00 sec)
可以看到，在MySQL 8.x中仍然可以使用索引，并使用了索引的反向扫描。
（5）MySQL 8.x中不再对GROUP BY进行隐式排序
在MySQL 5.7中执行如下命令，按照c2字段进行分组，查询每组中数据的记录条数。
mysql> select count(*), c2 from t2 group by c2; +----------+------+ | count(*) | c2 | +----------+------+ | 1 | 50 | | 1 | 100 | | 1 | 150 | | 1 | 200 | +----------+------+ 4 rows in set (0.18 sec)
可以看到，在MySQL 5.7中，在c2字段上进行了排序操作。
在MySQL 8.x中执行如下命令，按照c2字段进行分组，查询每组中数据的记录条数。
mysql> select count(*), c2 from t2 group by c2; +----------+------+ | count(*) | c2 | +----------+------+ | 1 | 100 | | 1 | 200 | | 1 | 150 | | 1 | 50 | +----------+------+ 4 rows in set (0.00 sec)
可以看到，在MySQL 8.x中，在c2字段上并没有进行排序操作。
在MySQL 8.x中如果需要对c2字段进行排序，则需要使用order by语句明确指定排序规则，如下所示。
mysql> select count(*), c2 from t2 group by c2 order by c2; +----------+------+ | count(*) | c2 | +----------+------+ | 1 | 50 | | 1 | 100 | | 1 | 150 | | 1 | 200 | +----------+------+ 4 rows in set (0.00 sec)
（1）创建测试表t3
在testdb数据库中创建一张测试表t3，如下所示。
mysql> create table if not exists t3(c1 varchar(10), c2 varchar(10)); Query OK, 0 rows affected (0.01 sec)
（2）创建普通索引
在c1字段上创建普通索引
mysql> create index idx1 on t3(c1); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
（3）创建函数索引
在c2字段上创建一个将字段值转化为大写的函数索引，如下所示。
mysql> create index func_index on t3 ((UPPER(c2))); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
（4）查看t3表上的索引信息，如下所示。
mysql> show index from t3 \G*************************** 1. row *************************** Table: t3 Non_unique: 1 Key_name: idx1 Seq_in_index: 1 Column_name: c1 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 2. row *************************** Table: t3 Non_unique: 1 Key_name: func_index Seq_in_index: 1 Column_name: NULL Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: upper(`c2`)2 rows in set (0.01 sec)
（5）查看查询优化器对两个索引的使用情况
首先，查看c1字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c1) = 'ABC' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)
可以看到，没有使用索引，进行了全表扫描操作。
接下来，查看c2字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c2) = 'ABC' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ref possible_keys: func_index key: func_index key_len: 43 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
可以看到，使用了函数索引。
（6）函数索引对JSON数据的索引
首先，创建测试表emp，并对JSON数据进行索引，如下所示。
mysql> create table if not exists emp(data json, index((CAST(data->>'$.name' as char(30))))); Query OK, 0 rows affected (0.02 sec)
上述SQL语句的解释如下：
简单的理解为，就是取name节点的值，将其转化为char(30)类型。
接下来，查看emp表中的索引情况，如下所示。
mysql> show index from emp \G *************************** 1. row *************************** Table: emp Non_unique: 1 Key_name: functional_index Seq_in_index: 1 Column_name: NULL Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: cast(json_unquote(json_extract(`data`,_utf8mb4\'$.name\')) as char(30) charset utf8mb4) 1 row in set (0.00 sec)
（7）函数索引基于虚拟列实现
首先，查看t3表的信息，如下所示。
mysql> desc t3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | varchar(10) | YES | MUL | NULL | | | c2 | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
在c1上建立了普通索引，在c2上建立了函数索引。
接下来，在t3表中添加一列c3，模拟c2上的函数索引，如下所示。
mysql> alter table t3 add column c3 varchar(10) generated always as (upper(c1)); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
c3列是一个计算列，c3字段的值总是使用c1字段转化为大写的结果。
接下来，向t3表中插入一条数据，其中，c3列是一个计算列，c3字段的值总是使用c1字段转化为大写的结果，在插入数据的时候，不需要为c3列插入数据，如下所示。
mysql> insert into t3(c1, c2) values ('abc', 'def'); Query OK, 1 row affected (0.00 sec)
查询t3表中的数据，如下所示。
mysql> select * from t3; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | abc | def | ABC | +------+------+------+ 1 row in set (0.00 sec)
可以看到，并不需要向c3列中插入数据，c3列的数据为c1字段的大写结果数据。
如果想模拟函数索引的效果，则可以使用如下方式。
首先，在c3列上添加索引，如下所示。
mysql> create index idx3 on t3(c3); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0
接下来，再次查看c1字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c1) = 'ABC' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ref possible_keys: idx3 key: idx3 key_len: 43 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
此时，就使用了idx3索引。
推荐学习：mysql视频教程
The above is the detailed content of Detailed analysis of the three new index methods added in MySQL 8.x (summary sharing). For more information, please follow other related articles on the PHP Chinese website!