首页 > 代码库 > 后端系统性能优化(第一季 3 sql优化)
后端系统性能优化(第一季 3 sql优化)
昨天的博文介绍了如何去发现坏代码，如何优雅的去实现一个应用内的监控程序。
博文地址：后端系统性能优化（第一季 2 找出坏代码）
当然发现了坏代码之后，我们还是要想办法来改掉它，也许它会很顽固。今天说说性能优化的一个非常重要的部分：sql的优化
今天要说的不是怎么来写优秀的，性能好的sql，这些DBA们会比我更加专业。在我们公司，凡是DBA能优化的sql，DBA都在内部消化了，需要反馈给我们的，说明他们可能也束手无策。也是我们该出手的时候了。
insert,update这类型的sql,性能一般不会太慢，我把这其中可能出现的问题糅合在一个例子中，组成这样的一个场景：锁定一条记录，插入10W条pojo记录后更新锁定记录的状态为已完成，使用oracle数据库，需要每条记录来获取一个序列作为主键值来插入，每条记录还需获取一个序列用于产生no，不能与id相同，如下代码：
| 1 2 3 | PojoOrder order = pojoOrderDao.lock(pojoOrderId);<br>for(Pojo p:pojoList){ long sequce = pojoDao.genSequce(); p.setId(sequce);<br> long noSequce = pojoDao.genNoSequce();<br> p.setNo(noSequce);<br> pojoDao.save(p);<br>}<br>order.setStatus(Status.FINISH);<br>pojoOrderDao.save(order); |
10W条数据循环单条插入在遇上高峰期时非常慢，而同一个事务中的悲观锁一直锁定一条记录，锁定的时间过长，进而出现了锁等待，数据库连接无法释放，数据库负载飙升，甚至导致数据库的宕机。
以上简单的几行代码在业务实现上可能没什么问题，但是从性能的角度上说，它就是几行坏的代码，我们进行了一些改造，
首先，不再去数据库取序列，将序列值写在insert语句中（这种情况就不要用hibernate的save了，使用原生sql）。类似 insert into pojo(id,no)values(SEQ_pojo.nextVal,SEQ_pojo_no.nextVal) 。
其次，将单条提交修改为批量每1000条提交一次。
再次，将锁定pojoOrder修改状态的三步操作 锁定记录-->批量插入-->更新成已完成 为分成三个事务四步来做，锁定-->更新成处理中-->提交事务 批量插入(发生异常把状态更新成未处理)提交事务 插入完成---更新成已完成-->提交事务，分三个事务来控制。这种方式也许像之前的代码一样能保证原子性，我们牺牲这种一致性来提高性能，减少风险，作为一个并发量还算大的web应用也是非常值得。而且这几年来，没有这种强一致性不对而引起故障，发生数据不一致的概率是非常非常低。
经过以上的处理后，插入的性能得到了大幅的提升，也没有再出现频繁的锁等待的情况。
插入和更新上的问题，我们遇到的比较少，最让人头疼的莫过于是一些复杂的查询，为了满足业务的要求，不得不写一些查询语句来获得基础的数据，这个时候性能很差的sql的优化我们需要根据不同的情况来出不同的优化方案，下面列举几个比较常见的场景。
1、关联改成单表。数据库表中可能经常会有一些字典表来描述某个值的意义，有时候一个业务表中会有好几个字典表，查询的时候需要关联字典表的属性而非字典的id来进行关联查询。如:
| 1 | select m.* from M m inner join C c on m.c_id = c.id inner join B b on m.b_id = b.id where c.cw = ? and b.px=? and m.status = ? and m.create_time > sysdate -10 |
其中B,C两表均为小表，数据量不大，c.cw 与 c.id 一对一的关系，b.px 与 b.id 一对一的关系。这种查询的关联其实是完全没有必要的。直接可以改成
| 1 | select m.* from M m where m.c_id = ? and m.b_id = ? and m.status = ? and m.create_time = sysdate -10; |
其中c.cw 与 c.id 的值以及 b.id 与 b.px 值的相互映射，简单的利用一下缓存就可以做到了。就是B,C两表数据都放入到缓存中，需要的时候直接从缓存中取即可。
2、执行过于的频繁的查询sql降低执行频率。这种场景大家都应该能想到，本身处理这种情况的方案也非常简单，使用缓存即可，缓存相信很多同学都会用了，但是要利用好缓存也非常有讲究，缓存过期的策略更是需要慎重，现在笔者大部分使用的过期策略均有主动过期(数据更新时进行delete缓存操作)和定时过期(设置一个缓存时间，到了时间后自动过期)。缓存用好了确实能很大程度上降低数据库的压力，但是用不好，就很有可能造成数据脏读的问题。
用缓存来缓解执行频率是一个比较好的方法，但是在一些关键业务上必须满足非常频繁的读实时的数据的时候，该怎么办呢？如下一条非常简单的sql
| 1 | select * from A a where a.id = ? |
这条sql本身是没有多大的性能问题，执行的速度非常快，但是DBA很严肃的告诉我们，必须降低它的执行频率。我们对这条sql进行了监控，发现高峰期每分钟就有20K次的调用，调用的频率非常高。检查代码之后发现，很多这种简单查询是放在循环中来查的。改成批量查询，在可以增加缓存的地方增加缓存，sql的执行频率降低到每分钟2K-5K次。
DBA也给我们提供了一个方法，通过增加hint的方式，减少单条sql的解析次数。 这里有比较详细的介绍：http://www.dbafree.net/?p=778
| 1 2 3 4 5 | select /**1*/ * from A a where a.id = ?select /**2*/ * from A a where a.id = ?select /**3*/ * from A a where a.id = ?select /**4*/ * from A a where a.id = ?select /**5*/ * from A a where a.id = ? |
3、分区表增加分区键值条件。这个很好理解，我们oracle数据的表都是分区表，增加分区键值之后，oracle进行数据查找的时候可以很快的定位到哪个区，避免进行全表的扫描。如: select a.id,a.account from A a where a.register_time > sysdate -10 ; 如果A表的分区键值是 create_time .这条sql如果不增加create_time的条件，将会扫描所有的区来查找register_time > sysdate-10条件的数据，而如果增加了分区值的条件，会根据分区键值来扫描对应的分区，其他的分区将不扫描。
4、把不需要的字段的select去除。我们实际的应用中，由于种种原因，查询一条记录的时候直接就select * 或者把所有的字段都列出来之后查询返回。这种写法在一定程度上能提高编码的效率，提高了重用性，但是在性能上，我们不提倡这种写法，为了其中的两个字段值查出来30个字段，好吧，你太不环保了。我们有一个表有一个字段值存放的是大字符串，然后有一天发现，我们在查这个表的时候数据非常慢，dba检查了索引什么的都没有太大的问题，后来我们发现存放这个非常大的字符串的值也被查出来了，由于网络传输等等因素，返回结果很慢，最可恶的是，业务中并没有使用到这个大字符串。后来我们把这个字段和一些不需要的字段从select列表中删除，皆大欢喜。
5、死锁，是系统性能的死对头，当然要完全避免它。之前业务量还不是那么高的时候，我们的系统发生过一次死锁，具体的业务场景是：
A表中的记录 a 首先被方法 M 锁定，悲观锁获取 B 表中的记录 b 的时候发现 b记录被另外一条线程的 N 方法锁定，N方法获取 A表中记录 a 的锁的时候发现，a记录被 方法 M 锁定，两条线程相互等待锁释放。这种场景，锁都是无法释放的。
| 1 2 3 4 5 6 | M方法线程select * from A where id = 1 for update // 这条记录锁定的时候，N方法的 B表记录也被锁定select * from B where aid = 1 for update //等待获取B表记录的锁N方法线程select * from B where aid = 1 for update //锁定select * from A where id = 1 for update //等待M方法线程释放锁 |
死锁场景还有不少（两个不同的线程执行同一个方法也可能造成死锁），各个场景的解决方案原理都一样，不需要锁的去掉锁，实在无法避免的，调整锁定的顺序成一致或者改成悲观锁+重试机制。如上面这种场景可以改成同样的锁定顺序即可（注意：根据自己的业务来看是否适合自己）
| 1 2 3 4 5 6 | M方法线程select * from B where aid = 1 for updateselect * from A where id = 1 for updateN方法线程select * from B where aid = 1 for updateselect * from A where id = 1 for update |
至于两个不同的线程在同一个方法中的死锁也是有的，一般是在循环update操作的时候，如果方法被并发调用，而集合中的元素的顺序又不相同。
| 1 2 3 4 | for(Pojo p:pojoList){ pojoDao.update(p);}//想象一下如果第一条线程更新的时候是a记录在第一条,n记录在最后一条，而另外一条线程同时更新的时候n在第一条而a记录在最后一条。 |
当然很多时候调用方可能会告诉你，不可能出现并发调用的情况，但是从服务端的角度，我们需要完全避免出现死锁的概率。这种情况在更新之前将集合中的元素按照一个规则排序就可以了。
总之，在平时的工作中，我们会遇到各种各样因为sql引起的性能问题，sql上的优化空间非常少，优化的手段也比较固定。优化之后所反应出来的效果也不尽一样，有的效果不错，大部分都是效果一般，如果有些sql实在没有优化空间而又天天被DBA追着的时候，也许可以考虑换一种业务处理方案可以来避免使用这种sql。
在完成一些需求的时候，我们如果能多考虑考虑性能，能不使用到数据库就不使用数据库。而实际上很多开发人员的思维比较固定，没有充分利用缓存，内存的意识和观念，认为什么都要存在数据库中，什么都从数据库中取才是靠谱的。也许我们可以还可以用其他的方法来达到跟数据库一样的可靠性。
下一节，说说系统性能优化的第三篇：调用链优化