1. 首頁 > 生活百科 > >

MySQL 8.0 的隱藏索引：索引管理的利器，還是性能陷阱？

上海市飛機evtol低空經濟

text":"0 前言MySQL8.0開始支持隱藏索引 ， 不可見索引 。 它允許快速啟用/禁用MySQL Optimizer使用的索引 。 隱藏索引不會被優化器使用 ， 但仍需維護1 應用場景軟刪除灰度發布 ， 測試新索引 ：在不影響現有查詢性能的情況下 ， 測試新創建的索引是否有效維護索引 ：在更新或重建索引時 ， 可以先將其設置為不可見 ， 避免影響在線查詢臨時禁用無用索引 ：如果某個索引不再被頻繁使用 ， 可以將其設置為不可見 ， 減少不必要的開銷2 啥用？若想刪除一個索引 ， 又想事先知道效果 。 可使它對優化程序不可見 。 這是一個快速的元數據更改 ， 使索引不可見 。 一旦確定沒有性能下降 ， 就可真正去刪除索引 。 關鍵：隱藏索引不能供優化器使用 ， 但它仍存在 ， 并通過寫入操作保持最新 。 即便嘗試“FORCE INDEX” ， 優化器也不會用它 ， 雖然我認為我們應該能在某種程度強制它 。 可能會有這樣情況：可創建一個新的隱形索引 ， 但若想測試它 ， 須使它可見 。 即所有對應用程序有即時影響的查詢都將能用它 。 若目的只是想測試它 ， 我不認為這是最好方法 ， 不是所有人的服務器都有相同的數據大小和真實數據 。 強制隱藏索引這時可能會很有用 。 你有許多索引 ， 但不確定哪個未使用 。 可將一個索引更改為不可見 ， 以查看是否存在任何性能下降 。 若是 ， 你可立即更改 。 可能有個特殊情況：只有一個查詢可用該索引 。 此時 ， 隱藏索引可能是很好解決方案 。 3 創建有兩個選項：3.1 創建一個具有隱藏索引的表CREATE TABLE t1 (i INTj INTk INTINDEX i_idx (i) INVISIBLE) ENGINE = InnoDB;CREATE INDEX j_idx ON t1 (j) INVISIBLE;3.2 alter table并將索引更改為隱藏ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;4 使用如果我們現在要刪除索引 ， 我們可以將其更改為隱藏 。但是使用“FORCE / USE INDEX”的查詢怎么樣？ 他們是否會拋出一個錯誤？ 如果強制不存在的索引 ， 你會收到錯誤 。你不會看到隱藏索引的錯誤 。優化器不會使用它 ， 但知道它存在 。 show create table t1 G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`i` int(11) DEFAULT NULL`j` int(11) DEFAULT NULL`k` int(11) DEFAULT NULLKEY `i_idx` (`i`)KEY `idx_1` (`i``j``k`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+|1 | SIMPLE| t1| NULL| ref| idx_1| idx_1 | 10| constconst |2 |100.00 | Using index |+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+1 row in set 1 warning (0.00 sec)mysql> alter table t1 alter index idx_1 invisible;Query OK 0 rows affected (0.01 sec)Records: 0Duplicates: 0Warnings: 0mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|1 | SIMPLE| t1| NULL| ALL| NULL| NULL | NULL| NULL |16 |6.25 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set 1 warning (0.01 sec)mysql> explain select * from t1 where i=1 and j=4;+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+|1 | SIMPLE| t1| NULL| ref| i_idx| i_idx | 5| const |2 |10.00 | Using where |+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+1 row in set 1 warning (0.00 sec)若用帶有隱藏索引的“FORCE INDEX” ， MySQL會執行全表掃描 。MySQL不會拋任何錯誤 ， 因為索引存在 ， 但不可見 。即使有另一個可用的索引 ， 它也將執行全表掃描 。 大型表上 ， 這可能導致嚴重性能問題 。即使MySQL在查詢執行期間不拋任何錯誤 ， 它也應在錯誤日志中記錄一個警告 。 參考：關于 MySQL 8.0 新特性“隱藏索引”的一點思考https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html本文已收錄在Github ， 關注我 ， 緊跟本系列專欄文章 ， 咱們下篇再續！魔都架構師 | 全網30W技術追隨者大廠分布式系統/數據中臺實戰專家主導交易系統百萬級流量調優 & 車聯網平臺架構AIGC應用開發先行者 | 區塊鏈落地實踐者以技術驅動創新 ， 我們的征途是改變世界！實戰干貨：編程嚴選網"

    推薦閱讀