技術文章 » 完全掌握mysql的索引技巧（總結分享）

完全掌握mysql的索引技巧（總結分享）

2022-01-04 19:01:15
本篇文章給大家帶來了關於mysql索引的相關知識，其中包括mysql的邏輯架構和sql執行語句，希望對大家有幫助。

一、MySQL三層邏輯架構

MySQL的儲存引擎架構將查詢處理與資料的儲存/提取相分離。下面是MySQL的邏輯架構圖：

1、第一層負責連線管理、授權認證、安全等等。

每個使用者端的連線都對應著伺服器上的一個執行緒。伺服器上維護了一個執行緒池，避免為每個連線都建立銷燬一個執行緒。當用戶端連線到MySQL伺服器時，伺服器對其進行認證。可以通過使用者名稱和密碼的方式進行認證，也可以通過SSL證書進行認證。登入認證通過後，伺服器還會驗證該使用者端是否有執行某個查詢的許可權。

2、第二層負責解析查詢

編譯SQL，並對其進行優化(如調整表的讀取順序，選擇合適的索引等)。對於SELECT語句，在解析查詢前，伺服器會先檢查查詢快取，如果能在其中找到對應的查詢結果，則無需再進行查詢解析、優化等過程，直接返回查詢結果。儲存過程、觸發器、檢視等都在這一層實現。

3、第三層是儲存引擎

儲存引擎負責在MySQL中儲存資料、提取資料、開啟一個事務等等。儲存引擎通過API與上層進行通訊，這些API遮蔽了不同儲存引擎之間的差異，使得這些差異對上層查詢過程透明。儲存引擎不會去解析SQL。

二、對比InnoDB與MyISAM

1、 儲存結構

MyISAM：每個MyISAM在磁碟上儲存成三個檔案。分別為：表定義檔案、資料檔案、索引檔案。第一個檔案的名字以表的名字開始，擴充套件名指出檔案型別。.frm檔案儲存表定義。資料檔案的擴充套件名為.MYD (MYData)。索引檔案的擴充套件名是.MYI (MYIndex)。

InnoDB：所有的表都儲存在同一個資料檔案中（也可能是多個檔案，或者是獨立的表空間檔案），InnoDB表的大小隻受限於作業系統檔案的大小，一般為2GB。

2、 儲存空間

MyISAM： MyISAM支援支援三種不同的儲存格式：靜態表(預設，但是注意資料末尾不能有空格，會被去掉)、動態表、壓縮表。當表在建立之後並匯入資料之後，不會再進行修改操作，可以使用壓縮表，極大的減少磁碟的空間佔用。

InnoDB： 需要更多的記憶體和儲存，它會在主記憶體中建立其專用的緩衝池用於高速緩衝資料和索引。

3、 可移植性、備份及恢復

MyISAM：資料是以檔案的形式儲存，所以在跨平臺的資料轉移中會很方便。在備份和恢復時可單獨針對某個表進行操作。

InnoDB：免費的方案可以是拷貝資料檔案、備份 binlog，或者用 mysqldump，在資料量達到幾十G的時候就相對痛苦了。

4、 事務支援

MyISAM：強調的是效能，每次查詢具有原子性,其執行數度比InnoDB型別更快，但是不提供事務支援。

InnoDB：提供事務支援事務，外部鍵等高階資料庫功能。 具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

5、 AUTO_INCREMENT

MyISAM：可以和其他欄位一起建立聯合索引。引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據前面幾列進行排序後遞增。

InnoDB：InnoDB中必須包含只有該欄位的索引。引擎的自動增長列必須是索引，如果是組合索引也必須是組合索引的第一列。

6、 表鎖差異

MyISAM： 只支援表級鎖，使用者在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以後的表滿足insert並行的情況下，可以在表的尾部插入新的資料。

InnoDB： 支援事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多使用者並行操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。

7、 全文索引

MyISAM：支援 FULLTEXT型別的全文索引

InnoDB：不支援FULLTEXT型別的全文索引，但是innodb可以使用sphinx外掛支援全文索引，並且效果更好。

8、表主鍵

MyISAM：允許沒有任何索引和主鍵的表存在，索引都是儲存行的地址。

InnoDB：如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6位元組的主鍵(使用者不可見)，資料是主索引的一部分，附加索引儲存的是主索引的值。

9、表的具體行數

MyISAM： 儲存有表的總行數，如果select count() from table;會直接取出出該值。

InnoDB： 沒有儲存表的總行數，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了wehre條件後，myisam和innodb處理的方式都一樣。

10、CRUD操作

MyISAM：如果執行大量的SELECT，MyISAM是更好的選擇。

InnoDB：如果你的資料執行大量的INSERT或UPDATE，出於效能方面的考慮，應該使用InnoDB表。

11、 外來鍵

MyISAM：不支援

InnoDB：支援

三、sql優化簡介

1、什麼情況下進行sql優化

效能低、執行時間太長、等待時間太長、連線查詢、索引失效。

2、sql語句執行過程

（1）編寫過程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

（2）解析過程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

3、sql優化就是優化索引

索引相當於書的目錄。

索引的資料結構是B+樹。

四、索引

1、索引的優勢

（1）提高查詢效率（降低IO使用率）

（2）降低CPU使用率

比如查詢order by age desc，因為B+索引樹本身就是排好序的，所以再查詢如果觸發索引，就不用再重新查詢了。

2、索引的弊端

（1）索引本身很大，可以存放在記憶體或硬碟上，通常儲存在硬碟上。

（2）索引不是所有情況都使用，比如①少量資料②頻繁變化的欄位③很少使用的欄位

（3）索引會降低增刪改的效率

3、索引的分類

（1）單值索引

（2）唯一索引

（3）聯合索引

（4）主鍵索引

備註：唯一索引和主鍵索引唯一的區別：主鍵索引不能為null

4、建立索引

alter table user add INDEX `user_index_username_password` (`username`,`password`)

5、MySQL索引原理 -> B+樹

MySQL索引的底層資料結構是B+樹

B+Tree是在B-Tree基礎上的一種優化，使其更適合實現外儲存索引結構，InnoDB儲存引擎就是用B+Tree實現其索引結構。

B-Tree結構圖中每個節點中不僅包含資料的key值，還有data值。而每一個頁的儲存空間是有限的，如果data資料較大時將會導致每個節點（即一個頁）能儲存的key的數量很小，當儲存的資料量很大時同樣會導致B-Tree的深度較大，增大查詢時的磁碟I/O次數，進而影響查詢效率。在B+Tree中，所有資料記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只儲存key值資訊，這樣可以大大加大每個節點儲存的key值數量，降低B+Tree的高度。

B+Tree相對於B-Tree有幾點不同：

非葉子節點只儲存鍵值資訊。
所有葉子節點之間都有一個鏈指標。
資料記錄都存放在葉子節點中。
將上一節中的B-Tree優化，由於B+Tree的非葉子節點只儲存鍵值資訊，假設每個磁碟塊能儲存4個鍵值及指標資訊，則變成B+Tree後其結構如下圖所示：

通常在B+Tree上有兩個頭指標，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即資料節點）之間是一種鏈式環結構。因此可以對B+Tree進行兩種查詢運算：一種是對於主鍵的範圍查詢和分頁查詢，另一種是從根節點開始，進行隨機查詢。

可能上面例子中只有22條資料記錄，看不出B+Tree的優點，下面做一個推算：

InnoDB儲存引擎中頁的大小為16KB，一般表的主鍵型別為INT（佔用4個位元組）或BIGINT（佔用8個位元組），指標型別也一般為4或8個位元組，也就是說一個頁（B+Tree中的一個節點）中大概儲存16KB/(8B+8B)=1K個鍵值（因為是估值，為方便計算，這裡的K取值為〖10〗^3）。也就是說一個深度為3的B+Tree索引可以維護10^3 * 10^3 * 10^3 = 10億 條記錄。

實際情況中每個節點可能不能填充滿，因此在資料庫中，B+Tree的高度一般都在2~4層。MySQL的InnoDB儲存引擎在設計時是將根節點常駐記憶體的，也就是說查詢某一鍵值的行記錄時最多隻需要1~3次磁碟I/O操作。

資料庫中的B+Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。上面的B+Tree範例圖在資料庫中的實現即為聚集索引，聚集索引的B+Tree中的葉子節點存放的是整張表的行記錄資料。輔助索引與聚集索引的區別在於輔助索引的葉子節點並不包含行記錄的全部資料，而是儲存相應行資料的聚集索引鍵，即主鍵。當通過輔助索引來查詢資料時，InnoDB儲存引擎會遍歷輔助索引找到主鍵，然後再通過主鍵在聚集索引中找到完整的行記錄資料。

五、如何觸發聯合索引

1、對user表建立聯合索引username、password

2、觸發聯合索引

（1）使用聯合索引的全部索引鍵可觸發聯合索引

（2）使用聯合索引的全部索引鍵，但是用or連線的，不可觸發聯合索引

（3）單獨使用聯合索引的左邊第一個欄位時，可觸發聯合索引

（4）單獨使用聯合索引的其它欄位時，不可觸發聯合索引

六、分析sql的執行計劃---explain

explain可以模擬sql優化執行sql語句。

1、explan使用簡介

（1）使用者表

（2）部門表

（3）未觸發索引

（4）觸發索引

（5）結果分析

explain中第一行出現的表是驅動表。

  1. 指定了聯接條件時，滿足查詢條件的記錄行數少的表為[驅動表]
  2. 未指定聯接條件時，行數少的表為[驅動表]

對驅動表直接進行排序就會觸發索引，對非驅動表進行排序不會觸發索引。

2、explain查詢結果簡介

（1）id：SELECT識別符。這是SELECT的查詢序列號。

（2）select_type：SELECT型別：

  • SIMPLE： 簡單SELECT(不使用UNION或子查詢)

  • PRIMARY： 最外面的SELECT

  • UNION：UNION中的第二個或後面的SELECT語句

  • DEPENDENT UNION：UNION中的第二個或後面的SELECT語句，取決於外面的查詢

  • UNION RESULT：UNION的結果

  • SUBQUERY：子查詢中的第一個SELECT

  • DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決於外面的查詢

  • DERIVED：匯出表的SELECT(FROM子句的子查詢)

（3）table：表名

（4）type：聯接型別

  • system：表僅有一行(=系統表)。這是const聯接型別的一個特例。

  • const：表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩餘部分認為是常數。const用於用常數值比較PRIMARY KEY或UNIQUE索引的所有部分時。

  • eq_ref：對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接型別，除了const型別。它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY。eq_ref可以用於使用= 操作符比較的帶索引的列。比較值可以為常數或一個使用在該表前面所讀取的表的列的表示式。

  • ref：對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的字首，或如果鍵不是UNIQUE或PRIMARY KEY(換句話說，如果聯接不能基於關鍵字選擇單個行的話)，則使用ref。如果使用的鍵僅僅匹配少量行，該聯接型別是不錯的。ref可以用於使用=或<=>操作符的帶索引的列。

  • ref_or_null：該聯接型別如同ref，但是新增了MySQL可以專門搜尋包含NULL值的行。在解決子查詢中經常使用該聯接型別的優化。

  • index_merge：該聯接型別表示使用了索引合併優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。

  • unique_subquery：該型別替換了下面形式的IN子查詢的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一個索引查詢函數，可以完全替換子查詢，效率更高。

  • index_subquery：該聯接型別類似於unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range：只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該型別中ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常數比較關鍵字列時，可以使用range

  • index：該聯接型別與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。

  • all：對於每個來自於先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，並且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基於前面的表中的常數值或列值被檢索出。

（5）possible_keys：possible_keys列指出MySQL能使用哪個索引在該表中找到行。注意，該列完全獨立於EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。

（6）key：key列顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

（7）key_len：key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。注意通過key_len值我們可以確定MySQL將實際使用一個多部關鍵字的幾個部分。

（8）ref：ref列顯示使用哪個列或常數與key一起從表中選擇行。

（9）rows：rows列顯示MySQL認為它執行查詢時必須檢查的行數。

（10）Extra：該列包含MySQL解決查詢的詳細資訊。

  • Distinct：MySQL發現第1個匹配行後，停止為當前的行組合搜尋更多的行。

  • Not exists：MySQL能夠對查詢進行LEFT JOIN優化，發現1個匹配LEFT JOIN標準的行後，不再為前面的的行組合在該表內檢查更多的行。

  • range checked for each record (index map: #)：MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合，MySQL檢查是否可以使用range或index_merge存取方法來索取行。

  • Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據聯接型別瀏覽所有行併為所有匹配WHERE子句的行儲存排序關鍵字和行的指標來完成排序。然後關鍵字被排序，並按排序順序檢索行。

  • Using index：從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。當查詢只使用作為單一索引一部分的列時，可以使用該策略。

  • Using temporary：為了解決查詢，MySQL需要建立一個臨時表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。

  • Using where：WHERE子句用於限制哪一個行匹配下一個表或傳送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where並且表聯接型別為ALL或index，查詢可能會有一些錯誤。

  • Using sort_union(...), Using union(...), Using intersect(...)：這些函數說明如何為index_merge聯接型別合併索引掃描。

  • Using index for group-by：類似於存取表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜尋硬碟存取實際的表。並且，按最有效的方式使用索引，以便對於每個組，唯讀取少量索引條目。

通過相乘EXPLAIN輸出的rows列的所有值，你能得到一個關於一個聯接如何的提示。這應該粗略地告訴你MySQL必須檢查多少行以執行查詢。當你使用max_join_size變數限制查詢時，也用這個乘積來確定執行哪個多表SELECT語句。

推薦學習：

以上就是完全掌握mysql的索引技巧（總結分享）的詳細內容，更多請關注TW511.COM其它相關文章！