索引（基礎） 一、索引介紹 1.1、前言 在資料庫中，執行如下語句時： select * from emp where id=1000; mysql 是從第一條記錄開始遍歷，直至找到 id = 1000 的數據，然而這樣查詢的效率低，所以 mysql 允許通過建立索引來加快數據表的查詢和排序。 1. ...
索引（基礎）
一、索引介紹
1.1、前言
在資料庫中，執行如下語句時：
select * from emp where id=1000;
mysql 是從第一條記錄開始遍歷，直至找到 id = 1000 的數據，然而這樣查詢的效率低，所以 mysql 允許通過建立索引來加快數據表的查詢和排序。
1.2、索引概念
資料庫的索引類似字典中的拼音，是對資料庫表中一列或多列的值進行排序後的一種結構。
作用：就是提高表中數據的查詢速度。
1.3、索引分類
-
普通索引：
不加任何參數，直接通過
key或index來創建的索引。是 mysql 中的基本索引類型，可創建在任意數據類型中。其值是否唯一、非空，則由欄位本身的約束決定。 -
唯一索引：
在定義索引時，加上
unique即可，該索引所在欄位的值必須唯一。但允許有空值。在一張數據表裡可以有多個唯一索引。 -
全文索引：
在定義索引時，加上
fulltext即可，該索引只能創建在 char、varchar 、text 類型的欄位上。 -
單列索引：
是在表中單個欄位上創建索引，可以為普通索引、唯一索引等，只要保證該索引只對應表中一個欄位即可。
-
多列索引：
是在表中多個欄位上創建索引，但是只有在查詢條件中使用了這些欄位中的第一個欄位時，該索引才會被使用。
-
空間索引：
在定義索引時，加上spatial即可，該索引只能創建在空間數據類型的欄位上。mysql 中空間類型索引有 4 中：
GEOMETRY、POINT、LINESTRING 和 POLYGON。但是當使用空間索引時，該欄位的約束必須為not null且資料庫的存儲引擎為MyISAM中使用。
註：主鍵：primary key 也可作為索引
但本質上看其是一種約束，而索引是一種數據結構，用來提升查詢效率
二、創建索引
2.1、創建表時
語法：
create table 表名(
欄位 數據類型 約束
[unique|fulltext|spatial] index|key [別名] (欄位) [ASC|DESC]
);
[]：中的值表示可選項
[unique|fulltext|spatial]：分別表示：唯一索引、全文索引、空間索引
[ASC|DESC]：升序、降序
2.1.1、創建普通索引
語法：
create table 表名(
欄位 數據類型 約束
index|key [別名] (欄位) [ASC|DESC]
);
例如這裡需要在 t1 表中的 id 創建索引：
create table t1(
id int,
name varchar(10),
age int,
index(id)
);
通過下述命令查看是否創建成功：
show create table t1\g
通過上圖可知，t1 表中成功創建索引。
可再通過下述命令查看是否使用：
explain select * from t1 where id=1\g
通過圖中的 possible_keys 與 key 知 其值都為 id ，表明 id 索引已經存在並開始使用。
2.1.2、創建唯一索引
語法：
create table 表名(
欄位 數據類型 約束
unique index|key [別名] (欄位) [ASC|DESC]
);
例如這裡需要在 t2 表中在 id 上創建 unique_id 索引：
create table t2(
id int,
name varchar(7),
score float,
unique index unique_id (id ASC)
);
2.1.3、創建全文索引
語法：
create table 表名(
欄位 數據類型 約束
fulltext index|key [別名] (欄位) [ASC|DESC]
);
例如這裡需要在 t3 表上根據 name 欄位來創建全文索引：
create table t3(
id int,
name varchar(7),
score float,
fulltext index fulltext_name (name)
);
2.1.4、創建單列索引
語法：
create table 表名(
欄位 數據類型 約束
index|key [別名] (欄位1,欄位2......) [ASC|DESC]
);
例如需要在 t4 表上根據 name 創建索引
create table t4(
id int,
name varchar(7),
score float,
index single_name (name(7))
);
2.1.5、創建多列索引
語法：
create table 表名(
欄位 數據類型 約束
index|key [別名] (欄位1,欄位2......) [ASC|DESC]
);
例如需要在 t5 表上創建多列索引：
create table t5(
id int,
name varchar(7),
score float,
index multi (id,name(7))
);
註意：只有當在查詢條件中使用索引欄位的第一個欄位時，才會有效。驗證如下：
這裡通過 id 來查詢：
explain select * from t5 where id=1\g
當通過 name 來查詢時:
explain select * from t5 where name='張三'\g
這時，發現通過 name 查詢的結果中：possible_keys | key 皆為 null,表明索引併為使用。
2.1.6、創建空間索引
語法：
create table 表名(
欄位 數據類型 約束
spatial index|key [別名] (欄位1,欄位2......) [ASC|DESC]
);
例如需要在 t6 表上創建空間索引：
create table t6(
id int,
space geometry not null,
spatial index sp (space)
);
2.2、創表後
1、通過 create 創建
語法：
create [unique|fulltext|spatial] index 索引名 on 表明 (欄位名[長度]) [asc|desc];
這裡以創建唯一索引為例：
需要在 t7 表上根據 bookid 創建唯一索引
create unique index unique_id on book(bookid);
其餘類似。
2、通過 alter 創建
語法：
alter table 表名 add [unique|fulltext|spatial] index 索引名 (欄位名[長度]) [asc|desc];
這裡以創建唯一索引為例：
需要在 t7 表上根據 bookid 創建唯一索引
alter table book add unique index unique_id (bokid);
其餘類似。
三、刪除索引
由於索引會占用一定的磁碟空間，所以為了避免影響資料庫性能，需刪除不使用的索引。
3.1、alter
語法：
alter table 表名 drop index 索引名
這裡以刪除唯一索引為例：
需要在 t7 表上根據 bookid 刪除唯一索引：
alter table book drop index unique_id;
其餘類似。
3.2、drop
語法：
drop index 索引名 on 表名;
這裡以刪除唯一索引為例：
需要在 t7 表上根據 bookid 刪除唯一索引：
drop index unique_id on book;
其餘類似。
