SQL优化 COUNT()优化 SUM(ID='ID')替代COUNT()

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);
		}
	}