MySQL优化，200万数据，十倍效率提升方案

👉 这是一个或许对你有用的社群

🐱 一对一交流/面试小册/简历优化/求职解惑，欢迎加入「芋道快速开发平台」知识星球。下面是星球提供的部分资料： 

• 《项目实战（视频）》：从书中学，往事中“练”
• 《互联网高频面试题》：面朝简历学习，春暖花开
• 《架构 x 系统设计》：摧枯拉朽，掌控面试高频场景题
• 《精进 Java 学习指南》：系统学习，互联网主流技术栈
• 《必读 Java 源码专栏》：知其然，知其所以然

• 对比实验1：全表扫描（相差11倍）
• 对比实验2：等值筛选（相差3倍）
• 对比实验3：等值筛选 + count（*）（几乎相等）
• 对比实验4：多条件等值筛选（相差3倍）
• 对比实验5：IN 多值（相差3倍）
• 对比实验6：like 全表扫描+排序（相差2倍）
• 总结
• 可能遇到的问题

新建测试表（默认是InnoDB引擎）

CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50)  DEFAULT NULL,
  `value` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

往测试表中插入200万条测试数据：

• 创建一个生成数据的存储过程，用于快速批量插入数据：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test_data`()
BEGIN
    DECLARE i INT DEFAULT 413241;
    WHILE i < 2000000 DO
        INSERT INTO test_table (name, value) VALUES (CONCAT('test_name_', i), i);
        SET i = i + 1;
    END WHILE;
END

• 执行存储过程：

call insert_test_data();

• 为日期字段赋值，值为随机值：

update test_table t
SET t.create_time = (
    SELECT DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 1000000) SECOND)
    WHERE create_time IS NULL
)

以上脚本执行完毕后，测试数据插入完毕。

执行分析语句，可以看到查询表完整数据，实际会花费3303毫秒。

explain analyze select * from test_table;

现在新建一张相同字段的测试表：

CREATE TABLE m_test_table LIKE test_table;

将表的引擎改为memory：

往新的测试表中插入数据：

INSERT INTO m_test_table
SELECT * FROM test_table;

在这一步，可能会报“The table 'm_test_table' is full”这个错误。这是因为系统默认给内存表分配的空间大小是16M，可以通过更新配置的方式，调整这个大小。

SET GLOBAL max_heap_table_size = 51539607552;
SET GLOBAL tmp_table_size = 51539607552;

具体调整到多少合适，根据服务器实际内存进行调整。

调整完毕后，需要重新创建内存表，重新插入数据。

对比实验1：全表扫描（相差11倍）

新表数据插入完毕后，执行分析语句，可以看到查询表完整数据，实际会花费296毫秒。

跟原表test_table相比，数据完全一致，查询完整数据，速度上相差11倍（3303/296）。

查询速度上的差异，主要原因是test_table表的引擎为InnoDB，数据存储在磁盘上的。m_test_table表的引擎为MEMORY，数据存储在内存中。内存的读取速度会比磁盘快很多。

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

• 项目地址：https://github.com/YunaiV/ruoyi-vue-pro
• 视频教程：https://doc.iocoder.cn/video/

对比实验2：等值筛选（相差3倍）

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

• 项目地址：https://github.com/YunaiV/yudao-cloud
• 视频教程：https://doc.iocoder.cn/video/

对比实验3：等值筛选 + count(*)（几乎相等）

对比实验4：多条件等值筛选（相差3倍）

对比实验5：IN 多值（相差3倍）

对比实验6：like 全表扫描+排序（相差2倍）

查看数据存储尺寸

select *
  from information_schema.TABLES ta
where 1=1
  and ta.table_schema not in (
 'information_schema',
 'mysql',
 'sys',
 'performance_schema'
 )
order by ta.table_schema,ta.table_name;

查看不同存储引擎占用内存情况

SELECT SUBSTRING_INDEX(event_name,'/',2) AS
 code_area, sys.format_bytes(SUM(current_alloc))
 AS current_alloc
 FROM sys.x$memory_global_by_current_bytes
 GROUP BY SUBSTRING_INDEX(event_name,'/',2)
 ORDER BY SUM(current_alloc) DESC;

总结

MEMORY引擎虽然速度快，但也有缺点，缺点是数据在内存中保存，如果服务器重启，内存中的数据就会丢失。所以内存表并不适用于所有情况。MySQL默认引擎是InnoDB，数据存储在磁盘上。

使用场景：

• 缓存数据： 内存表可以用作缓存临时数据或频繁访问的数据，以提高查询性能。
• 临时表： 在处理复杂查询或中间计算时，可以使用内存表作为临时存储。
• 会话数据： 对于需要快速访问但不需要持久化的会话数据，内存表是一个很好的选择。
• 只读数据： 如果数据是只读的，并且不需要长期保存，那么内存表可以提供极佳的查询性能。
• 实时数据分析： 对于需要快速响应的实时数据分析任务，内存表可以显著提高处理速度。

针对Memory引擎，数据丢失和同步，问题的解决方法：

• Memory与InnoDB引擎同时创建一张相同的表。
• 在应用启动时，将InnoDB表全量数据同步到Memory表中，程序访问Memory表中的数据。
• 使用MySQL触发器，在对InnoDB表操作数据时，自动更新Memory表与InnoDB据一致。

如果不方便使用触发器，也可以将程序代码改为同步双写，在往InnoDB表插入数据后，同步往Memory表中也插入一条数据。

注意Memory不支持事务，抛异常手动删除即可

可能遇到的问题

为什么不用Redis？

Redis无法使用SQL，多维度查询比较慢。

如果单条数据大，会形成大Key，对单线程的Redis是致命的。

Memory引擎是表锁，频繁写入是否会出现瓶颈？

虚拟机环境460万数据10秒写入，锁表时间极端，大概率不会成为瓶颈。

服务器内存会占用多少？

以booking(预定为例，5千万行数据，全部载入占用2G内存，压力不大）

遇到单行超大情况，建议拆表剥离大字段，将其他小字段载入内存表。