MySQL資料庫應用總結（八）—MySQL資料庫索引的操作

長文預警，多圖預警...

------------------------------------------------------------------------------

SQL語法預覽：

創建表的時候創建索引：【create table 表名(欄位名稱 數據類型,unique|fulltext|spatial index 索引名(索引欄位名(索引長度)|…));】

在已有表上增加索引：【alter table 表名 add unique|fulltext|spatial index 索引名(索引欄位名(索引長度)|…);】

在已有表上創建索引：【create unique|fulltext|spatial index 索引名on表名(索引欄位名(索引長度)|…);】

刪除索引：【alter table 表名drop index 索引名;】

刪除索引：【drop index索引名on 表名;】

詳解：

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

1. 索引的含義

索引是一個單獨的、存儲在磁碟上的資料庫結構，包含對數據表裡所有記錄的引用指針。 使用索引可以快速找出在某個或多個列中有一定值的行，MySQL中所有的列類型都可以被索引，對相關列使用索引是提高查詢造作速度的最佳途徑。

例如：假設某資料庫有50000條記錄，現在要執行這樣一個查詢：select * from table where

num=20000。如果沒有索引，必須遍歷整個表，直到num等於20000的行被找到為止；如果在num列上創建索引，MySQL不需要逐一進行查找，直接在索引裡面尋找20000，就可以得知這一行的位置。因此，提高了查詢的速度。

索引的優缺點

2. 索引的分類

MySQL資料庫中的索引分一下幾類：

3. 創建索引原則

（1）索引並非越多越好

多了站磁碟空間，也影響一些語句執行速度。

（2）避免對經常更新的表做過多索引

對於經常要查詢的欄位創建索引。

（3）數據量小的表最好不要使用索引

數據量小的時候，遍曆數據的時間較短，創建索引與否對查詢效率影響不大時，可以不建立索引。

（4）在不同值少的列上不要建立索引

枚舉數量少的欄位值，可以不建立索引。例如性別欄位值只有兩個，則無需建立索引。

（5）數據唯一時指定唯一索引

使用唯一索引能保證列的數據完整性。

（6）在頻繁進行排序或分組的列上建立組合索引

即在頻繁進行group by或 order by的操作時，如果待排列的列有多個，則建立組合索引。

MySQL支持多種方法在單個或多個列上創建索引：在創建表的定義語句create table…index(欄位名)中指定索引列，使用修改表的語句alter table在已經存在的表中用create index語句創建索引列。

1. 創建表的時候創建索引

a) 創建普通索引

語法結構：create table 表名(欄位名,數據類型 列級別約束條件…index(欄位名));

案例：創建資料庫db_demo3，在該資料庫中定義數據表tb_book1，包含欄位圖書編號、圖書名稱、作者、出版社、出版時間和圖書信息，創建索引為出版時間，SQL語句如下：

命令語句：

create database db_demo3;

use db_demo3;

create table tb_book1(bookid int(11) primary key not null,bookname varchar(255) not null,authors varchar(255) not null,press varchar(255), year_publication year not null,info varchar(255),index(year_publication));

執行結果：

由結果看到book1表上的year_publication欄位成功建立索引，其索引名稱是MySQL自動添加的。使用explain語句可以查看索引是否正在使用：

Explain語句輸出結果的各行解釋如下：

• select_type行：查詢類型，簡單查詢 。
• table行：資料庫讀取數據表的名稱。
• type行：指本資料庫表與其他數據表之間的關聯關係，可取值有system、const、eq_ref、ref、range、index和all。
• possible_keys行：給出MySQL在搜素數據記錄時渴望選用的各個索引。
• key行：MySQL實際選用的索引
• key_len行：給出索引按位元組計算的長度，值越小，則查詢速度越快。
• ref行：給出關聯關係中另一個數據表裡數據列的名稱。
• rows行：MySQL從表裡讀出數據行的個數。
• extra行：提供與關聯有關的信息。

可以看到，possible_keys和key的值都為year_publication，說明查詢時使用了索引。

b) 創建唯一索引

語法結構：create table 表名(欄位名,數據類型 列級別約束條件…unique index 索引名(欄位名));

案例：創建數據表tb_index1，包含欄位name和id，在id欄位上創建唯一索引，SQL語句如下：

命令語句：create table tb_index1(id int(11) primary key not null,name varchar(25) not null, unique index UniqIdx(id));

執行結果：

c) 創建單列索引

語法結構：create table 表名(欄位名,數據類型 列級別約束條件…index 索引名(欄位名(索引長度)));

案例：創建數據表tb_index2，包含欄位name和id，在name欄位上創建單列索引，SQL語句如下：

命令語句：create table tb_index2(id int(11) primary key not null,name varchar(25) not null, index SingleIdx(name(20)));

執行結果：

d) 創建組合索引

組合索引是在多個欄位上創建的索引。

語法結構：create table 表名(欄位名,數據類型 列級別約束條件…index 索引名(欄位名1,欄位名2,…,欄位名n));

案例：創建數據表tb_index3，包含欄位id、name、info和age欄位，在id、name和age欄位上創建組合索引，SQL語句如下：

命令語句：create table tb_index3(id int(11) not null,name char(30) not null,age int not null,info varchar(255),index MultiIdx(id,name,age));

執行結果：

e) 創建全文索引

FULLTEXT全文索引可以用於全文搜索。只有MyISAM存儲引擎支持FULLTEXT索引，並且只能在數據類型為char、varchar和text的列上建立全文索引。

語法結構：create table 表名(欄位名,數據類型 列級別約束條件…fulltext index 索引名(欄位名))ENGINE=MyISAM;

案例：創建數據表tb_index4，包含欄位id、name、info和age欄位，在info欄位上創建全文索引，SQL語句如下：

命令語句：create table tb_index4(id int(11) not null,name char(30) not null,age int(11) not null,info varchar(255), fulltext index MultiIdx(info)) ENGINE=MyISAM;

執行結果：

f) 創建空間索引

空間索引必須在MyISAM類型的表中創建，且空間類型的欄位不能為空。

語法結構：create table 表名(欄位名,數據類型 列級別約束條件…spatial index 索引名(欄位名))ENGINE=MyISAM;

案例：創建數據表tb_index5，包含欄位id、name、info和age欄位，在info欄位上創建空間索引，SQL語句如下：

命令語句：create table tb_index5(jh geometry not null, spatial index SpatIdx(jh)) ENGINE=MyISAM;

執行結果：

2. 在已有表上創建索引

在已有欄位的表中創建索引，可以使用alter table語句或者create index語句。

a) 使用alter table … add index…語法創建索引

i. 創建普通索引

與創建表時創建索引的語法不同的是，這裡使用了alter table 語句和add關鍵字，add表示向表中增加索引。

語法結構：alter table 表名add index 索引名(欄位名(索引長度));

增加索引之前，先用show index語句查看指定表中已經創建的索引：

語法結構：show index from表名 G;

上面主要參數各行解釋如下：

• table行：查看的當前數據表的名稱。
• Non_unique行：索引非唯一。1代表真表示不是唯一索引，0表示假表示是唯一索引。
• Key_name行：主鍵，也是索引。
• Seq_in_index行：欄位在索引中的位置，單列索引該值為1，組合欄位為每個欄位在索引定義中的順序。
• Column_name行：當前索引的欄位名。
• Cardinality行：索引基數。
• Sub_part行：表示索引長度。
• Packed行：索引包。
• Null行：表示該欄位是否能為空值。
• Index_type行：表示索引類型。
• Comment行：表示注釋。

可以看到Key_name的值都為PRIMARY和year_publication（未定義索引名默認為索引欄位名），說明查有兩個索引。

下面給表中增加一個普通索引。

案例：在tb_book1表中的欄位bookname欄位上增加索引名為BKNameIdx的普通索引，SQL語句如下：

命令語句：alter table tb_book1 add index BKNameIdx(bookname(30));

執行結果：

可以看到，現在表中又新增一個索引，即通過alter table語句添加的名稱為BKNameIdx的索引，該索引為非唯一索引，長度為30。

ii. 創建唯一索引語法結構：alter table 表名add unique index 索引名(欄位名);

案例：在數據表tb_book1欄位bookid上建立名為UniqidIdx的唯一索引，SQL語句如下：

命令語句：alter table tb_book1 add unique index UniqidIdx(bookid);

執行結果：

iii. 創建單列索引

語法結構：alter table 表名add index 索引名(欄位名(索引長度));

案例：在數據表tb_book1欄位press上建立名為SigBKIdx的單列索引，SQL語句如下：

命令語句：alter table tb_book1 add index SigBKIdx(press);

執行結果：

iv. 創建組合索引

語法結構：alter table 表名add index 索引名(欄位名1(索引長度),欄位名2(索引長度)…);

案例：在數據表tb_book1欄位authors和info上建立名為ZHIdx的組合索引，SQL語句如下：

命令語句：alter table tb_book1 add index ZHIdx(authors(20),info(50));

執行結果：

v. 創建全文索引

語法結構：alter table 表名add fulltext index 索引名(欄位名);

案例：創建表tb_index6，在數據表tb_index6欄位info上建立名為infoFTIdx的全文索引，SQL語句如下：

先創建表：

命令語句：create table tb_index6(id int not null,info char(255)) ENGINE=MyISAM;

alter table tb_index6 add fulltext index infoFTIdx(info);

執行結果：

vi. 創建空間索引

語法結構：alter table 表名add spatial index 索引名(欄位名);

案例：創建表tb_index7，在數據表tb_index7欄位jh上建立名為spatIdx的空間索引，SQL語句如下：

先創建表：

命令語句：create table tb_index7(jh geometry not null) ENGINE=MyISAM;

alter table tb_index7 add spatial index spatIdx(jh);

執行結果：

b) 使用create index …語法創建索引

i. 創建普通索引

這裡使用了create index語句向表中增加索引。先創建一個新表tb_book2,方便案例師範，因為同一欄位不能同時創建多個索引。

命令語句： create table tb_book2(bookid int(11) not null,bookname varchar(255) not null,authors varchar(255) not null,press varchar(255),year_publication year not null,info varchar(255));

語法結構：create index 索引名on 表名(欄位名(索引長度));

案例：在tb_book2表中的欄位bookname欄位上增加索引名為BKNameIdx的普通索引，SQL語句如下：

命令語句：create index BKNameIdx on tb_book2(bookname(30));

執行結果：

可以看到，create index語句成功創建名稱為BKNameIdx的索引，該索引為非唯一索引，長度為30。

ii. 創建唯一索引

語法結構：create unique index 索引名on 表名(欄位名);

案例：在數據表tb_book2欄位bookid上建立名為UniqidIdx的唯一索引，SQL語句如下：

命令語句：create unique index UniqidIdx on tb_book2(bookid);

執行結果：

iii. 創建單列索引

語法結構：create index 索引名 on 表名(欄位名(索引長度));

案例：在數據表tb_book2欄位press上建立名為SigIdx的單列索引，SQL語句如下：

命令語句：create index SigIdx on tb_book2(press);

執行結果：

iv. 創建組合索引

語法結構：create index 索引名 on 表名(欄位名1(索引長度),欄位名2(索引長度)…);

案例：在數據表tb_book2欄位authors和info上建立名為ZHIdx的組合索引，SQL語句如下：

命令語句：create index ZHIdx on tb_book2(authors(20),info(50));

執行結果：

v. 創建全文索引

語法結構：create fulltext index 索引名 on 表明(欄位名);

案例：創建表tb_index8，在數據表tb_index8欄位info上建立名為infoFTIdx的全文索引，SQL語句如下：

先創建表：

命令語句：create table tb_index8(id int not null,info char(255)) ENGINE=MyISAM;

create fulltext index infoFTIdx on tb_index8(info);

執行結果：

vi. 創建空間索引

語法結構：create spatial index 索引名on表名(欄位名);

案例：創建表tb_index9，在數據表tb_index9欄位jh上建立名為spatIdx的空間索引，SQL語句如下：

先創建表：

命令語句：create table tb_index9(jh geometry not null) ENGINE=MyISAM;

create spatial index spatIdx on tb_index9(jh);

執行結果：

三、 刪除索引

在MySQL中，刪除索引使用alter table或者drop index語句，兩者可實現相同的功能。

1.使用alter table語句刪除索引

語法結構：alter table 表名 drop index索引名;

案例：刪除數據表tb_book2欄位bookid上名為UniqidIdx的唯一索引，SQL語句如下：

首先查看tb_book2表是否有名為UniqiIdx的索引。

命令語句： show create table tb_book2 G;

alter table tb_book2 drop index UniqidIdx;

執行結果：

2.使用drop index語句刪除索引

語法結構：drop index 索引名 on 表名;

案例：刪除數據表tb_book2欄位bookname上名為的普通索引，SQL語句如下：

首先查看tb_book2表是否有名為BKNameIdx的索引。

命令語句： show create table tb_book2 G;

drop index BKNameIdx on tb_book2;

執行結果：

SQL語法總結：

創建表的時候創建索引：【create table 表名(欄位名稱 數據類型,unique|fulltext|spatial index 索引名(索引欄位名(索引長度)|…));】

在已有表上增加索引：【alter table 表名 add unique|fulltext|spatial index 索引名(索引欄位名(索引長度)|…);】

在已有表上創建索引：【create unique|fulltext|spatial index 索引名on表名(索引欄位名(索引長度)|…);】

刪除索引：【alter table 表名drop index 索引名;】

刪除索引：【drop index索引名on 表名;】

-------------------《end》------------------