【SQL】索引的使用及EXPLAIN关键字

索引的类型及创建

1. 主键索引

表中的列设定为主键之后会，数据库会为该列自动建立主SQL键索引
可随表创建时创建主键，如下：

CREATE TABLE 表名 (
	字段1 数据类型 PRIMARY KEY [AUTO_INCREMENT],
	...
);

也可以为已存在的表添加主键（但通常做法是在创建表时创建主键约束） ALTER TABLE 表名 ADD PRIMARY KEY(字段);

2. 唯一索引

列表中的列创建唯一约束时，数据库会为该列自动建立唯一索引
可随表创建时创建唯一索引，如下两种方法

-- 不设置索引名，默认索引名为字段名
CREATE TABLE 表名 (
	字段1 数据类型 UNIQUE,
	...
);

-- 设置索引名
CREATE TABLE 表名 (
	字段1 数据类型,
	...
	UNIQUE  索引名(字段1)
);

也可以为已存在的表设置唯一约束同时建立唯一索引：ALTER TABLE 表名 ADD UNIQUE 索引名(字段名)

3. 单值索引

一个索引中只包含单个列，一张表中可以有多个单值索引
可随表创建时创建单值索引，如下

CREATE TABLE 表名 (
	字段1 数据类型,
	...
	KEY  索引名(字段1)
);

也可以为已存在的表创建单值索引：ALTER TABLE 表名 ADD INDEX 索引名(字段名)

4. 复合索引

一个索引中包含多个列
可随表创建时创建复合索引，如下

CREATE TABLE 表名 (
	字段1 数据类型,
	字段2 数据类型,
	字段3 数据类型,
	...
	KEY  索引名(字段1, 字段2, 字段3, ...)
);

也可以为已存在的表创建复合索引：ALTER TABLE 表名 ADD INDEX 索引名(字段1, 字段2, 字段3, ...)

索引的删除

对于主键索引的删除我们需要删除主键约束 ALTER TABLE 表名 DROP PRIMARY KEY
对于其余索引的删除我们可以执行 DROP INDEX 索引名 ON 表名

注意事项：
当我们删除唯一索引时，对应列的唯一约束也会被删除

EXPLAIN 关键字的使用

用法：EXPLAIN SQL语句
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道数据库是如何处理SQL语句的。可以用来分析查询语句或是表结构的性能瓶颈。

1.id

查询的序列号，表示查询中执行SELECT子句或操作表的顺序

• 当id相同时，执行顺序由上至下
• 当id全都不同时，id值越大越先被执行
• 当id相同和不相同都存在时，id相同的可以理解为一组，组内从上往下顺序执行，组外id值越大越先执行

2. table

当前操作关于那一张表

• 临时表会以 derived{id}的形式命名，id取决于子查询语句的id

3. select_type

查询类型，常见类型有SIMPLE，DERIVED，PRIMARY，SUBQUERY

• SIMPLE：简单的SELECT查询，查询中不包含任何子查询或者UNION
• DERIVED：在FROM列表中包含的子查询会被标记为 DERIVED。数据库会递归这些子查询，把结果放在临时表里。例如 SELECT * FROM (SELECT t1.content FROM t1) s1; 其中的 SELECT t1.content FROM t1 段便会被标记为DERIVED
• PRIMARY：查询中包含任何复杂的子部分，最外层的查询被标记为PRIMARY
• SUBQUERY：在 SELECT或WHERE 列表中包含的子查询

4. type

访问类型排序

• system：表只有一行记录（等于系统表），这是 CONST 类型的特例，平时基本不会出现
• const：表示通过索引一次就找到了
• eq_ref：唯一性索引扫描（字段设置了主键索引或唯一索引）例如：SELECT * FROM t1 JOIN t2 ON t1.id = t2.id 中首先会遍历找到t1中所有数据的id（type为ALL），并将该id分别与t2的id对照，因为t2的id是t2表的主键（每个值都唯一），因此对于t2表的操作会被标记为eq_ref
• ref：非唯一性索引扫描
• range：只检索给定范围的行，使用一个索引来选择行。例如 SELECT * FROM t1 WHERE t1.id > 0
• index：当查询字段均建立过索引时，查询则会只遍历索引树，因为索引文件通常比数据文件小因此index会高效于ALL
• ALL：将遍历全表以找到匹配的行

注意事项：
效率从好到差依次是：system>const>eq_ref>ref>range>index>all
一般尽可能保证达到range级别，最好能到ref

5. possible_keys

显示可能应用在这张表中的索引（一个或多个）查询涉及到的字段上如果存在索引，则该索引将会被列出来，但不一定被查询实际使用上