MySQL Memory引擎

布满荆棘的人生 2023-03-02 10:16 1阅读 0赞

# 数据组织 #

## 表初始化 ##

CREATE TABLE t1 (id INT PRIMARY KEY, c INT) ENGINE=Memory;
    CREATE TABLE t2 (id INT PRIMARY KEY, c INT) ENGINE=InnoDB;
    INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
    INSERT INTO t2 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

## 执行语句 ##

-- 0在最后
    mysql> SELECT * FROM t1;
    +----+------+
    | id | c    |
    +----+------+
    |  1 |    1 |
    |  2 |    2 |
    |  3 |    3 |
    |  4 |    4 |
    |  5 |    5 |
    |  6 |    6 |
    |  7 |    7 |
    |  8 |    8 |
    |  9 |    9 |
    |  0 |    0 |
    +----+------+
    
    -- 0在最前
    mysql> SELECT * FROM t2;
    +----+------+
    | id | c    |
    +----+------+
    |  0 |    0 |
    |  1 |    1 |
    |  2 |    2 |
    |  3 |    3 |
    |  4 |    4 |
    |  5 |    5 |
    |  6 |    6 |
    |  7 |    7 |
    |  8 |    8 |
    |  9 |    9 |
    +----+------+

## 组织形式 ##

1.  索引组织表（Index Organizied Table）：InnoDB引擎把数据放在主键索引上，其它索引上保存主键ID
2.  堆组织表（Heap Organizied Table）：Memory引擎把数据单独存放，索引上保存数据位置

## 索引组织表 ##

t2的数据组织方式，主键索引上的值是有序存储的，执行SELECT \*时，按叶子节点从左到右扫描  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70]

## 堆组织表 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 1]

1.  Memory引擎的数据和索引是分开存储的。**数据部分以数组的方式单独存放，主键索引（采用哈希索引）存的是数据位置**
2.  在t1上执行SELECT \*时，走的也是全表扫描，即顺序扫描整个数组，因此0是最后一个被读到的
3.  t1的主键索引是哈希索引，如果是范围查询，需要走全表扫描，例如SELECT \* FROM t1 WHERE id<5
4.  Memory表也支持B-Tree索引

### B-Tree索引 ###

ALTER TABLE t1 ADD INDEX a_btree_index USING BTREE (id);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 2]

-- 优化器选择了a_btree_index索引
    mysql> EXPLAIN SELECT * FROM t1 WHERE id<5;
    +----+-------------+-------+------------+-------+-----------------------+---------------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys         | key           | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+-----------------------+---------------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | t1    | NULL       | range | PRIMARY,a_btree_index | a_btree_index | 4       | NULL |    6 |   100.00 | Using where |
    +----+-------------+-------+------------+-------+-----------------------+---------------+---------+------+------+----------+-------------+
    
    -- 0在最前面
    mysql> SELECT * FROM t1 WHERE id<5;
    +----+------+
    | id | c    |
    +----+------+
    |  0 |    0 |
    |  1 |    1 |
    |  2 |    2 |
    |  3 |    3 |
    |  4 |    4 |
    +----+------+
    
    -- 强制走主键索引，为哈希索引，走全表扫描
    mysql> SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE id<5;
    +----+------+
    | id | c    |
    +----+------+
    |  1 |    1 |
    |  2 |    2 |
    |  3 |    3 |
    |  4 |    4 |
    |  0 |    0 |
    +----+------+

## 对比 ##

1.  InnoDB的数据总是有序存放的，而内存表的数据是按照写入顺序存放的
2.  当数据文件有空洞时
    
    1.  InnoDB表在插入新数据时，为了保证数据的有序性，只能在固定位置写入新值
    2.  而Memory表只要找到空位就可以插入新值
3.  数据位置发生变化时，InnoDB只需要修改主键索引，而Memory表需要修改所有索引
4.  数据查找
    
    1.  InnoDB表利用主键查找需要走一次索引查找，用辅助索引查找需要走两次索引查找
    2.  Memory表中所有索引的地位都是相同的
5.  变长数据类型
    
    1.  InnoDB表支持变长数据类型
    2.  Memory表不支持BLOB和TEXT类型
        
        1.  即使定义了VARCHAR(N)，实际也会当做CHAR(N)，固定长度
        2.  此Memory表的每行数据长度相同
        3.  每个数据被删除后，空出的位置可以被接下来要插入的数据复用

DELETE FROM t1 WHERE id=5;
    INSERT INTO t1 VALUES (10,10);
    
    mysql> SELECT * FROM t1;
    +----+------+
    | id | c    |
    +----+------+
    |  1 |    1 |
    |  2 |    2 |
    |  3 |    3 |
    |  4 |    4 |
    | 10 |   10 |
    |  6 |    6 |
    |  7 |    7 |
    |  8 |    8 |
    |  9 |    9 |
    |  0 |    0 |
    +----+------+

## 缺点 ##

不推荐在生产环境使用Memory表

## 锁粒度 ##

Memory表不支持行锁，只支持表锁（并不是MDL锁），对并发访问的支持不够好。

<table> 
 <thead> 
  <tr> 
   <th>SessionA</th> 
   <th>SessionB</th> 
   <th>SessionC</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>UPDATE t1 SET id=SLEEP(50) WHERE id=1;</code></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>SELECT * FROM t1 WHERE id=2;(Wait 50s)</code></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td><code>SHOW PROCESSLIST;</code></td> 
  </tr> 
 </tbody> 
</table>

## 数据持久化 ##

数据库重启后，所有的Memory表都会被清空

## Master-Slave架构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 3]

1.  业务正常访问主库
2.  备库硬件升级，备库重启，内存表t1被清空
3.  备库重启后，应用日志线程执行一条UPDATE t1的语句，备库会报错，导致主备同步停止
4.  如果期间发生主备切换，客户端会看到t1的数据丢失了

## Master-Master架构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 4]

1.  MySQL知道重启后，Memory表的数据会丢失，所以担心主库重启后，会出现主备不一致
2.  在数据库重启之后，自动往binlog里面写一行DELETE FROM t1
3.  在备库重启时，备库binlog的DELETE语句会传到主库，然后主库Memory表的内容被莫名其妙地删除了

## 选择InnoDB ##

1.  如果表的更新量很大，那么并发度是一个很重要的参考指标，InnoDB支持行锁
2.  能放到Memory表的数据量都不大，InnoDB也有Buffer Pool，读性能也不差
3.  建议将普通Memory表替换成InnoDB表
4.  例外场景：在数据量可控，可以采用内存临时表，例如JOIN优化里面的临时表优化。 **CREATE TEMPORARY TABLE … ENGINE=Memory**

PS： 参考资料 《MySQL实战45讲》

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70]: /images/20230208/103f40d2597a4aef8089c3799865a377.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 1]: /images/20230208/d7d9ab17cfdd4820b68a060fd373955d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 2]: /images/20230208/74b9a996ad0642bc82e363c869f10488.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 3]: /images/20230208/8da828e7e4e64da6b15b5875b9bd22c2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 4]: /images/20230208/ebdadfb670cb4747953a9cac1124b6d6.png