Redis数据库测试和缓存穿透、雪崩、击穿

Redis数据库测试实验

实验要求

1.新建一张user表，在表内插入10000条数据。
2.①通过jdbc查询这10000条数据，记录查询时间。
②通过redis查询这10000条数据，记录查询时间。
3.①再次查询这一万条数据，要求根据年龄进行排序，mysql和redis各实现一次。
4.上面排序后的前5人可进行抽奖，每人有一次抽奖机会，抽奖奖品随意设计，抽奖方式通过redis实现。

1.基本准备

先下载好jar包

在根目录下，新建lib文件夹，并将两个jar包移动到lib文件夹中

在IDEA中，右键点击lib，选择“添加为库”

两个jar包显示可展开即为成功。

2.mysql建立用户表user

CREATE TABLE `user` (`id` int primary key AUTO_INCREMENT,`name` varchar(10) COMMENT '姓名',`age` int COMMENT '年龄'
) ;

3.为mysql和redis添加数据

（1）获取数据库连接，并为mysql添加数据

    //获取数据库连接public Connection getConnection() {System.out.println("获取数据库连接");String url = "jdbc:mysql://localhost:3306/homework";String username = "root";String password = "123456";Connection conn = null;try {conn = DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();}return conn;}

    //mysql添加数据public void addMysql() {System.out.println("mysql添加数据");Connection conn = null;PreparedStatement ps = null;conn = getConnection();try {Random random = new Random();for (int i = 0; i < 10000; i++) {String name = "Name" + i;int age = random.nextInt(100) + 1;ps = conn.prepareStatement("INSERT INTO user (name,age) VALUES (?,?)");ps.setString(1, name);ps.setInt(2, age);ps.executeUpdate();}} catch (SQLException e) {e.printStackTrace();} finally {try {ps.close();conn.close();} catch (SQLException e) {throw new RuntimeException(e);}}}

（2）将Mysql数据转储到redis中

    // 将Mysql数据库数据转储到Redispublic void addRedis() {System.out.println("redis添加数据");Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;conn = getConnection();try {ps = conn.prepareStatement("select * from user");rs = ps.executeQuery();Jedis jedis = new Jedis("localhost", 6379);while (rs.next()) {String id = String.valueOf(rs.getInt("id"));String name = rs.getString("name");int age = rs.getInt("age");// 使用有序集合存储学生ID和年龄，以便进行排序jedis.zadd("UserByAge", age, id);// 存储学生数据jedis.hset("user:" + id, "name", name);jedis.hset("user:" + id, "age", String.valueOf(age));}jedis.close();} catch (SQLException e) {e.printStackTrace();} finally {try {rs.close();ps.close();conn.close();} catch (SQLException e) {throw new RuntimeException(e);}}}

4.实现mysql和redis查询，并比较查询时间

（1）mysql查询

    //mysql查询public void queryDataWithJDBC() {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;conn = getConnection();try {ps = conn.prepareStatement("select * from user");rs = ps.executeQuery();while (rs.next()) {
//                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Age: " + rs.getInt("age"));}} catch (SQLException e) {e.printStackTrace();} finally {try {rs.close();ps.close();conn.close();} catch (SQLException e) {throw new RuntimeException(e);}}}

（2）redis查询

 //redis查询public void queryDataWithRedis() {Jedis jedis = new Jedis("localhost", 6379);Set<String> keys = jedis.keys("user:*");for (String key : keys) {Map<String, String> user = jedis.hgetAll(key);
//            System.out.println("Key: " + key + ", Value: " + user);}jedis.close();}

（3）记录并比较查询时间

// 比较查询时间public void compareTime() {// 通过jdbc查询这10000条数据，记录查询时间long start = System.currentTimeMillis();queryDataWithJDBC();long end = System.currentTimeMillis();System.out.println("JDBC查询时间: " + (end - start) + "ms");// 通过redis查询这10000条数据，记录查询时间start = System.currentTimeMillis();queryDataWithRedis();end = System.currentTimeMillis();System.out.println("Redis查询时间: " + (end - start) + "ms");}

5.根据年龄进行排序

（1）mysql排序

    //mysql实现排序public void queryAndSortDataWithJDBC() {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;conn = getConnection();try {ps = conn.prepareStatement("SELECT * FROM user ORDER BY age");rs = ps.executeQuery();System.out.println("mysql实现排序：");while (rs.next()) {System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Age: " + rs.getInt("age"));}} catch (SQLException e) {e.printStackTrace();} finally {try {rs.close();ps.close();conn.close();} catch (SQLException e) {throw new RuntimeException(e);}}}

（2）redis排序

    //redis实现排序public void queryAndSortDataWithRedis() {Jedis jedis = new Jedis("localhost", 6379);List<Tuple> users = jedis.zrangeWithScores("UserByAge", 0, -1);System.out.println("redis实现排序：");for (Tuple user : users) {String id = user.getElement();double age = user.getScore();String name = jedis.hget("user:" + id, "name");System.out.println("ID: " + id + ", Name: " + name + ", Age: " + (int) age);}jedis.close();}

6.抽奖功能

    //抽奖public void lottery() {Jedis jedis = new Jedis("localhost", 6379);// 添加奖品String[] prizes = {"锅", "碗", "瓢", "盆", "金元宝"};for (String prize : prizes) {jedis.sadd("prizes", prize);}// 年龄最小的前5人System.out.println("年龄最小的前5人：");List<Tuple> youngestUsers = jedis.zrangeWithScores("UserByAge", 0, 4);for (Tuple user : youngestUsers) {String id = user.getElement();double age = user.getScore();String name = jedis.hget("user:" + id, "name");String prize = jedis.srandmember("prizes");System.out.println("恭喜 " + name + " 获得了抽奖机会！奖品是：" + prize);}jedis.close();}

7.主函数

    public static void main(String[] args) throws SQLException {JedisHomework jedisHomework = new JedisHomework();jedisHomework.addMysql();jedisHomework.addRedis();jedisHomework.compareTime();jedisHomework.queryAndSortDataWithJDBC();jedisHomework.queryAndSortDataWithRedis();jedisHomework.lottery();}