目录
1 分页查询
2 锁并发控制
3 返回大数量级结果集
4 大批量INSERT
1 分页查询
分页查询在数据库应用中很常见，几种常见的分页查询场景。分页查询一般是要求排序的，如果可以不排序，性能一般没有问题，下面重点探讨需要排序的情况。
常见分页查询写法有以下两种：
● 第一种
select * from
( select a.*, rownum rn from
( select * from tab1 u where status = 1 order by u.id) a
where rownum <= 500
) b
where rn >= 1
● 第二种
select * from tab1 u where status = 1 order by u.id limit 500, 1
场景优化及建议
● 场景1
场景描述：select * from tab1 u where status = 1查出的数据非常多，表中90%
以上的数据满足条件。
优化建议：为了避免对大表全表扫描并且排序，建议走排序字段的索引规避排序（index full sacn），尤其是大部分的查询是访问第一页或前几页的话，这样性能是很高的。
● 场景2
场景描述：select * from tab1 u where status = 1查出的数据非常少，表中5%以
下数据满足条件
优化建议：此时应该走where条件的索引。
● 场景3
场景描述：如果是对一个大表不断翻页，直到把数据处理完
优化建议：如果还用普通的分页查询的SQL，那么越往后会越慢，很多数据会重
复扫描，效率很低。
比较好的做法如下：
select a.*, rownum rn from
( select * from tab1 u where status = 1 and id > :max_id order by u.id) a
where rownum <= 500
或者
select * from tab1 where id > :max_id order by id limit 500;
每次获取上次的最大ID，查询时指定大于上次的最大ID，走ID索引实际不排序，
这样的效率会比较高，每次查询的效率也是比较稳定的。
● 场景4
场景描述：需要对统计分页数据的总数，并且数据量很大
优化建议：如果每次分页查询都需要统计总数，并且数据量还很大，那么性能肯
定很差，建议业务上对结果缓存或者不统计总数，例如可以显示前10页。
2 锁并发控制
场景分析
● 重复插入数据
问题描述：需要判断某条数据是否存在，如果不存在则插入该数据。在并发场景下，如果不做锁控制并且表上没有唯一键的话，就可能出现重复插入数据的情况。
例如，用户订购业务前判断下是否存在该业务，但是用户ID和业务ID并不是联合主键，并发场景下，两个会话同时查询某个用户是否有该业务订购关系时结果都是不存在，然后两个会话就会为该用户重复订购了该业务。
解决方法：
– 通过行锁，在查询用户会否有该订购关系前先锁定用户的某条数据。例如，锁定用户的信息表中的数据，这里肯定不能去锁定要插入的数据，因为数据还不存在，也不能锁定公共数据，那样的话所有用户的并发都受影响了。锁的方式最好是使用select for update，比update开销要小一些。
方案缺点：必须有适合锁定的数据，不够通用；必须在一个事务中，否则commit后锁就不存在了。
– 咨询锁
咨询锁是用户自定义锁，COMMIT/ROLLBACK不会释放锁，需要用户显式的释放锁，会话中断会自动释放锁。
使用咨询锁可以不锁定任何用户数据解决这个问题，并且可以跨事务，推荐使用该方案。
▪ GET_LOCK(name_expr[, timeout_expr])
GET_LOCK()的返回值如下：
1： 成功获取到锁。
0： 未能获取到锁。
通过GET_LOCK()获取到的锁可通过以下两种方式释放：
显式释放：通过调用RELEASE_LOCK()释放。
隐式释放：会话中断(不论正常或异常)时该会话占有的锁自动释放。
▪ RELEASE_LOCK(name_expr)
功能：通过锁名释放会话先前使用GET_LOCK()函数上的锁。
RELEASE_LOCK()的返回值如下：
1： 成功获取所指定的锁。
NULL： 当前会话并不占有所指定的锁。
● 并发更新同一条数据
问题描述：用户开戸后更新当天
开戸人数，如果很多会话要同时更新一条数据，那么事务锁等待会成为性能瓶颈。一般情况业务要避免出现这样的逻辑，如果必须这样做，也要尽量减少锁的影响。如果一个
开戸事务执行100ms，更新
开戸数需要5ms，也是在
开戸事务中，怎么做性能高？
解决方案：以下三种方式，虽然只是更新
开戸数的位置不同，但是对性能影响差
距是很大的。如果在
开戸事务开始就更新用户数，那么差不多要锁定100ms，而如果在最后更新用户数，那么最多锁定几毫秒。所以，对于可能阻塞其他会话的SQL应该尽量放在事务的最后面。
– 先更新
开戸数
– 中间更新
开戸数
– 最后更新
开戸数
3 返回大数量级结果集
如果查询结果返回很多数据，设置fetch_size非常重要，如果fetch_size过小会导致交互
次数多、查询效率低；如果fetch_size过大或者全部返回，客户端程序的内存可能会撑爆。不同数据库的默认fetch_size不同，GaussDB T默认大小是100，对应参数：
_PREFETCH_ROWS。
这个特性非常重要，应用程序在返回大数据量结果集时就不要分批查询了，因为分批
查询比较麻烦，性能也可能很差，只要合理的设置fetch_size就可以一次查询所有数据
了，应用不用分批查询，由数据库分批fetch。
JDBC接口：java.sql.PreparedStatement.setFetchSize(int)
不过，如果一次性查询太多的数据，可能出现快照过旧的问题，需要综合考虑。
4 大批量 INSERT
大批量的insert数据是业务常见的场景，为了提高性能需要注意以下方面：
● 使用动态extent表空间：默认8K的extent性能比较差。
● 要有足够的redo：redo过小会导致日志追尾。
● 使用分区表和分区索引：往一个空分区插入数据比往一个大表插入数据性能高，主要是维护索引的开销不同；同时往不同分区insert比同时往一个分区insert效率高，因为可以减少beffer busy waits。
● 避免过多的索引：表上有过多的索引对insert性能影响很大。
● 一次parse，多次bind：可以减少parse的次数。
● 避免逐条commit：逐条commit回导致log fià sync较多的等待。
● 避免分布式事务：分布式事务开销比单机事务大。
2020年3月13日
