MySQL实战45讲-笔记

开篇词 | 这一次，让我们一起来搞懂MySQL

01 | 基础架构：一条SQL查询语句是如何执行的？

mysql01

02 | 日志系统：一条SQL更新语句是如何执行的？

重要的日志模块：redo log

InnoDB 的 redo log 是固定大小的

从头开始写，写到末尾就又回到开头循环写

重要的日志模块：binlog

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）

redo log 与binlog

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

执行流程

update T set c=c+1 where ID=2;

  1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

mysql02

03 | 事务隔离：为什么你改了我还看不见？

在 MySQL 中，事务支持是在引擎层实现的。

尽量不要使用长事务

建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
串行：我的事务尚未提交，别人就别想改数据。
这4种隔离级别，并行性能依次降低，安全性依次提高。

04 | 深入浅出索引（上）

每一个索引在 InnoDB 里面对应一棵 B+ 树。

基于主键索引和普通索引的查询有什么区别？

  • 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
  • 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

哪些场景下应该使用自增主键，而哪些场景下不应该。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？

  1. 只有一个索引；
  2. 该索引必须是唯一索引。

05 | 深入浅出索引（下）

索引执行流程

select * from T where k between 3 and 5

表数据

insert into T values
(100,1, 'aa'),
(200,2,'bb'),
(300,3,'cc'),
(500,5,'ee'),
(600,6,'ff'),
(700,7,'gg');

我们一起来看看这条 SQL 查询语句的执行流程：

  1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
  2. 再到 ID 索引树查到 ID=300 对应的 R3；
  3. 在 k 索引树取下一个值 k=5，取得 ID=500；
  4. 再回到 ID 索引树查到 ID=500 对应的 R4；
  5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

全局锁

全局锁的典型使用场景是，做全库逻辑备份。

single-transaction

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

表级锁

表锁的语法是 lock tables … read/write

另一类表级的锁是 MDL（metadata lock)。

如何安全地给小表加字段？

比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

ALTER TABLE tbl_name WAIT N add column ...

07 | 行锁功过：怎么减少行锁对性能的影响？

死锁和死锁检测

发生死锁时, 有两种策略:

  • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。默认值是 50s
  • 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。默认值本身就是 on

主动死锁检测的缺点

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

行级锁的处理

一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。

另一个思路是控制并发度。

你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。

08 | 事务到底是隔离的还是不隔离的？

09 | 普通索引和唯一索引，应该怎么选择？

普通索引和唯一索引应该怎么选择

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引。

10 | MySQL为什么有时候会选错索引？

11 | 怎么给字符串字段加索引？

alter table SUser add index index2(email(6));

创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

遇到前缀的区分度不够好的情况时，我们要怎么办呢？

第一种方式是使用倒序存储。

第二种方式是使用 hash 字段。

小结

  1. 直接创建完整索引，这样可能比较占用空间；
  2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
  3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

12 | 为什么我的MySQL会“抖”一下？

redo log 写满了，要 flush 脏页

内存不够用了，要先将脏页写到磁盘

innodb_io_capacity 过小

13 | 为什么表数据删掉一半，表文件大小不变？

将 innodb_file_per_table 设置为 ON，是推荐做法，我们接下来的讨论都是基于这个设置展开的。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。重建表

重建表

如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。我跟你介绍了重建表的两种实现方式，Online DDL 的方式是可以考虑在业务低峰期使用的，而 MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的，这个你需要特别小心。

14 | count(*)这么慢，我该怎么办？

count(*) 的实现方式

我们只能自己计数

count(*)、count(主键 id)、count(字段) 和 count(1) 的区别

count 函数的参数不是 NULL，累计值就加 1，否则不加

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(1) 执行得要比 count(主键 id) 快

但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

count(字段)<count(主键 id)<count(1)≈count(*)

16 | “order by”是怎么工作的？

全字段排序

select city,name,age from t where city='杭州' order by name limit 1000  ;

mysql03

执行流程:

  1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
  2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
  3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
  7. 按照排序结果取前 1000 行返回给客户端。

执行流程的示意图如下所示

mysql04

缺点

MySQL 做排序是一个成本比较高的操作.

优化

并不是所有的 order by 语句，都需要排序操作的

我们可以在这个市民表上创建一个 city 和 name 的联合索引，对应的 SQL 语句是：

alter table t add index city_user(city, name);

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。

这样整个查询过程的流程就变成了：

  1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
  2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
  3. 从索引 (city,name) 取下一个记录主键 id；
  4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

17 | 如何正确地显示随机消息？

需求: 从一个单词表中随机选出三个单词

对语句 : select word from words order by rand() limit 3; 的优化

这条语句的执行流程是这样的：

  1. 创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
  2. 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
  3. 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
  4. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
  5. 从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
  6. 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
  7. 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

随机排序方法

随机算法1:

  1. 取得这个表的主键 id 的最大值 M 和最小值 N;
  2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
  3. 取不小于 X 的第一个 ID 的行。

缺点: 如果这四行的 id 分别是 1、2、40000、40001 呢？这个算法基本就能当 bug 来看待了

随机算法 2:

  1. 取得整个表的行数，并记为 C。
  2. 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
  3. 再用 limit Y,1 取得一行。

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

案例一：条件字段函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

案例二：隐式类型转换

数据类型转换，就需要走全索引扫描

19 | 为什么我只查一行的语句，也执行这么慢？

锁表了

20 | 幻读是什么，幻读有什么问题？

21 | 为什么我只改一行的语句，锁这么多？

22 | MySQL有哪些“饮鸩止渴”提高性能的方法？

短连接风暴

第一种方法：先处理掉那些占着连接但是不工作的线程

设置 wait_timeout 参数表示的是，一个线程空闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。

第二种方法：减少连接过程的消耗。

慢查询性能问题

第一种可能是，索引没有设计好。

假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

  1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
  2. 执行主备切换；
  3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

第二种可能是，语句没写好

QPS 突增问题

如果你做业务开发的话，要知道，连接异常断开是常有的事，你的代码里要有正确地重连并重试的机制。

23 | MySQL是怎么保证数据不丢的？

binlog 的写入机制

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

  1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
  2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
  3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

redo log 的写入机制

mysql05

这三种状态分别是：

  1. 存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
  2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
  3. 持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。

日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

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

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

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

24 | MySQL是怎么保证主备一致的？

MySQL 主备的基本原理

依然建议你把节点 B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：

  1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
  2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
  3. 可以用 readonly 状态，来判断节点的角色。

binlog 的三种格式对比

设置 binlog_format=statement

  1. statement statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的
  2. row binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。
  3. mixed mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

优缺点

基于上面的信息，我们来讨论一个问题：为什么会有 mixed 这种 binlog 格式的存在场景？推论过程是这样的：

  • 因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
  • 但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
  • 所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

25 | MySQL是怎么保证高可用的？

主备延迟

  1. 主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
  2. 之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;
  3. 备库 B 执行完成这个事务，我们把这个时刻记为 T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

主备延迟的来源

有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。

读,分析语句都在备库上运行 解决: 一主多从

大事务, 一次性地用 delete 语句删除太多数据

可靠性优先策略

主备切换的过程

  1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
  2. 把主库 A 改成只读状态，即把 readonly 设置为 true；
  3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
  4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
  5. 把业务请求切到备库 B。

可用性优先策略

26 | 备库为什么会延迟好几个小时？

为什么要有多线程复制呢？这是因为单线程复制的能力全面低于多线程复制，对于更新压力较大的主库，备库是可能一直追不上主库的。从现象上看就是，备库上 seconds_behind_master 的值越来越大。

27 | 主库出问题了，从库怎么办？

在一主多从架构下，主库故障后的主备切换问题。

如果你使用的 MySQL 版本支持 GTID 的话，我都建议你尽量使用 GTID 模式来做一主多从的切换。

28 | 读写分离有哪些坑？

读写分离的主要目标就是分摊主库的压力。

坑: 过期读

解决方案:

  • 强制走主库方案；
  • sleep 方案；
  • 判断主备无延迟方案；
  • 配合 semi-sync 方案；
  • 等主库位点方案；
  • 等 GTID 方案。

29 | 如何判断一个数据库是不是出问题了？

select 1 判断

select 1 成功返回，只能说明这个库的进程还在，并不能说明主库没问题。

问题: 并发线程不够时, select 1 是能执行成功的，但是查询表 t 的语句会被堵住

查表判断

一般的做法是，在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行;

问题: 空间满了以后，这种方法又会变得不好使。

更新判断

为了让主备之间的更新不产生冲突，我们可以在 mysql.health_check 表上存入多行数据，并用 A、B 的 server_id 做主键。

问题: 日志盘的 IO 利用率已经是 100% 的场景。这时候，整个系统响应非常慢

内部统计

内部每一次 IO 请求的时间

MySQL 5.6 版本以后提供的 performance_schema 库，就在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。

31 | 误删数据后除了跑路，还能怎么办？

误删行

具体恢复数据时，对单个事务做如下处理：

  1. 对于 insert 语句，对应的 binlog event 类型是 Write_rows event，把它改成 Delete_rows event 即可；
  2. 同理，对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event；
  3. 而如果是 Update_rows 的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

需要说明的是，我不建议你直接在主库上执行这些操作。

恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。

误删库 / 表

用 truncate table 或者 drop table 命令,即使我们配置了 binlog_format=row，执行这三个命令时，记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句，这些信息是恢复不出数据的。

这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。

恢复步骤:

  1. 取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
  2. 用备份恢复出一个临时库；
  3. 从日志备份里面，取出凌晨 0 点之后的日志；
  4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

延迟复制备库

如果有非常核心的业务，不允许太长的恢复时间，我们可以考虑搭建延迟复制的备库。这个功能是 MySQL 5.6 版本引入的。

预防误删库 / 表的方法

第一条建议是，账号分离。这样做的目的是，避免写错命令。比如：

  • 我们只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。
  • 即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。

第二条建议是，制定操作规范。这样做的目的，是避免写错要删除的表名。比如：

  • 在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
  • 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。

rm 删除数据

对于一个有高可用机制的 MySQL 集群来说，最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。

32 | 为什么还有kill不掉的语句？

在 MySQL 中有两个 kill 命令：

一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句；

一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

这个例子是 kill 无效的第一类情况，即：线程没有执行到判断线程状态的逻辑。跟这种情况相同的，还有由于 IO 压力过大，读写 IO 的函数一直无法返回，导致不能及时判断线程的状态。

另一类情况是，终止逻辑耗时较长。这时候，从 show processlist 结果上看也是 Command=Killed，需要等到终止逻辑完成，语句才算真正完成

33 | 我查这么多数据，会不会把数据库内存打爆？

取数据和发数据的流程是这样的：

  1. 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
  2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
  3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
  4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

MySQL 是“边读边发的”

34 | 到底可不可以使用join？

应该让小表来做驱动表

35 | join语句怎么优化？

Index Nested-Loop Join（NLJ）和 Block Nested-Loop Join（BNL）的优化方法。

在这些优化方法中：

  1. BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
  2. BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
  3. 基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
  4. MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

36 | 为什么临时表可以重名？

临时表的特性

  1. 建表语法是 create temporary table …。
  2. 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。
  3. 临时表可以与普通表同名。
  4. session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
  5. show tables 命令不显示临时表。
  6. 临时表也存储在磁盘

37 | 什么时候会使用内部临时表？

group by 的几种实现算法，从中可以总结一些使用的指导原则：

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

38 | 都说InnoDB好，那还要不要使用Memory引擎？

两个引擎的一些典型不同：

  1. InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
  2. 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
  3. 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
  4. InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
  5. InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。

在生产上，我不建议你使用普通内存表。

39 | 自增主键为什么不是连续的？

40 | insert语句的锁为什么这么多？

41 | 怎么最快地复制一张表？

42 | grant之后要跟着flush privileges吗？

43 | 要不要使用分区表？

45 | 自增id用完怎么办？

posted @ 2021-06-02 18:10  宁君  阅读(789)  评论(0)  编辑  收藏  举报