怎么优化SQL

70次阅读

没有评论

共计 3304 个字符，预计需要花费 9 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下怎么优化 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$、$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 语句

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

• 避免 where 1= 1 写法

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

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

SQL 优化执行计划

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

字段解释 id 每个被独立执行的操作标识，标识对象被操作的顺序，id 值越大，先被执行，如果相同，执行顺序从上到下 select_type 查询中每个 select 字句的类型 table 被操作的对象名称，通常是表名，但有其他格式 partitions 匹配的分区信息(对于非分区表值为 NULL)type 连接操作的类型 possible_keys 可能用到的索引 key 优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为 const、eq_reg、ref、range、index 和 ALL。当出现 ALL 时表示当前 SQL 出现了“坏味道”key_len 被优化器选定的索引键长度，单位是字节 ref 表示本行被操作对象的参照对象，无参照对象为 NULLrows 查询执行所扫描的元组个数（对于 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;

查看执行时间

查看执行计划