淺談mysql資料庫索引應用（mysql資料庫的索引有哪些）

本文目錄一覽：

• 1、mysql索引
• 2、如何理解並正確使用MySql索引
• 3、如何高效地利用MySQL索引
• 4、深入淺析Mysql聯合索引最左匹配原則
• 5、MySQL資料庫優化（七）：MySQL如何使用索引
• 6、MySQL索引

mysql索引

二叉搜索樹、N叉樹

頁分裂：B+樹的插入可能會引起數據頁的分裂，刪除可能會引起數據頁的合併，二者都是比較重的IO消耗，所以比較好的方式是順序插入數據，這也是我們一般使用自增主鍵的原因之一。

頁分裂逆過程：頁合併，當刪除數據後，相鄰的兩個數據頁利用率很低的時候會做數據頁合併

主鍵索引：key：主鍵，value：數據頁，存儲每行數據

非主鍵索引：key：非主鍵索引，value：主鍵key，導致回表

最左匹配：優先將區分度高的列放到前面，這樣可以高效索引，

最左匹配原則遇到範圍查詢就停止匹配，範圍查詢(、、between、like)為什麼？因為出現範圍匹配後，後面的索引欄位無法保證有序，局部有序失去，順序失去則無法提高查詢效率

SELECT * FROM table WHERE a IN (1,2,3) and b 1;

如何建立索引？

還是對(a，b)建立索引，因為IN在這裡可以視為等值引用，不會中止索引匹配，所以還是(a,b)!

索引組織表

索引用頁存儲：key【10】-point【6】，通過調整key大小，當頁大小固定的情況下，通過調整key大小，使得N叉樹變化；

如key 10, point 6則單個索引16位元組，頁大小為16k，則頁面總共可以存儲1024個索引，即N大小

覆蓋索引: 二級索引的信息已經存在想要的列，例如主鍵

如果現在有一個高頻請求，要根據市民的身份證號查詢他的姓名，這個聯合索引就有意義了。它可以在這個高頻請求上用到覆蓋索引，不再需要回表查整行記錄，減少語句的執行時間。

索引下推優化：可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

整理索引碎片，重建表：alter table T engine=InnoDB

  首先是看key的大小，另外是數據頁的大小，如果需要改變N，則需要從這兩個方面做改動；

一個innoDB引擎的表，數據量非常大，根據二級索引搜索會比主鍵搜索快，文章闡述的原因是主鍵索引和數據行在一起，非常大搜索慢，我的疑惑是：通過普通索引找到主鍵ID後，同樣要跑一邊主鍵索引，對於使用覆蓋索引的情況下，使用覆蓋索引可以直接解決問題

如何理解並正確使用MySql索引

MySQL索引類型包括：

（1）普通索引

這是最基本的索引，它沒有任何限制。它有以下幾種創建方式：

◆創建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR類型，length可以小於欄位實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

◆修改表結構

ALTER mytable ADD INDEX [indexName] ON (username(length))

◆創建表的時候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 刪除索引的語法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引

與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式：

◆創建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表結構

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

如何高效地利用MySQL索引

1、要想高效利用索引，我們首先要考慮如何正確建立索引。

（1）在經常做搜索的列上，也就是WHERE子句里經常出現的列，考慮加上索引，加快搜索速度。

（2）唯一標識記錄的列，應該加上唯一索引，強制該列的唯一性並且加快按該列查找記錄的速度。

（3）在內連接使用的列上加上索引，最好是在內連接用到欄位都加上，因為MySQL優化器會自動地選擇連接順序，然後觀察索引的使用情況，將沒用的索引刪除即可。

（4）在需要排序的列上加上索引，因為索引本身是按順序的組織的，它可以避免 filesort，要知道，Server層在進行排序時是在內存中進行的，非常消耗資源。

（5）可以考慮實現覆蓋索引，即根據 SELECT 的所有欄位上創建聯合索引，這樣存儲引擎只用讀取索引而不用去回表查詢，極大地減少了對數據表的訪問，大大地提高了性能。

（6）對於那些選擇性很小的列，比如性別列，增加索引並不能明顯加快查詢速度，反而該索引會成為表的累贅。

（7）對於那些定義為text, image和bit數據類型的列不應該增加索引。這是因為，這些列的要麼數據量相當大，要麼取值很少。

（8）當對寫性能的要求遠遠大於讀性能時，不應該創建索引。寫性能和讀性能是互相矛盾的。這是因為，維護一個 B+Tree 成本是非常大的，對索引的寫會涉及到頁的分裂等。

（9）複合索引的幾個欄位是否經常同時以AND方式出現在Where子句中？單欄位查詢是否極少甚至沒有？如果是，則可以建立複合索引，否則考慮單欄位索引。這還是說明，滿足查詢性能的前提下，索引越少越好。

（10）如果複合索引所包含的欄位超過3個，那麼仔細考慮其必要性，考慮減少複合的欄位。

（11）在用於GROUP BY的列上加上索引，避免使用臨時表。

（12）對於較長的字元列，如 char、varchar等，由於字元串的比較相對來說非常耗時，因此考慮使用前綴索引減少索引長度，或者創建自定義哈希索引，將字元串映射成整數，然後以該整數作為索引，同時以字元串的值作為過濾條件。

我們在創建索引時，可以根據下面原則進行簡單判斷：索引是否將相關記錄集合到了一起，從未減少了磁碟I/O，加快搜索速度？索引中數據的排列順序是否和查找的數據的排列順序一致，從而避免了Server層的排序？索引中的列是否包含了查詢中需要的全部列從而實現了覆蓋索引？ 這幾個條件層層遞進，滿足得越多越好。

2、索引正確地建立了，我們還需要正確地使用它們：

（1）使用了運算符 !=，以及關鍵字not in，not exist，，等，總之產生的結果集很大時（也在where條件進行大範圍的選擇時），往往導致引擎不使用索引而是走全盤掃描。因為如果使用索引會造成大量的隨機I/O，得不償失。

（2）如果對索引列進行運算，如 WHERE substr(name, 1, 3)=『mark』，存儲引擎並不能聰明地判斷哪些索引滿足等式，因此不能使用到索引。

（3）使用到了LIKE，並且通配符在最前面時，不能使用索引。

（4）對於聯合索引 (a, b, c)，如果沒用到最左列，那麼一般情況下都使用不到索引。但是，比如統計操作 count(*) where a xxx，是可以使用到該聯合索引的。畢竟統計這類操作，它不是檢索，並不需要索引完全有序。

（5）對於聯合索引，如果某個列使用了範圍查找，那麼其右邊的列都無法作為索引優化查詢，但是由於 ICP（Index Condition Pushdown），這些列能作為過濾條件在存儲引擎中對數據進行過濾。

（6）如果條件中有 OR，則必須每個OR用到的欄位都有索引，否則不能使用任何索引。

（7）想在聯合查詢中使用索引來避免 filesort，則關聯查詢中的ORDER BY用到的欄位必須全部是第一張表（驅動表）上的。

深入淺析Mysql聯合索引最左匹配原則

之前在網上看到過很多關於mysql聯合索引最左前綴匹配的文章，自以為就了解了其原理，最近面試時和面試官交流，發現遺漏了些東西，這裡自己整理一下這方面的內容。

最左前綴匹配原則

在mysql建立聯合索引時會遵循最左前綴匹配的原則，即最左優先，在檢索數據時從聯合索引的最左邊開始匹配，示例：

對列col1、列col2和列col3建一個聯合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

聯合索引 test_col1_col2_col3 實際建立了 (col1)、(col1,col2)、(col,col2,col3) 三個索引。

SELECT * FROM test WHERE col1=「1」 AND clo2=「2」 AND clo4=「4」

上面這個查詢語句執行時會依照最左前綴匹配原則，檢索時會使用索引(col1,col2)進行數據匹配。

注意

索引的欄位可以是任意順序的，如：

SELECT * FROM test WHERE col1=「1」 AND clo2=「2」

SELECT * FROM test WHERE col2=「2」 AND clo1=「1」

這兩個查詢語句都會用到索引(col1,col2)，mysql創建聯合索引的規則是首先會對聯合合索引的最左邊的，也就是第一個欄位col1的數據進行排序，在第一個欄位的排序基礎上，然後再對後面第二個欄位col2進行排序。其實就相當於實現了類似 order by col1 col2這樣一種排序規則。

有人會疑惑第二個查詢語句不符合最左前綴匹配：首先可以肯定是兩個查詢語句都保函索引(col1,col2)中的col1、col2兩個欄位，只是順序不一樣，查詢條件一樣，最後所查詢的結果肯定是一樣的。既然結果是一樣的，到底以何種順序的查詢方式最好呢？此時我們可以藉助mysql查詢優化器explain，explain會糾正sql語句該以什麼樣的順序執行效率最高，最後才生成真正的執行計劃。

減少開銷 。建一個聯合索引(col1,col2,col3)，實際相當於建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁碟空間的開銷。對於大量數據的表，使用聯合索引會大大的減少開銷！

覆蓋索引 。對聯合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那麼MySQL可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機io操作。減少io操作，特別的隨機io其實是dba主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升性能的優化手段之一。

效率高 。索引列越多，通過索引篩選出的數據越少。有1000W條數據的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設假設每個條件可以篩選出10%的數據，如果只有單值索引，那麼通過該索引能篩選出1000W10%=100w條數據，然後再回表從100w條數據中找到符合col2=2 and col3= 3的數據，然後再排序，再分頁；如果是聯合索引，通過索引篩選出1000w10% 10% *10%=1w，效率提升可想而知！

引申

對於聯合索引(col1,col2,col3)，查詢語句 SELECT * FROM test WHERE col2=2; 是否能夠觸發索引？

大多數人都會說NO，實際上卻是YES。

原因：

EXPLAIN SELECT * FROM test WHERE col2=2;

EXPLAIN SELECT * FROM test WHERE col1=1;

觀察上述兩個explain結果中的type欄位。查詢中分別是：

index： 這種類型表示mysql會對整個該索引進行掃描。要想用到這種類型的索引，對這個索引並無特別要求，只要是索引，或者某個聯合索引的一部分，mysql都可能會採用index類型的方式掃描。但是呢，缺點是效率不高，mysql會從索引中的第一個數據一個個的查找到最後一個數據，直到找到符合判斷條件的某個索引。所以，上述語句會觸發索引。

ref： 這種類型表示mysql會根據特定的演算法快速查找到某個符合條件的索引，而不是會對索引中每一個數據都進行一一的掃描判斷，也就是所謂你平常理解的使用索引查詢會更快的取出數據。而要想實現這種查找，索引卻是有要求的，要實現這種能快速查找的演算法，索引就要滿足特定的數據結構。簡單說，也就是索引欄位的數據必須是有序的，才能實現這種類型的查找，才能利用到索引。

以上所述是我給大家介紹的Mysql聯合索引最左匹配原則，希望對大家有所幫助，如果大家有任何疑問請給我留言，我會及時回復大家的。

《 兩個月拿到N個offer，看看我是如何做到的 》

《 面試總結：2019年最全面試題資料學習大全—（含答案） 》

《 淘寶面試回來，想對程序員們談談 》

《 看過太多大廠面試題，其實考的無非是這 3 點能力 》

MySQL資料庫優化（七）：MySQL如何使用索引

索引用於快速找到特定一些值的記錄。如果沒有索引，MySQL就必須從第一行記錄開始讀取整個表來檢索記錄。表越大，資源消耗越大。如果在欄位上有索引的話，MySQL就能很快決定該從數據文件的哪個位置開始搜索記錄，而無須查找所有的數據。如果表中有1000條記錄的話，那麼這至少比順序地讀取數據快100倍。注意，如果需要存取幾乎全部1000條記錄的話，那麼順序讀取就更快了，因為這樣會使磁碟搜索最少。

大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B樹方式存儲。只有空間類型的欄位使用R樹存儲，MEMORY (HEAP)表支持哈希索引。

字元串默認都是自動壓縮前綴和後綴中的空格。

通常，如下所述幾種情況下可以使用索引。哈希索引(用於 MEMORY 表)的獨特之處在後面會討論到。

想要儘快找到匹配 WHERE 子句的記錄。

根據條件排除記錄。如果有多個索引可共選擇的話，MySQL通常選擇能找到最少記錄的那個索引。

做表連接查詢時從其他表中檢索記錄。

想要在指定的索引欄位 key_col 上找到它的 MIN() 或 MAX() 值。優化程序會在檢查索引的

key_col 欄位前就先檢查其他索引部分是否使用了 WHERE key_part_# = constant 子句。這樣的話，

MySQL會為 MIN() 或 MAX() 表達式分別單獨做一次索引查找，並且將它替換成常數。當所有的表達式都被替換成常數後，查詢就立刻返回。如下：

SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

對錶作排序或分組，當在一個可用的最左前綴索引上做分組或排序時(如 ORDER

BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。

有些時候，查詢可以優化使得無需計算數據就能直接取得結果。當查詢使用表中的一個數字型欄位，且這個欄位是索引的最左部分，則可能從索引樹中能很快就取得結果：

SELECTkey_part3FROMtbl_nameWHEREkey_part1=1

假設有如下 SELECT 語句：

如果在 col1 和 col2 上有一個多欄位索引的話，就能直接取得對應的記錄了。

MySQL索引

MySQL的Innodb存儲引擎的索引分為聚集索引和非聚集索引兩大類

特點：B+樹葉子節點存儲行數據

一個表中，必須有一個聚集索引，只能有一個聚集索引，Innodb通常把一個表的主鍵索引作為聚集索引，如果沒有主鍵InnoDB會選擇一個唯一索引代替。如果沒有這樣的索引，InnoDB會隱式的定義一個主鍵來作為聚集索引，這個欄位為6個位元組，類型為長整形。

利用主鍵索引查找行數據是最快的，建議使用自增主鍵原因是利於索引樹的構建（主鍵自增寫入時新插入的數據不會影響到原有頁，插入效率高；但是如果主鍵是無序的或者隨機的，那每次的插入可能會導致原有頁頻繁的分裂，影響插入效率）

特點：B+樹葉子節點存儲主鍵ID

一個表中可以有多個非聚集索引，每個非聚集索引即是一棵B+樹

通過非聚集索引查找數據時，需要先在非聚集索引上找到主鍵ID，再從聚集索引獲取行數據，這個過程就稱之為回表

B樹索引中的B樹實際上是B+樹，至於為什麼使用B+樹而不使用B樹或者紅黑樹的原因在另外的文章中有提及。

特點：

特點：類似JDK中的HashMap，但無法支持範圍查詢

特點：使用的演算法仍然是B樹索引，不同的就是索引列的值必須唯一

對於普通索引來說，查找到滿足條件的第一個記錄後，需要查找下一個記錄，直到碰到第一個不滿足條件的記錄。

對於唯一索引來說，由於索引定義了唯一性，查找到第一個滿足條件的記錄後，就會停止繼續檢索，提升索引性能

另外插入行時會構建該唯一索引，假如索引值重複將插入失敗，適合業務上做唯一性檢驗

通過建立倒排索引，可以極大的提升檢索效率,解決判斷欄位是否包含的問題，但是業務上一般都不採用這種索引，而是使用ES處理全文搜索需求

僅對某個特定欄位建立的索引，如(biz_id)

對多個欄位建立的索引，如(biz_id,type)