前言

什麼是Mysql？你真的知道嗎？

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取資料的資料結構。資料庫查詢是資料庫的最主要功能之一，我們都希望查詢資料的速度能儘可能的快，因此資料庫系統的設計者會從查詢演算法的角度進行最佳化。

一、 MySQL如何使用索引

索引用於快速查詢具有特定列值的行。如果沒有索引，MySQL必須從第一行開始，然後遍歷整個表以找到相關的行。表越大，花費越多。如果表中有相關列的索引，MySQL可以快速確定要在資料檔案中間查詢的位置，而不必檢視所有資料。這比順序讀取每一行要快得多。

大多數MySQL索引（PRIMARY KEY，UNIQUE，INDEX和FULLTEXT）儲存在B樹（B-tree）中。例外情況：空間資料型別的索引使用R樹； MEMORY表還支援雜湊索引。 InnoDB對FULLTEXT索引使用倒排列表。

MySQL使用索引進行以下操作：

1.快速查詢與WHERE子句匹配的行

2.如果可以在多個索引之間進行選擇，則MySQL通常會使用查詢最小行數（最具選擇性的索引）的索引

3.有多列索引（也叫“複合索引”或者“聯合索引”），那麼最佳化器可以使用索引的任何最左字首來查詢行。 例如，如果在（col1，col2，col3）上有一個三列索引，則在（col1），（col1，col2）和（col1，col2，col3）上都有索引搜尋功能。

4.使用關聯（join）查詢從其他表中檢索行時，如果宣告相同的型別和大小，MySQL可以更有效地在列上使用索引。在這種情況下，如果將VARCHAR和CHAR宣告為相同的大小，則認為它們相同。例如，VARCHAR（10）和CHAR（10）的大小相同，但VARCHAR（10）和CHAR（15）的大小不同。

5.對於非二進位制字串列之間的比較，兩個列應使用相同的字符集

6.如果排序或分組是在可用索引的最左字首（例如，ORDER BY key_part1，key_part2）上完成的，則對錶進行排序或分組。如果在所有key部分後面都跟隨有DESC，則將以相反的順序讀取key。

7.在某些情況下，MySQL可以使用索引來滿足ORDER BY子句，並避免執行檔案排序操作時涉及的額外排序。

8.在某些情況下，可以最佳化查詢以檢索值而無需查詢資料行。（為查詢提供所有必要結果的索引稱為覆蓋索引）如果查詢僅從表中使用某些索引中包含的列，則可以從索引樹中檢索所選值以提高速度。

最後，索引對小表的查詢不太重要。當查詢需要訪問大多數行時，順序讀取比處理索引快。

二、避免全表掃描

當MySQL使用全表掃描來解析查詢時，EXPLAIN的輸出在type列中顯示ALL。 這通常在以下情況下發生：

1.表太小，以至於執行全表掃描要比索引查詢要快得多。對於少於10行且行長度較短的表，這是很常見的。

2.在ON或WHERE字句中沒有使用索引列。

3.將索引列與常量值進行比較，而MySQL已計算（基於索引樹）常量覆蓋了表的很大一部分並且表掃描會更快。

4.你正在透過另一列使用基數低的鍵（許多行與鍵值匹配）。在這種情況下，MySQL假定透過使用該鍵，它有可能執行許多鍵查詢，並且表掃描會更快。

對於小表，表掃描通常是合適的，並且對效能的影響可以忽略不計。

對於大表，可以嘗試以下技術，以避免最佳化器錯誤地選擇表掃描：

5.用ANALYZE TABLE tbl_name來更新key的分佈

6.使用FORCE INDEX來告訴MySQL相比於使用給定的索引來說，表掃描是非常昂貴的

三、列索引

B樹（B-tree）資料結構使索引可以在WHERE子句中快速找到與運算子（例如=，>，≤，BETWEEN，IN等）相對應的特定值，一組值或一系列值。

每個儲存引擎都會定義每個表的最大索引數和最大索引長度。所有儲存引擎支援每個表至少16個索引，並且索引總長度至少為256個位元組。

索引字首

用col_name(N)可以建立僅使用列的前N個字元的索引。在InnoDB表中，字首最長767位元組。

全文索引

FULLTEXT索引用於全文搜尋。僅InnoDB和MyISAM儲存引擎支援FULLTEXT索引，並且僅支援CHAR，VARCHAR和TEXT列。索引始終在整個列上進行，並且不支援列字首索引。

空間索引

指依據空間物件的位置和形狀或空間物件之間的某種空間關係按一定的順序排列的一種資料結構

MEMORY儲存引擎上的索引

預設情況下，MEMORY儲存引擎使用HASH索引，但也支援BTREE索引。

四、多列索引

MySQL可以建立複合索引（即多列上的索引）。 一個索引最多可以包含16列。

假設有一張表示這樣定義的：

CREATE TABLE test (    id         INT NOT NULL,    last_name  CHAR(30) NOT NULL,    first_name CHAR(30) NOT NULL,    PRIMARY KEY (id),    INDEX idx_name (last_name,first_name));

idx_name索引是建立在last_name和first_name列之上的索引，該索引可以用於指定了last_name和first_name值組合的查詢，也可以用於僅指定last_name值的查詢，因為該索引是最左字首匹配的。

因此，idx_name索引可以用於下列查詢：

SELECT * FROM test WHERE last_name='Jones';SELECT * FROM test WHERE last_name='Jones' AND first_name='John';SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N';

然而，idx_name索引不能用於下列查詢：

SELECT * FROM test WHERE first_name='John';SELECT * FROM test WHERE last_name='Jones' OR first_name='John';

考慮下面的SQL：

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果在col1和col2上存在一個多列索引，那麼可以直接抓取適當的行。如果col1和col2上分別存在單獨的單列索引，則最佳化器將嘗試使用索引合併最佳化，或者透過確定哪個索引需要排除更多行來查詢限制性最強的索引，並使用該索引來獲取行。

如果表具有多列索引，那麼最佳化器可以使用該索引的任何最左字首來查詢行。例如，如果有一個三列索引(col1, col2, col3)，那麼在(col1), (col1, col2), (col1, col2, col3) 上具有索引搜尋功能。

如果列不構成索引的最左字首，則MySQL無法使用索引執行查詢。

再看下面的SQL語句：

SELECT * FROM tbl_name WHERE col1=val1;SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; 如果在（col1, col2, col3）上存在複合索引，那麼只有前兩個查詢會使用。而後最後兩個查詢不會使用索引來執行查詢，因為（col2）和（col2，col3）並不是（col1，col2，col3）的最左字首。

五、B-Tree 和 Hash 索引的比較B樹索引特徵

B樹（B-tree）索引可用於使用=，>，>=，<，<=，BETWEEN運算子的表示式中的列比較。如果LIKE的引數是一個不以萬用字元開頭的常量字串，則該索引也可以用於LIKE比較。

下列這些子句不會使用索引：

/* the LIKE value begins with a wildcard character */SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';/*  the LIKE value is not a constant */SELECT * FROM tbl_name WHERE key_col LIKE other_col;

沒有覆蓋WHERE子句中所有AND級別的任何索引都不會用於最佳化查詢。換句話說，為了能夠使用索引，必須在每個AND組中使用索引的字首。

下列WHERE子句會使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3    /* index = 1 OR index = 2 */... WHERE index=1 OR A=10 AND index=2    /* optimized like "index_part1='hello'" */... WHERE index_part1='hello' AND index_part3=5    /* Can use index on index1 but not on index2 or index3 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面這些WHERE子句不會使用索引：

   /* index_part1 is not used */... WHERE index_part2=1 AND index_part3=2    /*  Index is not used in both parts of the WHERE clause  */... WHERE index=1 OR A=10    /* No index spans all rows  */... WHERE index_part1=1 OR index_part2=10

有時，即使有可用的索引，MySQL也不使用索引。發生這種情況的一種可能原因是，最佳化器估計使用索引將需要訪問表中很大比例的行。（在這種情況下，表掃描可能會更快，因為它需要更少的查詢。）但是，如果這樣的查詢使用LIMIT只檢索某些行，則MySQL仍然使用索引，因為它可以更快地找到返回結果的幾行。

1.雜湊索引只用於=或者<=>運算子的相等比較（但非常快），不用於比較運算子來查詢值的範圍。依賴於這種單值查詢的系統被稱為“鍵值對儲存”，為了將MySQL用於此類應用，請儘可能地使用雜湊索引。

2.最佳化器無法使用雜湊索引來加快 ORDER BY 操作。（雜湊型別的索引不能用於按順序搜尋下一個條目）

3.MySQL無法確定兩個值之間大約有多少行（範圍最佳化器使用它來決定使用哪個索引）

4.只有整個keys可用於搜尋行。（對於B樹索引，key的任何最左邊的字首都可用於查詢行）

B-tree

樹型資料結構，廣泛用於資料庫索引中。該結構始終保持有序，從而可以快速查詢精確匹配（等於運算子）和範圍（例如，大於，小於和BETWEEN運算子）。 此類索引可用於大多數儲存引擎，例如InnoDB和MyISAM。

因為B樹節點可以有很多子節點，所以B樹與二叉樹不同，後者的每個節點最多隻能有2個子節點。

術語B樹的使用旨在參考索引設計的一般類別。由於經典B樹設計中不存在複雜性，MySQL儲存引擎使用的B樹結構可能被視為變體。

Hash index

一種索引型別，專用於使用相等運算子而不是範圍運算子的查詢。 它可用於MEMORY表。 儘管出於歷史原因，雜湊索引是MEMORY表的預設索引，但是該儲存引擎還支援B樹索引，對於一般用途的查詢而言，B樹索引通常是更好的選擇。

六、最佳化資料大小

設計表以使得它們在磁碟上佔用最少的空間。 透過減少寫入磁碟和從磁碟讀取的資料量，這可以帶來巨大的改進。 較小的表通常在查詢執行期間處理其內容時需要較少的主記憶體。表資料的任何空間減少都會導致索引變小，從而可以更快地處理索引。

MySQL支援許多不同的儲存引擎（表型別）和行格式。對於每個表，可以決定使用哪種儲存和索引方法。為應用程式選擇適當的表格式可以大大提高效能。

Table Columns

1.儘可能使用最有效（最小）的資料型別。MySQL具有許多專門的型別，可以節省磁碟空間和記憶體。例如，如果可能，使用較小的整數型別以獲得較小的表。MEDIUMINT通常比INT更好，因為MEDIUMINT列使用的空間要少25％。

2.如果可能，將列宣告為NOT NULL。透過更好地使用索引並消除測試每個值是否為NULL的開銷，它可以使SQL操作更快。而且還節省了一些儲存空間，每列一位元。如果表中確實需要NULL值，那就用它們。只要避免使用預設設定，該預設設定允許每列中都為NULL值。

Row Format

1.為了透過壓縮形式儲存表資料來進一步減少空間，請在建立InnoDB表時指定ROW_FORMAT=COMPRESSED

Indexes

1.表的主鍵索引應儘可能短。這使得識別每一行變得容易而高效。對於InnoDB表，主鍵列在每個輔助索引條目中都是重複的，因此如果你有許多輔助索引，則較短的主鍵可節省大量空間。

2.僅建立需要提高查詢效能的索引。索引很適合檢索，但是會降低插入和更新操作的速度。如果你主要透過搜尋列的組合來訪問表，請在表上建立單個組合索引，而不是為每個列建立單獨的索引。索引的第一部分應該是最常用的列。如果從表中查詢時總是使用許多列，則索引中的第一列應是重複次數最多的列，以便更好地壓縮索引。

3.如果是一個長字串列，則很可能在第一個字元上具有唯一的字首，這種情況下最好使用MySQL字首進行索引（PS：只對前幾個字元進行索引）。索引越短越快，這不僅是因為它們需要較少的磁碟空間，而且還因為它們還會使索引快取中的命中次數增加，從而減少磁碟尋道次數。

Joins

1.在具有相同資料型別的不同表中宣告具有相同資訊的列，以加快基於相應列的聯接。

2.保持列名簡單，以便可以在不同的表中使用相同的名稱，並簡化聯接查詢。例如，在名為customer的表中，使用name列名代替customer_name。為了使你的名稱可移植到其他SQL伺服器中，請考慮將名稱長度控制在18個字元以內。

Normalization

1.通常，儘量保持所有資料不冗餘(資料庫理論中稱為第三正規化)。為它們分配唯一的id來代替一個重複冗長的值，根據需要在多個較小的表中重複這些id，並透過在join子句中引用id來連線查詢中的表。

七、最佳化資料型別數值型別

1.行的唯一標識最好使用數值而不是字串，因為大數值比相應的字串佔用更少的儲存位元組，因此傳輸和比較它們更快，佔用的記憶體也更少。

字元和字串型別

1.在比較來自不同列的值時，儘可能使用相同的字符集和排序規則宣告這些列，以避免在執行查詢時進行字串轉換。

2.對於小於8KB的列值，請使用二進位制VARCHAR而不是BLOB。 GROUP BY和ORDER BY子句可以生成臨時表，並且如果原始表不包含任何BLOB列，則這些臨時表可以使用MEMORY儲存引擎。

3.如果一個表包含名稱和地址等字串列，但是許多查詢沒有檢索這些列，那麼可以考慮將字串列分割成單獨的表，並在必要時使用帶有外來鍵的連線查詢。當MySQL從一行中檢索任何值時，它讀取包含該行所有列(可能還有其他相鄰行)的資料塊。保持每行較小，只包含最常用的列，可以讓每個資料塊容納更多的行。這種緊湊的表減少了常見查詢的磁碟I/O和記憶體使用。

4.當在InnoDB表中使用一個隨機生成的值作為主鍵時，最好在它前面加上一個升序值，比如當前日期和時間(如果可能的話)。當連續的主鍵值物理上彼此相鄰儲存時，InnoDB可以更快地插入和檢索它們。

其它

1.ORDER BY 和 GROUP BY 使用的列不一致，或者 在連線查詢中ORDER BY 或 GROUP BY 使用了第一個表以外的表的列時會使用臨時表。

2.MySQL對每個表有4096列的硬限制，但是對於給定的表，有效最大值可能會更少。 InnoDB對每個表有1017列的限制。

最後