房产经济人怎么做网站,网站负责人可以备案,企业信息网站模板,南充房产管理网文章目录 1. 为什么临时表可以重名?1.1 临时表的特性1.2 临时表的应用1.3 为什么临时表可以重名#xff1f;1.4 临时表和主备复制 2. MySql内部临时表使用场景2.1 union 执行流程2.2 group by 执行流程2.3 group by 优化方法 -- 索引2.4 group by 优化方法 -- 直接排序 3. Me… 文章目录 1. 为什么临时表可以重名?1.1 临时表的特性1.2 临时表的应用1.3 为什么临时表可以重名1.4 临时表和主备复制 2. MySql内部临时表使用场景2.1 union 执行流程2.2 group by 执行流程2.3 group by 优化方法 -- 索引2.4 group by 优化方法 -- 直接排序 3. Memory引擎3.1 内存表的数据组织结构3.2 hash 索引和 B-Tree 索引3.3 内存表的锁3.4 数据持久性问题 1. 为什么临时表可以重名?
在优化 join 查询的时候使用到了临时表。当时是这么用的
create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b1 and b2000;
select * from t1 join temp_t on (t1.btemp_t.b);临时表有哪些特征为什么它适合这个场景
临时表和内存表的区别 内存表指的是使用 Memory 引擎的表建表语法是 create table … enginememory。这种表的数据都保存在内存里系统重启的时候会被清空但是表结构还在。除了这两个特性看上去比较“奇怪”外从其他的特征上看它就是一个正常的表。 临时表可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表写数据的时候是写到磁盘上的。当然临时表也可以使用 Memory 引擎。
1.1 临时表的特性
图 1 临时表特性示例 可以看到临时表在使用上有以下几个特点
建表语法是 create temporary table …。一个临时表只能被创建它的 session 访问对其他线程不可见。所以图中 session A 创建的临时表 t对于 session B 就是不可见的。临时表可以与普通表同名。session A 内有同名的临时表和普通表的时候show create 语句以及增删改查语句访问的是临时表。show tables 命令不显示临时表。
由于临时表只能被创建它的 session 访问所以在这个 session 结束的时候会自动删除临时表。也正是由于这个特性临时表就特别适合我们文章开头的 join 优化这种场景。原因如下:
不同 session 的临时表是可以重名的如果有多个 session 同时执行 join 优化不需要担心表名重复导致建表失败的问题。不需要担心数据删除问题。如果使用普通表在流程执行过程中客户端发生了异常断开或者数据库发生异常重启还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收所以不需要这个额外的操作。
1.2 临时表的应用
不用担心线程之间的重名冲突临时表经常会被用在复杂查询的优化过程中。其中分库分表系统的跨库查询就是一个典型的使用场景。
一般分库分表的场景就是要把一个逻辑上的大表分散到不同的数据库实例上。
比如。将一个大表 ht按照字段 f拆分成 1024 个分表然后分布到 32 个数据库实例上。如下图所示 图 2 分库分表简图 一般情况下这种分库分表系统都有一个中间层 proxy。不过也有一些方案会让客户端直接连接数据库也就是没有 proxy 这一层。在这个架构中分区 key 的选择是以“减少跨库和跨表查询”为依据的。
如果大部分的语句都会包含 f 的等值条件那么就要用 f 做分区键。这样在 proxy 这一层解析完 SQL 语句以后就能确定将这条语句路由到哪个分表做查询。
比如下面这条语句
select v from ht where fN;可以通过分表规则比如N%1024) 来确认需要的数据被放在了哪个分表上。这种语句只需要访问一个分表是分库分表方案最欢迎的语句形式了。
但是如果这个表上还有另外一个索引 k并且查询语句是这样的
select v from ht where k M order by t_modified desc limit 100;由于查询条件里面没有用到分区字段 f只能到所有的分区中去查找满足条件的所有行然后统一做 order by 的操作。
这种情况下有两种比较常用的思路。 第一种思路是在 proxy 层的进程代码中实现排序。 优点处理速度快拿到分库的数据以后直接在内存中参与计算 缺点 需要的开发工作量比较大。举例的这条语句还算是比较简单的如果涉及到复杂的操作比如 group by甚至 join 这样的操作对中间层的开发能力要求比较高对 proxy 端的压力比较大尤其是很容易出现内存不够用和 CPU 瓶颈的问题。 第二种思路就是把各个分库拿到的数据汇总到一个 MySQL 实例的一个表中然后在这个汇总实例上做逻辑操作。
比如上面这条语句执行流程可以类似这样
在汇总库上创建一个临时表 temp_ht表里包含三个字段 v、k、t_modified在各个分库上执行select v,k,t_modified from ht_x where k M order by t_modified desc limit 100;把分库执行的结果插入到 temp_ht 表中执行select v from temp_ht order by t_modified desc limit 100;
得到结果。 过程对应的流程图如下所示
图 3 跨库查询流程示意图 在实践中往往会发现每个分库的计算量都不饱和所以会直接把临时表 temp_ht 放到 32 个分库中的某一个上。
1.3 为什么临时表可以重名
在执行create temporary table temp_t(id int primary key)engineinnodb;语句的时候MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义还要有地方保存表数据。
这个 frm 文件放在临时文件目录下文件名的后缀是.frm前缀是“#sql{进程 id}{线程 id} 序列号”。可以使用 select tmpdir 命令来显示实例的临时文件目录。
关于表中数据的存放方式在不同的 MySQL 版本中有着不同的处理方式
在 5.6 以及之前的版本里MySQL 会在临时文件目录下创建一个相同前缀、以.ibd 为后缀的文件用来存放数据文件从 5.7 版本开始MySQL 引入了一个临时文件表空间专门用来存放临时文件的数据。因此就不需要再创建 ibd 文件了。
可以看到其实创建一个叫作 t1 的 InnoDB 临时表MySQL 在存储上认为我们创建的表名跟普通表 t1 是不同的因此同一个库下面已经有普通表 t1 的情况下还是可以再创建一个临时表 t1 的。
举例
图 4 临时表的表名 这个进程的进程号是 1234session A 的线程 id 是 4session B 的线程 id 是 5。可以看到session A 和 session B 创建的临时表在磁盘上的文件不会重名。
MySQL 维护数据表除了物理上要有文件外内存里面也有一套机制区别不同的表每个表都对应一个 table_def_key。
一个普通表的 table_def_key 的值是由“库名 表名”得到的所以如果你要在同一个库下创建两个同名的普通表创建第二个表的过程中就会发现 table_def_key 已经存在了。对于临时表table_def_key 在“库名 表名”基础上又加入了“server_idthread_id”。
session A 和 sessionB 创建的两个临时表 t1它们的 table_def_key 不同磁盘文件名也不同因此可以并存。
实现上每个线程都维护了自己的临时表链表。这样每次 session 内操作表的时候先遍历链表检查是否有这个名字的临时表如果有就优先操作临时表如果没有再操作普通表在 session 结束的时候对链表里的每个临时表执行 “DROP TEMPORARY TABLE 表名”操作。 1.4 临时表和主备复制
binlog 中也记录了 DROP TEMPORARY TABLE 这条命令。临时表只在线程内自己可以访问为什么需要写到 binlog 里面
既然写 binlog就意味着备库需要。
设想一下在主库上执行下面这个语句序列
create table t_normal(id int primary key, c int)engineinnodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/如果关于临时表的操作都不记录那么在备库就只有 create table t_normal 表和 insert into t_normal select * from temp_t 这两个语句的 binlog 日志备库在执行到 insert into t_normal 的时候就会报错“表 temp_t 不存在”。
如果把 binlog 设置为 row 格式因为 binlog 是 row 格式时在记录 insert into t_normal 的 binlog 时记录的是这个操作的数据即write_row event 里面记录的逻辑是“插入一行数据1,1)”。如果当前的 binlog_formatrow那么跟临时表有关的语句就不会记录到 binlog 里。也就是说只在 binlog_formatstatment/mixed 的时候binlog 中才会记录临时表的操作。
这种情况下创建临时表的语句会传到备库执行因此备库的同步线程就会创建这个临时表。主库在线程退出的时候会自动删除临时表但是备库同步线程是持续在运行的。所以这时候我们就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行。
问题 1 MySQL 在记录 binlog 的时候不论是 create table 还是 alter table 语句都是原样记录甚至于连空格都不变但是如果执行 drop table t_normal系统记录 binlog 就会写成DROP TABLE t_normal /* generated by server */ 也就是改成了标准的格式。为什么要这么做呢 drop table 命令是可以一次删除多个表的。比如在上面的例子中设置 binlog_formatrow如果主库上执行 drop table t_normal, temp_t这个命令那么 binlog 中就只能记录DROP TABLE t_normal /* generated by server */ 因为备库上并没有表 temp_t将这个命令重写后再传到备库执行才不会导致备库同步线程停止。 所以drop table 命令记录 binlog 的时候就必须对语句做改写。“/* generated by server */”说明了这是一个被服务端改写过的命令。 问题 2 主库上不同的线程创建同名的临时表是没关系的但是传到备库执行是怎么处理的呢
下面的序列中实例 S 是 M 的备库 主库 M 上的两个 session 创建了同名的临时表 t1这两个 create temporary table t1 语句都会被传到备库 S 上。
但是备库的应用日志线程是共用的也就是说要在应用线程里面先后执行这个 create 语句两次。即使开了多线程复制也可能被分配到从库的同一个 worker 中执行。这会不会导致同步线程报错
显然是不会。 MySQL 在记录 binlog 的时候会把主库执行这个语句的线程 id 写到 binlog 中。这样在备库的应用线程就能够知道执行每个语句的主库线程 id并利用这个线程 id 来构造临时表的 table_def_key
session A 的临时表 t1在备库的 table_def_key 就是库名 t1“M 的 serverid”“session A 的 thread_id”;session B 的临时表 t1在备库的 table_def_key 就是 库名 t1“M 的 serverid”“session B 的 thread_id”。
由于 table_def_key 不同所以这两个表在备库的应用线程里面是不会冲突的。 小结 在实际应用中临时表一般用于处理比较复杂的计算逻辑。由于临时表是每个线程自己可见的所以不需要考虑多个线程执行同一个处理逻辑时临时表的重名问题。在线程退出的时候临时表也能自动删除省去了收尾和异常处理的工作。
在 binlog_formatrow’的时候临时表的操作不记录到 binlog 中也省去了不少麻烦这也可以成为选择 binlog_format 时的一个考虑因素。
需要注意的是上面说到的这种临时表是用户自己创建的 也可以称为用户临时表。与它相对应的就是内部临时表 思考 下面的语句序列是创建一个临时表并将其改名
图 6 关于临时表改名的思考题 可以使用 alter table 语法修改临时表的表名而不能使用 rename 语法。这是为什么 执行 rename table 语句的时候要求按照“库名 / 表名.frm”的规则去磁盘找文件但是临时表在磁盘上的 frm 文件是放在 tmpdir 目录下的并且文件名的规则是“#sql{进程 id}{线程 id} 序列号.frm”因此会报“找不到文件名”的错误。 2. MySql内部临时表使用场景
2.1 union 执行流程
表 t1 来举例
create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begindeclare i int;set i1;while(i1000)doinsert into t1 values(i, i, i);set ii1;end while;
end;;
delimiter ;
call idata();执行下面这条语句
(select 1000 as f) union (select id from t1 order by id desc limit 2);这条语句用到了 union它的语义是取这两个子查询结果的并集。并集的意思就是这两个集合加起来重复的行只保留一行。 语句的 explain 结果
图 1 union 语句 explain 结果 可以看到
第二行的 keyPRIMARY说明第二个子句用到了索引 id。第三行的 Extra 字段表示在对子查询的结果集做 union 的时候使用了临时表 (Using temporary)。
语句的执行流程是这样的
创建一个内存临时表这个临时表只有一个整型字段 f并且 f 是主键字段。执行第一个子查询得到 1000 这个值并存入临时表中。执行第二个子查询 拿到第一行 id1000试图插入临时表中。但由于 1000 这个值已经存在于临时表了违反了唯一性约束所以插入失败然后继续执行取到第二行 id999插入临时表成功。 从临时表中按行取出数据返回结果并删除临时表结果中包含两行数据分别是 1000 和 999。
图 2 union 执行流程 这里的内存临时表起到了暂存数据的作用而且计算过程还用上了临时表主键 id 的唯一性约束实现了 union 的语义。 如果把上面这个语句中的 union 改成 union all 的话就没有了“去重”的语义。这样执行的时候就依次执行子查询得到的结果直接作为结果集的一部分发给客户端。因此也就不需要临时表了。
图 3 union all 的 explain 结果 第二行的 Extra 字段显示的是 Using index表示只使用了覆盖索引没有用临时表了。
2.2 group by 执行流程
另外一个常见的使用临时表的例子是 group by
select id%10 as m, count(*) as c from t1 group by m;这个语句的逻辑是把表 t1 里的数据按照 id%10 进行分组统计并按照 m 的结果排序后输出。它的 explain 结果如下
图 4 group by 的 explain 结果 可以看到:
Using index表示这个语句使用了覆盖索引选择了索引 a不需要回表Using temporary表示使用了临时表Using filesort表示需要排序。
执行流程:
创建内存临时表表里有两个字段 m 和 c主键是 m扫描表 t1 的索引 a依次取出叶子节点上的 id 值计算 id%10 的结果记为 x 如果临时表中没有主键为 x 的行就插入一个记录 (x,1);如果表中有主键为 x 的行就将 x 这一行的 c 值加 1 遍历完成后再根据字段 m 做排序得到结果集返回给客户端。
执行图如下:
图 5 group by 执行流程 图中最后一步对内存临时表的排序如下图 临时表的排序过程就是图 6 中虚线框内的过程。 图 7 group by 执行结果 如果需求并不需要对结果进行排序那可以在 SQL 语句末尾增加 order by null也就是改成
select id%10 as m, count(*) as c from t1 group by m order by null;这样就跳过了最后排序的阶段直接从临时表中取数据返回。返回的结果如图 8 所示。
图 8 group order by null 的结果内存临时表 由于表 t1 中的 id 值是从 1 开始的因此返回的结果集中第一行是 id1扫描到 id10 的时候才插入 m0 这一行因此结果集里最后一行才是 m0。 这个例子里由于临时表只有 10 行内存可以放得下因此全程只使用了内存临时表。但是内存临时表的大小是有限制的参数 tmp_table_size 就是控制这个内存大小的默认是 16M。
如果执行下面这个语句序列
set tmp_table_size1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;把内存临时表的大小限制为最大 1024 字节并把语句改成 id % 100这样返回结果里有 100 行数据。但是这时的内存临时表大小不够存下这 100 行数据也就是说执行过程中会发现内存临时表大小到达了上限1024 字节。这时候就会把内存临时表转成磁盘临时表磁盘临时表默认使用的引擎是 InnoDB。
返回的结果如图所示 如果这个表 t1 的数据量很大很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间
2.3 group by 优化方法 – 索引
可以看到不论是使用内存临时表还是磁盘临时表group by 逻辑都需要构造一个带唯一索引的表执行代价都是比较高的。如果表的数据量比较大上面这个 group by 语句执行起来就会很慢有什么优化的方法呢
问题 执行 group by 语句为什么需要临时表 group by 的语义逻辑是统计不同的值出现的个数。但是由于每一行的 id%100 的结果是无序的就需要有一个临时表来记录并统计结果。如果扫描过程中可以保证出现的数据是有序的就可以实现快速排序 现在有一个类似图 10 的这么一个数据结构
图 10 group by 算法优化 - 有序输入 如果可以确保输入的数据是有序的那么计算 group by 的时候就只需要从左到右顺序扫描依次累加。也就是下面这个过程
当碰到第一个 1 的时候已经知道累积了 X 个 0结果集里的第一行就是 (0,X);当碰到第一个 2 的时候已经知道累积了 Y 个 1结果集里的第二行就是 (1,Y);
按照这个逻辑执行的话扫描到整个输入的数据结束就可以拿到 group by 的结果不需要临时表也不需要再额外排序。 在 MySQL 5.7 版本支持了 generated column 机制用来实现列数据的关联更新。可以用下面的方法创建一个列 z然后在 z 列上创建一个索引如果是 MySQL 5.6 及之前的版本你也可以创建普通列和索引来解决这个问题。 alter table t1 add column z int generated always as(id % 100), add index(z);这样索引 z 上的数据就是类似图 10 这样有序的了。上面的 group by 语句就可以改成
select z, count(*) as c from t1 group by z;优化后的 group by 语句的 explain 结果如下图所示
图 11 group by 优化的 explain 结果 从 Extra 字段可以看到这个语句的执行不再需要临时表也不需要排序了。
2.4 group by 优化方法 – 直接排序
如果碰上不适合创建索引的场景还是要老老实实做排序的。那么这时候的 group by 要怎么优化呢
MySQL 有没有走磁盘临时表的方法? 在 group by 语句中加入 SQL_BIG_RESULT 这个提示hint就可以告诉优化器这个语句涉及的数据量很大请直接用磁盘临时表。 MySQL 的优化器一看磁盘临时表是 B 树存储存储效率不如数组来得高。所以既然告诉了数据量很大那从磁盘空间考虑还是直接用数组来存吧。
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;执行流程如下:
初始化 sort_buffer确定放入一个整型字段记为 m扫描表 t1 的索引 a依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中扫描完成后对 sort_buffer 的字段 m 做排序如果 sort_buffer 内存不够用就会利用磁盘临时文件辅助排序排序完成后就得到了一个有序数组。
两张图分别是执行流程图和执行 explain 命令得到的结果
图 12 使用 SQL_BIG_RESULT 的执行流程图
图 13 使用 SQL_BIG_RESULT 的 explain 结果 从 Extra 字段可以看到这个语句的执行没有再使用临时表而是直接用了排序算法。
MySQL 什么时候会使用内部临时表
如果语句执行过程可以一边读数据一边直接得到结果是不需要额外内存的否则就需要额外的内存来保存中间结果join_buffer 是无序数组sort_buffer 是有序数组临时表是二维表结构如果执行逻辑需要用到二维表特性就会优先考虑使用临时表。比如例子中union 需要用到唯一索引约束 group by 还需要用到另外一个字段来存累积计数。 小结 重点讲了 group by 的几种实现算法从中可以总结一些使用的指导原则
如果对 group by 语句的结果没有排序要求要在语句后面加 order by null尽量让 group by 过程用上表的索引确认方法是 explain 结果里没有 Using temporary 和 Using filesort如果 group by 需要统计的数据量不大尽量只使用内存临时表也可以通过适当调大 tmp_table_size 参数来避免用到磁盘临时表如果数据量实在太大使用 SQL_BIG_RESULT 这个提示来告诉优化器直接使用排序算法得到 group by 的结果。 思考 文章中图 8 和图 9 都是 order by null为什么图 8 的返回结果里面0 是在结果集的最后一行而图 9 的结果里面0 是在结果集的第一行 答案见下节3. Memory引擎正文 3. Memory引擎
3.1 内存表的数据组织结构
假设有以下的两张表 t1 和 t2其中表 t1 使用 Memory 引擎 表 t2 使用 InnoDB 引擎。
create table t1(id int primary key, c int) engineMemory;
create table t2(id int primary key, c int) engineinnodb;
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);分别执行 select * from t1 和 select * from t2。 图 1 两个查询结果 -0 的位置 可以看到内存表 t1 的返回结果里面 0 在最后一行而 InnoDB 表 t2 的返回结果里 0 在第一行。
表 t2 用的是 InnoDB 引擎它的主键索引 id 的组织方式InnoDB 表的数据就放在主键索引树上主键索引是 B 树。所以表 t2 的数据组织方式如下图所示
图 2 表 t2 的数据组织 与 InnoDB 引擎不同Memory 引擎的数据和索引是分开的。看一下表 t1 中的数据内容
图 3 表 t1 的数据组织 可以看到内存表的数据部分以数组的方式单独存放而主键 id 索引里存的是每个数据的位置。主键 id 是 hash 索引可以看到索引上的 key 并不是有序的。
在内存表 t1 中当我执行 select * 的时候走的是全表扫描也就是顺序扫描这个数组。因此0 就是最后一个被读到并放入结果集的数据。
可见InnoDB 和 Memory 引擎的数据组织方式是不同的
InnoDB 引擎把数据放在主键索引上其他索引上保存的是主键 id。这种方式称之为索引组织表Index Organizied Table。Memory 引擎采用的是把数据单独存放索引上保存数据位置的数据组织形式我们称之为堆组织表Heap Organizied Table。
这两个引擎的一些典型不同
InnoDB 表的数据总是有序存放的而内存表的数据就是按照写入顺序存放的当数据文件有空洞的时候InnoDB 表在插入新数据的时候为了保证数据有序性只能在固定的位置写入新值而内存表找到空位就可以插入新值数据位置发生变化的时候InnoDB 表只需要修改主键索引而内存表需要修改所有索引InnoDB 表用主键索引查询时需要走一次索引查找用普通索引查询的时候需要走两次索引查找。而内存表没有这个区别所有索引的“地位”都是相同的。InnoDB 支持变长数据类型不同记录的长度可能不同内存表不支持 Blob 和 Text 字段并且即使定义了 varchar(N)实际也当作 char(N)也就是固定长度字符串来存储因此内存表的每行数据长度相同。
由于内存表的这些特性每个数据行被删除以后空出的这个位置都可以被接下来要插入的数据复用
比如如果要在表 t1 中执行
delete from t1 where id5;
insert into t1 values(10,10);
select * from t1;看到返回结果里id10 这一行出现在 id4 之后也就是原来 id5 这行数据的位置。
需要指出的是表 t1 的这个主键索引是哈希索引因此如果执行范围查询比如
select * from t1 where id5;是用不上主键索引的需要走全表扫描。
3.2 hash 索引和 B-Tree 索引
内存表也是支持 B-Tree 索引的。在 id 列上创建一个 B-Tree 索引SQL 语句可以这么写
alter table t1 add index a_btree_index using btree (id);这时表 t1 的数据组织形式就变成了这样
图 4 表 t1 的数据组织 – 增加 B-Tree 索引 新增 B-Tree 索引跟 InnoDB 的 b 树索引组织形式类似。 看一下这下面这两个语句的输出
图 5 使用 B-Tree 和 hash 索引查询返回结果对比 执行 select * from t1 where id5 的时候优化器会选择 B-Tree 索引所以返回结果是 0 到 4。 使用 force index 强行使用主键 id 这个索引id0 这一行就在结果集的最末尾了。
一般在我们的印象中内存表的优势是速度快其中的一个原因就是 Memory 引擎支持 hash 索引。当然更重要的原因是内存表的所有数据都保存在内存而内存的读写速度总是比磁盘快。
但是不建议在生产环境上使用内存表。这里的原因主要包括两个方面
锁粒度问题数据持久化问题。
3.3 内存表的锁
内存表不支持行锁只支持表锁。因此一张表只要有更新就会堵住其他所有在这个表上的读写操作。 注意的是这里的表锁跟之前我们介绍过的 MDL 锁不同但都是表级的锁。
图 6 内存表的表锁 – 复现步骤 session A 的 update 语句要执行 50 秒在这个语句执行期间 session B 的查询会进入锁等待状态。session C 的 show processlist 结果输出如下
图 7 内存表的表锁 – 结果 跟行锁比起来表锁对并发访问的支持不够好。所以内存表的锁粒度问题决定了它在处理并发事务的时候性能也不会太好。
3.4 数据持久性问题
数据放在内存中是内存表的优势但也是一个劣势。因为数据库重启的时候所有的内存表都会被清空。
如果数据库异常重启内存表被清空也就清空了不会有什么问题。但是在高可用架构下内存表的这个特点简直可以当做 bug 来看待了
先看看 M-S 架构下使用内存表存在的问题。
图 8 M-S 基本架构 看一下下面这个时序:
业务正常访问主库备库硬件升级备库重启内存表 t1 内容被清空备库重启后客户端发送一条 update 语句修改表 t1 的数据行这时备库应用线程就会报错“找不到要更新的行”。
这样就会导致主备同步停止。当然如果这时候发生主备切换的话客户端会看到表 t1 的数据“丢失”了。
在图 8 中这种有 proxy 的架构里大家默认主备切换的逻辑是由数据库系统自己维护的。这样对客户端来说就是“网络断开重连之后发现内存表数据丢失了”。 由于 MySQL 知道重启之后内存表的数据会丢失。所以担心主库重启之后出现主备不一致MySQL 在实现上做了这样一件事儿在数据库重启之后往 binlog 里面写入一行 DELETE FROM t1。
如果使用是如图 9 所示的双 M 结构的话
图 9 双 M 结构 在备库重启的时候备库 binlog 里的 delete 语句就会传到主库然后把主库内存表的内容删除。这样在使用的时候就会发现主库的内存表数据突然被清空了。
基于上面的分析内存表并不适合在生产环境上作为普通数据表使用。
如果你的表更新量大那么并发度是一个很重要的参考指标InnoDB 支持行锁并发度比内存表好能放到内存表的数据量都不大。如果你考虑的是读的性能一个读 QPS 很高并且数据量不大的表即使是使用 InnoDB数据也是都会缓存在 InnoDB Buffer Pool 里的。因此使用 InnoDB 表的读性能也不会差。
**建议你把普通内存表都用 InnoDB 表来代替。**但是有一个场景却是例外的。在数据量可控不会耗费过多内存的情况下可以考虑使用内存表。
内存临时表刚好可以无视内存表的两个不足主要是下面的三个原因
临时表不会被其他线程访问没有并发性的问题临时表重启后也是需要删除的清空数据这个问题不存在备库的临时表也不会影响主库的用户线程。 小结 由于重启会丢数据如果一个备库重启会导致主备同步线程停止如果主库跟这个备库是双 M 架构还可能导致主库的内存表数据被删掉。 因此在生产上不建议使用普通内存表。
如果你是 DBA可以在建表的审核系统中增加这类规则要求业务改用 InnoDB 表。我们在文中也分析了其实 InnoDB 表性能还不错而且数据安全也有保障。而内存表由于不支持行锁更新语句会阻塞查询性能也未必就如想象中那么好。
基于内存表的特性我们还分析了它的一个适用场景就是内存临时表。 思考 假设你刚刚接手的一个数据库上真的发现了一个内存表。备库重启之后肯定是会导致备库的内存表数据被清空进而导致主备同步停止。这时最好的做法是将它修改成 InnoDB 引擎表。假设当时的业务场景暂时不允许你修改引擎你可以加上什么自动化逻辑来避免主备同步停止呢
假设的是主库暂时不能修改引擎那么就把备库的内存表引擎先都改成 InnoDB。对于每个内存表执行
set sql_log_binoff;
alter table tbl_name engineinnodb;这样就能避免备库重启的时候数据丢失的问题。
由于主库重启后会往 binlog 里面写“delete from tbl_name”这个命令传到备库备库的同名的表数据也会被清空。
因此就不会出现主备同步停止的问题。
如果由于主库异常重启触发了 HA这时候我们之前修改过引擎的备库变成了主库。而原来的主库变成了新备库在新备库上把所有的内存表这时候表里没数据都改成 InnoDB 表。
所以如果我们不能直接修改主库上的表引擎可以配置一个自动巡检的工具在备库上发现内存表就把引擎改了。
同时跟业务开发同学约定好建表规则避免创建新的内存表。 来自林晓斌《MySql实战45讲》
