【MySQL调优】性能分析

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 的使用为例：

# 查看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