MySQL優化索引-有解無憂

1. MySQL如何使用索引

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

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

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

快速查找與WHERE子句匹配的行
如果可以在多個索引之間進行選擇，則MySQL通常會使用查找最小行數（最具選擇性的索引）的索引
有多列索引（也叫“復合索引”或者“聯合索引”），那么優化器可以使用索引的任何最左前綴來查找行，例如，如果在（col1，col2，col3）上有一個三列索引，則在（col1），（col1，col2）和（col1，col2，col3）上都有索引搜索功能，
使用關聯（join）查詢從其他表中檢索行時，如果宣告相同的型別和大小，MySQL可以更有效地在列上使用索引，在這種情況下，如果將VARCHAR和CHAR宣告為相同的大小，則認為它們相同，例如，VARCHAR（10）和CHAR（10）的大小相同，但VARCHAR（10）和CHAR（15）的大小不同，
對于非二進制字串列之間的比較，兩個列應使用相同的字符集
如果排序或分組是在可用索引的最左前綴（例如，ORDER BY key_part1，key_part2）上完成的，則對表進行排序或分組，如果在所有key部分后面都跟隨有DESC，則將以相反的順序讀取key，
在某些情況下，MySQL可以使用索引來滿足ORDER BY子句，并避免執行檔案排序操作時涉及的額外排序，
在某些情況下，可以優化查詢以檢索值而無需查詢資料行，（為查詢提供所有必要結果的索引稱為覆寫索引）如果查詢僅從表中使用某些索引中包含的列，則可以從索引樹中檢索所選值以提高速度

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

2. 避免全表掃描

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

表太小，以至于執行全表掃描要比索引查找要快得多，對于少于10行且行長度較短的表，這是很常見的，
在ON或WHERE字句中沒有使用索引列，
將索引列與常量值進行比較，而MySQL已計算（基于索引樹）常量覆寫了表的很大一部分并且表掃描會更快，
你正在通過另一列使用基數低的鍵（許多行與鍵值匹配），在這種情況下，MySQL假定通過使用該鍵，它有可能執行許多鍵查找，并且表掃描會更快，

對于小表，表掃描通常是合適的，并且對性能的影響可以忽略不計，

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

用ANALYZE TABLE tbl_name來更新key的分布
使用FORCE INDEX來告訴MySQL相比于使用給定的索引來說，表掃描是非常昂貴的

3. 列索引

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索引，

4. 多列索引

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）的最左前綴，

5. 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仍然使用索引，因為它可以更快地找到回傳結果的幾行，

哈希索引特征

哈希索引與剛剛討論的索引具有一些不同的特征：

哈希索引只用于=或者<=>運算子的相等比較（但非常快），不用于比較運算子來查找值的范圍，依賴于這種單值查找的系統被稱為“鍵值對存盤”，為了將MySQL用于此類應用，請盡可能地使用哈希索引，
優化器無法使用哈希索引來加快 ORDER BY 操作，（哈希型別的索引不能用于按順序搜索下一個條目）
MySQL無法確定兩個值之間大約有多少行（范圍優化器使用它來決定使用哪個索引）
只有整個keys可用于搜索行，（對于B樹索引，key的任何最左邊的前綴都可用于查找行）

B-tree

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

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

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

Hash index

一種索引型別，專用于使用相等運算子而不是范圍運算子的查詢，它可用于MEMORY表，盡管出于歷史原因，哈希索引是MEMORY表的默認索引，但是該存盤引擎還支持B樹索引，對于一般用途的查詢而言，B樹索引通常是更好的選擇，

6. 優化資料大小

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

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

Table Columns

盡可能使用最有效（最小）的資料型別，MySQL具有許多專門的型別，可以節省磁盤空間和記憶體，例如，如果可能，使用較小的整數型別以獲得較小的表，MEDIUMINT通常比INT更好，因為MEDIUMINT列使用的空間要少25％，
如果可能，將列宣告為NOT NULL，通過更好地使用索引并消除測驗每個值是否為NULL的開銷，它可以使SQL操作更快，而且還節省了一些存盤空間，每列一位元，如果表中確實需要NULL值，那就用它們，只要避免使用默認設定，該默認設定允許每列中都為NULL值，

Row Format

為了通過壓縮形式存盤表資料來進一步減少空間，請在創建InnoDB表時指定ROW_FORMAT=COMPRESSED

Indexes

表的主鍵索引應盡可能短，這使得識別每一行變得容易而高效，對于InnoDB表，主鍵列在每個輔助索引條目中都是重復的，因此如果你有許多輔助索引，則較短的主鍵可節省大量空間，
僅創建需要提高查詢性能的索引，索引很適合檢索，但是會降低插入和更新操作的速度，如果你主要通過搜索列的組合來訪問表，請在表上創建單個組合索引，而不是為每個列創建單獨的索引，索引的第一部分應該是最常用的列，如果從表中查詢時總是使用許多列，則索引中的第一列應是重復次數最多的列，以便更好地壓縮索引，
如果是一個長字串列，則很可能在第一個字符上具有唯一的前綴，這種情況下最好使用MySQL前綴進行索引（PS：只對前幾個字符進行索引），索引越短越快，這不僅是因為它們需要較少的磁盤空間，而且還因為它們還會使索引快取中的命中次數增加，從而減少磁盤尋道次數，

Joins

在具有相同資料型別的不同表中宣告具有相同資訊的列，以加快基于相應列的聯接，
保持列名簡單，以便可以在不同的表中使用相同的名稱，并簡化聯接查詢，例如，在名為customer的表中，使用name列名代替customer_name，為了使你的名稱可移植到其他SQL服務器中，請考慮將名稱長度控制在18個字符以內，

Normalization

通常，盡量保持所有資料不冗余(資料庫理論中稱為第三范式)，為它們分配唯一的id來代替一個重復冗長的值，根據需要在多個較小的表中重復這些id，并通過在join子句中參考id來連接查詢中的表，

7. 優化資料型別

數值型別

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

字符和字串型別

在比較來自不同列的值時，盡可能使用相同的字符集和排序規則宣告這些列，以避免在運行查詢時進行字串轉換，
對于小于8KB的列值，請使用二進制VARCHAR而不是BLOB， GROUP BY和ORDER BY子句可以生成臨時表，并且如果原始表不包含任何BLOB列，則這些臨時表可以使用MEMORY存盤引擎，
如果一個表包含名稱和地址等字串列，但是許多查詢沒有檢索這些列，那么可以考慮將字串列分割成單獨的表，并在必要時使用帶有外鍵的連接查詢，當MySQL從一行中檢索任何值時，它讀取包含該行所有列(可能還有其他相鄰行)的資料塊，保持每行較小，只包含最常用的列，可以讓每個資料塊容納更多的行，這種緊湊的表減少了常見查詢的磁盤I/O和記憶體使用，
當在InnoDB表中使用一個隨機生成的值作為主鍵時，最好在它前面加上一個升序值，比如當前日期和時間(如果可能的話)，當連續的主鍵值物理上彼此相鄰存盤時，InnoDB可以更快地插入和檢索它們，

其它

ORDER BY 和 GROUP BY 使用的列不一致，或者在連接查詢中ORDER BY 或 GROUP BY 使用了第一個表以外的表的列時會使用臨時表
MySQL對每個表有4096列的硬限制，但是對于給定的表，有效最大值可能會更少， InnoDB對每個表有1017列的限制，

https://dev.mysql.com/doc/refman/5.7/en/optimization.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/239517.html

標籤：MySQL

上一篇：在C++連接mysql的路上，你掉進過哪些坑？

下一篇：何為“混合云”？