8000 [docs add]MySQL执行计划分析 · OceanChen2012/JavaGuide@48eab7d · GitHub
[go: up one dir, main page]
Skip to content

Commit 48eab7d

Browse files
SnailclimbSnailclimb
committed
[docs add]MySQL执行计划分析
1 parent 653d7b5 commit 48eab7d

File tree

7 files changed

+216
-26
lines changed

7 files changed

+216
-26
lines changed

‎README.md

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -189,7 +189,7 @@ JVM 这部分内容主要参考 [JVM 虚拟机规范-Java8 ](https://docs.oracle
189189
### 基础
190190

191191
- [数据库基础知识总结](./docs/database/basis.md)
192-
- [NoSQL基础知识总结](./docs/database/nosql.md)
192+
- [NoSQL 基础知识总结](./docs/database/nosql.md)
193193
- [字符集详解](./docs/database/character-set.md)
194194

195195
### MySQL
@@ -201,13 +201,14 @@ JVM 这部分内容主要参考 [JVM 虚拟机规范-Java8 ](https://docs.oracle
201201

202202
**重要知识点：**
203203

204-
- [MySQL数据库索引总结](./docs/database/mysql/mysql-index.md)
205-
- [事务隔离级别(图文详解)](./docs/database/mysql/transaction-isolation-level.md)
204+
- [MySQL索引详解](./docs/database/mysql/mysql-index.md)
205+
- [MySQL事务隔离级别图文详解)](./docs/database/mysql/transaction-isolation-level.md)
206206
- [MySQL三大日志(binlog、redo log和undo log)详解](./docs/database/mysql/mysql-logs.md)
207-
- [InnoDB存储引擎对MVCC的实现](./docs/database/mysql/innodb-implementation-of-mvcc.md)
208-
- [SQL语句在MySQL中的执行过程](./docs/database/mysql/how-sql-executed-in-mysql.md)
209-
- [关于数据库中如何存储时间的一点思考](./docs/database/mysql/some-thoughts-on-database-storage-time.md)
210-
- [MySQL中的隐式转换造成的索引失效](./docs/database/mysql/index-invalidation-caused-by-implicit-conversion.md)
207+
- [InnoDB 存储引擎对 MVCC 的实现](./docs/database/mysql/innodb-implementation-of-mvcc.md)
208+
- [SQL 语句在 MySQL 中的执行过程](./docs/database/mysql/how-sql-executed-in-mysql.md)
209+
- [MySQL执行计划分析](./docs/database/mysql/mysql-query-execution-plan.md)
210+
- [MySQL 时间类型数据存储建议](./docs/database/mysql/some-thoughts-on-database-storage-time.md)
211+
- [MySQL 隐式转换造成索引失效](./docs/database/mysql/index-invalidation-caused-by-implicit-conversion.md)
211212

212213
### Redis
213214

‎docs/.vuepress/sidebar.ts

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -229,7 +229,7 @@ export const sidebarConfig = sidebar({
229229
{
230230
text: "基础",
231231
icon: "basic",
232-
children: ["basis", "nosql","character-set"],
232+
children: ["basis", "nosql", "character-set"],
233233
},
234234
{
235235
text: "MySQL",
@@ -243,11 +243,15 @@ export const sidebarConfig = sidebar({
243243
icon: "star",
244244
collapsible: true,
245245
children: [
246-
"mysql-index",
247-
"mysql-logs",
246+
"mysql-index",
247+
{
248+
text: "MySQL三大日志详解",
249+
link: "mysql-logs",
250+
},
248251
"transaction-isolation-level",
249252
"innodb-implementation-of-mvcc",
250253
"how-sql-executed-in-mysql",
254+
"mysql-query-execution-plan",
251255
"some-thoughts-on-database-storage-time",
252256
"index-invalidation-caused-by-implicit-conversion",
253257
],
@@ -280,10 +284,7 @@ export const sidebarConfig = sidebar({
280284
text: "MongoDB",
281285
prefix: "mongodb/",
282286
icon: "mongodb",
283-
children: [
284-
"mongodb-questions-01",
285-
"mongodb-questions-02",
286-
],
287+
children: ["mongodb-questions-01", "mongodb-questions-02"],
287288
},
288289
],
289290
},

‎docs/database/mysql/index-invalidation-caused-by-implicit-conversion.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
---
2-
title: MySQL中的隐式转换造成的索引失效
2+
title: MySQL隐式转换造成索引失效
33
category: 数据库
44
tag:
55
- MySQL

‎docs/database/mysql/mysql-query-execution-plan.md

Lines changed: 142 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,142 @@
1+
---
2+
title: MySQL执行计划分析
3+
category: 数据库
4+
tag:
5+
- MySQL
6+
head:
7+
- - meta
8+
- name: keywords
9+
content: MySQL基础,MySQL执行计划,EXPLAIN,查询优化器
10+
- - meta
11+
- name: description
12+
content: 执行计划是指一条 SQL 语句在经过MySQL 查询优化器的优化会后，具体的执行方式。优化 SQL 的第一步应该是读懂 SQL 的执行计划。
13+
---
14+
15+
> 本文来自公号 MySQL 技术，JavaGuide 对其做了补充完善。原文地址：https://mp.weixin.qq.com/s/d5OowNLtXBGEAbT31sSH4g
16+
17+
优化 SQL 的第一步应该是读懂 SQL 的执行计划。本篇文章，我们一起来学习下 MySQL `EXPLAIN` 执行计划相关知识。
18+
19+
## 什么是执行计划？
20+
21+
**执行计划** 是指一条 SQL 语句在经过 **MySQL 查询优化器** 的优化会后，具体的执行方式。
22+
23+
执行计划通常用于 SQL 性能分析、优化等场景。通过 `EXPLAIN` 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。
24+
25+
## 如何获取执行计划？
26+
27+
MySQL 为我们提供了 `EXPLAIN` 命令，来获取执行计划的相关信息。
28+
29+
需要注意的是，`EXPLAIN` 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。
30+
31+
`EXPLAIN` 执行计划支持 `SELECT`、`DELETE`、`INSERT`、`REPLACE` 以及 `UPDATE` 语句。我们一般多用于分析 `SELECT` 查询语句，使用起来非常简单，语法如下：
32+
33+
```sql
34+
EXPLAIN + SELECT 查询语句；
35+
```
36+
37+
我们简单来看下一条查询语句的执行计划：
38+
39+
```sql
40+
mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
41+
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
42+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
43+
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
44+
| 1 | PRIMARY | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | Using where |
45+
| 2 | SUBQUERY | dept_emp | NULL | index | PRIMARY,dept_no | PRIMARY | 16 | NULL | 331143 | 100.00 | Using index |
46+
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
47+
```
48+
49+
可以看到，执行计划结果中共有 12 列，各列代表的含义总结如下表：
50+
51+
| **列名** | **含义** |
52+
| ------------- | -------------------------------------------- |
53+
| id | SELECT查询的序列标识符 |
54+
| select_type | SELECT关键字对应的查询类型 |
55+
| table | 用到的表名 |
56+
| partitions | 匹配的分区，对于未分区的表，值为 NULL |
57+
| type | 表的访问方法 |
58+
| possible_keys | 可能用到的索引 |
59+
| key | 实际用到的索引 |
60+
| key_len | 所选索引的长度 |
61+
| ref | 当使用索引等值查询时，与索引作比较的列或常量 |
62+
| rows | 预计要读取的行数 |
63+
| filtered | 按表条件过滤后，留存的记录数的百分比 |
64+
| Extra | 附加信息 |
65+
66+
## 如何分析 EXPLAIN 结果？
67+
68+
为了分析 `EXPLAIN` 语句的执行结果，我们需要搞懂执行计划中的重要字段。
69+
70+
### id
71+
72+
SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELELCT 语句的顺序。
73+
74+
id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。
75+
76+
### select_type
77+
78+
查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：
79+
80+
- **SIMPLE**：简单查询，不包含 UNION 或者子查询。
81+
- **PRIMARY**：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
82+
- **SUBQUERY**：子查询中的第一个 SELECT。
83+
- **UNION**：在 UNION 语句中，UNION 之后出现的 SELECT。
84+
- **DERIVED**：在 FROM 中出现的子查询将被标记为 DERIVED。
85+
- **UNION RESULT**：UNION 查询的结果。
86+
87+
### table
88+
89+
查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：
90+
91+
- **`<unionM,N>`** : 本行引用了 id 为 M 和 N 的行的 UNION 结果；
92+
- **`<derivedN>`** : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
93+
-**`<subqueryN>`** : 本行引用了 id 为 N 的表所产生的的物化子查询结果。
94+
95+
### type（重要）
96+
97+
查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
98+
99+
常见的几种类型具体含义如下：
100+
101+
- **system**：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
102+
- **const**：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
103+
- **eq_ref**：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
104+
- **ref**：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
105+
- **index_merge**：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
106+
- **range**：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
107+
- **index**：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
108+
- **ALL**：全表扫描。
109+
110+
### possible_keys
111+
112+
possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。
113+
114+
### key（重要）
115+
116+
key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。
117+
118+
### key_len
119+
120+
key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。
121+
122+
### rows
123+
124+
rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。
125+
126+
### Extra（重要）
127+
128+
这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：
129+
130+
- **Using filesort**：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
131+
- **Using temporary**：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
132+
- **Using index**：表明查询使用了覆盖索引，不用回表，查询效率非常高。
133+
- **Using index condition**：表示查询优化器选择使用了索引条件下推这个特性。
134+
- **Using where**：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
135+
- **Using join buffer (Block Nested Loop)**：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。
136+
137+
这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。
138+
139+
## 参考
140+
141+
- https://dev.mysql.com/doc/ B41A refman/5.7/en/explain-output.html
142+
- https://juejin.cn/post/6953444668973514789

‎docs/database/mysql/mysql-questions-01.md

Lines changed: 49 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ head:
1515

1616
## MySQL 基础
1717

18-
### 关系型数据库介绍
18+
### 什么是关系型数据库？
1919

2020
顾名思义，关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。
2121

@@ -29,7 +29,7 @@ head:
2929

3030
MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite） ......。
3131

32-
### MySQL 介绍
32+
### 什么是 MySQL？
3333

3434
![](https://img-blog.csdnimg.cn/20210327143351823.png)
3535

@@ -590,11 +590,56 @@ MySQL 提供了两个方法来处理 ip 地址
590590
591591
### 有哪些常见的 SQL 优化手段？
592592
593-
[《Java 面试指北》](https://www.yuque.com/docs/share/f37fc804-bfe6-4b0d-b373-9c462188fec7) 的「技术面试题篇」有一篇文章详细介绍了常见的 SQL 优化手段，非常全面，清晰易懂！
593+
[《Java 面试指北》(付费)](https://javaguide.cn/zhuanlan/java-mian-shi-zhi-bei.html) 的 **「技术面试题篇」** 有一篇文章详细介绍了常见的 SQL 优化手段，非常全面，清晰易懂！
594594
595595
![常见的 SQL 优化手段](https://guide-blog-images.oss-cn-shenzhen.aliyuncs.com/javamianshizhibei/javamianshizhibei-sql-optimization.png)
596596
597-
## 书籍推荐
597+
### 如何分析 SQL 的性能？
598+
599+
我们可以使用 `EXPLAIN` 命令来分析 SQL 的 **执行计划** 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。
600+
601+
`EXPLAIN` 并不会真的去执行相关的语句，而是通过 **查询优化器** 对语句进行分析，找出最优的查询方案，并显示对应的信息。
602+
603+
`EXPLAIN` 适用于 `SELECT`, `DELETE`, `INSERT`, `REPLACE`, 和 `UPDATE`语句，我们一般分析 `SELECT` 查询较多。
604+
605+
我们这里简单来演示一下 `EXPLAIN` 的使用。
606+
607+
`EXPLAIN` 的输出格式如下：
608+
609+
```sql
610+
mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
611+
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
612+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
613+
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
614+
| 1 | SIMPLE | cus_order | NULL | ALL | NULL | NULL | NULL | NULL | 997572 | 100.00 | Using filesort |
615+
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
616+
1 row in set, 1 warning (0.00 sec)
617+
```
618+
619+
各个字段的含义如下：
620+
621+
| **列名** | **含义** |
622+
| ------------- | -------------------------------------------- |
623+
| id | SELECT查询的序列标识符 |
624+
| select_type | SELECT关键字对应的查询类型 |
625+
| table | 用到的表名 |
626+
| partitions | 匹配的分区，对于未分区的表，值为 NULL |
627+
| type | 表的访问方法 |
628+
| possible_keys | 可能用到的索引 |
629+
| key | 实际用到的索引 |
630+
| key_len | 所选索引的长度 |
631+
| ref | 当使用索引等值查询时，与索引作比较的列或常量 |
632+
| rows | 预计要读取的行数 |
633+
| filtered | 按表条件过滤后，留存的记录数的百分比 |
634+
| Extra | 附加信息 |
635+
636+
篇幅问题，我这里只是简单介绍了一下 MySQL 执行计划，详细介绍请看：[SQL 的执行计划](./mysql-query-execution-plan.md)这篇文章。
637+
638+
### 读写分离和分库分表了解吗？
639+
640+
读写分离和分库分表相关的问题比较多，于是，我单独写了一篇文章来介绍： [读写分离和分库分表详解](https://javaguide.cn/high-performance/read-and-write-separation-and-library-subtable.html)。
641+
642+
## MySQL 书籍推荐
598643
599644
参见：[https://javaguide.cn/books/database.html#mysql](https://javaguide.cn/books/database.html#mysql) 。
600645

‎docs/database/mysql/some-thoughts-on-database-storage-time.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
---
2-
title: MySQL数据库时间类型数据存储建议
2+
title: MySQL时间类型数据存储建议
33
category: 数据库
44
tag:
55
- MySQL

‎docs/home.md

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -202,13 +202,14 @@ JVM 这部分内容主要参考 [JVM 虚拟机规范-Java8 ](https://docs.oracle
202202

203203
**重要知识点：**
204204

205-
- [MySQL数据库索引总结](./database/mysql/mysql-index.md)
206-
- [事务隔离级别(图文详解)](./database/mysql/transaction-isolation-level.md)
205+
- [MySQL索引详解](./database/mysql/mysql-index.md)
206+
- [MySQL事务隔离级别图文详解)](./database/mysql/transaction-isolation-level.md)
207207
- [MySQL三大日志(binlog、redo log和undo log)详解](./database/mysql/mysql-logs.md)
208-
- [InnoDB存储引擎对MVCC的实现](./database/mysql/innodb-implementation-of-mvcc.md)
209-
- [SQL语句在MySQL中的执行过程](./database/mysql/how-sql-executed-in-mysql.md)
210-
- [关于数据库中如何存储时间的一点思考](./database/mysql/some-thoughts-on-database-storage-time.md)
211-
- [MySQL中的隐式转换造成的索引失效](./database/mysql/index-invalidation-caused-by-implicit-conversion.md)
208+
- [InnoDB 存储引擎对 MVCC 的实现](./database/mysql/innodb-implementation-of-mvcc.md)
209+
- [SQL 语句在 MySQL 中的执行过程](./database/mysql/how-sql-executed-in-mysql.md)
210+
- [MySQL执行计划分析](./database/mysql/mysql-query-execution-plan.md)
211+
- [MySQL 时间类型数据存储建议](./database/mysql/some-thoughts-on-database-storage-time.md)
212+
- [MySQL 隐式转换造成索引失效](./database/mysql/index-invalidation-caused-by-implicit-conversion.md)
212213

213214
### Redis
214215

0 commit comments

Comments
 (0)
0