一.索引的作用

一般的應(yīng)用系統(tǒng)，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現(xiàn)性能問(wèn)題，遇到最多的，也是最容易出問(wèn)題的，還是一些復(fù)雜的查詢操作，所以查詢語(yǔ)句的優(yōu)化顯然是重中之重。

[[274518]]

在數(shù)據(jù)量和訪問(wèn)量不大的情況下，mysql訪問(wèn)是非常快的，是否加索引對(duì)訪問(wèn)影響不大。但是當(dāng)數(shù)據(jù)量和訪問(wèn)量劇增的時(shí)候，就會(huì)發(fā)現(xiàn)mysql變慢，甚至down掉，這就必須要考慮優(yōu)化sql了，給數(shù)據(jù)庫(kù)建立正確合理的索引，是mysql優(yōu)化的一個(gè)重要手段。

索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個(gè)單詞，我們肯定需要定位到m字母，然后從上往下找到y(tǒng)字母，再找到剩下的sql。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的。除了詞典，生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過(guò)不斷的縮小想要獲得數(shù)據(jù)的范圍來(lái)篩選出最終想要的結(jié)果，同時(shí)把隨機(jī)的事件變成順序的事件，也就是我們總是通過(guò)同一種查找方式來(lái)鎖定數(shù)據(jù)。

在創(chuàng)建索引時(shí)，需要考慮哪些列會(huì)用于 SQL 查詢，然后為這些列創(chuàng)建一個(gè)或多個(gè)索引。事實(shí)上，索引也是一種表，保存著主鍵或索引字段，以及一個(gè)能將每個(gè)記錄指向?qū)嶋H表的指針。數(shù)據(jù)庫(kù)用戶是看不到索引的，它們只是用來(lái)加速查詢的。數(shù)據(jù)庫(kù)搜索引擎使用索引來(lái)快速定位記錄。

INSERT 與 UPDATE 語(yǔ)句在擁有索引的表中執(zhí)行會(huì)花費(fèi)更多的時(shí)間，而SELECT 語(yǔ)句卻會(huì)執(zhí)行得更快。這是因?yàn)?，在進(jìn)行插入或更新時(shí)，數(shù)據(jù)庫(kù)也需要插入或更新索引值。

二.索引的創(chuàng)建、刪除

索引的類型：

• UNIQUE(唯一索引)：不可以出現(xiàn)相同的值，可以有NULL值
• INDEX(普通索引)：允許出現(xiàn)相同的索引內(nèi)容
• PROMARY KEY(主鍵索引)：不允許出現(xiàn)相同的值
• fulltext index(全文索引)：可以針對(duì)值中的某個(gè)單詞，但效率確實(shí)不敢恭維
• 組合索引：實(shí)質(zhì)上是將多個(gè)字段建到一個(gè)索引里，列值的組合必須唯一

溫馨提示：根據(jù)《阿里巴巴Java開發(fā)手冊(cè)》里的mysql規(guī)約，唯一索引建議命名為uk_字段名，普通索引名則為idx_字段名。(uk_即unique key; idx_即index的簡(jiǎn)稱)。

(1)使用ALTER TABLE語(yǔ)句創(chuàng)建索性

應(yīng)用于表創(chuàng)建完畢之后再添加。

ALTER TABLE 表名 ADD 索引類型 （unique,primary key,fulltext,index）[索引名]（字段名） 

//普通索引 
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) ; 

ALTER TABLE可用于創(chuàng)建普通索引、UNIQUE索引和PRIMARY KEY索引3種索引格式，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è)索引。

(2)使用CREATE INDEX語(yǔ)句對(duì)表增加索引

CREATE INDEX可用于對(duì)表增加普通索引或UNIQUE索引，可用于建表時(shí)創(chuàng)建索引。

CREATE INDEX index_name ON table_name(username(length)); 

如果是CHAR，VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度;如果是BLOB和TEXT類型，必須指定 length。

//只能添加這兩種索引; 
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索引。

(3)刪除索引

刪除索引可以使用ALTER TABLE或DROP INDEX語(yǔ)句來(lái)實(shí)現(xiàn)。DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語(yǔ)句處理，其格式如下：

drop index index_name on table_name ; 
 
alter table table_name drop index index_name ; 
 
alter table table_name drop primary key ; 

其中，在前面的兩條語(yǔ)句中，都刪除了table_name中的索引index_name。而在最后一條語(yǔ)句中，只在刪除PRIMARY KEY索引中使用，因?yàn)橐粋€(gè)表只可能有一個(gè)PRIMARY KEY索引，因此不需要指定索引名。如果沒有創(chuàng)建PRIMARY KEY索引，但表具有一個(gè)或多個(gè)UNIQUE索引，則MySQL將刪除第一個(gè)UNIQUE索引。

如果從表中刪除某列，則索引會(huì)受影響。對(duì)于多列組合的索引，如果刪除其中的某列，則該列也會(huì)從索引中刪除。如果刪除組成索引的所有列，則整個(gè)索引將被刪除。

(4) 組合索引與前綴索引

在這里要指出，組合索引和前綴索引是對(duì)建立索引技巧的一種稱呼，并不是索引的類型。為了更好的表述清楚，建立一個(gè)demo表如下。

create table USER_DEMO 
( 
   ID                   int not null auto_increment comment '主鍵', 
   LOGIN_NAME           varchar(100) not null comment '登錄名', 
   PASSWORD             varchar(100) not null comment '密碼', 
   CITY                 varchar(30) not null comment '城市', 
   AGE                  int not null comment '年齡', 
   SEX                  int not null comment '性別(0:女 1：男)', 
   primary key (ID) 
); 

為了進(jìn)一步榨取mysql的效率，就可以考慮建立組合索引，即將LOGIN_NAME,CITY,AGE建到一個(gè)索引里：

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE); 

建表時(shí)，LOGIN_NAME長(zhǎng)度為100，這里用16，是因?yàn)橐话闱闆r下名字的長(zhǎng)度不會(huì)超過(guò)16，這樣會(huì)加快索引查詢速度，還會(huì)減少索引文件的大小，提高INSERT，UPDATE的更新速度。

如果分別給LOGIN_NAME,CITY,AGE建立單列索引，讓該表有3個(gè)單列索引，查詢時(shí)和組合索引的效率是大不一樣的，甚至遠(yuǎn)遠(yuǎn)低于我們的組合索引。雖然此時(shí)有三個(gè)索引，但mysql只能用到其中的那個(gè)它認(rèn)為似乎是最有效率的單列索引，另外兩個(gè)是用不到的，也就是說(shuō)還是一個(gè)全表掃描的過(guò)程。

建立這樣的組合索引，就相當(dāng)于分別建立如下三種組合索引：

LOGIN_NAME,CITY,AGE 
LOGIN_NAME,CITY 
LOGIN_NAME 

為什么沒有CITY,AGE等這樣的組合索引呢?這是因?yàn)閙ysql組合索引“最左前綴"的結(jié)果。簡(jiǎn)單的理解就是只從最左邊的開始組合，并不是只要包含這三列的查詢都會(huì)用到該組合索引。也就是說(shuō)name_city_age(LOGIN_NAME(16),CITY,AGE)從左到右進(jìn)行索引，如果沒有左前索引，mysql不會(huì)執(zhí)行索引查詢。

如果索引列長(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,這樣可以減少索引文件大小,加快索引查詢速度 

三.索引的使用及注意事項(xiàng)

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

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

Explain select * from user where id=1; 

盡量避免這些不走索引的sql：

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

索引雖然好處很多，但過(guò)多的使用索引可能帶來(lái)相反的問(wèn)題，索引也是有缺點(diǎn)的：

• 雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT,UPDATE和DELETE。因?yàn)楦卤頃r(shí)，mysql不僅要保存數(shù)據(jù)，還要保存一下索引文件
• 建立索引會(huì)占用磁盤空間的索引文件。一般情況這個(gè)問(wèn)題不太嚴(yán)重，但如果你在要給大表上建了多種組合索引，索引文件會(huì)膨脹很寬

索引只是提高效率的一個(gè)方式，如果mysql有大數(shù)據(jù)量的表，就要花時(shí)間研究建立優(yōu)秀的索引，或優(yōu)化查詢語(yǔ)句。

使用索引時(shí)，有一些技巧

1. 索引不會(huì)包含有NULL的列

只要列中包含有NULL值，都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此符合索引就是無(wú)效的。

      2.使用短索引

對(duì)串列進(jìn)行索引，如果可以就應(yīng)該指定一個(gè)前綴長(zhǎng)度。例如，如果有一個(gè)char(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi)，多數(shù)值是唯一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

     3.索引列排序

mysql一張表查詢只能用到一個(gè)索引。因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的。因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求的情況下不要使用排序操作，盡量不要包含多個(gè)列的排序，如果需要最好給這些列建復(fù)合索引。這一點(diǎn)是很多程序猿容易忽略的，如where子句的字段建了索引，排序的字段建了索引，但是分開建的，以為會(huì)走索引，其實(shí)這樣的話排序的字段不會(huì)使用索引的，除非建復(fù)合索引，切記。

     4.like語(yǔ)句操作

一般情況下不鼓勵(lì)使用like操作，如果非使用不可，注意正確的使用方式。like '%aaa%'不會(huì)使用索引，而like 'aaa%'可以使用索引。

    5.不要在列上進(jìn)行運(yùn)算

    6.不使用NOT IN 、<>、!=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的。

    7.索引要建立在經(jīng)常進(jìn)行select操作的字段上。

這是因?yàn)椋绻@些列很少用到，那么有無(wú)索引并不能明顯改變查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求。

    8.索引要建立在值比較唯一的字段上。

    9.對(duì)于那些定義為text、image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。因?yàn)檫@些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。

    10.在where和join中出現(xiàn)的列需要建立索引。

     11.where的查詢條件里有不等號(hào)(where column != ...),mysql將無(wú)法使用索引。

     12.如果where字句的查詢條件里使用了函數(shù)(如：where DAY(column)=...),mysql將無(wú)法使用索引。

     13.在join操作中(需要從多個(gè)數(shù)據(jù)表提取數(shù)據(jù)時(shí))，mysql只有在主鍵和外鍵的數(shù)據(jù)類型相同時(shí)才能使用索引，否則即使建立了索引也不會(huì)使用。這一點(diǎn)很容易忽略，切記，切記，切記!

     14.在進(jìn)行聯(lián)表查詢時(shí)，建立關(guān)聯(lián)的表的字段類型最好一樣且長(zhǎng)度一致，這樣能更好的發(fā)揮索引的作用。

      15.組合索引時(shí)切記此條約束：組合索引中有多個(gè)字段，其中一個(gè)字段是有范圍判斷，則需將此字段在最后面。如

ALTER TABLE USER_DEMO ADD INDEX name_age (NAME,AGE); 

因?yàn)閍ge會(huì)有范圍判斷，則建組合索引時(shí)將AGE字段放在后面。

       16.字符集字段比較，UTF8與UTF-BIN聯(lián)合查詢是不能走索引的。

如某張表的order_no字段類型為varchar(50),另一張表的order_no字段類型為varchar(50) COLLATE utf8_BIN。則此時(shí)聯(lián)合查詢時(shí)不能走索引的，切記。

即兩張表的字段類型如下：

`order_no` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '訂單號(hào)'; 
`order_no` varchar(50) NOT NULL DEFAULT '' COMMENT '訂單號(hào)'; 

       17.以下幾種情況不適合建索引：

• 表記錄太少
• 經(jīng)常插入、刪除、修改的表
• 數(shù)據(jù)重復(fù)且分布平均的表字段。如一個(gè)表有10萬(wàn)行記錄，其中字段column1只有A和B兩種值，且每個(gè)值的分布概率大約為50%，那么對(duì)這種表column1字段建索引一般不會(huì)提高數(shù)據(jù)庫(kù)的查詢速度。

       18.給表創(chuàng)建主鍵，對(duì)于沒有主鍵的表，在查詢和索引定義上有一定的影響。

       19.避免表字段為null，建議設(shè)置默認(rèn)值(如int類型設(shè)置默認(rèn)值為0)，這樣在索引查詢上，效率會(huì)高很多。

       20.關(guān)于order by的索引問(wèn)題重點(diǎn)說(shuō)下:

• 無(wú)條件查詢?nèi)绻挥衞rder by create_time，即便create_time上有索引，也不會(huì)使用到。

           因?yàn)閮?yōu)化器認(rèn)為走二級(jí)索引再去回表成本比全表掃描排序更高，所以選擇走權(quán)標(biāo)掃描。

• 無(wú)條件查詢但是order by create_time limit m，如果m值較小，是可以走索引的。

           因?yàn)閮?yōu)化器認(rèn)為根據(jù)索引有序性去回表查數(shù)據(jù)，然后得到m條數(shù)據(jù)，就可以終止循環(huán)，

           那么成本比全表掃描小，則選擇走二級(jí)索引。

           即便沒有二級(jí)索引，mysql針對(duì)order by limit也做了優(yōu)化，采用堆排序。

• order by排序分為file sort和index，index的效率更高。但以下情況不會(huì)使用index排序：

1. 檢查的行數(shù)過(guò)多，并且沒有使用覆蓋索引
2. 使用了多個(gè)索引，mysql一次只會(huì)采用一個(gè)索引
3. where和order by使用了不同的索引，與上一條類似
4. order by中加入了非索引列，且非索引列不在where中
5. 當(dāng)使用left join，使用右邊的表字段排序