文章來源地址http://www.zghlxwxcb.cn/news/detail-709771.html

1.?覆蓋索引

1.1.?設計優(yōu)秀的索引應該考慮到整個查詢，而不單是WHERE條件部分

1.2.?如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為覆蓋索引

1.3.?只有B-tree索引可以用于覆蓋索引

1.4.?如果查詢只需要掃描索引而無須回表

1.4.1.?索引條目通常遠小于數(shù)據行大小，所以如果只需要讀取索引，那么MySQL就會極大地減少數(shù)據訪問量

1.4.2.?覆蓋索引對于I/O密集型的應用也有幫助，因為索引比數(shù)據更小，更容易全部放入內存中

1.4.3.?因為索引是按照列值的順序存儲的（至少在單頁內如此），所以對于I/O密集型的范圍查詢會比隨機從磁盤讀取每一行數(shù)據的I/O要少得多

1.4.4.?由于InnoDB的聚簇索引的特點，覆蓋索引對InnoDB表特別有用

1.4.4.1.?InnoDB的二級索引在葉子節(jié)點中保存了記錄的主鍵值，所以如果二級索引能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢

1.5.?在索引中滿足查詢的成本一般比查詢記錄本身要小得多

2.?使用索引掃描來做排序

2.1.?生成有序的結果

2.1.1.?通過排序操作

2.1.2.?按索引順序掃描

2.2.?如果索引不能覆蓋查詢所需的全部列，那么就不得不每掃描一條索引記錄都回表查詢一次對應的記錄

2.2.1.?基本上都是隨機I/O

2.2.2.?按索引順序讀取數(shù)據的速度通常要比順序地全表掃描慢，尤其是在I/O密集型的應用負載上

2.3.?只有當索引的順序和ORDER BY子句的順序完全一致，并且所有列的排序方向（倒序或正序）都一樣時，MySQL才能使用索引來對結果做排序

2.4.?如果前導列為常量的時候，ORDER BY子句中的列也可以不滿足索引的最左前綴的要求

2.5.?如果在WHERE子句或者JOIN子句中將這些列指定為了常量，就可以“填補”索引字段的間隙了

2.6.?使用索引做排序的另一個最重要的場景是，查詢語句中同時有ORDERBY和LIMIT子句的情況

3.?重復索引

3.1.?指在相同的列上按照相同順序創(chuàng)建的相同類型的索引

3.2.?MySQL允許在相同列上創(chuàng)建多個相同的索引

3.2.1.?MySQL會拋出一個警告，但是并不會阻止你這么做

3.2.2.?MySQL需要單獨維護重復的索引，優(yōu)化器在優(yōu)化查詢的時候也需要逐個地進行評估，這會影響性能，同時也浪費磁盤空間

4.?冗余索引

4.1.?如果創(chuàng)建了索引（A，B），再創(chuàng)建索引（A）就是冗余索引

4.1.1.?索引（A，B）也可以當作索引（A）來使用

4.1.2.?前一個索引的前綴索引

4.1.3.?這種冗余只是對B-tree索引來說的

4.2.?如果再創(chuàng)建索引（B，A），則不是冗余索引，索引（B）也不是，因為B不是索引（A，B）的最左前綴列

4.3.?將一個索引擴展為（A，ID），其中ID是主鍵，因為主鍵列已經包含在二級索引中了，所以這也是冗余的

4.4.?冗余索引通常發(fā)生在為表添加新索引的時候

4.5.?大多數(shù)情況下都不需要冗余索引，應該盡量擴展已有的索引而不是創(chuàng)建新的索引

4.6.?出于性能方面的考慮也需要冗余索引，因為擴展已有的索引會導致其變得太大，從而影響其他使用該索引的查詢的性能

4.7.?索引越多，插入的速度越慢

4.7.1.?增加新索引會導致INSERT、UPDATE、DELETE等操作的速度變慢，特別是當新增索引后達到了內存瓶頸的時候

5.?未使用的索引

5.1.?一些服務器永遠不用的索引

5.2.?這樣的索引完全是累贅，建議刪除

5.3.?找到未使用索引的最好辦法就是使用系統(tǒng)數(shù)據庫performance_schema和sys

5.4.?在sys數(shù)據庫中，在table_io_waits_summary_by_index_usage視圖中可以非常簡單地知道哪些索引從來沒有被使用過

6.?解決冗余索引和重復索引的方法

6.1.?刪除這些索引就可以了

6.2.?針對INFORMATION_SCHEMA表編寫各種復雜的查詢來識別這類索引

6.3.?Percona工具箱中的pt-duplicate-key-checker，該工具通過分析表結構來找出冗余和重復索引

6.4.?使用Percona工具箱中的pt-upgrade工具來仔細檢查計劃中的索引變更

6.5.?使用MySQL 8.0的不可見索引特性，而不是直接刪除索引

6.5.1.?可以通過ALTER TABLE語句，改變索引的一個標志位，使得優(yōu)化器在確定執(zhí)行計劃時，忽略該索引

6.5.2.?如果你發(fā)現(xiàn)計劃刪除的索引依舊有非常重要的作用，可以直接把索引改成可見，而不需要重新構建該索引

7.?維護索引和表

7.1.?找到并修復損壞的表

7.1.1.?對于數(shù)據表來說，最糟糕的情況就是表被損壞了

7.1.2.?損壞的索引會導致查詢返回錯誤的結果或者出現(xiàn)莫須有的主鍵沖突等問題，嚴重時甚至還會導致數(shù)據庫的崩潰

7.1.3.?可以嘗試運行CHECK TABLE來檢查是否發(fā)生了表損壞

7.1.4.?可以使用REPAIR TABLE命令來修復損壞的表

7.1.5.?如果是InnoDB存儲引擎的表發(fā)生了損壞，那么一定是發(fā)生了嚴重的錯誤，需要立刻調查一下原因

7.1.5.1.?常見的類似錯誤通常是由于嘗試使用rsync備份InnoDB導致的

7.1.6.?如果遇到數(shù)據損壞，最重要的是找出是什么導致了損壞，而不只是簡單地修復，否則很有可能還會不斷地出現(xiàn)數(shù)據損壞的情況

7.2.?維護準確的索引統(tǒng)計信息

7.2.1.?MySQL的優(yōu)化器使用的是基于成本的模型，而衡量成本的主要指標就是一個查詢需要掃描多少行

7.2.2.?可以使用SHOW INDEX FROM命令來查看索引的基數(shù)（cardinality）

7.3.?減少索引和數(shù)據的碎片

7.3.1.?B-tree索引可能會產生碎片化，這會降低查詢的效率

7.3.2.?碎片化的索引可能會以很差或者無序的方式存儲在磁盤上

7.3.3.?如果葉子頁在物理分布上是順序且緊密的，那么查詢的性能就會更好

7.3.3.1.?否則，對于范圍查詢、索引覆蓋掃描等操作來說，速度可能會降低很多

7.3.3.2.?對于索引覆蓋掃描，這一點會表現(xiàn)得更加明顯

7.3.4.?行碎片（Row fragmentation）

7.3.4.1.?數(shù)據行被存儲在多個地方的多個片段中

7.3.4.2.?即使查詢只從索引中訪問一行記錄，行碎片也會導致性能下降

7.3.5.?行間碎片（Intra-row fragmentation）

7.3.5.1.?指邏輯上順序的頁或者行，在磁盤上不是順序存儲的

7.3.5.2.?對諸如全表掃描和聚簇索引掃描之類的操作有很大的影響，因為這些操作原本能夠從磁盤上順序存儲的數(shù)據中獲益

7.3.6.?剩余空間碎片（Free space fragmentation）

7.3.6.1.?指數(shù)據頁中有大量的空余空間

7.3.6.2.?導致服務器讀取大量不需要的數(shù)據，從而造成浪費

7.3.6.3.?可以通過執(zhí)行OPTIMIZE TABLE或者導出再導入的方式來重新整理數(shù)據

7.3.6.4.?對多數(shù)存儲引擎都是有效的

8.?原則

8.1.?單行訪問是很慢的，特別是在機械硬盤中存儲

8.1.1.?盡可能選擇合適的索引以避免單行查找

8.1.2.?SSD的隨機I/O要快很多，不過這一點仍然成立

8.2.?按順序訪問范圍數(shù)據是很快的

8.2.1.?順序I/O不需要多次磁盤尋道，所以比隨機I/O要快很多（特別是對于機械硬盤）

8.2.2.?如果服務器能夠按需順序讀取數(shù)據，那么就不再需要額外的排序操作，并且GROUP BY查詢也無須再做排序和將行按組進行聚合計算了

8.3.?索引覆蓋查詢是很快的

8.3.1.?如果一個索引包含了查詢需要的所有列，那么存儲引擎就不需要再回表查找行

8.4.?建議按響應時間來對查詢進行分析

8.4.1.?如果一個查詢無法從所有可能的索引中獲益，則應該看看是否可以創(chuàng)建一個更合適的索引來提升性能

到了這里，關于讀高性能MySQL（第4版）筆記09_創(chuàng)建高性能索引（下）的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！