索引用法和利弊權衡

索引（Index）是幫助MySQL高效獲取數據的數據結構。
使用索引前需要創(chuàng)建索引慨绳。

創(chuàng)建索引

普通索引

直接基于表創(chuàng)建：

CREATE INDEX indexName ON table_name(column_name(length));

修改表結構創(chuàng)建：

ALTER table table_name ADD INDEX index_name(column_name);

創(chuàng)建表時指定：

CREATE TABLE table_name(
ID INT NOT NULL,
...
INDEX [index_name] (column_name(length))
);

唯一索引

唯一索引的值必須唯一脐雪，但允許多個空值战秋。

直接基于表創(chuàng)建：

REATE UNIQUE INDEX indexName ON table_name(column_name(length));

修改表結構創(chuàng)建：

ALTER table table_name ADD UNIQUE index_name(column_name);

創(chuàng)建表時指定：

CREATE TABLE table_name(
ID INT NOT NULL,
...
UNIQUE [index_name] (column_name(length))
);

主鍵索引

主鍵索引是唯一索引的一種讨韭，但主鍵索引不可為空，且一個表只能創(chuàng)建一個主鍵索引狰闪，但可以創(chuàng)建多個唯一索引濒生。另外，主鍵索引可以被其他表引用為外鍵秋泄，但唯一索引不可以恒序。

在創(chuàng)建表的時候指定：

CREATE TABLE table_name(
ID INT NOT NULL,
...
PRIMARY KEY (column_name(length))
);

或

CREATE TABLE table_name(
ID INT PRIMARY KEY NOT NULL,
...
);

修改表結構指定:

ALTER table table_name ADD PRIMARY KEY (column_name);

例子

向score表的score列創(chuàng)建主鍵索引：

mysql> CREATE UNIQUE INDEX score_index ON score(score);
Query OK, 0 rows affected (0.75 sec)

刪除索引

直接刪除

DROP INDEX index_name ON table_name;

修改表結構刪除

ALTER TABLE table_name DROP INDEX index_name;

刪除主鍵
因為一個表只有一個主鍵谁撼，因此可如下直接刪除主鍵索引

ALTER TABLE table_name DROP PRIMARY KEY;

例子
刪除剛剛添加的score列的唯一索引：

mysql> DROP INDEX score_index ON score;
Query OK, 0 rows affected (0.34 sec)

刪除score表的主鍵

mysql> ALTER TABLE score DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

因為score表的主鍵厉碟，id列為自增字段箍鼓，自增的字段必須為主鍵，因此刪除主鍵前需要先刪除自增：

mysql> SHOW COLUMNS FROM score;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| student_id | int(11) | NO   | MUL | NULL    |                |
| subject_id | int(11) | NO   | MUL | NULL    |                |
| score      | double  | YES  | UNI | NULL    |                |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> ALTER TABLE score MODIFY id INT(11); // 刪除自增
Query OK, 5 rows affected (1.97 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM score;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | NO   | PRI | NULL    |       |
| student_id | int(11) | NO   | MUL | NULL    |       |
| subject_id | int(11) | NO   | MUL | NULL    |       |
| score      | double  | YES  | UNI | NULL    |       |
+------------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)

再刪除主鍵：

mysql> ALTER TABLE score DROP PRIMARY KEY;
Query OK, 5 rows affected (1.77 sec)

查詢索引

SHOW INDEX FROM table_name;

使用索引

最開始說到索引是幫助MySQL高效獲取數據的數據結構，因此索引即是用來快速檢索數據的海洼。

SELECT * FROM table_name WHERE column_name = value;

如我們向上面的column_name列創(chuàng)建了索引后坏逢，當表中數據非常多時，檢索數據的效率會大大高于無索引的情況赘被。

索引的利弊

利

如剛才說到的是整，索引能夠提高數據檢索的效率，降低數據庫的IO成本民假。
索引能夠降低數據的排序成本贰盗。
每個索引中的數據都是按照索引鍵鍵值進行排序后存放的，所以在排序分組操作時阳欲，如果排序字段和索引鍵字段剛好一致舵盈，在取得數據后就不用排序了。因為根據索引取得的數據已經滿足客戶的排序要求球化。

弊

索引是獨立于基礎數據外的一部分數據秽晚，因此索引會消耗存儲空間資源。另外筒愚，基礎數據越多赴蝇，相應的索引也越多，消耗的空間也越多巢掺。就好比圖書館里的書越多句伶，借閱檢索系統的數據也就越多先嬉。

對于創(chuàng)建了索引的列疫蔓，更新該列時酥筝，相應的索引也要更新哀九。這就增加了更新所帶來的 IO 量和調整索引所致的計算量。比如當我們在圖書館借書還書時息裸，借閱檢索系統的數據也要更新。

什么時候創(chuàng)建索引

較為頻繁的作為查詢條件的字段應創(chuàng)建索引
因為索引就是用來提高數據檢索的效率访圃。
表中數據很多時適合創(chuàng)建索引
當表中數據較少時饭宾，數據檢索無論有沒有索引都挺快的徽鼎。
唯一性太差的列不適合創(chuàng)建索引
例如性別列，目前只有‘男’和‘女’檐嚣，每個值都會有成千上萬的值报咳，再創(chuàng)建索引提高檢索效率也沒有意義厢漩。且當索引字段中每個值都含有大量的記錄時架谎，存儲引擎在根據索引訪問數據的時候會帶來大量的隨機IO土全，甚至有些時候還會出現大量的重復IO裹匙。
更新非常頻繁的列不適合創(chuàng)建索引。
這點由索引的弊端我們即可明白。