MySQL索引

一、 索引簡介

1.1 索引含義

索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可提高資料庫中特定資料的查詢速度，

索引的含義：是一個單獨的、存盤在磁盤 上的資料結構，它們包含著對資料表里所有記錄的參考指標，

所有MySQL列型別都可以被索引，

1.2 索引和引擎

索引是在存盤引擎中實作的，每種存盤引擎的索引都 不一定完全相同，并且每種存盤引擎也不一定支持所有索引型別，

根據存盤引擎定義每個表的最大索引數和最大索引長度，所有存盤引擎支持每個表 至少16個索引，總索引長度至少為 256位元組，大多數存盤引擎有更高的限制，

MySQL 中索引的存盤型別有兩種：BTree 和 Hash，具體和表的存盤引擎相關；

MyISAM 和 InnoDB 存盤引擎只支持BTree 索引；

MEMORY/HEAP 存盤引擎可以支持 Hash和 BTree 索引，

查看user表的索引詳情：

show INDEX from user;

可以看到使用的索引型別是 BTree，

1.3 索引的優點

1. 通過創建唯一索引，可以保證資料庫表中每一行的資料唯一性，
2. 大大加快資料的查詢速度，也是創建索引的最主要原因，
3. 在實作資料的參考完整性方面，可以加速表和表之間的鏈接，
4. 在使用 分組 和 排序 字句進行資料查詢時，也可以顯著減少查詢中 分組和排序 的時間，

1.4 索引的缺點

1. 創建索引 和 維護索引 耗費時間，并且隨著資料量增加所耗費的時間也會增加，
2. 索引需要占磁盤空間，如果有大量索引，索引檔案可能比資料檔案更快達到最大檔案尺寸，
3. 當對表中的資料進行 增加、洗掉和修改 的時候，索引也要動態地維護，降低了資料的維護速度，

二、 索引的分類

2.1 普通索引和唯一索引

普通索引(Normal)：MySQL 中的 基本索引型別，允許在定義索引的列中插入 重復值 和 空值；

唯一索引(Unique)：索引列中的值必須唯一，但允許有空值，如果是組合索引，則列值的組合必須唯一，

主鍵索引：一種特殊的唯一索引，不允許有空值，

2.2 單列索引和組合索引

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

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

2.3 全文索引

全文索引(Full Text)：在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值，

全文索引可以在 CHAR、VARCHAR 或者 TEXT 型別的列上創建，

MySQL 中只有 MyISAM 存盤引擎支持全文索引，

2.4 空間索引

空間索引：是對空間資料型別的欄位建立的索引，

MySQL中的空間資料型別有四種：GEOMETRY、POINT、LINESTRING 和 POLYGON，

MySQL 使用 SPATIAL 關鍵字進行擴展，使得能夠用于創建正規索引類似的語法創建空間索引，空間索引的列，必須宣告為 NOT NULL，空間索引只能在 MyISAM 的表中創建，

2.5 索引的設計原則

適合：

1. 頻繁作為 where 條件的欄位
2. 關聯欄位可以建索引，比如外鍵，
3. 頻繁進行排序或分組，(即 order by 或 group by)，排序列有多個，可以建立組合索引，

不適合：

1. 并非越多越好
2. where條件用不到的欄位
3. 頻繁更新的欄位(insert, delete, update)
4. 資料值發布比較均勻的的欄位，例如男女、真偽值，
5. 表的資料可以確地行數的，且資料量少的，(遍歷索引時間 可能大于 查詢時間)

三、 創建索引

可以在創建表的時候創建索引，語法格式如下：

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

3.1 創建普通索引

3.1.1 創建表時創建索引

有 book 表，在year_publication 欄位建立普通索引，INDEX(year_publication)(后面創建重復的創建陳述句不再給出)

CREATE TABLE book(
    bookid				INT  			  NOT NULL,
    bookname			VARCHAR(255)      NOT NULL,
    authors	    		VARCHAR(255)      NULL,
    info				VARCHAR(255)      NULL,
    comment	    		VARCHAR(255)      NULL,
    year_publication  	YEAR              NOT NULL,
    INDEX(year_publication)
);

通過

SHOW CREATE TABLE book;

查看創建陳述句，可以看到

KEY `year_publication` (`year_publication`)

其中索引名稱 year_publication 由MySQL默認給出，

查看是否使用了索引：(EXPLAIN)

查詢全表，

EXPLAIN SELECT* FROM book;

結果：

有條件的查詢

EXPLAIN SELECT* FROM book where year_publication = 1990;

我們可以推測，查詢全表的肯定是沒有索引的，有條件的查詢是否用了索引呢，

結果：

通過上面兩張圖，可以對比得出，year_publication 這個索引在被帶條件的查詢時，確實被使用到了，

來看引數表示的意思：

1. select_type:指定所使用的查詢型別，SIMPLE，表示只是普通的查詢，不是UNION或子查詢，

   其他可能值：PRIMARY、UNION、SUBQUERY

2. table：表名，

3. type：指定了本資料表 與 其他資料表之間的關聯關系，

   可能取值：system、const、eq_ref、ref、range、index 和 ALL，

4. possible_keys: MySQL在搜索資料記錄時可選用的各個索引，

5. key：MySQL 實際選用的索引

6. key_len :索引按 位元組 計算的長度， key_len 數值越小，表示越快

7. ref：給出了關聯關系中另一個資料表里的資料列的名字，

8. rows：MySQL在執行這個查詢時預計會從這個表里讀出的資料行的個數，

9. extra：提供了與關聯操作有關的資訊，

3.1.2 已存在表上創建索引

ALTER:

在 bookname欄位創建索引

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

CREATE:

CREATE INDEX BkNameIdx ON book(bookname);

3.2 創建唯一索引

3.2.1 創建表時創建索引

創建一個表t1，唯一索引 UNIQUE INDEX UniqIdx(id);

CREATE TABLE t1(
id    INT   		 NOT NULL,
name  CHAR(30)   NOT NULL,
UNIQUE INDEX  UniqIdx(id)
);

通過

SHOW CREATE TABLE t1;

查看，可以看到索引資訊，

UNIQUE KEY `UniqIdx`  (`id`)

3.2.2 已存在表上創建索引

ALTER:

bookId欄位：

ALTER TABLE book ADD UNIQUE INDEX UniqidIdx(bookId);

CREATE:

CREATE UNIQUE INDEX UniqidIdx ON book(bookId);

3.3 創建單列索引

3.3.1 創建表時創建索引

單列索引：在表的某一個欄位創建的索引，一個表可以有多個單列索引，前面兩個例子都是，

創建表t2，INDEX SingleIdx(name(20))，

CREATE TABLE t2(
id    INT   		 NOT NULL,
name  CHAR(30)   NOT NULL,
INDEX SingleIdx(name(20))
);

通過

SHOW CREATE TABLE t2;

查看，可以看到索引資訊，

KEY `SingleIdx` (`name`(20))

3.3.2 已存在表上創建索引

ALTER:

book表欄位上創建單列索引,comment 欄位：

ALTER TABLE book ADD INDEX BkcmtIdx(comment(50));

CREATE:

CREATE INDEX BkcmtIdx ON book(comment(50));

3.4 創建組合索引

3.4.1 創建表時創建索引

組合索引：在多個欄位創建一個索引

創建表 t3： INDEX MutiIdx(id, name, age(100))

CREATE TABLE t3(
id    INT   		 NOT NULL,
name  CHAR(30)   NOT NULL,
age   INT        NOT NULL,
info  VARCHAR(200),
INDEX MultiIdx(id,name,age(100))
);

同樣可以查看索引，不在贅述，

3.4.2 已存在表上創建索引

ALTER:

在 book表的 authors 和 **info **欄位上建立組合索引

ALTER TABLE book ADD INDEX BkAuAndInfoIdx(authors(30),info(50));

CREATE:

CREATE INDEX BkAuAndInfoIdx ON book (authors(20), info(50));

3.5 創建全文索引

3.5.1 創建表時創建索引

全文索引：FULLTEXT，可以用于全文搜索，

只有MyISAM 存盤引擎支持FULLTEXT索引，并且只為CHAR、VARCHAR 和 TEXT列創建索引，

索引總是對整個列進行，不支持區域（前綴）索引，

創建表 t4；FULLTEXT INDEX FullTxtIdx(info)，需要指定引擎，ENGINE=MyISAM;

CREATE TABLE t4(
id    INT   		 NOT NULL,
name  CHAR(30)   NOT NULL,
age   INT        NOT NULL,
info  VARCHAR(200),
FULLTEXT INDEX FullTxtIdx(info)
)ENGINE=MyISAM;

3.5.2 已存在表上創建索引

ALTER:

ALTER TABLE t4 ADD FULLTEXT INDEX inforFTIdx(info);

CREATE:

CREATE FULLTEXT INDEX ON t4(info);

3.6 創建空間引擎

3.6.1 創建表時創建索引

空間引擎必須在MyISAM型別的表中創建，且空間型別的欄位必須為非空，

CREATE TABLE t5(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
)ENGINE=MyISAM

3.6.2 已存在表上創建索引

ALTER:

ALTER TABLE t5 ADD SPATIAL spatIdx(g);

CREATE:

CREATE SPATIAL INDEX spatIdx ON t5(g);

四、 洗掉索引

ALTER TABLE形式

ALTER TABLE table_name DROP INDEX index_name;

DROP INDEX形式：

DROP INDEX index_name ON table_name;

參考

《MySQL5.7從入門到精通》

標籤：MySQL

上一篇：mysql中的事務

下一篇：B+Tree索引為什么可以支持千萬級別資料量的查找——講講mysql索引的底層資料結構