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