explanin mysql 性能调优

mysql中的 explain命令可以用来查看sql语句是否使用了索引，用了什么索引，有没有做全表扫描。可以帮助我们优化查询语句。

explain 命令的信息有10列 ,本文主要介绍 key 、type 、Extra 这三个字段.

部门表: 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  `desc` varchar(255) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_no` (`dept_no`) USING BTREE,
  KEY `dept_name` (`dept_name`) USING BTREE,
  KEY `dept_no_name` (`dept_no`,`dept_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
BEGIN;
INSERT INTO `dept` VALUES ('1', '部门1', '1');
INSERT INTO `dept` VALUES ('2', '部门2', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

部门员工关联表:

DROP TABLE IF EXISTS `dept_emp`;
CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  UNIQUE KEY `dept_no` (`dept_no`) USING BTREE,
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept_emp
-- ----------------------------
BEGIN;
INSERT INTO `dept_emp` VALUES (1, '1', '2020-07-11', '2020-07-11');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
 

员工表:

-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `index_b_f` (`first_name`,`last_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employees
-- ----------------------------
BEGIN;
INSERT INTO `employees` VALUES (1, '2020-07-11', '张三', '张三', 'M', '2020-07-11');
INSERT INTO `employees` VALUES (2, '2020-07-11', '李四', '李四', 'F', '2020-07-11');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

1、key : mysql 使用的索引列,有时候mysql会使用效果不好的索引列,这时我们可以使用 select 语句中的force index(indexname) 来强制使用mysql 的索引列.或者使用 ignore index(indexname) 来忽略指定索引列.

explain select dept_no  from  dept force index(dept_no) ;

explain select dept_no  from  dept ignore index(dept_no) ;

2、type: 访问类型,表示mysql 数据库引擎查找表的方式.常见的方式有 :all、index、range、ref、eq_ref、const;

  all:全表扫描,表示sql 语句会将整张表的数据读取扫描一遍,效率最低,我们应当避免.

explain select *  from  dept;

  index:全索引扫描,表示mysql 会将整颗索引树,全部扫描一遍.因为二级索引树的数据,比全表的数据量要小得多,所有效率比all 类型要高一些.一般查询语句,且无where 字段,type 就为index.

explain select dept_no from  dept;

range:部分索引扫描、当查询为区间查询时,且查询字段为索引查询.这时会根据where条件对索引进行部分扫描.

explain select * from  dept where dept_no >'1';

ref:出现于 where 后的条件为‘=’时,且where 后字段是非唯一索引的单表查询或联表查询.

explain select * from  dept where dept_name ='1';

 

eq_ref:出现于 where 后的条件为‘=’时,且where 后字段是唯一索引的联表查询.
 explain select * from dept_emp ,dept
 where dept_emp.dept_no=dept.dept_no;

const:出现于where 操作符为‘=’时,,且where 后字段是唯一索引的单表查询,此时最多会匹配到一行数据.

explain select dept_no from  dept where dept_no ='1';

单从type字段考虑效率比较:const>eq_ref>ref>range>index>all; 我们并不能用type去考虑两条sql 的效率.例如type为range的查询不一定比type为index的全表查询速度要快，还要看具体的sql。要判断是否有回表操作.

列如:sqlA(explain select dept_no from dept;)  sqlB(explain select dept_name from dept where  dept_no >'1';)

sqlA 虽然用的type:index 看起要比 sqlB type:range 要慢. 但是 sqlB where 条件后,dept_no>1 用到range,查询结果是 dept_name 字段,sqlB 需要进行回表操作,索引sqlB 比sqlA 效率更低.

3、Extra: extra列会包含一些十分重要的信息，我们可以根据这些信息进行sql优化.

useing index:sql 没有where查询条件 ,使用覆盖索引,不需要回表即可拿到结果.

explain select dept_no from dept;

useing where :没有使用索引列/使用了索引列但需要回表操作,且没有使用到下推索引.

explain select dept_name from dept where  dept_no >'1';

useing index & useing where :sql 语句有查询条件,且使用覆盖索引,不需要回表即可拿到结果.

explain select dept_name ,dept_no from dept where dept_no >'1' and dept_name='部门1';

using index condition :使用索引查询,且where子句 查询条件字段与查询字段为统一字段,且开启索引下推功能,需要回表即可拿到结果.

 explain  select * from employees where first_name ='张三'  and last_name like '%张%' 

using index condition& using where :使用索引查询,sql 语句的where子句查询条件字段,存在非同一索引字段,且开始索引开启下推功能,需要回表查询即可拿到结果.

 explain  select * from employees where first_name ='张三'  and last_name like '%张%'  and gender='F'

using filesort:当语句存在order by 时,order by 字段不是索引字段,这个时候mysql 就无法利用索引进行排序.只能用排序算法额外排序,会额外消耗资源.

 explain  select * from employees   order by birth_date

using temporary :建立临时表保存中间结果.查询完有把临时表删除,会影响性能,需要优化.

 explain select b.* from dept  b
left  join dept_emp  b1   on b1.dept_no =b.dept_no
order by b1.from_date desc

有时在extra字段中会出现"Impossible WHERE noticed after reading const tables"这种描述。翻看网上资料后，个人发现这是mysql一种很怪的处理方式。

当sql 满足:

1、根据主键查询或者唯一性索引查询

2、where 后操作符号为‘=’时.

在sql语句优化阶段，mysql会先根据查询条件找到相关记录，这样，如果这条数据不存在，实际上就进行了一次全扫描，然后得出一个结论，该数据不在表中。这样对于并发较高的数据库，会加大负载。所以，如果数据不用唯一的话，普通的索引比唯一索引更好用。

索引下推 (using condition)

解释:根据联合查询的其他索引做一个筛选，筛选通过的才回表查询，减少回表次数；

配置: 

索引下推优化是默认开启的。可以通过下面的脚本控制开关

SET optimizer_switch = ‘index_condition_pushdown=off’;
SET optimizer_switch = ‘index_condition_pushdown=on’;

回表: 简单说就是mysql内部需要经过两次查询. 第一次先索引扫描，然后再通过主键去取索引中未能提供的数据。

只有在使用了索引，且Extra是Using where的情况下，才代表回表查询数据。

 

本文地址：https://blog.csdn.net/qq_29651203/article/details/107282012