一、基础知识
1.1 数据库服务器的优化步骤
1.2 查看系统性能参数
1.3 统计 SQL 查询成本
1.4 查看 SQL 执行成本
二、mysqldumpslow 慢日志分析
2.1 相关变量
2.2 案例准备
2.3 测试
2.4 工具使用
三、trace 分析优化器执行计划
3.1 相关参数
3.2 使用分析
四、sys.schema 监控分析视图
4.1 视图摘要
4.2 视图使用场景
4.2.1 索引情况
4.2.2 表相关
4.2.3 语句相关
4.2.4 IO 相关
4.2.5 Innodb 相关
一、基础知识
1.1 数据库服务器的优化步骤
整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。
优化步骤
1.2 查看系统性能参数
在MySQL中，可以使用SHOW STATUS语句查询一些MySQL数据库服务器的性能参数、执行频率。
mysql> SHOW STATUS LIKE ‘Slow_queries’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Slow_queries | 0 |
+—————+——-+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE ‘Innodb_rows_read’;
+——————+———+
| Variable_name | Value |
+——————+———+
| Innodb_rows_read | 8792854 |
+——————+———+
1 row in set (0.00 sec)
mysql>
常用参数 说明
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 删除操作的次数
1.3 统计 SQL 查询成本
它是作为比较各个查询之间的开销的一个依据。
它只能检测比较简单的查询开销，对于包含子查询和union的查询是测试不出来的。
当我们执行查询的时候，MySQL会自动生成一个执行计划，也就是query plan，而且通常有很多种不同的实现方式，它会选择最低的那一个，而这个cost值就是开销最低的那一个。
它对于比较我们的开销是非常有用的，特别是我们有好几种查询方式可选的时候。
mysql> SELECT COUNT(DISTINCT(student_id)) FROM `student_info`;
+-----------------------------+
| COUNT(DISTINCT(student_id)) |
+-----------------------------+
| 198058 |
+-----------------------------+
1 row in set (0.67 sec)
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+---------------+
| Variable_name | Value |
+-----------------+---------------+
| Last_query_cost | 100458.549000 |
+-----------------+---------------+
1 row in set (0.00 sec) 1.4 查看 SQL 执行成本
开启 profiling
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.05 sec)
mysql> set profiling = 'ON';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 3355654 | 3455655 | uWAJVB | 89 | 777 |
+---------+---------+--------+------+---------+
1 row in set (1.97 sec)
mysql>使用 profiling
mysql> SELECT * FROM student WHERE stuno = 3455655;
1 row in set (1.97 sec)
mysql> SELECT * FROM student WHERE name = 'JsJtPT';
...
8 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------+
| 1 | 1.98446300 | SELECT * FROM student WHERE stuno = 3455655 |
| 2 | 0.00596225 | SELECT * FROM student WHERE name = 'JsJtPT' |
+----------+------------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
# 默认展示最近的一条记录，即 ‘Query_ID = 2’
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000091 |
| Executing hook on transaction | 0.000006 |
| starting | 0.000009 |
| checking permissions | 0.000007 |
| Opening tables | 0.000053 |
| init | 0.000006 |
| System lock | 0.000010 |
| optimizing | 0.000011 |
| statistics | 0.001088 |
| preparing | 0.000023 |
| executing | 0.004580 |
| end | 0.000015 |
| query end | 0.000005 |
| waiting for handler commit | 0.000013 |
| closing tables | 0.000011 |
| freeing items | 0.000022 |
| cleaning up | 0.000015 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
# 定制化查询某条记录的某些指标
mysql> show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000096 | 0.000068 | 0.000019 | 0 | 0 |
| Executing hook on transaction | 0.000016 | 0.000007 | 0.000002 | 0 | 0 |
| starting | 0.000008 | 0.000006 | 0.000002 | 0 | 0 |
| checking permissions | 0.000007 | 0.000005 | 0.000001 | 0 | 0 |
| Opening tables | 0.000040 | 0.000031 | 0.000009 | 0 | 0 |
| init | 0.000007 | 0.000006 | 0.000002 | 0 | 0 |
| System lock | 0.000021 | 0.000011 | 0.000003 | 0 | 0 |
| optimizing | 0.000012 | 0.000010 | 0.000002 | 0 | 0 |
| statistics | 0.000020 | 0.000015 | 0.000005 | 0 | 0 |
| preparing | 0.000020 | 0.000016 | 0.000004 | 0 | 0 |
| executing | 1.963674 | 1.342543 | 0.115670 | 316896 | 0 |
| end | 0.000023 | 0.000016 | 0.000004 | 0 | 0 |
| query end | 0.000007 | 0.000005 | 0.000002 | 0 | 0 |
| waiting for handler commit | 0.000013 | 0.000011 | 0.000003 | 0 | 0 |
| closing tables | 0.000012 | 0.000009 | 0.000002 | 0 | 0 |
| freeing items | 0.000132 | 0.000024 | 0.000007 | 0 | 0 |
| logging slow query | 0.020331 | 0.000175 | 0.000049 | 416 | 8 |
| cleaning up | 0.000026 | 0.000020 | 0.000006 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
18 rows in set, 1 warning (0.00 sec)
mysql>
常用查询参数
选项 说明
ALL 显示所有的开销信息
BLOCK IO 显示块IO开销
CONTEXT SWITCHES 上下文切换开销
CPU 显示CPU开销信息
IPC 显示发送和接收开销信息
MEMORY 显示内存开销信息
PAGE FAULTS 显示页面错误开销信息
SOURCE 显示和 Source_function, Source_file, Source_line 相关的开销信息
SWAPS 显示交换次数开销信息
二、mysqldumpslow 慢日志分析
在生产环境中，如果要手工从慢日志中查找、分析 SQL 显然是个体力活，MySQL 提供了慢查询日志分析工具 mysqldumpslow。
2.1 相关变量
启开状态
mysql> SHOW VARIABLES LIKE ‘slow_query_log%’;
+———————+————————————–+
| Variable_name | Value |
+———————+————————————–+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/5157698acdb3-slow.log |
+———————+————————————–+
2 rows in set (0.00 sec)
mysql>
1
2
3
4
5
6
7
8
9
10
记录阈值
mysql> SHOW VARIABLES LIKE ‘%long_query_time%’;
+—————–+———–+
| Variable_name | Value |
+—————–+———–+
| long_query_time | 10.000000 |
+—————–+———–+
1 row in set (0.00 sec)
mysql>
1
2
3
4
5
6
7
8
9
慢查询数目
mysql> SHOW GLOBAL STATUS LIKE ‘%Slow_queries%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Slow_queries | 0 |
+—————+——-+
1 row in set (0.01 sec)
mysql>
1
2
3
4
5
6
7
8
9
2.2 案例准备
建表
CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
stuno INT NOT NULL ,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
classId INT(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
设置参数 log_bin_trust_function_creators
创建函数，假如报错：
This function has none of DETERMINISTIC……
命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1;
创建函数
函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(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 ;
#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1)) ;
RETURN i;
END //
DELIMITER ;
创建存储过程
DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId )
VALUES ((START+i), rand_string(6), rand_num(10,100), rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
调用存储过程
#调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);
2.3 测试
打开慢日志记录并调低慢查询的记录阈值
mysql> SET GLOBAL slow_query_log=on;
Query OK, 0 rows affected (0.32 sec)
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)
mysql>
测试
mysql> SELECT * FROM student WHERE stuno = 3455655;
+———+———+——–+——+———+
| id | stuno | name | age | classId |
+———+———+——–+——+———+
| 3355654 | 3455655 | uWAJVB | 89 | 777 |
+———+———+——–+——+———+
1 row in set (1.90 sec)
mysql> SELECT * FROM student WHERE name = ‘JsJtPT’;
+———+———+——–+——+———+
| id | stuno | name | age | classId |
+———+———+——–+——+———+
| 242278 | 342279 | JsJtPT | 100 | 352 |
| 611433 | 711434 | jSJTpT | 51 | 683 |
| 1000005 | 1100006 | JsJtPT | 99 | 279 |
| 1612477 | 1712478 | jsjTPt | 57 | 474 |
| 2240808 | 2340809 | JSjtpt | 98 | 729 |
| 2970233 | 3070234 | JSjtpt | 97 | 709 |
| 2992184 | 3092185 | JSjtpt | 98 | 747 |
| 3679784 | 3779785 | jSJTpT | 52 | 707 |
+———+———+——–+——+———+
8 rows in set (1.68 sec)
mysql>
记录
mysql> show status like ‘slow_queries’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Slow_queries | 2 |
+—————+——-+
1 row in set (0.12 sec)
mysql>
2.4 工具使用
工具简介
root@5157698acdb3:/# 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，字符串抽象成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
root@5157698acdb3:/#
按照查询时间排序，查看前五条 SQL 语句
root@5157698acdb3:/# mysqldumpslow -s t -t 5 -a /var/lib/mysql/5157698acdb3-slow.log
Reading mysql slow query log from /var/lib/mysql/5157698acdb3-slow.log
Count: 1 Time=1.67s (1s) Lock=0.00s (0s) Rows=8.0 (8), root[root]@localhost
SELECT * FROM student WHERE name = ‘JsJtPT’
Count: 1 Time=1.53s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
SELECT * FROM student WHERE stuno = 3455655
Died at /usr/bin/mysqldumpslow line 162, <> chunk 2.
root@5157698acdb3:/#
mysql> ALTER TABLE student ADD INDEX(name);
Query OK, 0 rows affected (21.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM student WHERE name = ‘JsJtPT’;
+———+———+——–+——+———+
| id | stuno | name | age | classId |
+———+———+——–+——+———+
| 242278 | 342279 | JsJtPT | 100 | 352 |
| 611433 | 711434 | jSJTpT | 51 | 683 |
| 1000005 | 1100006 | JsJtPT | 99 | 279 |
| 1612477 | 1712478 | jsjTPt | 57 | 474 |
| 2240808 | 2340809 | JSjtpt | 98 | 729 |
| 2970233 | 3070234 | JSjtpt | 97 | 709 |
| 2992184 | 3092185 | JSjtpt | 98 | 747 |
| 3679784 | 3779785 | jSJTpT | 52 | 707 |
+———+———+——–+——+———+
8 rows in set (0.02 sec)
mysql>
其它常用分析语句
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log
另外建议在使用这些命令时结合 | 和 less 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | less
三、trace 分析优化器执行计划
3.1 相关参数
查询
mysql> select @@optimizer_trace;
+————————–+
| @@optimizer_trace |
+————————–+
| enabled=off,one_line=off |
+————————–+
1 row in set (0.00 sec)
mysql> select @@end_markers_in_json;
+———————–+
| @@end_markers_in_json |
+———————–+
| 0 |
+———————–+
1 row in set (0.00 sec)
最大能够使用的内存大小
mysql> select @@optimizer_trace_max_mem_size;
+——————————–+
| @@optimizer_trace_max_mem_size |
+——————————–+
| 1048576 |
+——————————–+
1 row in set (0.00 sec)
mysql>
开启
mysql> SET optimizer_trace=”enabled=on”,end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)
mysql>
4.2 视图使用场景
4.2.1 索引情况
1. 查询冗余索引
select * from sys.schema_redundant_indexes;
2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema=’dbname’ ;
4.2.2 表相关
1. 查询表的访问量
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;
2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;
3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db=’dbname’;
4.2.3 语句相关
1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis order by exec_count desc;
2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1;
3. 监控使用了临时表或者磁盘临时表的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.2.4 IO 相关
1. 查看消耗磁盘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;
4.2.5 Innodb 相关
1. 行锁阻塞情况
select * from sys.innodb_lock_waits;
