MySQL 實(shí)驗(yàn) 7：索引的操作

索引是對數(shù)據(jù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，索引可以大大提高 MySQL 的檢索速度。合理使用索引，可以大大提升 SQL 查詢的性能。

索引好比是一本書前面的目錄，假如我們需要從書籍查找與 xx 相關(guān)的內(nèi)容，我們可以從目錄中查找，定位到 xx 內(nèi)容所在頁面，如果沒有設(shè)置目錄（索引），則只能逐字逐頁閱讀文本查找。

當(dāng)執(zhí)行查詢操作時，如果不使用索引，MySQL 必須從第一條記錄開始讀完整個表，直到找出相關(guān)的行。如果表中查詢的列有一個索引，MySQL 能夠快速到達(dá)一個位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)。

一、索引的優(yōu)缺點(diǎn)

索引雖然可以提高檢索的速度，但創(chuàng)建過多的、不必要的索引還會影響數(shù)據(jù)增、刪、改的效率。

1、索引的優(yōu)點(diǎn)

（1）索引大大減小了服務(wù)器需要掃描的數(shù)據(jù)量，從而大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。

（2）通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

（3）可以加速表和表之間的連接。

（4）在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時，可以減少查詢中分組和排序的時間。

2、索引的缺點(diǎn)

（1）創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間，這種時間隨著數(shù)據(jù)量的增加而增加。

（2）索引需要占物理空間，除了數(shù)據(jù)表占用數(shù)據(jù)空間之外，每一個索引還要占用一定的物理空間，如果需要建立聚簇索引，那么需要占用的空間會更大。

（3）對表中的數(shù)據(jù)進(jìn)行增、刪、改的時候，索引也要動態(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度。

3、創(chuàng)建索引的原則

（1）在經(jīng)常需要檢索的列上創(chuàng)建索引可以加快檢索的速度。

（2）在作為主鍵的列上創(chuàng)建聚簇索引可以保證該列的唯一性和組織表中數(shù)據(jù)的排列順序。

（3）在經(jīng)常用在連接的列上，一般是表中的外鍵創(chuàng)建索引，可以提高連接的速度。

（4）在經(jīng)常需要根據(jù)范圍（<，<=，=，>，>=，BETWEEN，IN）進(jìn)行搜索的列上創(chuàng)建索引。

（4）在經(jīng)常需要排序（order by）的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間。

（5）在經(jīng)常使用在 WHERE 子句中的列上面創(chuàng)建索引，可以加快條件的判斷速度。

（6）對于那些在查詢中很少使用的列不應(yīng)該創(chuàng)建索引。

（7）對于那些重復(fù)值過多的列也不應(yīng)該增加索引。

（8）經(jīng)常進(jìn)行數(shù)據(jù)更新的列不應(yīng)該創(chuàng)建索引。

二、索引的分類

MySQL 的索引有兩種分類方式：邏輯分類和物理分類。

1、邏輯分類

（1）按功能劃分索引可以分為四類：主鍵索引、唯一索引、普通索引和全文索引。

主鍵索引：主鍵索引默認(rèn)創(chuàng)建為聚簇索引，主鍵索引會改變表中記錄的物理順序。一張表只能創(chuàng)建一個主鍵索引，主鍵索引所包含的列不允許重復(fù)、不允許為 NULL。

唯一索引：唯一索引包含的數(shù)據(jù)列不允許取重復(fù)值，但允許為 NULL 值。一張表可以創(chuàng)建多個唯一索引，索引列的值必須唯一，如果是組合索引，則唯一索引包含的所有列的組合必須取值唯一。

普通索引：一張表可以創(chuàng)建多個普通索引，普通索引允許數(shù)據(jù)重復(fù)，索引所包含的列允許取 NULL 值。

全文索引：查找文本中的關(guān)鍵詞，主要用于全文檢索。

（2）按索引包含的列數(shù)可以分為兩類：單列索引和多列索引（又叫組合索引）。

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

組合索引：一個組合索引包含兩個或兩個以上的列。查詢時遵循組合索引的【最左前綴】原則，即使用 where 時條件要按照建立索引的時候字段的排列方式放置索引才會生效。

2、物理分類

按索引的存儲結(jié)構(gòu)劃分，可以把索引分為聚簇索引（也叫聚集索引）和非聚簇索引。

（1）聚簇索引：聚簇索引（clustered index）不是單獨(dú)的一種索引類型，而是一種數(shù)據(jù)存儲方式，每張表最多只能擁有一個聚簇索引。表中的數(shù)據(jù)其實(shí)就是按照聚簇索引的順序進(jìn)行排列。因此：按照聚簇索引查詢速度更快。

（2）非聚簇索引：聚簇索引之外的索引稱之為非聚簇索引，又稱為輔助索引。查找數(shù)據(jù)時首先通過非聚簇索引找到主鍵值，然后到主鍵索引樹中通過主鍵值找到數(shù)據(jù)行。

三、創(chuàng)建表的同時創(chuàng)建索引

（1）主鍵索引和唯一索引的創(chuàng)建請參見【MySQL 實(shí)驗(yàn)6：定義數(shù)據(jù)的完整性】。

（2）創(chuàng)建普通索引：可以使用關(guān)鍵字 key 或 index 創(chuàng)建普通索引。語法格式如下：

create table table_name(col_name data_type primary key,col_name data_type,... ,col_name data_type,INDEX|KEY [索引名] (列名[(長度)] , ...)
);
-- 說明：
（1）索引名：給創(chuàng)建的索引取一個新名稱。如果不指定則采用字段名作為索引名。
（2）列名：指定索引對應(yīng)的列的名稱。列名可以有多個，即創(chuàng)建多列索引。
（3）長度：指索引的長度，字符串類型才可以使用。
（4）可以根據(jù)表達(dá)式創(chuàng)建索引。

例如：

create table emp02(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30),key(e_name),                               -- 不指定索引名稱index idx_salary(salary),                  -- 指定索引名稱index idx_dept_salary(dept_name,salary),   -- 指定多列索引key idx_phone(phone(11))                   -- 指定索引長度
);-- 查看表結(jié)構(gòu)
mysql> desc emp02;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  | MUL | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)-- 查看索引
mysql> show create table emp02\G
*************************** 1. row ***************************Table: emp02
Create Table: CREATE TABLE `emp02` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `e_name` (`e_name`),KEY `idx_salary` (`salary`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

00試環(huán)境進(jìn)行據(jù)庫

create table emp02(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30),key(e_name),                               -- 不指定索引名稱index idx_salary(salary),                  -- 指定索引名稱index idx_dept_salary(dept_name,salary),   -- 指定多列索引key idx_phone(phone(11)) 
);

四、添加索引

數(shù)據(jù)表創(chuàng)建完成后可以使用 alter table 或 create index 命令添加所需的索引。

1、使用 alter table 添加索引

語法格式如下：

ALTER TABLE 表名 
ADD INDEX|KEY [索引名](字段名[(長度)], ...);

例如：

create table emp03(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30)
);-- 添加索引，不指定索引名
alter table emp03 add index(e_name);-- 添加索引，指定索引名
alter table emp03 add index idx_salary(salary);-- 添加多列索引，指定索引名
alter table emp03 add index idx_dept_salary(dept_name, salary);-- 添加索引，指定長度
alter table emp03 add index idx_phone(phone(11));-- 查看表結(jié)構(gòu)
mysql> desc emp03;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  | MUL | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)-- 查看索引
mysql> show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE `emp03` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `e_name` (`e_name`),KEY `idx_salary` (`salary`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2、使用 create index 添加索引

語法格式如下：

CREATE INDEX 索引名 ON 表名(字段名[(長度)], ...);

例如：

create table emp04(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30)
);-- 添加單列索引
create index idx_name on emp04(e_name);-- 添加多列索引
create index idx_dept_salary on emp04(dept_name,salary);-- 指定索引長度
create index idx_phone on emp04(phone(11));-- 查看表結(jié)構(gòu)
mysql> desc emp04;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)-- 查看索引
mysql> show create table emp04\G
*************************** 1. row ***************************Table: emp04
Create Table: CREATE TABLE `emp04` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `idx_name` (`e_name`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

五、刪除索引

刪除索引的語法格式如下：

-- 使用 alter table 命令刪除索引
LTER TABLE 表名 DROP INDEX 索引名;
-- 使用 drop index 命令刪除索引
DROP INDEX 索引名 ON 表名;

例如：

-- 查看 emp03 表中的索引
mysql> show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE `emp03` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `e_name` (`e_name`),KEY `idx_salary` (`salary`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)-- 刪除索引：e_name
alter table emp03 drop index e_name;-- 刪除索引：idx_salary
drop index idx_salary on emp03;-- 重新查看 emp03 表中的索引
mysql> show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE `emp03` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)