這篇文章為大家帶來了MySQL 8.x版本中新增的三大索引的相關知識。 MySQL 8.x中新增了三種索引方式隱藏索引、降序索引和函數索引,希望對大家有幫助。
在先前MySQL的版本中,只能透過明確的方式刪除索引,如果刪除後發現索引刪錯了,又只能透過建立索引的方式將刪除的索引加回來,如果資料庫中的資料量非常大,或表格比較大,這種操作的成本非常高。
在MySQL 8.0中,只需要將這個索引先設定為隱藏索引,讓查詢最佳化器不再使用這個索引,但是,此時這個索引還是需要MySQL背景維護,當確認將這個索引設定為隱藏索引系統不會受到影響時,再將索引徹底刪除。這就是軟刪除功能。
灰階發布,是說創建索引時,首先將索引設定為隱藏索引,透過修改查詢優化器的開關,使隱藏索引對查詢最佳化器可見,透過explain對索引進行測試,確認這個索引有效,某些查詢可以使用到這個索引,就可以將其設定為可見索引,完成灰階發布的效果。
(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 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: 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)
可以看到,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: 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)
可以看到,在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: 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视频教程
以上是詳細解析MySQL 8.x中新增了三種索引方式(總結分享)的詳細內容。更多資訊請關注PHP中文網其他相關文章!