本篇內(nèi)容主要講解“MySQL的索引技巧有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“mysql的索引技巧有哪些”吧!

創(chuàng)新互聯(lián)從2013年開始，先為昌寧等服務建站，昌寧等地企業(yè)，進行企業(yè)商務咨詢服務。為昌寧企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務解決您的所有建站問題。

一、MySQL三層邏輯架構

MySQL的存儲引擎架構將查詢處理與數(shù)據(jù)的存儲/提取相分離。下面是MySQL的邏輯架構圖：

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

每個客戶端的連接都對應著服務器上的一個線程。服務器上維護了一個線程池，避免為每個連接都創(chuàng)建銷毀一個線程。當客戶端連接到MySQL服務器時，服務器對其進行認證?？梢酝ㄟ^用戶名和密碼的方式進行認證，也可以通過SSL證書進行認證。登錄認證通過后，服務器還會驗證該客戶端是否有執(zhí)行某個查詢的權限。

2、第二層負責解析查詢

編譯SQL，并對其進行優(yōu)化(如調(diào)整表的讀取順序，選擇合適的索引等)。對于SELECT語句，在解析查詢前，服務器會先檢查查詢緩存，如果能在其中找到對應的查詢結果，則無需再進行查詢解析、優(yōu)化等過程，直接返回查詢結果。存儲過程、觸發(fā)器、視圖等都在這一層實現(xiàn)。

3、第三層是存儲引擎

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

二、對比InnoDB與MyISAM

1、 存儲結構

MyISAM：每個MyISAM在磁盤上存儲成三個文件。分別為：表定義文件、數(shù)據(jù)文件、索引文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。.frm文件存儲表定義。數(shù)據(jù)文件的擴展名為.MYD (MYData)。索引文件的擴展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一個數(shù)據(jù)文件中（也可能是多個文件，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統(tǒng)文件的大小，一般為2GB。

2、 存儲空間

MyISAM： MyISAM支持支持三種不同的存儲格式：靜態(tài)表(默認，但是注意數(shù)據(jù)末尾不能有空格，會被去掉)、動態(tài)表、壓縮表。當表在創(chuàng)建之后并導入數(shù)據(jù)之后，不會再進行修改操作，可以使用壓縮表，極大的減少磁盤的空間占用。

InnoDB： 需要更多的內(nèi)存和存儲，它會在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。

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

MyISAM：數(shù)據(jù)是以文件的形式存儲，所以在跨平臺的數(shù)據(jù)轉移中會很方便。在備份和恢復時可單獨針對某個表進行操作。

InnoDB：免費的方案可以是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，在數(shù)據(jù)量達到幾十G的時候就相對痛苦了。

4、 事務支持

MyISAM：強調(diào)的是性能，每次查詢具有原子性,其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務支持。

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

5、 AUTO_INCREMENT

MyISAM：可以和其他字段一起建立聯(lián)合索引。引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據(jù)前面幾列進行排序后遞增。

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

6、 表鎖差異

MyISAM： 只支持表級鎖，用戶在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以后的表滿足insert并發(fā)的情況下，可以在表的尾部插入新的數(shù)據(jù)。

InnoDB： 支持事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多用戶并發(fā)操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。

7、 全文索引

MyISAM：支持 FULLTEXT類型的全文索引

InnoDB：不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8、表主鍵

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

InnoDB：如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6字節(jié)的主鍵(用戶不可見)，數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。

9、表的具體行數(shù)

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

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

10、CRUD操作

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

InnoDB：如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表。

11、 外鍵

MyISAM：不支持

InnoDB：支持

三、sql優(yōu)化簡介

1、什么情況下進行sql優(yōu)化

性能低、執(zhí)行時間太長、等待時間太長、連接查詢、索引失效。

2、sql語句執(zhí)行過程

（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優(yōu)化就是優(yōu)化索引

索引相當于書的目錄。

索引的數(shù)據(jù)結構是B+樹。

四、索引

1、索引的優(yōu)勢

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

（2）降低CPU使用率

比如查詢order by age desc，因為B+索引樹本身就是排好序的，所以再查詢?nèi)绻|發(fā)索引，就不用再重新查詢了。

2、索引的弊端

（1）索引本身很大，可以存放在內(nèi)存或硬盤上，通常存儲在硬盤上。

（2）索引不是所有情況都使用，比如①少量數(shù)據(jù)②頻繁變化的字段③很少使用的字段

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

3、索引的分類

（1）單值索引

（2）唯一索引

（3）聯(lián)合索引

（4）主鍵索引

備注：唯一索引和主鍵索引唯一的區(qū)別：主鍵索引不能為null

4、創(chuàng)建索引

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

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

MySQL索引的底層數(shù)據(jù)結構是B+樹

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

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

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

非葉子節(jié)點只存儲鍵值信息。
所有葉子節(jié)點之間都有一個鏈指針。
數(shù)據(jù)記錄都存放在葉子節(jié)點中。
將上一節(jié)中的B-Tree優(yōu)化，由于B+Tree的非葉子節(jié)點只存儲鍵值信息，假設每個磁盤塊能存儲4個鍵值及指針信息，則變成B+Tree后其結構如下圖所示：

通常在B+Tree上有兩個頭指針，一個指向根節(jié)點，另一個指向關鍵字最小的葉子節(jié)點，而且所有葉子節(jié)點（即數(shù)據(jù)節(jié)點）之間是一種鏈式環(huán)結構。因此可以對B+Tree進行兩種查找運算：一種是對于主鍵的范圍查找和分頁查找，另一種是從根節(jié)點開始，進行隨機查找。

可能上面例子中只有22條數(shù)據(jù)記錄，看不出B+Tree的優(yōu)點，下面做一個推算：

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

實際情況中每個節(jié)點可能不能填充滿，因此在數(shù)據(jù)庫中，B+Tree的高度一般都在2~4層。MySQL的InnoDB存儲引擎在設計時是將根節(jié)點常駐內(nèi)存的，也就是說查找某一鍵值的行記錄時最多只需要1~3次磁盤I/O操作。

數(shù)據(jù)庫中的B+Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。上面的B+Tree示例圖在數(shù)據(jù)庫中的實現(xiàn)即為聚集索引，聚集索引的B+Tree中的葉子節(jié)點存放的是整張表的行記錄數(shù)據(jù)。輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點并不包含行記錄的全部數(shù)據(jù)，而是存儲相應行數(shù)據(jù)的聚集索引鍵，即主鍵。當通過輔助索引來查詢數(shù)據(jù)時，InnoDB存儲引擎會遍歷輔助索引找到主鍵，然后再通過主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)。

五、如何觸發(fā)聯(lián)合索引

1、對user表建立聯(lián)合索引username、password

2、觸發(fā)聯(lián)合索引

（1）使用聯(lián)合索引的全部索引鍵可觸發(fā)聯(lián)合索引

（2）使用聯(lián)合索引的全部索引鍵，但是用or連接的，不可觸發(fā)聯(lián)合索引

（3）單獨使用聯(lián)合索引的左邊第一個字段時，可觸發(fā)聯(lián)合索引

（4）單獨使用聯(lián)合索引的其它字段時，不可觸發(fā)聯(lián)合索引

六、分析sql的執(zhí)行計劃---explain

explain可以模擬sql優(yōu)化執(zhí)行sql語句。

1、explan使用簡介

（1）用戶表

（2）部門表

（3）未觸發(fā)索引

（4）觸發(fā)索引

（5）結果分析

explain中第一行出現(xiàn)的表是驅動表。

1. 指定了聯(lián)接條件時，滿足查詢條件的記錄行數(shù)少的表為[驅動表]

2. 未指定聯(lián)接條件時，行數(shù)少的表為[驅動表]

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

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：聯(lián)接類型

• system：表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個特例。

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

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

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

• ref_or_null：該聯(lián)接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經(jīng)常使用該聯(lián)接類型的優(yōu)化。

• index_merge：該聯(lián)接類型表示使用了索引合并優(yōu)化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。

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

• index_subquery：該聯(lián)接類型類似于unique_subquery?？梢蕴鎿QIN子查詢，但只適合下列形式的子查詢中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)

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

• index：該聯(lián)接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小。

• all：對于每個來自于先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，并且通常在它情況下很差。通?？梢栽黾痈嗟乃饕灰褂肁LL，使得行能基于前面的表中的常數(shù)值或列值被檢索出。

（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列顯示使用哪個列或常數(shù)與key一起從表中選擇行。

（9）rows：rows列顯示MySQL認為它執(zhí)行查詢時必須檢查的行數(shù)。

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

• Distinct：MySQL發(fā)現(xiàn)第1個匹配行后，停止為當前的行組合搜索更多的行。

• Not exists：MySQL能夠對查詢進行LEFT JOIN優(yōu)化，發(fā)現(xiàn)1個匹配LEFT JOIN標準的行后，不再為前面的的行組合在該表內(nèi)檢查更多的行。

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

• Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據(jù)聯(lián)接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。然后關鍵字被排序，并按排序順序檢索行。

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

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

• Using where：WHERE子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會有一些錯誤。

• Using sort_union(...), Using union(...), Using intersect(...)：這些函數(shù)說明如何為index_merge聯(lián)接類型合并索引掃描。

• Using index for group-by：類似于訪問表的Using index方式，Using index for group-by表示MySQL發(fā)現(xiàn)了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問實際的表。并且，按最有效的方式使用索引，以便對于每個組，只讀取少量索引條目。

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

到此，相信大家對“mysql的索引技巧有哪些”有了更深的了解，不妨來實際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關內(nèi)容可以進入相關頻道進行查詢，關注我們，繼續(xù)學習！

標題名稱：mysql的索引技巧有哪些
本文URL：http://aaarwkj.com/article22/gjdpjc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供標簽優(yōu)化、網(wǎng)站導航、營銷型網(wǎng)站建設、自適應網(wǎng)站、網(wǎng)站制作、小程序開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)