MySQL学习笔记2.0

关注

原创最新推荐文章于 2026-03-14 01:51:33 发布 703 阅读 AI 写同款· GEO 优化 ›

【漏洞修复】Diffie-Hellman Key Agreement Protocol 资源管理错误漏洞（CVE-2002-20001） Diffie-Hellman Key Agreement Protocol 存在安全漏洞，远程攻击者可以发送实际上不是公钥的任意数字，并触发服务器端DHE模幂计算。阅读详情

1.主备延迟

来源：

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

由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略：可靠性优先策略和可用性优先策略。

2.读写分离

客户端直连方案，因为少了一层 proxy 转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。
带 proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy 也需要有高可用架构。因此，带 proxy 架构的整体就相对比较复杂。

3.数据恢复

不同情况：
1.使用 delete 语句误删数据行；

Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。
具体恢复数据时，对单个事务做如下处理：
对于 insert 语句，对应的 binlog event 类型是 Write_rows event，把它改成 Delete_rows event 即可；
对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event；
如果是 Update_rows 的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

2.使用 drop table 或者 truncate table 语句误删数据表；
3.使用 drop database 语句误删数据库；
需要使用全量备份，加增量日志的方式。这个方案要求线上有定期的全量备份，并且实时备份 binlog。

4.使用 rm 命令误删整个 MySQL 实例。
系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。

4.大数据量查询

由于 MySQL 采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在 server 端保存完整的结果集。所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。
而对于 InnoDB 引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于 InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。

5.连接

在join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。如果可以使用被驱动表的索引，join 语句还是有其优势的；不能使用被驱动表的索引，这样的语句就尽量不要使用join；在使用 join 的时候，应该让小表做驱动表。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

6.临时表

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

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

7.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 的结果。

8.Memory 引擎

InnoDB 和 Memory 引擎的数据组织方式是不同的：

InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

从中可以看出，这两个引擎的一些典型不同：

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

9.自增值不连续

在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

如果插入数据时 id 字段指定为 null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。

假设，某次要插入的值是 X，当前的自增值是 Y。如果 X插入的值是 X，当前的自增值是 Y。如果X<Y，那么这个表的自增值不变；如果X≥Y，就需要把当前自增值修改为新的自增值。

唯一键冲突是导致自增主键 id 不连续的第一种原因。事务回滚就是第二种原因。

10.特殊情况下的insert语句

insert … select 是很常见的在两个表之间拷贝数据的方法。在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，需要引入用户临时表来做优化。insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

11.复制表

物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：必须是全表拷贝，不能只拷贝部分数据；需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。
用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

12.分区表

分区表跟用户分表比起来，有两个绕不开的问题：一个是第一次访问的时候需要访问所有分区，另一个是共用 MDL 锁。因此，如果要使用分区表，就不要创建太多的分区。
有两个问题需要注意：分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时的 drop 掉。至于分区表的其他问题，比如查询需要跨多个分区取数据，查询性能就会比较慢，基本上就不是分区表本身的问题，而是数据量的问题或者说是使用方式的问题了。

收录于