AliSQL · 特性介紹 · 支持 Invisible Indexes

前言

MySQL 8.0 引入了 Invisible Indexes 這一個特性，對於 DBA 同學來說是一大福音，索引生命周期管理除了有和無外，又多了一種形態–可見和不可見，進而對業務SQL的調優又多了一種手段。

關於 Invisible Indexes，不管是官方還是第三方，都有非常多的介紹文檔，這裏推薦大家可以先看下：

簡單來說，Invisible Indexes 的特點是：對優化器來說是不可見的，但是引擎內部還是會維護這個索引，並且不可見屬性的修改操隻改了元數據，所以可以非常快。
當我們發現某個索引不需要，想要去掉的話，可以先把索引設置為不可見，觀察下業務的反應，如果一切正常，就可以 drop 掉；如果業務有受影響，那麼說明這個索引刪掉會有問題，就可以快速改回來。所以相對於 DROP/ADD 索引這種比較重的操作，Invisible Indexes 就會顯得非常靈活方便。

Invisible Indexes 是 server 層的特性，和引擎無關，因此所有引擎（InnoDB, TokuDB, MyISAM, etc.）都可以使用。

MySQL 官方隻在 8.0 版本中支持了這一特性，考慮到 8.0 的普及還比較遙遠，為了讓大家能早日用上這麼好的功能，我們將 Invisible Indexes 這一特性 backport 到 AliSQL 分支，目前開源分支已經支持，大家可以下載使用。

用法介紹

雖然官方文檔裏有詳細的使用介紹，本文為了完整性，也簡單介紹下使用方法。

CREATE TABLE：我們可以在建表時指定索引的不可見屬性，默認是可見的。

 CREATE TABLE `t1` (
   `id` int(11) DEFAULT NULL,
   `tid` int(11) DEFAULT NULL,
   KEY `idx_tid` (`tid`) INVISIBLE
 ) ENGINE=InnoDB;

ADD INDEX: 我們可以在後續加索引時，指定加的索引是否可見

 CREATE TABLE `t1` (
   `id` int(11) DEFAULT NULL,
   `tid` int(11) DEFAULT NULL
   ) ENGINE=InnoDB;
 CREATE INDEX idx_tid ON t1(tid) INVISIBLE;
 ALTER TABLE t1 ADD INDEX idx_tid(tid) INVISIBLE;

ALTER INDEX: 我們可以在後續使用時，更改已有索引的可見性

 CREATE TABLE `t1` (
   `id` int(11) DEFAULT NULL,
   `tid` int(11) DEFAULT NULL,
   KEY `idx_tid` (`tid`) INVISIBLE
 ) ENGINE=InnoDB;
 ALTER TABLE t1 ALTER INDEX idx_tid VISIBLE;

展示信息增加：INFORMATION_SCHEMA.STATISTICS 內存表和 SHOW INDEX 結果裏，分別多了一個 Visible/IS_VISIBLE 字段，表示索引是否可見：

 mysql> SHOW INDEX FROM t1\G
 *************************** 1. row ***************************
 Table: t1
 Non_unique: 1
 Key_name: idx_tid
 Seq_in_index: 1
 Column_name: tid
 Collation: A
 Cardinality: 0
 Sub_part: NULL
 Packed: NULL
 Null: YES
 Index_type: BTREE
 Comment:
 Index_comment:
 Visible: NO

 mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS where table_name='t1' AND index_name='idx_tid'\G
 *************************** 1. row ***************************
 TABLE_CATALOG: def
 TABLE_SCHEMA: test
 TABLE_NAME: t1
 NON_UNIQUE: 1
 INDEX_SCHEMA: test
 INDEX_NAME: idx_tid
 SEQ_IN_INDEX: 1
 COLUMN_NAME: tid
 COLLATION: A
 CARDINALITY: 0
 SUB_PART: NULL
 PACKED: NULL
 NULLABLE: YES
 INDEX_TYPE: BTREE
 COMMENT:
 INDEX_COMMENT:
 IS_VISIBLE: NO
 1 row in set (0.00 sec)

下麵我們用一例子來看下:

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  KEY `idx_tid` (`tid`) /*!50616 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES(1, 2), (3, 4), (5, 6), (7, 8), (9, 10);

可以看到下麵的 EXPLAIN 結果，用的是全表掃描：

mysql> EXPLAIN SELECT * FROM t1 WHERE tid=4;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

如果用 FORCE INDEX 強製指定的話，會報索引不存在的錯（這個官方早期版本是不會報錯的，最新新版本已經fix）：

mysql> EXPLAIN SELECT * FROM t1 FORCE INDEX(idx_tid) WHERE tid=4;
ERROR 1176 (42000): Key 'idx_tid' doesn't exist in table 't1'

索引改為可見之後，優化器就可以用了：

mysql> ALTER TABLE t1 ALTER INDEX idx_tid VISIBLE;
mysql> EXPLAIN SELECT * FROM t1 WHERE tid=4;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | ref  | idx_tid       | idx_tid | 5       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

雖然索引對優化器不可見，但是 MySQL 內部還是會維護索引的，包括約束條件，可以看下麵這個例子：

CREATE TABLE `t2` (
`id` int(11) NOT NULL DEFAULT '0',
`tid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_tid` (`tid`) INVISIBLE
) ENGINE=InnoDB;

mysql> INSERT INTO t2 VALUES (1, 2), (3, 4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (5, 2);
ERROR 1062 (23000): Duplicate entry '2' for key 'idx_tid'

可以看到雖然 idx_tid 索引不可見，但是 UNIQUE 約束還是被遵守的。

使用注意和實現區別

使用注意：
1. PK 不能設置為不可見，這裏的 PK 包括顯示的PK，或者因為PK不存在，被提升為 PK 的 UK；
2. 雖然設置索引的不可見屬性不需要重建表，但是改變了表定義（frm），需要重新打開表，因此會請求 MDL 排它鎖，如果有大事務或者長SQL，會被 block，這點使用時需要注意；
3. INFORMATION_SCHEMA.STATISTICS 內存表和 SHOW INDEX 結果裏多一個字段，如果有用到的話，需要做好兼容。

另外 AliSQL 支持索引使用統計(INFORMATION_SCHEMA.INDEX_STATISTICS)，和 Invisible Indexes 配合使用效果更佳，比如我們可以根據索引使用找出使用頻率低的索引，然後快速設置為不可見，如果業務沒有影響的話，就可以進一步 DROP 掉索引。

實現上區別：
官方的 INVISIBLE INDEX 是實現在 8.0 裏的，而在 8.0 其中一個重大改變，就是引入了 Data Dictionary，把原來在 Server 層放的元文件（.frm, .par, etc.）裏的信息，全放在 InnoDB 裏了。AliSQL 是 5.6 版本的，因此在元信息還是存儲在 frm 文件裏。這裏有一個問題是，其中索引標誌位占2個字節，目前16個 bit 已經全部被定義，如果擴展標誌位的話，會造成不兼容，因為這裏用了一個原先不會存在 frm 裏flag HA_SORT_ALLOWS_SAME 來存儲在 frm 表示索引不可見，這是為了保證兼容性，實現上比較 trick 的地方。

最後更新：2017-07-21 09:02:47

AliSQL · 特性介紹 · 支持 Invisible Indexes

前言

用法介紹

使用注意和實現區別

上一篇： TokuDB · 引擎特性 · HybridDB for MySQL高壓縮引擎TokuDB 揭秘

下一篇： PgSQL · 應用案例 · 阿裏雲RDS金融數據庫(三節點版) - 背景篇

相關內容

熱門內容

最新內容