1 支持降序索引

舉例：分別在MySQL 5.7版本和MySQL 8.0版本中創(chuàng)建數(shù)據(jù)表ts1，結(jié)果如下：

CREATE TABLE ts1(
  a int,
  b int,
  index idx_a_b(a,b desc)
);

在MySQL 5.7版本中查看數(shù)據(jù)表ts1的結(jié)構(gòu)，從結(jié)果可以看出，索引仍然是默認的升序。

在MySQL 8.0版本中查看數(shù)據(jù)表ts1的結(jié)構(gòu)，從結(jié)果可以看出，索引已經(jīng)是降序了。下面繼續(xù)測試降序索引在執(zhí)行計劃中的表現(xiàn)。

分別在MySQL 5.7版本和MySQL 8.0版本的數(shù)據(jù)表ts1中插入800條隨機數(shù)據(jù)，執(zhí)行語句如下：

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i < 800
  DO
    insert into ts1 select rand()80000,rand()80000;
    SET i = i + 1;
  END WHILE;
  commit;
END //
DELIMITER ;
# 調(diào)用
CALL ts_insert();

在MySQL 5.7版本中查看數(shù)據(jù)表ts1的執(zhí)行計劃，結(jié)果如下：

EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

從結(jié)果可以看出，執(zhí)行計劃中掃描數(shù)為799，而且使用了Using filesort。
提示 Using filesort是MySQL中一種速度比較慢的外部排序，能避免是最好的。多數(shù)情況下，管理員
可以通過優(yōu)化索引來盡量避免出現(xiàn)Using filesort，從而提高數(shù)據(jù)庫執(zhí)行速度。

在MySQL 8.0版本中查看數(shù)據(jù)表ts1的執(zhí)行計劃。從結(jié)果可以看出，執(zhí)行計劃中掃描數(shù)為5，而且沒有使用Using filesort。
注意 降序索引只對查詢中特定的排序順序有效，如果使用不當，反而查詢效率更低。例如，上述查詢排序條件改為order by a desc, b desc，MySQL 5.7的執(zhí)行計劃要明顯好于MySQL 8.0。

將排序條件修改為order by a desc, b desc后，下面來對比不同版本中執(zhí)行計劃的效果。 在MySQL 5.7版本中查看數(shù)據(jù)表ts1的執(zhí)行計劃，結(jié)果如下：

EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;

在MySQL 8.0版本中查看數(shù)據(jù)表ts1的執(zhí)行計劃。

從結(jié)果可以看出，修改后MySQL 5.7的執(zhí)行計劃要明顯好于MySQL 8.0。

2 隱藏索引

在MySQL 5.7版本及之前，只能通過顯式的方式刪除索引。此時，如果發(fā)現(xiàn)刪除索引后出現(xiàn)錯誤，又只能通過顯式創(chuàng)建索引的方式將刪除的索引創(chuàng)建回來。如果數(shù)據(jù)表中的數(shù)據(jù)量非常大，或者數(shù)據(jù)表本身比較大，這種操作就會消耗系統(tǒng)過多的資源，操作成本非常高。

從MySQL 8.x 開始支持 隱藏索引（invisible indexes） ，只需要將待刪除的索引設(shè)置為隱藏索引，使查詢優(yōu)化器不再使用這個索引（即使使用force index（強制使用索引），優(yōu)化器也不會使用該索引），

確認將索引設(shè)置為隱藏索引后系統(tǒng)不受任何響應(yīng)，就可以徹底刪除索引。 這種通過先將索引設(shè)置為隱藏索引，再刪除索引的方式就是軟刪除 。

同時你想驗證某個索引刪除之后的 查詢性能影響就可以暫時先隱藏起來。

注意：主鍵不能被設(shè)置為隱藏索引。當表中沒有顯示主鍵時，表中的第一個唯一非空索引會成為隱式主鍵，也不能設(shè)置為隱藏索引。

• 創(chuàng)建表時直接創(chuàng)建

在MySQL中創(chuàng)建隱藏索引通過SQL語句INVISIBLE來實現(xiàn)，其語法形式如下：

CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
);

上述語句比普通索引多了一個關(guān)鍵字INVISIBLE，用來標記索引為不可見索引。

• 在已經(jīng)存在的表上創(chuàng)建
  可以為已經(jīng)存在的表設(shè)置隱藏索引，其語法形式如下：

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

• 通過ALTER TABLE語句創(chuàng)建
  語法形式如下：

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

• 切換索引可見狀態(tài)
  已存在的索引可通過如下語句切換可見狀態(tài)：

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切換成隱藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切換成非隱藏索引

如果將index_cname索引切換成可見狀態(tài)，通過explain查看執(zhí)行計劃，發(fā)現(xiàn)優(yōu)化器選擇了index_cname索引。
注意 當索引被隱藏時，它的內(nèi)容仍然是和正常索引一樣實時更新的。如果一個索引需要長期被隱藏，那么可以將其刪除，因為索引的存在會影響插入、更新和刪除的性能。

通過設(shè)置隱藏索引的可見性可以查看索引對調(diào)優(yōu)的幫助。

• 使隱藏索引對查詢優(yōu)化器可見

在MySQL 8.x版本中，為索引提供了一種新的測試方式，可以通過查詢優(yōu)化器的一個開關(guān)（use_invisible_indexes）來打開某個設(shè)置，使隱藏索引對查詢優(yōu)化器可見。如果 use_invisible_indexes
設(shè)置為off(默認)，優(yōu)化器會忽略隱藏索引。如果設(shè)置為on，即使隱藏索引不可見，優(yōu)化器在生成執(zhí)行計劃時仍會考慮使用隱藏索引。

（1）在MySQL命令行執(zhí)行如下命令查看查詢優(yōu)化器的開關(guān)設(shè)置。

mysql> select @@optimizer_switch \G

在輸出的結(jié)果信息中找到如下屬性配置

use_invisible_indexes=off

此屬性配置值為off，說明隱藏索引默認對查詢優(yōu)化器不可見。

（2）使隱藏索引對查詢優(yōu)化器可見，需要在MySQL命令行執(zhí)行如下命令：

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

SQL語句執(zhí)行成功，再次查看查詢優(yōu)化器的開關(guān)設(shè)置。

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_co
st_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_ind
exes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)

此時，在輸出結(jié)果中可以看到如下屬性配置

use_invisible_indexes=on

use_invisible_indexes屬性的值為on，說明此時隱藏索引對查詢優(yōu)化器可見。

（3）使用EXPLAIN查看以字段invisible_column作為查詢條件時的索引使用情況。

explain select * from classes where cname = '高一2班';

查詢優(yōu)化器會使用隱藏索引來查詢數(shù)據(jù)。

（4）如果需要使隱藏索引對查詢優(yōu)化器不可見，則只需要執(zhí)行如下命令即可。

mysql> set session optimizer_switch="use_invisible_indexes=off";
Query OK, 0 rows affected (0.00 sec)

此時，use_invisible_indexes屬性的值已經(jīng)被設(shè)置為“off”。

到此這篇關(guān)于一文詳解MySQL8.0的索引新特性的文章就介紹到這了,更多相關(guān)MySQL8.0索引新特性內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論