MySQL-必须要知道的小细节

一、mysql隐式回滚

需要注意，这里的场景是说我们在提交一个事务的时候需要手动begin，dml，commit。也就是set autocommit=0;

1、会话窗口被关闭

2、数据库关闭

3、出现事务冲突

二、mysql隐式提交

1、begin； dml；begin

2、set autocommit=1

3、DDL

4、DCL

5、锁定语句：lock table，unlock tables

三、事务的ACID

A：原子性

    不可再分，一个事务生命周期中的DML语句，全部成功或全部失败  

C：一致性

    事务发生前、中、后数据都最终保持一致

    通过ACSR+DWB保证  

I：隔离性

    事务操作期间不会受其他事务影响  

    通过隔离级别、MVCC、锁控制保证  

D：持久性

    一旦事务提交，永久生效（落盘）  

    通过redo、DWB保证  

四：mysql的数据结构

1、mysql存储数据的最小单元是页，16kb

2、每页至少有两条数据（单行数据不能超过8kb）

3、每条记录有3个隐藏列（自动聚簇索引id、操作号码、指针）

五：mysql的物理存储结构（Innodb存储引擎）

8.0以前：

    ibd：数据和索引  

    frm：私有数据字典  

    ibdataN：系统数据字典  

8.0以后：

    ibd：数据、索引、私有数据字典

六：mysqlAHI（自适应hash索引）

自动评估热的内存索引page，生成hash索引表，帮助innodb加速读取索引页，相当于索引的索引。

七：优化器算法

BNLJ

用于优化大型表的连接查询，其中一个表太大，无法一次性加载到内存中。BNLJ通过分块和循环嵌套来处理大型表和小型表之间的连接

SNLJ

示例：select * from t1 join t2 on t1.id=t2.id
优化器默认优化规则
在选择驱动表时，在on条件中，优化器优先选择有索引的列作为驱动表
如果两个列都有索引，优化器会按照执行的代价区选择驱动表和非驱动表
多数情况为小表驱动大表

MRR

示例：select * from t1 where e= xxx 
若e（辅助索引）有多个值的时候，在回表前会对聚簇索引的id进行排序，从而达到减少回表的效果

ICP

idx（a,b,c）
            查询条件为 where a= and b> and c<
ICP的作用：server层做完过滤后，只能用a，b的部分辅助索引，将c列条件的过滤下推到engine层，进行再次过滤，排查无用的数据列，最终再去磁盘上拿数据页。
            大大减少无用IO，减少回表

八：Mysql内存不足会导致的问题

1、IO慢
2、CKPT（刷新脏页到磁盘）触发不及时
3、语句优化不好

九：Innodb存储引擎的核心特性

1、mvcc多版本并发控制
2、聚簇索引
3、支持事务
4、支持行级锁
5、外键
6、自适应hash索引
7、热备
8、自动故障恢复
9、双写机制

十：不走索引的情况

1、没有查询条件或查询条件没有建立索引
2、查询结果集超过总数据行大约25%
3、索引本身失效
4、查询条件使用函数在索引列上
5、隐式转换
6、<>\not in
7、like%在前

十一：执行计划

count（主键或唯一键）
>
eq_ref（多表连接时，非驱动表的连接条件是主键或者唯一键）
>
reg（辅助索引等值查询）
>
range（索引范围扫描）
>
index（全索引扫描）
>
all（全表扫描）

十二：B树和B+树在mysql中的区别

b+树在叶子节点之间增加了双向指针
1、范围查找更加高效
2、叶子节点分裂更加简易

十三：叶子节点分裂的原因

大多数情况下是由于插入无需数据造成的，分裂会导致空间占用更多，全表扫描更慢，表碎片更多

十四：MySQL的主要线程

1、main thread：核心后台进程、负责全局的管理和数据一致性
2、IO Thread：负责读、写、日志等磁盘io交互
3、Purge Thread：undo页回收
4、Page Clear：脏页刷新
5、Other Threads

十五：MySQL体系结构

1、连接层：协议、验证、连接限制、线程
2、server层：语法语义限制、解析器、优化器、执行器
3、存储引擎层：各类线程

十六：alter table的三种算法

1、copy：
  a、锁表，期间DML不可并行执行
  b、生成临时表以及临时表文件（.frm  .ibd）
  c、拷贝原表数据到临时表
  d、重命名临时表及文件
  e、删除原表及文件
  f、提交事务、释放锁

2、iplace
  a、对表加元数据共享锁、并升级为排他锁
  b、在原表所在路径下创建.frm  .ibd临时中转文件
  c、申请row log空间，用于存放ddl执行阶段产生的dml操作
  d、释放排他锁，保留元数据共享锁（此时DML可并行）
  e、扫描原表主键及二级索引和所有的数据页，生成b+树，存储到临时文件中
  f、将所有对原表的dml操作记录在row log中
  g、升级元数据共享锁、产生排他锁（此时DML不能并行）
  h、重做row log中的内容
  i、重命名原表文件，将临时文件改名为原表文件，删除原表文件
  j、提交事务释放锁

3、instant
  8.0以后的新特性，只需要修改数据字典中的元数据，无需拷贝数据也无需重建整表，也无需加排他锁，原表数据不受影响，整个ddl几乎瞬间完成，也不会阻塞dml

十七：pt-osc备份数据的工作流程

1、检查更改表是否有主键活唯一索引，是否有触发器
2、检查更改表的表结构，创建一个临时表，在临时表上执行ddl
3、在源表上创建三个触发器，分别对insert、update、delete
4、拷贝源表数据到临时表，拷贝过程中对源表的更新会通过触发器写入新表
5、修改源表和临时表的名字
6、删除源表和触发器，完成表结构修改

注意：限制

源表必须有主键或唯一键
如果该表的主从逻辑过于复杂将停止工作
如果主从延迟高将停止工作
只支持innodb表

十八：数据库开发流程

1、可行性需求分析阶段
2、概要设计：er图、模型图、需求确认
3、详细设计：范式、规范
4、代码编写
5、功能测试：功能完整性、性能、安全
6、部署、上线
7、技术支持、培训

十九：叶子节点分裂的原理

会按照原数据页的50%进行分裂，然后创建新页，存放被分离出的数据与新插入的数据，更改双向指针

二十：innodb存储引擎的核心特性

1、mvcc多版本并发控制
2、聚簇索引
3、支持事务
4、支持行级锁
5、外键
6、自适应hash索引
7、热备
8、自动故障恢复
9、双写机制

二十一：MySQL历史版本发布日期

mysql 5.5.0  (2010年12月15日)
mysql 5.5.62（2018年04月19日）
mysql 5.6.0 （2013年02月05日）
mysql 5.6.51（2019年02月01日）
mysql 5.7.0 （2015年10月21日）
mysql 5.7.35（2021年04月20日）
mysql 8.0.0 （2018年04月19日）
mysql 8.0.25（2021年04月20日）