索引

2019-04-06 本文已影响0人夜阑w

索引（Index）是帮助MySQL高效获取数据的数据结构，即索引是数据结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

一、索引的用法

1. 创建索引

普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制，通常有三种方式可以创建：

直接基于表创建：

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))
);

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。创建唯一索引有三种方法：

直接基于表创建：

CREATE 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))
);

主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”，每个表只能有一个主键，通常是自增的ID，大部分在创建表的时候指定。它不能用CREATE INDEX语句创建，共有两种创建方式。

创建表时指定：

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

修改表结构创建：

ALTER table table_name ADD PRIMARY KEY (column_name);

主键索引由于每个表中只有一个，所以不需要指定索引名。主键可用自动生成，比如INT字段，可以用AUTO_INCREMENT来自动生成。

2. 删除索引

直接删除

DROP INDEX index_name ON table_name;

修改表结构删除

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name DROP PRIMARY KEY;

3. 查看索引

SHOW INDEX FROM table_name;

二、索引的利弊权衡

1. 索引的好处

提高数据检索的效率。
在数据库中表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件时可以极大地提高检索效率，加快检索时间，降低检索过程中须要读取的数据量。
降低数据的排序成本。
每个索引中的数据都是按照索引键键值进行排序后存放的。如果排序字段和索引键字段刚好一致，则取得数据后就不用排序了，因为根据索引取得的数据已经满足客户的排序要求。分组操作是要先进行排序然后分组的，如果分组字段也刚好和索引键字段一致，同样可以利用索引已经排好序的这个特性，省略掉分组中的排序操作。排序分组操作主要消耗的是内存和 CPU 资源，如果能够在进行排序分组操作中利用好索引，将会极大地降低CPU资源的消耗。

2. 索引的弊端

确实，索引能够极大地提高数据检索效率，也能够改善排序分组操作的性能，但有不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。

如果创建了索引，则MySQL在更新表的同时，都需要更新索引数据，调整因为更新带来键值变化的索引信息。而如果没有创建索引，MySQL要做的仅仅是更新表中的信息。这样，最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外，索引需要占用存储空间，而且随着表中数据量的增加，索引所占用的空间也会不断增加，所以索引还会带来存储空间资源消耗的增加。

3. 索引的选择

较频繁的作为查询条件的字段应该创建索引。
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。
如状态字段、类型字段等这些字段中存放的数据可能总共就是那么几个或几十个值重复使用，每个值都会存在于成千上万或更多的记录中。对于这类字段，完全没有必要创建单独的索引。
更新非常频繁的字段不适合创建索引。
引中的字段被更新的时候，不仅要更新表中的数据，还要更新索引数据，以确保索引信息是准确的。这个问题致使IO 访问量较大增加，不仅仅影响了更新 Query 的响应时间，还影响了整个存储系统的资源消耗，加大了整个存储系统的负载。
不会出现在 WHERE 子句中的字段不该创建索引。

索引