sql 优化
书写高质量SQL的30条建议
一条SQL语句执行得很慢的原因有哪些
limit 优化
有一张财务流水表,未分库分表,目前的数据量为9555695,分页查询使用到了limit,优化之前的查询耗时16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms),按照下文的方式调整SQL后,耗时347 ms (execution: 163 ms, fetching: 184 ms);
操作: 查询条件放到子查询中,子查询只查主键ID,然后使用子查询中确定的主键关联查询其他的属性字段;
原理: 减少回表操作,利用延迟关联或者子查询优化超多分页场景。
-- 优化前SQL
SELECT 各种字段
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
-- 优化后SQL
SELECT 各种字段
FROM `table_name` main_tale
RIGHT JOIN
(
SELECT 子查询只查主键
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
) temp_table ON temp_table.主键 = main_table.主键
sql replace into
REPLACE
的运行与 INSERT
很相似。
只有一点例外
假如表中的一个 旧记录 与一个用于 PRIMARYKEY 或一个 UNIQUE 索引的 新记录 具有相同的值
则在 新记录被插入之前 ,旧记录被删除
操作规则
1. replace into 首先尝试插入数据到表中,如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据
2. 没有此行数据的话,直接插入新数据
注意,除非
表有一个PRIMARY KEY
或UNIQUE索引
,否则,使用一个REPLACE语句没有意义
。该语句会与INSERT
相同,因为没有索引
被用于确定是否新行复制了其它的行
into 关键字可以省略,不过最好加上 into ,这样意思更加直观
所有列的值均取 自在 REPLACE 语句中 被指定的值。所有 缺失的列 被设置为 各自的默认值,这 和INSERT一样
使用REPLACE,必须同时拥有表的INSERT和DELETE权限
。
三种用法
replace into tbl_name(col_name, ...) values(...)
类似于insert into
replace into tbl_name(col_name, ...) select ...
类似于insert select,这种用法并不一定要求列名匹配
MYSQL 不关心 select 返回的 列名,它需要的是列的 位置
例如,replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2;?这个例子使用replace into从?tb2中将所有数据导入tb1中
replace into tbl_name set col_name=value, ...
类似于 update set 用法,使用一个例如“SET col_name = col_name + 1”的赋值
对位于 右侧的列名称 的 引用 会被作为 DEFAULT(col_name) 处理。因此,该赋值相当于 SET col_name = DEFAULT(col_name) + 1
业务 sql 查询优化
一次SQL查询优化原理分析(900W+数据,从17s到300ms)
减少回表,覆盖索引
从20s优化到500ms,我用了这三招
优化索引 -> 联合索引
单线程改成多线程
前端做分页/ 分批调用接口
案发现场
我们每天早上上班前,都会收到一封线上慢查询接口汇总邮件,邮件中会展示接口地址、调用次数、最大耗时、平均耗时和traceId等信息
。
我看到其中有一个批量评分查询接口
,最大耗时达到了20s,平均耗时也有2s。
用skywalking
查看该接口的调用信息
,发现绝大数情况下,该接口响应还是比较快的,大部分情况都是500ms左右就能返回,但也有少部分超过了20s的请求。
这个现象就非常奇怪了。
莫非跟数据有关?
比如:要查某一个组织的数据,是非常快的。但如果要查平台,即组织的根节点,这种情况下,需要查询的数据量非常大,接口响应就可能会非常慢。
但事实证明不是这个原因。
很快有个同事给出了答案。
他们在结算单列表页面中,批量请求了这个接口,但他传参的数据量非常大
。
怎么回事呢?
当初说的需求是这个接口给分页的列表页面调用,每页大小有:10、20、30、50、100,用户可以选择。
换句话说,调用批量评价查询接口,一次性最多可以查询100条记录
。
但实际情况是:结算单列表页面还包含了很多订单。基本上每一个结算单,都有多个订单。调用批量评价查询接口时,需要把结算单和订单的数据合并到一起。
这样导致的结果是:调用批量评价查询接口时,一次性传入的参数非常多
,入参list中包含几百、甚至几千条数据
都有可能。
现状
如果一次性传入几百或者几千个id,批量查询数据还好,可以走主键索引,查询效率也不至于太差。
但那个批量评分查询接口,逻辑不简单。
public List<ScoreEntity> query(List<SearchEntity> list) {
//结果
List<ScoreEntity> result = Lists.newArrayList();
//获取组织id
List<Long> orgIds = list.stream().map(SearchEntity::getOrgId).collect(Collectors.toList());
//通过regin调用远程接口获取组织信息
List<OrgEntity> orgList = feginClient.getOrgByIds(orgIds);
for(SearchEntity entity : list) {
//通过组织id找组织code
String orgCode = findOrgCode(orgList, entity.getOrgId());
//通过组合条件查询评价
ScoreSearchEntity scoreSearchEntity = new ScoreSearchEntity();
scoreSearchEntity.setOrgCode(orgCode);
scoreSearchEntity.setCategoryId(entity.getCategoryId());
scoreSearchEntity.setBusinessId(entity.getBusinessId());
scoreSearchEntity.setBusinessType(entity.getBusinessType());
List<ScoreEntity> resultList = scoreMapper.queryScore(scoreSearchEntity);
if(CollectionUtils.isNotEmpty(resultList)) {
ScoreEntity scoreEntity = resultList.get(0);
result.add(scoreEntity);
}
}
return result;
}
其实在真实场景中,代码比这个复杂很多,这里为了给大家演示,简化了一下。
最关键的地方有两点:
在接口中远程调用了另外一个接口
需要在for循环中查询数据
其中的第1点,即:在接口中远程调用了另外一个接口,这个代码是必须的
。
因为如果在评价表中冗余一个组织code字段,万一哪天组织表中的组织code有修改,不得不通过某种机制,通知我们同步修改评价表的组织code,不然就会出现数据不一致的问题。
很显然,如果要这样调整的话,业务流程上要改了,代码改动有点大。
所以,还是先保持在接口中远程调用
吧。
这样看来,可以优化的地方
只能在:for循环中查询数据
优化索引
由于需要在for循环中,每条记录都要根据不同的条件,查询出想要的数据。
由于业务系统调用这个接口时,没有传id,不好在where条件中用id in (...)
,这方式批量查询数据。
其实,有一种办法不用循环查询,一条sql就能搞定需求:使用or关键字拼接
,例如
(org_code='001' and category_id=123 and business_id=111 and business_type=1)
or (org_code='002' and category_id=123 and business_id=112 and business_type=2)
or (org_code='003' and category_id=124 and business_id=117 and business_type=1)...
这种方式会导致sql语句会非常长,性能也会很差。
其实还有一种写法:
where (a,b) in ((1,2),(1,3)...)
不过这种sql,如果一次性查询的数据量太多的话,性能也不太好
居然没法改成批量查询
,就只能优化单条查询sql的执行效率
了
首先从索引入手
,因为改造成本最低
评价表之前建立一个business_id字段的普通索引,但是从目前来看效率不太理想。
由于我果断加了联合索引
:
alter table user_score add index `un_org_category_business` (`org_code`,`category_id`,`business_id`,`business_type`) USING BTREE;
该联合索引由:org_code、category_id、business_id和business_type四个字段组成。
经过这次优化,效果立竿见影。
批量评价查询接口最大耗时,从最初的20s,缩短到了5s
左右。
多线程
由于需要在for循环中,每条记录都要根据不同的条件,查询出想要的数据。
只在一个线程中查询数据,显然太慢
。
那么,为何不能改成多线程调用?
第二次优化,查询数据库由单线程改成多线程
。
但由于该接口是要将查询出的所有数据,都返回回去的,所以要获取查询结果。
使用多线程调用,并且要获取返回值
,这种场景使用java8中的CompleteFuture
非常合适。
代码调整为:
CompletableFuture[] futureArray = dataList.stream()
.map(data -> CompletableFuture
.supplyAsync(() -> query(data), asyncExecutor)
.whenComplete((result, th) -> {
})).toArray(CompletableFuture[]::new);
CompletableFuture.allOf(futureArray).join();
CompleteFuture
的本质是创建线程执行
,为了避免产生太多的线程
,所以使用线程池
是非常有必要的。
优先
推荐使用ThreadPoolExecutor
类,我们自定义线程池
具体代码如下:
ExecutorService threadPool = new ThreadPoolExecutor(
8, //corePoolSize线程池中核心线程数
10, //maximumPoolSize 线程池中最大线程数
60, //线程池中线程的最大空闲时间,超过这个时间空闲线程将被回收
TimeUnit.SECONDS,//时间单位
new ArrayBlockingQueue(500), //队列
new ThreadPoolExecutor.CallerRunsPolicy()); //拒绝策略
也可以使用ThreadPoolTaskExecutor
类创建线程池:
@Configuration
public class ThreadPoolConfig {
/**
* 核心线程数量,默认1
*/
private int corePoolSize = 8;
/**
* 最大线程数量,默认Integer.MAX_VALUE;
*/
private int maxPoolSize = 10;
/**
* 空闲线程存活时间
*/
private int keepAliveSeconds = 60;
/**
* 线程阻塞队列容量,默认Integer.MAX_VALUE
*/
private int queueCapacity = 1;
/**
* 是否允许核心线程超时
*/
private boolean allowCoreThreadTimeOut = false;
@Bean("asyncExecutor")
public Executor asyncExecutor() {
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
executor.setCorePoolSize(corePoolSize);
executor.setMaxPoolSize(maxPoolSize);
executor.setQueueCapacity(queueCapacity);
executor.setKeepAliveSeconds(keepAliveSeconds);
executor.setAllowCoreThreadTimeOut(allowCoreThreadTimeOut);
// 设置拒绝策略,直接在execute方法的调用线程中运行被拒绝的任务
executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
// 执行初始化
executor.initialize();
return executor;
}
}
经过这次优化,接口性能也提升了5倍
。
从5s左右,缩短到1s左右。
但整体效果还不太理想。
前端优化 限制一次性查询的记录条数/多次发送
经过前面的两次优化,批量查询评价接口性能有一些提升,但耗时还是大于1s
出现这个问题的根本原因是:一次性查询的数据太多。
那么,我们为什么不限制一下,每次查询的记录条数
呢?
第三次优化,限制一次性查询的记录条数
。其实之前也做了限制,不过最大是2000条记录,从目前看效果不好。
限制该接口一次只能查200条记录
,如果超过200条则会报错提示
。
如果直接对该接口做限制
,则可能会导致业务系统出现异常
为了避免这种情况的发生,必须跟业务系统团队一起讨论一下优化方案。
主要有下面两个方案:
前端做分页
在结算单列表页中,每个结算单默认只展示1个订单,多余的分页查询。
这样的话,如果按照每页最大100条记录计算的
话,结算单和订单最多一次只能查询200条记录
。
这就需要业务系统的前端做分页功能,同时后端接口
要调整支持分页查询
。
但目前现状是前端没有多余开发资源。
由于人手不足的原因,这套方案目前只能暂时搁置。
分批调用接口
业务系统后端之前是一次性调用评价查询接口
,现在改成分批调用
比如:之前查询500条记录,业务系统只调用一次查询接口
现在改成业务系统每次只查100条记录,分5批调用
,总共也是查询500条记录
这样不是变慢了吗?
答:如果那5批调用评价查询接口的操作,是在for循环中单线程顺序的,整体耗时当然可能会变慢。
但业务系统也可以改成多线程调用
,只需最终汇总结果
即可。
此时,有人可能会问题:在评价查询接口的服务器多线程调用,跟在其他业务系统中多线程调用不是一回事?
还不如把批量评价查询接口的服务器中,线程池的最大线程数调大一点?
显然你忽略了一件事:线上应用一般不会被部署成单点
。绝大多数情况下,为了避免因为服务器挂了
,造成单点故障
,基本会部署至少2个节点
。这样即使一个节点挂了
,整个应用也能正常访问
。
当然也可能会出现这种情况:假如挂了一个节点
,另外一个节点
可能因为访问的流量太大了
,扛不住压力,也可能因此挂掉
。
换句话说,通过业务系统中的多线程调用接口
,可以将访问接口的流量负载均衡到不同的节点上
。
他们也用8个线程,将数据分批,每批100条记录,最后将结果汇总。
经过这次优化,接口性能再次提升了1倍。
从1s左右,缩短到小于500ms。
温馨提醒一下,无论是在批量查询评价接口查询数据库,还是在业务系统中调用批量查询评价接口,使用多线程调用,都只是一个临时方案,并不完美。
这样做的原因主要是为了先快速解决问题
,因为这种方案改动是最小的
。