欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

explanin mysql 性能调优

程序员文章站 2022-05-15 11:33:37
mysql中的 explain命令可以用来查看sql语句是否使用了索引,用了什么索引,有没有做全表扫描。可以帮助我们优化查询语句。explain 命令的信息有10列 ,本文主要介绍 key 、type 、Extra 这三个字段.1、key : mysql 使用的索引列,有时候mysql会使用效果不好的索引列,这时我们可以使用 select 语句中的force index(indexname) 来强制使用mysql 的索引列.或者使用 ignore index(indexname) 来忽略指定索引列....

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) ;

explanin mysql 性能调优

explain select dept_no  from  dept ignore index(dept_no) ;

explanin mysql 性能调优

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

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

explain select *  from  dept;

explanin mysql 性能调优

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

explain select dept_no from  dept;
explanin mysql 性能调优

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

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

explanin mysql 性能调优

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

explain select * from  dept where dept_name ='1';
explanin mysql 性能调优

 

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

explanin mysql 性能调优

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

explain select dept_no from  dept where dept_no ='1';
explanin mysql 性能调优

单从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;

explanin mysql 性能调优

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

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

explanin mysql 性能调优

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

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

explanin mysql 性能调优

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

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

explanin mysql 性能调优

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

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

explanin mysql 性能调优

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

 explain  select * from employees   order by birth_date

explanin mysql 性能调优

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

explanin mysql 性能调优

有时在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