MySQL性能分析

于 2024-03-15 14:16:21 发布
阅读量1.1k 收藏 16
点赞数 19
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/scj1022/article/details/136738518
版权

文章目录

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支持不支持perlmysql官方自带
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

名称描述
simpleSimple SELECT (not using UNION or subqueries)
primaryOutermost SELECT
unionSecond or later SELECT statement in a UNION
union resultResult of a UNION
subqueryFirst SELECT in subquery
dependent subqueryFirst SELECT in subquery, dependent on outer query
dependent unionSecond or later SELECT statement in a UNION, dependent on outer query
derivedDerived table
materializedMaterialized subquery
uncacheable subqueryA subquery for which the result cannot be cached
and must be re-evaluated for each row of the outer query
uncacheable unionThe 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 视图- 摘要

  1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。

  2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。

  3. I/O相关：以io开头，汇总了等待I/O、I/O使用量情况。

  4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况

  5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。

  6. 表相关：以schema_table开头的视图，展示了表的统计信息。

  7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。

  8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。

  9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。

  10. 等待事件相关信息：以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;
scj1022
关注
  • 19
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
【mysql】#起步#mysql多个版本特性对比
JoinIn,
05-07 998
收集合并了大神的描述 mysql5.5相对mysql5.1特性 性能提升 默认InnoDB plugin引擎。具有提交、回滚和crash恢复功能、ACID兼容 行级锁(一致性的非锁定读 MVCC) 表与索引存储在表空间、表大小无限制 支持dynamic(primary key缓存内存 避免主键查询引起的IO )与compressed(支持数据及索引压缩)行格式 In...
MySQL性能分析及explain的使用说明
09-11
本文我们主要介绍了MySQL性能分析以及explain的使用，包括：组合索引、慢查询分析、MYISAM和INNODB的锁定、MYSQL的事务配置项等，希望能够对您有所帮助。
MySql概述及其性能说明
最新发布
小筱在线博客
12-06 1030
MySQL支持SQL语言，提供了一套完整的数据库管理系统，包括数据的存储、查询、备份、还原等功能。MySQL也是LAMP架构中的重要组成部分，与Linux、Apache、PHP（或Perl或Python）一起构成了一个完整的Web开发平台。在今天的商业环境下，MySQL是一种理想的数据库解决方案，值得企业级用户考虑和采用。MySQL可以运行在多种操作系统中，包括Windows、Linux、Unix等，因此可以适用于多种企业级应用程序。MySQL使用高效的数据压缩技术，减少了存储空间的使用。
MySQL免费性能监控工具-MONyog
十年彩虹
11-09 2万+
一款不错的工具，我们服务器是windows2003。所以我本地安装了MONyog进行远程监控。只需要在服务器端安装SSH协议即可。（收费，有破解的）   1、登录配置界面   2、show processlist  查看当前使用的进程   3、警告建议你应该优化哪些参数。    4、介绍一下慢查询的配置，其它的可以自己配置，都是简单的英文。   该工具，用着还
mysql性能测试及不同版本的比较
03-07 1万+
I. 引言 Oracle 公司发布了MySQL5.5版本,这也是该公司获得MySQL后,对MySQL的第一次升级。Oracle表示，按照内部的标准，在Linux 上，MySQL 5.5的读写速度比5.1版本提升了360%。在Windows Server机器上，这方面性能的提升超过了1500%。MySQL 5.5版本的吞吐量更高。如果真如Oracle所言,MySQL5.5相对于MySQL5.1
MySQL性能分析命令
疾风先生的博客
06-04 1667
服务性能简介 三个性能服务请求 如何确认服务器是否达到了性能最佳状态 找出某条sql语句执行慢的原因 诊断被用户描述成“停顿”,“堆积”或者是“卡死”的间歇性疑难故障. 性能定义 为完成某件任务所需要的时间度量,换言之,性能即响应时间,这是一个非常重要的原则. SQL性能分析 执行分析 - 基于执行时间的分析 等待分析 - 基于等待时间的分析 SQL性能优化 在一定的工作负载下尽可能地降低响应时间,在SQL服务器中定义的吞吐量指标为单位时间内的查询量. 性能优化工具 慢查询日志是
MySQL性能分析 一
PinkCoder
04-12 2290
MySQL性能分析，慢查询、执行计划分析
mysql性能分析
NRapid&JRapid
04-29 1658
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。显示的值为索引字段的最大可能长度 并非实际使用的长度。说明mysql会对数据使用一个外部的要求排序，而不是按照表内的索引顺序进行读取。id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
MYSQL性能分析
qq_38484679的博客
12-27 2343
log_queries_not_using_indexes:查询未使用索引是否开启记录慢查询日志 on(开启)/off(关闭)；参考慢sql个数，根据慢sql日志查看执行较慢的sql，针对慢sql进行sql优化。，能搜到block的话就是存在数据库死锁，查看对应的sql，优化造成死锁的sql。如果正在创建的进程数>缓存进程数，并且正在创建的进程数较大，建议增加缓存进程数。查询慢sql开启的状态 可以查看到慢sql日志已开启 以及慢sql日志文件位置。查询具体sql语句执行的时间。查看正在创建的进程数。
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL性能分析常用指令
FAIRYTAIL的博客
04-14 1992
之前详细记录了explain的使用方式，感兴趣的可以点此查看，MySQL 执行计划explain各参数含义及示例，今天补充一下其它查看sql性能常用的命令，各位看到此博客的小伙伴，如有不对的地方请及时通过私信我或者评论此博客的方式指出，以免误人子弟。多谢！ 查看INSERT、UPDATE、DELETE、SELECT的执行频率 我们可以在MySQL提供的客户端或者自己的连接工具上使用 show [session|global] status 命令查看服务器状态信息，比如，我们可以通过如下命令，查看当...
MySQL性能分析与优化调整
07-29
MySQL性能分析与优化调整_
MySQL性能分析工具profile使用教程
09-10
主要介绍了MySQL性能分析工具profile使用教程,本文描述了如何使用MySQL profile,不涉及具体的样例分析,需要的朋友可以参考下
MYSQL 性能分析器 EXPLAIN 用法实例分析
09-08
主要介绍了MYSQL 性能分析器 EXPLAIN 用法,结合实例形式分析了MYSQL 性能分析器 EXPLAIN 基本功能、使用方法及操作注意事项,需要的朋友可以参考下
mysql性能分析工具sqlyog
04-15
mysql性能分析工具sqlyog破解版 性能分析 要的下载。 sdf
Mysql自带profiling性能分析工具使用分享
09-11
Mysql自带profiling性能分析工具使用分享，需要的朋友可以参考下
MySQL 性能优化神器 Explain 使用分析
01-22
MySQL 性能优化 Explain ,MySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 Explain
MySQL性能分析及explain的应用实践.docx
10-14
MySQL性能分析及explain的应用实践 MySQL性能分析是数据库优化的重要步骤，explain语句是MySQL性能分析的重要工具。explain语句可以帮助开发者和数据库管理员了解 MySQL 的查询执行计划，从而优化查询性能。 1. ...
MySQL性能常见原因分析
07-30
MySQL常见性能问题原因分析及解决办法，MySQL性能测试方法等
mysql 性能分析工具
09-16
MySQL 性能分析工具有很多，以下是一些常用的工具： 1. MySQL 自带的性能分析工具：MySQL 提供了一些内置的性能分析工具，包括 EXPLAIN、SHOW PROFILES、SHOW STATUS、SHOW PROCESSLIST 等，用于分析查询语句的执行...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

scj1022

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值