Mysql索引和最佳化

1、選擇索引的資料類型

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

(1)越小的資料類型通常更好：越小的資料類型通常在磁碟、記憶體和CPU快取中都需要更少的空間，處理起來更快。
(2)簡單的資料類型更好：整型資料比起字符，處理開銷更小，因為字串的比較更複雜。在MySQL中，應該用內建的日期和時間資料類型，而不是用字串來儲存時間；以及用整數資料類型儲存IP位址。
(3)盡量避免NULL：應該指定列為NOT NULL，除非你想儲存NULL。在MySQL中，含有空值的資料列很難進行查詢最佳化，因為它們使得索引、索引的統計資料以及比較運算更加複雜。你應該用0、一個特殊的值或一個空串來代替空值。

1.1、選擇標識符
選擇合適的標識符是非常重要的。選擇時不僅應該考慮​​儲存類型，而且應該考慮MySQL是怎麼進行運算和比較的。一旦選定資料類型，應該確保所有相關的表都使用相同的資料類型。
(1)    整型：通常是作為標識符的最佳選擇，因為可以更快的處理，並且可以設定為AUTO_INCREMENT。

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

2、索引入門
對於任何DBMS，索引都是進行最佳化的最主要的因素。對於少量的數據，沒有合適的索引影響不是很大，但是，當隨著數據量的增加，效能會急劇下降。
如果對多列進行索引(組合索引)，列的順序非常重要，MySQL僅能對索引最左邊的前綴進行有效的查找。例如：
假 設存在組合索引it1c1c2(c1,c2)，查詢語句select * from t1 where c1=1 and c2=2能夠使用該索引。查詢語句select * from t1 where c1=1也能夠使用該索引。但是，查詢語句select * from t1 where c2=2不能夠使用該索引，因為沒有組合索引的引導列，即，要想使用c2列進行查找，必需出現c1等於某值。

2.1、索引的類型
索引是在儲存引擎中實現的，而不是在伺服器層中實現的。所以，每種儲存引擎的索引不一定完全相同，並不是所有的儲存引擎都支援所有的索引類型。
2.1.1、B-Tree索引
假設有以下一個表：

CREATE TABLE People (
     last_name varchar(50)    not null,
     first_name varchar(50)    not   null,
     dob        date           not null,
     gender     enum('m', 'f') not   null,
     key(last_name, first_name, dob)
);

 其索引包含表中每一行的last_name、first_name和dob欄位。其結構大致如下：

索引儲存的值依索引列中的順序排列。可以利用B-Tree索引進行全關鍵字、關鍵字範圍和關鍵字前綴查詢，當然，如果想使用索引，你必須保證按索引的最左邊前綴(leftmost prefix of the index)來進行查詢。
(1)符合全值(Match the full value)：對索引中的所有欄位都指定具體的值。例如，上圖索引可以幫助你找出出生於1960-01-01的Cuba Allen。
(2)符合最左字首(Match a leftmost prefix)：你可以利用索引來找出last name為Allen的人，只使用索引中的第1列。
(3)匹配列前綴(Match a column prefix)：例如，你可以利用索引查找last name以J開始的人，這僅使用索引中的第1列。
(4)匹配值的範圍查詢(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之間的人，僅使用索引中第1列。
(5)匹配部分精確而其它部分進行範圍匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開始的人。
(6)僅對索引進行查詢(Index-only queries)：如果查詢的欄位都位於索引中，則不需要讀取元組的值。
由於B-樹中的節點都是順序儲存的，所以可以利用索引進行查找(找某些值)，也可以對查詢結果進行ORDER BY。當然，使用B-tree索引有以下一些限制：
(1) 查詢必須從索引的最左邊的欄位開始。關於這點已經提了很多遍了。例如你不能利用索引來找出在某一天出生的人。
(2) 不能跳過某一索引列。例如，你不能利用索引來尋找last name為Smith且出生於某一天的人。
(3) 儲存引擎不能使用索引中範圍條件右邊的欄位。例如，如果你的查詢語句為WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，則該查詢只會使用索引中的前兩列，因為LIKE是範圍查詢。

以上就是Mysql索引和優化的內容，更多相關內容請關注PHP中文網（www.php.cn）！