SQL 索引

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 的usernameage字段新建一个普通索引。

分析:

同例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. 小结

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