索引（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。 - 更新非常频繁的列不适合创建索引。
这点由索引的弊端我们即可明白。
网友评论