文章目录
MySQL性能分析
一、show status
在MySQL中,可以使用 show status
语句查询一些MySQL数据库服务器的 性能参数、执行频率 。
# show status 语法
show [global|session] status like '参数';
一些常用的性能参数如下:
Connections 连接MySQL服务器的次数。
Uptime MySQL服务器的上线时间。
Slow_queries 慢查询的次数。
Innodb_rows_read Select查询返回的行数
Innodb_rows_inserted 执行INSERT操作插入的行数
Innodb_rows_updated 执行UPDATE操作更新的行数
Innodb_rows_deleted 执行DELETE操作删除的行数
Com_select 查询操作的次数。
Com_insert 插入操作的次数。对于批量插入的 INSERT 操作,只累加一次。
Com_update 更新操作的次数。
Com_delete 删除操作的次数。
二、last_query_cost
通过 last_query_cost
可以查看上一个Query的页的数量(需要先执行 select 语句 再执行下列语句)
# 上一次 查询 页的数量
show status like 'last_query_cost';
三、慢查询日志
MySQL的慢查询日志会记录响应时间超过阈值
的语句,方便我们对查询进行优化。
1、慢查询日志的开启
mysql的慢查询日志记录默认是关闭的
。
# 查看是否开启慢查询日志(默认关闭)
show variables like '%slow_query_log%';
# 开启慢查询日志(mysql重启后失效)
set global slow_query_log=1;
如果想要mysql重启后依然生效,需要修改配置文件 my.cnf
# 开启慢查询日志(0关闭,1开启,默认关闭)
slow_query_log=1
# 保存慢查询日志的地址
slow_query_log_file=/usr/local/mysql/logs/slow.log
2、慢查询日志的阈值
# 查看慢查询的阈值,单位:s(默认10s)
show variables like 'long_query_time';
# 设置慢查询的阈值,单位:s(重新连接后生效,重启mysql后失效)
set global long_query_time=4;
如果想要mysql重启后依然生效,需要修改配置文件 my.cnf
# 定义执行时间超过多少秒为慢查询(默认为10)
long_query_time = 4
3、慢查询日志的相关配置
慢查询日志的开启与阈值
# 开启慢查询日志(0关闭,1开启,默认关闭)
slow_query_log=1
# 保存慢查询日志的地址
slow_query_log_file=/usr/local/mysql/logs/slow.log
# 定义执行时间超过多少秒为慢查询(默认为10)
long_query_time = 4
默认情况下,慢查询日志不记录 不使用索引进行查询的语句 和 管理语句(如alter table、create index等)
# 是否记录管理语句(0关闭,1开启,默认0)
log_slow_admin_statements=1
# 是否记录不使用索引进行查询的语句(0不记录,1记录。默认不记录)
log_queries_not_using_indexes=1
# 表示每分钟允许记录的不使用索引进行查询的语句的次数(默认为0,不限制)
log_throttle_queries_not_using_indexes=10
其他慢查询相关的设置
# 扫描少于200行不记录(默认为0)
min_examined_row_limit=200
# 从机是否开启慢查询(0关闭,1开启,默认0)
log_slow_slave_statements=1
# 日志记录形式('file'表示存入文件,'table'表示存入系统表。默认为file,开销较低)
log_output=file
4、慢查询日志的模拟
-- 模拟慢查询(这条语句会执行4秒左右)
select sleep(4);
-- 查看有几条慢查询
show global status like '%slow_queries%';
下图是慢查询日志的记录
主要功能是, 统计不同慢sql的:
1. Time 慢sql执行时间
2. User@Host 用户信息
3. Query_time 执行查询的时间
4. Lock_time 等待锁的时间
5. Rows_sent 发送给客户端的行总数
6. Rows_examined 扫描的行总数
7. sql 执行的sql(会格式化, 比如 limit 1,5 会用 limit N,N 表示)
5、慢查询日志的分析工具
慢查询日志里往往有许多SQL,不可能一条一条SQL看过去,我们需要将所有的慢SQL分组统计后在进行分析。
工具/功能 | 一般统计信息 | 高级统计信息 | 脚本 | 优势 |
---|---|---|---|---|
mysqldumpslow | 支持 | 不支持 | perl | mysql官方自带 |
mysqlsla | 支持 | 支持 | perl | 功能强大,数据报表齐全,定制化能力强. |
mysql-explain-slow-log | 支持 | 不支持 | perl | 无 |
mysql-log-filter | 支持 | 部分支持 | python or php | 不失功能的前提下,保持输出简洁 |
myprofi | 支持 | 不支持 | php | 非常精简 |
以 mysqldumpslow 的使用为例:
# 查看mysqldumpslow的帮助信息
$ mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count # 访问次数
l: lock time # 锁定时间
r: rows sent # 返回记录
t: query time # 查询时间
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries # 返回前多少条数据
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string # 正则表达式(大小写不敏感)
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
举例:我们想要按照查询时间排序,查看前五条 SQL 语句,这样写即可:
# 查看mysqldumpslow的帮助信息
$ mysqldumpslow -s t -t 5 /usr/local/mysql/logs/slow.log
Reading mysql slow query log from /usr/local/mysql/logs/slow.log
Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost
SELECT * FROM student WHERE name = 'S'
Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
SELECT * FROM student WHERE stuno = N
Died at /usr/bin/mysqldumpslow line 162, <> chunk 2.
一些常用的命令参考
# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /usr/local/mysql/logs/slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /usr/local/mysql/logs/slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /usr/local/mysql/logs/slow.log
# 另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /usr/local/mysql/logs/slow.log | more
四、explain
使用 explain
关键字可以模拟优化器执行SQL语句
,分析查询语句 或 表结构的性能瓶颈。
- https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
- https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
具体作用:
查看表的读取顺序
数据读取操作的操作类型
查看哪些索引可以使用
查看哪些索引被实际使用
查看表之间的引用
查看每张表有多少行被优化器执行
使用方式
在要执行的sql语句之前加上explain关键字即可
版本情况:
MySQL 5.6.3 以前只能 EXPLAIN SELECT
MYSQL 5.6.3 以后可以 EXPLAIN SELECT,UPDATE,DELETE
MySQL 5.7 以前
想要显示 partitions 需要使用 explain partitions 命令
想要显示 filtered 需要使用 explain extended 命令。
MySQL 5.7 以后
默认直接显示 partitions 和 filtered 的信息。
【数据准备】
# 建表
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR ( 100 ),
key2 INT,
key3 VARCHAR ( 100 ),
key_part1 VARCHAR ( 100 ),
key_part2 VARCHAR ( 100 ),
key_part3 VARCHAR ( 100 ),
common_field VARCHAR ( 100 ),
PRIMARY KEY ( id ),
INDEX idx_key1 ( key1 ),
UNIQUE INDEX idx_key2 ( key2 ),
INDEX idx_key3 ( key3 ),
INDEX idx_key_part ( key_part1, key_part2, key_part3 )
) ENGINE = INNODB CHARSET = utf8;
CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR ( 100 ),
key2 INT,
key3 VARCHAR ( 100 ),
key_part1 VARCHAR ( 100 ),
key_part2 VARCHAR ( 100 ),
key_part3 VARCHAR ( 100 ),
common_field VARCHAR ( 100 ),
PRIMARY KEY ( id ),
INDEX idx_key1 ( key1 ),
UNIQUE INDEX idx_key2 ( key2 ),
INDEX idx_key3 ( key3 ),
INDEX idx_key_part ( key_part1, key_part2, key_part3 )
) ENGINE = INNODB CHARSET = utf8;
# 允许创建函数设置
set global log_bin_trust_function_creators=1;
# 创建函数
DELIMITER //
CREATE FUNCTION rand_string1 ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
DECLARE
chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE
return_str VARCHAR ( 255 ) DEFAULT '';
DECLARE
i INT DEFAULT 0;
WHILE
i < n DO
SET return_str = CONCAT(
return_str,
SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER;
# 创建往s1表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_s1 (
IN min_num INT ( 10 ),
IN max_num INT ( 10 )) BEGIN
DECLARE
i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1
VALUES
(
( min_num + i ),
rand_string1 ( 6 ),
( min_num + 30 * i + 5 ),
rand_string1 ( 6 ),
rand_string1 ( 10 ),
rand_string1 ( 5 ),
rand_string1 ( 10 ),
rand_string1 ( 10 ));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER;
# 创建往s2表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_s2 (
IN min_num INT ( 10 ),
IN max_num INT ( 10 )) BEGIN
DECLARE
i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2
VALUES
(
( min_num + i ),
rand_string1 ( 6 ),
( min_num + 30 * i + 5 ),
rand_string1 ( 6 ),
rand_string1 ( 10 ),
rand_string1 ( 5 ),
rand_string1 ( 10 ),
rand_string1 ( 10 ));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER;
# 调用存储过程,插入1万条记录
CALL insert_s1(10001, 10000);
CALL insert_s2(10001, 10000);
00、Explain 输出列概述
列名 | 描述 | |
---|---|---|
id | 每个select对应一个唯一的id,表示select执行的顺序 | |
select_type | 查询类型。主要用于区别普通查询、联合查询、子查询等复杂查询 | |
table | 表名。显示这一行的数据是关于哪张表的(取了别名则显示别名) | |
partitions | 匹配的分区信息 | |
☆ | type | 针对单表的访问方法 |
possible_keys | 可能用到的索引 | |
☆ | key | 实际上使用的索引 |
☆ | key_len | 实际使用到的索引长度 |
ref | 当使用索引列等值查询时,与索引列进行等值匹配的对象信息 | |
☆ | rows | 预估的需要读取的记录条数(越小越好) |
filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 | |
☆ | Extra | 一些额外的信息 |
01、id
每个select都对应一个唯一的id,表示查询中select或操作表的顺序(id相同从上往下,id不同值越大优先级越高)
- id如果相同,可以认为是同一组,
从上往下顺序执行
- id如果不同,可以认为是不同组,
id值越大的,优先级越高,越先执行
- 每个id号表示一趟独立的查询, 一个sql的查询趟数越少越好
# 简单查询
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
# 内连接
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
# 子查询
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
# 被重写的子查询(查询优化器将此处的`子查询`重写为`连接查询`)
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a');
# 全连接 union
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
# 全连接 union all
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
02、select_type
名称 | 描述 |
---|---|
simple | Simple SELECT (not using UNION or subqueries) |
primary | Outermost SELECT |
union | Second or later SELECT statement in a UNION |
union result | Result of a UNION |
subquery | First SELECT in subquery |
dependent subquery | First SELECT in subquery, dependent on outer query |
dependent union | Second or later SELECT statement in a UNION, dependent on outer query |
derived | Derived table |
materialized | Materialized subquery |
uncacheable subquery | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
uncacheable union | The second or later select in a UNION that belongs to an uncacheable subquery (see uncacheable subquery) |
1)SIMPLE
简单查询 和 连接查询 (这里包括内连接和外连接) 属于 SIMPLE(查询中未使用 union 或 子查询)
# 简单查询
EXPLAIN SELECT * FROM s1;
# 连接查询
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
# 被重写的子查询(查询优化器将此处的`子查询`重写为`连接查询`,因此本质上还是连接查询)
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a');
2)PRIMARY
查询中使用了 Union 或 子查询 时,最外层的查询属于 PRIMARY
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
3)UNION
查询中使用了 union 时,union 后面的 select 属于 UNION
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
4)UNION RESULT
查询中使用了 union 时,因去重对 union的结果集的查询 属于 UNION RESULT(
union all
不需要去重,就没有)
# 全连接 union(去重)
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
# 全连接 union all
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
5)SUBQUERY
查询中使用了
不相关子查询
时,子查询的select属于 SUBQUERY
- 注意:这里的 不相关子查询 的查询语句不能够转为对应的 semi-join 的形式
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
6)DEPENDENT SUBQUERY
查询中使用了
相关子查询
时,相关子查询的select属于 SUBQUERY(相关子查询:依赖于外部查询的子查询)
- 注意:这里的 相关子查询 的查询语句不能够转为对应的 semi-join 的形式
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
7)DEPENDENT UNION
在包含 union 或 union all 的大查询中,如果各个小查询都依赖于外层查询,则 union 后面的 小查询 属于 DEPENDENT UNION
# 这里看上去没有依赖于外层查询,但是优化器会将这里的in优化为exists,这样就依赖于外层查询了
EXPLAIN SELECT * FROM s1 WHERE key1 IN (
SELECT key1 FROM s2 WHERE key1 = 'a'
UNION
SELECT key1 FROM s1 WHERE key1 = 'b'
);
8)DERIVED
对于包含
派生表
的查询,该 派生表 对应的子查询 属于 DERIVED(衍生)
EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;
9)MATERIALIZED
查询优化器在执行包含子查询的语句时,选择将子查询物化之后与外层查询进行连接查询(
物化表
)
# 子查询 SELECT key1 FROM s2 被转为了物化表
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
03、type ☆
代表MySQL对某个表的执行查询时的访问方法,如下(从好到差)
完整的访问方法(从好到差)
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
常用的访问方法(从好到差)
system > const > eq_ref > ref > range > index > ALL
SQL 性能优化的目标:
至少要达到 range 级别,要求是 ref 级别,最好是 consts 级别。
01)system
表中只有一条记录,并且该表使用的存储引擎的统计数据是精确的,比如MyISAM、Memory
- MyISAM有一个变量记录统计数据,是system;而InnoDB是读取数据累加的,因此会是all
create table t1(int i) engine=InnoDB;
insert into t1 value(1);
EXPLAIN select * from t1; # all
create table t2(int i) engine=MyISAM;
insert into t2 value(1);
EXPLAIN select * from t2; # system
02)const
根据 主键或唯一索引列 进行 等值匹配(只匹配一行数据)
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
03)eq_ref
连接查询时,被驱动表 通过 主键或唯一索引列 等值匹配(对于被驱动表只匹配一行数据)
- 如果改主键或唯一索引时联合索引,则 所有的索引列 都必须进行等值匹配
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
从执行计划可以看出,MySQL将s2作为驱动表,s1作为被驱动表,通过s1的主键等值匹配
04)ref(要求)
通过 普通的二级索引列 等值匹配(可能会找到多个符合条件的行)
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
05)fulltext
通过 全文索引 进行匹配
06)ref_or_null
通过 普通的二级索引列 等值匹配,该索引列的值可以为Null时(可能会找到多个符合条件的行)
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
07)index_merge
单表访问在某些场景下可以使用 Intersection、Union、Sort-Union 这三种索引合并的方式来执行查询(如or)
# 此处如果将or换成and,则只会用到一个索引,则type=ref
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
08)unique_subquery
在包含 in子查询 的查询语句中,查询优化器 将 in子查询 转化为 exists子查询,并且子查询可以使用到主键索引列进行等值匹配
EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
查询优化器 将 in子查询 转化为 exists子查询,并且用到了 s2的主键索引列 和 s1的key2 进行 等值匹配
09)index_subquery
在包含 in子查询 的查询语句中,查询优化器 将 in子查询 转化为 exists子查询,并且子查询可以使用到 索引列 进行等值匹配
EXPLAIN SELECT * FROM s1
WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
查询优化器 将 in子查询 转化为 exists子查询,并且用到了 s2的key3索引列 和 s1的common_field 进行 等值匹配
10)range(底线)
使用 索引 获取某些 范围区间 的记录(between、in、> 和 < 等条件)
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
11)index
查询字段与匹配字段在同一颗索引树上,通过遍历索引树,然后根据索引字段去查询需要的信息(覆盖索引,避免全表扫描)
# 联合索引是 ( key_part1, key_part2, key_part3 ) 根据最左原则,理论上索引会失效
# 但是由于查询的字段和条件的字段在同一颗索引树上(索引覆盖),因此还是用上了索引
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
12)ALL(禁止)
EXPLAIN SELECT * FROM s1;
04、possible_keys 和 key
possible_keys是可能使用的索引;key表示实际使用的索引
possible_keys如果有多个,MySQL会比较几个索引,选择最为合适的(因此不是越多越好,多了反而会降低效率)
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
key不一定是possible_keys的子集,例如type=index的那个例子,possible_keys=NULL,但实际使用上了索引
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
05、key_len ☆
实际使用到的索引长度(字节数),可以检查是否充分利用上了索引,相对于字段大小,值越大越好(通常用于联合索引)
- 字符对应字节数:utf8=3、gbk=2、latin1=1
- 允许为NULL需要多加一个字节
- varchar边长字段需要加两个字节(记录变长字段);char固定字段就不需要
# 主键索引:id INT(int类型占4个字节)
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
# 唯一索引:key2 INT
# int类型占4个字节,可以为null -> 加上1个字节记录null
EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
# 普通索引:key1 VARCHAR ( 100 )
# utf8一个字符占3个字节,加上1个字节记录null,加上2个字节记录变长的实际长度(100*3+1+2=303)
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
# 联合索引:key_part1 VARCHAR (100), key_part2 VARCHAR (100), key_part3 VARCHAR (100)
# utf8一个字符占3个字节,加上1个字节记录null,加上2个字节记录变长的实际长度(100*3+1+2=303)
# 虽然和上面一样都是303,但是上面充分利用了索引,这个只利用了三分之一(key_len的比较是相对于索引字段长度的)
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
# 联合索引:key_part1 VARCHAR (100), key_part2 VARCHAR (100), key_part3 VARCHAR (100)
# 同时使用上了key_part1和key_part2,比上面的就更精准一些
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
06、ref
当使用索引等值查询时,显示与索引列进行等值匹配的对象信息(无索引null / 常数const / 函数func / 库.列.具体字段)
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
07、rows ☆
根据表统计信息及索引选用情况,预估扫描的行数,每张表有多少行被优化器查询过。(值越小越好)
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
08、filtered
满足查询条件的记录数量的比例(检索结果/扫描行数),值越大越好。(依赖统计信息,并不十分准确)
- 对于单表查询来说,filtered的值没什么意义
- 对于连接查询来说,驱动表对应的filtered值,决定了被驱动表要执行的次数(即:rows * filtered/100 )
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
09、Extra ☆
记录一些额外的信息(可以更准确的理解MySQL到底将如何执行给定的查询语句)
01)No tables used
查询语句没有 from 子句
EXPLAIN SELECT 1;
02)Impossible WHERE
where 条件永远不成立
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
03)Using where
使用全表扫描来执行某个表的查询,并且where条件中有针对该表的搜索条件。(where存在无索引的条件列)
# where 无索引列
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
# where 索引列 and 无索引列
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
04)No matching min/max row
SQL返回的数据有min或max聚合函数,但是没有符合where搜索条件的记录
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
05)Using index
SQL所需要返回的所有列数据均在一棵索引树上,而无需回表查询。(覆盖索引)
- 如果同时出现 Using where,表明索引被用来执行索引键值的查找
- 如果没有出现 Using where,表明索引被用来读取数据而非执行查找动作
EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
06)Using index condition
确实命中了索引,但不是所有的列数据都在索引树上,还需要回表查询。
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z'
的 Extra 也是 Using index condition
07)Using join buffer (Block Nested Loop)
在连接查询中,当被驱动表不能有效利用索引时,MySQL一般会为其分配一块 join buffer 缓冲区来加快查询速度
- Block Nested Loop Join 优化的思路是不再逐条获取驱动表的数据,而是一块一块的获取,减少IO的次数
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
08)Not exists
使用外连接时,where条件中包含 被驱动表的某个列 IS NULL 的条件,而那个列又不允许存储NULL
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
09)Using union
or连接的两个条件都使用了索引,type=index_merge
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
10)Zero limit
EXPLAIN SELECT * FROM s1 LIMIT 0;
11)Using filesort
排序没有使用到索引,只能在内存中(记录较少的时候)或磁盘中(记录较多的时候)进行文件排序
# 排序使用到了索引
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
# 排序未使用到索引
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
12)Using temporary
存在distinct、group by、union等操作并且不能有效利用到索引时,mysql会创建一个内部的临时表来执行操作
EXPLAIN SELECT DISTINCT common_field FROM s1;
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
从 Extra 的 Using index 的提示里我们可以看出,上述查询只需要扫描 idx_key1 索引就可以搞定了,不再需要临时表了。
13)其它
其它特殊情况这里省略。
10、小结
- EXPLAIN不考虑各种Cache
- EXPLAIN不能显示MySQL在执行查询时所作的优化工作
- EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- 部分统计信息是估算的,并非精确值
五、show profile
show profile
是mysql提供的 可以用来分析当前会话中sql语句执行的资源消耗情况的工具。
1、是否开启 show profile
show profile 默认关闭,并保存最近15次的运行结果。
# 查看是否开启(默认关闭)
show variables like 'profiling';
# 开启show profile(重新连接后生效,重启mysql后失效)
set profiling = 'ON';
2、show profiles
# 查看当前会话的Query及耗时
show profiles;
3、show profile
# 查看最近一次查询的开销
show profile;
4、show profile for query
ALL 显示所有的开销信息
BLOCK IO 显示块IO开销
CONTEXT SWITCHES 上下文切换开销
CPU 显示CPU开销信息
IPC 显示发送和接收开销信息
MEMORY 显示内存开销信息
PAGE FAULTS 显示页面错误开销信息
SOURCE 显示和Source_function,Source_file, Source_line相关的开销信息。
SWAPS 显示交换次数开销信息
# 查看指定查询语句的开销
show profile cpu, block io for query 4;
5、show prodile 使用小结
-
开启 show profile
- show profile默认是关闭的,并且开启后只存活于当前会话,也就说每次使用前都需要开启。
-
show profiles
- 通过
show profiles
查看sql语句的耗时时间
- 通过
-
show profile
- 通过
show profile
对耗时时间长的sql语句进行诊断。
- 通过
-
分析show profile的诊断结果
- Converting heap to MyISAM:查询结果太大,内存不够,数据往磁盘上搬了。
- Creating tmp table:创建临时表。先拷贝数据到临时表,用完后再删除临时表。
- Copying to tmp table on disk:把内存中临时表复制到磁盘上,危险!!!
- Locked
如果在show profile诊断结果中出现了以上几种情况,则sql语句需要优化。
六、mysql sys schema
`mysql sys schema` 是MySQL官方提供的一个系统数据库,它包含一些视图和函数,用于提供MySQL服务器和实例的性能监控和状态查询。通过 `mysql sys schema` 可以更方便地了解MySQL服务器的运行情况,进行性能优化和故障排查。
0、Sys schema 视图- 摘要
-
主机相关:以host_summary开头,主要汇总了IO延迟的信息。
-
Innodb相关:以innodb开头,汇总了innodb buffer信息和事务等待innodb锁的信息。
-
I/O相关:以io开头,汇总了等待I/O、I/O使用量情况。
-
内存使用情况:以memory开头,从主机、线程、事件等角度展示内存的使用情况
-
连接与会话信息:processlist和session相关视图,总结了会话相关信息。
-
表相关:以schema_table开头的视图,展示了表的统计信息。
-
索引信息:统计了索引的使用情况,包含冗余索引和未使用的索引情况。
-
语句相关:以statement开头,包含执行全表扫描、使用临时表、排序等的语句信息。
-
用户相关:以user开头的视图,统计了用户使用的文件I/O、执行语句统计信息。
-
等待事件相关信息:以wait开头,展示等待事件的延迟情况。
1、Sys schema 视图- 索引相关
# 查询冗余索引
select * from sys.schema_redundant_indexes;
# 查询未使用过的索引
select * from sys.schema_unused_indexes;
# 查询索引的使用情况
select index_name, rows_selected, rows_inserted, rows_updated, rows_deleted
from sys.schema_index_statistics where table_schema='dbname';
2、Sys schema 视图- 表相关
# 查询表的访问量
select table_schema, table_name, sum(io_read_requests+io_write_requests) as io
from sys.schema_table_statistics group by table_schema, table_name order by io desc;
# 查询占用bufferpool较多的表
select object_schema, object_name, allocated, `data`
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';
3、Sys schema 视图- 语句相关
# 监控SQL执行的频率
select db, exec_count, query
from sys.statement_analysis order by exec_count desc;
# 监控使用了排序的SQL
select db, exec_count, first_seen, last_seen, query
from sys.statements_with_sorting limit 1;
# 监控使用了临时表或者磁盘临时表的SQL
select db, exec_count, tmp_tables, tmp_disk_tables, query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;
4、Sys schema 视图- IO相关
# 查看消耗磁盘IO的文件
select file, avg_read,avg_write, avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes
order by avg_read limit 10;
5、Sys schema 视图- Innodb相关
# 行锁阻塞情况
select * from sys.innodb_lock_waits;