高性能mysql優化二之索引篇

04-27

前言

為什麼要使用索引？索引有什麼用途呢？我的親身經歷，一個幾千萬數據的項目，我寫了一條查詢，沒有用到索引，由於訪問量比較大，瞬間網站就跪了，從此以後我寫的每一條sql都會explain解析看是否用到索引或者掃描錶行數。所以索引是對查詢起著至關重要的作用，良好的索引能讓你的應用從背著一個幾百斤的胖子爬行變為坐著火箭飛速前行。例如：你的資料庫原本有30W條數據，沒建索引前，可能你的很多查詢都是全表掃描，什麼意思呢？就是你的每一次查詢都要去查詢30W條數據。而當你建立良好的索引後，他只會去掃描你需要的那幾行。所以你的查詢速度會有一個質變。

一、建表和欄位注意

選擇索引的數據類型

MySQL支持很多數據類型，選擇合適的數據類型存儲數據對性能有很大的影響。通常來說，可以遵循以下一些指導原則：

(1)越小的數據類型通常更好：越小的數據類型通常在磁碟、內存和CPU緩存中都需要更少的空間，處理起來更快。

(2)簡單的數據類型更好：整型數據比起字元，處理開銷更小，因為字元串的比較更複雜。在MySQL中，應該用內置的日期和時間數據類型，而不是用字元串來存儲時間；以及用整型數據類型存儲IP地址。

(3)盡量避免NULL：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值。

1.1、選擇標識符

選擇合適的標識符是非常重要的。選擇時不僅應該考慮存儲類型，而且應該考慮MySQL是怎樣進行運算和比較的。一旦選定數據類型，應該保證所有相關的表都使用相同的數據類型。

(1) 整型：通常是作為標識符的最好選擇，因為可以更快的處理，而且可以設置為AUTO_INCREMENT。

(2) 字元串：盡量避免使用字元串作為標識符，它們消耗更大的空間，處理起來也較慢。而且，通常來說，字元串都是隨機的，所以它們在索引中的位置也是隨機的，這會導致頁面分裂、隨機訪問磁碟，聚簇索引分裂（對於使用聚簇索引的存儲引擎）。

二、創建高性能索引

獨立的列

獨立的列是指索引列不能是表達式的一部分，也不是是函數的參數。例如以下兩個查詢無法使用索引：

1）表達式： select actor_id from sakila.actor where actor_id+1=5;

2）函數參數：select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

前綴索引和索引選擇性

通常可以索引開始的部分字元，這樣可以大大節約索引空間，從而提高索引效率。但這樣也會降低索引的選擇性。索引的選擇性是指，不重複的索引值（基數）和數據表中的記錄總數（#T）的比值，範圍從1/#T之間。索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MYSQL在查找時過濾掉更多的行。

唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對於BLOB、TEXT或者很長的VARCHAR類型的列，必須使用前綴索引，因為MYSQL不允許索引這些列的完整長度。

決竅在於要選擇足夠長的前綴以保證較高的選擇性，同時又不能太長（以便節約空間）。前綴應該足夠長，以使得前綴索引的選擇性接近於索引整個列。換句話說，前綴的「基數」應該接近於完整列的「基數」。

為了決定前綴的合適長度，需要找到最常見的值的列表，然後和最常見的前綴列表進行比較。例如以下查詢：

select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;

select count(*) as cnt,left(city,7) as perf from sakila.city_demo group by city order by cnt desc limit 10;

直到這個前綴的選擇性接近完整列的選擇性。

計算合適的前綴長度的另一個方法就是計算完整列的選擇性，並使前綴的選擇性接近於完整列的選擇性，如下：

select count(distinct city)/count(*) from sakila.city_demo;

select count(distinct left(city,7))/count(*) from sakila.city_demo;

前綴索引是一種能使索引更小、更快的有效辦法，但另一方面也有其缺點：MYSQL無法使用前綴索引做order by和group by，也無法使用前綴索引做覆蓋掃描。

多列索引

一個多列索引與多個列索引MYSQL在解析執行上是不一樣的，如果在explain中看到有索引合併，應該好好檢查一下查詢的表和結構是不是已經最優。

選擇合適的索引列順序

對於如何選擇索引的順序有一個經驗法則：將選擇性最高的列放在索引最前列。

當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是最好的。然後，性能不只是依賴於所有索引列的選擇性（整體基數），也和查詢條件的具體值有關，也就是和值的分布有關。這和前面介紹的選擇前綴的長度需要考慮的地方一樣。可能需要根據那些運行頻率最高的查詢來調整索引列的順序，讓這種情況下索引的選擇性最高。

使用經驗法則要注意不要假設平均情況下的性能也能代表特殊情況下的性能，特殊情況可能會摧毀整個應用的性能（當使用前綴索引時，在某些條件值的基數比正常值高的時候）。

覆蓋索引(Covering Indexes)

覆蓋索引是一種非常強大的工具，能大大提高查詢性能。設計優秀的索引應該考慮到整個查詢，而不單單的where條件部分。索引確實是一種查找數據的高效方式，但是MYSQL也可以使用索引來直接獲取列的數據，這樣就不再需要讀取數據行。索引的葉子節點中已經包含要查詢的數據，那麼就沒有必要再回表查詢了，如果索引包含滿足查詢的所有數據，就稱為覆蓋索引。

只需要讀取索引而不用讀取數據有以下一些優點：

(1)索引項通常比記錄要小，所以MySQL訪問更少的數據；

(2)索引都按值的大小順序存儲，相對於隨機訪問記錄，需要更少的I/O；

(3)大多數據引擎能更好的緩存索引。比如MyISAM只緩存索引。

(4)覆蓋索引對於InnoDB表尤其有用，因為InnoDB使用聚集索引組織數據，如果二級索引中包含查詢所需的數據，就不再需要在聚集索引中查找了。

覆蓋索引不能是任何索引，只有B-TREE索引存儲相應的值。而且不同的存儲引擎實現覆蓋索引的方式都不同，並不是所有存儲引擎都支持覆蓋索引(Memory和Falcon就不支持)。

向了解更多可以關注我的博客：姚茂磊的博客_web學習者的最佳選擇