31 SQL 索引

优质

小牛编辑

136浏览

2023-12-01

1. 定义

慕课解释：索引（index）是一个单独的数据库物理结构，是包含数据表字段的列表，列表中注明字段每个值所在的存储位置。通俗来说，索引类似于书的目录，你可以通过目录迅速查询书的内容，通过索引迅速查询数据表的数据。

2. 前言

本小节，我们将一起学习 SQL 中的索引。

数据库在进行查询的时候，需要对整张表进行扫描，当数据表的数据量大的时候，这样扫描的效率是很低下的。为了提高数据检索能力，增强数据库查询性能，数据库引入了索引机制，且 SQL 标准中也加入了索引相关操作。

本小节测试数据如下，请先在数据库中执行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22),(4,'mike',18),(5,'tom',20);

3. 索引的创建与删除

索引是一个单独的数据库物理结构，因此它也可以通过 Create 和 Drop 指令来创建和删除。

语法如下：

CREATE INDEX [index_name] ON [table_name]([col]);

DROP INDEX [index_name] ON [table_name];

其中index_name表示索引名称，table_name表示数据表名称，col表示字段名称。

3.1 例1 单字段普通索引

请书写 SQL 语句，为 imooc_user 的age字段新建一个普通索引。

分析：

按照语法，使用 Create 新建索引，并指定索引名称即可。

语句：

CREATE INDEX age_index ON imooc_user(age);

3.2 例2 单字段唯一索引

索引可以分为普通索引和唯一索引，唯一索引要求字段必须唯一、不可重复。

请书写 SQL 语句，为 imooc_user 的username字段新建一个唯一索引。

分析：

同例1，但需要给索引添加唯一约束，即 Unique。

语句：

CREATE UNIQUE INDEX username_index ON imooc_user(username);

3.3 例3 多字段索引

SQL 也支持我们为多个字段建立索引。

请书写 SQL 语句，为 imooc_user 的username和age字段新建一个普通索引。

分析：

同例1。

语句：

CREATE INDEX username_age_index ON imooc_user(username,age);

3.4 例4 删除索引

请书写 SQL 语句，删除掉 imooc_user 上的age_index索引。

分析：

按照删除索引语法写出语句即可。

语句：

DROP INDEX age_index;

在 MySQL 中，你还需要告诉数据库索引所在的数据表，如下：

DROP INDEX age_index ON imooc_user;

4. 使用索引

索引的使用是智能的，数据库会自动找到对应的索引来加速你的查询。

如，你已经新建了 username_index 索引，当你以 username 作为条件查找时，会自动使用到 username_index 索引。

如下：

SELECT * FROM imooc_user WHERE username = 'pedro';

4.1 例5 显示使用索引

索引选择虽然是智能的，但它有时也会犯错，所以你可以显示的指定使用某个索引。

请书写 SQL 语句，显示的使用索引去搜索 imooc_user 表中的用户pedro。

分析：

通过 Force Index 语法显示使用索引即可。

语句：

SELECT * FROM imooc_user FORCE INDEX(username_index) WHERE username = 'pedro';

注意： MySQL 支持 Force Index，其它数据库不支持，且强制式使用某个索引的方式并不好，而选错索引的概率很低。如果出现了索引选错的情况，请优先检查 SQL 语句，尝试优化一下可读性。

5. 小结

索引是大数据查询的利器，能显著提升搜索的效率，是一种典型的空间换时间思想运用。
不要吝啬你的磁盘容量，如果某个查询相应时间很慢，请马上思考一下，能否使用索引解决。
索引遵循最左原则，即优先匹配左边的元素，联合索引也不例外。