SQL优化 COUNT()优化 COUNT(*)优化 SUM(ID=‘ID’)替代COUNT()
平时使用count来计算数据库中的数据的数量，
但是在数据量大的时候考虑用sum。
以下的例子是在一千万的表中查询数据，结果是采用连续查询五次的平均值，
可以发现：
1.在只是查询一个结果的时候，count的时间和sum的时间是相差很小，
不过我的项目部署上线测试发现，实际上count的效率比count的效率高，
具体原因暂时也没想明白。
2.如果是查询多个结果，用sum的效率是比count的效率高很多的，而且sql语句也比较
1.第一次测试是采用count(*)
public void test33() { Connection conn = new ConnectionManager().newConnection(); PreparedStatement ps = null; String sql = ""; int count = 0; try { Long start = System.currentTimeMillis(); sql = "SELECT COUNT(*) FROM `mmscm`.uuidtest WHERE ACCOUNT_NO=? AND USERID=?" ; ps = conn.prepareStatement(sql); ps.setString(1, "Jim"); ps.setString(2, "demo2"); ResultSet rs = ps.executeQuery(); while (rs.next()) { count = rs.getInt("COUNT(*)"); } System.out.println("count: "+count); Long end = System.currentTimeMillis(); System.out.println("time: "+(end-start)); } catch (Exception e) { System.out.println(e); } }运行结果：count: 9899990
time: 12006
2.第二次是采用count(主键名)
public void test34() { Connection conn = new ConnectionManager().newConnection(); PreparedStatement ps = null; String sql = ""; int count = 0; try { Long start = System.currentTimeMillis(); sql = "SELECT COUNT(ACCOUNT_NO) FROM `mmscm`.uuidtest WHERE ACCOUNT_NO=? AND USERID=?" ; ps = conn.prepareStatement(sql); ps.setString(1, "Jim"); ps.setString(2, "demo2"); ResultSet rs = ps.executeQuery(); while (rs.next()) { count = rs.getInt("COUNT(ACCOUNT_NO)"); } System.out.println("count: "+count); Long end = System.currentTimeMillis(); System.out.println("time: "+(end-start)); } catch (Exception e) { System.out.println(e); } }运行结果：count: 9899990
time: 12177
3.第三次是用sum(列名=条件)查询
public void test35() { Connection conn = new ConnectionManager().newConnection(); PreparedStatement ps = null; String sql = ""; int count = 0; try { Long start = System.currentTimeMillis(); sql = "SELECT IFNULL(SUM(USERID=\'demo2\'),0) AS TOTAL " + "FROM `mmscm`.uuidtest WHERE ACCOUNT_NO=? AND USERID=?" ; ps = conn.prepareStatement(sql); // ps.setString(1, "demo2"); ps.setString(1, "Jim"); ps.setString(2, "demo2"); ResultSet rs = ps.executeQuery(); while (rs.next()) { count = rs.getInt("TOTAL"); } System.out.println("count: "+count); Long end = System.currentTimeMillis(); System.out.println("time: "+(end-start)); } catch (Exception e) { System.out.println(e); } }运行结果：count: 9899990
time: 12745
4.如果需要同时查询几个结果，使用sum的效率会更快！
public void test36() { Connection conn = new ConnectionManager().newConnection(); PreparedStatement ps = null; String sql = ""; int count = 0; int count1 = 0; int count2 = 0; try { Long start = System.currentTimeMillis(); sql = "SELECT IFNULL(SUM(USERID=\'demo2\'),0) AS TOTAL2, IFNULL(SUM(USERID=\'demo1\'),0) AS TOTAL1, " + "IFNULL(SUM(USERID=\'demo\'),0) AS TOTAL " + "FROM `mmscm`.uuidtest WHERE ACCOUNT_NO=?" ; ps = conn.prepareStatement(sql); ps.setString(1, "Jim"); // ps.setString(2, "demo2"); ResultSet rs = ps.executeQuery(); while (rs.next()) { count = rs.getInt("TOTAL"); count1 = rs.getInt("TOTAL1"); count2 = rs.getInt("TOTAL2"); } System.out.println("count: "+count); System.out.println("count1: "+count1); System.out.println("count2: "+count2); Long end = System.currentTimeMillis(); System.out.println("time: "+(end-start)); } catch (Exception e) { System.out.println(e); } }