日本久久久久久久久久,99热精品久久只有精品,久久婷婷人人澡人人爽人人爱

本篇文章給大家?guī)淼膬?nèi)容是關(guān)于mysql索引以及查詢優(yōu)化的詳細(xì)介紹，有一定的參考價(jià)值，有需要的朋友可以參考一下，希望對(duì)你有所幫助。

文章《MySQL查詢分析》講述了使用MySQL慢查詢和explain命令來定位mysql性能瓶頸的方法，定位出性能瓶頸的sql語句后，則需要對(duì)低效的sql語句進(jìn)行優(yōu)化。本文主要討論MySQL索引原理及常用的sql查詢優(yōu)化。

一個(gè)簡(jiǎn)單的對(duì)比測(cè)試

前面的案例中，c2c_zwdb.t_file_count表只有一個(gè)自增id，F(xiàn)FileName字段未加索引的sql執(zhí)行情況如下：

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

在上圖中，type=all，key=NULL，rows=33777。該sql未使用索引，是一個(gè)效率非常低的全表掃描。如果加上聯(lián)合查詢和其他一些約束條件，數(shù)據(jù)庫會(huì)瘋狂的消耗內(nèi)存，并且會(huì)影響前端程序的執(zhí)行。

這時(shí)給FFileName字段添加一個(gè)索引：

alter table c2c_zwdb.t_file_count add index index_title(FFileName);

再次執(zhí)行上述查詢語句，其對(duì)比很明顯：

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

在該圖中，type=ref，key=索引名（index_title），rows=1。該sql使用了索引index_title，且是一個(gè)常數(shù)掃描，根據(jù)索引只掃描了一行。

比起未加索引的情況，加了索引后，查詢效率對(duì)比非常明顯。

MySQL索引

通過上面的對(duì)比測(cè)試可以看出，索引是快速搜索的關(guān)鍵。MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的。對(duì)于少量的數(shù)據(jù)，沒有合適的索引影響不是很大，但是，當(dāng)隨著數(shù)據(jù)量的增加，性能會(huì)急劇下降。如果對(duì)多列進(jìn)行索引(組合索引)，列的順序非常重要，MySQL僅能對(duì)索引最左邊的前綴進(jìn)行有效的查找。

下面介紹幾種常見的MySQL索引類型。

索引分單列索引和組合索引。單列索引，即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引，但這不是組合索引。組合索引，即一個(gè)索引包含多個(gè)列。

1、MySQL索引類型

(1) 主鍵索引 PRIMARY KEY

它是一種特殊的唯一索引，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引。

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

當(dāng)然也可以用 ALTER 命令。記住：一個(gè)表只能有一個(gè)主鍵。

(2) 唯一索引 UNIQUE

唯一索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。可以在創(chuàng)建表的時(shí)候指定，也可以修改表結(jié)構(gòu)，如：

ALTER TABLE table_name ADD UNIQUE (column)

(3) 普通索引 INDEX

這是最基本的索引，它沒有任何限制。可以在創(chuàng)建表的時(shí)候指定，也可以修改表結(jié)構(gòu)，如：

ALTER TABLE table_name ADD INDEX index_name (column)

(4) 組合索引 INDEX

組合索引，即一個(gè)索引包含多個(gè)列。可以在創(chuàng)建表的時(shí)候指定，也可以修改表結(jié)構(gòu)，如：

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

(5) 全文索引 FULLTEXT

全文索引（也稱全文檢索）是目前搜索引擎使用的一種關(guān)鍵技術(shù)。它能夠利用分詞技術(shù)等多種算法智能分析出文本文字中關(guān)鍵字詞的頻率及重要性，然后按照一定的算法規(guī)則智能地篩選出我們想要的搜索結(jié)果。

可以在創(chuàng)建表的時(shí)候指定，也可以修改表結(jié)構(gòu)，如：

ALTER TABLE table_name ADD FULLTEXT (column)

2、索引結(jié)構(gòu)及原理

mysql中普遍使用B+Tree做索引，但在實(shí)現(xiàn)上又根據(jù)聚簇索引和非聚簇索引而不同，本文暫不討論這點(diǎn)。

b+樹介紹

下面這張b+樹的圖片在很多地方可以看到，之所以在這里也選取這張，是因?yàn)橛X得這張圖片可以很好的詮釋索引的查找過程。

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

如上圖，是一顆b+樹。淺藍(lán)色的塊我們稱之為一個(gè)磁盤塊，可以看到每個(gè)磁盤塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤塊1包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。

真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節(jié)點(diǎn)不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如17、35并不真實(shí)存在于數(shù)據(jù)表中。

查找過程

在上圖中，如果要查找數(shù)據(jù)項(xiàng)29，那么首先會(huì)把磁盤塊1由磁盤加載到內(nèi)存，此時(shí)發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P的IO）可以忽略不計(jì)，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時(shí)內(nèi)存中做二分查找找到29，結(jié)束查詢，總計(jì)三次IO。真實(shí)的情況是，3層的b+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。

性質(zhì)

(1) 索引字段要盡量的小。

通過上面b+樹的查找過程，或者通過真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)這個(gè)事實(shí)可知，IO次數(shù)取決于b+數(shù)的高度h。

假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)量為N，每個(gè)磁盤塊的數(shù)據(jù)項(xiàng)的數(shù)量是m，則樹高h(yuǎn)=㏒(m+1)N，當(dāng)數(shù)據(jù)量N一定的情況下，m越大，h越小；

而m = 磁盤塊的大小/數(shù)據(jù)項(xiàng)的大小，磁盤塊的大小也就是一個(gè)數(shù)據(jù)頁的大小，是固定的；如果數(shù)據(jù)項(xiàng)占的空間越小，數(shù)據(jù)項(xiàng)的數(shù)量m越多，樹的高度h越低。這就是為什么每個(gè)數(shù)據(jù)項(xiàng)，即索引字段要盡量的小，比如int占4字節(jié)，要比bigint8字節(jié)少一半。

(2) 索引的最左匹配特性。

當(dāng)b+樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時(shí)候，b+數(shù)是按照從左到右的順序來建立搜索樹的，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來檢索的時(shí)候，b+樹會(huì)優(yōu)先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來的時(shí)候，b+樹就不知道下一步該查哪個(gè)節(jié)點(diǎn)，因?yàn)榻⑺阉鳂涞臅r(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來檢索時(shí)，b+樹可以用name來指定搜索方向，但下一個(gè)字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了，這個(gè)是非常重要的性質(zhì)，即索引的最左匹配特性。

建索引的幾大原則

(1) 最左前綴匹配原則

對(duì)于多列索引，總是從索引的最前面字段開始，接著往后，中間不能跳過。比如創(chuàng)建了多列索引(name,age,sex)，會(huì)先匹配name字段，再匹配age字段，再匹配sex字段的，中間不能跳過。mysql會(huì)一直向右匹配直到遇到范圍查詢(>、

一般，在創(chuàng)建多列索引時(shí)，where子句中使用最頻繁的一列放在最左邊。

看一個(gè)補(bǔ)符合最左前綴匹配原則和符合該原則的對(duì)比例子。

實(shí)例：表c2c_db.t_credit_detail建有索引(Flistid,Fbank_listid)

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

不符合最左前綴匹配原則的sql語句：

select * from t_credit_detail where Fbank_listid=’201108010000199’G

該sql直接用了第二個(gè)索引字段Fbank_listid，跳過了第一個(gè)索引字段Flistid，不符合最左前綴匹配原則。用explain命令查看sql語句的執(zhí)行計(jì)劃，如下圖：

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

從上圖可以看出，該sql未使用索引，是一個(gè)低效的全表掃描。

符合最左前綴匹配原則的sql語句：

select * from t_credit_detail where Flistid=’2000000608201108010831508721′ and Fbank_listid=’201108010000199’G

該sql先使用了索引的第一個(gè)字段Flistid，再使用索引的第二個(gè)字段Fbank_listid，中間沒有跳過，符合最左前綴匹配原則。用explain命令查看sql語句的執(zhí)行計(jì)劃，如下圖：

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

從上圖可以看出，該sql使用了索引，僅掃描了一行。

對(duì)比可知，符合最左前綴匹配原則的sql語句比不符合該原則的sql語句效率有極大提高，從全表掃描上升到了常數(shù)掃描。

(2) 盡量選擇區(qū)分度高的列作為索引。

比如，我們會(huì)選擇學(xué)號(hào)做索引，而不會(huì)選擇性別來做索引。

(3) =和in可以亂序

比如a = 1 and b = 2 and c = 3，建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式。

(4) 索引列不能參與計(jì)算，保持列“干凈”

比如：Flistid+1>‘2000000608201108010831508721‘。原因很簡(jiǎn)單，假如索引列參與計(jì)算的話，那每次檢索時(shí)，都會(huì)先將索引計(jì)算一次，再做比較，顯然成本太大。

(5) 盡量的擴(kuò)展索引，不要新建索引。

比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。

索引的不足

雖然索引可以提高查詢效率，但索引也有自己的不足之處。

索引的額外開銷：

(1) 空間：索引需要占用空間；

(2) 時(shí)間：查詢索引需要時(shí)間；

(3) 維護(hù)：索引須要維護(hù)（數(shù)據(jù)變更時(shí)）；

不建議使用索引的情況：

(1) 數(shù)據(jù)量很小的表

(2) 空間緊張

常用優(yōu)化總結(jié)

優(yōu)化語句很多，需要注意的也很多，針對(duì)平時(shí)的情況總結(jié)一下幾點(diǎn)：

1、有索引但未被用到的情況（不建議）

(1) Like的參數(shù)以通配符開頭時(shí)

盡量避免Like的參數(shù)以通配符開頭，否則數(shù)據(jù)庫引擎會(huì)放棄使用索引而進(jìn)行全表掃描。

以通配符開頭的sql語句，例如：select * from t_credit_detail where Flistid like ‘%0’G

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

這是全表掃描，沒有使用到索引，不建議使用。

不以通配符開頭的sql語句，例如：select * from t_credit_detail where Flistid like ‘2%’G

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

很明顯，這使用到了索引，是有范圍的查找了，比以通配符開頭的sql語句效率提高不少。

(2) where條件不符合最左前綴原則時(shí)

例子已在最左前綴匹配原則的內(nèi)容中有舉例。

(3) 使用！= 或操作符時(shí)

盡量避免使用！= 或操作符，否則數(shù)據(jù)庫引擎會(huì)放棄使用索引而進(jìn)行全表掃描。使用>或

select * from t_credit_detail where Flistid != ‘2000000608201108010831508721’G

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

(4) 索引列參與計(jì)算

應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

select * from t_credit_detail where Flistid +1 > ‘2000000608201108010831508722’G

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

(5) 對(duì)字段進(jìn)行null值判斷

應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行null值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：低效：select * from t_credit_detail where Flistid is null ;

可以在Flistid上設(shè)置默認(rèn)值0，確保表中Flistid列沒有null值，然后這樣查詢：高效：select * from t_credit_detail where Flistid =0;

(6) 使用or來連接條件

應(yīng)盡量避免在where子句中使用or來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：低效：select * from t_credit_detail where Flistid = ‘2000000608201108010831508721’ or Flistid = ‘10000200001’;

可以用下面這樣的查詢代替上面的 or 查詢：高效：select from t_credit_detail where Flistid = ‘2000000608201108010831508721’ union all select from t_credit_detail where Flistid = ‘10000200001’;

MySQL索引以及查詢優(yōu)化的詳細(xì)介紹

2、避免select *

在解析的過程中，會(huì)將’*’ 依次轉(zhuǎn)換成所有的列名，這個(gè)工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費(fèi)更多的時(shí)間。

所以，應(yīng)該養(yǎng)成一個(gè)需要什么就取什么的好習(xí)慣。

3、order by 語句優(yōu)化

任何在Order by語句的非索引項(xiàng)或者有計(jì)算表達(dá)式都將降低查詢速度。

方法：1.重寫order by語句以使用索引；

  2.為所使用的列建立另外一個(gè)索引    3.絕對(duì)避免在order by子句中使用表達(dá)式。

4、GROUP BY語句優(yōu)化

提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉

低效:

SELECT JOB , AVG(SAL)

FROM EMP

GROUP by JOB

HAVING JOB = ‘PRESIDENT’

OR JOB = ‘MANAGER’

高效:

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = ‘PRESIDENT’

OR JOB = ‘MANAGER’

GROUP by JOB

5、用 exists 代替 in

很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇： select num from a where num in(select num from b) 用下面的語句替換： select num from a where exists(select 1 from b where num=a.num)