MySQL 续集 04

explain 续

possible_keys key

possible_keys：

显示可能用在表中的索引 一个或者多个

查询涉及到的字段上若存在多个索引，则该索引将会被列出（但不一定被实际查询使用）

key:

实际使用的索引 如果为null 则表示没有使用索引

若查询中出现覆盖索引，则该索引仅出现在key列表中

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_index_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '测试', '18');
INSERT INTO `user` VALUES ('3', '测试', '18');
INSERT INTO `user` VALUES ('4', '测试', '18');
INSERT INTO `user` VALUES ('5', '测试', '18');
INSERT INTO `user` VALUES ('6', '测试', '18');

执行1：

explain select  id   from user where id  = 5

结果1：

可能用到PRIMARY 实际用到PRIMARY

执行2：

explain select  age,id   from user where id  = 5  and age = 18

结果2：

可能用到PRIMARY,user_index_age 结果用到 PRIMARY

执行3：

explain select  age  from user where id  in (1,2,3,4,5)

结果3：

可能用到 PRIMARY 结果却用到了user_index_age

覆盖索引：

-- 创建表 test 
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_index_age_name` (`age`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行：

explain select name ,age  from test  
explain select name  from test 
explain select  age  from test

这个就是覆盖索引：

• • 
    
• 
  

解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。上边例子中age name 联合索引 如果只查询age 或者只查询name 或者查询age name 都会被覆盖

解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。
比如上边换成以下语句是不能用到覆盖索引的 id不在索引中 不能被覆盖:

explain select  age  from test  where id = ''

注意：不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引 可以换一下索引类型试试 确实不行

• 
  

key_len:

• • 
    
• 表示索引中使用的字节数，可通过该列计算查询中使用到索引的长度，在不损失精度的情况下（这里的精度指的是查询结果的精确度 比如where中条件多 结果就更精确） 长度越短越好
• key_len 显示的值为索引的最大可能长度，并非实际使用长度,即key_len是根据表定义计算而得出的结果，不是通过表内检索出的
  还是上边的test表：
  执行1：
  • 
    

explain select sex from test  where name ='测试'

结果1：

key_len = 768

执行2：

explain select sex from test  where name ='测试' and age =18

结果2：

key_len变成了 773了 因为查询条件加了个age 更加精确了

ref:

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

test表与test1表

执行：

explain select *  from test  ,test1 where test.id= test1.id and test.name = '测试'

在这里插入图片描述

​​test.name​​ = ‘测试’ =》 ref = const 是一个常量条件

test.id= ​​test1.id​​ 用到了test数据库中的test1表的id 字段 ​​test.test1.id​​

rows:

根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数（当然越小越好啦）

test表的索引先删除 创建几百条数据

explain select age  from test where age =12 and name ='测试'

可以看到 这里读取了2048行

把索引加上：

ALTER TABLE `test`
DROP INDEX `test_index_name_age` ,
ADD INDEX `test_index_name_age` (`age`, `name`) USING BTREE ;

再执行：

explain select age  from test where age =12 and name ='测试'

这里读取一行就把数据读去出来了 厉害了！！ 因为age字段有索引 它就会判断 一下子就知道没有12这个值得age 所以就直接出结果

Extra:

包含不适合在其他列显示的但是十分重要额外信息

1. Using filesort 遇到后 性能受影响
   说明mysql会对数据使用外部索引排序，而不是按照表内的索引顺序进行读取，也就是说MYSQL无法利用索引完成的排序操作称之为“文件排序”
   还是用test表

explain select  id from test   order  by   name

这里看到了 我们创建的索引顺序是age,name

但是这里直接是order by name 相当于age是一楼 name是二楼 你直接跳过一楼上二楼 那就不合适了 就会产生内排序！

再执行：

explain select  id from test   order  by   age,name

后者性能 高于前者 按照age name（索引顺序） 进行排序 ，出现前者问题 如果有可能 需要尽快优化

2. using temporary 这个性能更差了！
   使用了临时表保存中间结果，MYSQL在查询排序时使用临时表，常用语排序order by 和 分组查询 group by

explain select  id from test   group    by   name

explain select  id from test   group    by age,  name

这个与上边的order by意思差不多 就是我们索引顺序是age name 但是如果group by name 就是跳过了age 所以出现了使用临时表（伤性能） 所以最好group by 按照index的顺序来 group by age,name

3. using index 中奖了！
   表示相应的操作使用了覆盖索引，避免访问了表的数据行，效率不错
   如果同时出现using where 表明索引被用来执行键值查找

第一种：

explain select  age  from test  where  name ='测试'

第二种：

explain select  age  name from test

using index 直接从索引读取数据 没有进行查找动作

4. 4. 
      
6. 
   

using where 使用了where

using join buffer 使用了连接缓存 join join 多了 就会有缓存 这个最大值可以调

impossible where
where 值总是false 不能用来获取任何元组

4. 
   

或者

explain select  age  name from test    where name ='2' and name ='3'

7. select tables optimized away

没有group by 子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM 存储引擎优化count(*) 操作 ， 不必等到执行阶段再进行计算，查询执行计划生成阶段即完成优化

8. distinct
   使用了distinct