MySQL-索引詳解

索引是用來快速檢索出具有特定值的記錄。如果沒有索引，資料庫就必須從第一條記錄開始進行全表掃描，直到找出相關的行。數據越多，檢索的代價就越高，檢索時如果表的列存在索引，那麼MySQL就能快速到達指定位置去搜索數據文件，而不必查看所有數據。

概述

索引依托於存儲引擎的實現，因此，每種存儲引擎的索引都不一定完全相同，並且每種存儲引擎也不一定支持所有索引類型。所有存儲引擎支持每個表至少16個索引，總索引長度至少為256位元組。大多數存儲引擎有更高的額限制。

MySQL中索引的存儲類型有兩種：BTREE和HASH，具體和表的存儲引擎相關；

MyISAM和InnoDB存儲引擎只支持BTREE索引，MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。

優點

• 加快數據的查詢速度

• 唯一索引，可以保證資料庫表中每一行數據的唯一性

• 在實現數據的參考完整性方面，可以加速表和表之間的連接

• 在使用分組和排序子句進行數據查詢時，也可以顯著減少查詢中分組和排序的時間.

缺點

• 占用磁碟空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數據文件更快達到最大文件尺寸（合理運用，問題不大）

• 損耗性能（添加、修改、刪除） 索引需要動態地維護

分類

普通索引和唯一索引

• 普通索引： 資料庫中的基本索引類型，允許在定義索引的列中插入重覆值和空值

• 唯一索引：索引列的值必須唯一，但允許有空值，主鍵索引是一種特殊的唯一索引，不允許有空值（比如自增ID）

單列索引和組合索引

• 單列索引： 即一個索引只包含單個列，一個表可以有多個單列索引

• 組合索引： 指在表的多個欄位組合上創建的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用

全文索引

• 全文索引： 類型為FULLTEXT，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重覆值和空值。全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創建，MySQL中只有MyISAM存儲引擎支持全文索引

設計原則

索引設計不合理或者缺少索引都會對資料庫和應用程式的性能造成障礙，高效的索引對於獲得良好的性能非常重要。

註意事項

1. 索引並非越多越好，一個表中如有大量的索引，不僅占用磁碟空間，而且會影響INSERT、DELETE、UPDATE等語句的性能，因為當表中的數據更改的同時，索引也會進行調整和更新

2. 避免對經常更新的表設計過多的索引，並且索引中的列儘可能要少，而對經常用於查詢的欄位應該創建索引，但要避免添加不必要的欄位

3. 數據量小的表最好不要使用索引，由於數據較少，查詢花費的時間可能比遍歷索引時間還要短，索引可能不會產生優化效果

4. 在條件表達式中經常用到的不同值較多的列上建立索引，在不同值較少的列上不要建立索引，比如性別欄位只有男和女，就沒必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度

5. 當唯一性是某種數據本身的特征時，指定唯一索引。使用唯一索引需能確保定義的列的數據完整性，以提高查詢速度

6. 在頻繁排序或分組（即group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引

使用

使用 CREATE TABLE 創建表的時候，除了可以定義列的數據類型，還可以定義主鍵約束、外鍵約束或者唯一性約束，而不論創建哪種約束，在定義約束的同時相當於在指定列上創建了一個索引。

創建表時創建索引的基本語法如下：

CREATE TABLE table_name[col_name data_type][UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]

釋義

1. UNIQUE、FULLTEXT和SPATIAL為可選參數，分別表示唯一索引、全文索引和空間索引

2. INDEX和KEY為同義詞，二者作用相同，用來指定創建索引

3. col_name為需要創建索引的欄位列，該列必須從數據表中該定義的多個列中選擇

4. index_name為指定索引的名稱，為可選參數，如果不指定則MySQL預設col_name為索引值

5. length為可選參數，表示索引的長度，只有字元串類型的欄位才能指定索引長度

6. ASC或DESC指定升序或者降序的索引值存儲

普通索引

-- 這句作用是,如果 customer1 存在就刪除DROP TABLE IF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` bigint(20) NOT NULL COMMENT '客戶ID', `customer_name` varchar(30) DEFAULT NULL COMMENT '客戶姓名', INDEX `idx_customer_id` (`customer_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客戶表';

測試

-- 查看當前表的索引情況SHOW INDEX FROM customer1;-- 使用 EXPLAIN 分析 SQL語句 是否使用了索引EXPLAIN SELECT * FROM customer1 WHERE customer_id = 1;

釋義

EXPLAIN 語法下章會詳細講解,本章重心是索引

• select_type： 指定所使用的SELECT查詢類型，這裡值為SIMPLE，表示簡單的SELECT，不使用UNION或者子查詢。其他取值有PRIMARY、UNION、SUBQUERY、等

• table： 指定資料庫讀取的數據表的名字，它們按照被讀取的先後順序排列

• type： 指定了本數據表與其他數據表之間的關聯關係，其它取值有system、const、eq_ref、ref、range、index和All

• possible_keys： MySQL在搜索數據記錄時可選用的各個索引

• key： MySQL使用的實際索引

• key_len： 給出了索引按位元組計算的長度，key_len數值越小，表示越快

• ref： 提供了關聯關係中另外一個數據表裡的數據列的名字

• rows： 指MySQL執行查詢時預計從當前數據表中讀出的數據行數

• Extra： 提供了與關聯操作有關的信息

SHOW INDEX FROM 語法

• table： 表示創建索引的表

• Non_unique： 表示索引不是一個唯一索引，1表示非唯一索引，0表示唯一索引

• Key_name： 表示索引的名稱

• Seq_in_index： 表示該欄位在索引中的位置，單列索引改值該值為1，組合索引為每個欄位在索引中定義的順序

• Column_name： 表示定義索引的列欄位

• Sub_part： 表示索引的長度

• Null： 表示該欄位是否能為空值

• Index_type： 表示索引類型

當 possible_keys 與 key 都為 idx_customer_id，說明查詢時使用了索引

唯一索引

單列索引是在數據表中的某一個欄位上創建的索引，一個表中可以創建多個單列索引，前面兩個例子中創建的索引都是單列索引，比如：

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客戶ID', `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客戶姓名', UNIQUE INDEX `idx_customer_id` (`customer_id`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客戶表';

這樣就代表在表的customer_id欄位上創建了一個名為idx_customer_id的唯一索引

組合索引

組合索引是在多個欄位上創建一個索引，比如：

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客戶ID', `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客戶姓名', INDEX `idx_group_customer` (`customer_id`,`customer_name`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客戶表';SHOW INDEX FROM customer1;

這就為customer_id、customer_name兩個欄位成功創建了一個名為idx_group_customer的組合索引，通過SHOW INDEX FROM customer1; 將會看到兩條記錄（附圖）

全文索引

全文索引可以對全文進行搜索，只有MyISAM存儲引擎支持全文索引，並且只為CHAR、VARCHAR和TEXT列，索引總是對整個列進行，不支持局部索引，比如：

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客戶ID', `customer_name` VARCHAR (255) DEFAULT NULL COMMENT '客戶姓名', FULLTEXT INDEX `idx_fulltext_customer_name` (`customer_name`)) ENGINE = MyISAM DEFAULT CHARSET = utf8mb4 COMMENT = '客戶表';SHOW INDEX FROM customer1;

因為預設的存儲引擎為InnoDB，而全文索引只支持MyISAM，所以這裡創建表的時候要手動指定一下引擎。

看到這麼創建，就在info欄位上成功建立了一個名為idx_fulltext_customer_name的FULLTEXT全文索引，全文索引非常適合大型資料庫，而對於小的數據集，它的用處可能比較小

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

在已經存在的表上創建索引，可以使用ALTER TABLE語句或者CREATE INDEX語句，所以，分別講解一下如何使用ALTER TABLE和CREATE INDEX語句在已知的表欄位上創建索引。

ALTER TABLE 語法

ALTER TABLE創建索引的基本語法為：

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL][INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

普通索引

ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));

意思是查詢的時候，只需要檢索前面50個字元。這裡專門提一下，對字元串類型的欄位進行索引，如果可以儘可能的指定一個首碼長度，例如，一個CHAR(255)的列，如果在前10個或者前30個字元內，多數值是唯一的，則不需要對整個列進行索引，短索引不僅可以提高查詢速度而且可以節省磁碟空間、減少I/O操作。

唯一索引

ALTER TABLE customer1 ADD UNIQUE INDEX `idx_customer_id` (`customer_id`);

組合索引

ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);

CREATE TABLE 語法

CREATE INDEX語句可以在已經存在的表上添加索引，MySQL中CREATE INDEX被映射到一個ALTER TABLE語句上，基本語法結構為：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]

看到和ALTER INDEX語句的語法基本一樣，下麵把 customer1 表刪除了再創建，所有欄位都沒有索引，用CREATE INDEX語句創建一次索引：

CREATE INDEX idx_customer_id ON customer1(`customer_id`);CREATE UNIQUE INDEX idx_customer_id ON customer1(`customer_id`);CREATE INDEX idx_group_customer ON customer1(`customer_id`,`customer_name`);

刪除索引

最後一項工作就是刪除索引了，可以使用ALTER TABLE和DROP INDEX刪除索引。

ALTER TABLE 語法

ALTER TABLE的基本語法為：

ALTER TABLE table_name DROP EXISTS index_name;ALTER TABLE table_name DROP INDEX IF EXISTS index_name;

建議大家使用第二條

DROP INDEX 語法

DROP INDEX的基本語法為：

DROP INDEX index_name ON table_nameDROP INDEX IF EXISTS index_name ON table_name

建議大家使用第二條

註意一個細節，刪除表中的列時，如果要刪除的列為整個索引的組成部分，則該列也會從索引中刪除；如果組成索引的所有列都被刪除，則整個索引將被刪除

架構群：697579751