目录
MySql学习专栏
3. MySQL5.7开启binlog日志，及数据恢复简单示例
在MySQL中，我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
以下讲解是居于数据库 5.7.32 版本
一 、执行计划字段描述
| 类型 | 说明 |
| select_type | 查询类型 |
| table | 输出结果集的表（表别名） |
| partitions | 匹配的分区 |
| type | 连接类型 在常用的链接类型中：system > const > eq_ref > ref > range > index > all |
| possible_keys | 可以有一个或者多个，可能用到索引不代表一定用到索引。 |
| key | 可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。 |
| key_len | 索引的长度（使用的字节数）。跟索引字段的类型、长度有关 |
| ref | 使用哪个列或者常数和索引一起从表中筛选数据 |
| rows | MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好 |
| filtered | 这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比 |
| Extra | 执行计划给出的额外的信息说明 |
二、执行计划字段描述讲解
1. select_type:表示SELECT的类型
| 类型 | 说明 |
| SIMPLE | 简单表，不使用表连接或子查询 |
| PRIMARY | 主查询，即外层的查询 |
| UNION | UNION中的第二个或者后面的查询语句 |
| SUBQUERY | 子查询中的第一个 |
| DERIVED | 衍生查询，表示在得到最终查询结果之前会用到临时表 |
我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。
DROP TABLE IF EXISTS course;
CREATE TABLE `course` (
`cid` int(3) DEFAULT NULL,
`cname` varchar(20) DEFAULT NULL,
`tid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS teacher;
CREATE TABLE `teacher` (
`tid` int(3) DEFAULT NULL,
`tname` varchar(20) DEFAULT NULL,
`tcid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS teacher_contact;
CREATE TABLE `teacher_contact` (
`tcid` int(3) DEFAULT NULL,
`phone` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `course` VALUES ('1', 'mysql', '1');
INSERT INTO `course` VALUES ('2', 'jvm', '1');
INSERT INTO `course` VALUES ('3', 'juc', '2');
INSERT INTO `course` VALUES ('4', 'spring', '3');
INSERT INTO `teacher` VALUES ('1', 'qingshan', '1');
INSERT INTO `teacher` VALUES ('2', 'jack', '2');
INSERT INTO `teacher` VALUES ('3', 'mic', '3');
INSERT INTO `teacher_contact` VALUES ('1', '13688888888');
INSERT INTO `teacher_contact` VALUES ('2', '18166669999');
INSERT INTO `teacher_contact` VALUES ('3', '17722225555');Id : 执行计划中id 字段值不同的时候，先查询 id 值大的（先大后小），id值相同时，自上而下查询
1. 查询ID不相同
查询 mysql 课程的老师手机号，如下以下SQL语句
EXPLAIN SELECT
tc.phone
FROM
teacher_contact tc
WHERE
tcid = ( SELECT tcid FROM teacher t WHERE t.tid =
( SELECT c.tid FROM course c WHERE c.cname = 'mysql' )
);查询顺序：course c → teacher t → teacher → contact tc
先查课程表，再查老师表，最后查老师联系方式表。子查询只能以这种方式进行，只有拿到内层的结果之后才能进行外层的查询。
1. 查询ID相同
查询课程 ID 为 2， 或者联系表 ID 为 3 的老师
EXPLAIN SELECT
t.tname,
c.cname,
tc.phone
FROM
teacher t,
course c,
teacher_contact tc
WHERE
t.tid = c.tid
AND t.tcid = tc.tcid
AND ( c.cid = 2 OR tc.tcid = 3 );
id 值相同时，表的查询顺序是从上往下顺序执行。例如这次查询的 id 都是 1，查询的顺序是 teacher t（3 条）——course c（4 条）——teacher_contact tc（3 条）
teacher 表插入 3 条数据后
INSERT INTO `teacher` VALUES (4, 'james', 4);
INSERT INTO `teacher` VALUES (5, 'tom', 5);
INSERT INTO `teacher` VALUES (6, 'seven', 6);
COMMIT;id 也都是 1，但是从上往下查询顺序变成了：teacher_contact tc（3 条）——teacher t（6 条）——course c（4 条）
为什么数据量不同的时候顺序会发生变化呢？这个是由笛卡尔积决定的。
举例：假如有 a、b、c 三张表，分别有 2、3、4 条数据，如果做三张表的联合查询，当查询顺序是 a→b→c 的时候，它的笛卡尔积是：2*3*4=6*4=24。如果查询顺序是 c→b→a，它的笛卡尔积是 4*3*2=12*2=24。
因为 MySQL 要把查询的结果，包括中间结果和最终结果都保存到内存，所以 MySQL会优先选择中间结果数据量比较小的顺序进行查询。所以最终联表查询的顺序是 a→b→c
这个就是为什么 teacher 表插入数据以后查询顺序会发生变化。（小表驱动大表的思想）
3 既有相同也有不同
如果 ID 有相同也有不同，就是 ID 不同的先大后小，ID 相同的从上往下。
4.SIMPLE
简单查询，不包含子查询，不包含关联查询 union。
EXPLAIN SELECT * FROM teacher; 
再看一个包含子查询的案例：
-- 查询 mysql 课程的老师手机号
EXPLAIN SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (
SELECT tcid
FROM teacher t
WHERE t.tid = (
SELECT c.tid
FROM course c
WHERE c.cname = 'mysql'
));
5. PRIMARY
子查询 SQL 语句中的主查询，也就是最外面的那层查询。
6. SUBQUERY
子查询中所有的内层查询都是 SUBQUERY 类型的。
7. DERIVED
衍生查询，表示在得到最终查询结果之前会用到临时表。例如：
-- 查询 ID 为 1 或 2 的老师教授的课程
EXPLAIN SELECT cr.cname
FROM (
SELECT * FROM course WHERE tid = 1
UNION
SELECT * FROM course WHERE tid = 2
) cr;
对于关联查询，先执行右边的 table（UNION），再执行左边的 table，类型是DERIVED
5 UNION
用到了 UNION 查询。同上例
6. UNION RESULT
主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。同上例
2. type 连接类型
| 类型 | 说明 |
| system | system 是 const 的一种特例，只有一行满足条件 |
| const | 主键索引或者唯一索引，只能查到一条数据的 SQL。 |
| ref | 查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。 |
| range | 索引范围扫描 |
| index | Full Index Scan，查询全部索引中的数据（比不走索引要快）。 |
| all | 全表扫描 |
所有的连接类型中，上面的最好，越往下越差。
在常用的链接类型中：system > const > eq_ref > ref > range > index > all
这 里 并 没 有 列 举 全 部 （ 其 他 ： fulltext 、 ref_or_null 、 index_merger 、unique_subquery、index_subquery）。
以上访问类型除了 all，都能用到索引。
2.1 const
主键索引或者唯一索引，只能查到一条数据的 SQL。
DROP TABLE IF EXISTS single_data;
CREATE TABLE single_data(
id int(3) PRIMARY KEY,
content varchar(20)
);
insert into single_data values(1,'a');
EXPLAIN SELECT * FROM single_data a where id = 1;
2.2 system
system 是 const 的一种特例，只有一行满足条件。例如：只有一条数据的系统表。
EXPLAIN SELECT * FROM mysql.proxies_priv;
2.3 eq_ref
通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。
eq_ref 是除 const 之外最好的访问类型。
先删除 teacher 表中多余的数据，teacher_contact 有 3 条数据，teacher 表有 3条数据。
DELETE FROM teacher where tid in (4,5,6);
commit;为 teacher_contact 表的 tcid（第一个字段）创建主键索引。
-- ALTER TABLE teacher_contact DROP PRIMARY KEY;
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);为 teacher 表的 tcid（第三个字段）创建普通索引。
-- ALTER TABLE teacher DROP INDEX idx_tcid;
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);执行以下 SQL 语句：
select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
小结：
以上三种 system，const，eq_ref，都是可遇而不可求的，基本上很难优化到这个状态。
2.4 ref
查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
例如：使用 tcid 上的普通索引查询：
explain SELECT * FROM teacher where tcid = 3;
2.5 range
索引范围扫描。
如果 where 后面是 between and 或 < 或 > 或 >= 或 <=或 in 这些，type 类型就为 range。
不走索引一定是全表扫描（ALL），所以先加上普通索引。
-- ALTER TABLE teacher DROP INDEX idx_tid;
ALTER TABLE teacher ADD INDEX idx_tid (tid);执行范围查询（字段上有普通索引）：
EXPLAIN SELECT * FROM teacher t WHERE t.tid <3;
-- 或
EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2
IN 查询也是 range（字段有主键索引）
EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);
2.6 index
Full Index Scan，查询全部索引中的数据（比不走索引要快）。
EXPLAIN SELECT tid FROM teacher;
2.7 all
Full Table Scan，如果没有索引或者没有用到索引，type 就是 ALL。代表全表扫描。
2.8 NULL
不用访问表或者索引就能得到结果，例如：
EXPLAIN select 1 from dual where 1=1;小结：
一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。
ALL（全表扫描）和 index（查询全部索引）都是需要优化的。
3. possible_key、 key
可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。
possible_key 可以有一个或者多个，可能用到索引不代表一定用到索引。
反过来，possible_key 为空，key 可能有值吗？
user表结构
CREATE TABLE "user" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"phone" varchar(32) DEFAULT NULL COMMENT '手机号码',
"user_name" varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
"age" int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
"gender" tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别 (0-男；1-女；2：未知)',
"position" varchar(32) NOT NULL DEFAULT '' COMMENT '职位',
"create_time" datetime DEFAULT NULL COMMENT '创建时间',
"status" tinyint(1) DEFAULT NULL COMMENT '状态',
"remark" text,
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';表上创建联合索引
ALTER TABLE user DROP INDEX comidx_name_phone;
ALTER TABLE user add INDEX comidx_name_phone (name,phone);执行计划（改成 select name 也能用到索引）：
EXPLAIN select * from user where user_name ='Liping.Zou'
结论：是有可能的（这里是覆盖索引的情况）。
如果通过分析发现没有用到索引，就要检查 SQL 或者创建索引。
4. key_len
索引的长度（使用的字节数）。跟索引字段的类型、长度有关。
5. rows
MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。
6.filtered
这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。
7. ref
使用哪个列或者常数和索引一起从表中筛选数据。
8. Extra
执行计划给出的额外的信息说明。
using index
用到了覆盖索引，不需要回表。
EXPLAIN SELECT tid FROM teacher ;using where
CREATE TABLE "user" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"phone" varchar(32) DEFAULT NULL COMMENT '手机号码',
"user_name" varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
"age" int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
"gender" tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别 (0-男；1-女；2：未知)',
"position" varchar(32) NOT NULL DEFAULT '' COMMENT '职位',
"create_time" datetime DEFAULT NULL COMMENT '创建时间',
"status" tinyint(1) DEFAULT NULL COMMENT '状态',
"remark" text,
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='用户表';使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤（跟是否使用索引没有关系）。
EXPLAIN select * from user where phone ='13866667777';
Using index condition（ 索引条件下推）
索引下推，在《深入精通Mysql（二）》深入底层剖析Mysql索引（面试必问）已经讲解过了。
using filesort
不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。（复合索引的前提）
ALTER TABLE user DROP INDEX comidx_name_phone;
ALTER TABLE user add INDEX comidx_name_phone (user_name,phone);EXPLAIN select * from user where user_name ='Liping.Zou' order by id;（order by id 引起）
using temporary
用到了临时表。例如（以下不是全部的情况）：
1、distinct 非索引列
EXPLAIN select DISTINCT(tid) from teacher t;2、group by 非索引列
EXPLAIN select tname from teacher group by tname;3、使用 join 的时候，group 任意列
EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;需要优化，例如创建复合索引。
总结一下：
模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。
通过这种方式我们可以分析语句或者表的性能瓶颈。
分析出问题之后，就是对 SQL 语句的具体优化。
比如怎么用到索引，怎么减少锁的阻塞等待，在前面文章已经讲过。
三、SQL 与索引优化
当我们的 SQL 语句比较复杂，有多个关联和子查询的时候，就要分析 SQL 语句有没有改写的方法。
举个简单的例子，一模一样的数据：
-- 大偏移量的 limit
select * from user_innodb limit 900000,10;
-- 改成先过滤 ID， 再 limit
SELECT * FROM user_innodb WHERE id >= 900000 LIMIT 10;对于具体的 SQL 语句的优化，MySQL 官网也提供了很多建议，这个是我们在分析具体的 SQL 语句的时候需要注意的，也是大家在以后的工作里面要去慢慢地积累的（这里我们就不一一地分析了）。
四、存储引擎优化
1. 存储引擎的选择
为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用 MyISAM。
临时数据用 Memeroy。常规的并发大更新多的表用 InnoDB。
2. 分区或者分表
分区不推荐。
交易历史表：在年底为下一年度建立 12 个分区，每个月一个分区。
渠道交易表：分成当日表；当月表；历史表，历史表再做分区。
3.字段定义
原则：使用可以正确存储数据的最小数据类型。
为每一列选择合适的字段类型：
3.1 整数类型
INT 有 8 种类型，不同的类型的最大存储范围是不一样的。
性别？用 TINYINT，因为 ENUM 也是整型存储。
3.2 字符类型
变长情况下，varchar 更节省空间，但是对于 varchar 字段，需要一个字节来记录长度。
固定长度的用 char，不要用 varchar。
3.3 非空
非空字段尽量定义成 NOT NULL，提供默认值，或者使用特殊值、空串代替 null。
NULL 类型的存储、优化、使用都会存在问题。
3.4 不要用外键、 触发器、 视图
降低了可读性；
影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储；
数据的完整性应该在程序中检查。
3.5 大文件存储
不要用数据库存储图片（比如 base64 编码）或者大文件；
把文件放在 NAS 上，数据库只需要存储 URI（相对路径），在应用中配置 NAS 服务器地址。
3.6 表拆分
将不常用的字段拆分出去，避免列数过多和数据量过大。
比如在业务系统中，要记录所有接收和发送的消息，这个消息是 XML 格式的，用blob 或者 text 存储，用来追踪和判断重复，可以建立一张表专门用来存储报文。
参考链接文档
1.《深入精通Mysql（七）》系列之如何通过EXPLAIN 执行计划分析SQL语句的性能瓶颈
1226
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
