31. SQL--索引（index）

1. 前言

SQL 索引（Index）用于提高数据表的查询速度。一个表可以创建多个索引，一个索引可以包含一个或者多个字段。

不使用索引，数据库引擎将遍历整个表。

从表面上看，索引类似于书的目录，在没有目录的情况下，要从书中查找某项内容就必须阅读全文，而有了目录之后，通过页码就可以很快定位到相关内容。

从本质上看，索引是根据表的一个或者多个字段生成的子表，该子表中的数据已经进行了排序。子表除了包含指定字段中的数据，还包含一个 rowid 列，用于存储当前记录在原始表中的位置。用户无法看到索引，它只是用来加快查询速度。

为了提高查询效率，便于后期维护，索引都是基于某种数据结构而创建的，比如 B+ 树、B- 树、位图等。

索引的使用成本

在表中插入、修改或者删除数据时，数据库引擎也必须维护索引，以保持索引和原始表的同步；也就是说，使用索引是有额外开销的。不适合的索引，或者过多的索引，都会降低插入、修改和删除数据的效率。

索引还会占用磁盘空间，增加 I/O 成本，过多的索引甚至会增加碎片。

鉴于以上两点，使用索引时应该遵循以下几条原则：

• 仅在被频繁检索的字段上创建索引。
• 针对大数据量的表创建索引，而不是针对只有少量数据的表创建索引。
• 通常来说，经常查询的记录数目少于表中总记录数据的 15% 时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
• 尽量不要在有大量重复值得字段上建立索引，比如性别字段、季度字段等。

2. 语法

在某个字段上创建索引的基本语法如下：

create index index_name
on table_name ( column1, column2.....);

index_name 是索引的名字，以后在删除索引时会用到。

3. 示例

使用 SQL 语句创建一个包含七列的 website 表：

create table website (
    id      int              not null   auto_increment,
    name    varchar(20)      not null,
    url     varchar(30),
    age     tinyint unsigned not null,
    alexa   int unsigned     not null,
    uv      float                       default '0',
    country char(3)          not null,
    primary key (`id`)
);

现在您可以针对 name 字段创建索引，用以提高检索姓名时的效率，如下所示：

create index myindex
on website(name);

myIndex 是索引的名字。

4. 删除索引

删除索引请使用下面的 SQL 语句：

alter table website
drop index myindex;