SQL数据库慢查询优化
数据库优化
-
数据库优化要保证以下三点:
- 日志不能小
- 缓存足够大
- 连接足够用
-
数据库事务提交后需要将事务对数据的修改刷新到磁盘上,这样才能保证数据的持久性.这个刷新过程是一个随机写的过程,性能低.如果每次事务提交都需要对磁盘进行刷新,会极大影响数据库的性能
-
可以在数据库架构设计中使用以下方式进行优化:
- 先将事务写到日志文件RedoLog, 将随机写优化成顺序写
- 添加一层缓存结构Buffer, 将每次写优化成批量写
目标
-
根据不同的角色,数据库优化包含以下几个目标:
-
业务角度: 减少客户端页面响应时间
-
数据库角度: 减少数据库SQL响应时间
-
数据库服务器角度:
- 充分使用数据库服务器物理资源
- 减少数据库服务器CPU的使用率
- 减少数据库服务器IO使用率
- 减少数据库服务器内存使用率
-
指标
- 减少SQL的平均响应时间
- 减少数据库服务器CPU的使用率
- 减少数据库服务器的IO使用率
流程
- 了解优化问题: 首先要尽可能了解优化的问题
- 收集系统信息: 收集问题期间系统信息并做好存档
- 制定优化目标: 根据当前系统问题表现制定优化目标并和客户沟通目标达成一致
- 分析性能问题: 通过一系列工具分析系统问题
- 制定优化方案
- 实施优化方案
数据库参数优化
-
数据库架构都包括以下几个方面,可以调整以下方面参数使得数据库达到最佳性能:
- 数据缓存
- SQL解析区
- 排序内存
- REDO以及NUDO
- 锁 , LATCH, MUTEX
- 监听及连接
- 文件读写性能
Oracle
| 参数类别 | 参数名 | 参数值 | 说明 |
|---|---|---|---|
| 数据缓存 | SGA_TAGET MEMORY_TARGET DB_CACHE_SIZE | 物理内存的70% - 80% | 越大越好 |
| SQL解析 | SHARED_POOL_SIZE | 4GB - 16GB | 不宜设置过大6GB |
| 监听连接 | PROCESS SESSIONS OPEN_CURSORS | 根据业务需求设置 | 一般为业务预估连接数的120% |
| 其余参数 | SESSION_CACHED_CURSORS | 大于200 | 解析 |
MySQL
- 对于MySQL的InnoDB引擎有如下参数设置:
| 参数类别 | 参数名 | 参数值 | 说明 |
|---|---|---|---|
| 数据缓存 | INNODB_BUFFER_POOL_SIZE | 物理内存的50% - 80% | 越大越好 |
| 日志记录 | Innodb_log_buffer_size sync_binlog | 16MB - 32MB 1, 100, 0 | 根据运行状况调整 1时安全性最好 |
| 监听连接 | max_connections | 根据业务需求设置 | 可以预留一部分值 |
| 文件读写性能 | innodb_flush_log_at_trx_commit | 2 | 安全和性能的折中考虑 |
| 其余参数 | wait_timeout interactive_timeout | 28800 | 避免应用连接中断 |
SQL优化
定位慢查询SQL
-
定位慢查询SQL可以通过两个表象进行判断
-
系统级表象:
-
CPU消耗严重
-
IO等待严重
-
页面响应时间过长
-
项目日志出现超时等错误
- 使用sar命令和top命令查看当前系统的状态
- 也可以使用Prometheus和Grafana监控工具查看当前系统状态
-
-
SQL语句表象:
- SQL语句冗长
- SQL语句执行时间过长
- SQL从全表扫描中获取数据
- 执行计划中的rows和cost很大
-
-
根据不同的数据库使用不同的方式获取问题SQL
-
MySQL:
- 慢查询日志
- 测试工具loadrunner
- ptquery工具
-
Oracle:
- AWR报告
- 测试工具loadrunner
- 相关内部视图v$, $session_wait
- GRID CONTROL监控工具
-
SQL语句优化
-
合理使用索引:
- 索引少了查询慢. 索引多了占用空间大,执行增删改语句需要动态维护索引,影响性能
- 选择率高,重复值少并且where中频繁引用的字段需要建立B树索引
-
使用UNION ALL替代UNION
-
禁止使用SELECT * 的写法
-
对join的字段建立索引
-
避免复杂的SQL语句,可以转换成多个子查询,在业务层处理
-
使用explain sql查看SQL的执行计划:
| 字段 | 说明 |
|---|---|
| id | 每一个被执行的操作标识,标识对象被操作的顺序 ID的值较大的先被执行.如果相同,执行顺序从上向下 |
| select_type | 查询中每个select语句的类型 |
| table | 被操作的对象的名称,通常是表名.也会包含其余格式 |
| partitions | 匹配的分区的信息,对于非分区的表值为NULL |
| type | 连接操作的类型 |
| possible_keys | 可能用到的索引 |
| key | 优化器实际使用的索引 最好到最差的索引依次为const, eq_reg, ref, range, index, ALL 对于ALL类型的索引一定要进行优化 |
| key_len | 优化器选定的索引键长度,以字节为单位 |
| ref | 表示本行对象被操作对象的参照对象, 对于无参照对象时为NULL |
| rows | 执行查询所扫描的元组个数 |
| filtered | 条件表上数据被过滤的元组个数百分比 |
| extra | 计划执行的重要补充信息 当出现Using filesort, Using temporary时要对SQL语句进行优化 |
总结
- 使用explain查看SQL语句的执行计划
- 如果有告警信息,查看告警信息的show warnings
- 查看SQL语句涉及的表结构和索引信息
- 根据执行计划对SQL语句需要优化的地方进行优化
- 根据需要优化的情况执行表结构的修改,索引的添加 ,SQL语句的改写等操作
- 再次使用explain查看优化后的执行时间和执行计划
- 根据优化效果选择继续优化,还是优化成功
近期评论