我們知道資料的存盤和檢索是兩個很重要的功能，當我們的資料量大了，怎么能快速的檢索資料呢，答案是使用索引，可索引具體的技術實作有很多，選擇哪一種呢，我就以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紀錄的鎖定，如果使用這個binlog進行從資料庫的恢復，或者進行主資料庫的災難恢復，都將可能和主資料庫的執行效果不同，

因此，我們并不推薦通過設定這個引數來避免insert...select...導致的鎖，如果需要進行可能會掃描大量資料的insert...select操作，我們推薦使用select...into outfile和load data infile的組合來實作，這樣是不會對紀錄進行鎖定的，

例子：

INSERT INTO SMAP_HISTORY.SMAP2_SESSION (SESSION_ID,SESSION_TICKET_ID) SELECT S.SESSION_ID,S.SESSION_TICKET_ID FROM SMAP.SMAP2_SESSION S WHERE SESSION_SID = #sessionId#;

以上陳述句會對表SMAP2_SESSION施加表鎖，而由于業務上該表存在大量insert陳述句，業務壓力大的時候極易造成嚴重的阻塞，

 

5、適當使用commit

適當使用commit可以釋放事務占用的資源而減少消耗，commit后能釋放的資源如下：

• 事務占用的undo資料塊；

• 事務在redo log中記錄的資料塊； 

• 釋放事務施加的，減少鎖爭用影響性能，特別是在需要使用delete洗掉大量資料的時候，必須分解洗掉量并定期commit，

 

6、減少表的鎖沖突

對 Innodb 型別的表： 

1）首先要確認，在對表獲取行鎖的時候，要盡量的使用索引檢索紀錄，如果沒有使用索引訪問，那么即便你只是要更新其中的一行紀錄，也是全表鎖定的，要確保 sql 是使用索引來訪問紀錄的，必要的時候，請使用 explain 檢查 sql 的執行計劃，判斷是否按照預期使用了索引，

2）由于 MySQL 的行鎖是針對索引加的鎖，不是針對紀錄加的鎖，所以雖然是訪問不同行的紀錄，但是如果是相同的索引鍵，是會被加鎖的，應用設計的時候也要注意，這里和 Oracle 有比較大的不同， 

3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，當表有主鍵或者唯一索引的時候，不是必須使用主鍵或者唯一索引鎖定紀錄，其他普通索引同樣可以用來檢索紀錄，并只鎖定符合條件的行， 

4）如果要使用鎖定讀，（SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE），嘗試用更低的隔離級別，比如 READ COMMITTED，

 

7、使用SQL_BUFFER_RESULT減少鎖定時間

將強制 MySQL 生成一個臨時結果集，只要所有臨時結果集生成后，所有表上的鎖定均被釋放，這能在遇到表鎖定問題時或要花很長時間將結果傳給客戶端時有所幫助，當處理一個會讓客戶端耗費點時間才能處理的大結果集時，可以考慮使用SQL_BUFFER_RESULT 提示字，這樣可以告訴MySQL將結果集保存在一個臨時表中，這樣可以盡早的釋放各種鎖，需注意，該引數不能用于子查詢中以及union之后 語法：SELECT SQL_BUFFER_RESULT …

 

8、正確使用hint優化陳述句

MySQL中可以使用hint指定優化器在執行時選擇或忽略特定的索引，一般而言，處于版本變更帶來的表結構索引變化，更建議避免使用hint，而是通過Analyze table多收集統計資訊，但在特定場合下，指定hint可以排除其他索引干擾而指定更優的執行計劃，

1）USE INDEX 在你查詢陳述句中表名的后面，添加 USE INDEX 來提供希望 MySQL 去參考的索引串列，就可以讓 MySQL 不再考慮其他可用的索引，例子: SELECT col1 FROM table USE INDEX (mod_time, name)...

2）IGNORE INDEX 如果只是單純的想讓 MySQL 忽略一個或者多個索引，可以使用 IGNORE INDEX 作為 Hint，例子: SELECT col1 FROM table IGNORE INDEX (priority) ...

3）FORCE INDEX 為強制 MySQL 使用一個特定的索引，可在查詢中使用FORCE INDEX 作為Hint，例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

 

9、優化group by陳述句

默認情況下，MySQL 排序所有 “GROUP BY col1，col2，....;” 查詢的方法如同在查詢中指定 “ORDER BY col1，col2，...;” 如果顯式包括一個包含相同的列的 ORDER BY子句，MySQL 可以毫不減速地對它進行優化，盡管仍然進行排序，

如果查詢包括 GROUP BY 但你想要避免排序結果的消耗，你可以指定 ORDER BY NULL禁止排序，例如：

SELECT a, COUNT(1) FROM table GROUP BY a ORDER BY NULL ;

 

10、優化order by陳述句

在某些情況中，MySQL 可以使用一個索引來滿足 ORDER BY 子句，而不需要額外的排序，where 條件和 order by 使用相同的索引，并且 order by 的順序和索引順序相同 ，并且 order by 的欄位都是升序或者都是降序，

例如：下列 SQL 可以使用索引， 

SELECT col1 FROM t1 ORDER BY key_part1,key_part2,... ; 

SELECT col1 FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

以上復合索引包含欄位key_part1，key_part2... 

但是以下情況不使用索引：

SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 ASC； 

以上由于order by 的欄位混合 ASC和 DESC ，

SELECT col1 FROM t1 WHERE key2=constant ORDER BY key1； 

以上用于查詢行的關鍵字與 ORDER BY 中所使用的不相同，

SELECT col1 FROM t1 ORDER BY key1, key2； 

對不同的索引關鍵字使用 ORDER BY：

 

11、優化join陳述句

MySQL中可以通過子查詢來使用 SELECT 陳述句來創建一個單列的查詢結果，然后把這個結果作為過濾條件用在另一個查詢中，使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的 SQL 操作，同時也可以避免事務或者表鎖死，并且寫起來也很容易，但是，有些情況下，子查詢可以被更有效率的連接(JOIN)..替代，

例子：假設要將所有沒有訂單記錄的用戶取出來，可以用下面這個查詢完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID 

FROM salesinfo )

如果使用連接(JOIN).. 來完成這個查詢作業，速度將會有所提升，尤其是當 salesinfo表中對 CustomerID 建有索引的話，性能將會更好，查詢如下：

SELECT col1 FROM customerinfo 

LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 

WHERE salesinfo.CustomerID IS NULL 

連接(JOIN).. 之所以更有效率一些，是因為 MySQL 不需要在記憶體中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢作業，

 

12、優化or條件

對于 or 子句，如果要利用索引，則or 之間的每個條件列都必須用到索引；如果沒有索引，則應該考慮增加索引，

 

13、優化union查詢

MySQL通過創建并填充臨時表的方式來執行union查詢，除非確實要消除重復的行，否則建議使用union all，原因在于如果沒有all這個關鍵詞，MySQL會給臨時表加上distinct選項，這會導致對整個臨時表的資料做唯一性校驗，這樣做的消耗相當高，

高效：

SELECT COL1, COL2, COL3 

FROM TABLE 

WHERE COL1 = 10 

UNION ALL 

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

低效：

SELECT COL1, COL2, COL3 

FROM TABLE WHERE COL1 = 10 

UNION 

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

 

14、拆分復雜SQL為多個小SQL，避免大事務

• 簡單的SQL容易使用到MySQL的QUERY CACHE； 

• 減少鎖表時間特別是使用MyISAM存盤引擎的表； 

• 可以使用多核CPU，

 

15、使用truncate代替delete

當洗掉全表中記錄時，使用delete陳述句的操作會被記錄到undo塊中，洗掉記錄也記錄binlog，當確認需要洗掉全表時，會產生很大量的binlog并占用大量的undo資料塊，此時既沒有很好的效率也占用了大量的資源，使用truncate替代，不會記錄可恢復的資訊，資料不能被恢復，也因此使用truncate操作有其極少的資源占用與極快的時間，另外，使用truncate可以回收表的水位，

 

16、使用合理的分頁方式以提高分頁效率

使用合理的分頁方式以提高分頁效率 針對展現等分頁需求，合適的分頁方式能夠提高分頁的效率，

案例1： 

select * from t 

where thread_id = 10000 

and deleted = 0 

order by gmt_create asc limit 0, 15;

上述例子通過一次性根據過濾條件取出所有欄位進行排序回傳，資料訪問開銷=索引IO+索引全部記錄結果對應的表資料IO，因此，該種寫法越翻到后面執行效率越差，時間越長，尤其表資料量很大的時候，

適用場景：當中間結果集很小（10000行以下）或者查詢條件復雜（指涉及多個不同查詢欄位或者多表連接）時適用，

案例2： 

select t.* from ( 

select id from t 

where thread_id = 10000 and deleted = 0 order by gmt_create asc limit 0, 15) a, t 

where a.id = t.id; 

上述例子必須滿足t表主鍵是id列，且有覆寫索引secondary key:(thread_id, deleted, gmt_create)，通過先根據過濾條件利用覆寫索引取出主鍵id進行排序，再進行join操作取出其他欄位，資料訪問開銷=索引IO+索引分頁后結果（例子中是15行）對應的表資料IO，因此，該寫法每次翻頁消耗的資源和時間都基本相同，就像翻第一頁一樣，

適用場景：當查詢和排序欄位（即where子句和order by子句涉及的欄位）有對應覆寫索引時，且中間結果集很大的情況時適用，

 

17、避免不走索引的各種場景

在下面的SQL陳述句中的WHERE子句不使用索引： 

1）條件中有or，且or左右列并非全部由索引 Select col1 from table where key1=1 or no_key=2

2）like查詢以%開頭

3）where條件僅包含復合索引非前置列

Select col1 from table where key_part2=1 and key_part3=2

索引包含key_part1，key_part2，key_part3三列，但SQL陳述句沒有包含索引前置列，

4）隱式型別轉換造成不使用索引 

Select col1 from table where key_varchar=123; 

上述陳述句由于索引對列型別為varchar，但給定的值為數值，涉及隱式型別轉換，造成不能正確走索引， 

5）避免對索引欄位進行計算 

避免對索引欄位進行任何計算操作，對索引欄位的計劃操作會讓索引的作用失效，令資料庫選擇其他的較為低效率的訪問路徑，

6）避免對索引欄位進行是否NULL值判斷 

避免使用索引列值是否可為空的索引，如果索引列值可以是空值，在SQL陳述句中那些要回傳NULL值的操作，將不會用到索引，

7）避免對索引欄位不等于符號 

使用索引列作為條件進行查詢時，需要避免使用<>或者!=等判斷條件，如確實業務需要，使用到不等于符號，需要在重新評估索引建立，避免在此欄位上建立索引，改由查詢條件中其他索引欄位代替，

 

18、避免重復查詢更新的資料

針對業務中經常出現的更新行同時又希望獲得改行資訊的需求，MySQL并不支持PostgreSQL那樣的UPDATE RETURNING語法，在MySQL中可以通過變數實作，

例如，更新一行記錄的時間戳，同時希望查詢當前記錄中存放的時間戳是什么，簡單方法實作：

Update t1 set time=now() where col1=1; 

Select time from t1 where id =1; 

使用變數，可以重寫為以下方式： 

Update t1 set time=now () where col1=1 and @now: = now (); 

Select @now; 

前后二者都需要兩次網路來回，但使用變數避免了再次訪問資料表，特別是當t1表資料量較大時，后者比前者快很多，

 

19、避免出現不確定結果的函式

特定針對主從復制這類業務場景，由于原理上從庫復制的是主庫執行的陳述句，使用如now()、rand()、sysdate()、current_user()等不確定結果的函式很容易導致主庫與從庫相應的資料不一致，另外不確定值的函式,產生的SQL陳述句無法利用QUERY CACHE，

 

使用EXPLAIN分析SQL性能

1、執行計劃

執行計劃是一條查詢陳述句在資料庫中的執行程序或訪問路徑的描述，

2、怎樣查看MySQL執行計劃

在需要查看執行計劃的SQL前面添加explain并執行，即可獲取，

?

 

3、讀EXPLAIN中的資訊

1）table

顯示這一行的資料是關于哪張表的，

2）type

這是重要的列，顯示連接使用了何種型別，

從最好到最差的連接型別為const、eq_reg、ref、range、index和ALL，

3）possible_keys

顯示可能應用在這張表中的索引，如果為空，沒有可能的索引，

4）key

實際使用的索引，如果為NULL，則沒有使用索引，很少的情況下，MYSQL會選擇優化不足的索引，

5）key_len

使用的索引的長度，在不損失精確性的情況下，長度越短越好，

6）ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數，

7）rows

MYSQL認為必須檢查的用來回傳請求資料的行數，

8）Extra 

關于MYSQL如何決議查詢的額外資訊，效率最低的是Using temporary和Using filesort，意味著MYSQL根本不能使用索引，所以檢索會很慢，

 

2.3.1  索引分類

 

 

2.3.1.1  按存盤結構來分

• 哈希索引
• Btree索引（B+tree或B-tree) 
• full-index全文索引
• 自行研究?

2.3.1.2 按應用層次上來劃分

• 主鍵索引，索引列的值必須唯一，沒有空值，比如資料庫表中id自增列
• 唯一索引，索引列的值必須唯一，但允許有空值
• 普通索引，即一個索引只包含單個列，一個表可以有多個單列索引
• 復合索引，索引包含多個列

2.3.1.3  按表記錄的排列順序和索引的排列順序是否一致來劃分

• 聚集索引：表記錄的排列順序和索引的排列順序一致
• 非聚集索引：表記錄的排列順序和索引的排列順序不一致

1）簡單概括

• 聚集索引（clustered index）：就是以主鍵創建的索引， 
• 非聚集索引（secondary indexes）：就是以非主鍵創建的索引（也叫做二級索引），

2）詳細概括

• 聚集索引

聚集索引表記錄的排列順序和索引的排列順序一致，所以查詢效率快，因為只要找到第一個索引值記錄，其余的連續性的記錄在物理表中也會連續存放，一起就可以查詢到，缺點：新增比較慢，因為為了保證表中記錄的物理順序和索引順序一致，在記錄插入的時候，會對資料頁重新排序，

• 非聚集索引

索引的邏輯順序與磁盤上行的物理存盤順序不同，非聚集索引在葉子節點存盤的是主鍵和索引列，當我們使用非聚集索引查詢資料時，需要拿到葉子上的主鍵再去表中查到想要查找的資料，這個程序就是我們所說的回表，

3）聚集索引和非聚集索引的區別

• 聚集索引在葉子節點存盤的是表中的資料， 
• 非聚集索引在葉子節點存盤的是索引列和主鍵，

 

2.3.2  具體案例

我們是java碼農，可能關心最多的sql方面的知識，說了那么多理論，要有實際的案例襯托，假定創建了一張用戶表，如下：

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶id',
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `age` int(3) NOT NULL,
  `birthday` date NOT NULL,
  `gender` int(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_birthday` (`first_name`,`last_name`,`birthday`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用戶表';

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO user VALUES ('1', 'dong', 'guangming', '31', '2020-06-26', '1');
INSERT INTO user VALUES ('2', '董', '廣明', '21', '2020-06-25', '1');
INSERT INTO user VALUES ('3', '孫', '權', '88', '2019-06-26', '1');
INSERT INTO user VALUES ('4', '鐘', '南山', '84', '1950-06-26', '1');
INSERT INTO user VALUES ('5', 'dong', 'guangming', '21', '2020-06-23', '1');
INSERT INTO user VALUES ('6', 'dong', 'gm', '19', '2020-06-15', '1');
INSERT INTO user VALUES ('7', 'cao', 'cao', '99', '2020-05-03', '1');
INSERT INTO user VALUES ('8', 'sun', 'quan', '88', '2020-06-08', '1');

 然后查看表的索引

show index from `user`;

?

 

怎么確定你的sql陳述句有沒有使用索引呢，如果走索引的話，走哪個索引呢？？？就一一舉例：

2.3.2.1  主鍵索引

看上文，建user表時把欄位id設為了主鍵

explain select * from `user` where id=1

?

但有例外情況：

?

?

2.3.2.2  全值匹配

和索引中所有的列進行匹配配對，

explain select * from `user` where first_name ='dong' and last_name='guangming' and birthday='2020-06-25';

?

 

2.3.2.3 最左匹配原則

比如上面sql里建的復合索引

  KEY `name_birthday` (`first_name`,`last_name`,`birthday`) USING BTREE

你心里可以認為是三個子索引：（first_name） 、（first_name，last_name）、(`first_name`,`last_name`,`birthday`)

聯想到B+樹的原理就會想到必須要匹配最左原則，就是要有first_name開頭的列

?

切記： 遇到范圍查詢（>、<、between、like）就會停止匹配，比如：first_name = 'dong' and last_name>'guangming' and birthday='2020-06-23' ，birthday是用不到索引的，因為last_name欄位是一個范圍查詢，它之后的欄位會停止匹配，

 

 2.3.3.4   匹配列前綴

匹配某一列的值的開頭部分，例如查找所有以dong開頭的姓的人，

?

只有一個查詢用到了索引

2.3.3.4   匹配范圍值

查找姓在dong和sun之間的人，

 ?

2.3.3.5  精確匹配某一列并范圍匹配另外一列

查找姓為dong，出生日期小于某引數日期的用戶，

?

 2.3.3.6  只訪問索引的查詢，即覆寫索引

查詢只需要訪問索引，無需訪問資料行，

?

2.3.3.7  禁止在索引列上使用不等號

?

2.3.3.8  字串不加單引號索引失效

看資料庫版本（我用的是 5.5.56-MariaDB MariaDB Server），有的出現的情況不一樣

?

 2.3.3.9  索引列禁止計算等其他操作

索引列一旦參與計算、函式、（手動或自動）型別轉換等操作，會導致索引失效而進行全表掃描，

?

2.3.3.10  or連接導致索引失效

?

2.3.3.11  order by情況

正常情況：索引參與了排序，沒有違反最左匹配原則，
 非正常情況：非索引類參與排序，違反最左前綴法則，導致額外的檔案排序（會降低性能），

?

2.3.3.12  group by情況

正常情況：索引參與了分組排序，沒有違反最左匹配原則，
 非正常情況：非索引類參與分組排序，違反最左前綴法則，導致產生臨時表（會降低性能），

?

還有很多，就不再一一舉例了，

特別 注意資料庫版本的不同，可能會導致不同的輸出結果，我的資料庫是

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

不過索引的90%的案例功能還是一樣的！！！

 

 務必學好樹，特別是B+Tree，就會明白索引何時生效或失效，

 

參考軟文：

1.  The InnoDB Storage Engine  https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html

2. MySQL 高性能索引（哈希索引） https://xjwblog.com/?p=493

3. Binary Search Tree https://www.geeksforgeeks.org/binary-search-tree-data-structure/

4. What is a binary search tree http://staff.ustc.edu.cn/~csli/graduate/algorithms/book6/chap13.htm

5. The theory of BTrees http://www.virtualmachinery.com/btreeguide.htm

6. B+tree indexes?  http://www.unofficialmysqlguide.com/btrees.html

7. Difference between binary tree and binary search tree  https://stackoverflow.com/questions/6380231/difference-between-binary-tree-and-binary-search-tree

8. Difference between B Tree and B+ Tree  http://www.differencebetween.info/difference-between-b-tree-and-b-plus-tree

9. B Trees and B+ Trees. How they are useful in Databases  

   https://www.youtube.com/watch?v=aZjYr87r1b8

10. 從B樹、B+樹、B*樹談到R 樹  https://blog.csdn.net/v_JULY_v/article/details/6530142/

11. 為什么 MySQL 使用 B+ 樹  https://www.sohu.com/a/401856385_120437685

12. MySql性能調優(1)理解底層B+tree機制 https://www.dazhuanlan.com/2019/11/30/5de1598317cc3/

13.  MySQL索引的原理，B+樹、聚集索引和二級索引的結構分析 https://msd.misuland.com/pd/13769009898068930

14. MySQL索引機制 https://lilyssh.cn/mysql/2-mysql-B+Tree/

15. 深入理解Mysql的B+Tree索引原理 https://m.php.cn/article/448232.html

16. mysql等資料庫索引為什么偏愛b+tree https://zacard.net/2018/03/30/mysql-b-tree/

17. Database Index: usage of B+ tree in the practical database system https://www.callibrity.com/blog/database-index-usage-of-b-tree-in-the-practical-database-system

18. All About Indexes Part 2: MySQL Index Structure and Performance  https://www.vertabelo.com/blog/all-about-indexes-part-2-mysql-index-structure-and-performance/

19.  btree-index-structures-in-innodb  https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/ 
20.  深入理解MySQL索引 https://mp.weixin.qq.com/s/sT-Jz67p8Gadvcft-iO-9g, https://www.infoq.cn/article/OJKWYykjoyc2YGB0Sj2c

21. 由 B-/B+樹看 MySQL索引結構 https://segmentfault.com/a/1190000004690721

22. 為什么MongoDB使用B-Tree，MySQL使用B+Tree ?https://database.51cto.com/art/202004/615377.htm

23. 我以為自己足夠了解MySQL索引，直到遇見阿里面試官……https://dbaplus.cn/news-11-2659-1.html

24. 為什么 MySQL 使用 B+ 樹 https://draveness.me/whys-the-design-mysql-b-plus-tree/

25. mysql B+tree https://www.bbsmax.com/A/MAzArp7nJ9/

26. MySQL: B+ Tree Indexing Algorithm for InnoDB Storage Engine https://codesolu.com/2019/12/15/mysql-b-tree-indexing-algorithm-for-innodb-storage-engine/

27.  15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips
     https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/

28.  Useful Tips to Troubleshoot Common Errors in MySQL  https://www.tecmint.com/troubleshoot-common-errors-in-mysql/
29. https://use-the-index-luke.com/

 

參考書籍：

1. 演算法：C語言實作(第1-4部分)基礎知識、資料結構、排序及搜索（原書第3版）?
2. 資料庫系統全書第11-16章?

        3.  Inside.MySQL_InnoDB.Storage.Engine_zh-CN 第五章

        4.  [SQL編程風格]  https://github.com/dongguangming/java/tree/master/databases

        5.   程式員的SQL金典 https://github.com/dongguangming/java/tree/master/databases

        6.  海量資料庫解決方案 

 

附圖三張：英語的重要性，不言而喻

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

標籤：MySQL

上一篇：MySQL 事務 例外 事務隔離的級別

下一篇：Mariadb之日志相關配置