我們知道數據的存儲和檢索是兩個很重要的功能，當我們的數據量大了，怎麼能快速的檢索數據呢，答案是使用索引，可索引具體的技術實現有很多，選擇哪一種呢，我就以mysql為例記錄下它為什麼選擇了B+樹作為索引的實現方式。

1.  索引簡介

　　索引是一種用於快速查詢行的數據結構，就像一本書的目錄就是一個索引，如果想在一本書中找到某個主題，一般會先找到對應頁碼。MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。提取句子主幹，就可以得到索引的本質：索引是數據結構。

 2.  索引的幾種數據結構類型

2.1  哈希索引（hash index）

哈希索引（hash index）基於哈希表（也可以叫散列表）實現，只有精確匹配索引所有列的查詢才有效。對於每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼（hash code）,哈希碼是一個較小的值，並且不同鍵值的行計算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數據行的指針。

​

下麵舉個小例子

​

 它能快速的檢索數據，不過在mysql資料庫卻有局限：

a):  哈希索引數據並不是按照索引值順序存儲的，所以無法用來進行排序；

b):  不能進行多列欄位查詢數據；

c):  更不支持範圍查詢，比如查詢年齡大於30,。

d):  有大量重覆鍵值的情況下，哈希索引的效率也是極低的（出現哈希碰撞問題，比如示例中才十幾條數據j）

因為這些限制，哈希索引只適用於某些特殊的場合，mysql並沒有選擇哈希索引。

2.2  樹Tree

學過數據結構和演算法的人都知道，樹是一種抽象數據類型，或是實作這種抽象數據類型的數據結構，用來模擬具有樹狀結構性質的數據集合， 樹有很多種：二叉樹(Binary Tree)，二叉查找樹（Binary Search Tree），平衡二叉查找樹（Balanced Binary Search Tree），紅黑樹(Red-Black Tree )，B-tree/B+-tree/ B*-tree (B~Tree)。

​

樹有以下特性：

• 每個節點有零個或多個子節點；
• 沒有父節點的節點稱為根節點；
• 每一個非根節點有且只有一個父節點；
• 除了根節點外，每個子節點可以分為多個不相交的子樹
• 等等

2.2.1  二叉樹(Binary Tree)

它有以下特性：

• 具有唯一根節點
• 每個節點最多有兩個子節點
• 每個節點最多有一個父節點
• 具有天然的遞歸結構
• 每個節點的左子樹也是二叉樹
• 每個節點的右子樹也是二叉樹

2.2.2   二叉查找樹（Binary Search Tree）

​

 它有以下特性：

• 是二叉樹

• 每個節點的值

  • 大於其左子樹的所有節點的值
  • 小於其右子樹的所有節點的值
• 每一顆子樹也是二分搜索樹

示例​

不過有一種極端情況：

​

此時該平衡二叉查找樹登場了

2.2.3  AVL樹（平衡二叉查找樹（Balanced Binary Search Tree）的一種））

它有以下特性：

• 在二叉樹的基礎上，要求兩個子樹的高度差不能超過1；
• 每次增刪都會通過一次或多次旋轉來平衡二叉樹；

以二叉查找樹極端情況為例，那麼在平衡二叉樹時的情況：

​

2.2.4  紅黑樹(Red-Black Tree )，平衡二叉查找樹（Balanced Binary Search Tree）的一種）

它有以下特性：

• 節點要麼黑要麼紅；

• 根節點一定時黑色；
• 所有葉節點都為null，且為黑色；
• 紅色節點的兩個子節點都為黑色，不會有兩個連續的紅；
• 任意一個路徑上的黑節點數，一定時相等的；

示例：​

2.2.5  B樹也即B-tree

​

B樹也稱作B-樹，它是一顆多路平衡查找樹，我們描述一顆B樹時需要指定它的階數，階數表示了一個結點最多有多少個孩子結點，一般用字母m表示階數，當m取2時，就是我們常見的二叉搜索樹。

​

它有以下特性：

• 每個結點最多有m-1個關鍵字。
• 根結點最少可以只有1個關鍵字。
• 非根結點至少有Math.ceil(m/2)-1個關鍵字。
• 每個結點中的關鍵字都按照從小到大的順序排列，每個關鍵字的左子樹中的所有關鍵字都小於它，而右子樹中的所有關鍵字都大於它。
• 所有葉子結點都位於同一層，或者說根結點到每個葉子結點的長度都相同。

示例：​

2.2.6  B+樹

輪到主角登場了，​

B+樹是在B樹的基礎上做了升級優化

它有以下特性：

• B+樹包含2種類型的結點：內部結點（也稱索引結點）和葉子結點。根結點本身即可以是內部結點，也可以是葉子結點。根結點的關鍵字個數最少可以只有1個。

• B+樹與B樹最大的不同是內部結點不保存數據，只用於索引，所有數據（或者說記錄）都保存在葉子結點中。

• m階B+樹表示了內部結點最多有m-1個關鍵字（或者說內部結點最多有m個子樹），階數m同時限制了葉子結點最多存儲m-1個記錄。

• 內部結點中的key都按照從小到大的順序排列，對於內部結點中的一個key，左樹中的所有key都小於它，右子樹中的key都大於等於它。葉子結點中的記錄也按照key的大小排列。

• 每個葉子結點都存有相鄰葉子結點的指針，葉子結點本身依關鍵字的大小自小而大順序鏈接。

示例​

綜合考慮比較後（儘量保證樹不要太高，少讀寫磁碟IO，但存的數據要多，且支持經常使用的範圍、排序等功能），針對mysql資料庫而言，只剩下B+樹更合適做索引。

2.3  mysql中B+Tree索引的應用

先普及下mysql

2.3.0 mysql資料庫

首先簡單瞭解一下MySQL的體繫結構。

MySQL的邏輯結構

​

Connectors：用來與客戶端應用程式建立連接的資料庫介面。

Management Services & Utilities：系統管理和服務控制相關的輔助工具。

Connection Pool：負責處理與用戶訪問有關的各種用戶登錄、線程處理、記憶體和進程緩存需求。

Sql Interface：提供從用戶接受命令並把結果返回給用戶的機制。

Parser：對SQL語句進行語法分析和解析，構造一個月來執行查詢的數據結構。

Optimizer：優化查詢語句，以保證數據檢索動作的效率達到或者非常接近最最優。使用一種“選取-投影-聯結”策略來處理查詢，即先根據有關的限制條件進行選取(Select 操作)以減少將要處理的元組個數，再進行投影以減少被選取元組力的屬性欄位的個數，最後根據連接條件生產最終的查詢結果。

Caches & Buffers：保證使用頻率最高的數據或結構能夠以最有效率的方式被訪問，緩存的類型有：表緩存、記錄緩存、鍵緩存、許可權緩存、主機名緩存等。

Query流程

​

1、查詢緩存

檢查查詢緩存是否打開，檢查是否命中緩存中的數據（通過對大小寫敏感的HASH查找實現的），若不命中則進行下一階段的處理。若命中查詢緩存，檢查用戶許可權，若許可權沒問題，則直接把緩存數據返回給客戶端。

2、語法解析器和預處理器

詞法/語法解析器：將會進行語法規則的驗證和解析查詢（對語法解析），生成語法分析樹。

預處理器：根據MySQL規則進一步檢查語法分析樹是否合法。例如檢查表或列是否存在，解析名字和別名有沒有歧義。下一步預處理器會驗證許可權。

3、查詢優化器

優化器的作用就是找到最好的執行計劃。MySQL使用CBO優化器。MySQL使用很多優化策略生成最優的執行計劃，可以分為兩類：靜態優化（編譯時優化）、動態優化（運行時優化）。

4、查詢執行引擎

MySQL只是簡單的根據執行計劃給出的指令逐步執行。調用存儲引擎實現的介面來完成執行計劃。優化器根據介面可以獲取表的相關信息，包括表的所有列名、索引統計信息等。將結果返回給客戶端，或者返回這個查詢的一些信息，如查詢影響到的行數。如果查詢可以被緩存，那麼MySQL會將結果存放到查詢緩存中。

影響MySQL資料庫的常見因素

1、伺服器硬體

CPU：一般情況下CPU資源不會是性能瓶頸的直接原因；MySQL不支持多cpu對同一SQL併發處理。

記憶體：直接影響MySQL緩衝池的大小及MySQL資料庫的整體運行穩定性；如記憶體資源不足，容易造成MySQL的會話擁堵甚至實例重啟。

存儲IO：直接影響MySQL的處理性能；在大量數據變更的業務場景下，對存儲的IO性能要求往往較高。

2、資料庫存儲引擎

MyISAM：不支持事務型查詢，在OLTP類型業務場景中不建議使用。

InnoDB：支持事務型查詢，支持行級鎖，對併發業務支持較好。

3、MySQL參數

1）連接請求的參數：max_connections

MySQL的最大連接數，增加該值增加mysqld要求的文件描述符的數量。連接請求量大時，建議調高此值調的越高，記憶體開銷越大。

mysql>show variables like 'max_connections';

+-------------------------+----------+

|Variable_name|Value|

+-------------------------+----------+

|max_connections|512 |

+-------------------------+----------+

mysql>show status like 'max%connections';

+---------------------------+----------+

|Variable_name|Value|

+---------------------------+----------+

|max_used_connections|512 |

+---------------------------+----------+

2）全局緩存參數

key_buffer_size指定索引緩衝區的大小，它決定索引處理的速度，尤其是索引讀的速度。Key_reads是記憶體中沒有找到索引直接從硬碟讀取索引的數量。

mysql>show variables like' key_buffer_size'; 

+-------------------------+-------------+

|Variable_name|Value|

+-------------------------+-------------+

|key_buffer_size|536870912 |

+-------------------------+-------------+

mysql>show status like 'key_read%'; 

+-------------------------+---------------+

|Variable_name|Value|

+-------------------------+---------------+

|Key_read_requests|178306331520 |

|Key_reads|67 |

 +-------------------------+---------------+

使用查詢緩衝，MySQL將查詢結果存放在緩衝區中，今後對於同樣的SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。

mysql>show variables like ' key_buffer_size';

mysql>show status like ' key_read%';

查詢緩存碎片率= Qcache_free_blocks/ Qcache_total_blocks* 100%

查詢緩存利用率= (query_cache_size–Qcache_free_memory) / query_cache_size* 100%

查詢緩存命中率= (Qcache_hits–Qcache_inserts) / Qcache_hits* 100%

3）每個連接的緩存參數

① Sort_buffer_size

每個需要進行排序的線程分配該大小的一個緩衝區。增加這值加速ORDER BY或GROUP BY操作。預設數值是2097144(2M)，可改為16777208 (16M)。

② Join_buffer_size

聯合查詢操作所能使用的緩衝區大小。

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個線程獨占，也就是說，如果有100個線程連接，則占用為16M*100。

③ table_open_cache

表高速緩存的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被打開並放入其中，這樣可以更快地訪問表內容。

mysql> show global status like 'open%tables%';

+-----------------+-------+

| Variable_name| Value |

+-----------------+-------+

| Open_tables| 1024 |

| Opened_tables| 1465 |

+-----------------+-------+

mysql>showvariableslike'table_open_cache';

+----------------------+-------+

|Variable_name|Value|

+----------------------+-------+

|table_open_cache|1024|

+----------------------+-------+

④ tmp_table_size

臨時表大小。通過設置tmp_table_size選項來增加一張臨時表的大小，例如做高級GROUP BY操作生成的臨時表。

mysql>showglobal statuslike' created_tmp%';

+-----------------------------+----------+

|Variable_name|Value |

+-----------------------------+----------+

|Created_tmp_disk_tables|21197|

| Created_tmp_files| 58|

| Created_tmp_tables| 1771587 |

+-----------------------------+----------+

mysql> show variables like 'tmp_table_size';

+-----------------+------------+

| Variable_name| Value |

+-----------------+------------+

| tmp_table_size| 16777216 |

+-----------------+------------+

⑤ thread_cache_size

可以復用的保存在緩衝區中的線程的數量。當客戶端斷開之後，伺服器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀（前提是緩存數未達上限）。

mysql>show global status like 'Thread%';

+----------------------+-------+

|Variable_name|Value|

+----------------------+-------+

|Threads_cached|31|

|Threads_connected|239|

|Threads_created|2914|

|Threads_running|4|

+----------------------+-------+

mysql>show variables like 'thread_cache_size';

+---------------------+-------+

|Variable_name|Value|

+---------------------+-------+

|thread_cache_size|32|

+---------------------+-------+

4）配置InnoDB的參數

① Innodb_buffer_pool_size

InnoDB使用該參數指定大小的記憶體來緩衝數據和索引，其對InnoDB的重要性等於key_buffer_size對MyISAM的重要性。

② Innodb_log_buffer_size

Innodb_log緩存大小，一般為1-8M，預設為1M，對於較大的事務，可以增大緩存大小。可設置為4M或8M。

5）慢查詢參數：log_slow_queries

4、資料庫表設計

表體量過大：欄位過多或者記錄數過多的“大表”，在查詢中會消耗大量資源，且執行效率低；建議根據業務類型拆分大表（分區表）。

使用外鍵：無論是MySQL還是Oracle，都不建議採用外鍵進行表關聯。

缺少主鍵：無論對於主從同步還是查詢性能，主鍵發揮的作用都非常重要；建議所有業務表都添加主鍵。

5、SQL語

多表關聯：多表關聯容易造成關聯數據過大，影響查詢效率；建議查詢中的關聯表數量不超過2個。

全表掃描：觸發全表掃描容易造成大量IO讀寫，嚴重降低查詢效率；建議在查詢條件中加入帶索引的過濾條件。

根據現網環境優化執行的難易度，在優化順序可以按照：SQL語句->資料庫表設計->資料庫參數配置->資料庫存儲引擎->伺服器硬體。下麵重點論述上面第四、第五點，通過編寫高效的SQL語句，並以合適的方式創建表和索引，使系統始終保持良好的性能。

表設計建議

以合適的方式建立表，可以提高資料庫運行效率，有效降低歷史數據清理時的維護工作難度。

1、選定存儲引擎

MySQL支持多種存儲引擎，在處理不同類型的應用時，可以通過選擇使用不同的存儲引擎提高應用的效率，或者提供靈活的存儲。MySQL的存儲引擎包括：MyISAM、 InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。下麵是幾種常用的存儲引擎的對比和推薦使用方式。

​

其中，InnoDB 存儲引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。其設計目的主要面向線上事務處理（OLTP）及應用。但是對比 Myisam的存儲引擎，InnoDB 寫的處理效率差一些並且會占用更多的磁碟空間以保留數據和索引。從MySQL5.5版本開始，InnoDB存儲引擎是預設的存儲引擎。Myisam存儲引擎不支持事務，表鎖設計，支持群文索引，主要面向一些OLAP資料庫應用及Web應用。每個MyISAM在磁碟上存儲成三個文件。文件名都和表名相同，擴展名分別是.frm（存儲表定義）、.MYD (MYData，存儲數據)、.MYI (MYIndex，存儲索引)。數據文件和索引文件可以放置在不同的目錄，平均分佈IO，獲得更快的速度。在移動雲生產環境中我們建議所有業務表必須是innodb表。

2、表命名規範

1）命名大小寫規範：在 MySQL 中，資料庫對應數據目錄中的目錄。資料庫中的每個表至少對應資料庫目錄中的一個文件(也可能是多個，取決於存儲引擎)。因此，所使用操作系統的大小寫敏感性決定了資料庫名和表名的大小寫敏感性。這說明在大多數 Unix 中資料庫名和表名對大小寫敏感，而在 Windows 中對大小寫不敏感。MySQL有配置參數lower_case_table_names，不可動態更改，linux系統預設為 0，即庫表名以實際情況存儲，大小寫敏感。如果是1，以小寫存儲，大小寫不敏感。如果是2，以實際情況存儲，但以小寫比較。MySQL5.6預設為0。若大小寫混合使用，易導致使用及管理混亂，且欄位名顯式區分大小寫，但實際使用不區分，即不可以建立兩個名字一樣但大小寫不一樣的欄位。因此，建議為了統一規範, 庫名、表名、欄位名使用小寫字母，連接統一用下劃線‘_’。

2）命名字元長度規範：庫名、表名、欄位名支持最多64個字元，但為了統一規範、易於辨識以及減少傳輸量，禁止超過32個字元。

3）避免使用MySQL保留字：當庫名、表名、欄位名等屬性含有保留字時，SQL語句必須用反引號引用屬性名稱，這將使得SQL語句書寫、SHELL腳本中變數的轉義等變得非常複雜。

3、建立常規表

MySQL常規表對應到文件系統上單個數據文件。在MySQL5.6中建表時，不指定任何參數，預設會建立存儲引擎為innodb的常規表。常規表使用與大部分應用場景。預設情況下，由於部分操作系統對文件大小的限制，表大小限製為2G。

4、建立分區表

MySQL從5.1版本開始支持分區表，從5.6開始MySQL表分區以單個數據文件形式存儲於文件系統中，根據所使用的不同分區規則可以分成幾大類型：

RANGE 分區： 基於屬於一個給定連續區間的列值，把多行分配給分區。比較常用如按照時間欄位劃分分區，2019年1月的數據放到201901分區，2019年2月的數據放到201902分區以此類推。範圍分區方式適用於應用中頻繁對分區鍵值進行範圍查詢的場合。另外針對部監控表隨時間不斷累積數據，大量的歷史數據積壓，一方面會降低應用程式的效率，另一方面亦浪費大量的存儲空間。因此需要對歷史表進行定期清理，以基本保持當前總數據量。基於這個原則，建議對所有歷史表按清理時間鍵值進行範圍分區，時間範圍建議按月進行。表分區的命名採用以下的規範：<表名>_pYYYYMMDD，其中YYYY為分區數據的年份，MM為分區數據的月份，DD為分區數據的日期。

LIST 分區： 類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。列值分區與範圍分區有類似之處，該分區與範圍分區類似的是需要指定列的值，但是其分區值必須明確指定。 

HASH分區：基於用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL中有效的、產生非負整數值的任何表達式。此種分區方式最適用於查詢條件中，對分區欄位進行單值查詢的情況(如，col=1)。但是hash分區，並不適用於對索引欄位使用範圍查詢，如對欄位使用大於>，小於<，操作的查詢語句中。

KEY分區： 類似於按HASH分區，區別在於KEY分區只支持計算一列或多列，且MySQL伺服器提供其自身的哈希函數。必須有一列或多列包含整數值。

複合分區： 基於RANGE/LIST 類型的分區表中每個分區的再次分割。子分區可以是 HASH/KEY 等類型。

5、表欄位規範

• 儘量使用TINYINT、SMALLINT、MEDIUM_INT作為整數類型而非INT，如果非負則加上UNSIGNED；

• VARCHAR的長度只分配真正需要的空間；

• 使用枚舉或整數代替字元串類型；

• 儘量使用TIMESTAMP而非DATETIME；

• 單表不要有太多欄位，建議在20以內；

• 避免使用NULL欄位，很難查詢優化且占用額外索引空間；

• 用整型來存IP。

6、統一字元集

系統、服務端、客戶端、庫、表、開發程式端需統一字元集，通常中英文環境用utf8。

表使用建議

根據MySQL的表建立規範，以及在實際維護中的表使用經驗相結合，對錶使用作出如下的建議。

1、選擇合適的數據類型

InnoDB 存儲引擎和數據列。建議使用 varchar類型：對於InnoDB數據表，內部的行存儲格式沒有區分固定長度和可變長度列（所有數據行都使用指向數據列值的頭指針），因此在本質上，使用固定長度的 char列不一定比使用可變長度varchar列簡單。因而，主要的性能因素是數據行使用的存儲總量。由於CHAR平均占用的空間多於varchar，因此使用varchar來最小化需要處理的數據行的存儲總量和磁碟I/O是比較好的。

2、text和blob

在使用text和blob欄位類型時要註意以下幾點,以便更好的發揮資料庫的性能：

1）text和blob值在執行了大量的刪除或更新操作的時候容易影響效率。

刪除該類型值會在數據表中留下很大的"空洞"，以後填入這些"空洞"的記錄可能長度不同,為了提高性能,建議定期使用 OPTIMIZE TABLE 功能對這類表進行碎片整理。

2）使用合成的（synthetic）索引。

合成的索引列在某些時候是有用的。一種辦法是根據其它的列的內容建立一個散列值，並把這個值存儲在單獨的數據列中。之後可以通過檢索散列值找到數據。但是，這種索引只能用於精確匹配的查詢（散列值對於類似<或>=等範圍搜索操作符 是沒有用處的）。可以使用MD5()函數生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的應用程式邏輯來計算散列值。需註意數值型散列值可以很高效率地存儲。同樣，如果散列演算法生成的字元串帶有尾部空格，此時不要把它們存儲在char與varchar列中，它們會受到尾部空格去除的影響。合成的散列索引對於那些text和blob數據列特別有用。用散列標識符值查找的速度比搜索blob列本身的速度快很多。

3）把text或blob列分離到單獨的表中。

通過把這些數據列移動到單獨的數據表中，可以讓你把原數據表中的數據列轉換為固定長度的數據行格式。這會減少主表中的碎片，使你得到固定長度數據行的性能優勢。此時能避免在主數據表上運行 SELECT *查詢的時候通過網路傳輸大量的text或blob值。

3、拆分大欄位、訪問頻率低的欄位

將大欄位、訪問頻率低的欄位拆分到單獨的表中存儲,分離冷熱數據。有利於有效利用緩存，防止讀入無用的冷數據，較少磁碟IO，同時保證熱數據常駐記憶體提高緩存命中率。

4、數據文件磁碟分離

MySQL表以數據文件形式存儲於文件系統，針對不同的表的讀寫會打開不同的數據文件。建議對不同的熱表進行存儲的磁碟分離。通過將不同的熱表建立在不同的lun上，分散I/O，這樣就能進一步減少I/O消耗的瓶頸。

索引建立規範

建立合適的索引，是提高資料庫運行效率的一個很好的工具，這種效果是立竿見影的，但這裡也不並不是說表上的索引越多越好，過之而不及。在資料庫設計過程中，需要為表選擇一些合適的索引。在資料庫中索引的維護代價是表的3倍，寧缺勿濫，這是建立索引時的一個遵循標準。

索引使用規範

根據MySQL的索引使用經驗相結合，對索引使用做出如下的建議。

1、根據表數據量評估索引

詳細評估和分析建立索引所在表的實際數據量，數據量達到GB級別、記錄數達到百萬級別、訪問頻繁的表，需要建立合適的索引。相反，在數據量較少且訪問頻率不高的情況下，如只有一百行記錄以下的表不需要建立索引。因為在數據量少的情況下，使用全表掃描效果比走索引更好。

2、選擇適當的索引欄位

索引欄位的選擇需要結合業務需求，評估出應用中作為查詢條件出現比較頻繁的欄位，在此欄位上建立單獨或者複合索引。選擇建立索引的欄位，應該遵循以下的原則：

1）高選擇性，選擇性是指通過索引欄位查詢返回結果集占表總數據量的百分比，結果集占表總數據量的百分比越小選擇性越高，反之越低。選擇性越高，通過索引查詢返回的結果集越少，索引更為高效。在OLTP應用系統中，選擇性應高於1，也就是結果集占表總數據量的百分比應<1%。

2）空值少，避免在空值(Null)很多的欄位上建立B-tree索引，大量空值會降低索引效率，索引欄位中的空值占總數據量的百分比應少於10%。

3）數據分佈均勻，索引欄位中，個別數據值占總數據量的百分率明顯比其它數據值占總數據量的百分率高，表明該欄位數據值分佈不均，容易引起資料庫選擇錯誤索引，生成錯誤的查詢執行計劃。應該避免在數據值分佈不均的欄位上建立索引。

3、避免過度索引

每個額外的索引都要占用額外的磁碟空間，並降低寫操作的性能，這一點我們前面已經介紹過。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。如果有一個索引很少利用或從不使用，那麼會不必要地減緩表的修改速度。此外，MySQL在生成一個執行計劃時，要考慮各個索引，這也要費時間。創建多餘的索引給查詢優化帶來了更多的工作。索引太多，也可能會使 MySQL 選擇不到所要使用的最好索引。只保持所需的索引有利於查詢優化。如果想給已索引的表增加索引，應該考慮所要增加的索引是否是現有多列索引的最左索引。如果是，則就不要費力去增加這個索引了，因為已經有了。

4、使用唯一索引

考慮某列中值的分佈。對於唯一值的列，索引的效果最好，而具有多個重覆值的列，其索引效果最差。例如，存放年齡的列具有不同值，很容易區分各行。而用來記錄性別的列，只含有“ 男”和“女”，則對此列進行索引沒有多大用處（不管搜索哪個值，都會得出大約一半的行）。

5、使用短索引

如果對串列進行索引，應該指定一個首碼長度，只要有可能就應該這樣做。例如，如果有一個 CHAR(200) 列，如果在前10個或20個字元內，多數值是惟一的，那麼就不要對整個列進行索引。對前10個或20個字元進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁碟 I/O 較少，較短的值比較起來更快。更為重要的是，對於較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此，MySQL也可以在記憶體中容納更多的值。這增加了找到行而不用讀取索引中較多塊的可能性。

6、利用符合索引前置列

在創建一個 n 列的索引時，實際是創建了 MySQL 可利用的 n 個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左首碼。（這與索引一個列的首碼不同，索引一個列的首碼是利用該的前 n 個字元作為索引值。） 例如：(a,b,c)、(a,b),後者為冗餘索引。當SQL的where條件包含a，b時，能正確的走前一索引，後者作為冗餘沒有建立的必要。關鍵在於找到適合的前置列，可以避免建冗餘的索引。

7、考慮在列上進行的比較類型

索引可用於“ <”、“ < = ”、“ = ”、“ > =”、“ >”和 BETWEEN 運算。在模式具有一個直接量首碼時，索引也用於 LIKE 運算。如果只將某個列用於其他類型的運算時（如 STRCMP( )），對其進行索引沒有價值。

高效SQL編寫規範建議

1、大批量插入數據

如果同時執行大量的插入，建議使用多個值的INSERT語句(方法二)。這比使用分開INSERT語句快（方法一），一般情況下批量插入效率有幾倍的差別。

方法一：

insert into tablename values(1,2); 

insert into tablename values(1,3); 

insert into tablename values(1,4);

方法二：

Insert into tablename values(1,2),(1,3),(1,4); 

選擇後一種方法的原因有二。 

• 減少SQL語句解析的操作， MySQL沒有類似Oracle的share pool，採用方法二，只需要解析一次就能進行數據的插入操作；

• SQL語句較短，可以減少網路傳輸的IO。

此外，還有以下建議提高插入性能： 

• 通過使用 INSERT DELAYED 語句得到更高的速度。Delayed 的含義是讓 insert 語句馬上執行，其實數據都被放在記憶體的隊列中，並沒有真正寫入磁碟；

• 這比每條語句分別插入要快的多，但需要註意，DELAYED關鍵字只用於MyISAM，MEMORY這類只支持表鎖的存儲引擎； 

• 將索引文件和數據文件分在不同的磁碟上存放（利用建表中的選項）。

2、查詢優先還是更新（insert、update、delete）優先

MySQL 還允許改變語句調度的優先順序，它可以使來自多個客戶端的查詢更好地協作，這樣單個客戶端就不會由於鎖定而等待很長時間。改變優先順序還可以確保特定類型的查詢被處理得更快。我們首先應該確定應用的類型，判斷應用是以查詢為主還是以更新為主的，是確保查詢效率還是確保更新的效率，決定是查詢優先還是更新優先。下麵我們提到的改變調度策略的方法主要是針對只存在表鎖的存儲引擎，比如 MyISAM 、MEMROY、MERGE，對於Innodb 存儲引擎，語句的執行是由獲得行鎖的順序決定的。MySQL 的預設的調度策略可用總結如下：

1）寫入操作優先於讀取操作。

2）對某張數據表的寫入操作某一時刻只能發生一次，寫入請求按照它們到達的次序來處理。

3）對某張數據表的多個讀取操作可以同時地進行。MySQL 提供了幾個語句調節符，允許你修改它的調度策略：

• LOW_PRIORITY關鍵字應用於DELETE、INSERT、LOAD DATA、REPLACE和UPDATE；

• HIGH_PRIORITY關鍵字應用於SELECT和INSERT語句；

• DELAYED關鍵字應用於INSERT和REPLACE語句。

如果寫入操作是一個 LOW_PRIORITY（低優先順序）請求，那麼系統就不會認為它的優先順序高於讀取操作。在這種情況下，如果寫入者在等待的時候，第二個讀取者到達了，那麼就允許第二個讀取者插到寫入者之前。只有在沒有其它的讀取者的時候，才允許寫入者開始操作。這種調度修改可能存在 LOW_PRIORITY寫入操作永遠被阻塞的情況。SELECT 查詢的HIGH_PRIORITY（高優先順序）關鍵字也類似。它允許SELECT 插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優先順序更高。另外一種影響是，高優先順序的 SELECT 在正常的 SELECT 語句之前執行，因為這些語句會被寫入操作阻塞。如果希望所有支持LOW_PRIORITY 選項的語句都預設地按照低優先順序來處理，那麼 請使用--low-priority-updates 選項來啟動伺服器。通過使用 INSERTHIGH_PRIORITY 來把 INSERT 語句提高到正常的寫入優先順序，可以消除該選項對單個INSERT語句的影響。

3、避免出現select *

select * 操作在任何類型資料庫中都不是一個好的SQL開發習慣。使用select * 取出全部列，會讓優化器無法完成索引覆蓋掃描這類優化，會影響優化器對執行計劃的選擇，也會增加網路帶寬消耗，更會帶來額外的I/O,記憶體和CPU消耗。建議評估業務實際需要的列數，指定列名以取代select *。

• 規範：Select col1,col2,col3… from t1; 

• 不規範：Select * from t1。

4、避免使用insert..selec..語句

當使用insert...select...進行記錄的插入時，如果select的表是innodb類型的，不論insert的表是什麼類型的表，都會對select的表的紀錄進行鎖定。對於那些從Oracle遷移過來的應用，需要特別的註意，因為Oracle並不存在類似的問題，所以在Oracle的應用中insert...select...操作非常常見。例如：有時候會對比較多的紀錄進行統計分析，然後將統計的中間結果插入到另外一個表，這樣的操作因為進行的非常少，所以可能並沒有設置相應的索引。

如果遷移到MySQL資料庫後不進行相應的調整，那麼在進行這個操作期間，對需要select的表實際上是進行的全表掃描導致的所有記錄的鎖定，將會對應用的其他操作造成非常嚴重的影響。

究其主要原因，是因為MySQL在實現複製的機制時和Oracle是不同的，如果不進行select表的鎖定，則可能造成從資料庫在恢復期間插入結果集的不同，造成主從數據的不一致。如果不採用主從複製，關閉binlog並不能避免對select紀