《MYSQL教程MySQL的最佳索引攻略》要點(diǎn)：
本文介紹了MYSQL教程MySQL的最佳索引攻略，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

今天在csdn上看到的一篇非常不錯(cuò)的文章,關(guān)于索引的使用和講解,維易PHP培訓(xùn)網(wǎng)整理一下分享給大家,希望對(duì)大家學(xué)習(xí)有所幫助.

所謂索引就是為特定的mysql字段進(jìn)行一些特定的算法排序,比如二叉樹(shù)的算法和哈希算法,哈希算法是通過(guò)建立特征值,然后根據(jù)特征值來(lái)快速查找.而用的最多,并且是mysql默認(rèn)的就是二叉樹(shù)算法 BTREE,通過(guò)BTREE算法建立索引的字段,比如掃描20行就能得到未使用BTREE前掃描了2^20行的結(jié)果,具體的實(shí)現(xiàn)方式后續(xù)本博客會(huì)出一個(gè)算法專(zhuān)題里面會(huì)有具體的分析討論;

Explain優(yōu)化查詢(xún)檢測(cè)

EXPLAIN可以幫助開(kāi)發(fā)人員分析SQL問(wèn)題,explain顯示了mysql如何使用索引來(lái)處理select語(yǔ)句以及連接表,可以幫助選擇更好的索引和寫(xiě)出更優(yōu)化的查詢(xún)語(yǔ)句.

使用方法,在select語(yǔ)句前加上Explain就可以了：

Explain select * from blog where false;

mysql在執(zhí)行一條查詢(xún)之前,會(huì)對(duì)發(fā)出的每條SQL進(jìn)行分析,決定是否使用索引或全表掃描如果發(fā)送一條select * from blog where falseMysql是不會(huì)執(zhí)行查詢(xún)操作的,因?yàn)榻?jīng)過(guò)SQL分析器的分析后MySQL已經(jīng)清楚不會(huì)有任何語(yǔ)句符合操作;

Example

1. mysql>?EXPLAIN?SELECT?`birday`?FROM?`user`?WHERE?`birthday`?<?"1990/2/2";??
2. --?結(jié)果：??
3. id:?1??
4. ???
5. select_type:?SIMPLE?--?查詢(xún)類(lèi)型(簡(jiǎn)單查詢(xún),聯(lián)合查詢(xún),子查詢(xún))??
6. ???
7. table:?user?--?顯示這一行的數(shù)據(jù)是關(guān)于哪張表的??
8. ???
9. type:?range?--?區(qū)間索引(在小于1990/2/2區(qū)間的數(shù)據(jù)),這是重要的列,顯示連接使用了何種類(lèi)型.從最好到最差的連接類(lèi)型為system?>?const?>?eq_ref?>?ref?>?fulltext?>?ref_or_null?>?index_merge?>?unique_subquery?>?index_subquery?>?range?>?index?>?ALL,const代表一次就命中,ALL代表掃描了全表才確定結(jié)果.一般來(lái)說(shuō),得保證查詢(xún)至少達(dá)到range級(jí)別,最好能達(dá)到ref.??
10. ???
11. possible_keys:?birthday??--?指出MySQL能使用哪個(gè)索引在該表中找到行.如果是空的,沒(méi)有相關(guān)的索引.這時(shí)要提高性能,可通過(guò)檢驗(yàn)WHERE子句,看是否引用某些字段,或者檢查字段不是適合索引.???
12. ???
13. key:?birthday?--?實(shí)際使用到的索引.如果為NULL,則沒(méi)有使用索引.如果為primary的話(huà),表示使用了主鍵.??
14. ???
15. key_len:?4?--?最長(zhǎng)的索引寬度.如果鍵是NULL,長(zhǎng)度就是NULL.在不損失精確性的情況下,長(zhǎng)度越短越好??
16. ???
17. ref:?const?--?顯示哪個(gè)字段或常數(shù)與key一起被使用.???
18. ???
19. rows:?1?--?這個(gè)數(shù)表示mysql要遍歷多少數(shù)據(jù)才能找到,在innodb上是不準(zhǔn)確的.??
20. ???
21. Extra:?Using?where;?Using?index?--?執(zhí)行狀態(tài)說(shuō)明,這里可以看到的壞的例子是Using?temporary和Using??

select_type

1.simple 簡(jiǎn)單select(不使用union或子查詢(xún))
2.primary 最外面的select
3.union union中的第二個(gè)或后面的select語(yǔ)句
4.dependent union union中的第二個(gè)或后面的select語(yǔ)句,取決于外面的查詢(xún)
5.union result union的結(jié)果.
6.subquery 子查詢(xún)中的第一個(gè)select
7.dependent subquery 子查詢(xún)中的第一個(gè)select,取決于外面的查詢(xún)
8.derived 導(dǎo)出表的select(from子句的子查詢(xún))

Extra與type詳細(xì)說(shuō)明

1.Distinct:一旦MYSQL找到了與行相聯(lián)合匹配的行,就不再搜索了
2.Not exists: MYSQL優(yōu)化了LEFT JOIN,一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行,就不再搜索了
3.Range checked for each Record(index map:#):沒(méi)有找到理想的索引,因此對(duì)于從前面表中來(lái)的每一個(gè)行組合,MYSQL檢查使用哪個(gè)索引,并用它來(lái)從表中返回行.這是使用索引的最慢的連接之一
4.Using filesort: 看到這個(gè)的時(shí)候,查詢(xún)就需要優(yōu)化了 .MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序.它根據(jù)連接類(lèi)型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行
5.Using index: 列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的,這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候
6.Using temporary 看到這個(gè)的時(shí)候,查詢(xún)需要優(yōu)化了 .這里,MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果,這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上,而不是GROUP BY上
7.Where used 使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶(hù).如果不想返回表中的全部行,并且連接類(lèi)型ALL或index,這就會(huì)發(fā)生,或者是查詢(xún)有問(wèn)題不同連接類(lèi)型的解釋(按照效率高低的順序排序
8.system 表只有一行：system表.這是const連接類(lèi)型的特殊情況
9.const:表中的一個(gè)記錄的最大值能夠匹配這個(gè)查詢(xún)(索引可以是主鍵或惟一索引).因?yàn)橹挥幸恍?這個(gè)值實(shí)際就是常數(shù),因?yàn)镸YSQL先讀這個(gè)值然后把它當(dāng)做常數(shù)來(lái)對(duì)待
10.eq_ref:在連接中,MYSQL在查詢(xún)時(shí),從前面的表中,對(duì)每一個(gè)記錄的聯(lián)合都從表中讀取一個(gè)記錄,它在查詢(xún)使用了索引為主鍵或惟一鍵的全部時(shí)使用
11.ref:這個(gè)連接類(lèi)型只有在查詢(xún)使用了不是惟一或主鍵的鍵或者是這些類(lèi)型的部分(比如,利用最左邊前綴)時(shí)發(fā)生.對(duì)于之前的表的每一個(gè)行聯(lián)合,全部記錄都將從表中讀出.這個(gè)類(lèi)型嚴(yán)重依賴(lài)于根據(jù)索引匹配的記錄多少—越少越好+
12.range:這個(gè)連接類(lèi)型使用索引返回一個(gè)范圍中的行,比如使用>或<查找東西時(shí)發(fā)生的情況+
13.index: 這個(gè)連接類(lèi)型對(duì)前面的表中的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描(比ALL更好,因?yàn)樗饕话阈∮诒頂?shù)據(jù))+
14ALL:這個(gè)連接類(lèi)型對(duì)于前面的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描,這一般比較糟糕,應(yīng)該盡量避免

其中type:

1.如果是Only index,這意味著信息只用索引樹(shù)中的信息檢索出的,這比掃描整個(gè)表要快.
2.如果是where used,就是使用上了where限制.
3.如果是impossible where 表示用不著where,一般就是沒(méi)查出來(lái)啥.
4.如果此信息顯示Using filesort或者Using temporary的話(huà)會(huì)很吃力,WHERE和ORDER BY的索引經(jīng)常無(wú)法兼顧,如果按照WHERE來(lái)確定索引,那么在ORDER BY時(shí),就必然會(huì)引起Using filesort,這就要看是先過(guò)濾再排序劃算,還是先排序再過(guò)濾劃算.

索引

索引的類(lèi)型

UNIQUE唯一索引

不可以出現(xiàn)相同的值,可以有NULL值

INDEX普通索引

允許出現(xiàn)相同的索引內(nèi)容

PRIMARY KEY主鍵索引

不允許出現(xiàn)相同的值,且不能為NULL值,一個(gè)表只能有一個(gè)primary_key索引

fulltext index 全文索引

上述三種索引都是針對(duì)列的值發(fā)揮作用,但全文索引,可以針對(duì)值中的某個(gè)單詞,比如一篇文章中的某個(gè)詞, 然而并沒(méi)有什么卵用,因?yàn)橹挥衜yisam以及英文支持,并且效率讓人不敢恭維,但是可以用coreseek和xunsearch等第三方應(yīng)用來(lái)完成這個(gè)需求

索引的CURD

索引的創(chuàng)建

ALTER TABLE

適用于表創(chuàng)建完畢之后再添加

ALTER TABLE 表名 ADD 索引類(lèi)型 (unique,primary key,fulltext,index)[索引名](字段名)
?MYSQL教程

1. ALTER?TABLE?`table_name`?ADD?INDEX?`index_name`?(`column_list`)?--?索引名,可要可不要;如果不要,當(dāng)前的索引名就是該字段名;??
2. ALTER?TABLE?`table_name`?ADD?UNIQUE?(`column_list`)??
3. ALTER?TABLE?`table_name`?ADD?PRIMARY?KEY?(`column_list`)??
4. ALTER?TABLE?`table_name`?ADD?FULLTEXT?KEY?(`column_list`)??

CREATE INDEX

CREATE INDEX可對(duì)表增加普通索引或UNIQUE索引

?MYSQL教程

1. --例,只能添加這兩種索引;??
2. CREATE?INDEX?index_name?ON?table_name?(column_list)??
3. CREATE?UNIQUE?INDEX?index_name?ON?table_name?(column_list)??
4. ?
5. 另外,還可以在建表時(shí)添加?
6. ?
7. CREATE?TABLE?`test1`?(??
8. ??`id`?smallint(5)?UNSIGNED?AUTO_INCREMENT?NOT?NULL,?--?注意,下面創(chuàng)建了主鍵索引,這里就不用創(chuàng)建了??
9. ??`username`?varchar(64)?NOT?NULL?COMMENT?'用戶(hù)名',??
10. ??`nickname`?varchar(50)?NOT?NULL?COMMENT?'昵稱(chēng)/姓名',??
11. ??`intro`?text,??
12. ??PRIMARY?KEY?(`id`),???
13. ??UNIQUE?KEY?`unique1`?(`username`),?--?索引名稱(chēng),可要可不要,不要就是和列名一樣??
14. ??KEY?`index1`?(`nickname`),??
15. ??FULLTEXT?KEY?`intro`?(`intro`)??
16. )?ENGINE=MyISAM?AUTO_INCREMENT=4?DEFAULT?CHARSET=utf8?COMMENT='后臺(tái)用戶(hù)表';??
17. ?
18. 索引的刪除?
19. ?
20. DROP?INDEX?`index_name`?ON?`talbe_name`???
21. ALTER?TABLE?`table_name`?DROP?INDEX?`index_name`??
22. --?這兩句都是等價(jià)的,都是刪除掉table_name中的索引index_name;??
23. ???
24. ALTER?TABLE?`table_name`?DROP?PRIMARY?KEY?--?刪除主鍵索引,注意主鍵索引只能用這種方式刪除??

索引的查看

show index from tablename \G;

索引的更改

更改個(gè)毛線(xiàn),刪掉重建一個(gè)既可

創(chuàng)建索引的技巧

1.維度高的列創(chuàng)建索引

數(shù)據(jù)列中 不重復(fù)值 出現(xiàn)的個(gè)數(shù),這個(gè)數(shù)量越高,維度就越高

如數(shù)據(jù)表中存在8行數(shù)據(jù)a ,b ,c,d,a,b,c,d這個(gè)表的維度為4

要為維度高的列創(chuàng)建索引,如性別和年齡,那年齡的維度就高于性別

性別這樣的列不適合創(chuàng)建索引,因?yàn)榫S度過(guò)低

2.對(duì) where,on,group by,order by 中出現(xiàn)的列使用索引

3.對(duì)較小的數(shù)據(jù)列使用索引,這樣會(huì)使索引文件更小,同時(shí)內(nèi)存中也可以裝載更多的索引鍵

4.為較長(zhǎng)的字符串使用前綴索引

5.不要過(guò)多創(chuàng)建索引,除了增加額外的磁盤(pán)空間外,對(duì)于DML操作的速度影響很大,因?yàn)槠涿吭鰟h改一次就得從新建立索引

6.使用組合索引,可以減少文件索引大小,在使用時(shí)速度要優(yōu)于多個(gè)單列索引

組合索引與前綴索引

注意,這兩種稱(chēng)呼是對(duì)建立索引技巧的一種稱(chēng)呼,并非索引的類(lèi)型;

組合索引

MySQL單列索引和組合索引究竟有何區(qū)別呢?

為了形象地對(duì)比兩者,先建一個(gè)表：
?MYSQL教程

1. CREATE?TABLE?`myIndex`?(??
2. ??`i_testID`?INT?NOT?NULL?AUTO_INCREMENT,???
3. ??`vc_Name`?VARCHAR(50)?NOT?NULL,???
4. ??`vc_City`?VARCHAR(50)?NOT?NULL,???
5. ??`i_Age`?INT?NOT?NULL,???
6. ??`i_SchoolID`?INT?NOT?NULL,???
7. ??PRIMARY?KEY?(`i_testID`)???
8. );??

假設(shè)表內(nèi)已有1000條數(shù)據(jù),在這 10000 條記錄里面 7 上 8 下地分布了 5 條 vc_Name="erquan" 的記錄,只不過(guò) city,age,school 的組合各不相同.來(lái)看這條 T-SQL：
?MYSQL教程

1. SELECT?`i_testID`?FROM?`myIndex`?WHERE?`vc_Name`='erquan'?AND?`vc_City`='鄭州'?AND?`i_Age`=25;?--?關(guān)聯(lián)搜索;??

首先考慮建MySQL單列索引：

在 vc_Name 列上建立了索引.執(zhí)行 T-SQL 時(shí),MYSQL 很快將目標(biāo)鎖定在了 vc_Name=erquan 的 5 條記錄上,取出來(lái)放到一中間結(jié)果集.在這個(gè)結(jié)果集里,先排除掉 vc_City 不等于"鄭州"的記錄,再排除 i_Age 不等于 25 的記錄,最后篩選出唯一的符合條件的記錄.雖然在 vc_Name 上建立了索引,查詢(xún)時(shí)MYSQL不用掃描整張表,效率有所提高,但離我們的要求還有一定的距離.同樣的,在 vc_City 和 i_Age 分別建立的MySQL單列索引的效率相似.

為了進(jìn)一步榨取 MySQL 的效率,就要考慮建立組合索引.就是將 vc_Name,vc_City,i_Age 建到一個(gè)索引里：

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

建表時(shí),vc_Name 長(zhǎng)度為 50,這里為什么用 10 呢?這就是下文要說(shuō)到的前綴索引,因?yàn)橐话闱闆r下名字的長(zhǎng)度不會(huì)超過(guò) 10,這樣會(huì)加速索引查詢(xún)速度,還會(huì)減少索引文件的大小,提高 INSERT 的更新速度.

執(zhí)行 T-SQL 時(shí),MySQL 無(wú)須掃描任何記錄就到找到唯一的記錄!!

如果分別在 vc_Name,vc_City,i_Age 上建立單列索引,讓該表有 3 個(gè)單列索引,查詢(xún)時(shí)和上述的組合索引效率一樣嗎?答案是大不一樣,遠(yuǎn)遠(yuǎn)低于我們的組合索引.雖然此時(shí)有了三個(gè)索引, 但 MySQL 只能用到其中的那個(gè)它認(rèn)為似乎是最有效率的單列索引,另外兩個(gè)是用不到的,也就是說(shuō)還是一個(gè)全表掃描的過(guò)程 .

建立這樣的組合索引,其實(shí)是相當(dāng)于分別建立了

vc_Name,vc_City,i_Age
vc_Name,vc_City
vc_Name
這樣的三個(gè)組合索引!為什么沒(méi)有 vc_City,i_Age 等這樣的組合索引呢?這是因?yàn)?mysql 組合索引 "最左前綴" 的結(jié)果.簡(jiǎn)單的理解就是只從最左面的開(kāi)始組合.并不是只要包含這三列的查詢(xún)都會(huì)用到該組合索引,下面的幾個(gè) T-SQL 會(huì)用到：

SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="鄭州" SELECT * FROM myIndex WHREE vc_Name="erquan"

而下面幾個(gè)則不會(huì)用到：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州" SELECT * FROM myIndex WHREE vc_City="鄭州"

也就是,name_city_age(vc_Name(10),vc_City,i_Age) 從左到右進(jìn)行索引,如果沒(méi)有左前索引Mysql不執(zhí)行索引查詢(xún)

前綴索引

如果索引列長(zhǎng)度過(guò)長(zhǎng),這種列索引時(shí)將會(huì)產(chǎn)生很大的索引文件,不便于操作,可以使用前綴索引方式進(jìn)行索引前綴索引應(yīng)該控制在一個(gè)合適的點(diǎn),控制在0.31黃金值即可(大于這個(gè)值就可以創(chuàng)建)

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 這個(gè)值大于0.31就可以創(chuàng)建前綴索引,Distinct去重復(fù) ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- 增加前綴索引SQL,將人名的索引建立在10,這樣可以減少索引文件大小,加快索引查詢(xún)速度

什么樣的sql不走索引

要盡量避免這些不走索引的sql
?MYSQL教程

1. SELECT?`sname`?FROM?`stu`?WHERE?`age`+10=30;--?不會(huì)使用索引,因?yàn)樗兴饕袇⑴c了計(jì)算??
2. ???
3. SELECT?`sname`?FROM?`stu`?WHERE?LEFT(`date`,4)?<1990;?--?不會(huì)使用索引,因?yàn)槭褂昧撕瘮?shù)運(yùn)算,原理與上面相同??
4. ???
5. SELECT?*?FROM?`houdunwang`?WHERE?`uname`?LIKE'后盾%'?--?走索引??
6. ???
7. SELECT?*?FROM?`houdunwang`?WHERE?`uname`?LIKE?"%后盾%"?--?不走索引??
8. ???
9. --?正則表達(dá)式不使用索引,這應(yīng)該很好理解,所以為什么在SQL中很難看到regexp關(guān)鍵字的原因??
10. ???
11. --?字符串與數(shù)字比較不使用索引;??
12. CREATE?TABLE?`a`?(`a`?char(10));??
13. EXPLAIN?SELECT?*?FROM?`a`?WHERE?`a`="1"?--?走索引??
14. EXPLAIN?SELECT?*?FROM?`a`?WHERE?`a`=1?--?不走索引??
15. ???
16. select?*?from?dept?where?dname='xxx'?or?loc='xx'?or?deptno=45?--如果條件中有or,即使其中有條件帶索引也不會(huì)使用.換言之,就是要求使用的所有字段,都必須建立索引,?我們建議大家盡量避免使用or?關(guān)鍵字??
17. ???
18. --?如果mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引??

多表關(guān)聯(lián)時(shí)的索引效率

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會(huì)使用索引,因?yàn)槭褂昧撕瘮?shù)運(yùn)算,原理與上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引



從上圖可以看出,所有表的type為all,表示全表索引;也就是6 6 6,共遍歷查詢(xún)了216次;

除第一張表示全表索引(必須的,要以此關(guān)聯(lián)其他表),其余的為range(索引區(qū)間獲得),也就是6+1+1+1,共遍歷查詢(xún)9次即可;

所以我們建議在多表join的時(shí)候盡量少join幾張表,因?yàn)橐徊恍⌒木褪且粋€(gè)笛卡爾乘積的恐怖掃描,另外,我們還建議盡量使用left join,以少關(guān)聯(lián)多.因?yàn)槭褂胘oin 的話(huà),第一張表是必須的全掃描的,以少關(guān)聯(lián)多就可以減少這個(gè)掃描次數(shù).

索引的弊端

不要盲目的創(chuàng)建索引,只為查詢(xún)操作頻繁的列創(chuàng)建索引,創(chuàng)建索引會(huì)使查詢(xún)操作變得更加快速,但是會(huì)降低增加、刪除、更新操作的速度,因?yàn)閳?zhí)行這些操作的同時(shí)會(huì)對(duì)索引文件進(jìn)行重新排序或更新;

但是,在互聯(lián)網(wǎng)應(yīng)用中,查詢(xún)的語(yǔ)句遠(yuǎn)遠(yuǎn)大于DML的語(yǔ)句,甚至可以占到80%~90%,所以也不要太在意,只是在大數(shù)據(jù)導(dǎo)入時(shí),可以先刪除索引,再批量插入數(shù)據(jù),最后再添加索引;MYSQL教程

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/5795.html