国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁 > 數據庫 > MySQL > 正文

MySQL 8.x中新增了哪些索引方式

2024-07-24 12:33:58
字體：大 中 小
來源：轉載
供稿：網友
　　本篇內容介紹了“MySQL 8.x中新增了哪些索引方式”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
  
　　一、隱藏索引
　　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: 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.函數索引概述
　　MySQL 8.0.13開始支持在索引中使用函數（表達式）的值。
　　支持降序索引，支持JSON數據的索引
　　函數索引基于虛擬列功能實現
　　2.函數索引操作
　　（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語句的解釋如下：
 
　　JSON數據長度不固定，如果直接對JSON數據進行索引，可能會超出索引長度，通常，會只截取JSON數據的一部分進行索引。
　　CAST()類型轉換函數，把數據轉化為char(30)類型。使用方式為CAST(數據 as 數據類型)。
　　data ->> '$.name’表示JSON的運算符
　　簡單的理解為，就是取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 8.x中新增了哪些索引方式”的內容就介紹到這里了，感謝大家的閱讀。

（編輯：武林網）

發表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發表
主站蜘蛛池模板： 嘉定区| 剑川县| 怀远县| 疏勒县| 富平县| 天镇县| 益阳市| 宁波市| 寿光市| 恩施市| 青海省| 岳阳市| 孟州市| 丰城市| 涞源县| 会宁县| 巴林右旗| 交口县| 镇原县| 靖安县| 雷山县| 昔阳县| 平南县| 天峻县| 康定县| 酒泉市| 辽宁省| 东乌| 新平| 中卫市| 平罗县| 苏尼特右旗| 隆德县| 皋兰县| 彭阳县| 白山市| 阆中市| 疏附县| 林周县| 吉隆县| 琼海市|