Mysql 内存表的使用

使用场景

在系统中，用户查询订单数据之后，例如这边搜索出来的是：A,B,C

选择导出搜索订单，如果导出的是以条件去搜索，结果可能是 A,B,C,D 。是没有办法保证数据是一致的

当然，加上导出任务是异步执行的，所以这个误差也是在允许范围的。

使用

考虑到需求是保持查询和导出的数值是一样的，假设

情况1：查询订单后，把查询到的订单ID缓存在数据库中(设置到期时间5分钟)，生成查询缓存ID，然后当用户点击导出时，查询最近的缓存是否存在，存在则保存刚才保存到的缓存ID到导出条件里。(理想情况)

情况2：查询订单后，超出5分钟时间后，在未进行下次查询的时候直接导出，则按照条件导出。(误差范围内)

情况3：查询订单后，再新开一个窗口继续查询订单，后续的缓存会覆盖第一个页面的缓存，然后在第一个页面进行导出，则按照第二个页面的条件导出。(误差范围比较大)

执行导出条件的时候，遇到缓存主键就把原本的条件都舍弃掉，把缓存表的数值写入到内存中，再关联内存表查询导出。

注意事项

1.缓存表可以利用redis来实现，设置过期时间自动过期，或者保存在mysql，利用定时任务清理一段时间之前的数据

2.缓存表中是需要设置过期时间的，实现中建议是5分钟

3.因为公司网络可能会出现同一个账户，不同的人操作，需要 tab(类型,如待发货，已发货等情况) , user, user-agent, ip 这些组合在一起，md5或者sha1生成 key，查询匹配的缓存ID的时候也是根据这个条件去找缓存，找到了就用，没找到就使用原本的导出条件

4.一个内存表在查询中不能多次使用

5.测试和生产环境需要有创建内存表的权限

6.当生产环境是读写分离的时候要注意，创建 和 写入 是走的主库，查询有概率到从库，从库是不存在内存表的，所以要强制走到主库 /*FORCE_MASTER*/

代码

// 使用的是 gorm 1.22版本

// ... 省略业务逻辑代码

// 创建临时表
// 因为实际操作中协程还是会触发内存表重复,所以这里用了随机数值来产生表名称
if err := db.Exec("CREATE TEMPORARY TABLE `" + tempTableName1 + "`(order_id INT);").Error; err != nil {
    logger.Infof("cache_export_debug.err:create table1", err)
    return nil, 0, err
}
// 一个内存表在实际使用存在多次join会提示表找不到，所以这里使用了2个内存表(因为生产中会使用2次内存表)
// mysql 8.0之后支持 with的写法 因为生产是5.7，所以也没有尝试
if err := db.Exec("CREATE TEMPORARY TABLE `" + tempTableName2 + "`(order_id INT);").Error; err != nil {
    logger.Infof("cache_export_debug.err:create table2", err)
    return nil, 0, err
}
// 写入数据
for _, v := range orderCacheList {
    if err := db.Exec("INSERT INTO `"+tempTableName1+"` (order_id) VALUES (?);", v).Error; err != nil {
        logger.Infof("cache_export_debug.err:insert table1", err)
        return nil, 0, err
    }
}
if err := db.Exec("INSERT INTO `" + tempTableName2 + "` SELECT * FROM `" + tempTableName1 + "`;").Error; err != nil {
    logger.Infof("cache_export_debug.err:insert table2", err)
    return nil, 0, err
}

// 查询参数
searchSql := " /*FORCE_MASTER*/ " +
    "  SELECT o.order_id FROM " + OrderTodayTableName + " o JOIN " + tempTableName1 + " as t1 on o.order_id = t1.order_id AND o.order_id > ? " +
    "  UNION " +
    "  SELECT o1.order_id FROM " + OrderTableName + " o1 JOIN " + tempTableName2 + " as t2 on o1.order_id = t2.order_id AND o1.order_id > ? " +
    "ORDER BY order_id DESC LIMIT ?"

if err := db.Raw(searchSql, minOrderId, minOrderId, pageSize).Pluck("order_id", &orderIdsRet).Error; err != nil {
    logger.Infof("cache_export_debug.err:exec sql1", err)
    return nil, 0, err
}
// 删除内存表
if err := db.Exec("DROP TABLE `" + tempTableName1 + "`;").Error; err != nil {
    logger.Infof("cache_export_debug.err:drop table1", err)
    return nil, 0, err
}
if err := db.Exec("DROP TABLE `" + tempTableName2 + "`;").Error; err != nil {
    logger.Infof("cache_export_debug.err:drop table2", err)
    return nil, 0, err
}

参考文档

1.Mysql 内存表2.RDS读写分离时效性3.Gorm Session