mysql实战45讲学习笔记五

• 生活家

本篇是这个专栏的收尾，主要讲了mysql临时表，什么时候使用内部临时表等问题，详细见目录。有问题及时在本博客或在个人博客留言.

文章目录

    • 临时表
      • 临时表的应用
      • 临时表和主备复制
      • 思考题
    • 什么时候使用内部临时表
      • union
      • group by
      • group by 优化方法 — 索引
      • group by 优化方法 — 直接排序
      • MySQL什么时候会使用内部临时表
    • insert…select
    • 如何快速的复制一张表
    • 问题解答
    • 附录-部分参数含义

临时表

临时表在使用上有以下几个特点：

  • 建表语法是create temporary table…。
  • 一个临时表只能被创建它的session访问，对其他线程不可见。
  • 临时表可以与普通表同名。
  • sessionA内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。
  • showtables命令不显示临时表。
  • 由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。

临时表的应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。

假设一个大表ht的分库分表字段为f，且f上有索引，另外还有一个索引字段k（非分库分表字段），假如有如下查询语句：

select v from ht where k >= M order by t_modified desc limit 100;

这时候，由于查询条件里面没有用到分区字段f，只能到所有的分区中去查找满足条件的所有行，然后统一做orderby的操作。这种情况下，临时表就是一种方案。

把各个分库拿到的数据，汇总到一个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;

临时表和主备复制

如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到binlog里。也就是说，只在binlog_format=statment/mixed的时候，binlog中才会记录临时表的操作。

这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。所以，这时候我们就需要在主库上再写一个DROP TEMPORARY TABLE传给备库执行。

思考题

为什么不能用 rename 修改临时表的改名。
在实现上，执行 rename table 语句的时候，要求按照“库名 / 表名.frm”的规则去磁盘 找文件，但是临时表在磁盘上的 frm 文件是放在 tmpdir 目录下的，并且文件名的规则 是“#sql{进程 id}_{线程 id}_ 序列号.frm”，因此会报“找不到文件名”的错误。

什么时候使用内部临时表

之前文章中介绍了sort buffer、内存临时表和join buffer。这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助SQL语句的执行的。其中，我们在排序的时候用到了sort buffer，在使用join语句的时候用到了join buffer

那么MySQL 什么时候会使用内部临时表呢？

union

Union它的语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行，因为要判断是否重复行，所以会有使用到临时表。

union改成unionall的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

group by

另外一个常见的使用临时表的例子是 group by,假设有下面的语句：

select id%10 as m, count(*) as c from t1 group by m;

这个语句的逻辑是把表 t1 里的数据，按照 id%10 进行分组统计，并按照 m 的结果排序 后输出。它的 explain 结果如下：

在Extra字段里面，我们可以看到三个信息：
Using index，表示这个语句使用了覆盖索引，选择了索引a，不需要回表；
Using temporary，表示使用了临时表；
Using filesort，表示需要排序。

这个语句的执行流程是这样的：
1.创建内存临时表，表里有两个字段m和c，主键是m；

2.扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

​ 如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);

​ 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；

3.遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

如果你的需求并不需要对结果进行排序，那你可以在 SQL 语句末尾增加 order by null， 也就是改成

select id%10 as m, count(*) as c from t1 group by m order by null;

这样就跳过了最后排序的阶段，直接从临时表中取数据返回。

如果内存可以放得下，全程只使用内存临时表。但是，内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。当内存放不下时，就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB

group by 优化方法 – 索引

不论是使用内存临时表还是磁盘临时表，group by逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个group by语句执行起来就会很慢，我们有什么优化的方法呢

先想一下这个问题：执行 group by 语句为什么需要临时表？

groupby的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的id%100的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。如果扫描过程中可以保证出现的数据是有序的，是不是就简单了呢？InnoDB 的索引，就可以满足这个输入有序的条件。

在MySQL5.7版本支持了generatedcolumn机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列z，然后在z列上创建一个索引（如果是MySQL5.6及之前的版本，你也可以创建普通列和索引，来解决这个问题）。

alter table t1 add column z int generated always as(id % 100), add index(z);

这样，索引 z 上的数据就是有序的了。上面的 group by 语句就可以改成：

select z, count(*) as c from t1 group by z;

这时这个语句的执行不再需要临时表，也不需要排序了。

group by 优化方法 – 直接排序

如果碰上不适合创建索引的场景，我们还是要老老实实做排序的。那么，这时候的 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 内存不够用，就会利用磁盘临时文件辅助排序）；
  • 排序完成后，就得到了一个有序数组。
  • 根据有序数组，得到数组里面的不同值，以及每个值的出现次数。

MySQL什么时候会使用内部临时表

  • 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
  • join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；
  • 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union需要用到唯一索引约束，group by还需要用到另外一个字段来存累积计数。

下面是一些使用的指导原则：

  • 如果对group by语句的结果没有排序要求，要在语句后面加order by null；
  • 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary和Using filesort；
  • 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
  • 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。

insert…select

insert…select是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给select的表里扫描到的记录和间隙加读锁。
而如果insert和select的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。

insert语句如果出现唯一键冲突，会在冲突的唯一值上加共享的next-keylock(S锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

如何快速的复制一张表

当需要拷贝一张表时，如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用insert…select语句即可实现

为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。这时，有两种常用的方法。

问题解答

1.如果用left join的话，左边的表一定是驱动表吗？2.如果两个表的join包含多个条件的等值匹配，是都要写到on里面呢，还是只把一个条件写到on里面，其他条件写到where部分？

下面通过例子回答该问题：

假设有如下两个表：

create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); 
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

表 a 和 b 都有两个字段 f1 和 f2，不同的是表 a 的字段 f1 上有索引。然后，往两个表中都插入了 6 条记录，其中在表 a 和 b 中同时存在的数据有 4 行。

上面问题的第二个其实就是下面两个语句的区别：

select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/
select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/

这两个 left join 语句的语义逻辑并不相同,执行结果分别如下：

语句Q1返回的数据集是6行，表a中即使没有满足匹配条件的记录，查询结果中也会返回一行，并将表b的各个字段值填成NULL。
语句Q2返回的是4行。从逻辑上可以这么理解，最后的两行，由于表b中没有匹配的字段，结果集里面b.f2的值是空，不满足where部分的条件判断，因此不能作为结果集的一部分。

我们看下mysql是怎么执行这两条语句的：

下面是Q1的explain结果：

这个结果符合我们的预期：

驱动表是表 a，被驱动表是表 b；
由于表 b 的 f1 字段上没有索引，所以使用的是 Block Nexted Loop Join（简称 BNL） 算法。

看到 BNL 算法，你就应该知道这条语句的执行流程其实是这样的：

  • 把表 a 的内容读入 join_buffer 中。因为是 select * ，所以字段 f1 和 f2 都被放入 join_buffer 了。
  • 顺序扫描表 b，对于每一行数据，判断 join 条件（也就是 a.f1=b.f1 and a.f2=b.f2) 是否满足，满足条件的记录, 作为结果集的一行返回。如果语句中有 where 子句，需要先判断 where 部分满足条件后，再返回。
  • 表 b 扫描完成后，对于没有被匹配的表 a 的行（在这个例子中就是 (1,1)、(2,2) 这两行），把剩余字段补上 NULL，再放入结果集中。

可以看到，这条语句确实是以表 a 为驱动表，而且从执行效果看，也和使用 straight_join 是一样的。

对应的流程图如下：

下面是Q2的explain结果：

可以看到，这条语句是以表 b 为驱动表的。而如果一条 join 语句的 Extra 字段什么都没写 的话，就表示使用的是 Index Nested-Loop Join（简称 NLJ）算法。

因此，语句 Q2 的执行流程是这样的：顺序扫描表 b，每一行用 b.f1 到表 a 中去查，匹配到记录后判断 a.f2=b.f2 是否满足，满足条件的话就作为结果集的一部分返回。

为什么语句Q1和Q2这两个查询的执行流程会差距这么大呢？其实，这是因为优化器基于Q2这个查询的语义做了优化

在MySQL里，NULL跟任何值执行等值判断和不等值判断的结果，都是NULL。这里包括，selectNULL=NULL的结果，也是返回NULL。

因此，语句Q2里面where a.f2=b.f2就表示，查询结果里面不会包含b.f2是NULL的行，这样这个left join的语义就是“找到这两个表里面，f1、f2对应相同的行。对于表a中存在，而表b中匹配不到的行，就放弃”

这条语句虽然用的是 left join，但是语义跟 join 是一致的,因此，优化器就把这条语句的left join改写成了join，然后因为表a的f1上有索引，就把表b作为驱动表，这样就可以用上NLJ算法。在执行explain之后，你再执行show warnings，就能看到这个改写的结果，如下图所示：

这个例子说明，即使我们在SQL语句中写成left join，执行过程还是有可能不是从左到右连接的。也就是说，使用left join时，左边的表不一定是驱动表。
这样看来，如果需要left join的语义，就不能把被驱动表的字段放在where条件里面做等值判断或不等值判断，必须都写在on里面。

那如果是 join 语句呢？看下如下两条语句：

select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/ 
select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q4*/

再使用一次看 explain 和 show warnings 的方法，看看优化器是怎么做的

可以看到，这两条语句都被改写成：

select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);

也就是说，在这种情况下，join 将判断条件是否全部放在 on 部分就没有区别了

2. Simple Nested Loop Join 的性能问题

虽然BNL算法和Simple Nested Loop Join算法都是要判断M*N次（M和N分别是join的两个表的行数），但是Simple Nested Loop Join算法的每轮判断都要走全表扫描，因此性能上BNL算法执行起来会快很多

BNL算法的执行逻辑是：

  • 首先，将驱动表的数据全部读入内存join_buffer中，这里join_buffer是无序数组；
  • 然后，顺序遍历被驱动表的所有行，每一行数据都跟join_buffer中的数据进行匹配，匹配成功则作为结果集的一部分返回。

Simple Nested Loop Join算法的执行逻辑是：顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。

现在的疑问是：Simple Nested Loop Join 算法，其实也是把数据读到内存里，然后按照匹配条件进行判断，为什么性能差距会这么大呢？

解释这个问题，需要用到MySQL中索引结构和Buffer Pool的相关知识点：

  • 在对被驱动表做全表扫描的时候，如果数据没有在Buffer Pool中，就需要等待这部分数据从磁盘读入；从磁盘读入数据到内存中，会影响正常业务的BufferPool命中率，而且这个算法天然会对被驱动表的数据做多次访问，更容易将这些数据页放到BufferPool的头部；
  • 即使被驱动表数据都在内存中，每次查找“下一个记录的操作”，都是类似指针操作。而join_buffer中是数组，遍历的成本更低。

所以说，BNL 算法的性能会更好

3. distinct 和 group by 的性能

如果只需要去重，不需要执行聚合函数，distinct 和 group by 哪种效率高一些呢？

这个问题可以展开如下：

如果表 t 的字段 a 上没有索引，那么下面这两条语句的性能是不是相同的?：

select a from t group by a order by null; 
select distinct a from t;

首先需要说明的是，这种group by的写法，并不是SQL标准的写法。标准的group by语句，是需要在select部分加一个聚合函数,比如：

select a,count(*) from t group by a order by null;

这条语句的逻辑是：按照字段 a 分组，计算每组的 a 出现的次数。在这个结果里，由于做的是聚合计算，相同的 a 只出现一次。

没有了count(*)以后，也就是不再需要执行“计算总数”的逻辑时，第一条语句的逻辑就变成是：按照字段a做分组，相同的a的值只返回一行。而这就是distinct的语义，所以不需要执行聚合函数时，distinct和groupby这两条语句的语义和执行流程是相同的，因此执行性能也相同。
这两条语句的执行流程是下面这样的。

  • 创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引；
  • 遍历表t，依次取数据插入临时表中：
    • 如果发现唯一键冲突，就跳过；
    • 否则插入成功
  • 遍历完成后，将临时表作为结果集返回给客户端

附录-部分参数含义

show processlist

查看当前数据库的连接状态

wait_timeout

客户端连接到mysql服务器后，客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时，可以用show VARIABLES like "wait_timeout"查看，结果单位是秒

query_cache_type

控制查询缓存的使用，将参数 query_cache_type 设置 成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓 存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样

select SQL_CACHE * from T where id = 10

MySQL 8.0 版本直接将查询缓存的整块功能删掉了

transaction-isolation

查看数据库的隔离级别

innodb_trx

可以在 information_schema 库的 innodb_trx 这个表中查询长事务

innodb_undo_tablespaces

如果使用的是MySQL5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

Flush tables with read lock (FTWRL)

MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

set global readonly=true

也可以让全库进入只读状态,但是还是建议使用上面的FTWRL

一是，在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大，不建议使用。
二是，在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

lock tables … read/write

表锁的语法是lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动 释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别 的线程的读写外，也限定了本线程接下来的操作对象。

innodb_lock_wait_timeout

设置锁等待的超时时间

innodb_deadlock_detect

值为on/off，表示发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

innodb_change_buffer_max_size

change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

long_query_time

设置慢查询界限值，执行超过这个时间的记录为慢查询。

show index from 表名

查看索引基数信息。

innodb_stats_persistent

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent的值来选择：
设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

innodb_io_capacity

要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。
这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。这个值建议设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试，如果用阿里云数据库可以直接在性能监控页查看到。

innodb_max_dirty_pages_pct

脏页比例上限，默认值是75%

innodb_flush_neighbors

一旦一个查询请求需要在执行过程中先flush掉一个脏页时，这个查询就可能要比平时慢了。而MySQL中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

在InnoDB中，innodb_flush_neighbors参数就是用来控制这个行为的，值为1的时候会有上述的“连坐”机制，值为0时表示不找邻居，自己刷自己的。
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机IO。机械硬盘的随机IOPS一般只有几百，相同的逻辑操作减少随机IO就意味着系统性能的大幅度提升。

而如果使用的是SSD这类IOPS比较高的设备的话，我就建议你把innodb_flush_neighbors的值设置成0。因为这时候IOPS往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少SQL语句响应时间。

在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了

innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：

  • 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
  • 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以.ibd为后缀的文件中。
  • 从MySQL5.6.6版本开始，它的默认值就是ON了。

sort_buffer_size

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序

max_length_for_sort_data

MySQL 中专门控制用于排序的行数据的长度的一个参 数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

tmp_table_size

这个配置限制了内存临时表的大小，默认值是 16M。如果 临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

internal_tmp_disk_storage_engine

磁盘临时表使用的引擎默认是 InnoDB，是由参数internal_tmp_disk_storage_engine控制的。

binlog_cache_size

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。
系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

sync_binlog

binlog写入机制中，write（写到binlog文件）和fsync（刷到磁盘）的时机，是由参数sync_binlog控制的：

  • sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
  • sync_binlog=1的时候，表示每次提交事务都会执行fsync；3.sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。
    因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。
    但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

innodb_flush_log_at_trx_commit

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit参 数，它有三种可能取值：

  • 设置为0的时候，表示每次事务提交时都只是把redolog留在redo log buffer中;
  • 设置为1的时候，表示每次事务提交时都将redolog直接持久化到磁盘；
  • 设置为2的时候，表示每次事务提交时都只是把redolog写到page cache。

binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count

如果你想提升binlog组提交的效果，可以通过设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count来实现。

  • binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync;
  • binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。
    这两个条件是或的关系，也就是说只要有一个满足条件就会调用fsync。所以，当binlog_group_commit_sync_delay设置为0的时候，binlog_group_commit_sync_no_delay_count也无效了。

log_slave_updates

把参数log_slave_updates设置为on，表示备库执行relaylog后生成binlog。

slave_parallel_workers

为了让日志在备库上的执行逻辑足够快，降低主备延迟，现在版本的mysql都采用多线程复制的方式。线程的个数由参数slave_parallel_workers决定的。把这个值设置为8~16之间最好（32核物理机的情况），毕竟备库还有可能要提供读查询，不能把CPU都吃光了。

show engine innodb status

执行show engine innodb status，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率

innodb_buffer_pool_size

InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置 成可用物理内存的 60%~80%。

join_buffer_size

join查询语句中join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。

tmp_table_size

内存临时表的大小是有限制的，参数tmp_table_size 就是控制这个内存大小的，默认是 16M

本文地址：https://blog.csdn.net/u010408502/article/details/108589643

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐