JDBC查询缓存

原文地址：http://blog.csdn.net/shajunxing/archive/2010/01/06/5144950.aspx

前段时间因为实际需要，写了一个简单的JDBC查询缓存，发表在这里：

http://topic.csdn.net/u/20091209/18/e366812c-5cc6-47b2-83d6-f78350206781.html?1092065064


经过一段时间的使用，发现很严重的问题：缓存的CachedRowSet因为是通过游标访问记录的，所以如果多线程同时操作一个CachedRowSet，游标就会冲突，为了解决这个问题，我在上面补了很多代码，非常非常难看！


今天重拾旧题，也多亏了“火龙果@菜菜宝宝”的点拨，使用了Apache Commons DbUtil的List格式的结果集代替了CachedRowSet，这样游标冲突就避免了（多线程肯定是拥有独立的迭代器的，而不像游标只有一个），然 后再使用Apache Commons Collections中的LRUMap以Collections.synchronizedMap()方法包装成线程安全的Map作为缓存，懒得自己写 线程安全的代码了，呵呵。最后，连接池使用的是C3P0。


废话少说，上代码：

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.atomic.AtomicLong;
import java.util.logging.Logger;
import org.apache.commons.collections.map.LRUMap;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

/**
 * JDBC查询缓存
 * @author shajunxing
 */
public class QueryCache {

    private static final Logger logger = Logger.getLogger(QueryCache.class.getName());
    private Map<String, List<Map<String, Object>>> cache = null;
    private String driver;
    private String url;
    private String user;
    private String password;
    private ComboPooledDataSource cpds;
    private AtomicLong visitedCount = new AtomicLong(0);
    private AtomicLong hitCount = new AtomicLong(0);

    /**
     * 默认构造函数
     * @param driver JDBC驱动类
     * @param url 连接字符串
     * @param user 用户名
     * @param password 口令
     * @param maxSize 查询缓存大小
     */
    public QueryCache(String driver, String url, String user, String password, int maxSize) {
        this.driver = driver;
        this.url = url;
        this.user = user;
        this.password = password;
        cache = Collections.synchronizedMap(new LRUMap(maxSize));
        cpds = new ComboPooledDataSource();
        cpds.setJdbcUrl(url);
        cpds.setUser(user);
        cpds.setPassword(password);
        try {
            Class.forName(driver);
            cpds.setDriverClass(driver);
        } catch (ClassNotFoundException ex) {
            logger.severe(ex.toString());
        } catch (PropertyVetoException ex) {
            logger.severe(ex.toString());
        }
    }

    public void clear() {
        cache.clear();
    }

    /**
     * 非缓存查询
     * @param sql SQL语句
     * @param pooled 是否使用连接池
     * @return 查询结果
     */
    public List<Map<String, Object>> query(String sql, boolean pooled) {
        Connection conn = null;
        try {
            if (pooled) {
                conn = cpds.getConnection();
            } else {
                conn = DriverManager.getConnection(url, user, password);
            }
            return new QueryRunner().query(conn, sql, new MapListHandler());
        } catch (SQLException ex) {
            logger.severe(ex.toString());
            return null;
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    /**
     * 缓存查询
     * @param sql SQL语句
     * @param pooled 是否使用连接池
     * @return 查询结果
     */
    public List<Map<String, Object>> cachedQuery(String sql, boolean pooled) {
        visitedCount.incrementAndGet();
        if (cache.containsKey(sql)) {
            hitCount.incrementAndGet();
            return cache.get(sql);
        } else {
            List<Map<String, Object>> result = query(sql, pooled);
            cache.put(sql, result);
            return result;
        }
    }

    public long getVisitedCount() {
        return visitedCount.get();
    }

    public long getHitCount() {
        return hitCount.get();
    }

    public int getSize() {
        return cache.size();
    }

    public Set<String> getKeySet() {
        return cache.keySet();
    }
}


进行了两个测试：


其一测试了下面7种情形：

A单线程，无连接池、无查询缓存

B单线程，有连接池、无查询缓存

C单线程，无连接池、有查询缓存

D单线程，有连接池、有查询缓存

E多线程，有连接池、无查询缓存

F多线程，无连接池、有查询缓存

G多线程，有连接池、有查询缓存

注：多线程，无连接池、无查询缓存的情况因为数据库最大连接数的限制，不做测试。

import java.util.logging.Logger;

/**
 * 性能测试1
 * @author shajunxing
 */
public class PerformanceTest {

    private static final Logger logger = Logger.getLogger(PerformanceTest.class.getName());
    private static final String driver = "XXX";
    private static final String url = "XXX";
    private static final String user = "XXX";
    private static final String password = "XXX";
    private static final String sql = "XXX";
    private static final int loopCount = 1000;
    private static QueryCache cache = new QueryCache(driver, url, user, password, 1000000);

    private static void testQuery() {
        for (int i = 0; i < loopCount; i++) {
            cache.query(sql, false);
        }
    }

    private static void testPooledQuery() {
        for (int i = 0; i < loopCount; i++) {
            cache.query(sql, true);
        }
    }

    private static void testCachedQuery() {
        for (int i = 0; i < loopCount; i++) {
            cache.cachedQuery(sql, false);
        }
    }

    private static void testCachedPooledQuery() {
        for (int i = 0; i < loopCount; i++) {
            cache.cachedQuery(sql, true);
        }
    }

    private static void testPooledQueryMS() {
        Thread[] threads = new Thread[loopCount];
        for (int i = 0; i < loopCount; i++) {
            Thread thread = new Thread(new Runnable() {

                public void run() {
                    cache.query(sql, true);
                }
            });
            thread.start();
            threads[i] = thread;
        }
        for (Thread thread : threads) {
            try {
                thread.join();
            } catch (InterruptedException ex) {
                logger.severe(ex.toString());
            }
        }
    }

    private static void testCachedQueryMS() {
        Thread[] threads = new Thread[loopCount];
        for (int i = 0; i < loopCount; i++) {
            Thread thread = new Thread(new Runnable() {

                public void run() {
                    cache.cachedQuery(sql, false);
                }
            });
            thread.start();
            threads[i] = thread;
        }
        for (Thread thread : threads) {
            try {
                thread.join();
            } catch (InterruptedException ex) {
                logger.severe(ex.toString());
            }
        }
    }

    private static void testCachedPooledQueryMS() {
        Thread[] threads = new Thread[loopCount];
        for (int i = 0; i < loopCount; i++) {
            Thread thread = new Thread(new Runnable() {

                public void run() {
                    cache.cachedQuery(sql, true);
                }
            });
            thread.start();
            threads[i] = thread;
        }
        for (Thread thread : threads) {
            try {
                thread.join();
            } catch (InterruptedException ex) {
                logger.severe(ex.toString());
            }
        }
    }

    public static void main(String[] args) {
        testQuery();
        testPooledQuery();
        testCachedQuery();
        testCachedPooledQuery();
        testPooledQueryMS();
        testCachedQueryMS();
        testCachedPooledQueryMS();
    }
}


使用NetBeans Profiler做三次测试，结果如下：

从中可以得出结论：

单线程的四种方式时间比大约为A : B : C : D = 15000 : 2000 : 15 : 1；
多线程三种方式耗时差不多，估计在线程的创建以及调度上花费了很多时间。

其二测试了缓存在模拟的实际情况下面的增长以及命中率等数值。

方法是首先通过JDBC元数据操作获取数据库中的所有表名和各个表的字段名，然后将字段名和表名随机组合构造SQL语句，测试10000次（单线程，在我们的项目中多线程同时访问的情况很少），每100次统计一下缓存的命中率等信息，代码如下：

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.logging.Logger;
import org.apache.commons.dbutils.DbUtils;

/**
 * 性能测试2
 * @author shajunxing
 */
public class PerformanceTest2 {

    private static final Logger logger = Logger.getLogger(PerformanceTest2.class.getName());
    private static final String driver = "XXX";
    private static final String url = "XXX";
    private static final String user = "XXX";
    private static final String password = "XXX";
    private static QueryCache cache = new QueryCache(driver, url, user, password, 1000000);
    private static Map<String, List<String>> tables = new HashMap<String, List<String>>();
    private static List<String> tableNames = new LinkedList<String>();
    private static Random rand = new Random(Calendar.getInstance().getTimeInMillis());

    private static void getTables() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        DbUtils.loadDriver(driver);

        try {
            conn = DriverManager.getConnection(url, user, password);
            // 获取所有表名称
            logger.info("获取所有表名称");
            try {
                DatabaseMetaData dbMeta = conn.getMetaData();
                rs = dbMeta.getTables(null, null, null, new String[]{"TABLE"});
                if (rs != null) {
                    while (rs.next()) {
                        tables.put(rs.getString("TABLE_NAME"), new LinkedList<String>());
                    }
                }
            } catch (SQLException ex) {
                logger.severe(ex.toString());
            } finally {
                DbUtils.closeQuietly(rs);
            }

            // 获取所有表的字段名称
            Set<String> illegalTables = new HashSet<String>();
            for (String tableName : tables.keySet()) {
                logger.info(String.format("获取表%s的字段", tableName));
                List<String> tableColumns = tables.get(tableName);
                try {
                    stmt = conn.createStatement();
                    rs = stmt.executeQuery(String.format("select * from %s where rownum=1", tableName));
                    ResultSetMetaData rsMeta = rs.getMetaData();
                    for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
                        String columnName = rsMeta.getColumnName(i);
                        tableColumns.add(columnName);
                    }
                } catch (SQLException ex) {
                    logger.severe(String.format("获取表%s的字段失败：%s", tableName, ex.toString()));
                    illegalTables.add(tableName);
                } finally {
                    DbUtils.closeQuietly(rs);
                    DbUtils.closeQuietly(stmt);
                }
            }

            // 删除非法的表
            for (String illegal : illegalTables) {
                tables.remove(illegal);
            }

            // 表名列表
            for (String tableName : tables.keySet()) {
                tableNames.add(tableName);
            }

        } catch (SQLException ex) {
            logger.severe(ex.toString());
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    public static void main(String[] args) {
        // 获取所有的表和字段名
        getTables();

        // 打印所有的表和字段名
        for (String tableName : tables.keySet()) {
            System.out.println(tableName);
            for (String columnName : tables.get(tableName)) {
                System.out.println("    " + columnName);
            }
            System.out.println();
        }

        // 用随机组合的SQL语句测试
        long lastVisitedCount = 0;
        long lastHitCount = 0;
        long lastTime = Calendar.getInstance().getTimeInMillis();
        for (int i = 0; i < 100; i++) {
            // 每100次循环统计一次
            for (int j = 0; j < 100; j++) {
                String tableName = tableNames.get(rand.nextInt(tableNames.size()));
                List<String> columnNames = tables.get(tableName);
                String columnName = columnNames.get(rand.nextInt(columnNames.size()));
                String sql = String.format("select \"%s\" from \"%s\"", columnName, tableName);
                cache.cachedQuery(sql, true);
            }
            long visitedCount = cache.getVisitedCount();
            long hitCount = cache.getHitCount();
            long time = Calendar.getInstance().getTimeInMillis();
            System.out.println(String.format("缓存总访问数：%d，总命中数：%d，当前大小：%d，命中率：%.2f%%，耗时：%d毫秒",
                    visitedCount,
                    hitCount,
                    cache.getSize(),
                    100.0 * (hitCount - lastHitCount) / (visitedCount - lastVisitedCount),
                    time - lastTime));
            lastVisitedCount = visitedCount;
            lastHitCount = hitCount;
            lastTime = time;
        }

        // 按任意键继续
        System.out.println("按任意键继续...");
        try {
            new BufferedReader(new InputStreamReader(System.in)).readLine();
        } catch (IOException ex) {
            logger.severe(ex.toString());
        }

        // 打印缓存中的键
        for (String key : cache.getKeySet()) {
            System.out.println(key);
        }
    }
}

结果是：

缓存总访问数：100，总命中数：2，当前大小：98，命中率：2.00%，耗时：4157毫秒
缓存总访问数：200，总命中数：16，当前大小：184，命中率：14.00%，耗时：2062毫秒
缓存总访问数：300，总命中数：40，当前大小：260，命中率：24.00%，耗时：1860毫秒
缓存总访问数：400，总命中数：74，当前大小：326，命中率：34.00%，耗时：578毫秒
缓存总访问数：500，总命中数：111，当前大小：389，命中率：37.00%，耗时：1281毫秒
缓存总访问数：600，总命中数：154，当前大小：446，命中率：43.00%，耗时：3391毫秒
缓存总访问数：700，总命中数：188，当前大小：512，命中率：34.00%，耗时：953毫秒
缓存总访问数：800，总命中数：237，当前大小：563，命中率：49.00%，耗时：1515毫秒
缓存总访问数：900，总命中数：283，当前大小：617，命中率：46.00%，耗时：2094毫秒
缓存总访问数：1000，总命中数：350，当前大小：650，命中率：67.00%，耗时：297毫秒
缓存总访问数：1100，总命中数：415，当前大小：685，命中率：65.00%，耗时：531毫秒
缓存总访问数：1200，总命中数：477，当前大小：723，命中率：62.00%，耗时：953毫秒
缓存总访问数：1300，总命中数：538，当前大小：762，命中率：61.00%，耗时：328毫秒
缓存总访问数：1400，总命中数：604，当前大小：796，命中率：66.00%，耗时：1172毫秒
缓存总访问数：1500，总命中数：667，当前大小：833，命中率：63.00%，耗时：2000毫秒
缓存总访问数：1600，总命中数：739，当前大小：861，命中率：72.00%，耗时：2000毫秒
缓存总访问数：1700，总命中数：813，当前大小：887，命中率：74.00%，耗时：188毫秒
缓存总访问数：1800，总命中数：893，当前大小：907，命中率：80.00%，耗时：1265毫秒
缓存总访问数：1900，总命中数：973，当前大小：927，命中率：80.00%，耗时：516毫秒
缓存总访问数：2000，总命中数：1055，当前大小：945，命中率：82.00%，耗时：422毫秒
缓存总访问数：2100，总命中数：1134，当前大小：966，命中率：79.00%，耗时：516毫秒
缓存总访问数：2200，总命中数：1212，当前大小：988，命中率：78.00%，耗时：1015毫秒
缓存总访问数：2300，总命中数：1287，当前大小：1013，命中率：75.00%，耗时：438毫秒
缓存总访问数：2400，总命中数：1371，当前大小：1029，命中率：84.00%，耗时：937毫秒
缓存总访问数：2500，总命中数：1457，当前大小：1043，命中率：86.00%，耗时：750毫秒
缓存总访问数：2600，总命中数：1540，当前大小：1060，命中率：83.00%，耗时：235毫秒
缓存总访问数：2700，总命中数：1627，当前大小：1073，命中率：87.00%，耗时：140毫秒
缓存总访问数：2800，总命中数：1719，当前大小：1081，命中率：92.00%，耗时：1235毫秒
缓存总访问数：2900，总命中数：1806，当前大小：1094，命中率：87.00%，耗时：125毫秒
缓存总访问数：3000，总命中数：1889，当前大小：1111，命中率：83.00%，耗时：312毫秒
缓存总访问数：3100，总命中数：1981，当前大小：1119，命中率：92.00%，耗时：63毫秒
缓存总访问数：3200，总命中数：2071，当前大小：1129，命中率：90.00%，耗时：125毫秒
缓存总访问数：3300，总命中数：2161，当前大小：1139，命中率：90.00%，耗时：62毫秒
缓存总访问数：3400，总命中数：2254，当前大小：1146，命中率：93.00%，耗时：172毫秒
缓存总访问数：3500，总命中数：2344，当前大小：1156，命中率：90.00%，耗时：156毫秒
缓存总访问数：3600，总命中数：2436，当前大小：1164，命中率：92.00%，耗时：141毫秒
缓存总访问数：3700，总命中数：2529，当前大小：1171，命中率：93.00%，耗时：125毫秒
缓存总访问数：3800，总命中数：2624，当前大小：1176，命中率：95.00%，耗时：78毫秒
缓存总访问数：3900，总命中数：2717，当前大小：1183，命中率：93.00%，耗时：16毫秒
缓存总访问数：4000，总命中数：2811，当前大小：1189，命中率：94.00%，耗时：93毫秒
缓存总访问数：4100，总命中数：2906，当前大小：1194，命中率：95.00%，耗时：16毫秒
缓存总访问数：4200，总命中数：3003，当前大小：1197，命中率：97.00%，耗时：47毫秒
缓存总访问数：4300，总命中数：3095，当前大小：1205，命中率：92.00%，耗时：47毫秒
缓存总访问数：4400，总命中数：3190，当前大小：1210，命中率：95.00%，耗时：47毫秒
缓存总访问数：4500，总命中数：3285，当前大小：1215，命中率：95.00%，耗时：15毫秒
缓存总访问数：4600，总命中数：3372，当前大小：1228，命中率：87.00%，耗时：500毫秒
缓存总访问数：4700，总命中数：3471，当前大小：1229，命中率：99.00%，耗时：16毫秒
缓存总访问数：4800，总命中数：3563，当前大小：1237，命中率：92.00%，耗时：15毫秒
缓存总访问数：4900，总命中数：3658，当前大小：1242，命中率：95.00%，耗时：0毫秒
缓存总访问数：5000，总命中数：3751，当前大小：1249，命中率：93.00%，耗时：32毫秒
缓存总访问数：5100，总命中数：3846，当前大小：1254，命中率：95.00%，耗时：15毫秒
缓存总访问数：5200，总命中数：3937，当前大小：1263，命中率：91.00%，耗时：219毫秒
缓存总访问数：5300，总命中数：4034，当前大小：1266，命中率：97.00%，耗时：0毫秒
缓存总访问数：5400，总命中数：4131，当前大小：1269，命中率：97.00%，耗时：16毫秒
缓存总访问数：5500，总命中数：4230，当前大小：1270，命中率：99.00%，耗时：0毫秒
缓存总访问数：5600，总命中数：4327，当前大小：1273，命中率：97.00%，耗时：0毫秒
缓存总访问数：5700，总命中数：4423，当前大小：1277，命中率：96.00%，耗时：297毫秒
缓存总访问数：5800，总命中数：4520，当前大小：1280，命中率：97.00%，耗时：703毫秒
缓存总访问数：5900，总命中数：4618，当前大小：1282，命中率：98.00%，耗时：0毫秒
缓存总访问数：6000，总命中数：4715，当前大小：1285，命中率：97.00%，耗时：47毫秒
缓存总访问数：6100，总命中数：4811，当前大小：1289，命中率：96.00%，耗时：125毫秒
缓存总访问数：6200，总命中数：4907，当前大小：1293，命中率：96.00%，耗时：31毫秒
缓存总访问数：6300，总命中数：5005，当前大小：1295，命中率：98.00%，耗时：62毫秒
缓存总访问数：6400，总命中数：5103，当前大小：1297，命中率：98.00%，耗时：16毫秒
缓存总访问数：6500，总命中数：5201，当前大小：1299，命中率：98.00%，耗时：16毫秒
缓存总访问数：6600，总命中数：5299，当前大小：1301，命中率：98.00%，耗时：0毫秒
缓存总访问数：6700，总命中数：5397，当前大小：1303，命中率：98.00%，耗时：31毫秒
缓存总访问数：6800，总命中数：5496，当前大小：1304，命中率：99.00%，耗时：15毫秒
缓存总访问数：6900，总命中数：5593，当前大小：1307，命中率：97.00%，耗时：0毫秒
缓存总访问数：7000，总命中数：5691，当前大小：1309，命中率：98.00%，耗时：0毫秒
缓存总访问数：7100，总命中数：5791，当前大小：1309，命中率：100.00%，耗时：0毫秒
缓存总访问数：7200，总命中数：5889，当前大小：1311，命中率：98.00%，耗时：16毫秒
缓存总访问数：7300，总命中数：5988，当前大小：1312，命中率：99.00%，耗时：16毫秒
缓存总访问数：7400，总命中数：6088，当前大小：1312，命中率：100.00%，耗时：0毫秒
缓存总访问数：7500，总命中数：6185，当前大小：1315，命中率：97.00%，耗时：0毫秒
缓存总访问数：7600，总命中数：6281，当前大小：1319，命中率：96.00%，耗时：62毫秒
缓存总访问数：7700，总命中数：6381，当前大小：1319，命中率：100.00%，耗时：0毫秒
缓存总访问数：7800，总命中数：6480，当前大小：1320，命中率：99.00%，耗时：31毫秒
缓存总访问数：7900，总命中数：6580，当前大小：1320，命中率：100.00%，耗时：0毫秒
缓存总访问数：8000，总命中数：6679，当前大小：1321，命中率：99.00%，耗时：0毫秒
缓存总访问数：8100，总命中数：6777，当前大小：1323，命中率：98.00%，耗时：0毫秒
缓存总访问数：8200，总命中数：6877，当前大小：1323，命中率：100.00%，耗时：0毫秒
缓存总访问数：8300，总命中数：6975，当前大小：1325，命中率：98.00%，耗时：16毫秒
缓存总访问数：8400，总命中数：7074，当前大小：1326，命中率：99.00%，耗时：0毫秒
缓存总访问数：8500，总命中数：7172，当前大小：1328，命中率：98.00%，耗时：0毫秒
缓存总访问数：8600，总命中数：7270，当前大小：1330，命中率：98.00%，耗时：16毫秒
缓存总访问数：8700，总命中数：7368，当前大小：1332，命中率：98.00%，耗时：0毫秒
缓存总访问数：8800，总命中数：7465，当前大小：1335，命中率：97.00%，耗时：0毫秒
缓存总访问数：8900，总命中数：7563，当前大小：1337，命中率：98.00%，耗时：15毫秒
缓存总访问数：9000，总命中数：7663，当前大小：1337，命中率：100.00%，耗时：0毫秒
缓存总访问数：9100，总命中数：7763，当前大小：1337，命中率：100.00%，耗时：0毫秒
缓存总访问数：9200，总命中数：7862，当前大小：1338，命中率：99.00%，耗时：0毫秒
缓存总访问数：9300，总命中数：7960，当前大小：1340，命中率：98.00%，耗时：0毫秒
缓存总访问数：9400，总命中数：8060，当前大小：1340，命中率：100.00%，耗时：0毫秒
缓存总访问数：9500，总命中数：8158，当前大小：1342，命中率：98.00%，耗时：16毫秒
缓存总访问数：9600，总命中数：8258，当前大小：1342，命中率：100.00%，耗时：0毫秒
缓存总访问数：9700，总命中数：8356，当前大小：1344，命中率：98.00%，耗时：0毫秒
缓存总访问数：9800，总命中数：8455，当前大小：1345，命中率：99.00%，耗时：0毫秒
缓存总访问数：9900，总命中数：8554，当前大小：1346，命中率：99.00%，耗时：0毫秒
缓存总访问数：10000，总命中数：8654，当前大小：1346，命中率：100.00%，耗时：0毫秒

从中大致可以看出缓存的增长趋势以及性能不断改进的趋势。

最后的内存占用率如下图所示：

当然了，这也是不得已而为之的技术，呵呵，如果项目中能用到Hibernate等高级技术的话（Hibernate内置查询缓存了，甚至很多数据库也内置了），还是尽量高级的吧。

总结这个查询缓存应用的场合：

1、SQL语句的可能性不能无限多（例如如果语句中包含可变的时间日期就不行）；
2、读操作远远多于写操作。

例如，在权限管理（登陆注销、操作鉴权...）中，还是很合适的。