MySQL入門(五) MySQL中的索引詳講

　　　　　　序言

　　　　　　　　　之前寫到MySQL對表的增刪改查(查詢最為重要)后，就感覺MySQL就差不多學完了，沒有想繼續學下去的心態了，原因可能是由于別人的影響，覺得對于MySQL來說，知道了一些復雜的查詢，就夠了，但是我認為，不管有沒有用，現在學著不懂的東西，說明就是自己薄弱的地方，多學才能比別人更強

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　--WH

一、什么是索引？為什么要建立索引？

　　　　　　　索引用于快速找出在某個列中有一特定值的行，不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大，查詢數據所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜索數據文件，而不必查看所有數據，那么將會節省很大一部分時間。

　　　　　　　例如：有一張person表，其中有2W條記錄，記錄著2W個人的信息。有一個Phone的字段記錄每個人的電話號碼，現在想要查詢出電話號碼為xxxx的人的信息。

　　　　　　　　　　如果沒有索引，那么將從表中第一條記錄一條條往下遍歷，直到找到該條信息為止。

　　　　　　　　　　如果有了索引，那么會將該Phone字段，通過一定的方法進行存儲，好讓查詢該字段上的信息時，能夠快速找到對應的數據，而不必在遍歷2W條數據了。其中MySQL中的索引的存儲類型有兩種：BTREE、HASH。 也就是用樹或者Hash值來存儲該字段，要知道其中詳細是如何查找的，就需要會算法的知識了。我們現在只需要知道索引的作用，功能是什么就行。

二、MySQL中索引的優點和缺點和使用原則

　　　　　　優點：

　　　　　　　2、所有的MySql列類型(字段類型)都可以被索引，也就是可以給任意字段設置索引

　　　　　　　3、大大加快數據的查詢速度

　　　　　　缺點：

　　　　　　　1、創建索引和維護索引要耗費時間，并且隨著數據量的增加所耗費的時間也會增加

　　　　　　　2、索引也需要占空間，我們知道數據表中的數據也會有最大上線設置的，如果我們有大量的索引，索引文件可能會比數據文件更快達到上線值

　　　　　　　3、當對表中的數據進行增加、刪除、修改時，索引也需要動態的維護，降低了數據的維護速度。

　　　　　　使用原則：

　　　　　　 通過上面說的優點和缺點，我們應該可以知道，并不是每個字段度設置索引就好，也不是索引越多越好，而是需要自己合理的使用。

　　　　　　　1、對經常更新的表就避免對其進行過多的索引，對經常用于查詢的字段應該創建索引，

　　　　　　　2、數據量小的表最好不要使用索引，因為由于數據較少，可能查詢全部數據花費的時間比遍歷索引的時間還要短，索引就可能不會產生優化效果。

　　　　　　　3、在一同值少的列上(字段上)不要建立索引，比如在學生表的"性別"字段上只有男，女兩個不同值。相反的，在一個字段上不同值較多可是建立索引。

　　　　　　上面說的只是很片面的一些東西，索引肯定還有很多別的優點或者缺點，還有使用原則，先基本上理解索引，然后等以后真正用到了，就會慢慢知道別的作用。注意，學習這張，很重要的一點就是必須先得知道索引是什么，索引是干嘛的，有什么作用，為什么要索引等等，如果不知道，就重復往上面看看寫的文字，好好理解一下。一個表中很夠創建多個索引，這些索引度會被存放到一個索引文件中(專門存放索引的地方)

三、索引的分類　　

　　　　　　　注意：索引是在存儲引擎中實現的，也就是說不同的存儲引擎，會使用不同的索引

　　　　　　　　　　　　MyISAM和InnoDB存儲引擎：只支持BTREE索引， 也就是說默認使用BTREE，不能夠更換

　　　　　　　　　　　　MEMORY/HEAP存儲引擎：支持HASH和BTREE索引

　　　　　　　1、索引我們分為四類來講 單列索引(普通索引，唯一索引，主鍵索引)、組合索引、全文索引、空間索引、

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

　　　　　　　　　　　　　1.1.1、普通索引：

　　　　　　　　　　　　　　　　　　MySQL中基本索引類型，沒有什么限制，允許在定義索引的列中插入重復值和空值，純粹為了查詢數據更快一點。

　　　　　　　　　　　　　1.1.2、唯一索引：

　　　　　　　　　　　　　　　　　　索引列中的值必須是唯一的，但是允許為空值，

　　　　　　　　　　　　　1.1.3、主鍵索引：

　　　　　　　　　　　　　　　　　　是一種特殊的唯一索引，不允許有空值。

　　　　　　　　　　1.2、組合索引

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

　　　　　　　　　　1.3、全文索引

　　　　　　　　　　　　　　　全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT類型字段上使用全文索引，介紹了要求，說說什么是全文索引，就是在一堆文字中，通過其中的某個關鍵字等，就能找到該字段所屬的記錄行，比如有"你是個大煞筆，二貨 ..." 通過大煞筆，可能就可以找到該條記錄。這里說的是可能，因為全文索引的使用涉及了很多細節，我們只需要知道這個大概意思，如果感興趣進一步深入使用它，那么看下面測試該索引時，會給出一個博文，供大家參考。

　　　　　　　　　　1.4、空間索引

　　　　　　　　　　　　　　　空間索引是對空間數據類型的字段建立的索引，MySQL中的空間數據類型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。

　　　　　　　　　　　　　　　在創建空間索引時，使用SPATIAL關鍵字。

　　　　　　　　　　　　　　　要求，引擎為MyISAM，創建空間索引的列，必須將其聲明為NOT NULL。具體細節看下面　　　

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

　　　　　　4.1、創建索引

　　　　　　　　　　4.1.1、創建表的時候創建索引

　　　　　　　　　　　　格式：CREATE TABLE 表名[字段名 數據類型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) 　　[ASC|DESC]

　　　　　　　　　　　　　　　|--------------------------------------| |-----------------------------------| |------------| |---------| |---------------| 　　|------------|

　　　　　　　　　　　　　　　　　　　　普通創建表語句　　　　　　　　設置什么樣的索引(唯一、全文等)　　索引關鍵字　 索引名字　對哪個字段設置索引　　對索引進行排序　

　　　　　　　　　　　　　　4.1.1.1、創建普通索引　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　　CREATE TABLE book　　　　　　　　　　　　　　　　　　　　CREATE TABLE book

　　　　　　　　　　　　　　　　(　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　(

　　　　　　　　　　　　　　　　　　bookid INT NOT NULL,　　　　　　　　　　　　　　　　　　bookid INT NOT NULL,

　　　　　　　　　　　　　　　　　　bookname VARCHAR(255) NOT NULL,　　　　　　　　　　 bookname VARCHAR(255) NOT NULL,

　　　　　　　　　　　　　　　　　　authors VARCHAR(255) NOT NULL,　　　　　　　　　　　 authors VARCHAR(255) NOT NULL,

　　　　　　　　　　　　　　　　　　info VARCHAR(255) NULL,　　　　　　　　　　　　　　　　info VARCHAR(255) NULL,

　　　　　　　　　　　　　　　　　　comment VARCHAR(255) NULL,　　　　　　　　　　　　　comment VARCHAR(255) NULL,　

　　　　　　　　　　　　　　　　　　year_publication YEAR NOT NULL,　　　　　　　　　　　　year_publication YEAR NOT NULL,

　　　　　　　　　　　　　　　　　　INDEX(year_publication)　　　　　　　　　　　　　　　　　KEY(year_publication)　

　　　　　　　　　　　　　　　　);　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　);

　　　　　　　　　　　　　　上面兩種方式創建度可以，通過這個例子可以對比一下格式，就差不多明白格式是什么意思了。

　　　　　　　　　　　　　　　　通過打印結果，我們在創建索引時沒寫索引名的話，會自動幫我們用字段名當作索引名。

　　　　　　　　　　　　　　　　測試：看是否使用了索引進行查詢。

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

　　　　　　　　　　　　　　　　　　解釋：雖然表中沒數據，但是有EXPLAIN關鍵字，用來查看索引是否正在被使用，并且輸出其使用的索引的信息。

　　　　　　　　　　　　　　　　　　　　id:　SELECT識別符。這是SELECT的查詢序列號,也就是一條語句中，該select是第幾次出現。在次語句中，select就只有一個，所以是1.

　　　　　　　　　　　　　　　　　　　　select_type:所使用的SELECT查詢類型，SIMPLE表示為簡單的SELECT，不實用UNION或子查詢，就為簡單的SELECT。也就是說在該SELECT查詢時會使用索引。其他取值，PRIMARY：最外面的SELECT.在擁有子查詢時，就會出現兩個以上的SELECT。UNION：union(兩張表連接)中的第二個或后面的select語句 SUBQUERY：在子查詢中，第二SELECT。

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

　　　　　　　　　　　　　　　　　　　　type：指定本數據表和其他數據表之間的關聯關系，該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯合。ref用于連接程序使用鍵的最左前綴或者是該鍵不是 primary key 或 unique索引（換句話說，就是連接程序無法根據鍵值只取得一條記錄）的情況。當根據鍵值只查詢到少數幾條匹配的記錄時，這就是一個不錯的連接類型。(注意，個人這里不是很理解，百度了很多資料，全是大白話，等以后用到了這類信息時，在回過頭來補充，這里不懂對后面的影響不大。)可能的取值有 system、const、eq_ref、index和All

　　　　　　　　　　　　　　　　　　　　possible_keys：MySQL在搜索數據記錄時可以選用的各個索引，該表中就只有一個索引，year_publication

　　　　　　　　　　　　　　　　　　　　key：實際選用的索引

　　　　　　　　　　　　　　　　　　　　key_len：顯示了mysql使用索引的長度(也就是使用的索引個數)，當 key 字段的值為 null時，索引的長度就是 null。注意，key_len的值可以告訴你在聯合索引中mysql會真正使用了哪些索引。這里就使用了1個索引，所以為1，

　　　　　　　　　　　　　　　　　　　　ref:給出關聯關系中另一個數據表中數據列的名字。常量（const），這里使用的是1990，就是常量。

　　　　　　　　　　　　　　　　　　　　rows：MySQL在執行這個查詢時預計會從這個數據表里讀出的數據行的個數。

　　　　　　　　　　　　　　　　　　　　extra：提供了與關聯操作有關的信息，沒有則什么都不寫。　　　

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

　　　　　　　　　　　　　4.1.1.2、創建唯一索引　　

　　　　　　　　　　　　　　　　CREATE TABLE t1

　　　　　　　　　　　　　　　　(

　　　　　　　　　　　　　　　　　　id INT NOT NULL,

　　　　　　　　　　　　　　　　　　name CHAR(30) NOT NULL,

　　　　　　　　　　　　　　　　　　UNIQUE INDEX UniqIdx(id)

　　　　　　　　　　　　　　　　);　　

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

　　　　　　　　　　　　　　　　SHOW CREATE TABLE t1\G;

　　　　　　　　　　　　　　　 要查看其中查詢時使用的索引，必須先往表中插入數據，然后在查詢數據，不然查找一個沒有的id值，是不會使用索引的。

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

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

　　　　　　　　　　　　　　　　可以看到，通過id查詢時，會使用唯一索引。并且還實驗了查詢一個沒有的id值，則不會使用索引，我覺得原因是所有的id應該會存儲到一個const tables中，到其中并沒有該id值，那么就沒有查找的必要了。　　　

　　　　　　　　　　　　4.1.1.3、創建主鍵索引

　　　　　　　　　　　　　　　　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;

　　　　　　　　　　　　　　　　　通過這個主鍵索引，我們就應該反應過來，其實我們以前聲明的主鍵約束，就是一個主鍵索引，只是之前我們沒學過，不知道而已。

　　　　　　　　　　　 4.1.1.4、創建單列索引　

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

　　　　　　　　　　　 4.1.1.5、創建組合索引

　　　　　　　　　　　　　　　　組合索引就是在多個字段上創建一個索引

　　　　　　　　　　　　　　　　創建一個表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;

解釋最左前綴

組合索引就是遵從了最左前綴，利用索引中最左邊的列集來匹配行，這樣的列集稱為最左前綴，不明白沒關系，舉幾個例子就明白了，例如，這里由id、name和age3個字段構成的索引，索引行中就按id/name/age的順序存放，索引可以索引下面字段組合(id，name，age)、(id，name)或者(id)。如果要查詢的字段不構成索引最左面的前綴，那么就不會是用索引，比如，age或者（name，age）組合就不會使用索引查詢

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

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

　　　　　　　　　　　　　　　　在t3表中，查詢(age，name)字段，這樣就不會使用索引查詢。來看看結果

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

　　　　　　　　　　　　4.1.1.6、創建全文索引

　　　　　　　　　　　　　　　　全文索引可以用于全文搜索，但只有MyISAM存儲引擎支持FULLTEXT索引，并且只為CHAR、VARCHAR和TEXT列服務。索引總是對整個列進行，不支持前綴索引，

　　　　　　　　　　　　　　　　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;

　　　　　　　　　　　　　　　使用一下什么叫做全文搜索。就是在很多文字中，通過關鍵字就能夠找到該記錄。

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

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

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

　　　　　　　　　　　　　　　　　　注意：在使用全文搜索時，需要借助MATCH函數，并且其全文搜索的限制比較多，比如只能通過MyISAM引擎，比如只能在CHAR,VARCHAR,TEXT上設置全文索引。比如搜索的關鍵字默認至少要4個字符，比如搜索的關鍵字太短就會被忽略掉。等等，如果你們在實驗的時候可能會實驗不出來。感興趣的同學可以看看這篇文章，全文搜索的使用

　　　　　　　　　　　　4.1.1.7、創建空間索引

　　　　　　　　　　　　　　　　空間索引也必須使用MyISAM引擎， 并且空間類型的字段必須為非空。 這個空間索引具體能干嘛我也不知道，可能跟游戲開發有關，可能跟別的東西有關，等遇到了自然就知道了，現在只要求能夠創建出來。

　　　　　　　　　　　　　　　　CREATE TABLE t5

　　　　　　　　　　　　　　　　(

　　　　　　　　　　　　　　　　　　g GEOMETRY NOT NULL,

　　　　　　　　　　　　　　　　　　SPATIAL INDEX spatIdx(g)

　　　　　　　　　　　　　　　　) ENGINE = MyISAM;

　　　　　　　　　　　　　　　　SHOW CREATE TABLE t5\G;

　　　　　　　　　　4.1.2、在已經存在的表上創建索引

　　　　　　　　　　　　　　格式：ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

　　　　　　　　　　　　　　　　　有了上面的基礎，這里就不用過多陳述了。

　　　　　　　　　　　　　　命令一：SHOW INDEX FROM 表名\G　　

　　　　　　　　　　　　　　　　　　查看一張表中所創建的索引

　　　　　　　　　　　　　　　　　　SHOW INDEX FROM book\G;

　　　　　　　　　　　　　　　　　　挑重點講，我們需要了解的就5個，用紅顏色標記了的，如果想深入了解，可以去查查該方面的資料，我個人覺得，這些等以后實際工作中遇到了在做詳細的了解把。

　　　　　　　　　　　　　　　　　　Table:創建索引的表

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

　　　　　　　　　　　　　　　　　　Key_name：索引名稱

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

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

　　　　　　　　　　　　　　　　　　Sub_part：表示索引的長度

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

　　　　　　　　　　　　　　　　　　Index_type：表示索引類型

　　　　　　　　　　　　　4.1.2.1、為表添加索引

　　　　　　　　　　　　　　　　就拿上面的book表來說。本來已經有了一個year_publication，現在我們為該表在加一個普通索引

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

　　　　　　　　　　　　　　　　看輸出結果，就能知道，添加索引成功了。

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

　　　　　　　　　　　　　4.1.2.2、使用CREATE INDEX創建索引。

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

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

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

　　　　　　　　　　　　　　　　CREATE INDEX BkBookNameIdx ON book(bookname);

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

　　　　　　　　　　　　　　　　　　解釋：第一條截圖沒截到，因為圖太大了，這里只要看到有我們新加進去的索引就證明成功了。。其他索引也是一樣的創建。

　　　　　　4.2、刪除索引

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

　　　　　　　　　　　　　　添加的兩種方式

　　　　　　　　　　　　　　　　1在創建表的同時如何創建索引，

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

　　　　　　　　　　　　　　查詢的方式

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

　　　　　　　　　　　　　　現在來說說如何給表刪除索引的兩種操作。

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

　　　　　　　　　　　　　　　　　　很簡單的語句，現在通過一個例子來看看，還是對book表進行操作，刪除我們剛才為其添加的索引。

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

　　　　　　　　　　　　　　　　　　ALTER TABLE book DROP INDEX BkBookNameIdx;

　　　　　　　　　　　　　　　　　　SHOW INDEX FROM book\G;　　//在查看book表中的索引，就會發現BkBookNameIdx這個索引已經不在了

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

　　　　　　　　　　　　　　　　　　　　　　刪除book表中名為BkNameIdx的索引

　　　　　　　　　　　　　　　　　　　　　　DROP INDEX BkNameIdx ON book;

　　　　　　　　　　　　　　　　　　　　　　SHOW INDEX FROM book\G;

五、總結

　　　　　　MySQL的索引到這里差不多就講完了，總結一下我們到目前為止應該知道哪些東西

　　　　　　　　1、索引是干嘛的？為什么要有索引？

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

　　　　　　　　2、索引的分類

　　　　　　　　3、索引的操作

　　　　　　　　　　　　給表中創建索引，添加索引，刪除索引，刪除索引