分享幾個(gè)索引創(chuàng)建的小 Tips

作者：江南一點(diǎn)雨 2023-05-30 11:52:11

由于在 A 這個(gè)二級(jí)索引中就包含了 ID 字段，所以上面這個(gè)查詢是可以使用到索引排序的。此時(shí)，如果由于其他需求，我們將 A 這個(gè)索引擴(kuò)展成聯(lián)合索引（A、B）了，那么很明顯，再執(zhí)行上面的查詢的時(shí)候就用不了索引排序了，只能 filesort 了。這樣的問題小伙伴們?cè)趧?chuàng)建或者修改索引的時(shí)候很容易忽略，所以一定要仔細(xì)。

關(guān)于 MySQL 中的索引，松哥前面已經(jīng)和小伙伴們聊了不少了，不過在索引使用的時(shí)候，還是有一些需要注意的細(xì)節(jié)，如果忽略了這些細(xì)節(jié)，可能會(huì)讓索引的使用效果大打折扣。

1. 冗余索引

注意我這里使用了冗余索引，沒有使用重復(fù)索引，因?yàn)槲矣X得在小伙伴們使用索引的過程中，創(chuàng)建重復(fù)索引的概率應(yīng)該還是比較小，同一個(gè)字段上創(chuàng)建多個(gè)一模一樣的索引，應(yīng)該很少有人會(huì)犯這種錯(cuò)誤。但是，會(huì)有一些容易被大家忽略的冗余索引，我們來捋一捋。

1.1 聯(lián)合索引左邊列

例如我創(chuàng)建了一個(gè)聯(lián)合索引 (A,B,C)，按照我們之前跟大家講的最左匹配原則，當(dāng)我們使用 A、（A、B）或者（A、B、C）去查詢數(shù)據(jù)的時(shí)候，都會(huì)用到這個(gè)聯(lián)合索引，所以我們就沒有必要再去單獨(dú)針對(duì) A 字段創(chuàng)建一個(gè)索引，或者針對(duì) A、B 字段創(chuàng)建一個(gè)聯(lián)合索引。

1.2 索引中加入主鍵

假設(shè)我有一張表，該表有如下字段（ID、A、B、C），其中 ID 是主鍵，現(xiàn)在又針對(duì) A 和 ID 兩個(gè)字段創(chuàng)建了聯(lián)合索引（A、ID）。

根據(jù)松哥前面的介紹，小伙伴們知道，在二級(jí)索引中，葉子結(jié)點(diǎn)上存儲(chǔ)的數(shù)據(jù)就是 ID，所以，這個(gè)聯(lián)合索引中的 ID 字段顯然是多余的。

大部分情況下我們都不需要冗余索引，但是也有一些特殊情況可能讓我們不得不創(chuàng)建一些冗余索引，這個(gè)小伙伴們還是要具體問題具體分析。

另外需要注意一點(diǎn)，針對(duì)相同的字段，如果索引類型不同，則不能算是重復(fù)索引，例如一個(gè)普通索引和一個(gè)全文索引，同一個(gè)字段上同時(shí)有這兩個(gè)索引，不算重復(fù)索引。

2. 隱藏的索引排序

上篇文章松哥剛剛和大家聊了索引排序的問題。

結(jié)合上篇文章的內(nèi)容，小伙伴們思考這樣一個(gè)問題：假設(shè)我有一張表，表中包含如下字段（ID、A、B），其中 ID 是主鍵，現(xiàn)在我針對(duì) A 字段建立一個(gè)索引，如果我有如下查詢 SQL：

select ... from table where A=xxx order by ID

3. 刪除不使用的索引

有的索引可能是由于過度考慮創(chuàng)建了，創(chuàng)建成功之后就沒用過，這樣的索引也應(yīng)該刪除掉。

小伙伴們知道，索引雖然可以提高查詢速度，但是卻會(huì)降低插入和修改速度。

在 MySQL 的元數(shù)據(jù)庫(kù) sys 中有一個(gè)名為 schema_unused_indexes 的視圖，該視圖中就保存了各種創(chuàng)建了但是未使用的索引：

4. 手動(dòng)更新索引統(tǒng)計(jì)信息

當(dāng)我們想要查看一條 SQL 的執(zhí)行計(jì)劃時(shí)，這個(gè)執(zhí)行計(jì)劃中會(huì)展示出來這個(gè) SQL 執(zhí)行過程中大概會(huì)掃描多少行數(shù)據(jù)，如下：

這個(gè)預(yù)估的掃描行數(shù)非常重要，這是 MySQL 優(yōu)化器在執(zhí)行 SQL 的時(shí)候一個(gè)重要的參考指標(biāo)，如果表沒有這個(gè)統(tǒng)計(jì)信息，或者統(tǒng)計(jì)信息不準(zhǔn)確，那么就有可能導(dǎo)致優(yōu)化器做出錯(cuò)誤的決定。

當(dāng)滿足如下條件的時(shí)候，這個(gè)統(tǒng)計(jì)信息會(huì)自動(dòng)生成或者更新：

首次打開表。
表大小發(fā)生變化。
執(zhí)行 SHOW TABLE STATUS
執(zhí)行 SHOW INDEX
MySQL 客戶端開啟自動(dòng)補(bǔ)全功能
打開 infomation_schema 庫(kù)中一些相關(guān)的表

這些行為都會(huì)觸發(fā)統(tǒng)計(jì)信息的自動(dòng)更新，如果表中數(shù)據(jù)量比較大，擔(dān)心以上行為降低表的性能，那么也可以修改 innodb_stats_on_metadata 參數(shù)來關(guān)閉以上行為。

當(dāng)然，我們也可以手動(dòng)執(zhí)行 analyze table 命令來更新索引的統(tǒng)計(jì)信息。

5. 適時(shí)優(yōu)化表

InnoDB 中的索引是一個(gè) B+Tree，這個(gè)我們?cè)谥暗奈恼轮芯秃托』锇閭兞倪^了。B+Tree 通過一個(gè)多路平衡查找樹將數(shù)據(jù)組織在一起，然而這個(gè)樹中的各個(gè)結(jié)點(diǎn)在存儲(chǔ)的時(shí)候在物理分布上卻并不一定連續(xù)，如果是連續(xù)的，則在數(shù)據(jù)操作的時(shí)候就會(huì)快很多，如果不需連續(xù)，數(shù)據(jù)操作性能必然會(huì)有下降，一般來說，存在這樣幾種不同的碎片形式：

行碎片：數(shù)據(jù)行分布在不同的地方，讀取數(shù)據(jù)行的時(shí)候涉及到多次隨機(jī) IO。
行間碎片：邏輯上應(yīng)該是連續(xù)的行或者數(shù)據(jù)頁(yè)，在磁盤上存儲(chǔ)時(shí)并不連續(xù)。原本全表掃描的時(shí)候是順序 IO，現(xiàn)在變成了隨機(jī) IO。
剩余空間碎片：小伙伴們知道，InnoDB 操作數(shù)據(jù)表最基本單位是頁(yè)，一頁(yè)是 16KB，也就是 InnoDB 從磁盤上讀、往磁盤上寫，最低單位都是 16KB，有時(shí)候這 16KB 中，有效數(shù)據(jù)很少，其他地方都是剩余空間，就會(huì)讓 InnoDB 在讀寫數(shù)據(jù)的時(shí)候造成很大浪費(fèi)。

對(duì)于以上情況，我們可以通過執(zhí)行 optimize table 來重新整理數(shù)據(jù)，如果存儲(chǔ)引擎不支持 optimize table 命令，那么我們也可以通過執(zhí)行 alter table <table> engine=xxx 命令來實(shí)現(xiàn)數(shù)據(jù)的重整（命令中的 xxx 就是表原本的引擎）。