MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
42933 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

38 | 都说InnoDB好,那还要不要使用Memory引擎?

林晓斌 2019-02-08
我在上一篇文章末尾留给你的问题是:两个 group by 语句都用了 order by null,为什么使用内存临时表得到的语句结果里,0 这个值在最后一行;而使用磁盘临时表得到的结果里,0 这个值在第一行?
今天我们就来看看,出现这个问题的原因吧。

内存表的数据组织结构

为了便于分析,我来把这个问题简化一下,假设有以下的两张表 t1 和 t2,其中表 t1 使用 Memory 引擎, 表 t2 使用 InnoDB 引擎。
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);
然后,我分别执行 select * from t1 和 select * from t2。
图 1 两个查询结果 -0 的位置
可以看到,内存表 t1 的返回结果里面 0 在最后一行,而 InnoDB 表 t2 的返回结果里 0 在第一行。
出现这个区别的原因,要从这两个引擎的主键索引的组织方式说起。
表 t2 用的是 InnoDB 引擎,它的主键索引 id 的组织方式,你已经很熟悉了:InnoDB 表的数据就放在主键索引树上,主键索引是 B+ 树。所以表 t2 的数据组织方式如下图所示:
图 2 表 t2 的数据组织
主键索引上的值是有序存储的。在执行 select * 的时候,就会按照叶子节点从左到右扫描,所以得到的结果里,0 就出现在第一行。
与 InnoDB 引擎不同,Memory 引擎的数据和索引是分开的。我们来看一下表 t1 中的数据内容。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(20)

  • 老师新年快乐!过年都不忘给我们传授知识!

    作者回复: 新年快乐🤝

    2019-02-08
    5
  • 于家鹏
    新年好!
    课后作业:在备库配置跳过该内存表的主从同步。

    有一个问题一直困扰着我:SSD以及云主机的广泛运用,像Innodb这种使用WAL技术似乎并不能发挥最大性能(我的理解:基于SSD的WAL更多的只是起到队列一样削峰填谷的作用)。对于一些数据量不是特别大,但读写频繁的应用(比如点赞、积分),有没有更好的引擎推荐。

    作者回复: 即使是SSD,顺序写也比随机写快些的。 不过确实没有机械盘那么明显。

    2019-02-08
    4
  • lionetes
    重启前 my.cnf 添加 skip-slave-errors 忽略 内存表引起的主从异常导致复制失败

    作者回复: 嗯,这个也是可以的。不过也会放过其他引擎表的主备不一致的报错哈

    2019-02-11
    3
  • 往事随风,顺其自然
    为什么memory 引擎中数据按照数组单独存储,0索引对应的数据怎么放到数组的最后

    作者回复: 这就是堆组织表的数据存放方式

    2019-02-09
    3
  • 陈扬鸿
    老师你好,今天生产上出碰到一个解决不了的问题,php的yii框架,使用show full processlist 查看 全是如下语句有100多条
    SELECT
        kcu.constraint_name,
        kcu.column_name,
        kcu.referenced_table_name,
        kcu.referenced_column_name
    FROM information_schema.referential_constraints AS rc
    JOIN information_schema.key_column_usage AS kcu ON
        (
            kcu.constraint_catalog = rc.constraint_catalog OR
            (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
        ) AND
        kcu.constraint_schema = rc.constraint_schema AND
        kcu.constraint_name = rc.constraint_name
    WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
    AND rc.table_name = 't1' AND kcu.table_name = 't1'
    这个可以优化吗 这个库是数据字典的 现在数据库无法对外提供服务 请老师指教!

    作者回复: 你看看能不能把短连接改成长连接

    还有,这个语句应该是没用的,看看能不能通过配置框架参数,来避免执行这个语句

    2019-03-06
    1
    1
  • llx
    1、如果临时表读数据的次数很少(比如只读一次),是不是建临时表时不创建索引效果很更好?
    2、engine=memory 如果遇到范围查找,在使用哈希索引时应该不会使用索引吧

    作者回复: 1. 取决于对临时表的访问模式哦,如果是需要用到索引查找,还是要创建的。如果创建的临时表只是用于全表扫描,就可以不创建索引;
    2. 是的,如果明确要用范围查找,就得创建b-tree索引

    2019-02-11
    1
  • AI杜嘉嘉
    我的认识里,有一点不是很清楚。memory这个存储引擎,最大的特性应该是把数据存到内存。但是innodb也可以把数据存到内存,不但可以存到内存(innodb buffer size),还可以进行持久化。这样一对比,我感觉memory的优势更没有了。不知道我讲的对不对

    作者回复: 是,如我们文中说的,不建议使用普通内存表了哈

    2019-02-10
    1
  • Long
    老师新年好 :-)
    刚好遇到一个问题。

    本来准备更新到,一个查询是怎么运行的里面的,看到这篇更新文章,就写在这吧,希望老师帮忙解答。

    关于这个系统memory引擎表:information_schema.tables
    相关信息如下
    (1)Verison: MySQL 5.6.26
    (2)数据量table_schema = abc的有接近4W的表,整个实例有接近10W的表。(默认innodb引擎)
    (3)mysql.user和mysql.db的数据量都是100-200的行数,MyISAM引擎。
    (4)默认事务隔离级别RC


    在运行查询语句1的时候:select * from information_schema.tables where table_schema = 'abc';
    状态一直是check permission,opening tables,其他线程需要打开的表在opend tables里面被刷掉的,会显示在opening tables,可能需要小几秒后基本恢复正常。

    但是如果在运行查询语句2:select count(1) from information_schema.tables where table_schema = 'abc'; 这个时候语句2本身在profiling看长期处于check permission状态,其他线程就会出现阻塞现象,大部分卡在了opening tables,小部分closing tables。我测试下了,当个表查询的时候check permission大概也就是0.0005s左右的时间,4W个表理论良好状态应该是几十秒的事情。
    但是语句1可能需要5-10分钟,语句2需要5分钟。

    3个问题,请老师抽空看下:
    (1)information_schema.tables的组成方式,是我每次查询的时候从数据字典以及data目录下的文件中实时去读的吗?
    (2)语句1和语句2在运行的时候的过程分别是怎样的,特别是语句2。
    (3)语句2为什么会出现大量阻塞其他事务,其他事务都卡在opening tables的状态。


    PS: 最后根据audit log分析来看,语句实际上是MySQL的一个客户端Toad发起的,当使用Toad的object explorer的界面来查询表,或者设置connection的时候指定的的default schema是大域的时候就会run这个语句:(table_schema改成了abc,其他都是原样)
    SELECT COUNT(1) FROM information_schema.tables WHERE table_schema = 'abc' AND table_type != 'VIEW';


    再次感谢!

    作者回复: 1&2 查询information_schema.tables的时候,会把所有的表都访问到一次,这里不止是4w个表,而是这个实例上所有的表,也就是10万

    3. 因为系统一般设置的table_definition_cache 都不会太大,你要打开10万张表,就只能轮流打开,然后轮流从table_definition_cache里面淘汰。这样就跟其他查询在table_definition_cache这个结构里出现了互相等待资源的情况。

    嗯,这个其实就是我不建议用界面工具的原因之一

    不好意思,你这个问题这么迟才回复你😆

    2019-02-08
    1
  • godtrue
    老师 memory 引擎,只有表锁,这个表锁是锁在哪里?
    “在内存表 t1 中,当我执行 select * 的时候,走的是全表扫描,也就是顺序扫描这个数组。因此,0 就是最后一个被读到,并放入结果集的数据。”
    这个数组是指存放数据的数组,这次(0,0)是在数据数组的最后,难道每次都会放在最后,没明白为什么会这样?
    另外,看图3存放数据的数组是有序的,难道是先依次存好数据,然后对主键哈希定位主键应该存放的位置?
    2019-08-06
  • Geek_37984c
    数据位置发生变化的时候,InnoDB 表只需要修改主键索引,而内存表需要修改所有索引?
    --老师内存表为什么要修改所有索引,删旧数据,再新增一条是否就可以解决

    2019-07-07
  • 纸片人
    等等,我好像明白了。结合下文,这里说的“诡异”现象是在双M架构中,用户没有断开连接或收到错误报告的情况下,看到表t1的数据全部消失了!这不符合隔离性。这就相当于,MySQL一边告诉你“一切正常”,一边偷偷删除了你的数据。
    2019-06-18
  • Long
    追问更新1: 谢谢老师的答复,我看了下innodb_stats_on_metadata就是OFF,今天在5.7环境验证,发现竟然不是几百秒,而且几秒,不知道这个是代码的优化,还是参数不一致的原因,有几十个参数差异需要排查。 所以在不知道是因为参数变化导致,还是内部查询逻辑变化。如果是参数,担心有一天被人设置错,又回滚到不好的情况. 老师,我想入坑源码了…

    作者回复: 这种情况最适合源码入坑😄

    你有两个可以稳定复现的对比场景,而且单线程就能复现。

    这两天我用电脑不方便,下周末来给出答案哈。

    你可否把5.6/5.7这个对照试验组,包括实验过程和结果差异,再单独写一个问题😄

    2019-03-01
  • Long
    追问:多谢老师回复,在上面回复中,为什么语句2会阻塞其他的线程把其他线程都卡在opening tables 和closing tables,而语句1不会.

    猜测是不是语句2用了lock_open的方法?
    老师有什么好的建议,我怎么能通过日志或者调试来看?
    已经看了innodb status, processlist, profiling都看了,没发现异常

    语句1: SELECT table_name, table_schema, data_length, index_length FROM information_schema.TABLES WHERE ENGINE in ('MyISAM','InnoDB') and table_schema <> 'information_schema';

    语句2:select count(1) from information_schema.tables where table_schema = 'abc';

    作者回复: 你如果是innodb_stats_on_metadata设置为off的话,第二个语句是不用打开表的。

    2019-03-01
  • 夜空中最亮的星(华仔)
    老师,结合课程 和 MySQL8 的新技术 老师有出书的计划吗?
    2019-02-13
  • Long
    老师可能没看到,再发下。

    老师新年好 :-)
    刚好遇到一个问题。

    本来准备更新到,一个查询是怎么运行的里面的,看到这篇更新文章,就写在这吧,希望老师帮忙解答。

    关于这个系统memory引擎表:information_schema.tables
    相关信息如下
    (1)Verison: MySQL 5.6.26
    (2)数据量table_schema = abc的有接近4W的表,整个实例有接近10W的表。(默认innodb引擎)
    (3)mysql.user和mysql.db的数据量都是100-200的行数,MyISAM引擎。
    (4)默认事务隔离级别RC


    在运行查询语句1的时候:select * from information_schema.tables where table_schema = 'abc';
    状态一直是check permission,opening tables,其他线程需要打开的表在opend tables里面被刷掉的,会显示在opening tables,可能需要小几秒后其他线程基本恢复正常。

    但是如果在运行查询语句2:select count(1) from information_schema.tables where table_schema = 'abc'; 这个时候语句2本身在profiling看长期处于check permission状态,其他线程就会出现被阻塞现象,大部分卡在了opening tables,小部分closing tables。我测试下了,单个表查询的时候check permission大概也就是0.0005s左右的时间,4W个表理论良好状态应该是几十秒的事情。
    但是语句1可能需要5-10分钟,语句2需要5分钟。

    3个问题,请老师抽空看下:
    (1)information_schema.tables的组成方式,是我每次查询的时候从数据字典以及data目录下的文件中实时去读的吗?
    (2)语句1和语句2在运行的时候的过程分别是怎样的,特别是语句2。
    (3)语句2为什么会出现大量阻塞其他事务,其他事务都卡在opening tables的状态。


    PS: 最后根据audit log分析来看,语句实际上是MySQL的一个客户端Toad发起的,当使用Toad的object explorer的界面来查询表,或者设置connection的时候指定的的default schema是大域的时候就会run这个语句:(table_schema改成了abc,其他都是原样)
    SELECT COUNT(1) FROM information_schema.tables WHERE table_schema = 'abc' AND table_type != 'VIEW';


    再次感谢!
    2019-02-13
  • 朱高建
    备库要重启,那么备库的访问业务流量应该会被摘除,那么在备库摘除访问流量之后,重启之前,将备库的内存表引擎改为innodb,再重启备库。如果业务必须使用memory引擎,可以在重启之后改回memory引擎。
    2019-02-12
  • 夹心面包
    我们线上就有一个因为内存表导致的主从同步异常的例子,我的做法是先跳过这个表的同步,然后开发进行改造,取消这张表的作用

    作者回复: 嗯嗯,联系开发改造是对的😆

    2019-02-11
  • 长杰
    内存表一般数据量不大,并且更新不频繁,可以写一个定时任务,定期检测内存表的数据,如果数据不空,就将它持久化到一个innodb同结构的表中,如果为空,就反向将数据写到内存表中,这些操作可设置为不写入binlog。

    作者回复: 因为重启的时候已经执行了delete语句,所以再写入数据的动作也可以保留binlog哈

    2019-02-09
  • HuaMax
    课后题。是不是可以加上创建表的操作,并且是innodb 类型的?

    作者回复: 嗯,如果可以改引擎,也是极好的😄

    2019-02-09
  • 老杨同志
    安装之前学的知识,把主库delete语句的gtid,设置到从库中,就可以跳过这条语句了吧。
    但是主备不一致是不是要也处理一下,将主库的内存表数据备份一下。然后delete数据,重新插入。
    等备件执行者两个语句后,主备应该都有数据了

    作者回复: 题目里说的是 “备库重启”哈

    2019-02-08
收起评论
20
返回
顶部