String,StringBuffer,StringBuilder的效率问题

    在查看同事写的代码时发现一个问题，拼写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 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
GROUP 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 ms
testStringBufferAppender,cost time is :20110 ms
testStringBuilderAppender,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，不过肯定会通过设计避免安全的问题啦。