前面一篇文章从实例的角度进行数据库优化, 通过配置一些参数让数据库性能达到最优. 但是一些 "不好" 的 SQL 也会导致数据库查询变慢, 影响业务流程. 本文从 SQL 角度进行数据库优化, 提升 SQL 运行效率.
判断问题 SQL
判断 SQL 是否有问题时可以通过两个表象进行判断:
系统级别表象
CPU 消耗严重
IO 等待严重
页面响应时间过长
应用的日志出现超时等错误
可以使用 sar 命令, top 命令查看当前系统状态.
也可以通过
Prometheus,Grafana
等监控工具观察系统状态.(感兴趣的可以翻看我之前的文章)
SQL 语句表象
冗长
执行时间过长
从全表扫描获取数据
执行计划中的 rows,cost 很大
冗长的 SQL 都好理解, 一段 SQL 太长阅读性肯定会差, 而且出现问题的频率肯定会更高. 更进一步判断 SQL 问题就得从执行计划入手, 如下所示:
执行计划告诉我们本次查询走了全表扫描 Type=ALL,rows 很大 (9950400) 基本可以判断这是一段 "有味道" 的 SQL.
获取问题 SQL
不同数据库有不同的获取方法, 以下为目前主流数据库的慢查询 SQL 获取工具
MySQL
慢查询日志
测试工具 loadrunner
Percona 公司的 ptquery 等工具
Oracle
AWR 报告
测试工具 loadrunner 等
相关内部视图如 v$sql,v$session_wait 等
GRID CONTROL 监控工具
达梦数据库
AWR 报告
测试工具 loadrunner 等
达梦性能监控工具(dem)
相关内部视图如 v$sql,v$session_wait 等
SQL 编写技巧
SQL 编写有以下几个通用的技巧:
• 合理使用索引
索引少了查询慢; 索引多了占用空间大, 执行增删改语句的时候需要动态维护索引, 影响性能
选择率高 (重复值少) 且被 where 频繁引用需要建立 B 树索引; 一般 join 列需要建立索引; 复杂文档类型查询采用全文索引效率更好; 索引的建立要在查询和 DML 性能之间取得平衡; 复合索引创建时要注意基于非前导列查询的情况
• 使用 UNION ALL 替代 UNION
UNION ALL 的执行效率比 UNION 高, UNION 执行时需要排重; UNION 需要对数据进行排序
• 避免 select * 写法
执行 SQL 时优化器需要将 * 转成具体的列; 每次查询都要回表, 不能走覆盖索引.
• JOIN 字段建议建立索引
一般 JOIN 字段都提前加上索引
• 避免复杂 SQL 语句
提升可阅读性; 避免慢查询的概率; 可以转换成多个短查询, 用业务端处理
• 避免 where 1=1 写法
• 避免 order by rand()类似写法
RAND()导致数据列被多次扫描
SQL 优化
执行计划
完成 SQL 优化一定要先读执行计划, 执行计划会告诉你哪些地方效率低, 哪里可以需要优化. 我们以 MySQL 为例, 看看执行计划是什么.(每个数据库的执行计划都不一样, 需要自行了解)
explain sql
字段 | 解释 |
---|---|
id | 每个被独立执行的操作标识,标识对象被操作的顺序,id 值越大,先被执行,如果相同,执行顺序从上到下 |
select_type | 查询中每个 select 字句的类型 |
table | 被操作的对象名称,通常是表名,但有其他格式 |
partitions | 匹配的分区信息(对于非分区表值为 NULL) |
type | 连接操作的类型 |
possible_keys | 可能用到的索引 |
key | 优化器实际使用的索引 ( 最重要的列 ) 从最好到最差的连接类型为 const、eq_reg、ref、range、index 和 ALL。当出现 ALL 时表示当前 SQL 出现了“坏味道” |
key_len | 被优化器选定的索引键长度,单位是字节 |
ref | 表示本行被操作对象的参照对象,无参照对象为 NULL |
rows | 查询执行所扫描的元组个数(对于 innodb,此值为估计值) |
filtered | 条件表上数据被过滤的元组个数百分比 |
extra | 执行计划的重要补充信息,当此列出现 Using filesort , Using temporary 字样时就要小心了,很可能 SQL 语句需要优化 |
接下来我们用一段实际优化案例来说明 SQL 优化的过程及优化技巧.
优化案例
表结构
CREATE TABLE `a` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_id` bigint(20) DEFAULT NULL, `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `b` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_name` varchar(100) DEFAULT NULL, `user_id` varchar(50) DEFAULT NULL, `user_name` varchar(100) DEFAULT NULL, `sales` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `c` ( `id` int(11) NOT NULLAUTO_INCREMENT, `user_id` varchar(50) DEFAULT NULL, `order_id` varchar(100) DEFAULT NULL, `state` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) );
三张表关联, 查询当前用户在当前时间前后 10 个小时的订单情况, 并根据订单创建时间升序排列, 具体 SQL 如下
select a.seller_id, a.seller_name, b.user_name, c.state from a, b, c where a.seller_name = b.seller_name and b.user_id = c.user_id and c.user_id = 17 and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create;
查看数据量
原执行时间
原执行计划
初步优化思路
SQL 中 where 条件字段类型要跟表结构一致, 表中 user_id 为 varchar(50)类型, 实际 SQL 用的 int 类型, 存在隐式转换, 也未添加索引. 将 b 和 c 表 user_id 字段改成 int 类型.
因存在 b 表和 c 表关联, 将 b 和 c 表 user_id 创建索引
因存在 a 表和 b 表关联, 将 a 和 b 表 seller_name 字段创建索引
利用复合索引消除临时表和排序
初步优化 SQL
alter table b modify `user_id` int(10) DEFAULT NULL; alter table c modify `user_id` int(10) DEFAULT NULL; alter table c add index `idx_user_id`(`user_id`); alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`); alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
查看优化后执行时间
查看优化后执行计划
查看 warnings 信息
继续优化
alter table a modify "gmt_create" datetime DEFAULT NULL;
查看执行时间
查看执行计划
优化总结
查看执行计划 explain
如果有告警信息, 查看告警信息 show warnings;
查看 SQL 涉及的表结构和索引信息
根据执行计划, 思考可能的优化点
按照可能的优化点执行表结构变更, 增加索引, SQL 改写等操作
查看优化后的执行时间和执行计划
如果优化效果不明显, 重复第四步操作
请关注个人公众号: JAVA 日知录
来源: https://www.cnblogs.com/jianzh5/p/11781053.html