MySQL数据库开启、分析慢SQL
背景：业务人员对性能要求很高，总是想让系统快速响应，遇到此类问题分析性能从两个方面入手
- 分析代码，是否有死循环、死锁、内存泄露、内存溢出等
- 分析SQL语句是否有慢SQL，SQL查询是否走了索引，SQL写的逻辑是否有问题等。
慢查询日志可以用来定位执行时间很长的查询，它是我们常用的性能分析工具，通过在开发、测试期间关注慢查询，我们可以尽量避免引入效率很差的查询，来分析一波。
说干就干-开启慢SQL
慢SQL开启情况查询
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.03 sec)
slow_query_log的value为ON时是开启了慢SQL查询
如果配置文件或启动参数没有给出file_name值，慢查询日志将默认命名为“主机名-slow.log”，如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
方法一开启慢SQL
关闭慢sql查询
mysql> SET GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)
重新开启
mysql> SET GLOBAL slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)
mysql>
慢SQL的阈值 默认10秒
mysql> SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 10.00000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> SET GLOBAL long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
方法二修改配置
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/localhost-slow.log # MySQL的日志路径，要有权限
long_query_time = 1
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost ~]#
测试慢SQL
mysql> SELECT SLEEP(12),t.* FROM testTable t WHERE test_id =1 ;
+-----------+---------+-----------------+-------------+------------+
| SLEEP(12) | test_id | test_title | test_author | test_date |
+-----------+---------+-----------------+-------------+------------+
| 0 | 1 | 这是第一条 | 王德卿 | 2020-11-19 |
+-----------+---------+-----------------+-------------+------------+
1 row in set (12.02 sec)
mysql>
查看慢SQL
[root@localhost ~]# more /var/lib/mysql/localhost-slow.log
# Time: 2020-11-19T10:09:45.122744Z
# User@Host: root[root] @ [192.168.170.1] Id: 4
# Query_time: 12.232602 Lock_time: 0.228128 Rows_sent: 1 Rows_examined: 1
use test001;
SET timestamp=1605780585;
SELECT SLEEP(12),t.* FROM testTable t where test_id =1 LIMIT 0, 1000;
慢SQL详解
- 优化策略
碰到了慢SQL不要着急，首先找到前10个慢SQL进行优化，优化完可能会有一些提升，随着前几个优化的结束，再新一轮的优化10个策略，随着用户流量的增加，可以进行多轮次优化。
- 慢查询日志的格式
- Query_time：查询耗时。
- Rows_examined：检查了多少条记录。
- Rows_sent：返回了多少行记录（结果集）。
这三条信息基本能看出一条SQL的查询成本
/usr/sbin/mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2020-11-23T10:15:11.876535+08:00
# User@Host: root[root] @ [192.168.170.1] Id: 4
# Query_time: 1.381814 Lock_time: 0.004785 Rows_sent: 1 Rows_examined: 4194304
use test001;
SET timestamp=1606097711;
SELECT COUNT(member_id) id FROM ums_member_statistics_info_slow LIMIT 0, 1000;
- Time：客户端查询时间
- root[root] ：客户端查询用户和IP
- Lock_time：等待table lock的时间，注意InnoDB的行锁等待是不会反应在这里的。
使用工具分析慢查询日志
但在一个高并发的数据库服务上，或者在做压力测试时，如果发现慢查询日志增长得非常快，很难筛选和查找里面的信息，那么在这种情况下，有如下两种选择。
- 调整阈值，先设置为较大的阈值，这样慢查询记录就很少了，等优化得差不多了，再减少阈值，不断进行优化。
- 使用命令/脚本、工具进行分析，如mysqldumpslow、pt-query-digest等。
第一种方法比较繁琐，建议大家使用第二种方法。如果优化效果比较理想，希望更进一步调优，则可以减低阈值，然后记录更多的慢查询日志，然后继续使用脚本、工具进行分析。
使用操作系统命令分析
可以使用操作系统自带的命令进行一些简单的统计，如grep、awk、wc，但不容易实现更高级的筛选排序。
- 原生态命令行
查询每秒钟查询的次数,如果你的shell编程能力很强，可以做个很全面的分析
#
[root@localhost ~]# awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' /var/lib/mysql/localhost-slow.log
2020-11-23T01:19:36.996394Z
2020-11-23T01:19:49.843742Z 1
……
2020-11-23T10:59:43.769142+08:00 1
[root@localhost ~]#
- mysqldumpslow 工具进行分析
mysqldumpslow 使用方法和技巧
[root@localhost ~]# man mysqldumpslow
#查看一下这个命令怎么使用
mysqldumpslow经常使用的参数：
-s，是order的顺序
----- al 平均锁定时间
-----ar 平均返回记录时间
-----at 平均查询时间（默认）
-----c 计数
-----l 锁定时间
-----r 返回记录
-----t 查询时间
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的
文本出处
mysqldumpslow查看文件如下
[root@localhost ~]# mysqldumpslow /var/lib/mysql/localhost-slow.log
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 9 Time=1.92s (17s) Lock=0.00s (0s) Rows=1.0 (9), root[root]@[192.168.170.1]
SELECT COUNT(*) id FROM ums_member_statistics_info_slow WHERE consume_amount =N LIMIT N, N
……
Count: 1 Time=1.25s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[192.168.170.1]
select count(*) id from ums_member_statistics_info_slow where member_id =N LIMIT N, N
[root@localhost ~]#
访问时间最长的10个sql语句的命令如下。
[root@localhost ~]# mysqldumpslow -t 10 /var/lib/mysql/localhost-slow.log
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 9 Time=1.92s (17s) Lock=0.00s (0s) Rows=1.0 (9), root[root]@[192.168.170.1]
SELECT COUNT(*) id FROM ums_member_statistics_info_slow WHERE consume_amount =N LIMIT N, N
……
Count: 1 Time=1.25s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[192.168.170.1]
select count(*) id from ums_member_statistics_info_slow where member_id =N LIMIT N, N
Died at /usr/bin/mysqldumpslow line 167, <> chunk 13.
[root@localhost ~]#
访问次数最多的10个sql语句的命令如下。
[root@localhost ~]# mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 9 Time=1.92s (17s) Lock=0.00s (0s) Rows=1.0 (9), root[root]@[192.168.170.1]
SELECT COUNT(*) id FROM ums_member_statistics_info_slow WHERE consume_amount =N LIMIT N, N
……
Count: 1 Time=1.25s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[192.168.170.1]
select count(*) id from ums_member_statistics_info_slow where member_id =N LIMIT N, N
Died at /usr/bin/mysqldumpslow line 167, <> chunk 13.
[root@localhost ~]#
访问记录集最多的10个sql语句的命令如下。
[root@localhost ~]# mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
对查询到的条件进行过滤 -g参数
[root@localhost ~]# mysqldumpslow -s c -t 10 -g "count\(\*" /var/lib/mysql/localhost-slow.log
还有很多使用方法，根据场景进行编写使用man mysqldumpslow 进行查询。
3.pt-query-digest工具使用
pt-query-digest 比mysqldumpslow更强大、更友好，可以生成一份比mysqldumpslow更好的可读性好的报告。
pt-query-digest也是最应该被掌握的一个工具。它可以分析MySQL的各种日志，如慢查询日志、generel日志，也可以分析SHOW PROCESSLIST的输出。配合tcpdump我们还可以对线上数据库流量进行采样，实时监控数据库流量，及时发现性能问题。
安装如下
[root@localhost ~]# wget www.percona.com/get/pt-query-digest
chmod u+x pt-query-digest--2020-11-23 13:18:20-- http://www.percona.com/get/pt-query-digest
Resolving www.percona.com (www.percona.com)... 74.121.199.235
Connecting to www.percona.com (www.percona.com)|74.121.199.235|:80...
……
Connecting to www.percona.com (www.percona.com)|74.121.199.235|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 529350 (517K) [text/plain]
Saving to: ‘pt-query-digest’
100%[=======================================================================================================================================================>] 529,350 14.9KB/s in 33s
2020-11-23 13:18:56 (15.5 KB/s) - ‘pt-query-digest’ saved [529350/529350]
[root@localhost ~]# chmod u+x pt-query-digest
[root@localhost ~]# ll
-rwxr--r--. 1 root root 529350 Sep 2 22:55 pt-query-digest
[root@localhost ~]#
牛刀小试
[root@localhost ~]# ./pt-query-digest /var/lib/mysql/localhost-slow.log
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./pt-query-digest line 75.
BEGIN failed--compilation aborted at ./pt-query-digest line 75.
哎呦我嘞个去，翻车了！
打工人，不抛弃不放弃，go on
[root@localhost ~]# yum install -y perl-CPAN perl-Time-HiRes
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
……
systemtap-sdt-devel.x86_64 0:4.0-13.el7
Complete!
[root@localhost ~]# ./pt-query-digest /var/lib/mysql/localhost-slow.log
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./pt-query-digest line 2492.
BEGIN failed--compilation aborted at ./pt-query-digest line 2492.
继续翻车
抛弃了就不是打工人，加个油继续前进
[root@localhost ~]# yum -y install perl-Digest-MD5
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Resolving Dependencies
……
Installed:
perl-Digest-MD5.x86_64 0:2.52-3.el7
Complete!
[root@localhost ~]# ./pt-query-digest /var/lib/mysql/localhost-slow.log
#该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 100ms user time, 60ms system time, 19.24M rss, 175.66M vsz
#工具执行时间
# Current date: Mon Nov 23 13:24:52 2020
#运行分析工具的主机名
# Hostname: localhost.localdomain
#被分析的文件名
# Files: /var/lib/mysql/localhost-slow.log
#语句总数量，唯一的语句数量，QPS，并发数
# Overall: 16 total, 5 unique, 0.00 QPS, 0.00x concurrency _______________
#日志记录的时间范围
# Time range: 2020-11-23T01:19:36 to 2020-11-23T10:59:43
#属性 总计 最小 最大 平均 95% 标准 中等
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
#语句执行时间
# Exec time 28s 1s 3s 2s 2s 439ms 2s
#锁占用时间
# Lock time 30ms 90us 10ms 2ms 7ms 3ms 144us
#发送到客户端的行数
# Rows sent 16 1 1 1 1 0 1
#select语句扫描行数
# Rows examine 64.00M 4.00M 4.00M 4.00M 4.00M 0 4.00M
#查询的字符数
# Query size 1.40k 79 94 89.31 92.72 6.84 92.72
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== ================================= ============= ===== ====== =====
# 1 0x540AF2BCF2C5367E13CD2028259D... 17.3185 61.2% 9 1.9243 0.13 SELECT ums_member_statistics_info_slow
# 2 0x6CEE5E9C52ECE04F9E6DDE07294F... 5.1490 18.2% 3 1.7163 0.00 SELECT ums_member_statistics_info_slow
# 3 0x68FF704A8D7486239579992F4BA6... 3.2095 11.3% 2 1.6047 0.13 SELECT ums_member_statistics_info_slow
# 4 0xE333451ECC5E585FA5E08C9ADC5D... 1.3818 4.9% 1 1.3818 0.00 SELECT ums_member_statistics_info_slow
# MISC 0xMISC 1.2537 4.4% 1 1.2537 0.0 <1 ITEMS>
# Query 1: 0.00 QPS, 0.00x concurrency, ID 0x540AF2BCF2C5367E13CD2028259D8707 at byte 77767
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.13
# Time range: 2020-11-23T01:19:49 to 2020-11-23T10:59:43
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 56 9
# Exec time 61 17s 1s 3s 2s 3s 493ms 2s
# Lock time 78 23ms 90us 10ms 3ms 10ms 4ms 131us
# Rows sent 56 9 1 1 1 1 0 1
# Rows examine 56 36.00M 4.00M 4.00M 4.00M 4.00M 0 4.00M
# Query size 59 846 94 94 94 94 0 94
# String:
#数据库
# Databases test001
#客户端IP
# Hosts 192.168.170.1
#执行用户
# Users root
#展示查询时间分布，此处是大部分在1s左右
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `test001` LIKE 'ums_member_statistics_info_slow'\G
# SHOW CREATE TABLE `test001`.`ums_member_statistics_info_slow`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) id FROM ums_member_statistics_info_slow WHERE consume_amount =1 LIMIT 0, 1000\G
……
#此处省略一些
[root@localhost ~]# ^C
[root@localhost ~]#
安装pt-query-digestn需要perl-CPAN perl-Time-HiRes perl-Digest-MD5三个模块
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== ================================= ============= ===== ====== =====
# 1 0x540AF2BCF2C5367E13CD2028259D... 17.3185 61.2% 9 1.9243 0.13 SELECT ums_member_statistics_info_slow
# 2 0x6CEE5E9C52ECE04F9E6DDE07294F... 5.1490 18.2% 3 1.7163 0.00 SELECT ums_member_statistics_info_slow
# 3 0x68FF704A8D7486239579992F4BA6... 3.2095 11.3% 2 1.6047 0.13 SELECT ums_member_statistics_info_slow
# 4 0xE333451ECC5E585FA5E08C9ADC5D... 1.3818 4.9% 1 1.3818 0.00 SELECT ums_member_statistics_info_slow
# MISC 0xMISC 1.2537 4.4% 1 1.2537 0.0 <1 ITEMS>
输出格式的解释具体如下。
- Rank：所有查询日志分析完毕后，此查询的排序。
- Query ID：查询的标识字符串。可以搜索这个字符串以快速定位到慢查询语句。
- Response time：总的响应时间，以及总占比，应优化占比较高的查询，对于比例较小的查询一般可以忽略，不进行优化。
- Calls：查询被调用执行的次数。
- R/Call：每次执行的平均响应时间。
- Apdx：应用程序的性能指数得分，响应时间越长，得分越低。
- V/M：响应时间的方差均值比。可说明样本的分散程度，这个值越大，往往是越值得考虑优化的对象。
- Item：查询的简单显示，包含了查询涉及的表。对于报告中的如下输出，我们可以利用偏移量到慢查询日志里定位具体的sql语句。
# Query 1: 0.00 QPS, 0.00x concurrency, ID 0x540AF2BCF2C5367E13CD2028259D8707 at byte 77767
定位到具体日志的方法，根据字节定位到具体慢SQL的位置
[root@localhost ~]# tail -c +77767 /var/lib/mysql/localhost-slow.log |head
# Time: 2020-11-23T02:01:29.356794Z
# User@Host: root[root] @ [192.168.170.1] Id: 4
# Query_time: 2.846838 Lock_time: 0.000133 Rows_sent: 1 Rows_examined: 4194304
SET timestamp=1606096889;
SELECT COUNT(*) id FROM ums_member_statistics_info_slow WHERE consume_amount =1 LIMIT 0, 1000;
/usr/sbin/mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2020-11-23T02:09:21.682141Z
# User@Host: root[root] @ [192.168.170.1] Id: 4
[root@localhost ~]#
此工具还能生成执行计划的SQL语句，可以根据生成的计划进行确定调优
用法示例：
- 重定向到新的日志文件
./pt-query-digest /var/lib/mysql/localhost-slow.log > slow_report1.log
- 分析最近12小时内的查询：
./pt-query-digest --since=12h /var/lib/mysql/localhost-slow.log > slow_report2.log
- 分析指定时间范围内的查询：
./pt-query-digest /var/lib/mysql/localhost-slow.log --since '2020-11-21 09:30:00' --until '2020-11-23 10:00:00'> slow_report3.log
- 分析指含有select语句的慢查询
./pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/localhost-slow.log> slow_report4.log
- 针对某个用户的慢查询
./pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/localhost-slow.log> slow_report5.log
- 查询所有所有的全表扫描或full join的慢查询
./pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/localhost-slow.log> slow_report6.log
- 分析tcpdump的输出。
分析执行SQL的频率，一般在高峰期取样，一定要记得关闭tcpdump，因为生成的文件可能会很大。首先运行命令。
捕获tcp日志
[root@localhost ~]# nohup tcpdump -i eno16777736 port 3306 -s 65535 -x -nn -q -tttt > db_sql_new.log &
通过tcp包获取查询语句
[root@localhost ~]# ./pt-query-digest --type=tcpdump --watch-server 192.168.170.129:3306 db_sql_new.log > report_to_develper.rtf
- 分析genlog日志
[root@localhost ~]# ./pt-query-digest --type genlog /var/lib/mysql/localhost.log
genlog开启参见
- binlog日志分析
[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000429 > /root/binlog.sql
[root@localhost ~]# ./pt-query-digest --type binlog /root/binlog.sql
日志分析有很多种办法，最终找到根源所在即可，希望见到mysql慢的问题，能有个清晰的思路。
MySQL慢查询（二） - pt-query-digest详解慢查询日志
分析SQL语句
找到SQL语句之后对SQL进行分析，一般使用EXPLAIN工具进行分析查看执行计划
找到慢SQL在select之前加上EXPLAIN，即可得到执行计划。
类似于
explain select ……;
EXPLAIN命令还有如下两种变体。
EXPLAIN EXTENDED SELECT……
以上命令将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS可得到被MySQL优化器优化后的查询语句。
EXPLAIN PARTITIONS SELECT……
以上命令用于分区表的EXPLAIN命令。
创建数据
-- 创建user表
create table user(
id int,
name varchar(20),
role_id int,
primary key(id)
)engine=innodb default charset=utf8;
-- 创建role表
create table role(
id int,
name varchar(20),
primary key(id)
)engine=innodb default charset=utf8;
mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
- id
id包含一组数字，表示查询中执行SELECT子句或操作表的顺序。
如果id相同，则执行顺序由上至下，例如：
-- 左关联
mysql> explain select * from user a left join user b on a.id=b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test001.a.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
-- 右关联
mysql> explain select * from user a right join user b on a.id=b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test001.b.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
mysql>
通过left join 和 right join 验证；id一样（注意执行计划的table列），left join 先扫描a表，再扫描b表；right join 先扫描b表，再扫描a表
如果id不同，id越大优先级越高，越先被执行
mysql> explain select * from user where role_id=(select id from role where name='开发');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 2 | SUBQUERY | role | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql>
我们编写查询角色为开发的用户；可以知道先查询角色name为开发角色id，查询序列号为2；再根据角色id查询用户，查询序列号为1；
- select_type
select_type表示查询中每个SELECT子句的类型（是简单还是复杂）。输出结果类似如下，下面是对select_type的详细说明。
- SIMPLE：查询中不包含子查询或UNION。
mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
- 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
mysql> explain select * from user where role_id=(select id from role where name='开发');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 2 | SUBQUERY | role | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql>
- 在SELECT或WHERE列表中若包含了子查询，则该子查询被标记为SUBQUERY。
mysql> desc select * from user where role_id=(select id from role where name='开发');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| 2 | SUBQUERY | role | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql>
- 从UNION表中获取结果的SELECT将被标记为UNION RESULT。
mysql> desc select * from user where name='Java' union select * from user where role_id=1;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| 2 | UNION | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
mysql>
- DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
mysql> desc select * from user a
-> where id in (
-> select b.id from user b where b.id=a.id union
-> select c.id from role c where c.id=a.role_id
-> );
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------+
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test001.a.id | 1 | 100.00 | Using where; Using index |
| 3 | DEPENDENT UNION | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test001.a.role_id | 1 | 100.00 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------+
4 rows in set, 3 warnings (0.00 sec)
mysql>
- 在FROM列表中包含的子查询将被标记为DERIVED（衍生）。
mysql> desc select * from ( select * from user where name='Java' union select * from user where role_id=1 ) a;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 2 | DERIVED | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| 3 | UNION | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)
mysql>
- 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，则外层SELECT将被标记为DERIVED。
mysql> desc select * from ( select * from user where name='Java' union select * from user where role_id=1 ) a;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 2 | DERIVED | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| 3 | UNION | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)
mysql>
不管是DBA、开发、还是运维人员，对Linux命令和诊断工具要熟练掌握，等到用的时候再去学习，为时已晚。特别是现在对性能要求巨高的情况下，一定要掌握好分析性能的命令。
2340
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
