# 实际开发中临时表的使用

实际开发中临时表的使用

sql 效率优化方案

描述见：https://blog.csdn.net/qq_37248504/article/details/126259099

背景描述

• 再sql查询的过程中，如果遇到一些 in 的子查询语句 sql性能不高

• sql 超过数据库的容量报错

• sql 不能联查只能通过 in拼接方式查询

• 针对上面的情况可以采取使用临时表。

• 当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。

临时表介绍

• 临时表与实体表类似，只是在使用过程中，临时表是存储在系统数据库tempdb中。当我们不再使用临时表的时候，临时表会自动删除。
• 临时表是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作，在VFP退出时自动被释放。

Mysql 临时表

• Mysql临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间，也可以自当删除临时表
• Mysql临时表支持索引、主键等

建表语法

-- 临时表 tempuser
-- 新建
create temporary  TABLE  IF NOT EXISTS `tempuser` (
`id` varchar(36) ,
`username` varchar(100) ,
`password` varchar(100),
PRIMARY KEY (`id`),
KEY `username_index` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 删除
drop table if exists tempuser

Oracle临时表

事务级临时表

• 事务中，数据可以查询，事务结束后（commit、rollback）后，数据被清空。

建表语法

-- 事务级临时表
CREATE  GLOBAL TEMPORARY TABLE "TEMPUSER"
(        "ID" VARCHAR2(36 CHAR),
    "REQUESTID" NVARCHAR2(36),
    "CODE" NVARCHAR2(30),
    "COLUMNNAME" VARCHAR2(50 CHAR),
    "DATATYPE" VARCHAR2(30 CHAR)
) ON COMMIT DELETE ROWS;

会话级临时表

• commit 时，保存数据至表中，会话结束后，数据被清空

建表语法

-- 会话级临时表
CREATE  GLOBAL TEMPORARY TABLE "TEMPUSER"
(        "ID" VARCHAR2(36 CHAR),
    "REQUESTID" NVARCHAR2(36),
    "CODE" NVARCHAR2(30),
    "COLUMNNAME" VARCHAR2(50 CHAR),
    "DATATYPE" VARCHAR2(30 CHAR)
) ON COMMIT PRESERVE ROWS;

SpringBoot+Mybatis 使用临时表

Mysql 数据库

• MysqlMapper.java

@Mapper
public interface MysqlMapper {

    /**
     * 执行 sql
     *
     * @param sql sql
     */
    @UpdateProvider(value = MysqlProvider.class, method = "executeSql")
    void executeSql(String sql);

    @Insert("<script>INSERT INTO tempuser\n" +
            "        (id,username,password)\n" +
            "        values\n" +
            "        <foreach collection=\"list\" item=\"item\" separator=\",\">\n" +
            "            (#{item.id}, #{item.username}, #{item.password})\n" +
            "        </foreach></script>")
    void insertBatch(List<User> list);

    @Select("${sql}")
    List<Map<String,Object>> listSql(String sql);

    /**
     * Mybatis Provider
     */
    class MysqlProvider {
        public String executeSql(String sql) {
            return sql;
        }
    }
}

• MysqlServiceImpl.java

@Service
public class MysqlServiceImpl implements MysqlService {

    private static final Logger logger = LoggerFactory.getLogger(MysqlServiceImpl.class);

    @Autowired
    private MysqlMapper mysqlMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private CommonMapper commonMapper;

    /**
     * Mysql 临时表使用测试
     */
    @Override
    public void tempTableInsert() {
        // 创建临时表
        String createSql = "create temporary  TABLE  IF NOT EXISTS `tempuser` (\n" +
                "`id` varchar(36) ,\n" +
                "`username` varchar(100) ,\n" +
                "`password` varchar(100)\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
        mysqlMapper.executeSql(createSql);
        // 往临时表写数据
        List<User> userList = getUserList();
        List<List<User>> lists = ListUtils.splitList(userList, BaseConst.TEN_THOUSAND);
        for (List<User> list : lists) {
            mysqlMapper.insertBatch(list);
        }
        List<Map<String, Object>> list1 = mysqlMapper.listSql("select * from tempuser");
        // 使用临时表查询数据
        List<Map<String, Object>> list2 = mysqlMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
        List<Map<String, Object>> list3 = commonMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
        queryTest1();
        // 删除临时表
        String dropSql = "drop table if exists tempuser;";
        mysqlMapper.executeSql(dropSql);

    }

    private void queryTest1() {
        List<Map<String, Object>> list1 = mysqlMapper.listSql("select * from tempuser");
        // 使用临时表查询数据
        List<Map<String, Object>> list2 = mysqlMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
        List<Map<String, Object>> list3 = commonMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
        logger.info("queryTest1");
    }

    /**
     * 获取 10 万用户
     *
     * @return List
     */
    private List<User> getUserList() {
        List<User> userList = new ArrayList<User>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setId(i);
            user.setUsername("user" + i);
            user.setPassword("user" + i);
            userList.add(user);
        }
        return userList;
    }
}

Oracle

• 如果创建了事务级别的临时表，需要手动控制事务

手动控制事务方式

@Service
public class OracleServiceImpl implements OracleService {

    private static final Logger logger = LoggerFactory.getLogger(OracleServiceImpl.class);

    @Autowired
    private OracleMapper oracleMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private CommonMapper commonMapper;

    @Autowired
    private DataSourceTransactionManager dataSourceTransactionManager;

    @Autowired
    private TransactionDefinition transactionDefinition;

    /**
     * Mysql 临时表使用测试
     */
    @Override
    public void tempTableInsert() {
        // 手动开启事务：基础数据组织机构数据量过多的时候会写临时表 left join 查询所以开启手动提交事务
        TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
        try {
            // 创建临时表
            String createSql = "DECLARE NUM INTEGER;\n" +
                    "BEGIN\n" +
                    "\tSELECT COUNT(*) INTO NUM FROM USER_ALL_TABLES WHERE TABLE_NAME='TEMPUSER';\n" +
                    "IF NUM=0\n" +
                    "THEN\n" +
                    "EXECUTE IMMEDIATE 'CREATE  GLOBAL TEMPORARY TABLE TEMPUSER\n" +
                    "   (ID VARCHAR2(36 CHAR), REQUESTID NVARCHAR2(36),CODE NVARCHAR2(30),\n" +
                    "       COLUMNNAME VARCHAR2(50 CHAR),DATATYPE VARCHAR2(30 CHAR)\n" +
                    "   ) ON COMMIT DELETE ROWS';\n" +
                    "END IF;\n" +
                    "END;";
            oracleMapper.executeSql(createSql);

            // 往临时表写数据
            List<User> userList = getUserList();
            List<List<User>> lists = ListUtils.splitList(userList, BaseConst.TEN_THOUSAND);
            for (List<User> list : lists) {
                oracleMapper.insertBatch(list);
            }

            List<Map<String, Object>> list1 = oracleMapper.listSql("select * from tempuser");
            // 使用临时表查询数据
            List<Map<String, Object>> list2 = oracleMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
            List<Map<String, Object>> list3 = commonMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
            queryTest1();
            // 删除临时表
            String dropSql = "DECLARE\n" +
                    "\tNUM NUMBER;\n" +
                    "BEGIN\n" +
                    "\tSELECT COUNT(1) INTO NUM FROM\n" +
                    "\tUSER_TABLES WHERE TABLE_NAME = UPPER('TEMPUSER') ;\n" +
                    "IF NUM > 0 THEN\n" +
                    "    EXECUTE IMMEDIATE 'DROP TABLE TEMPUSER' ;\n" +
                    "END IF;\n" +
                    "END;";
            oracleMapper.executeSql(dropSql);
        } finally {
            dataSourceTransactionManager.rollback(transactionStatus);
        }

    }

    private void queryTest1() {
        List<Map<String, Object>> list1 = oracleMapper.listSql("select * from tempuser");
        // 使用临时表查询数据
        List<Map<String, Object>> list2 = oracleMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
        List<Map<String, Object>> list3 = commonMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
        logger.info("queryTest1");
    }

    /**
     * 获取 10 万用户
     *
     * @return List
     */
    private List<User> getUserList() {
        List<User> userList = new ArrayList<User>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setId(i);
            user.setUsername("user" + i);
            user.setPassword("user" + i);
            userList.add(user);
        }
        return userList;
    }
}

会话级的临时表使用

• 直接创建直接使用就可以

数据库事务特性的使用

• 其实不用写临时表，可以创建一张固化表作为作为临时表使用
• 这张固化表使用手动提交事务的方式，等到查询sql 结束后回滚或者提交就可以了