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

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

SQL优化是数据库性能优化的重要环节，可以提高数据库查询效率，减少系统资源的消耗。对于优化SQL语句，首先要判断SQL是否有问题，然后根据问题点进行优化。以下是SQL优化的一些通用技巧和优化案例。

一、判断问题SQL

判断SQL是否有问题时可以通过两个表象进行判断：系统级别表象和SQL语句表象。

1. 系统级别表象

系统级别表象主要从CPU消耗、IO等待、页面响应时间等方面进行判断。可以使用sar命令、top命令等工具查看当前系统状态，也可以通过Prometheus、Grafana等监控工具观察系统状态。

2. SQL语句表象

SQL语句表象主要从冗长、执行时间过长、全表扫描等方面进行判断。可以通过执行计划来判断SQL的问题点，例如全表扫描等。获取问题SQL可以使用目前主流数据库的慢查询SQL获取工具，例如MySQL慢查询日志、Percona公司的ptquery等工具，Oracle AWR报告、测试工具loadrunner等，达梦数据库AWR报告、测试工具loadrunner等，相关内部视图如v$、$session_wait等；也可以使用GRIDCONTROL监控工具、达梦性能监控工具（dem）等获取。

二、SQL编写技巧

SQL编写有以下几个通用的技巧：合理使用索引，使用UNION ALL替代UNION，避免select *写法，JOIN字段建议建立索引，避免复杂SQL语句，避免where 1=1写法，避免orderby rand()类似写法。

三、SQL优化

执行计划完成SQL优化一定要先读执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。SQL优化可以从表结构变更、增加索引、SQL改写等方面入手。

以下是一个SQL优化案例：

优化案例：

表结构：

CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_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 NULL AUTO_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 NULL AUTO_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`)
);

SQL语句：

登录后复制三张表关联，查询当前用户在当前时间前后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;

原执行时间：1.35s

原执行计划：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE c NULL ALL PRIMARY,idx_user_id NULL NULL 4 25.00 Using where; Using temporary; Using filesort
1 SIMPLE b NULL ref PRIMARY,idx_user_id_sell_name,idx_user_id_sell_name_2 idx_user_id_sell_name 53 test.c.user_id 1 100.00
1 SIMPLE a NULL ref idx_sellname_gmt_sellid idx_sellname_gmt_sellid 306 test.b.seller_name 1 100.00 Using where
字段解释：
id：每个被独立执行的操作标识，标识对象被操作的顺序，id值越大，先被执行，如果相同，执行顺序从上到下
select_type：查询中每个select字句的类型
table：被操作的对象名称，通常是表名，但有其他格式
partitions：匹配的分区信息(对于非分区表值为NULL)
type：连接操作的类型，从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
possible_keys：可能用到的索引
key：优化器实际使用的索引(最重要的列)
key_len：被优化器选定的索引键长度，单位是字节
ref：表示本行被操作对象的参照对象，无参照对象为NULL
rows：查询执行所扫描的元组个数（对于InnoDB，此值为估计值）
filtered：条件表上数据被过滤的元组个数百分比
Extra：执行计划的重要补充信息

初步优化思路：

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`);

优化后执行时间：1.16s

优化后执行计划：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE b NULL ref PRIMARY,idx_user_id_sell_name,idx_user_id_sell_name_2 idx_user_id_sell_name_2 57 const,test.c.user_id 1 100.00 Using index condition; Using temporary; Using filesort
1 SIMPLE c NULL ref PRIMARY,idx_user_id idx_user_id 53 test.b.user_id 1 100.00 Using where
1 SIMPLE a NULL ref idx_sellname_gmt_sellid,idx_sellname_gmt_sellid_2 idx_sellname_gmt_sellid_2 306 test.b.seller_name,test.b.seller_id 1 100.00 Using where

优化效果不明显，继续优化：

将字段gmt_create类型改为datetime类型，再次查看执行时间和执行计划。

alter table a modify `gmt_create` datetime DEFAULT NULL;

优化后执行时间：0.98s

优化后执行计划：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE c NULL ref PRIMARY,idx_user_id idx_user_id 53 const,const,const 1 100.00 Using where; Using temporary; Using filesort
1 SIMPLE b NULL ref PRIMARY,idx_user_id_sell_name,idx_user_id_sell_name_2,idx_user_id idx_user_id_sell_name_2 57 const,test.c.user_id 1 100.00 Using where; Using index
1 SIMPLE a NULL ref idx_sellname_gmt_sellid,idx_sellname_gmt_sellid_2 idx_sellname_gmt_sellid_2

扫码添加UTHEME微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。