《MYSQL教程MySQL數(shù)據(jù)庫(kù)查詢索引優(yōu)化》要點(diǎn)：
本文介紹了MYSQL教程MySQL數(shù)據(jù)庫(kù)查詢索引優(yōu)化，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。
對(duì)于任何DBMS,索引都是進(jìn)行優(yōu)化的最主要的因素.對(duì)于少量的數(shù)據(jù),沒(méi)有合適的索引影響不是很大,但是,當(dāng)隨著數(shù)據(jù)量的增加,性能會(huì)急劇下降.MYSQL數(shù)據(jù)庫(kù)
小寶鴿試了一下,2.5萬(wàn)數(shù)據(jù)單表中,無(wú)索引：200ms-700ms,添加索引后10ms-15ms,使用redis緩存1ms-7ms,如果數(shù)據(jù)量更大的時(shí)候,索引效果將會(huì)更加明顯.更甚者,多表查詢.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
1、除了詞典,生活中隨處可見(jiàn)索引的例子,如火車站的車次表、圖書(shū)的目錄等.它們的原理都是一樣的,通過(guò)不斷的縮小想要獲得數(shù)據(jù)的范圍來(lái)篩選出最終想要的結(jié)果,同時(shí)把隨機(jī)的事件變成順序的事件,也就是我們總是通過(guò)同一種查找方式來(lái)鎖定數(shù)據(jù).MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)也是一樣,但顯然要復(fù)雜許多,因?yàn)椴粌H面臨著等值查詢,還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等.數(shù)據(jù)庫(kù)應(yīng)該選擇怎么樣的方式來(lái)應(yīng)對(duì)所有的問(wèn)題呢?我們回想字典的例子,能不能把數(shù)據(jù)分成段,然后分段查詢呢?最簡(jiǎn)單的如果1000條數(shù)據(jù),1到100分成第一段,101到200分成第二段,201到300分成第三段……這樣查第250條數(shù)據(jù),只要找第三段就可以了,一下子去除了90%的無(wú)效數(shù)據(jù).但如果是1千萬(wàn)的記錄呢,分成幾段比較好?稍有算法基礎(chǔ)的同學(xué)會(huì)想到搜索樹(shù),其平均復(fù)雜度是lgN,具有不錯(cuò)的查詢性能.但這里我們忽略了一個(gè)關(guān)鍵的問(wèn)題,復(fù)雜度模型是基于每次相同的操作成本來(lái)考慮的,數(shù)據(jù)庫(kù)實(shí)現(xiàn)比較復(fù)雜,數(shù)據(jù)保存在磁盤上,而為了提高性能,每次又可以把部分?jǐn)?shù)據(jù)讀入內(nèi)存來(lái)計(jì)算,因?yàn)槲覀冎涝L問(wèn)磁盤的成本大概是訪問(wèn)內(nèi)存的十萬(wàn)倍左右,所以簡(jiǎn)單的搜索樹(shù)難以滿足復(fù)雜的應(yīng)用場(chǎng)景.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
2、另外,比如學(xué)生信息表,添加學(xué)生姓名索引,索引是在name上排序的.現(xiàn)在,當(dāng)查找某個(gè)學(xué)生信息時(shí),就不需要逐行搜索全表,可以利用索引進(jìn)行有序查找(如二分查找法),并快速定位到匹配的值,以節(jié)省大量搜索時(shí)間.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
3、是當(dāng)數(shù)據(jù)量非常大,查詢涉及多個(gè)表時(shí),使用索引往往能使查詢速度加快成千上萬(wàn)倍.?MYSQL數(shù)據(jù)庫(kù)
例如,有3個(gè)未索引的表t1、t2、t3,分別只包含列c1、c2、c3,每個(gè)表分別含有1000行數(shù)據(jù)組成,指為1～1000的數(shù)值,查找對(duì)應(yīng)值相等行的查詢?nèi)缦滤?MYSQL數(shù)據(jù)庫(kù)
SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
此查詢結(jié)果應(yīng)該為1000行,每行包含3個(gè)相等的值.在無(wú)索引的情況下處理此查詢,必須尋找3個(gè)表所有的組合,以便得出與WHERE子句相配的那些行.而可能的組合數(shù)目為1000×1000×1000(十億),顯然查詢將會(huì)非常慢.MYSQL數(shù)據(jù)庫(kù)
如果對(duì)每個(gè)表進(jìn)行索引,就能極大地加速查詢進(jìn)程.利用索引的查詢處理如下.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
(1)從表t1中選擇第一行,查看此行所包含的數(shù)據(jù).MYSQL數(shù)據(jù)庫(kù)
(2)使用表t2上的索引,直接定位t2中與t1的值匹配的行.類似,利用表t3上的索引,直接定位t3中與來(lái)自t1的值匹配的行.MYSQL數(shù)據(jù)庫(kù)
(3)掃描表t1的下一行并重復(fù)前面的過(guò)程,直到遍歷t1中所有的行.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
在此情形下,仍然對(duì)表t1執(zhí)行了一個(gè)完全掃描,但能夠在表t2和t3上進(jìn)行索引查找直接取出這些表中的行,比未用索引時(shí)要快一百萬(wàn)倍.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
利用索引,MySQL加速了WHERE子句滿足條件行的搜索,而在多表連接查詢時(shí),在執(zhí)行連接時(shí)加快了與其他表中的行匹配的速度.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
MySQL的索引包括普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
1、普通索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
在創(chuàng)建普通索引時(shí),不附加任何限制條件.這類索引可以創(chuàng)建在任何數(shù)據(jù)類型中,其值是否唯一和非空由字段本身的完整性約束條件決定.建立索引以后,查詢時(shí)可以通過(guò)索引進(jìn)行查詢.例如,在student表的stu_id字段上建立一個(gè)普通索引.查詢記錄時(shí),就可以根據(jù)該索引進(jìn)行查詢.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
2、唯一性索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
使用UNIQUE參數(shù)可以設(shè)置索引為唯一性索引.在創(chuàng)建唯一性索引時(shí),限制該MYSQL數(shù)據(jù)庫(kù)
索引的值必須是唯一的.例如,在student表的stu_name字段中創(chuàng)建唯一性索引,那么stu_name字段的值就必需是唯一的.通過(guò)唯一性索引,可以更快速地確定某條記錄.主鍵就是一種特殊唯一性索引.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
3、全文索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
使用FULLTEXT參數(shù)可以設(shè)置索引為全文索引.全文索引只能創(chuàng)建在CHAR、VARCHAR或TEXT類型的字段上.查詢數(shù)據(jù)量較大的字符串類型的字段時(shí),使用全文索引可以提高查詢速度.例如,student表的information字段是TEXT類型,該字段包含了很多的文字信息.在information字段上建立全文索引后,可以提高查詢information字段的速度.MySQL數(shù)據(jù)庫(kù)從3.23.23版開(kāi)始支持全文索引,但只有MyISAM存儲(chǔ)引擎支持全文檢索.在默認(rèn)情況下,全文索引的搜索執(zhí)行方式不區(qū)分大小寫.但索引的列使用二進(jìn)制排序后,可以執(zhí)行區(qū)分大小寫的全文索引.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
4、單列索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
在表中的單個(gè)字段上創(chuàng)建索引.單列索引只根據(jù)該字段進(jìn)行索引.單列索引可以是普通索引,也可以是唯一性索引,還可以是全文索引.只要保證該索引只對(duì)應(yīng)一個(gè)字段 即可.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
5、多列索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
多列索引是在表的多個(gè)字段上創(chuàng)建一個(gè)索引.該索引指向創(chuàng)建時(shí)對(duì)應(yīng)的多個(gè)字段,可以通過(guò)這幾個(gè)字段進(jìn)行查詢.但是,只有查詢條件中使用了這些字段中第一個(gè)字段時(shí),索引才會(huì)被使用.例如,在表中的id、name和sex字段上建立一個(gè)多列索引,那么,只有查詢條件使用了id字段時(shí)該索引才會(huì)被使用.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
6、空間索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
使用SPATIAL參數(shù)可以設(shè)置索引為空間索引.空間索引只能建立在空間數(shù)據(jù)類型上,這樣可以提高系統(tǒng)獲取空間數(shù)據(jù)的效率.MySQL中的空間數(shù)據(jù)類型包括GEOMETRY和POINT、LINESTRING和POLYGON等.目前只有MyISAM存儲(chǔ)引擎支持空間檢索,而且索引的字段不能為空值.對(duì)于初學(xué)者來(lái)說(shuō),這類索引很少會(huì)用到.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
1、添加PRIMARY KEY(主鍵索引)MYSQL數(shù)據(jù)庫(kù)
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2、添加UNIQUE(唯一索引)MYSQL數(shù)據(jù)庫(kù)
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )
3、添加INDEX(普通索引)MYSQL數(shù)據(jù)庫(kù)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4、添加FULLTEXT(全文索引)MYSQL數(shù)據(jù)庫(kù)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5、添加多列索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
創(chuàng)建索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
在執(zhí)行CREATE TABLE語(yǔ)句時(shí)可以創(chuàng)建索引,也可以單獨(dú)用CREATE INDEX或ALTER TABLE來(lái)為表增加索引.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
1、ALTER TABLEMYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
ALTER TABLE用來(lái)創(chuàng)建普通索引、UNIQUE索引或PRIMARY KEY索引.MYSQL數(shù)據(jù)庫(kù)
ALTER TABLE table_name ADD INDEX index_name (column_list) ?
ALTER TABLE table_name ADD UNIQUE (column_list) ?
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
其中table_name是要增加索引的表名,column_list指出對(duì)哪些列進(jìn)行索引,多列時(shí)各列之間用逗號(hào)分隔.索引名index_name可選,缺省時(shí),MySQL將根據(jù)第一個(gè)索引列賦一個(gè)名稱.另外,ALTER TABLE允許在單個(gè)語(yǔ)句中更改多個(gè)表,因此可以在同時(shí)創(chuàng)建多個(gè)索引.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
2、CREATE INDEXMYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
CREATE INDEX可對(duì)表增加普通索引或UNIQUE索引.MYSQL數(shù)據(jù)庫(kù)
CREATE INDEX index_name ON table_name (column_list) ?
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name、index_name和column_list具有與ALTER TABLE語(yǔ)句中相同的含義,索引名不可選.另外,不能用CREATE INDEX語(yǔ)句創(chuàng)建PRIMARY KEY索引.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
3、索引類型MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
在創(chuàng)建索引時(shí),可以規(guī)定索引能否包含重復(fù)值.如果不包含,則索引應(yīng)該創(chuàng)建為PRIMARY KEY或UNIQUE索引.對(duì)于單列惟一性索引,這保證單列不包含重復(fù)的值.對(duì)于多列惟一性索引,保證多個(gè)值的組合不重復(fù).?MYSQL數(shù)據(jù)庫(kù)
PRIMARY KEY索引和UNIQUE索引非常類似.事實(shí)上,PRIMARY KEY索引僅是一個(gè)具有名稱PRIMARY的UNIQUE索引.這表示一個(gè)表只能包含一個(gè)PRIMARY KEY,因?yàn)橐粋€(gè)表中不可能具有兩個(gè)同名的索引.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
下面的SQL語(yǔ)句對(duì)students表在sid上添加PRIMARY KEY索引.MYSQL數(shù)據(jù)庫(kù)
ALTER TABLE students ADD PRIMARY KEY (sid)
刪除索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
可利用ALTER TABLE或DROP INDEX語(yǔ)句來(lái)刪除索引.類似于CREATE INDEX語(yǔ)句,DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語(yǔ)句處理,語(yǔ)法如下.MYSQL數(shù)據(jù)庫(kù)
DROP INDEX index_name ON talbe_name ?
ALTER TABLE table_name DROP INDEX index_name ?
ALTER TABLE table_name DROP PRIMARY KEY
其中,前兩條語(yǔ)句是等價(jià)的,刪除掉table_name中的索引index_name.MYSQL數(shù)據(jù)庫(kù)
第3條語(yǔ)句只在刪除PRIMARY KEY索引時(shí)使用,因?yàn)橐粋€(gè)表只可能有一個(gè)PRIMARY KEY索引,因此不需要指定索引名.如果沒(méi)有創(chuàng)建PRIMARY KEY索引,但表具有一個(gè)或多個(gè)UNIQUE索引,則MySQL將刪除第一個(gè)UNIQUE索引.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
如果從表中刪除了某列,則索引會(huì)受到影響.對(duì)于多列組合的索引,如果刪除其中的某列,則該列也會(huì)從索引中刪除.如果刪除組成索引的所有列,則整個(gè)索引將被刪除.MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
注：一般數(shù)據(jù)庫(kù)默認(rèn)都會(huì)為主鍵生成索引MYSQL數(shù)據(jù)庫(kù)
?MYSQL數(shù)據(jù)庫(kù)
參考文章：?
http://blog.csdn.net/yuanzhuohang/article/details/6497021?
http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html?
http://edu.cnzz.cn/201305/88671f51.shtmlMYSQL數(shù)據(jù)庫(kù)
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5810.html
