在查看同事写的代码时发现一个问题，拼写sql语句的时候每个人的习惯很不一样，有用String直接拼接的，有用StringBuffer来append的，有用StringBuilder来append的。那我们就要考虑一下性能问题了，这三种到底哪一个更快呢？其实很多人都知道答案了，肯定是String拼接最快，那到底快多少呢？下面是我测试用的例子。
一、String,StringBuffer,StringBuilder的效率
我用我们项目中用到的一个sql语句来打个比方。
SELECT a.MapWebsiteDoctorId,SUM(c.RemainAvailableNumber) AS RemainAvailableNumber,GROUP_CONCAT(c.DutyDate) AS DutyDate,GROUP_CONCAT(c.DutyCode) AS DutyCodeFROM DHB_Doctor a LEFT JOIN DHB_Hospital b ON b.MapWebsiteHospitalId=a.MapWebsiteHospitalId LEFT JOIN DR_DoctorDutySource c ON c.MapWebsiteDoctorId=a.MapWebsiteDoctorId AND c.Status=1 AND TO_DAYS(c.DutyDate)>TO_DAYS(NOW()) WHERE a.IsShow=1 AND b.IsShow=1GROUP BY a.MapWebsiteDoctorId,c.MapWebsiteDoctorId ORDER BY SUM(c.RemainAvailableNumber) DESC LIMIT ?,?我们不管这条sql语句的执行效率，只看拼接的速度。分别用String,StringBuffer,StringBuilder来实现。为了能看到明显的效果，我们让每个字符串拼接循环30000000次。
String拼接：
private static void testStringAppender() {long beginTime = System.currentTimeMillis();for(int i = 0; i < 30000000; i++) {String sql = "SELECT a.MapWebsiteDoctorId,SUM(c.RemainAvailableNumber) AS RemainAvailableNumber,GROUP_CONCAT(c.DutyDate) AS DutyDate,GROUP_CONCAT(c.DutyCode) AS DutyCode " +"FROM DHB_Doctor a " +"LEFT JOIN DHB_Hospital b ON b.MapWebsiteHospitalId=a.MapWebsiteHospitalId " +"LEFT JOIN DR_DoctorDutySource c ON c.MapWebsiteDoctorId=a.MapWebsiteDoctorId AND c.Status=1 AND TO_DAYS(c.DutyDate)>TO_DAYS(NOW()) " +"WHERE a.IsShow=1 AND b.IsShow=1 %s %s %s " +"GROUP BY a.MapWebsiteDoctorId,c.MapWebsiteDoctorId " +"ORDER BY SUM(c.RemainAvailableNumber) DESC " +"LIMIT ?,?";}long executeTime = System.currentTimeMillis() – beginTime;System.out.println("testStringAppender,cost time is :" + executeTime + "ms");}StringBuffer拼接：
private static void testStringBufferAppender() {long beginTime = System.currentTimeMillis();for(int i = 0; i < 30000000; i++) {StringBuffer sql = new StringBuffer();sql.append("SELECT a.MapWebsiteDoctorId,SUM(c.RemainAvailableNumber) AS RemainAvailableNumber,GROUP_CONCAT(c.DutyDate) AS DutyDate,GROUP_CONCAT(c.DutyCode) AS DutyCode ");sql.append("FROM DHB_Doctor a ");sql.append("LEFT JOIN DHB_Hospital b ON b.MapWebsiteHospitalId=a.MapWebsiteHospitalId ");sql.append("LEFT JOIN DR_DoctorDutySource c ON c.MapWebsiteDoctorId=a.MapWebsiteDoctorId AND c.Status=1 AND TO_DAYS(c.DutyDate)>TO_DAYS(NOW()) ");sql.append("WHERE a.IsShow=1 AND b.IsShow=1 %s %s %s ");sql.append("GROUP BY a.MapWebsiteDoctorId,c.MapWebsiteDoctorId ");sql.append("ORDER BY SUM(c.RemainAvailableNumber) DESC ");sql.append("LIMIT ?,?");}long executeTime = System.currentTimeMillis() – beginTime;System.out.println("testStringBufferAppender,cost time is :" + executeTime + "ms");}StringBuilder拼接：
private static void testStringBuilderAppender() {long beginTime = System.currentTimeMillis();for(int i = 0; i < 30000000; i++) {StringBuilder sql = new StringBuilder();sql.append("SELECT a.MapWebsiteDoctorId,SUM(c.RemainAvailableNumber) AS RemainAvailableNumber,GROUP_CONCAT(c.DutyDate) AS DutyDate,GROUP_CONCAT(c.DutyCode) AS DutyCode ");sql.append("FROM DHB_Doctor a ");sql.append("LEFT JOIN DHB_Hospital b ON b.MapWebsiteHospitalId=a.MapWebsiteHospitalId ");sql.append("LEFT JOIN DR_DoctorDutySource c ON c.MapWebsiteDoctorId=a.MapWebsiteDoctorId AND c.Status=1 AND TO_DAYS(c.DutyDate)>TO_DAYS(NOW()) ");sql.append("WHERE a.IsShow=1 AND b.IsShow=1 %s %s %s ");sql.append("GROUP BY a.MapWebsiteDoctorId,c.MapWebsiteDoctorId ");sql.append("ORDER BY SUM(c.RemainAvailableNumber) DESC ");sql.append("LIMIT ?,?");}long executeTime = System.currentTimeMillis() – beginTime;System.out.println("testStringBuilderAppender,cost time is :" + executeTime + "ms");}运行之前介绍下我的电脑配置：
执行结果如下：
testStringAppender,cost time is :20 mstestStringBufferAppender,cost time is :20110 mstestStringBuilderAppender,cost time is :19941 ms
至于为什么这样，大家查查官方api吧。很容易找到答案的。
二、I/O
你肯定听过很多人说过，程序运行的瓶颈在I/O上，那我们今天就简单测试一下吧。
早上面的测试代码里加一句
System.out.println(sql);
然后运行一下吧，，我是11点30分开始运行的，现在时间是13：44，我抽了N根烟，吃了个中午饭，到现在没跑完呢。
悲剧啊，CPU一直是13%。
即使配置相当好的生产环境的服务器也不会轻易完成这样的任务。
这回知道为啥大牛们都说瓶颈在I/O上了吧。就一句System.out.println(sql);就可以让程序拖慢很多倍，别说三次握手，四次握手的TCP,HTTP了，很难想象吧，呵呵。就写到这里了。
三、总结
1.去掉你的System.out.println();，不要让它上到你的生产环境。
2.字符串拼接，最好我们用String去连接，当然，我说的是声明的时候。如果考虑到线程安全还得靠StringBuffer，不过肯定会通过设计避免安全的问题啦。
把艰辛的劳作看作是生命的必然，
