mysql题目把_mysql基础~经典题目

一 mysql双写( 目标机械盘)

1 double write是什么

1 内存中开启的一个连续空间

2 共享表空间 ibdata中的一个缓存区间

3 大小2M,由2个段构成(每个段64个页 一共128个页)

目的 是为了避免页损坏/页中断后无法恢复的情况,实现原子写特性

2 描述下double write的具体过程

1 每次脏也刷新 先通过函数拷贝到这个连续空间,然后通过这个空间刷新

2 先把doublewrite buffer的页数据写入到共享表空间ibdata1磁盘上(顺序写刷新页,数据文件的缓存,最多一次性刷新64个页(1个段))

3 再把double write buffer的页数据写入各个表空间(每次以512字节写入,防止页损坏，随机写)

3 为什么mysql需要双写

mysql数据页是16K 而linux系统的数据页是4K

16K的数据，写入4K 时，发生了系统断电/os crash ，只有一部分写是成功的，这种情况下就是 partial page write 问题。(页损坏,页中断) 重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

4 双写带来的问题

1 会导致系统有更多的fsync操作, 而硬盘的fsync性能是很慢的, 所以它会降低mysql的整体性能

5 为什么日志不需要双写

1　因为redolog写入的单位就是512字节，也就是磁盘IO的最小单位，所以无所谓数据损坏

6 为什么SSD不需要开启双写

因为SSD能保证16KB数据的原子性写入。

二 MDL锁

1 MDL锁简介

MDL锁是server层的锁,目的是保护保护表的元数据,防止表的元数据在事务活动期间被更改,导致问题 MDL锁是针对语句级别的,并非事务本身

2 MDL 锁分类

SRO锁(MDL_SHARED_READ_ONLY) -> 对应操作 lock table read 说明

可阻止所有尝试更新表内容数据和元数据，持有这种锁的连接可以读取表元数据并读取表数据。

RW锁 (MDL_SHARED_WRITE)-> 对应操作 DML

说明 当执行DML语句会申请,优先级低于SRO

RA锁(MDL_SHARED_READ)->对应操作 SELECT

说明 读取表的数据/元数据锁.,而且操作类型不能是DDL和lock table

MDL_SHARED_UPGRADABLE-》对应操作 DDL

说明共享元数据锁，用于需要从表中读取数据的情况 并阻止对其进行所有并发修改(针对数据和元数据)。

3 MDL 申请对象

1 SELECT 申请意向( db级别EXCLUSIVE锁) RA锁 释放意向(db级别EXCLUSIVE锁) mdl-shard-read锁

2 DML操作 申请意向(db级别EXCLUSIVE锁) RW锁 释放意向(db级别EXCLUSIVE锁) mdl-shard-write锁

4 全局性操作 申请 SRO锁,全局锁定

5 copy加锁 MDL_SHARED_UPGRADABLE(x)->(copy data)MDL_SHARED_NO_WRITE->(rename table)MDL_EXCLUSIVE

in-place加锁 MDL_SHARED_UPGRADABLE-(x)>(rename table)MDL_EXCLUSIVE

因为没有了第二阶段,所以不会阻塞事务和查询  ONLINE X -S(build阶段降级)-X(build完会提升) COPY 一直锁不会进行降级操作

6 MDL_SHARED_UPGRADABLE锁,MDL_SHARED_UPGRADABLE锁之间是互斥的,所以两个DDL是没法并行操作的,

7 为什么发生DDL阻塞

一旦有操作无法获取应有的MDL锁,就会陷入等待状态,导致后续都会变成串行化操作(实际生产环境常见于大量查询操作)

事务1 查询 获取 SHARED_READ 事务2 为DDL操作,需要获取(MDL_EXCLUSIVE)，这两者是互斥的,事务1 不释放 ,DDL会一直处于等待状态,导致后续串行化

8 MDL是基于整个事务的,一旦事务没有提交或者长期处于查询的查询语句 亦或者其他DDL,都有可能造成DDL等待

三 ahi

0 目的: 当对某个页面访问次数满足一定条件会将页面地址存于Hash表，下次查询可以非常快速的找到页面不需要Btree去查。

1 AHI的具体结构是什么

满足 AHI 缓存建立的条件后，记录 hash value(索引的前缀索引hash值) -> page block(热点数据页) 之间的对应关系

2 AHI构建的条件

关键词 AHI成功次数 前缀索引信息

在数据页 block 上使用 AHI 成功的次数大于此数据页上用户记录的 1/16 且当前前缀索引的条件下使用 AHI 成功的次数大于 100 时， 如果此数据页使用 AHI 潜在成功的次数大于 2 倍该数据页上的用户记录或者当前推荐的前缀索引信息发生了变化的时，则需要为数据页构造 AHI 缓存信息

3 AHI 注意点

所以在打开 AHI 的系统中不建议经常变换查询条件,因为每次会进行判断,如果查询条件判断没有变化,就会增加潜在利用AHI的次数,反之,则会清空状态,重新进行统计

四二阶段提交

1 二阶段提交的意义

是为了解决redo+binlog的数据一致性问题,在没有二阶段提交的时候,mysql主从同步可能发现数据异常

2 二阶段提交的过程r

背景: xid redo 和undo已经生成,sql已执行成t功

具体流程: 事务状态设置为prepare状态,redo log写入文件并进行刷盘  commit阶段 binlog 写入文件并进行刷盘 事务在存储引擎层进行提交

3   在这里有人可能会有一个疑问,如果在flush后传递到从库,这时候主库 down掉 是不是就意味着从库比主库多,肯定不会,因为这与innodb恢复机制有关,innodb判断binlog完整时,会选择重做事务.所以,当binlog落盘时,我们实际可以认为这个事务已经完成了,如果 binlog的事务中含有XID,算是一个完整的事务

4 补充

flush操作需要经历系统缓存,但是sync操作不用,直接到磁盘

六 lsn与checkpoint

一  通过show engine innodb status观察 大概有以下几项,从上向下是LSN是递减的

1 当前系统LSN最大值，存在于redo buffer种

2 落入redo文件的LSN号 存在于redo文件种

3 脏页刷盘的LSN号  存在于磁盘数据文件种,存在于data page

4 当前已经写入Checkpoint的LSN

二 LSN与页

1 页的头部和尾部都会记录一次LSN,头部代表第一次修改LSN,底部记录最新更改的LSN,FLUSH链表是根据脏页第一次修改由小到大排序,当出现一个脏页会进行判定,如果不是第一次修改跳过,防止重复加入

2 下次checkpoint会直接记录flush链表最小的LSN号作为全局一致性位点

三 checkpoint

Innodb实现了Fuzzy Checkpoint的机制，每次取到最老的脏页，然后确保此脏页对应的LSN之前的LSN都已经写入日志文件，再将此脏页的LSN作为Checkpoint点记录到日志文件，意思就是“此LSN之前的LSN对应的日志和数据都已经写入磁盘文件”。恢复数据文件的时候，Innodb扫描日志文件，当发现LSN小于Checkpoint对应的LSN，就认为恢复已经完成。

Checkpoint写入的位置在日志文件开头固定的偏移量处，即每次写Checkpoint都覆盖之前的Checkpoint信息。

四 可能触发checkpoint的操作

1 关闭mysql进程

2 master thread周期性的生成checkpoint点

3 redo log达到阈值,不可覆盖

4 buffer_pool中脏页比达到pct阈值

5 buffer_pool中空闲页太少

五 checkpoint的优势是加快故障恢复的速度,当故障恢复时,只需要重做checkpoint点之前的redo日志即可到达数据的一致性

七 隔离级别问题

0 针对对象

都是同一事务内的多次查询

1 幻读和不可重复性读定义

幻读 针对的是insert/delete操作,两次(或以上)查询同一个范围内的数据,发现数据的数量不同

不可重复性读 针对的是update操作,一个事务读到另一个事务修改后并提交的数据

2 如何解决

幻读 利用的是innodb gap lock 锁 锁定范围解决,防止插入

不可重复读 利用的是MVCC机制(在事务开始时会形成一个快照),保证每次相同查询都查询的都是历史版本

3 MVCC实现机制

基本概念:

1 read view 事务开始时会对现有的活动事务进行一次快照,被称为read view,每次事务结束时才能被销毁

2 行版本号 innodb的每行都有隐藏的列,我们需要记住trx_id(活动事务号),代表最新的事务ID,指向undo历史版本的指针

具体过程

1 即MVCC一致性度是在事务启动时，获取当前活跃事务列表。

2 如果事务ID小于read view的最小事务ID,则可以直接读取

3 如果事务ID 大于等于read view的最大事务ID+1,则去undo中去寻找需要的快照版本

4 如果事务ID 介于这两者之间,则进行具体判断,如果存在与活动事务列表中,则需要读取undo的历史版本快照

4 一些总结

1 RC情况下同样可能造成死锁,只不过几率降低了，同样,RC也会存在gap lock(插入意向锁,唯一值检测)

八 SSD的mysql优化配置

1 innodb_io_capacity

innodb_io_capacity_max参数默认是200，单位是页。该参数设置的大小取决于硬盘的IOPS,我们通过fio工具压测SSD然后设置线上的 innodb_io_capacity_max =4000 常规值是最大值70%

2 linux 调度算法

SSD调整为noop Noop对所有的I/O请求都用FIFO队列(先进先出队列)形式处理，默认认为I/O不会存在性能问

3 关闭双写, SSD能实现原子写,就不再需要双写了

4 关闭邻页刷新

何为邻页刷新 MySQL 中的一个机制，当刷新一个脏页时，InnoDB会检测该页所在区(extent)的所有页，如果有其他页也是脏页，也会进行刷新； 用这种方式可以在机械硬盘下减少随机IO操作. SSD下建议关闭这个参数,提高性能,减少性能消耗,分散写操作

九 update具体流程

1 认证流程

1 登录权限认证 2 执行权限认证 3 语法合法性解析 4 执行计划树生成

2 交互流程

1 读取需要的数据页

2 修改数据页并生成undo_log

3 进入二阶段提交阶段

4 事务返回程序提交

3 后台线程执行

1 刷新脏页,进行落盘

2 清除可回收的undo空间

十  xtrabackup的一些问题

1 具体过程

1 备份开始 同时开启两个进程 一个进程进行copy redo log 另一个进程进行copy innodb文件

2 innodb文件备份完成后,执行命令flush table with read lock

1 开始拷贝非innodb表

2 将内存的redo缓存刷新到文件中并进行拷贝

3 同时获得一致性位点

4 解开全局一致性锁

5 停止拷贝redo log,备份完成

2 具体原理

redo的拷贝从最新的一次checkpoint点开始拷贝,具体记录文件为xtrabackup_checkpoints

3 备份参数

1 对于慢查询

kill语句类型  long_query_type

kill语句阈值  long_query_time_out

2 对于MDL语句

锁定全局DDL语句阻塞 --lock-ddl

3 对于DML语句

--ftwrl-wait-timeout

4  和innobackupex的关系

innobackupex是一个封装了xtrabackup的脚本，能同时处理innodb和myisam，但在处理myisam时需要加一个读锁

5 增量备份

innobackupex只能针对innodb做增量备份,对于myisam还是全量备份.原理就是根据上次全量备份的LSN号(xtrabackup_checkpoints文件),然后对于所有大于这个LSN号的数据页进行备份,文件结尾为.dela

十一 innodb故障恢复

1 一致性恢复

redo前滚 先利用redo做一致性恢复,,针对目标是数据页,通过redo lsn和 data page lsn的比较,当data page lsn小于redo lsn时执行前滚操作,如果data page lsn大于redo lsn lsn 不做任何处理 可以理解成就是事务提交后没来得及刷新到磁盘的脏页,通过redo重做磁盘的原始页达到效果

2 undo建立起恢复事务链表,并标记事务状态

3 再利用undo对事务做回滚或者重做 针对的是事务

对事务的判定是通过redo+binlog共同完成

1 如果redo事务是完整的,则进行重做

2 如果redo中事务是不完整(处于prepare状态)就必须通过server层进行判定binlog,通过获取redo的prepare事务列表和最后一个binlog文件的xid事务列表进行结合,

1 binlog事务完整 进行事务重应用

2 binlog不完整 回滚未提交的事务

十二 BUFF_POOL链表管理

1 定义

free_list  管理未被使用的空闲页,如果lru列表需要空闲页,free里有,就分配给lru,并从链表中删除(空闲页)

lru_list   管理缓冲池中页的使用(大部分使用的数据页页)

flu_list   用来管理将页刷新回磁盘(脏页)

2 关系

1 free list需要保留一定量的空页,当free_list不足到达阈值

1 针对LRU链表首先会优先回收掉非脏页,顺序是从LRU尾部扫描到头部

2 如果不满足会进一步触发刷脏操作,针对脏页,顺序是从LRU尾部扫描到头部,但是不会跳过赞一个

3 当发现可回收的页时会进行回收病加入FREE_LIST列表中

2 flu_list 存储的脏页一定存在于lru_list,但是反之则不一定(LRU 和FLU中 都有脏页)

3 补充

buffer_pool中还有其他一些页比如锁信息,插入缓冲等就不是lru链表进行管理

4  LRU机制

当页到内存时,会先在LRU链表的midpoint的old区保留,当存留时间超过设置的保留时间时,才会从old区移动到yang区,整个过程被称为make page yang,这样做的优势在于能防止短时间内的一次性大量查询的页挤掉LRU其他的高频页

5 预读机制

当一个区中有连续56个页面(56为默认值)被加载到BufferPool中，会将这个区中的所有页面都加载到BufferPool中。其实挺合理的，毕竟一个区最多才64个页 ，此外还有个随机预读机制,不过是关闭的

6 主要页构成

数据页 索引页 undo页 redo页

十三 mysql后台线程

1 master_thread

1 每秒

1 重做日志缓冲刷新到磁盘(总是) 其他都是可能

2 每十秒

1 产生一个ckpt点 2 嗲用purge_cleaner 3 重做日志刷新到磁盘 4 刷新10个(如果小于脏页比)或者100个脏页到磁盘(如果大于脏页比)(调用page cleaner)

2 异步IO线程 (read thread,write thread,insert buffer thread,log thread)

3 purge线程 1 清理undo空间 2 异步删除标记的记录

4 page cleaner -脏页清理

5 注意 master线程两个循环周期进行脏页的刷新和undo段的回收 调用page cleaner 和 purge cleaner和异步IO

十四 binlog事件

1 ROW_EVENT(事务)

WRITE_ROWS_EVENT/DELETE_ROWS_EVENT/UPDATE_ROWS_EVENT/QUERY_LOG_EVENT(ddl)

2  库表事件

TABLE_MAP_EVENT(表描述信息)

3 全局事件

GTID_LOG_EVENT/PREVIOUS_GTIDS_LOG_EVENT/XID_EVENT

4 标准事件

GTID_LOG_EVENT->TABLE_MAP_EVENT->DELETE_EVENT->XID_EVENT

5 大事务产生大量的event

MySQL一个dml可能产生多个rows event，但是rows event的大小由参数：binlog-row-event-max-size(默认1024)超过了就不会再显示原始语句 可以看到flags: STMT_END_F

6 binlog解析问题

1 原始的binlog文件都是行事件  DBA通过 mysqlbinlog  --base64-output，可以控制输出语句输出base64编码的BINLOG语句;decode-rows：选项将把基于行的事件解码成一个SQL语句 统一用-v表示  解析出的binlog是注释的

2 如果想要恢复数据 需要mysqlbinlog+原始的binlog文件(不加-v)才可以 mysqlbinlog a.log < mysql -uroot

十五 B+树索引

1 名称 平衡多叉树

2 特点

1 聚簇索引的叶子节点存储整行数据，非 聚簇索引 存储着对应的主键值

2 通过双向链表将叶子节点数据(数据页)进行连接

3 叶子节点内部数据(具体数据行)是通过单向链表进行连接的

4 从根节点出发 根据存储在非叶子节点的k-v信息定位到具体的数据页,然后进行查找定位到具体的行,如果存在相邻的行,则可以直接通过双向链表读取数据页(一切操作单位都是页)

5 k-v信息指的是(具体的主键(k)对应的数据页指针(v))

6B+树只能定位到具体的数据页,在数据页内部还要进行二分查找

3 选择优势

MySQL 默认的存储引擎选择 B+ 树而不是哈希或者 B 树的原因： 关键点

1 对比哈希 优势在于排序,范围查询和多条件查询 hash本身具有很多局限性(1不支持范围查找  2  并不是按照索引顺序存储的，无法用于排序 3 哈希索引不支持多列联合索引的最左匹配规则 4 不能使用覆盖索引)

2 对比 B树 优势在于连续查询数据时随机IO的减少,叶子节点本身存在双向链表,可以通过这种方式访问

十六 GTID相关

1 GTID名称是全局性唯一事务ID 由uuid+分配的事务ID号组成 每个事务在执行之前都会被分配一个GTID,从库如果判断这个GTID已经在被执行的集合内,就不会再进行重复执行

2 GTID的好处在于取代了传统复制的位点,当进行配置连接主库的change信息时,不必再寻找位点,直接设置 auto_poistion即可

3 GTID的限制是不能进行某些操作 1create table select(因为需要分配两个gtid,但是实际只给了一个)2 create/drop tempory 3一个sql同时操作innodb引擎和myisam引擎：

4 GTID 在从库执行relay-log的时候会自动替换成自己的GTID(uuid) 所以从库开启binlog记录的是自己的信息

5 GTID 在binlog的文件头部会记录以前执行过的gtid集合,如果包含就会跳过,从后向前扫描binlog，能大大节省时间

6 GTID 运维

GTID 跳过异常:1 设置下一个GTID号 2 提交一个空事务 3 set@@session.gtid_next=automatic;

GTID 搭建从库: set global @@gtid_purged='' start slave

7  匿名事务复制错误 ANONYMOUS: 事务没有GTID, 只使用 file and position 作为标识,匿名事务发生了就会有问题 5.7以上能在线调整GTID模式

7   GTID选项

--set-gtid-purged 设置为ON(默认) 会 增加SET @@GLOBAL.GTID_PURGED=''

SET @@SESSION.SQL_LOG_BIN= 0

分析 我们会发现,导入 1 不产生binlog 2 会自动执行  set purge事务 场景 适用于 从库重新搭建

--set-gtid-pirged设置为OFF,就会产生binlog进行同步,适用于 1 非GTID环境  2 主库重新导入

8  变量

1 RESET MASTER  清除的是 GTID_EXECUTED( 包含 GTID_PURGED)  2 GTID_PURGED  binlog已经被删除的GTID集合 3 只有当GTID_EXECUTED为空的时候才能设置GTID_PURGED,这也是为啥搭建从库之前需要先执行reset master清除GTID_EXECUTED

十七 增强半同步复制

1 5.6与5.7的最大不同

对于其他客户端可见性的问题(after之后做什么操作)

5.6 after_commit 在master等待ack的时候master已经在引擎层提交,对于其他的客户端访问可见(认为事务已经提交).如果这时候master down掉,binlog还没传到从库,那么切换到新主上,对于其他客户端访问最新数据时没有的(因为事务并没传递到从库),发生了幻读

5.7 after_sync 直到master收到ack确认信息然后再引擎层提交,才会对其他的客户端访问可见,即便master down掉,其他客户端也不会发生幻读,而且还有利于binlog的组提交

2 半同步复制的一些问题

需要设置sync_binlog=1,是为了防止出现主库还没落盘时传递到从库以后,这时候主库崩溃,出现从库比主库多的情况.当设置为1时,只有在主库binlog落盘的时候才会dump binlog到从库

十八 双一特性的区别

1  innodb_flush_log_at_trx_commit

0 代表mysql每秒刷新到os cache,然后触发os cache刷新到硬盘,mysqld进程或者linux的崩溃会导致上一秒钟所有事务数据的丢失。

1 代表mysql每个事务都会写入os cache刷新到硬盘, 当mysql/linux出现故障时最多损失一个事务(依赖mysql刷新函数,不依赖于系统刷新函数)

2 代表mysql每个事务都会刷新到os cache,然后os cache每秒刷新到硬盘 当mysql挂掉后 最多损失一个事务，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失

2 sync_log

=1 代表有1个事务就会触发刷新到磁盘

=N  代表有N个事务触发一起刷新到磁盘

=0  代表由系统周期性进行刷新到磁盘

3 补充

innodb_redo flush 会经过OS CACHE   binlog sync 直接刷新到磁盘

十九  并行复制

0 定义

主库在同一时间戳内进入prepare状态的事务在从库可以并行进行回放,加快复制速度,减少复制延时.

改进:当同一组事务有一个完成后,会立刻进行下一组事务的判断,如果没有冲突,则会应用,不会再等待整个一组事务全部完成

1 缺陷

1 不能避免因为DDL和大事务造成的主从延时问题

2 如果主库是单线程执行,那么从库也是单线程执行,直到5.7高版本出现的基于writeset的并行复制才解决

2 关键点

last_committed 表示获取上次事务提交的逻辑时间编号

seq_number 表示 事务commit的时候获取的逻辑时间编号(是一个连续增长的数字,每个last_commited等与上一个事务的seq_nummer)

在非writeset的并行复制基础上,只有相同的last_committed的情况下才能实现从库的并行回放

3 线程

1 线程1 负责协调分发 线程2(worker)负责执行,我们设置的那个参数就是负责执行参数的数量

4 相应点 可以通过调整主库的事务提交参数增加整个MTS的并发读

4 变量

binlog_transaction_dependency_tracking是设在主库，可以取commit_order(常规方式),writeset,writeset_session，主要是控制binlog文件中last_committed的计算方式：

slave_preserve_commit_order是设在从库，控制从库并行reply时事务提交的顺序,目的避免gap现象 设置成这样,执行的时候还是并发执行,但是提交的时候和以前一样,依然是串行提交,只能提升一定程度的速度

gap原因 :这种‘gap’可能是由于在并行回放的事务中存在一个大事务没有执行完成，但其随后的事务已经由其他工作线程执行完成。如果设置了slave_preserve_commit_order将会防止这种‘gap’现象的存在。

二十 writeset(改进型并行复制)

1 机制 writeset是通过检测两个事务是否更新了相同的记录来判断事务能否并行回放的,通过分配进程进行判定

2 writeset是容器,内部放置针对单个事务内行变更的信息的所做的hash值,包括(DB,TABLE,key名称,所有value值)和其他一些提交信息

3,内存中会存在一个writeset map,存储大量的writeset history,将要提交的事务会被要求对已提交事务的hash值进行比较(也即是 writeset map中的hash值) 如果没有冲突,就证明操作了不同的行,是可以进行并行复制的,否则是不行的,会退化成之前的方式

4 writeset的优势在于哪怕在主库没有并发提交的事务在从库也有可能并发执行,提高了commit_order所不能具备的性能提升,在5.7的时候如果想提高性能需要主库设置延时提交功能

5 binlog_transaction_dependency_history_size 这个控制hash_map的容量

6 是对last_commited的进一步处理,在binlog中可以发现是乱序的.即便回退也是很方面

7hash_map本身是需要额外内存的

8  每次事务提交都会对hash_map进行更新,只针对冲突的行做hash和seq更新,更改last_commited 以便于并行回放,因为从库还是基于last_commited进行判定是否进行并行回放

二十一  元数据的改进

1 5.6-8.0的变化

基本核心点 information_schema 因为需要 逐个去扫描本地库文件相应的frm文件,而且查询也不会走索引 会创建临时表

8.0 改进 针对数据字典已经进行了合并处理 ,不必再扫描文件系统目录查找frm文件,也不用再查询数据字典表时创建临时表 能使用到数据字典表的索引

二十二 8.0的mgr改进特性

1 优化了查询视图,能在每个点直接查看集群的详细信息,便于查询和监控

2 增加了clone特性支持,对于新加入节点在无法进行binlog复制的情况下回选择clone复制特性

3 增加了集群一直性读的特性 可以设置蚕食 确保所有队列事务都应用完成的情况下才会提供读写服务 确保在故障切换时客户端都能读取到主服务器上的最新数据，保证了一致性 参数为before_on_parimary_failover，还有其他一些参数特性

4 增加了集群节点离开时对自身设置的角色属性,我们设置的是offline_mode(离线模式)

二十三 8.0 在线加列的原理

1 好处 极大的减少了因为DDL所产生的成本问题 又避免了因为DDL所产生的主从延时问题

2 过程 1 Instant add column在增加列时,实际只修改了元数据

2 所有新的记录都会设置一个特殊的标记，同时在记录内存储字段的个数

3 老数据的格式在存储上没有变化，新插入的数据按照新格式存储

4 当查询老数据时,会将新添加列的默认值追加到查询内容的最后面

二十四 复制新特性(克隆)

0 [INIT] ---> [ DATA FILE COPY] ---> [PAGE COPY] ---> [REDO COPY] -> [Done,包括应用redo log]

1 init 需要持有backup lock, 阻止ddl进行,防止元数据被修改

2 file copy

开始拷贝数据文件和 开启page tracking功能记录在拷贝过程中修改的page

3 page copy

开启redo archiving功能，从当前点开始存储新增的redo log，这样从page copy 开始所有的增量修改都不会丢失

同时在page track的page被发送到目标端。确保当前点之前所做的变更一定发送到目标端

4 redo copy

停止Redo Archiving”, 所有归档的日志被发送到目标端，这些日志包含了从page copy阶段开始到现在的所有日志，另外可能还需要记下当前的复制点

5 done 实例重启 应用redo log,完成任务

二十五 统计信息的收集与改进

1 定义: 统计信息的内容包括数据库库表的基本信息(列 行数 压缩方式,索引等),可以在information_schema提供查询(show table status) 或者 mysql执行计划树时的基准信息

2 收集方式

手动方式 analyze table 语句来手动的更新统计信息

自动方式 持久化 异步更新 (innodb_index_stats和innodb_index_tables)->(默认开启持久化)

非持久化 同步更新 (indexes/tables->show table status根据)

3 配置参数

innodb_stats_persistent 默认为ON

1 由于是异步更新,应该是不能及时收集信息的

2 收集的信息innodb只是预估值,所以不能完全相信,即便手动触发整理表,也可能不是完全一致

innodb_stats_auto_recalc 默认为ON

确认变化的数据是否超过10%，超过自动收集统计信息,即便触发更新,也还是异步处理,show table status也会触发收集

innodb 其他参数 是用来控制手动更新统计数据时的采页信息

4 8.0改进

5.6-8.0的变化

基本核心点 information_schema 因为需要 逐个去扫描本地库文件相应的frm文件,而且查询也不会走索引 会创建临时表,所以导致表越多,元数据收集越慢

8.0 改进 针对数据字典已经进行了合并处理 ,不必再扫描文件系统目录查找frm文件,也不用再查询数据字典表时创建临时表 能使用到数据字典表的索引,提升了效率,用innodb代替了memory引擎

5 sys库收集

sys schema的数据主要源自performance_schema。其目标是把查询performance_schema的复杂度降低，让DBA能更好地利用这个库里的数据，更快地了解MySQL的运行情况。

二十六 影响慢查询的相关因素

1 min_examined_row_limit 默认是1000,小于1000的不会被记录, 根据的扫描行数row_exrmind

2 log_queries_not_using_indexes 把没有走索引的sql也会进行记录,默认不会开启

3 slow_query_log_file 设置慢日志位置

4 slow_query_log 是否开启慢日志文件

5 long_query_time 设置慢日志时

二十七 排查故障相关问题

1 mysql出现 too many connection 无法登录怎么办

1 联系研发中断相应访问频繁出现BUG的程序

2 登录服务器建立iptables规则拒掉相应的问题IP

2 linux服务由于高负载无法登录服器怎么办

1 尝试用raid控制卡进行后台登陆,如果能登录的话

2 linux服务器无法登录不意味mysql无法连接,通过mysql进行操作,kill session等操作为linux减压

3 排查问题思路

1 linux角度 io 内存 cpu 整体负载

2 mysql角度 1 通过show processlist 1 总线程数 2 事务活动线程 3 查询活动线程 4 锁等待

二十八 死锁日志分析

0 锁属性分析

Lock_X (排它锁)

Lock_S (共享锁)

1 锁类型分析

LOCK_REC_NOT_GAP (锁记录)

LOCK_GAP (锁记录前的GAP)

LOCK_ORDINARY (同时锁记录+记录前的GAP 。传说中的Next Key锁)

LOCK_INSERT_INTENTION(插入意向锁，其实是特殊的GAP锁)

LOCK_WAIT (锁等待)

2 不同属性的锁和不同类型的锁进行搭配,我们称之为锁组合

3 innodb行锁是Record lock，默认就是带gap锁的next-key lock。如果见到locks rec but not gap这种，才是不带gap锁的record lock

4 分析

1 关键字 wating ,通过定位waiting关键字可以获取两个事务都需要什么锁被获取,此处都为lock_x

2 死锁事务并不会显示完整的事务,所以显示的sql是等待的事务,可能并非执行的事务,需要拿到完整的事务来进行分析

3 一个事务如果有这行的记录锁,然后对这个记录进行操作并不会再次申请锁,适用于以下情况 ( 条件项和索引项完全一致,并且不包含NULL值 也就是所谓的唯一性查询,如果不是唯一性查询 会申请等待lock x)

二十九 索引

1 ICP特性

定义:索引下推,在innodb层实现索引的二次过滤,而不是返回server层

优势:减少了回表的次数,节省IO和cpu资源

特定:通过explain的extra能发现是否应用了icp特性

2 索引失效场景

隐式转换,函数使用,基于成本的使用了其他的索引项(数据量有关)

3 优化器

mysql优化器是基于成本来决定使用合适的索引,根据的是统计信息,通过以下条件判定

1 可能性是需要扫描行数的估值 2 需要考虑索引失效的场景

4  分类

聚簇索引(数据文件和索引在一起): innodb主键 非聚簇索引(数据和索引文件不在一起):  唯一性索引,联合索引,单列辅助索引,全文索引

三十数据页构成

1 file header(数据页通用元数据,含有上下页的编号,形成单向链表  以下八种 :checksum、页的偏移量、prev页、next页、log sequence num、页类型、更新到LSN值、页属于的表空间)

2 page header(数据页专用元数据,比如此页总条数这种信息 )

3 Infimum + Supremum(两个虚拟的伪记录，分别表示页中的最小和最大记录)

4user record(用户数据)

5 free space(尚未使用的空间,被删除的数据也放置再次)

6 page directory(地址偏移量,定位到具体的数据页后在此进行用户数据的二次查找)

7 File Trailer(校验完整性,LSN+CHECKSUM,从硬盘读取页进行的校验)

三十一 线程池

mysql :现阶段是一对一,也就是一个线程服务一个常态链接,这些常态链接通过thread_cache进行缓存,保证可以重新利用而不用再耗费资源重新创建,通过show thread_cache可以发现被使用的cache

percona: 开启了pool连接池,这样不仅能缓存已经建立的常态链接,而且还能实现一个thread管理多个连接服务,这种情况下是不会使用thread_cache的

三十二  DDL原理

mysql自从5.6之后会分为两种模式,copy和in-place模式,通过in-place模式可以不阻塞事务进行,以下为相关原理

1  mysql内部会有一块专门的内存区域,行为分为rebuild和no-rebuild两种

2  rebuild 会同时生成ibd文件和frm文件,对于事务在做DDL期间,如果有事务发生,期间事务会作用于原ibd文件,而同时会存储一份操作日志放入此内存区域,等待DDL提交完成时,将这些操作作用于新的ibd文件,此过程成为rebuild

3  no-rebulid 并不会重建表和内存空间,只会生成frm文件 只涉及到元数据的更改和索引项的维护,事务直接应用.所以成本较低

4  online ddl方式的DDL语句传递到从库会阻塞相关的事务语句,和主库不一样,所以大的DDL会导致从库延时

三十三  表碎片与页合并

1 对数据的删除操作-可以重用

2 页分裂造成的碎片-基本不会重用碎片空间,常见于对比ID主键和UUID主键的大小对比

3 insert语句回滚造成的碎片-不会释放磁盘占用的空间

4 insert语句失败造成的碎片-不会释放磁盘占用的空间

5 表碎片的空间都是可以重用的

2 页分裂和页合并

页合并: 所谓的页合并是指当达到页的合并阈值时,会寻找相邻的数据页进行合并,这样两个页数据变成一个页,另一个页属于可用状态的空白页

页分裂:如果InnoDB页没有足够的空间容纳新的记录或索引条目,就会发生页分裂 它将被分成2页，每页约占50%(第一页的数据平分到第二个页)。这意味着，即使对表只有插入，没有回滚和删除，最终也可能只有平均75%的页利用率——因此这种页内部损失为   25%。

页合并解决方法:将创建的页降至合并阈值之下。当这种情况发生时，InnoDB通过合并操作将数据从分裂的页中移出。对表和特定的索引合并阈值是可配置的。

另一种重新组织数据的方法是OPTIMIZE TABLE 这是解决过多页比较稀疏的唯一方法。所以对于经常需要大量删除数据的表要周期性整理下

三十四 select count(*)

1 如果同时存在多个二级索引，会选择key_len 最小的二级索引；如果不存在二级索引，那么会走主键索引；如果连主键都不存在，那么就走全表扫描！

2 如果存在辅助索引的话,针对表总数量的查询走辅助索引的话,查询的成本就会大大降低

3 表的数据量越大,不止表数据量增大,索引的大小也在慢慢变大,索引查询总数会越来越慢

4 推荐采用count(*)进行查询,官方做过优化, innodb如果能实现myisam那样的计数器功能的话,统计表的数量会快很多

三十五 关于mutex锁我们需要知道的

1 在许多对象(如数据缓冲区、字典表、系统锁表、双写缓冲区等)上、在许多操作作用的对象(如事务、回滚段等)上，InnoDB都定义了很多系统锁，用以保护某个对象。这些系统锁，Mutex是一种系统锁的泛指概念

2 Spin Lock，InnoDB称为读写锁 r-w lock 也是读写锁的一种,目的是保护读写锁对象

3 spin lock

1 自旋(spin)是一种通过不间断地测试来查看一个资源是否变为可用状态的等待操作，用于仅需要等待很短的时间等待所需资源的场景。使用自旋这种“空闲循环(busy-loop)”来完成资源等待的方式要比通过上下文切换使线程转入睡眠状态的方式要高效得多。但如果自旋了一个很短的时间后其依然无法获取资源，则仍然会转入前述第二种资源等待方式.

2 自旋锁是低成本的等待；不过它是一个活跃的等待，会浪费一些cpu资源。因此如果看到大量的自旋等待和自旋轮转，则很显然它浪费了很多cpu资源。浪费cpu时间和无谓的上下文切换之间可以通过该值来平衡

3 通过spin lock可以确保临界资源最多只被一个线程拥有,线程释放后,会唤醒其他等待线程

4 自旋锁会自旋，然后升级成更高级的像内核事件。

Spin Waits：自旋锁的个数：

Spin Rounds：自旋锁旋转的次数

Os Waits：自旋锁升级到os wait的个数

5  补充

mutex是针对内存对象的保护对象,比如链表,共享buffer之类,而rw-lock是针对各种页的保护对象,针对于多线程对同一页的读写  而我们经常谈到的lock是针对事务,目标是具体的表和相应的指定行,还有针对io的操作 io_fix

6  针对全局并发的线程限制控制

free/LRU/flush list Mutex buffer_pool全局链表 8.0是6个链表都有自己的mutex锁,但是8.0之前=只有一个全局大锁

hash_lock rw_lock   针对hash表的mutex锁 对象是针对每个buffer pool instance   首先获取hash_lock, 然后才是 buffer block mutex 或者是 page frame mutex ->AHi

BPageMutex mutex   mutex 针对各种页对象

io_fix, buf_fix_count   针对IO的io_fix

BPageLock lock rw_lock rw-lock读写锁 针对各种页对象

log_sys_t mutex针对的就是redo的log_buffer对象

总结 就是 mysql的三大链表+维护多个instance实例+redo+各种页对象 面对这些目标 用mutex+rw-lock实现了innodb_buffer_pool的并发访问

buffer pool size必须以 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances来做向上对齐 所以实际使用的大于buffer_pool设定

三十六 innodb行格式

1 行格式一共有四种：Compact(5.6)、Redundant、Dynamic(5.7默认格式)和Compressed(压缩格式)

2 对于 Compact 和 Redundant 行格式，InnoDB将变长字段(VARCHAR, VARBINARY, BLOB 和 TEXT)的前786字节存储在B+树节点中，其余的数据存放在溢出页(off-page)

3 Dynamic行格式，列存储是否放到溢出页，主要取决于行大小，他会把行中最长的一列放到off-page，直到数据页能存放下两行。TEXT或BLOB列<=40bytes时总是存在于数据页。

4 Compressed物理结构上与Dynamic类似，Compressed行记录格式的另一个功能就是存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度数据能够进行有效的存储(减少40%，但对CPU要求更高)。

5 总结来说 就是对于大字段来说,存储本页和溢出页的处理机制

三十七分析内存占用问题i

0 两个角度 1 是事件占用资源(MySQL后台) 2 线程占用资源(程序访问)

1 查看buffer_pool和内存总量占用比,确定是否是buffer_pool设置太大导致

2 查看sys内存相关视图

0 memory_global_total 分配总资源(总数)

1 memory_global_by_current_bytes 全局内存分配相关(单列)

2 memory_by_host_by_current_bytes 主机占用资源

3 memory_by_user_by_current_bytes 用户占用资源

4 memory_by_thread_by_current_bytes 线程占用资源(常用分析内存泄漏表)-重点

select thread_id,

event_name,

SUM_NUMBER_OF_BYTES_ALLOC

from performance_schema.memory_summary_by_thread_by_event_name

order by SUM_NUMBER_OF_BYTES_ALLOC desc

limit 20;

5 memory_summary_global_by_event_name 事件占用资源    (常用分析内存泄漏表)-重点

具体SQL语句

select event_name,

SUM_NUMBER_OF_BYTES_ALLOC

from performance_schema.memory_summary_global_by_event_name

order by SUM_NUMBER_OF_BYTES_ALLOC desc

LIMIT 10;

3 查看top下的mysql内存占用率

4 分析思路

1 从主机用户和线程三个角度进行分析,还要结合mysql是否有版本BUG因素

2 sys的视图并不准确,尤其分配的总资源,所以这些视图只能作为排查问题的参考值

3 如果没有数据 记得执行 UPDATE setup_instruments SET ENABLED = 'YES',TIMED = 'YES' where NAME like '%memory%';

4  如果分配总资源统计数据相差很大,基本由于临时开启造成的,因为是无法获取bf占用内存的,需要mysql重新开启才能获得较为准确的统计数据,所以推荐mysql实例初始化时都要加上此参数

5 ## 指定开启单个instruments 开启memory为例

UPDATE setup_instruments SET ENABLED = 'YES',TIMED = 'YES' where NAME like 'memory%';

因为 该命令是在线打开内存统计，所以只会统计打开后新增的内存对象，打开前的内存对象不会统计，建议您打开后等待一段时间再执行后续步骤，便于找出内存使用高的线程。

## 开关所有的instruments

--performance-schema-instrument= '%=ON'

--performance-schema-instrument= '%=OFF'

开启下

三十八 explain 之select type

一 derived

1 派生表 在explain上显示,extra中出现using tempoary,代表是在派生表(临时表的一种,代表子查询)中查询,而真正的子查询内部查询则在select_type中显示 devrived.这里需要注意

2 如果出现派生表,则代表先执行的一定是子查询,explain虽然第一行出现的虽然不是子查询的方式,但是依赖派生表,所以肯定不是第一执行,可以尝试把子查询改成join查询进行优化

二 select type 出现 DEPENDENT SUBQUERY。 mysql会将外层的结果先查询出来,然后逐条执行子查询,如果外层结果集很大,这个就会很慢,也即是DEPENDENT SUBQUERY所依赖的表需要上层查询配合完才进行条件过滤

| 1 | PRIMARY | | ref | | | 147 | loan_urge_platform.g.id | 18 | Using where |

| 2 | DERIVED | u | ALL | NULL | NULL | NULL | NULL | 1767 | Using where |

| 3 | DEPENDENT SUBQUERY | p | ref | IDX_PERSON_TASK_URGER,IDX_QT | IDX_QT | 148 | loan_urge_platform.u.id,const | 104 | Using index |

这个表先利用u执行条件查询,然后u的条件查询结果的每个都和p进行关联进行计算查询,最后的结果表形成派生表在explain derived2进行查询

三十九  mysql线程

1 thread_cache_size 此参数设置了mysql缓存线程数的大小,这样被换成的线程可以重复利用,不用重新创建花费成本,连接池不用此参数

2  max_connections_error 此参数设置host连接因为密码错误导致的host连接被拒绝,因为mysql会缓存host cache,解决此问题的办法是1 更改密码 flush host

四十 表缓存

1 table_definition_cache

制总frm文件的数量，还是个hash表，内部维护。如果打开的表实例的数量超过了table_definition_cache设置，LRU机制将开始标记表实例以进行清除，并最终将它们从数据字典缓存中删除。简单通俗点frm文件有多少，就设置多少了。

2 table_open_cache

表的文件描述符缓存。增加这个值会增加mysqld需要的文件描述符的数量。可以通过检查Opened_tables状态变量来检查是否需要增加表缓存。我认为应该是总的文件数,包含ibd文件

如果show processlist 一直出现opening table状态 则需要加大table_open_cache

四十一 mysql order by原理

1 将order by的列和select中的列读到sort buffer中,然后进行排序,如果排序的数据很多,则需要用到tmp buffer，则会放入tmp buffer中 需要利用归并排序算法，保证临时文件中记录是有序,然后再进行多文件排序

2 如果order by本身走的是索引,就不用执行排序操作,否则还需要创建排序索引,通过show processlist可以发现creating sort index explain出现using filesort

四十二 innodb status

1 master主线程

srv_master_thread loops master线程的循环次数(不论空闲还是繁忙,master线程总循环次数是一直在增加的)

当srv_active远远大于srv_idle的时候 代表繁忙

2 SEMAPHORES信号量

分为两部分 mutex锁和rw-lock锁

分为以下三部分内容

spin 自旋等待个数 round 自旋等待次数 os wait 自旋等待升级为os wait的个数

DEADLOCK

我们主要关注死锁发生的具体事务

3 TRANSACTIONS

1 我们主要关注 History list length 防止过长,代表undo段过程

2 transactions状态 : not start cleaning up 休眠

: activie sending data 活跃事务(不包含只读事务)

: rollback 回滚事务

3 包含 事务扫描锁定的行数

4 buffer_size

1 page单位个数

2 Free+Database < totoal 因为还有其他需要页,比如数据字典

3 各个 buffer instance 分配页现状

4 buffer_pool命中率

四十三 整体恢复备份方案制定

场景分析

1 线上工单变更,需要回滚

2 业务程序变更,需要回滚

3 历史数据,需要回溯

对应手段

1 对于研发提出的工单变更申请,可以通过数据库运维平台,选择是否备份.如果选择备份,可以在处理成功后进行回滚,而对于truncate/drop操作则不会自动执行,由DBA手动完成,执行操作前会进行二次确认是否进行备份

2 线上会专门提供一台机器,用来恢复前一天的历史备份数据,这样做得好处有两个 1 能确认前一天备份有效性的认证 2 如果需要恢复到前一天的数据,省却了恢复备份数据库实例的时间

3 针对于加速准确应用binlog的话,可以采用canal,指定库表和时间,这样能快速的恢复到指定的位点

4 针对于远古历史数据备份恢复,则需要2个条件 1 设置历史备份的保留期和存放地 2 利用binlogserver进行binlog进行全量备份

5 也可以建立需要有效恢复时间的延时库,可以针对大表被drop的快速恢复

四十四 事务锁与分类统计

1 分析

1 从作用来说 分为 共享锁和排它锁,

2 innodb底层的lock都是实际上就是加的共享/排他锁

3 实际上还有意向共享锁和意向排他锁.在获得共享锁/排他锁之前都要先获取各自的意向锁.这样做的好处是不用每一行都进行判断,节省了时间,意向锁的目标是表级别,而非意向的目标是行级别

2 定义

1 对于insert、update、delete等操作。会自动给涉及的数据加排他锁 对于一般的select语句，InnoDB不会加任何锁

2 因为select一般不会加任何锁,所以设定了可以手动添加排他/共享锁

共享锁：SELECT ... LOCK IN SHARE MODE;

排他锁：SELECT ... FOR UPDATE;

3 乐观锁和悲观锁都是针对读(select)来说的

3 在这里补充下

0 锁属性分析

Lock_X (排它锁)

Lock_S (共享锁)

1 锁类型分析

REC_NOT_GAP (锁记录)

GAP (锁记录前的GAP)

ORDINARY (同时锁记录+记录前的GAP 。传说中的Next Key锁)

LOCK_INSERT_INTENTION(插入意向锁，其实是特殊的GAP锁)

WAIT (锁等待)

2 不同属性的锁和不同类型的锁进行搭配,我们称之为锁组合

3 mysql8.0可以查看具体的锁情况 data_locks的lock_mode

4 gap-lock

1  1:加锁的基本单位是 next-key lock  范围是左开右闭。

2:针对唯一索引的等值查询，next-key lock 退化为行锁。优化

3:针对不唯一索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁.

4:针对不存在记录的等值查询,不论唯一/不唯一的索引 都会按照正常的GAP LOCK锁定上下区间

1 辅助索引是以索引值为基础进行架构组织的,主键本身是无序的,按照辅助索引排序

2 分析gap lock的时候,先确定辅助索引的锁定范围,再判断插入记录 首先判断辅助索引值是否在锁定范围内,辅助索引值相同的情况下按照主键值排序,是否在锁定范围内

3 innodb的锁机制实现了高性能的读写并发请求

四十五 binlog组提交

分为三个阶段 flush sync 和commit

1 flush阶段 所有已经注册线程都将写入binary log缓存

2 sync阶段 将binlog cache刷新到磁盘中,当sync_blog=1时,binlog落盘后dump线程才会去取日志

3 commit阶段将 整个队列的事务据顺序调用存储引擎提交事务,同一时间另一队列可进入flush阶段，从而使group commit不断生效

每个stage阶段都有各自的队列，使每个session的事务进行排队。当一个线程注册了一个空队列，该线程就视为该队列的leader，后注册到该队列的线程为follower，leader控制队列中follower的行为。leader同时带领当前队列的所有follower到下一个stage去执行，当遇到下一个stage并非空队列，此时leader可以变成follower到此队列中

存储引擎(InnoDB) Prepare    ---->    数据库上层(Binary Log)   Flush Stage    ---->    Sync Stage    ---->    调存储引擎(InnoDB)Commit stage. 这是优化后的阶段

binlog中的事务位置为 begin之前的at position commit之后的第一个at position

四十六 推荐比较流行的中间件

dble,sharding-jdbc sharding-proxy

四十七 mysql的临时文件存在大量文件(deleted)状态,磁盘没有释放

解释:系统存在大量文件已经被删除(deleted)，存在大量文件句柄没有释放，导致当前占用的磁盘空间无法释放。文件句柄释放不掉的根本原因，是因为当前有线程对句柄进行引用。

解决方式:

将当前线程进行重启，关闭线程，从而让句柄释放，释放空间,总结就是查看mysql当前在做什么,然后具体分析

四十八  GTID_PURGE与复制同步那点事

1 我们都知道,搭建从库的时候必须先手动执行set gtid_purge,但是大家知道这是为什么吗,这是因为gtid_executed是gtid_purge的子集,当设置gtid_purge时,gtid_executed也会被同样设置,

2 gtid_executed的作用之一从库读取GTID执行的集合,然后在第一次进行同步时,会从后向前扫描每个binlog文件,然后和gtid_executed做对比,如果不在范围内,就扫描binlog下面的gtid记录,找到位点后开始同步,gtid的auto_positon机制就是根据这个实现的

3 从库判断事务有没有执行也是根据gtid_executed,会把事务的GTID与gtid-executed进行对比,如果在里面,则跳过不执行,反之就执行 在5.7之前gtid_executed记录在内存中,所以需要开启binlog进行记录,5.7的时候已经记录到具体的表中,通过gtid复制的从库就不必须开启binlog了。

总而言之,如果你搭建从库,记得一定要set gtid_purge 否则会发生错误

四十九 mysql排查OOM的基本思路

1 排查是否是由于innodb_buffer_pool设置太大的缘故,如果不是则进行下一步,如果是,则调小innodb_buffer_pool的大小

2 查询sys.memory_global_by_current_bytes表

1 select event_name, current_alloc from sys.memory_global_by_current_byte

如果没有开启 设置

update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%'

然后确定排行最大的函数体

1 出现row0sel线程占用第一 大概率是由于sql语句使用ICP导致的,可以尝试调小table_cache和table_instance尝试

2 出现memomey线程占用第一,大概率是内存泄漏

3 分析具体的函数体 思路

1 查询相关mysql版本是否有内存泄漏的BUG与此相关

2 在源码中定位到此函数,进行分析

4 尝试进行解决

5  以下汇总的几个场景发生过的OOM

1 由于ICP特性导致的OOM

2 由于mysql相关BUG导致的OOM

3 由于NUMA导致的OOM

4 由于特定业务sql语句触发导致的OOM

6 总结:

尽量模拟下当前场景,是否能够重现

五十 mysql-join的算法

大概有三种

1 NLJ 驱动表的每一条数据都和被驱动进行关联查询,然后再回表访问下一条,以此类推,由于没有走索引,会形成笛卡尔积MXN

2 BNL 利用join_buffer,将驱动表的一部分加入内存中,然后和被驱动做关联,这样减少了每次都要回驱动表的IO操作.仅仅在连接字段不走索引的情况下.属于一种mysql的自我优化

3 INL 通过外层表匹配条件 直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较， 这样极大的减少了对内层表的匹配次数，从原来的匹配次数=外层表行数 * 内层表行数,变成了 外层表的行数 * 内层表索引的高度，极大的提升了 join的性能。

总结思路优化

1、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)

2、为匹配的条件增加索引(减少内层表的循环次数)

3、增大join buffer size的大小(一次缓存的数据越多，那么外层表循环的次数就越少)

4、减少不必要的字段查询(字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少)