一矢空、什么是索引？為什么要建立索引禀横？

索引用于快速找出在某個列中有一特定值的行屁药，不使用索引，MySQL必須從第一條記錄開始讀完整個表柏锄，直到找出相關(guān)的行酿箭，表越大，查詢數(shù)據(jù)所花費(fèi)的時間就越多趾娃，如果表中查詢的列有一個索引缭嫡，MySQL能夠快速到達(dá)一個位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)抬闷，那么將會節(jié)省很大一部分時間妇蛀。

例如：有一張person表，其中有2W條記錄，記錄著2W個人的信息评架。有一個Phone的字段記錄每個人的電話號碼眷茁，現(xiàn)在想要查詢出電話號碼為xxxx的人的信息。如果沒有索引纵诞，那么將從表中第一條記錄一條條往下遍歷蔼卡，直到找到該條信息為止。如果有了索引挣磨，那么會將該P(yáng)hone字段，通過一定的方法進(jìn)行存儲荤懂，好讓查詢該字段上的信息時茁裙，能夠快速找到對應(yīng)的數(shù)據(jù)，而不必在遍歷2W條數(shù)據(jù)了节仿。其中MySQL中的索引的存儲類型有兩種：BTREE晤锥、HASH。 也就是用樹或者Hash值來存儲該字段廊宪，要知道其中詳細(xì)是如何查找的矾瘾，就需要會算法的知識了。我們現(xiàn)在只需要知道索引的作用箭启，功能是什么就行壕翩。

二、MySQL中索引的優(yōu)點(diǎn)和缺點(diǎn)和使用原則

　　　優(yōu)點(diǎn)：

　　　1傅寡、所有的MySql列類型(字段類型)都可以被索引放妈，也就是可以給任意字段設(shè)置索引

　　　2、大大加快數(shù)據(jù)的查詢速度

　　　缺點(diǎn)：

　　　1荐操、創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間芜抒，并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時間也會增加

　　　2、索引也需要占空間托启，我們知道數(shù)據(jù)表中的數(shù)據(jù)也會有最大上線設(shè)置的宅倒，如果我們有大量的索引，索引文件可能會比數(shù)據(jù)文件更快達(dá)到上線值

　　　3屯耸、當(dāng)對表中的數(shù)據(jù)進(jìn)行增加拐迁、刪除、修改時疗绣，索引也需要動態(tài)的維護(hù)唠亚，降低了數(shù)據(jù)的維護(hù)速度。

　　　使用原則：

　　　通過上面說的優(yōu)點(diǎn)和缺點(diǎn)持痰，我們應(yīng)該可以知道灶搜，并不是每個字段度設(shè)置索引就好，也不是索引越多越好，而是需要自己合理的使用割卖。

　　　1前酿、對經(jīng)常更新的表就避免對其進(jìn)行過多的索引，對經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引鹏溯，

　　　2罢维、數(shù)據(jù)量小的表最好不要使用索引，因為由于數(shù)據(jù)較少丙挽，可能查詢?nèi)繑?shù)據(jù)花費(fèi)的時間比遍歷索引的時間還要短肺孵，索引就可能不會產(chǎn)生優(yōu)化效果。

　　　3颜阐、在一同值少的列上(字段上)不要建立索引平窘，比如在學(xué)生表的"性別"字段上只有男，女兩個不同值凳怨。相反的瑰艘，在一個字段上不同值較多可以建立索引。

　　　上面說的只是很片面的一些東西肤舞，索引肯定還有很多別的優(yōu)點(diǎn)或者缺點(diǎn)紫新，還有使用原則，先基本上理解索引李剖，然后等以后真正用到了芒率，就會慢慢知道別的作用。注意篙顺，學(xué)習(xí)這張敲董，很重要的一點(diǎn)就是必須先得知道索引是什么，索引是干嘛的慰安，有什么作用腋寨，為什么要索引等等，如果不知道化焕，就重復(fù)往上面看看寫的文字萄窜，好好理解一下。一個表中很夠創(chuàng)建多個索引撒桨，這些索引度會被存放到一個索引文件中(專門存放索引的地方)

三查刻、索引的分類　　

　　　注意：索引是在存儲引擎中實現(xiàn)的，也就是說不同的存儲引擎凤类，會使用不同的索引

　　　MyISAM和InnoDB存儲引擎：只支持BTREE索引穗泵， 也就是說默認(rèn)使用BTREE，不能夠更換MEMORY/HEAP存儲引擎：支持HASH和BTREE索引

　? ? ? ?1谜疤、索引我們分為四類來講 單列索引(普通索引佃延，唯一索引现诀，主鍵索引)、組合索引履肃、全文索引仔沿、空間索引、

　　? ? ? ?1.1尺棋、單列索引：一個索引只包含單個列封锉，但一個表中可以有多個單列索引。 這里不要搞混淆了膘螟。

　　　　? ?1.1.1成福、普通索引：MySQL中基本索引類型，沒有什么限制荆残，允許在定義索引的列中插入重復(fù)值和空值奴艾，純粹為了查詢數(shù)據(jù)更快一點(diǎn)。

　　　　? ?1.1.2脊阴、唯一索引：索引列中的值必須是唯一的，但是允許為空值蚯瞧，

　　　　? ?1.1.3嘿期、主鍵索引：是一種特殊的唯一索引，不允許有空值埋合。

　　? ? ? 1.2备徐、組合索引

　　在表中的多個字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時甚颂，索引才會被使用蜜猾，使用組合索引時遵循最左前綴集合。這個如果還不明白振诬，等后面舉例講解時在細(xì)說

　　1.3蹭睡、全文索引

　　全文索引，只有在MyISAM引擎上才能使用赶么，只能在CHAR,VARCHAR,TEXT類型字段上使用全文索引肩豁，介紹了要求，說說什么是全文索引辫呻，就是在一堆文字中清钥，通過其中的某個關(guān)鍵字等，就能找到該字段所屬的記錄行放闺，比如有"你是個靚仔祟昭，靚女 ..." 通過靚仔，可能就可以找到該條記錄怖侦。這里說的是可能篡悟，因為全文索引的使用涉及了很多細(xì)節(jié)谜叹，我們只需要知道這個大概意思，如果感興趣進(jìn)一步深入使用它恰力，那么看下面測試該索引時叉谜，會給出一個博文，供大家參考踩萎。

　? 1.4停局、空間索引

　　空間索引是對空間數(shù)據(jù)類型的字段建立的索引，MySQL中的空間數(shù)據(jù)類型有四種香府，GEOMETRY董栽、POINT、LINESTRING企孩、POLYGON锭碳。在創(chuàng)建空間索引時，使用SPATIAL關(guān)鍵字勿璃。要求擒抛，引擎為MyISAM，創(chuàng)建空間索引的列补疑，必須將其聲明為NOT NULL歧沪。具體細(xì)節(jié)看下面

四、索引操作(創(chuàng)建和刪除)

　　　4.1莲组、創(chuàng)建索引

　　　4.1.1诊胞、創(chuàng)建表的時候創(chuàng)建索引

　　　4.1.1.1、創(chuàng)建普通索引

　　　上面兩種方式創(chuàng)建都可以锹杈，通過這個例子可以對比一下格式撵孤，就差不多明白格式是什么意思了。

　　通過打印結(jié)果竭望，我們在創(chuàng)建索引時沒寫索引名的話邪码，會自動幫我們用字段名當(dāng)作索引名。

　　測試：看是否使用了索引進(jìn)行查詢咬清。

　　EXPLAIN SELECT * FROM book WHERE year_publication = 1990\G;

　　解釋：雖然表中沒數(shù)據(jù)霞扬，但是有EXPLAIN關(guān)鍵字，用來查看索引是否正在被使用枫振，并且輸出其使用的索引的信息喻圃。

　　id:　SELECT識別符。這是SELECT的查詢序列號,也就是一條語句中粪滤，該select是第幾次出現(xiàn)斧拍。在次語句中，select就只有一個杖小，所以是1.

　　select_type:所使用的SELECT查詢類型肆汹，SIMPLE表示為簡單的SELECT愚墓，不實用UNION或子查詢，就為簡單的SELECT昂勉。也就是說在該SELECT查詢時會使用索引浪册。其他取值，PRIMARY：最外面的SELECT.在擁有子查詢時岗照，就會出現(xiàn)兩個以上的SELECT村象。UNION：union(兩張表連接)中的第二個或后面的select語句? SUBQUERY：在子查詢中，第二SELECT攒至。

　　table：數(shù)據(jù)表的名字厚者。他們按被讀取的先后順序排列，這里因為只查詢一張表迫吐，所以只顯示book

　　type：指定本數(shù)據(jù)表和其他數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系库菲，該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯(lián)合。ref用于連接程序使用鍵的最左前綴或者是該鍵不是 primary key 或 unique索引（換句話說志膀，就是連接程序無法根據(jù)鍵值只取得一條記錄）的情況熙宇。當(dāng)根據(jù)鍵值只查詢到少數(shù)幾條匹配的記錄時，這就是一個不錯的連接類型溉浙。(注意烫止，個人這里不是很理解，百度了很多資料放航，全是大白話烈拒，等以后用到了這類信息時圆裕，在回過頭來補(bǔ)充广鳍，這里不懂對后面的影響不大。)可能的取值有 system吓妆、const赊时、eq_ref、index和All

　　　possible_keys：MySQL在搜索數(shù)據(jù)記錄時可以選用的各個索引行拢，該表中就只有一個索引祖秒，year_publication

　　　key：實際選用的索引

　　　key_len：顯示了mysql使用索引的長度(也就是使用的索引個數(shù))，當(dāng) key 字段的值為 null時舟奠，索引的長度就是 null竭缝。注意，key_len的值可以告訴你在聯(lián)合索引中mysql會真正使用了哪些索引沼瘫。這里就使用了1個索引抬纸，所以為1，

　　　ref:給出關(guān)聯(lián)關(guān)系中另一個數(shù)據(jù)表中數(shù)據(jù)列的名字耿戚。常量（const）湿故，這里使用的是1990阿趁，就是常量。

　　? rows：MySQL在執(zhí)行這個查詢時預(yù)計會從這個數(shù)據(jù)表里讀出的數(shù)據(jù)行的個數(shù)坛猪。

　　　extra：提供了與關(guān)聯(lián)操作有關(guān)的信息脖阵，沒有則什么都不寫。

　　 上面的一大堆東西能看懂多少看多少墅茉，我們最主要的是看possible_keys和key 這兩個屬性命黔，上面顯示了key為year_publication。說明使用了索引躁锁。

　　　4.1.1.2纷铣、創(chuàng)建唯一索引

　　　　　　　　　　　　　　　　CREATE TABLE t1

　　　　　　　　　　　　　　　　(

　　　　　　　　　　　　　　　　　　id INT NOT NULL,

　　　　　　　　　　　　　　　　　　name CHAR(30) NOT NULL,

　　　　　　　　　　　　　　　　　　UNIQUE INDEX UniqIdx(id)

　　　　　　　　　　　　　　　　);

　　　　　解釋：對id字段使用了索引，并且索引名字為UniqIdx战转。

　　　　　SHOW CREATE TABLE t1\G;　　　　　　　　　　　　　　　　　　　　?

?　　　要查看其中查詢時使用的索引搜立，必須先往表中插入數(shù)據(jù)，然后在查詢數(shù)據(jù)槐秧，不然查找一個沒有的id值啄踊，是不會使用索引的。

　　　　　INSERT INTO t1 VALUES(1,'xxx');

　　　　　EXPLAIN SELECT * FROM t1 WHERE id = 1\G;

　? 可以看到刁标，通過id查詢時颠通，會使用唯一索引。并且還實驗了查詢一個沒有的id值膀懈，則不會使用索引顿锰，我覺得原因是所有的id應(yīng)該會存儲到一個const tables中，到其中并沒有該id值启搂，那么就沒有查找的必要了硼控。

　4.1.1.3、創(chuàng)建主鍵索引

　　　　　　　　　　　　　　　　CREATE TABLE t2

　　　　　　　　　　　　　　　　(

　　　　　　　　　　　　　　　　　　id INT NOT NULL,

　　　　　　　　　　　　　　　　　　name CHAR(10),

　　　　　　　　　　　　　　　　　　PRIMARY KEY(id)

　　　　　　　　　　　　　　　　);

　　　　　　　　　　　　　　　　INSERT INTO t2 VALUES(1,'QQQ');

　　　　　　　　　　　　　　　　EXPLAIN SELECT * FROM t2 WHERE id = 1\G;

　　通過這個主鍵索引，我們就應(yīng)該反應(yīng)過來，其實我們以前聲明的主鍵約束鞍泉，就是一個主鍵索引，只是之前我們沒學(xué)過熏版，不知道而已。

　　4.1.1.4捍掺、創(chuàng)建單列索引

? ? ? 這個其實就不用在說了撼短，前面幾個就是單列索引。

?　　4.1.1.5挺勿、創(chuàng)建組合索引

　　組合索引就是在多個字段上創(chuàng)建一個索引曲横，創(chuàng)建一個表t3，在表中的id满钟、name和age字段上建立組合索引

　　　　　　　　　　　　　　　　CREATE TABLE t3

　　　　　　　　　　　　　　　　(

　　　　　　　　　　　　　　　　　　id INT NOT NULL,

　　　　　　　　　　　　　　　　　　name CHAR(30) NOT NULL,

　　　　　　　　　　　　　　　　　　age INT NOT NULL,

　　　　　　　　　　　　　　　　　　info VARCHAR(255),

　　　　　　　　　　　　　　　　　　INDEX MultiIdx(id,name,age)

　　　　　　　　　　　　　　　　);

　　　　SHOW CREATE t3\G;

　　解釋最左前綴

組合索引就是遵從了最左前綴胜榔，利用索引中最左邊的列集來匹配行胳喷，這樣的列集稱為最左前綴，不明白沒關(guān)系夭织，舉幾個例子就明白了吭露，例如，這里由id尊惰、name和age3個字段構(gòu)成的索引讲竿，索引行中就按id/name/age的順序存放，索引可以索引下面字段組合(id弄屡，name题禀，age)、(id膀捷，name)或者(id)迈嘹。如果要查詢的字段不構(gòu)成索引最左面的前綴，那么就不會是用索引全庸，比如秀仲，age或者（name，age）組合就不會使用索引查詢

在t3表中壶笼，查詢id和name字段

　　EXPLAIN SELECT * FROM t3 WHERE id = 1 AND name = 'joe'\G;

　在t3表中神僵，查詢(age，name)字段覆劈，這樣就不會使用索引查詢保礼。來看看結(jié)果

　EXPLAIN SELECT * FROM t3 WHERE age = 3 AND name = 'bob'\G;

4.1.1.6、創(chuàng)建全文索引

　? 全文索引可以用于全文搜索责语，但只有MyISAM存儲引擎支持FULLTEXT索引炮障，并且只為CHAR、VARCHAR和TEXT列服務(wù)鹦筹。索引總是對整個列進(jìn)行铝阐，不支持前綴索引址貌，

　　　　　　　　　　　　　　　　CREATE TABLE t4

　　　　　　　　　　　　　　　　(

　　　　　　　　　　　　　　　　　　id? INT NOT NULL,

　　　　　　　　　　　　　　　　　　name CHAR(30) NOT NULL,

　　　　　　　　　　　　　　　　　　age INT NOT NULL,

　　　　　　　　　　　　　　　　　　info VARCHAR(255),

　　　　　　　　　　　　　　　　　　FULLTEXT INDEX FullTxtIdx(info)

　　　　　　　　　　　　　　　　)ENGINE=MyISAM;

　　　　　　　　　　　　　　　　SHOW CREATE TABLE t4\G;

　使用一下什么叫做全文搜索铐拐。就是在很多文字中，通過關(guān)鍵字就能夠找到該記錄练对。

　INSERT INTO t4 VALUES(8,'AAA',3,'text is so good遍蟋，hei，my name is bob'),(9,'BBB',4,'my name isgorlr');

　SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');

　EXPLAIN?SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');　　　　　　　　　　　　　　　　　　　[if !vml]

　　注意：在使用全文搜索時螟凭，需要借助MATCH函數(shù)虚青，并且其全文搜索的限制比較多，比如只能通過MyISAM引擎螺男，比如只能在CHAR,VARCHAR,TEXT上設(shè)置全文索引棒厘。比如搜索的關(guān)鍵字默認(rèn)至少要4個字符纵穿，比如搜索的關(guān)鍵字太短就會被忽略掉。等等奢人，如果你們在實驗的時候可能會實驗不出來谓媒。感興趣的同學(xué)可以看看這篇文章，全文搜索的使用

　　4.1.1.7何乎、創(chuàng)建空間索引

　　空間索引也必須使用MyISAM引擎句惯， 并且空間類型的字段必須為非空。 這個空間索引具體能干嘛我也不知道支救，可能跟游戲開發(fā)有關(guān)抢野，可能跟別的東西有關(guān)，等遇到了自然就知道了各墨，現(xiàn)在只要求能夠創(chuàng)建出來指孤。

　　　　　　　　　　　　　　　　CREATE TABLE t5

　　　　　　　　　　　　　　　　(

　　　　　　　　　　　　　　　　　　g GEOMETRY NOT NULL,

　　　　　　　　　　　　　　　　　　SPATIAL INDEX spatIdx(g)

　　　　　　　　　　　　　　　　) ENGINE = MyISAM;

　　　　　　　　　　　　　　　　SHOW CREATE TABLE t5\G;

　　4.1.2、在已經(jīng)存在的表上創(chuàng)建索引

　　格式：ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL]

[INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

　　　　有了上面的基礎(chǔ)贬堵，這里就不用過多陳述了邓厕。

　　　　命令一：SHOW INDEX FROM 表名\G

　　　　查看一張表中所創(chuàng)建的索引

　　　SHOW INDEX FROM book\G;

　　挑重點(diǎn)講，我們需要了解的就5個扁瓢，用紅顏色標(biāo)記了的详恼，如果想深入了解，可以去查查該方面的資料引几，我個人覺得昧互，這些等以后實際工作中遇到了在做詳細(xì)的了解把。

　　　Table:創(chuàng)建索引的表

　　　Non_unique：表示索引非唯一伟桅，1代表 非唯一索引敞掘， 0代表 唯一索引，意思就是該索引是不是唯一索引

　　　Key_name：索引名稱

　　Seq_in_index 表示該字段在索引中的位置楣铁，單列索引的話該值為1玖雁，組合索引為每個字段在索引定義中的順序(這個只需要知道單列索引該值就為1，組合索引為別的)

　　Column_name：表示定義索引的列字段

　　Sub_part：表示索引的長度

　　Null：表示該字段是否能為空值

　　Index_type：表示索引類型

　　4.1.2.1盖腕、為表添加索引

　　就拿上面的book表來說赫冬。本來已經(jīng)有了一個year_publication，現(xiàn)在我們?yōu)樵摫碓诩右粋€普通索引

　　ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

　　看輸出結(jié)果溃列，就能知道劲厌，添加索引成功了。

　　這里只是拿普通索引做個例子听隐，添加其他索引也是一樣的补鼻。依葫蘆畫瓢而已。這里就不一一做講解了。

　　4.1.2.2风范、使用CREATE INDEX創(chuàng)建索引咨跌。

　　格式：CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名稱 ON 表名(創(chuàng)建索引的字段名[length])[ASC|DESC]

　　解釋：其實就是換湯不換藥，格式改變了一下而已硼婿，做的事情跟上面完全一樣虑润，做一個例子。

　　在為book表增加一個普通索引加酵，字段為authors拳喻。

　　CREATE INDEX BkBookNameIdx ON book(bookname);

SHOW INDEX FROM book\G;　　//查看book表中的索引

解釋：第一條截圖沒截到，因為圖太大了猪腕，這里只要看到有我們新加進(jìn)去的索引就證明成功了冗澈。。其他索引也是一樣的創(chuàng)建陋葡。

　4.2亚亲、刪除索引

?　　　前面講了對一張表中索引的添加，查詢的方法腐缤。

　　　添加的兩種方式

　　1在創(chuàng)建表的同時如何創(chuàng)建索引捌归，

　　2在創(chuàng)建了表之后如何給表添加索引的兩種方式，

　　 查詢的方式

　?? SHOW INDEX FROM表名\G岭粤；　　\G只是讓輸出的格式更好看

　　現(xiàn)在來說說如何給表刪除索引的兩種操作惜索。

　　格式一：ALTER TABLE 表名 DROP INDEX 索引名。

　　很簡單的語句剃浇，現(xiàn)在通過一個例子來看看巾兆，還是對book表進(jìn)行操作，刪除我們剛才為其添加的索引虎囚。

　　1角塑、刪除book表中的名稱為BkBookNameIdx的索引。

　　? ALTER TABLE book DROPINDEX?BkBookNameIdx;

SHOW INDEX FROM book\G;　　//在查看book表中的索引淘讥，就會發(fā)現(xiàn)BkBookNameIdx這個索引已經(jīng)不在了

?格式二：DROP INDEX 索引名 ON 表名圃伶；

?刪除book表中名為BkNameIdx的索引

DROP INDEX?BkNameIdx ON book;

SHOW INDEX FROM book\G;

五、總結(jié)

　　　MySQL的索引到這里差不多就講完了蒲列，總結(jié)一下我們到目前為止應(yīng)該知道哪些東西

　　　1窒朋、索引是干嘛的？為什么要有索引嫉嘀？

　　　　這個很重要炼邀，需要自己理解一下魄揉，不懂就看頂部的講解

　　　2剪侮、索引的分類

　　　3、索引的操作

　　　　 給表中創(chuàng)建索引，添加索引瓣俯，刪除索引杰标，刪除索引