索引（基礎）

一、索引介紹

1.1、前言

在資料庫中，執行如下陳述句時：

select * from emp where id=1000;

mysql 是從第一條記錄開始遍歷，直至找到 id = 1000 的資料，然而這樣查詢的效率低，所以 mysql 允許通過建立索引來加快資料表的查詢和排序，

1.2、索引概念

資料庫的索引類似字典中的拼音，是對資料庫表中一列或多列的值進行排序后的一種結構，

作用：就是提高表中資料的查詢速度，

1.3、索引分類

1. 普通索引：

   不加任何引數，直接通過 key 或 index 來創建的索引，是 mysql 中的基本索引型別，可創建在任意資料型別中，其值是否唯一、非空，則由欄位本身的約束決定，

2. 唯一索引：

   在定義索引時，加上 unique 即可，該索引所在欄位的值必須唯一，但允許有空值，在一張資料表里可以有多個唯一索引，

3. 全文索引：

   在定義索引時，加上 fulltext 即可，該索引只能創建在 char、varchar 、text 型別的欄位上，

4. 單列索引：

   是在表中單個欄位上創建索引，可以為普通索引、唯一索引等，只要保證該索引只對應表中一個欄位即可，

5. 多列索引：

   是在表中多個欄位上創建索引，但是只有在查詢條件中使用了這些欄位中的第一個欄位時，該索引才會被使用，

6. 空間索引：
   在定義索引時，加上 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;

其余類似，

標籤：MySQL

上一篇：大資料面試（個人總結含答案）

下一篇：返回列表