阅读全文

⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 blog.csdn.net/liangmengbk 「梁萌」欢迎转载，保留摘要，谢谢！

🙂🙂🙂关注微信公众号：【芋道源码】有福利：

RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表

RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址

您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。

新的源码解析文章实时收到通知。每周更新一篇左右。

认真的源码交流微信群。

新建测试表（默认是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，数据存储在内存中。内存的读取速度会比磁盘快很多。

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

对比实验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内存，压力不大）

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

芋道源码 —— 纯源码解析博客

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