从数据库中随机抽取一条记录的方法

一、背景

        在Spring项目开发的过程中，遇到一个需求：需要从一个存有300w数据的表中随机抽取一系列具有特定字段值的数据；由于数据量大，采用普通的方式暴力搜索会产生巨大性能与时间开销，所以需要编写一个高性能的查询方法以满足需求。

二、设计思路

        由于Spring及Mybatis并未提供高性能随机查询方法，所以代码应改动到Mapper.xml文件，自定义相关方法；其次，无论如何，操作数据库的方法都较重，且项目随机性要求不是非常严格，可以考虑缓存一部分查到的数据以供下次调用，尽可能减少与数据库的交互次数。

        注：本文仅提供思路参考，代码为便于理解改动过，请谨慎使用！

三、具体实现

3.1 Controller层逻辑

        基本的业务逻辑如下：通过GET方法发起请求，随后getRandomContent方法调用服务实现层(原因后续阐述)的某个应用缓存的方法用于获取一个指定大小的内容列表，每次调用时从该列表中随机抽取一条记录返回；

    @ApiOperation(value = "获取随机数据")
    @RequestMapping(value = "/getRandomContent", method = RequestMethod.GET)
    public ApiResponse<?> getRandomContent() {
        // 这里建立一个List负责存储查询到的数据
        List<Content> contentList = contentService.selectContentList(50);
        if (contentList.isEmpty()) {
            return Response.fail("未查询到可用数据");
        } else {
            // 在 0~List大小 的范围内随机生成一个数作为下标
            int random = new Random().nextInt(contentList.size());
            // 取下标为 random 的数据
            Content randomContent = contentList.get(random);
            return Response.success(randomContent);
        }
    }

3.2 ServiceImpl层逻辑

        Service层需要做到的事如下：

1. 初次搜索或缓存过期时：调用Dao层语句查询数据库，并将查询到的列表放入缓存；
2. 若缓存中存有键为"'ValidContentList' + #limit"的数据时，不进入方法体，直接返回缓存内容。

        注：关于注解@Cacheable的学习笔记笔者仍在总结，建议需要的读者先自行查询。

    @Override
    @Cacheable(value = Consts.CACHE_GROUP_ONE, 
               key = "'ValidContentList' + #limit")
    public List<Content> selectValidContentList(Integer limit) {
        // 查询数据库操作
        List<Content> contents = contentMapper.selectContentList(limit);
        // 若程序执行到此处说明缓存未命中或已过期，做出log提示
        log.error("selectValidContentList未命中缓存");
        if (contents.isEmpty()) {
            throw new ResponseException(ResponseEnum.QUERY_EMPTY);
        }
        return contents;
    }

3.3 Mapper层逻辑

        直接在Mapper层操作数据库的灵活度大于其他层，Mabatis提供的数据库操作语句十分全面，可以满足大多数需求；本层需要实现的目标为从数据库指定表中随机抽取数条"status"字段为"VALID"的记录；

    <!-- 查询可用内容集合 -->
    <select id="selectContentList" resultMap="ContentResultMap">
        <!-- 从最小递增值与最大递增值间随机取数作为虚拟表id -->
        SET @minId = (SELECT MIN(id) FROM content WHERE status = #{status});
        SET @maxId = (SELECT MAX(id) FROM content WHERE status = #{status});
        SET @rId = TRUNCATE(@minId + (RAND() * (@maxId - @minId)));
        <!-- 进行连表查询 -->
        SELECT *
        FROM content AS c
        WHERE c.id >= @rId
          AND c.status = 'VALID'
            LIMIT #{limit}
    </select>

SQL代码解析：

  首先通过两次查询分别确定表内自动递增值的上下限；

  其次生成一个虚拟id：TRUNCATE(@minId + (RAND() * (@maxId - @minId)))的含义是随机取(下限~上限)之间的一个浮点数，并截断其小数部分避免溢出；

  最后将真实表的主键与虚拟id关联，取limit条符合条件的数据后返回。

 四、总结

        在数据量较大且并发较高的环境下，频繁查询数据库的操作可能导致数据库崩溃，为了达成相应目标，最好牺牲部分随机性，采用缓存等方式预先储存一部分数据集，之后缓存存活期每次调用可以直接从缓存中取出数据；

        在这里笔者附上一个表格，可以直观展示使用SQL语句优化及ehCache后的系统性能：

不同方式下数据查询时间

查询方式(查询50条数据)	耗时
使用ORDER BY RAND()方法	>13000ms
使用虚拟表连表查询但不使用缓存	平均500ms
使用虚拟表连表查询且使用缓存(缓存存活期)	平均2ms

        以上，希望能够帮助到各位。