在實(shí)際開發(fā)中使用數(shù)據(jù)庫時(shí)，難免會(huì)遇到一些大表數(shù)據(jù)，對(duì)這些數(shù)據(jù)進(jìn)行查詢時(shí)，有時(shí)候SQL會(huì)查詢得特別慢，這時(shí)候，有經(jīng)驗(yàn)的老師傅會(huì)告訴你，你看一下哪幾個(gè)字段查的多，加一個(gè)索引就好了。
從事綿陽電信機(jī)房，服務(wù)器租用，云主機(jī)，網(wǎng)絡(luò)空間，域名注冊，CDN，網(wǎng)絡(luò)代維等服務(wù)。
那么，怎么合理地建立索引呢？這里分享一下我的一些經(jīng)驗(yàn)，如有不妥之處，歡迎批評(píng)指正。
1、不要盲目建立索引 ， 先分析再創(chuàng)建
索引雖然能大幅度提升我們的查詢性能，但也要知道，在你進(jìn)行增刪改時(shí)，索引樹也要同樣地進(jìn)行維護(hù)。所以，索引不是越多越好，而是按需建立。最好是在一整塊模塊開發(fā)完成后，分析一下，去針對(duì)大多數(shù)的查詢，建立聯(lián)合索引。
2、使用聯(lián)合索引盡量覆蓋多的條件
這是說在一個(gè)慢sql里假如有五個(gè)where ，一個(gè) order by ，那么我們的聯(lián)合索引盡量覆蓋到這五個(gè)查詢條件，如果有必要，order by 也覆蓋上 。
3、小基數(shù)字段不需要索引
這個(gè)意思是，如果一張表里某個(gè)字段的值只有那么幾個(gè)，那么你針對(duì)這個(gè)字段建立的索引其實(shí)沒什么意義，比如說，一個(gè)性別字段就兩種結(jié)果，你建了索引，排序也沒什么意思（也就是索引里把男女給分開了）
所以說，索引盡量選擇基數(shù)大的數(shù)據(jù)去建立，能最大化地利用索引
4、長字符串可以使用前綴索引
我們建立索引的字段盡量選擇字段類型較小的，比如一個(gè)varchar(20)和varchar(256)的，我們在20的上面建立的索引和在256上就有明顯的差距（字符串那么長排序也不好排呀，唉）。
當(dāng)然，如果一定是要對(duì)varchar(256)建立索引,我們可以選擇里面的前20個(gè)字符放在索引樹里（這里的20不絕對(duì)，選擇能盡量分辨數(shù)據(jù)的最小字符字段設(shè)計(jì)），類似這樣KEY index(name(20),age,job) ，索引只會(huì)對(duì)name的前20個(gè)字符進(jìn)行搜索，但前綴索引無法適用于order by 和 group by。
5、對(duì)排序字段設(shè)計(jì)索引的優(yōu)先級(jí)低
如果一個(gè)SQL里我們出現(xiàn)了范圍查找，后邊又跟著一個(gè)排序字段，那么我們優(yōu)先給范圍查找的字段設(shè)置索引，而不是優(yōu)先排序。
6、如果出現(xiàn)慢SQL，可以設(shè)計(jì)一個(gè)只針對(duì)該條SQL的聯(lián)合索引。
不過慢SQL的優(yōu)化，需要一步步去進(jìn)行分析，可以先用explain查看SQL語句的分析結(jié)果，再針對(duì)結(jié)果去做相應(yīng)的改進(jìn)。explain的東西我們下次再講。
PS：在 select 語句之前增加 explain 關(guān)鍵字，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記，執(zhí)行查詢會(huì)返回執(zhí)行計(jì)劃的信息，而不是 執(zhí)行這條SQL。
CREATE
[UNIQUE]
INDEX
ON
(字段
[ASC|DESC]);
UNIQUE
--確保所有的索引列中的值都是可以區(qū)分的。
[ASC|DESC]
--在列上按指定排序創(chuàng)建索引。
(創(chuàng)建索引的準(zhǔn)則：
1.如果表里有幾百行記錄則可以對(duì)其創(chuàng)建索引(表里的記錄行數(shù)越多索引的效果就越明顯)。
2.不要試圖對(duì)表創(chuàng)建兩個(gè)或三個(gè)以上的索引。
3.為頻繁使用的行創(chuàng)建索引。
)
示例
create
index
i_1
on
emp(empno
asc);
1、普通索引
這是最基本的索引，它沒有任何限制。
2、唯一索引
它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。
3、主鍵索引
它是一種特殊的唯一索引，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引。
4、聚簇索引
聚簇索引的索引順序就是數(shù)據(jù)存儲(chǔ)的物理存儲(chǔ)順序，這樣能保證索引值相近的元組所存儲(chǔ)的物理位置也相近。
5、全文索引（FULLTEXT）
全文索引只能創(chuàng)建在數(shù)據(jù)類型為VARCHAR或TEXT的列上，建立全文索引后，能夠在建立了全文索引的列上進(jìn)行全文查找。全文索引只能在MyISAM存儲(chǔ)引擎的表中創(chuàng)建。
實(shí)際工作使用中，索引可以建立在單一列上，稱為單列索引，也可以建立在多個(gè)列上，稱為組合索引。
mysql的索引類型及使用索引時(shí)的注意事項(xiàng)有：
一、普通索引。這是最基本的索引，它沒有任何限制。它有以下幾種創(chuàng)建方式：
1、創(chuàng)建索引
代碼如下:
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR類型，length可以小于字段實(shí)際長度；如果是BLOB和TEXT類型，必須指定 length，下同。
2、修改表結(jié)構(gòu)
代碼如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 創(chuàng)建表的時(shí)候直接指定
CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? INDEX [indexName] (username(length))?? );?
-- 刪除索引的語法：
DROP INDEX [indexName] ON mytable;
二、唯一索引。它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：
代碼如下:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表結(jié)構(gòu)
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 創(chuàng)建表的時(shí)候直接指定
CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? UNIQUE [indexName] (username(length))?? );
三、主鍵索引。它是一種特殊的唯一索引，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引：
代碼如下:
CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? PRIMARY KEY(ID)?? );
當(dāng)然也可以用 ALTER 命令。記住：一個(gè)表只能有一個(gè)主鍵。
四、組合索引。為了形象地對(duì)比單列索引和組合索引，為表添加多個(gè)字段：
代碼如下:
CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? city VARCHAR(50) NOT NULL,?? age INT NOT NULL? );
為了進(jìn)一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個(gè)索引里：
代碼如下:
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]
建表時(shí)，usernname長度為 16，這里用 10。這是因?yàn)橐话闱闆r下名字的長度不會(huì)超過10，這樣會(huì)加速索引查詢速度，還會(huì)減少索引文件的大小，提高INSERT的更新速度。
如果分別在 usernname，city，age上建立單列索引，讓該表有3個(gè)單列索引，查詢時(shí)和上述的組合索引效率也會(huì)大不一樣，遠(yuǎn)遠(yuǎn)低于我們的組合索引。雖然此時(shí)有了三個(gè)索引，但MySQL只能用到其中的那個(gè)它認(rèn)為似乎是最有效率的單列索引。
建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面三組組合索引：usernname,city,age?? usernname,city?? usernname? 為什么沒有 city，age這樣的組合索引呢？這是因?yàn)镸ySQL組合索引“最左前綴”的結(jié)果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會(huì)用到該組合索引，下面的幾個(gè)SQL就會(huì)用到這個(gè)組合索引：
[code]
SELECT * FROM mytable WHREE username="admin" AND city="鄭州"? SELECT * FROM mytable WHREE username="admin"
重慶分公司
重慶分公司
