MySql 性能分析篇
1 SQL语句-性能分析思路
- 首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
- 其次【查看执行计划】查看有问题的SQL的执行计划
- 最后可以使用【show profile[s]】 查看有问题的SQL的性能使用情况
1.2 慢查询日志
1.2.1 介绍
数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的SQL。MySQL数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我们快速定位到问题所在，以便对症下药。
1.2.2 开启慢日志
MySQL的慢查询日志功能默认是关闭的，需要手动开启。
- 查看慢日志是否开启
mysql> show variables like '%slow_query%';
+---------------------+-----------------------------------+
| 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> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
参数说明：
slow_query_log ：是否开启慢查询日志，ON为开启，OFF为关闭。
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志，单位为秒
- 临时开启慢查询功能
在 MySQL 执行 SQL 语句设置，但是如果重启 MySQL 的话将失效
# 全局修改
set global slow_query_log = ON;
set global long_query_time = 1;
# 当前数据下修改
set slow_query_log = ON;
set long_query_time = 1;
- 永久开启慢查询功能
修改/etc/my.cnf配置文件，重启 MySQL, 这种方式永久生效，一般不建议永久开启
[mysqld]
# 1-开启 0-关闭
slow_query_log = 1
# 慢日志文件路径
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
- 演示
- 查询睡眠5s
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
- 慢日志输出
[root@localhost mysql]# tail -100f /var/lib/mysql/localhost-slow.log
# Time: 201216 11:36:52
# User@Host: root[root] @ localhost [] Id: 36
# Query_time: 5.000554 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1608136612;
select sleep(5);
参数说明：
Time： sql执行时间
User@Host：用户连接信息
Query_time：查询消耗时间
Lock_time：在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
Rows_sent：查询返回条数
Rows_examined：查询检查的行数，越长就当然越费时间
SET timestamp：执行时间表示的时间戳
select sleep(5);：实际执行的sql语句，可能会很长
1.3 linux分析慢查询日志的工具
1.3.1 percona-toolkit工具
1.3.1.1 介绍
percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息，磁盘检测，分析慢查询日志，查找重复索引，实现表同步等等。
1.3.1.2 下载与安装
- 下载
wget "https://www.percona.com/downloads/percona-toolkit/3.0.3/binary/tarball/percona-toolkit-3.0.3_x86_64.tar.gz"
- 安装
# 解压
tar -xf percona-toolkit-3.0.11_x86_64.tar.gz
# 进入解压目录
cd percona-toolkit-3.0.11
#开始编译安装
perl Makefile.PL
make
make install
- 安装使用过程中可能会报错信息，解决方案如下
- Can’t locate ExtUtils/MakeMaker.pm in @INC 错误的解决方式:
yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
- Can’t locate Time/HiRes.pm in @INC
yum install -y perl-Time-HiRes
- Can’t locate Digest/MD5.pm in @INC
yum install perl-Digest-MD5.x86_64
- Warning: prerequisite DBD::mysql 3 not found.
yum install perl-DBD-MySQL
1.3.1.3 使用pt-query-digest查看慢查询日志
1.3.1.3.1 查询指令
pt-query-digest /var/lib/mysql/localhost-slow.log
1.3.1.3.2 输出说明
[root@localhost mysql]# pt-query-digest localhost-slow.log
## 第一部分
- 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 130ms user time, 10ms system time, 21.98M rss, 184.71M vsz
- 工具执行时间
# Current date: Thu Dec 17 05:49:59 2020
- 运行分析工具的主机名
# Hostname: localhost.localdomain
- 分析的慢日志文件
# Files: localhost-slow.log
- 语句总数量，唯一的语句数量，QPS，并发数
# Overall: 5 total, 2 unique, 0.00 QPS, 0.00x concurrency ________________
- 日志记录的时间范围
# Time range: 2020-12-16 11:27:41 to 2020-12-17 05:31:48
- 属性 总计 最小值 最大值 平均值 实际参考值 标准 中位数
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
- 执行时间
# Exec time 83s 2s 62s 17s 60s 22s 5s
- 锁占用时间
# Lock time 59us 0 59us 11us 57us 22us 0
- 返回给客户端条数
# Rows sent 4 0 1 0.80 0.99 0.40 0.99
- select 语句查询行数
# Rows examine 0 0 0 0 0 0 0
- 查询的字符数
# Query size 73 12 16 14.60 15.25 1.14 14.52
## 第二部分 查询分组统计结果 Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
# Profile
- Query ID:语句id(hash值) Response:总相应时间 time:实际执行时间 Calls:执行次数
- R/Call:平均每次执行时间 V/M :响应时间Variance-to-mean的比率 Item:查询对象
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ======= ===== ==========
# 1 0x62875C407F1C3D8F 61.7900 74.6% 1 61.7900 0.00 CALL pro12
# 2 0xF9A57DD5A41825CA 21.0046 25.4% 4 5.2511 2.26 SELECT
## 第三部分
- Attribute 属性值同上
- id：Profile中的id
# Query 1: 0 QPS, 0x concurrency, ID 0x62875C407F1C3D8F at byte 964 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2020-12-17 05:31:48
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 1
# Exec time 74 62s 62s 62s 62s 62s 0 62s
# Lock time 100 59us 59us 59us 59us 59us 0 59us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 16 12 12 12 12 12 0 12
# String:
- 数据库名
# Databases test
# Hosts 192.168.233.2
- 用户名
# Users root
- 查询时间分布, 长短体现区间占比
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
- 执行的存储过程
call pro12()\G
# Query 2: 0.00 QPS, 0.00x concurrency, ID 0xF9A57DD5A41825CA at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 2.26
# Time range: 2020-12-16 11:27:41 to 2020-12-17 04:37:12
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 80 4
# Exec time 25 21s 2s 11s 5s 11s 3s 8s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 100 4 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0 0
# Query size 83 61 15 16 15.25 15.25 0.31 14.52
# String:
# Databases test
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ #####################
# EXPLAIN /*!50100 PARTITIONS*/
- 执行的sql语句
select sleep(11)\G
1. 3.1.3.3 其他查询指令 - 分析日志并输出到指定文件
- 直接分析慢查询文件
pt-query-digest localhost-slow.log > slow_report.log
- 分析最近12小时内的查询
pt-query-digest --since=12h localhost-slow.log > slow_report.log
- 分析指定时间范围内的查询
pt-query-digest localhost-slow.log --since '2020-01-07 09:30:00' --until '2020-01-07 10:00:00'> slow_report.log
- 分析指含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' localhost-slow.log > slow_report.log
- 针对某个用户(root)的慢查询
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' localhost-slow.log > slow_report.log
- 查询所有所有的全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' localhost-slow.log > slow_report.log
- 把查询保存到query_history表
pt-query-digest --user=root –password=abc123 --review
h=localhost,D=test,t=query_history--create-review-table slow.log_0001
- 分析binlog
mysqlbinlog mysql-bin.000001 > mysql-000001.sql
pt-query-digest --type=binlog mysql-000001.sql > slow_report.log
1. 4 profile分析语句
1.4.1 介绍
- Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。
- 通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MySQL5.0.37以及以上版本中才有实现。
- 默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。
1.4.2 profile开启与关闭
- 查询是否开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
- 开启
# 1是开启、0是关闭
set profiling=1;
1.4.3 语句使用
- show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源
使用情况. - show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况.显示的记录数由变
量:profiling_history_size 控制,默认15条
- show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列
Status： MySql执行语句的详细步骤流程
Duration：每个步骤执行的时间，单位秒
语法结构
show profile 【Type】 【for query query_id】【limit offset, num】
Type类型：all - 查询所有指标、CPU、IPC、MEMORY等等。
ALL: 显示所有的开销信息
BLOCK IO： 显示块IO相关开销
CONTEXT SWITCHS: 上下文切换相关开销
CPU: 显示cpu 相关开销
IPC: 显示发送和接收相关开销
MEMORY：显示内存相关开销
PAGE FAULTS：显示页面错误相关开销信息
SOURCE：显示和Source_function ,Source_file,Source_line 相关的开销信息
SWAPS：显示交换次数相关的开销信息
Status ： sql 语句执行的状态
Duration: sql 执行过程中每一个步骤的耗时
CPU_user: 当前用户占有的cpu
CPU_system: 系统占有的cpu
Block_ops_in : I/O 输入
Block_ops_out : I/O 输出
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000094 |
| checking permissions | 0.000012 |
| Opening tables | 0.000013 |
| init | 0.000024 |
| optimizing | 0.000013 |
| executing | 0.000021 |
| end | 0.000010 |
| query end | 0.000009 |
| closing tables | 0.000008 |
| freeing items | 0.000020 |
| cleaning up | 0.000024 |
+----------------------+----------+
11 rows in set, 1 warning (0.00 sec)
- show profiles 查看分析列表
Duration：执行时间，单位秒
Query：执行的语句
mysql> show profiles;
+----------+------------+-----------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------+
| 1 | 0.00025475 | select @@profiling |
| 2 | 0.11139625 | select * from `order` |
+----------+------------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
- 查询query_id=2的指标索引信息
mysql> show profile all for query 2;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
| starting | 0.000082 | 0.000000 | 0.000074 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000016 | 0.000000 | 0.000016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5325 |
| Opening tables | 0.000034 | 0.000000 | 0.000034 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5118 |
| init | 0.000037 | 0.000000 | 0.000037 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_prepare_select | sql_select.cc | 1058 |
| System lock | 0.000020 | 0.000000 | 0.000020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 311 |
| optimizing | 0.000012 | 0.000000 | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 146 |
| statistics | 0.000024 | 0.000000 | 0.000024 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 372 |
| preparing | 0.000021 | 0.000000 | 0.000020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 495 |
| executing | 0.000008 | 0.000000 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 117 |
| Sending data | 0.111100 | 0.000000 | 0.094667 | 18 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 197 |
| end | 0.000012 | 0.000000 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_select | sql_select.cc | 1113 |
| query end | 0.000006 | 0.000000 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5023 |
| closing tables | 0.000010 | 0.000000 | 0.000010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5072 |
| freeing items | 0.000008 | 0.000000 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 6604 |
| cleaning up | 0.000008 | 0.000000 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1843 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
15 rows in set, 1 warning (0.00 sec)
2 服务器层面优化
2.1 介绍
- InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。
- innodb_buffer_pool_size默认大小为128M。最大值取决于CPU的架构。
- 建议innodb_buffer_pool_size设置为总内存大小的3/4或者4/5。
2.2 查看与设置规则
- 查看
mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)
重要参数说明：
innodb_buffer_pool_size：缓冲池大小，默认值128M
innodb_buffer_pool_chunk_size：定义InnoDB缓冲池大小调整操作的块大小，默认值128M
innodb_buffer_pool_instances：InnoDB 缓冲池划分为的区域数，默认值8
- 设置规则
-
缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。
-
如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。
-
动态修改
在mysql5.7.5之后，可以在mysql不重启的情况下动态修改pool的size
-- 修改缓冲区实例个数
set global innodb_buffer_pool_instances=32;
-- 修改缓存区大小,单位byte 4G = 32×128M = 4×1024×1024×1024
set global innodb_buffer_pool_size=4294967296;
- 静态修改
默认文件目录：/etc/my.cnf
# 设置缓存空间大小 256M
innodb_buffer_pool_size=268435456
#设置实例个数
innodb_buffer_pool_instances=16
mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 16 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 268435456 |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)
3 SQL语句优化建议
3.1 索引优化
- 为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据的业务场景：查询多还是增删多？
- 尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。
- 尽量使用覆盖索引，SELECT语句中尽量不要使用*。
- order by、group by语句要尽量使用到索引。
注意：全表查询，无where判断条件，order by与group by只有主键索引会生效，其他类型的索引无效。
3.2 limit优化
- 如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。
-- username没有建立唯一索引 全表扫描，效率很低
SELECT * FROM user WHERE username='lin';
-- username没有建立唯一索引，找到第一条直接返回，不继续查询
SELECT * FROM user WHERE username='lin' LIMIT 1;
- 处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常
差。LIMIT OFFSET , SIZE；LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。
一般的框架都使用分页查询插件，无法做到优化的效果，针对一些查询缓慢的分页，可以进行一下优化。
- 使用order by 和索引覆盖(只需要索引数据，一般情况很少使用到)
# id与sex均为索引，直接从索引文件获取数据，不需要进行表查询，效率极快
SELECT id, sex from user limit 10000, 20;
- 使用子查询
# id为主键
SELECT * FROM user where id >= (select id from user order by id limit 100000,1) limit 20;
- 单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows
# id为自增主键,offset为偏移量，即其实的查询位置
SELECT * FROM film where id > offset limit 20;
3.3 其他优化
- 尽量不使用count(*)、尽量使用count（主键）
COUNT(*)： 查询行数，是会遍历所有的行、所有的列。
COUNT（列）：查询指定列不为null的行数（过滤null），如果列可以为空，则COUNT（*）不等于COUNT(列)，除非指定的列是非空的列才会让COUNT（*）等于COUNT(列)
COUNT（伪列）：比如COUNT（1）
- JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。字段类型不一致会导致索引失效
- WHERE条件中尽量不要使用1=1、not in语句（建议使用not exists）
- 不用 MySQL 内置的函数，因为内置函数不会建立查询缓存。
SQL查询语句和查询结果都会在第一次查询只会存储到MySQL的查询缓存中，如果需要获取到查询缓存中的查询结果，查询的SQL语句必须和第一次的查询SQL语句一致。
原因：MySql缓存以hashKey-value存储，Sql中使用到函数，同一个查询条件计算出的hash值不一只，导致无法从缓存获取。
现象：目前普遍的企业不再使用MySql内置的缓存，大部分企业会选择其他缓存数据库，例如redis。
# 使用new()，导致无法从缓存查询；
SELECT * FROM user where birthday = now();
- 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。
扫一扫
550
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
