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