目录1 分页查询2 锁并发控制3 返回大数量级结果集4 大批量INSERT1 分页查询分页查询在数据库应用中很常见，几种常见的分页查询场景。分页查询一般是要求排序的，如果可以不排序，性能一般没有问题，下面重点探讨需要排序的情况。常见分页查询写法有以下两种：● 第一种select * from( select a.*, rownum rn from( select * from tab1 u where status = 1 order by u.id) awhere rownum <= 500) bwhere 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) awhere 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日