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 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,'/'