MySQL 中未使用的索引：基本指南

在 MySQL 中，未使用的索引會浪費(fèi)空間并使查詢變慢。本篇文章將向大家展示如何識別未使用的索引并避免創(chuàng)建它們。

介紹

正確設(shè)計(jì)和使用在多種 MySQL 中可用的索引可能是一個挑戰(zhàn)，因?yàn)樗鼈兊念愋投喾N多樣，并且它們具有獨(dú)特的所有細(xì)微差別。但是，不要擔(dān)心——這篇文章為想要了解 MySQL 如何在更深層次上使用索引的人是一個不錯的起點(diǎn)。

什么是索引，我們應(yīng)該在什么時候使用它們？

在最基本的形式中，數(shù)據(jù)庫索引充當(dāng)記錄列表——這些記錄引用了它們所在的數(shù)據(jù)庫結(jié)構(gòu)。索引主要用于消除數(shù)據(jù)的考慮，并通過這樣做使查詢更快。如果沒有索引，數(shù)據(jù)庫必須一次一行地讀取表中存在的所有數(shù)據(jù)，這意味著如果我們的表有 100,000 條記錄，則數(shù)據(jù)庫必須通讀其中的大部分，直到找到適合我們的行. 然而，使用索引，數(shù)據(jù)庫可以排除某些行，掃描更少的行，并更快地返回結(jié)果。

索引有多種類型——每一種都適用于不同的事物；例如，如果我們的主要查詢使用相等運(yùn)算符 (=)、大于 (>)、大于或等于 (>=)、小于 (<)、小于或等于，B 樹索引可能很適合我們(<=) 運(yùn)算符或?BETWEEN?關(guān)鍵字，以及在某些特定情況下的?LIKE?查詢。 

當(dāng)存儲在我們列中的數(shù)據(jù)非常大時，前綴索引可能很有用，因此，索引整個列可能是不可能的等等。由于我們已經(jīng)涵蓋了 MySQL 中索引的一些微妙之處，因此我們不會在這篇博文中沒有詳細(xì)介紹，但了解以下內(nèi)容應(yīng)該會有所幫助：

在上面，你可以看到開發(fā)人員可以使用索引的一些最基本和最常見的場景。關(guān)于索引的一般思想是索引加快SELECT查詢速度，同時減慢所有其他類型的查詢速度——它們減慢其他類型的索引速度，因?yàn)閿?shù)據(jù)庫需要做一些額外的工作——例如，當(dāng)向表中插入數(shù)據(jù)時，數(shù)據(jù)庫必須向某個表的每個索引添加一條新記錄。將其乘以你擁有的索引數(shù)量，你就已經(jīng)看到某些類型查詢的性能下降的速度有多快。

現(xiàn)在我們已經(jīng)了解了一些基礎(chǔ)知識，讓我們進(jìn)入正題——我們可以做些什么來使我們的索引對 MySQL 真正必要？

確保 MySQL 使用索引

如果我們想確保我們的 MySQL 實(shí)例使用我們提供給它們的索引，我們有幾個選擇：

要創(chuàng)建對你的 MySQL 實(shí)例有用的索引，你需要注意的一件事是你將要使用的索引類型——但是，你還需要注意要使用多少個索引使用和緩慢性能之間創(chuàng)造一個權(quán)衡?INSERT?，?UPDATE?以及?DELETE?查詢和更快的性能?SELECTs?表示會接受你。為此，首先，向你的 MySQL 實(shí)例添加一個索引——Arctype，導(dǎo)航到要向左側(cè)添加索引的表，然后單擊“編輯表”：如果你想為其添加唯一索引，例如，單擊約束下方的按鈕并對其進(jìn)行編輯（在這種情況下，選中“唯一”應(yīng)該可以解決問題）：

完成后，單擊應(yīng)用，你的更改應(yīng)該會在你的數(shù)據(jù)庫實(shí)例上可見：

一旦您的更改生效，Arctype 也會通知你 ：

將相關(guān)索引添加到數(shù)據(jù)庫后，就可以EXPLAIN發(fā)揮它的魔力了。連接到您的數(shù)據(jù)庫實(shí)例，然后EXPLAIN在你的查詢前面添加以查看它的作用以及你的查詢是否使用您定義的索引：

EXPLAIN SELECT * FROM arctype WHERE demo_column = 'Demo Data';
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | arctype | NULL | const | demo_column | demo_column | 602 | const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

為了最大限度地使您的列對 MySQL 有用，您需要完成以下步驟：

1. 僅查詢您已編入索引的列 - 這樣做將吸引最大的成功機(jī)會。換句話說，確保您要查詢的列是隔離的 - 將WHERE子句之后的所有內(nèi)容單獨(dú)保留，不要將任何內(nèi)容混入其中，同時避免將索引列作為查詢中的表達(dá)式或函數(shù)的一部分。例如，避免發(fā)出諸如?SELECT * FROM demo_table WHERE column_1 = ‘Demo’ AND column_2 = ‘Demo 2’;ifcolumn_2?未編入索引且僅編入索引column_1等查詢。
2. 查找列中的 " Using index" 值Extra——這是 MySQL 告訴我們是否正在使用索引。
3. 如果你想看看一切此外，一定要探索possible_keys，key和key_len列，以及：在possible_keys列描繪了索引的MySQL可以從選擇中，key列涵蓋實(shí)際上選擇的指數(shù)和key_len描繪了索引的長度。由于你不再擔(dān)心用于運(yùn)行查詢的 SQL 客戶端（你已經(jīng)在使用 Arctype，不是嗎？），這并不是很復(fù)雜。

索引角落案例

總的來說，到現(xiàn)在為止，你可能已經(jīng)明白，要使索引成為必需，你應(yīng)該非常謹(jǐn)慎地選擇它們的類型，并且在可能的情況下，使用EXPLAIN 查詢，這意味著上述建議應(yīng)該使你的數(shù)據(jù)庫和索引走上良好的道路。然而，很大一部分索引變得過時和不必要是因?yàn)?MySQL 不是很挑剔；它通常會接受我們?nèi)咏o它的任何東西。索引也是如此——如果你有 10 行并決定將它們?nèi)拷⑺饕?，你可以這么做，而且 MySQL 肯定能夠完成你的請求，但有什么意義呢？在這種情況下，添加索引會浪費(fèi)磁盤空間。

你應(yīng)該避免總是求助于索引來提高性能——分區(qū)和其他功能也可以做一些很棒的工作。如果這不能解決你的所有問題，請考慮規(guī)范化你的數(shù)據(jù)庫模式——這將使你更接近完美的數(shù)據(jù)庫。如果這也不能讓您走得很遠(yuǎn)，請考慮探索 MySQL 和 MariaDB 的文檔：這應(yīng)該可以解決問題。