索引用法和利弊权衡

索引（Index）是帮助MySQL高效获取数据的数据结构。
使用索引前需要创建索引。

创建索引

普通索引

• 直接基于表创建：

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

• 修改表结构创建：

ALTER table table_name ADD INDEX index_name(column_name);

• 创建表时指定：

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

唯一索引

唯一索引的值必须唯一，但允许多个空值。

• 直接基于表创建：

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

• 修改表结构创建：

ALTER table table_name ADD UNIQUE index_name(column_name);

• 创建表时指定：

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

主键索引

主键索引是唯一索引的一种，但主键索引不可为空，且一个表只能创建一个主键索引，但可以创建多个唯一索引。另外，主键索引可以被其他表引用为外键，但唯一索引不可以。

• 在创建表的时候指定：

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列创建主键索引：

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列创建了索引后，当表中数据非常多时，检索数据的效率会大大高于无索引的情况。

索引的利弊

利

• 如刚才说到的，索引能够提高数据检索的效率，降低数据库的IO成本。
• 索引能够降低数据的排序成本。
  每个索引中的数据都是按照索引键键值进行排序后存放的，所以在排序分组操作时，如果排序字段和索引键字段刚好一致， 在取得数据后就不用排序了。因为根据索引取得的数据已经满足客户的排序要求。

弊

索引是独立于基础数据外的一部分数据，因此索引会消耗存储空间资源。另外，基础数据越多，相应的索引也越多，消耗的空间也越多。就好比图书馆里的书越多，借阅检索系统的数据也就越多。

对于创建了索引的列，更新该列时，相应的索引也要更新。这就增加了更新所带来的 IO 量和调整索引所致的计算量。比如当我们在图书馆借书还书时，借阅检索系统的数据也要更新。

什么时候创建索引

• 较为频繁的作为查询条件的字段应创建索引
  因为索引就是用来提高数据检索的效率。
• 表中数据很多时适合创建索引
  当表中数据较少时，数据检索无论有没有索引都挺快的。
• 唯一性太差的列不适合创建索引
  例如性别列，目前只有‘男’和‘女’，每个值都会有成千上万的值，再创建索引提高检索效率也没有意义。且当索引字段中每个值都含有大量的记录时，存储引擎在根据索引访问数据的时候会带来大量的随机IO，甚至有些时候还会出现大量的重复IO。
• 更新非常频繁的列不适合创建索引。
  这点由索引的弊端我们即可明白。