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

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

介紹

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

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

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

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

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

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

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

確保 MySQL 使用索引

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

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

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

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

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

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)

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

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

索引角落案例

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

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