MySQL 索引及查詢最佳化

MySQL 索引及查詢最佳化

索引的類型 :

Ø 普通索引　　這是最基本的索引類型，沒唯一性之類的限制。

Ø 唯一性索引　和普通索引基本相同，但所有的索引列值保持唯一性。

Ø 主鍵索引　　主鍵是一種唯一索引，但必須指定為”PRIMARY KEY”。

Ø 全文索引　　MYSQL從3.23.23開始支援全文索引和全文檢索索引。在MYSQL中，全文索引的索引類型為FULLTEXT。全文索引可以在VARCHAR或者TEXT類型的列上建立。

大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B樹中儲存。

空間列類型的索引使用R-樹，MEMORY表支援hash索引。

單列索引和多列索引（複合索引）:

索引可以是單列索引，也可以是多列索引。對相關的列使用索引是提高SELECT操作效能的最佳途徑之一。

MySQL可以為多個列建立索引。一個索引可以包括15個列。對於某些列類型，可以索引列的左首碼，列的順序非常重要。

多列索引可以視為包含通過串連索引列的值而建立的值的排序的數組。一般來說，即使是限制最嚴格的單列索引，它的限制能力也遠遠低於多列索引。

多列索引有一個特點，即最左首碼（Leftmost Prefixing）。假如有一個多列索引為key(firstname lastname age)，當搜尋條件是以下各種列的組合和順序時，MySQL將使用該多列索引：

firstname，lastname，age

firstname，lastname

firstname

也就是說，相當於還建立了key(firstname lastname)和key(firstname)。

索引主要用於下面的操作：

Ø 快速找出匹配一個WHERE子句的行。

Ø 刪除行。當執行聯結時，從其它表檢索行。

Ø 對具體有索引的列key_col找出MAX()或MIN()值。由前置處理器進行最佳化，檢查是否對索引中在key_col之前發生所有關鍵字元素使用了WHERE key_part_# = constant。在這種情況下，MySQL為每個MIN()或MAX()運算式執行一次關鍵字尋找，並用常數替換它。如果所有運算式替換為常量，查詢立即返回。例如：

SELECT MIN(key2), MAX (key2)  FROM tb WHERE key1=10;

Ø 如果對一個可用關鍵字的最左面的首碼進行了排序或分組(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有關鍵字元素後面有DESC，關鍵字以倒序被讀取。

Ø 在一些情況中，可以對一個查詢進行最佳化以便不用查詢資料行即可以檢索值。如果查詢只使用來自某個表的數字型並且構成某些關鍵字的最左面首碼的列，為了更快，可以從索引樹檢索出值。

SELECT key_part3 FROM tb WHERE key_part1=1;

有時MySQL不使用索引，即使有可用的索引。一種情形是當最佳化器估計到使用索引將需要MySQL訪問表中的大部分行時。(在這種情況下，表掃描可能會更快些）。然而，如果此類查詢使用LIMIT只搜尋部分行，MySQL則使用索引，因為它可以更快地找到幾行並在結果中返回。例如：

合理的建立索引的建議：

(1)  越小的資料類型通常更好：越小的資料類型通常在磁碟、記憶體和CPU緩衝中都需要更少的空間，處理起來更快。

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

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

這部分是關於索引和寫SQL語句時應當注意的一些瑣碎建議和注意點 :

1. 當結果集只有一行資料時使用LIMIT 1

2. 避免SELECT *，始終指定你需要的列

　從表中讀取越多的資料，查詢會變得更慢。他增加了磁碟需要操作的時間，還是在資料庫伺服器與WEB伺服器是獨立分開的情況下。你將會經曆非常漫長的網路延遲，僅僅是因為資料不必要的在伺服器之間傳輸。

3. 使用串連（JOIN）來代替子查詢(Sub-Queries)

    串連（JOIN）.. 之所以更有效率一些，是因為MySQL不需要在記憶體中建立暫存資料表來完成這個邏輯上的需要兩個步驟的查詢工作。

4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的欄位屬性長度

5. 儘可能的使用NOT NULL

6. 固定長度的表會更快

7. 拆分大的DELETE 或INSERT 語句

8. 查詢的列越小越快

Where條件 :

在查詢中，WHERE條件也是一個比較重要的因素，盡量少並且是合理的where條件是很重要的，盡量在多個條件的時候，把會提取盡量少資料量的條件放在前面，減少後一個where條件的查詢時間。

有些where條件會導致索引無效：

Ø where子句的查詢條件裡有！=，MySQL將無法使用索引。

Ø where子句使用了Mysql函數的時候，索引將無效，比如：select * from tb where left(name, 4) = ‘xxx’

Ø 使用LIKE進行搜尋匹配的時候，這樣索引是有效：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 時索引無效

# The ideal of life is for ideal life! #

本文永久更新連結地址：