用实例告诉你该如何优化SQL

夏静小哥_8369

夏静小哥_8369

2021-08-04

1820人浏览

转载

虽然现今硬件成本已经下降,通过升级硬件提升系统性能也是常用的优化方式。而实时性要求很高的系统,还是要从sql方面进行优化,今天我们从实例的出发,介绍该如何优化sql。

判断问题SQL

判断SQL是否有问题时可以通过两个表象进行判断:

  • 系统级别表象

    • CPU消耗严重

    • IO等待严重

    • 页面响应时间过长

    • 应用的日志出现超时等错误

可以使用sar命令,top命令查看当前系统状态。也可以通过Prometheus、Grafana等监控工具观察系统状态。

640?wx_fmt=png

  • SQL语句表象

    • 冗长

    • 执行时间过长

    • 从全表扫描获取数据

    • 执行计划中的rows、cost很大

冗长的SQL都好理解,一段SQL太长阅读性肯定会差,而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手,如下所示:

640?wx_fmt=png

执行计划告诉我们本次查询走了全表扫描Type=ALL,rows很大(9950400)基本可以判断这是一段"有味道"的SQL。

获取问题SQL

不同数据库有不同的获取方法,以下为目前主流数据库的慢查询SQL获取工具

  • MySQL

    • 慢查询日志

    • 测试工具loadrunner

    • Percona公司的ptquery等工具

  • Oracle

    • AWR报告

    • 测试工具loadrunner等

    • 相关内部视图如v$、$session_wait等

    • GRID CONTROL监控工具

  • 达梦数据库

    • AWR报告

    • 测试工具loadrunner等

    • 达梦性能监控工具(dem)

    • 相关内部视图如v$、$session_wait等

SQL编写技巧

SQL编写有以下几个通用的技巧:

• 合理使用索引

索引少了查询慢;索引多了占用空间大,执行增删改语句的时候需要动态维护索引,影响性能 选择率高(重复值少)且被where频繁引用需要建立B树索引;一般join列需要建立索引;复杂文档类型查询采用全文索引效率更好;索引的建立要在查询和DML性能之间取得平衡;复合索引创建时要注意基于非前导列查询的情况

• 使用UNION ALL替代UNION

UNION ALL的执行效率比UNION高,UNION执行时需要排重;UNION需要对数据进行排序

• 避免select * 写法

执行SQL时优化器需要将 * 转成具体的列;每次查询都要回表,不能走覆盖索引。

• JOIN字段建议建立索引

一般JOIN字段都提前加上索引

• 避免复杂SQL语句

提升可阅读性;避免慢查询的概率;可以转换成多个短查询,用业务端处理

happycapy
happycapy

happycapy是一款AI智能体工具,Trickle 团队推出的云端 AI Agent 计算机。

下载

• 避免where 1=1写法

• 避免order by rand()类似写法

RAND()导致数据列被多次扫描

SQL优化

执行计划

完成SQL优化一定要先读执行计划,执行计划会告诉你哪些地方效率低,哪里可以需要优化。我们以MYSQL为例,看看执行计划是什么。(每个数据库的执行计划都不一样,需要自行了解)

640?wx_fmt=png

字段 解释
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;

查看数据量  

aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9FbnpBd3ZwVFFKNzY5TTZaaWNvbjRWVGdpY2FMdTNyN0s2R2lhY21zTjgySE1JZFh5aWJWWjFOZmhmeUxJOGlhWUl4SU54UFdxa1Nsalh5UFlxSHJOMVU2MWljUS82NDA_d3hfZm10PXBuZw.png

原执行时间

aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9FbnpBd3ZwVFFKNzY5TTZaaWNvbjRWVGdpY2FMdTNyN0s2M0pydlYyMlNWbENuRU1HZm1jSXBxaWNOWGliV2I4elBVbjdLMUVURnIzNWNXdmliQWYzQUlDRjV3LzY0MD93eF9mbXQ9cG5n.png

原执行计划

640?wx_fmt=png

初步优化思路

  1. SQL中 where条件字段类型要跟表结构一致,表中     user_id 为varchar(50)类型,实际SQL用的int类型,存在隐式转换,也未添加索引。将b和c表     user_id 字段改成int类型。

  2. 因存在b表和c表关联,将b和c表     user_id创建索引

  3. 因存在a表和b表关联,将a和b表     seller_name字段创建索引

  4. 利用复合索引消除临时表和排序

初步优化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`);

 查看优化后执行时间

aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9FbnpBd3ZwVFFKNzY5TTZaaWNvbjRWVGdpY2FMdTNyN0s2empYMm04ZG4yUDZvbXNTZkwxSFg2UFZKVlVjQUlsR1ZzSTB5cWw1ZWJNdTZIaGROV292aWJSQS82NDA_d3hfZm10PXBuZw.png

查看优化后执行计划

640?wx_fmt=png

查看warnings信息

640?wx_fmt=png

继续优化alter table a modify "gmt_create" datetime DEFAULT NULL;

查看执行时间

aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9FbnpBd3ZwVFFKNzY5TTZaaWNvbjRWVGdpY2FMdTNyN0s2UUFNcHh4TzI5R0huOEwxbmhYbmJTc2JBOFRHa2ljY1dpYVBsS3ptNWhLb1k3Q1RpYVRWU1o3U0ZnLzY0MD93eF9mbXQ9cG5n.png

查看执行计划

640?wx_fmt=png

总结

  1. 查看执行计划 explain

  2. 如果有告警信息,查看告警信息 show warnings;

  3. 查看SQL涉及的表结构和索引信息

  4. 根据执行计划,思考可能的优化点

  5. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作

  6. 查看优化后的执行时间和执行计划

  7. 如果优化效果不明显,重复第四步操作

相关推荐:《mysql教程》

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

sql优化

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

20

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

20

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

20

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

20

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

120

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
SQL优化与排查(MySQL版)
SQL优化与排查(MySQL版)

共26课时 | 3.3万人学习