MySQL 优化思路篇
- 1、MySQL 查询的优化步骤
- 2、查询系统性能参数
- 3、慢查询日志定位问题
- 3.1、开启慢查询日志参数
- 3.2、查看慢查询数目
- 3.3、慢查询日志的分析工具 mysqldumpslow
- 3.4、关闭慢查询日志
- 3.5、慢查询日志的删除与重建
- 4、SHOW PROFILE :查看SQL执行成本
1、MySQL 查询的优化步骤
数据库调优流程,主要分为两部分**观察(Show status)
**和 行动(Action)
,分别用字母 S 和 A 表示,如下图:
-
周期性的波动,可能是双十一,促销活动等,可以通过
加缓存或更改缓存失效策略
解决。 -
开启慢查询,并设置
long_query_time
参数定义【慢】的阈值,帮助我们定位执行慢的SQL语句,收集慢查询语句,再通过分析工具对慢查询日志进行分析。 -
对慢SQL使用
EXPLAIN
分析其执行计划,或者使用SHOW PROFILE
查看慢SQL每一步骤的时间成本,以确认慢SQL是执行时间长
还是等待时间长
。- 等待时间长,可以
调优服务器参数
,比如适当增加数据库的缓冲池。 - 执行时间长,就考虑
索引设计问题
、关联表数量过多
、数据库表结构设计问题
等。
- 等待时间长,可以
-
以上均不能解决,再考虑是否数据库自身的 SQL 查询性能达到瓶颈。
- 达到瓶颈,就考虑
增加服务器(采用读写分离架构)
,或进行分库分表,比如垂直分库、垂直分表、水平分表等
. - 如未达到瓶颈,则建议再
重新检查
以上各项。
- 达到瓶颈,就考虑
2、查询系统性能参数
MySQL可使用 SHOW STATUS
语句查询 MySQL 数据库的性能参数
、执行频率
等.
-- 执行语句
SHOW [GLOBAL | SESSION] STATUS LIKE '参数';
-- 查询 MySQL 服务器的连接次数,示例:
SHOW STATUS LIKE 'Connections';
-- 查询 MySQL 服务器的慢查询次数,示例:
SHOW STATUS LIKE 'Slow_queries';
参数 | 说明 |
---|---|
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_update | 更新操作的次数。 |
Com_insert | 插入操作的次数,批量插入只累加算一次。 |
Com_delete | 删除操作的次数。 |
last_query_cost | 统计查询成本,结果为查询所需要读取的页数量 |
3、慢查询日志定位问题
MySQL 的慢查询日志,用来记录在MySQL中 响应时间超过阈值
的语句,具体指运行时间超过 long_query_time
值的 SQL,会被记录到慢查询日志中。long_query_time
默认值为 10,指运行 10秒以上(不含10秒)的语句被认定为慢SQL.
MySQL 默认没有开启慢查询日志
,有需求可手动开启。由于开启慢查询日志,会或多或少带来一定的性能影响,如果不是调优需求的话,一般不建议启动该参数
。慢查询日志支持将日志记录写入文件。
3.1、开启慢查询日志参数
-- 查询是否开启了慢查询日志,ON 表示开启,OFF 表示未开启,默认为 OFF.
-- 查询结果中 slow_query_log_file 表示慢查询日志所在文件地址
SHOW VARIABLES LIKE '%slow_query_log%';
-- 查询慢查询 阈值,查询结果单位 为 秒,默认为 10秒。
SHOW VARIABLES LIKE '%long_query_time%';
-- 设置 慢查询阈值为1s。
-- 注意:使用该方式对当前session的long_query_time是失效的,对新连接的客户端有效。
SET GLOBAL long_query_time = 1;
可以通过修改配置文件 my.cnf 或 my.ini
的方式,永久设置。
[mysqld]
slow_query_log=ON # 开启慢查询日志
slow_query_log_file=/var/lib/mysql/my_slow.log # 设置慢查询日志的目录和文件名信息
long_query_time=3 # 设置慢查询阈值为 3 秒
log_output=FILE
3.2、查看慢查询数目
-- 查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%slow_queries%';
-- 慢查询的认定与两个因素有关,除慢查询阈值外,还有一个 扫描过的最少记录数(min_examined_row_limit )
-- 如果查询扫描的记录数,超过该变量指,且超过慢查询阈值,即认定为慢SQL.
-- min_examined_row_limit 默认值为 0
-- 可通过 修改配置文件 my.cnf 或 my.ini 修改 min_examined_row_limit值,
-- 也可通过 set 语句修改 min_examined_row_limit
3.3、慢查询日志的分析工具 mysqldumpslow
-- mysqldumpslow 工具由 MySQL提供。
-- 可以查看 mysqldumpslow 帮助说明
mysqldumpslow -- help
-- 使用 mysqldumpslow 查看 慢日志文件示例
-- 显示5条,不显示查询参数
-- mysqldumpslow -s t -t 5 [慢日志文件全路径地址]
mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu05-slow.log
-- 显示5条,显示查询参数
-- mysqldumpslow -a -s t -t 5 [慢日志文件全路径地址]
mysqldumpslow -a -s t -t 5 /var/lib/mysql/atguigu05-slow.log
3.4、关闭慢查询日志
-
永久性方式
-- 修改配置文件 my.cnf 或 my.ini ,把[mysqld]组下的 slow_query_log 值设为 OFF. -- 修改配置文件后,保存,再重启MySQL服务,即可生效。 [mysqld] slow_query_log=OFF -- 或者注释掉 slow_query_log,保存,再重启MySQL服务,即可生效。 [mysqld] # slow_query_log=OFF
-
临时性方式
-- 使用SET 语句设置 SET GLOBAL slow_query_log=OFF;
3.5、慢查询日志的删除与重建
-- 找到慢查询日志地址
SHOW VARIABLES LIKE '%slow_query_log_file%';
-- 使用 rm -rf [慢查询日志文件全路径地址] 删除
-- mysqladmin flush-logs slow 重新生成慢查询日志文件
mysqladmin -uroot -p flush-logs slow
-- 慢查询日志的删除重建,都是使用 mysqladmin flush-logs slow 来执行的,
-- 一旦执行该命令,慢查询日志都只存在新的日志文件中,如果需要旧的查询日志,就需要事先备份。
4、SHOW PROFILE :查看SQL执行成本
show profile 是MySQL 提供用来分析当前会话中的SQL做了什么、执行的资源消耗情况的工具,用于SQL调优优化。默认情况下处于关闭状态
,并保存最近15次的运行结果。
-- 查看 show profile 启动状态,ON 开启,OFF 关闭。
SHOW VARIABLES LIKE 'profiling';
-- 在会话级别开启
SET profiling = 'ON';
-- 查询最近 15次查询的 query_id
SHOW profiles;
-- 查看最近一次查询的开销
SHOW profile;
-- 查询 query_id 为2 的执行成本
SHOW profile for query 2;
-- 添加 cpu、memory 列参数
SHOW profile cpu,memory;
status列 结果说明:
starting
:查询开始执行的时间点。checking permissions
:检查执行查询所需的权限的时间点。Opening tables
:打开查询中涉及的表的时间点。init
:初始化查询执行的时间点。System lock
:获取系统锁的时间点。optimizing
:查询优化器优化查询的时间点。statistics
:收集统计信息的时间点。preparing
:准备执行查询的时间点。executing
:执行查询的时间点。Sending data
:发送查询结果的时间点。end
:查询执行结束的时间点。query end
:查询结束的时间点,包括发送结果到客户端的时间。closing tables
:关闭查询中涉及的表的时间点。freeing items
:释放查询执行过程中使用的资源的时间点。cleaning up
:清理查询执行过程中的临时数据和状态的时间点。
show profile 常用查询参数:
参数 | 说明 |
---|---|
ALL | 显示所有的开销信息。 |
BLOCK IO | 显示块 IO 开销。 |
CONTEXT SWITCHES | 上下文切换开销。 |
CPU | 显示 CPU 开销信息。 |
IPC | 显示发送和接收开销。 |
MEMORY | 显示内存开销信息。 |
PAGE FAULTS | 显示页面错误开销信息。 |
SOURCE | 显示和 source_function、source_file、source_line 相关的开销信息。 |
SWAPS | 显示交换次数开销信息。 |
日常开发需注意的结论:
- ① converting HEAP to MyISAM:查询结果太大,内存不够,数据往磁盘上搬了。
- ② Creating tmp table:创建临时表。先拷贝数据到临时表,用完再删除临时表。
- Copying to tmp table on disk:把内存中临时表复制到磁盘上,警惕!
- locked.
如果再 show profile 诊断结果中出现了以上四种结果中的一条,则SQL语句需要优化。
SHOW PROFILE 命令相关信息,也可以 通过查 information_schema 中的 profiling 数据表中查到。
EXPLAIN 相关的内容,请看系列文章四:
《MySQL 的索引分类和设计原则》
系列文章:
一: 《搞懂 MySql 的架构和执行流程》
二: 《从InnoDB索引的数据结构,去理解索引》
三: 《从 Hash索引、二叉树、B-Tree 与 B+Tree 对比看索引结构选择》
四: 《MySQL 的索引分类和设计原则》
五: 《MySQL 优化思路篇》
.