社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

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

Java基基 • 3 月前 • 135 次点击  

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

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

👉这是一个或许对你有用的开源项目

国产 Star 破 10w+ 的开源项目,前端包括管理后台 + 微信小程序,后端支持单体和微服务架构。

功能涵盖 RBAC 权限、SaaS 多租户、数据权限、商城、支付、工作流、大屏报表、微信公众号等等功能:

  • Boot 仓库:https://gitee.com/zhijiantianya/ruoyi-vue-pro
  • Cloud 仓库:https://gitee.com/zhijiantianya/yudao-cloud
  • 视频教程:https://doc.iocoder.cn
【国内首批】支持 JDK 21 + SpringBoot 3.2.2、JDK 8 + Spring Boot 2.7.18 双版本 

来源:blog.csdn.net/liangmengbk


新建测试表(默认是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 (namevalueVALUES (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() * 1000000SECOND)
    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,'/',2AS
 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内存,压力不大)

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


欢迎加入我的知识星球,全面提升技术能力。

👉 加入方式,长按”或“扫描”下方二维码噢

星球的内容包括:项目实战、面试招聘、源码解析、学习路线。

文章有帮助的话,在看,转发吧。

谢谢支持哟 (*^__^*)

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/177485
 
135 次点击