SQL索引(INDEX)提高数据表的查询速度 SQL索引的基本语法

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

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

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

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

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

索引的使用成本

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

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

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

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

语法

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

CREATE INDEX index_name
ON table_name ( column1, column2.....);

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

示例

使用 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 是索引的名字。

删除索引

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

ALTER TABLE website
DROP INDEX myIndex;