mysql索引總結--mysql 索引類型以及建立的詳細介紹

關於MySQL索引的好處，如果正確合理設計並且使用索引的MySQL是一輛蘭博基尼的話，那麼沒有設計和使用索引的MySQL就是一個人力三輪車。對於沒有索引的表，單表查詢可能幾十萬數據就是瓶頸，而通常大型網站單日就可能會產生幾十萬甚至幾百萬的數據，沒有索引查詢會變的非常緩慢。還是以WordPress來說，其多個資料表都會對經常被查詢的欄位添加索引，例如wp_comments表中針對5個欄位設計了BTREE索引。

一個簡單的對比測試

以我去年測試的數據作為一個簡單示例，20多個數據源隨機生成200萬條數據，平均每條數據源都重複大概10萬次，表格結構比較簡單，只包含一個自增ID，一個char類型，一個text類型和一個int類型，單表2G大小，使用MyIASM引擎。開始測試未新增任何索引。

執行下面的SQL語句：

mysql> SELECT id,FROM_UNIXTIME(time) FROM article WHERE a.title='测试标题'

查詢需要的時間非常恐怖的，如果加上聯合查詢和其他一些約束條件，資料庫會瘋狂的消耗內存，並且會影響前端程式的執行。這時為title欄位加入一個BTREE索引：

mysql> ALTER TABLE article ADD INDEX index_article_title ON title(200);

再次執行上述查詢語句，其比較非常明顯：

MySQL索引的概念

#索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含著對資料表裡所有記錄的參考指標。更通俗的說，資料庫索引好比是一本書前面的目錄，能加快資料庫的查詢速度。上述SQL語句，在沒有索引的情況下，資料庫會遍歷全部200個資料後選擇符合條件的；而有了對應的索引之後，資料庫會直接在索引中尋找符合條件的選項。如果我們把SQL語句換成“SELECT * FROM article WHERE id=2000000”，那麼你是希望資料庫按照順序讀取完200萬行資料以後給你結果還是直接在索引中定位呢？上面的兩張圖片鮮明的用時對比已經給出了答案（註：一般資料庫預設都會為主鍵產生索引）。

索引分為叢集索引和非叢集索引兩種，叢集索引是依照資料存放的實體位置為順序的，而非叢集索引就不一樣了；叢集索引能提高多行檢索的速度，而非叢集索引對於單行的檢索很快。

MySQL索引的型別

1. 普通索引

這是最基本的索引，它沒有任何限制，例如上文中為title欄位所建立的索引就是一個普通索引，MyIASM中預設的BTREE類型的索引，也是我們大多數情況下所使用的索引。

01    –直接创建索引    
02    CREATE INDEX index_name ON table(column(length))    
03    –修改表结构的方式添加索引    
04    ALTER TABLE table_name ADD INDEX index_name ON (column(length))    
05    –创建表的时候同时创建索引    
06    CREATE TABLE `table` (    
07    `id` int(11) NOT NULL AUTO_INCREMENT ,    
08    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
09    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
10    `time` int(10) NULL DEFAULT NULL ,    
11    PRIMARY KEY (`id`),    
12    INDEX index_name (title(length))    
13    )    
14    –删除索引    
15    DROP INDEX index_name ON table

2. 唯一索引

與普通索引類似，不同的是：索引列的值必須唯一，但允許有空值（注意和主鍵不同）。如果是組合索引，則列值的組合必須唯一，建立方法和普通索引類似。

01    –创建唯一索引    
02    CREATE UNIQUE INDEX indexName ON table(column(length))    
03    –修改表结构    
04    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))    
05    –创建表的时候直接指定    
06    CREATE TABLE `table` (    
07    `id` int(11) NOT NULL AUTO_INCREMENT ,    
08    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
09    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
10    `time` int(10) NULL DEFAULT NULL ,    
11    PRIMARY KEY (`id`),    
12    UNIQUE indexName (title(length))    
13    );

3. 全文索引（FULLTEXT）

#MySQL從3.23.23版開始支援全文索引和全文檢索，FULLTEXT索引僅可用於MyISAM 表；他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創建，或是隨後使用ALTER TABLE 或CREATE INDEX被添加。 ////對於較大的資料集，將你的資料輸入一個沒有FULLTEXT索引的表中，然後建立索引，其速度比把資料輸入現有FULLTEXT索引的速度更為快。不過切記對於大容量的資料表，產生全文索引是一個非常消耗時間非常消耗硬碟空間的做法。

01    –创建表的适合添加全文索引    
02    CREATE TABLE `table` (    
03    `id` int(11) NOT NULL AUTO_INCREMENT ,    
04    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
05    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
06    `time` int(10) NULL DEFAULT NULL ,    
07    PRIMARY KEY (`id`),    
08    FULLTEXT (content)    
09    );    
10    –修改表结构添加全文索引    
11    ALTER TABLE article ADD FULLTEXT index_content(content)    
12    –直接创建索引    
13    CREATE FULLTEXT INDEX index_content ON article(content)

4. 單列索引、多列索引

#多個單列索引與單一多列索引的查詢效果不同，因為執行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最嚴格的索引。

5. 組合索引（最左字首）

平常用的SQL查詢語句一般都有比較多的限制條件，所以為了進一步榨取MySQL的效率，就要考慮建立組合索引。例如上表中針對title和time建立一個組合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立這樣的組合索引，其實是相當於分別建立了下面兩組組合索引：

–title,time

–title

為什麼沒有time這樣的組合索引呢？這是因為MySQL組合索引「最左前綴」的結果。簡單的理解就是只從最左邊的開始組合。並不是只要包含這兩列的查詢都會用到該組合索引，如下面的幾個SQL所示：

1    –使用到上面的索引    
2    SELECT * FROM article WHREE title='测试' AND time=1234567890;    
3    SELECT * FROM article WHREE utitle='测试';    
4    –不使用上面的索引    
5    SELECT * FROM article WHREE time=1234567890;

 以上就是mysql索引總結--mysql 索引類型以及創建的詳細介紹的內容，更多相關內容請關注PHP中文網（www.php.cn）！