MYSQL memory engine,性能不升反降

作为DBA,需要经常给各个业务系统做数据库优化。

现在遇到业务系统跟踪到两个慢查询:

(1) 单表16万多，在生产环境并发业务场景经常出现需要7秒多

# Query_time: 7.776702  Lock_time: 0.000091 Rows_sent: 0  Rows_examined: 160155 Thread_id: 28237376 Schema: prod_gw Errno: 0 Killed: 0 Bytes_received: 
SET timestamp=1607308218;
SELECT * FROM FILE_LEVEL        WHERE 1 = 1        AND (SLICE_STATUS = 2 OR  SLICE_STATUS = 3)        AND FILETYPE in (1,6)        AND FIND_IN_SET('2',SYSTEM_TYPE)        AND SENDCOUNT < 3        ORDER BY PRIORITY desc, CREATE_DATETIME asc        LIMIT 0,100;

(2)单表16万多，在生产环境并发业务场景经常出现需要10秒多

# Query_time: 10.499983  Lock_time: 0.000090 Rows_sent: 0  Rows_examined: 192378 Thread_id: 28237338 Schema: prod_gw Errno: 0 Killed: 0 Bytes_received:
# QC_Hit: No  Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No  Filesort: Yes  Filesort_on_disk: No
use prod_gw;
SET timestamp=1607308218;
select * from FILE_BLOCK        WHERE 1=1        AND UPLOADSTATUS in (0,3)        AND SEND_COUNT < 3        ORDER BY CREATE_DATETIME asc        LIMIT 0,1000;

业务流程大概是文件分块，传输类的需求。在不考虑修改业务逻辑的情况下，先考虑从数据库的角度进行分析和优化。

两个表表结构如下:

CREATE TABLE `file_block` (
  `REFID` varchar(32) NOT NULL DEFAULT '',
  `STUDYIUID` varchar(128) DEFAULT NULL,
  `FILEID` varchar(128) DEFAULT NULL,
  `FILE_GUID` varchar(64) DEFAULT NULL,
  `BLOCKINDEX` int(16) DEFAULT NULL,
  `UPLOADSTATUS` tinyint(1) DEFAULT NULL,
  `START_BYTE` int(32) DEFAULT NULL,
  `END_BYTE` int(32) DEFAULT NULL,
  `FILE_PATH` varchar(255) DEFAULT NULL,
  `BLOCK_COUNT` int(16) DEFAULT NULL,
  `SEND_COUNT` int(4) DEFAULT NULL,
  `CREATE_DATETIME` varchar(19) DEFAULT NULL,
  `RESEND_STATUS` tinyint(1) DEFAULT '0',
  `FILETYPE` int(4) DEFAULT '0',
  `MODIFY_DATETIME` varchar(19) DEFAULT NULL,
  PRIMARY KEY (`REFID`),
  KEY `FILEID_INDEX` (`FILEID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `file_block` (
  `REFID` varchar(32) NOT NULL DEFAULT '',
  `STUDYIUID` varchar(128) DEFAULT NULL,
  `FILEID` varchar(128) DEFAULT NULL,
  `FILE_GUID` varchar(64) DEFAULT NULL,
  `BLOCKINDEX` int(16) DEFAULT NULL,
  `UPLOADSTATUS` tinyint(1) DEFAULT NULL,
  `START_BYTE` int(32) DEFAULT NULL,
  `END_BYTE` int(32) DEFAULT NULL,
  `FILE_PATH` varchar(255) DEFAULT NULL,
  `BLOCK_COUNT` int(16) DEFAULT NULL,
  `SEND_COUNT` int(4) DEFAULT NULL,
  `CREATE_DATETIME` varchar(19) DEFAULT NULL,
  `RESEND_STATUS` tinyint(1) DEFAULT '0',
  `FILETYPE` int(4) DEFAULT '0',
  `MODIFY_DATETIME` varchar(19) DEFAULT NULL,
  PRIMARY KEY (`REFID`),
  KEY `FILEID_INDEX` (`FILEID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询语句主要的问题都是利用状态字段或者类似的字段进行过滤，where条件中的字段的值重复度高，离散程度很低，即使建索引也效果不好。

从业务流程上思考，都是临时存储的数据，处理完就会删除，接着处理另一批，数据的重要性程序较低，表中存储的数据行数通常不会超过100万。

网上都说mysql的内存数据库性能较好，是不是可以用mysql的memory 存储引擎，把表存到内存中，加快并发查询速度？

说干就干：

1： 根据上面两个表，分别新建对应的memory存储引擎表

file_level_memory 和 file_block_memory

2： 分别把原表的数据插入到新增的两个表

发现无法直接插入,需要修改 tmp_table_size 和 max_heap_table_size 的大小,改的足够大才行。

SELECT 
table_schema AS '数据库',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE(data_length/1024/1024, 2) AS '数据容量(MB)',
TRUNCATE(index_length/1024/1024, 2) AS '索引容量(MB)'
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA='prod_gw' AND t.TABLE_NAME='FILE_BLOCK'
ORDER BY data_length DESC, index_length DESC;

查询表占用空间大小，两个表分别占用: file_level内存表需要约880多M内存空间，file_block内存表需要 380多M内存空间

修改tmp_table_size 和 max_heap_table_size都到了1.5G，才完成所有数据的插入。

3：使用jmeter进行并发性能测试

使用上述慢查询sql，在jemter中对原表和内存表分别进行并发查询性能测试

每个表对应的查询语句分别进行三次测试,然而测试结果差强人意:(内存16G,CPU 8核)

前面是表名，后面是查询耗时

发现MEMORY引擎的内存表并没有比Innodb引擎的表查询快，相反还要略差？！

理论上不是使用内存表会更快吗？然而......

不仅如此，数据库运行期间，内存表会长期占用内存；会导致mysql占用的总内存明显增加。除非删除内存表，内存才会下降。