MySQL8新特性之InvisibleIndexes的示例分析

小編給大家分享一下MySQL 8新特性之Invisible Indexes的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

創(chuàng)新互聯(lián)公司是一家專業(yè)提供婁底企業(yè)網(wǎng)站建設(shè),專注與做網(wǎng)站、網(wǎng)站建設(shè)、html5、小程序制作等業(yè)務(wù)。10年已為婁底眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)的建站公司優(yōu)惠進(jìn)行中。

背景

索引是把雙刃劍，在提升查詢速度的同時(shí)會(huì)減慢DML的操作。畢竟，索引的維護(hù)需要一定的成本。所以，對于索引，要加上該加的，刪除無用的。前者是加法，后者是減法。但在實(shí)際工作中，大家似乎更熱衷于前者，而很少進(jìn)行后者。究其原因，在于后者，難。難的不是操作本身，而是如何確認(rèn)一個(gè)索引是無用的。

如何確認(rèn)無用索引

在不可見索引出現(xiàn)之前，大家可以通過sys.schema_unused_indexes來確定無用索引。在MySQL 5.6中，即使沒有sys庫，也可通過該視圖的基表來進(jìn)行查詢。

mysql> show create table sys.schema_unused_indexes\G
*************************** 1. row ***************************
        View: schema_unused_indexes
    Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_unused_indexes` (
`object_schema`,`object_name`,`index_name`) AS select `t`.`OBJECT_SCHEMA` AS `object_schema`,`t`.`OBJECT_NAME` AS `object_name`,`t`.`INDEX_NAME` AS `index_name` from (`performance_schema`.`table_io_waits_summary_by_index_usage` `t` join `information_schema`.`STATISTICS` `s` on(((`t`.`OBJECT_SCHEMA` = convert(`s`.`TABLE_SCHEMA` using utf8mb4)) and (`t`.`OBJECT_NAME` = convert(`s`.`TABLE_NAME` using utf8mb4)) and (convert(`t`.`INDEX_NAME` using utf8) = `s`.`INDEX_NAME`)))) where ((`t`.`INDEX_NAME` is not null) and (`t`.`COUNT_STAR` = 0) and (`t`.`OBJECT_SCHEMA` <> 'mysql') and (`t`.`INDEX_NAME` <> 'PRIMARY') and (`s`.`NON_UNIQUE` = 1) and (`s`.`SEQ_IN_INDEX` = 1)) order by `t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME`character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set, 1 warning (0.00 sec)

但這種方式也有不足，

1. 如果實(shí)例發(fā)生重啟，performance_schema中的數(shù)據(jù)就會(huì)清零。

2. 如果基于上面的查詢刪除了索引，查詢性能突然變差，怎么辦？

不可見索引的出現(xiàn)，可有效彌補(bǔ)上述不足。將index設(shè)置為invisible，會(huì)導(dǎo)致優(yōu)化器在選擇執(zhí)行計(jì)劃時(shí)，自動(dòng)忽略該索引，即便使用了FORCE INDEX。

當(dāng)然，這個(gè)是由optimizer_switch變量中use_invisible_indexes選項(xiàng)決定的，默認(rèn)為off。如果想看一個(gè)查詢在索引調(diào)整前后執(zhí)行計(jì)劃的差別，可在會(huì)話級別調(diào)整use_invisible_indexes的值，如，

mysql> show create table slowtech.t1\G
*************************** 1. row ***************************
   Table: t1
Create Table: CREATE TABLE `t1` (
 `id` int(11) NOT NULL,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE   | t1  | NULL   | ALL | NULL     | NULL | NULL  | NULL |  6 |  16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE   | t1  | NULL   | ref | idx_name   | idx_name | 43   | const |  1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

不可見索引的常見操作

create table t1(id int primary key,name varchar(10),index idx_name (name) invisible);
alter table t1 alter index idx_name visible;
alter table t1 alter index idx_name invisible;

如何查看哪些索引不可見

mysql> select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no';
+--------------+------------+------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+--------------+------------+------------+-------------+------------+
| slowtech  | t1    | idx_name | name    | NO    |
+--------------+------------+------------+-------------+------------+
1 row in set (0.00 sec)

注意

主鍵索引不可被設(shè)置為invisible。

以上是“MySQL 8新特性之Invisible Indexes的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

分享名稱：MySQL8新特性之InvisibleIndexes的示例分析
本文URL：http://muchs.cn/article30/igecpo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、關(guān)鍵詞優(yōu)化、品牌網(wǎng)站制作、網(wǎng)站維護(hù)、網(wǎng)站內(nèi)鏈、定制開發(fā)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容