MySQL组合索引(a,b,c)和EXPLAIN的使用

前言

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。而索引可分单列索引和组合索引。

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
• 组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的增删改查语法

-- 创建索引
CREATE INDEX indexName ON table_name (column_name1,column_name2,...);

-- 删除索引
DROP INDEX [indexName] ON table_name;

-- 修改索引，也可以用来新增索引
ALTER table tableName ADD [UNIQUE] INDEX indexName(column_name1,column_name2,...);

-- 查看索引信息
SHOW INDEX FROM table_name

---

什么是组合索引的最左匹配原则？

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。

最左匹配原则测试

• 建表，插入测试数据

-- 建表
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`(
 `id` INT,
 `name` VARCHAR(10),
 `address` VARCHAR(100),
 `birthday` DATETIME
);
-- 创建组合索引，在后续注释中使用abc代表这三个字段
CREATE UNIQUE INDEX index_name ON t_user(id,name,address);
-- 添加测试数据
TRUNCATE TABLE t_user;
INSERT INTO t_user VALUES
(1,'zhangsan','guangdong',CURRENT_DATE()),
(2,'lisi','guangdong',CURRENT_DATE()),
(3,'wangwu','guangdong',CURRENT_DATE()),
(4,'zhaoliu','jiangxi',CURRENT_DATE());

• 使用 EXPLAIN关键字查看SQL的执行是否使用了索引

EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

1、查询条件：abc

EXPLAIN SELECT * FROM t_user WHERE id = 3 AND NAME = 'wangwu' AND address = 'guangdong';

执行结果如下：

2、查询条件：ab

EXPLAIN	SELECT * FROM t_user WHERE id = 3 AND NAME = 'wangwu';

执行结果如下：

3、查询条件：ac

EXPLAIN	SELECT * FROM t_user WHERE id = 3 AND address = 'guangdong';

执行结果如下：

4、查询条件：a

EXPLAIN	SELECT * FROM t_user WHERE id = 3;

执行结果如下：

>> 以上四次测试结果都是走了索引，并且观察第3次和第4次的测试结果中的key_len结果相同。由此可知，查询条件为ac的时候虽然使用了索引，实际其实只用了a，而不是ac。

如下这几种情况是不会走索引：

-- b
EXPLAIN	SELECT * FROM t_user WHERE NAME = 'wangwu';

-- c
EXPLAIN	SELECT * FROM t_user WHERE address = 'guangdong';

-- bc
EXPLAIN	SELECT * FROM t_user WHERE NAME = 'wangwu' AND address = 'guangdong';

他们的执行结果都为：

特殊情况：

-- 优化器会自动调整and前后的顺序，如下sql执行都走索引
-- ca
EXPLAIN	SELECT * FROM t_user WHERE address = 'guangdong' AND id = 3;

-- ba
EXPLAIN	SELECT * FROM t_user WHERE NAME = 'wangwu' AND id = 3;

-- cba
EXPLAIN	SELECT * FROM t_user WHERE address = 'guangdong' AND NAME = 'wangwu' AND id = 3;

-- bac
EXPLAIN	SELECT * FROM t_user WHERE NAME = 'wangwu' AND id = 3 AND address = 'guangdong';

-- bca
EXPLAIN	SELECT * FROM t_user WHERE NAME = 'wangwu' AND address = 'guangdong' AND id = 3;

-- 范围查询
EXPLAIN SELECT * FROM t_user WHERE id = 2 AND NAME LIKE '%lisi%' AND address = 'guangdong'; -- key_len = 5,只使用了a【前提是like使用了左%】
EXPLAIN SELECT * FROM t_user WHERE (id BETWEEN 3 AND 4) AND address = 'jiangxi'; -- key_len = 5,只使用了a
EXPLAIN SELECT * FROM t_user WHERE id > 1 AND address = 'guangdong'; -- key_len = 5,只使用了a

总结：用到索引的有a,ab,abc,ac 因为优化器会自动调整and前后的顺序，所以ba,cba,bca,ca【只要a存在】都会用到索引，其他的都不会用到该索引。ac这一组仅仅是a用到索引。
注意；组合索引 有“最左前缀”原则，但是遇到范围查询(>、<、between、like)就会停止匹配。

---

EXPLAIN执行结果各字段含义解释

select_type = SIMPLE：数据读取操作的操作类型

　　1、SIMPLE:简单的select 查询，SQL中不包含子查询或者UNION。
　　2、PRIMARY:查询中包含复杂的子查询部分，最外层查询被标记为PRIMARY
　　3、SUBQUERY:在select 或者WHERE 列表中包含了子查询
　　4、DERIVED:在FROM列表中包含的子查询会被标记为DERIVED(衍生表)，MYSQL会递归执行这些子查询，把结果集放到零时表中。
　　5、UNION:如果第二个SELECT 出现在UNION之后，则被标记位UNION；如果UNION包含在FROM子句的子查询中，则外层SELECT 将被标记为DERIVED
　　6、UNION RESULT:从UNION表获取结果的select

type = const 表示访问类型 ，由好到差system > const > eq_ref > ref > range > index > ALL；

　　1、system:表只有一条记录(等于系统表),这是const类型的特例，平时业务中不会出现。
　　2、const:通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快;如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。
　　3、eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
　　4、ref:非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行，就是说它可能会找到多条符合条件的数据，所以他是查找与扫描的混合体。
　　5、range：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
　　6、index：FUll Index Scan 扫描遍历索引树(扫描全表的索引，从索引中获取数据)。
　　7、ALL 全表扫描 从磁盘中获取数据 百万级别的数据ALL类型的数据尽量优化。

possible_keys = index_name：表示可能会使用的索引，一个或者多个。但不一定被查询实际使用；

key = index_name：表示该sql的执行实际使用到的索引，如果为NULL，则没有使用索引。；

key_len = 341：表示该索引使用的字节数；

ref = const,const,const : 表示索引的哪一列被引用了

rows = 1：表示执行该sql预计要扫描的行数

Extra：扩展属性，但是很重要的信息。

1、 Using filesort(文件排序)：mysql无法按照表内既定的索引顺序进行读取。

举例：假设id是表内的一个唯一索引列，但是order by 没有使用该索引列排序，所以mysql使用不得不另起一列进行排序。

2、Using temporary:Mysql使用了临时表保存中间结果，常见于排序order by 和分组查询 group by。

3、Using index 表示相应的select 操作使用了覆盖索引，避免访问了表的数据行，效率不错。

如果同时出现Using where ，表明索引被用来执行索引键值的查找。如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。

4、Using where 查找

5、Using join buffer ：表示当前sql使用了连接缓存。

...

---