歡迎深入了解 MySQL 的一項(xiàng)出色功能——隱形索引。該功能隨 MySQL 8.0 一起發(fā)布,增加了每個(gè)數(shù)據(jù)庫管理員都夢想的靈活性:能夠動(dòng)態(tài)??切換索引的可見性而不丟失它!讓我們深入研究一下這個(gè)功能的工作原理以及為什么它會(huì)改變游戲規(guī)則。
什么是隱形索引?
不可見索引是 MySQL 8.0 套件中的一個(gè)瑰寶,它允許您控制索引對優(yōu)化器的可見性。本質(zhì)上,您可以決定在查詢優(yōu)化期間是否應(yīng)考慮索引,而無需永久添加或刪除索引。
以下是如何使索引不可見或可見:
-- 使索引不可見 ALTER TABLE ALTER INDEX 不可見; -- 使索引可見 ALTER TABLE ALTER INDEX 可見;
登錄后復(fù)制
為什么要使用隱形索引?
假設(shè)您正在調(diào)整數(shù)據(jù)庫的性能或測試在沒有某些索引的情況下查詢的執(zhí)行情況。傳統(tǒng)上,您可能會(huì)刪除一個(gè)索引進(jìn)行測試,然后重新添加它,這個(gè)過程不僅耗時(shí),而且在實(shí)時(shí)環(huán)境中也存在一定風(fēng)險(xiǎn)。不可見索引通過允許保留索引但簡單地告訴優(yōu)化器忽略它來解決這個(gè)問題。
這個(gè)功能不僅僅是為了方便;這是關(guān)于性能的。使索引不可見幾乎是瞬時(shí)的,比刪除和重新添加索引快得多。
真實(shí)世界驗(yàn)證:案例研究
為了了解此功能的影響,我使用了 Gitpod Enterprise 工作區(qū),利用強(qiáng)大且短暫的環(huán)境來避免設(shè)置和安裝的麻煩。這是設(shè)置的概覽:
機(jī)器:Gitpod Enterprise XXLarge 工作區(qū)
操作系統(tǒng):Ubuntu 22.04.4 LTS(Jammy Jellyfish)
容器化:Docker 版本 26.0.1
MySQL 版本:官方 MySQL 8.0 Docker 鏡像
搭建舞臺
我首先創(chuàng)建一個(gè)書籍表并用大量數(shù)據(jù)集填充它:
創(chuàng)建表格書籍( id INT 主鍵自動(dòng)遞增, 名稱 VARCHAR(255) NOT NULL ); -- 新增1億條記錄 設(shè)置會(huì)話cte_max_recursion_深度= 100000000; 插入書籍(名稱) 與遞歸 cte (n) AS ( 選擇1 聯(lián)合所有 從 cte 中選擇 n + 1,其中 n <blockquote> 輸出: 查詢正常,100000000 行受影響(7 分 40.58 秒) 記錄:100000000 重復(fù):0 警告:0 </blockquote> <h3> 嘗試索引 </h3> <p>接下來,我向名稱列添加了索引,并執(zhí)行操作來切換其可見性:<br></p> <pre class="brush:php;toolbar:false">-- 添加索引 ALTER TABLE books 添加索引 idx_name(name);
登錄后復(fù)制
輸出: 查詢正常,0 行受影響(3 分 54.75 秒)
記錄:0 重復(fù):0 警告:0
--使索引不可見 ALTER TABLE books ALTER INDEX idx_name INVISIBLE;
登錄后復(fù)制
輸出: 查詢正常,0 行受影響(0.01 秒)
記錄:0 重復(fù):0 警告:0
--使索引再次可見 ALTER TABLE books ALTER INDEX idx_name VISIBLE;
登錄后復(fù)制
輸出: 查詢正常,0 行受影響(0.00 秒)
記錄:0 重復(fù):0 警告:0
--刪除索引 刪除書籍上的 idx_name 索引;
登錄后復(fù)制
輸出: 查詢正常,0 行受影響(0.02 秒)
記錄:0 重復(fù):0 警告:0
觀察結(jié)果
行動(dòng) | 處理時(shí)間 |
---|---|
使索引不可見 | 0.01秒 |
添加索引 | 3分54.75秒 |
使索引可見 | 0.00 秒 |
指數(shù)下降 | 0.02秒 |
結(jié)果很有啟發(fā)。使索引不可見和再次可見幾乎是瞬時(shí)的,這與添加或刪除索引所需的時(shí)間形成鮮明對比。這強(qiáng)調(diào)了使用隱形索引進(jìn)行性能調(diào)優(yōu)的效率。
結(jié)論
隱形索引是 MySQL 工具包中的一項(xiàng)重要功能,提供靈活性和性能優(yōu)勢。它們在索引可能會(huì)干擾性能的場景中特別有用,或者在測試索引對查詢執(zhí)行的影響而無需刪除和重新添加它們的開銷時(shí)特別有用。
對于詳細(xì)的 MySQL 操作,MySQL 在線 DDL 操作官方文檔提供了全面的指導(dǎo),包括添加全文索引或空間索引等不允許并發(fā) DML 操作的場景。
隱形索引不僅僅是一個(gè)功能;它們是數(shù)據(jù)庫優(yōu)化的戰(zhàn)略優(yōu)勢——對于任何參與數(shù)據(jù)庫管理或優(yōu)化的人來說絕對是一個(gè)值得掌握的工具!