MySQL

MySQL架构介绍

客户端：支持接口（标准的API、JDBC、ODBC、.NET、PHP、Phython等）
服务端：
- MySQL软件
  - Server层
    - 连接池：验证与授权、线程、内存与缓存管理
    - 管理服务与工具：备份与恢复、安全、复制、集群、分区管理、事务管理、数据库模板管理、工作台、合并工具
    - 核心业务处理层：
      - SQL接口：数据管理语言和数据定义语言、存储过程、视图、触发器等
      - 解析器：查询\事务对象优先级
      - 优化控制器：访问路径统计
      - 缓存和缓冲池：全局和局部引擎的缓存和缓冲池
  - 存储层
    - 插件式存储引擎：MyISM、InnoDB、Cluster。。。。。
- 磁盘文件
  - 文件系统：NTFS、UTS、EXT2/3、NFS、NAS。。。
  - 文件和日志：REDO、UNDO、DATA。。。。

连接池

show fulll processlist 查询所有连接

show processlist 只能查询前100个连接

通过max_connections控制修改最大连接数

SQL接口：CRUD分流执行SQL语句（执行器）

Parser：解析器

Optimizer：查询优化器

Cache和Buffer：查询缓存（MySQL8.0去掉：缓存命中太差，同一条SQL语句才能命中）

SQL执行过程

连接器：客户端通过连接器与服务端简历连接（mysql -uroot -p123456），验证用户，建立连接
查询缓存
- SQL语句一致才会存储缓存
- 表修改缓存则清空
解析器和预处理
- 词法分析：按照空格和逗号切割语句
- 语法分析：鉴别关键字、表名称、列名称、查询条件
- 形成语法树：
- 预处理：检查语法树是否合法（检查权限，列名是否存在、表名是否存在、别名改为表名）
优化器
- 解析语法树的执行一般有多种计划
- 最终只能选择其中一种执行计划去执行
  - 基于成本计算
    - 根据索引的执行情况选择
    - 一般一个SQL语句执行，只会选择一个索引去使用
    - 如果通过join连接的多表，可以再查询计划中选择基表
    - 自己写的SQL语句，不一定按照你写的条件执行
执行器
- 按照执行计划调用存储引擎接口，通过存储引擎去操作磁盘中的数据。

MySQL的存储引擎

存储引擎针对表制定，存储引擎也是一种性能优化

InnoDB
- MySQL5.以后默认
- 特点：行锁、事务
- 适合于读写不分离，并发高的场景
MyISAM
- 特点：不支持行锁、不支持事务
- 适合于读多、写少
- 写操作时，进行表级锁定
CSV ：csv文件
Memory：内存的存储引擎，类似Redis

MySQL磁盘文件

查看磁盘文件位置：show variables like ‘datadir%’；可以通过datadir指定磁盘文件位置。

数据文件
- InnoDB
  - frm表结构文件
  - idb数据和索引文件（用户表空间）
- MyISAM
  - frm表结构文件
  - myd数据文件
  - myi索引文件
- ibdata系统表空间文件
  - 不断变大
  - 性能优化的一个点
日志文件
- binlog：二进制日志文件(数据恢复、主从复制)
- errlog：错误日志文件 mycentos.err
- slow_query_log：慢查询日志文件（默认关闭，性能分析）
- general_query_log：通用查询日志文件；
- redo_log：重做日志文件（保证事务ACID特性）ib_logfile0、ib_logfile1
- undo_log：回滚日志文件（保证事务ACID特性）ibdata1
- relay_log：中继日志文件
磁盘如何存储数据

- 随机写（数据文件：性能低）
  - 旋转时间
  - 寻找磁道
  - 寻找扇区
  - 判断扇区是否占用
  - 占用继续上面
- 顺序写（日志文件：性能高）
  - 旋转时间
  - 寻找磁道
  - 寻找扇区

MySQL数据在磁盘中存取

MySQL在磁盘中存数据
- 磁盘和MySQL交互
  - 磁盘和操作系统（内核空间）交互
    - 磁盘中最小的存储单位是扇面（扇区），默认最小512字节，小于512字节也占用512字节
    - 磁盘和操作系统（内核空间）交互最小单位是4K，该大小由操作系统限制
  - 操作系统和MySQL应用（用户空间）交互
    - 操作系统和MySQL应用交互的最小单位是16K，该大小可以通过参数（innodb_page_size）控制，索引结构中的节点是一个物理页的大小
- 思考：100条数据存储在磁盘上如何存储
  - 100条数据对应100个磁盘地址，需要通过IO读取100次才能遍历所有数据
  - 如何快速查找
    - 索引数据结构
      - 数据结构学习网站
      - Hash
        
        Memory数据引擎应用的此数据结构，查询快，O(1)；不支持范围查询。
        
        ;对key进行hash计算（数组下标 = hashcode / 数组长度）
        
        hash表特点
        
        一次性把表数据加载到内存中
        
        hash冲突：
        
        多个KV对都存储到同一数组下标下
        
        链地址存储解决hash冲突
        
        链表越长，查询越慢
        
        reHash：当数据长度扩充的时候，同一个key再前后两个数组存储位置不一样
      - 二叉树
        
        二叉树是一种高瘦树
        
        按需加载
        
        一次加载一次IO
        
        一次加载两个节点，防止内存装不下
        
        左子树和右子树从小到大
        
        特点：会退化成链表
      - 平衡二叉树
        
        红黑树是一种近似平衡二叉树（）
        
        左子树和右子树高度差不能超过1，超过1发生左旋、右旋
        
        使用平衡二叉树查找的性能接近于二分查找，时间复杂度是O(log2n)
        
        存在问题
        
        时间复杂度和树高度有关。树高等于查询次数即IO次数，磁道每次查询时间10ms
        
        平衡二叉树不支持范围查找，查询从根节点查找，效率不高
      - B树（改造二叉树）
        
        一个节点可以插入多少个索引？
        
        假如一个值（key）weibigint=8字节，每个字节两个指针，每个指针为4个字节，一个字节占用的空间为16个字节，（8 + 4 * 2 = 16）
        
        磁盘一次IO读取一页（一个节点）16K的数据量，一个节点可以存储1000个索引（16K / 16 = 1000）
        
        树高度为2时，存储的数据为 1000 * 1000 = 1百万（记录）
        
        叶子节点之间没有指向
        
        叶子节点和非叶子节点之间的值是非重复的
        
        每个节点都包含多个元素，非叶子节点中会在元素前中后指向与另一个子节点
        
        等值查询
        
        范围查询
        
        MySQL特点：所有节点都存储数据
      - B+树（改造B树）
        
        叶子节点之间是有指向的
        
        叶子节点和非叶子节点之间值是有重复的
        
        左子节点中的最大值小于父节点值
        
        右子节点中最小值大于等于父节点值
        
        MySQL的索引采用B+树对叶子节点进行改造，MySQL使用的B+树的叶子节点之间使用的是双向链表　　
        
        等值查询
        
        第一次磁盘IO：将磁盘块1加入到内存中，在内存中遍历比较，15 < 28走左路到磁盘寻址磁盘块2
        
        第二次磁盘IO：将磁盘块2加载到内存中，在内存中遍历比较，10 < 15 < 17,到磁盘中寻址磁盘块5
        
        第三次磁盘IO：将磁盘块5加载到内存中，在内存中遍历比较， 15 = 15，取出data，data存储的是数据则查询结束，磁盘中存储的磁盘地址，则根据磁盘地址查询数据取出。
        
        范围查询（15-26）
        
        首先查找值等于15的数据，将缓存结果存储到数据集（查询步骤同上）。
        
        查询到15之后，底层的叶子节点是一个有序列表，从磁盘块5向后遍历筛选所有符合条件的数据。
        
        MySQL特点：只有叶子节点存储数据
- MySQL索引结构
  - MyISAM索引结构
    - 主键索引为非聚簇索引：：叶子节点存储索引和数据地址
    - 辅助索引为非聚簇索引：叶子节点存储索引和数据地址
MySQL在磁盘中读取数据

索引的磁层原理与使用原则

索引常用的数据结构
- 数据结构学习网址
- B+树
- Hash
索引的使用
- 索引分类
  - 主键索引
    - 索引中的值是唯一的，不允许为空
  - 唯一索引
    - 索引中的值是唯一的，允许为空
  - 普通索引
    - MySQL基本引用类型，没什么限制
  - 全文索引
    - 只能用在char、varchar、text文本类型的字段上。字段长度较大时，如果用普通索引效率低，可以用全文索引
  - 前缀索引
    - 在char、varchar、text文本类型的字段，可以指定索引列长度（前十个字符组成索引），但是数据类型不指定
  - 组合索引
    - 最左前缀原则
    - 建议使用组合索引代替单列索引，主键索引除外
MySQL索引实现
- MyISAM索引（B+树）
  - 主键索引
    - 等值查询
    - 范围查询
  - 辅助索引（B+树）
- InnoDB索引（一个索引一个索引树）
  - 主键索引（聚簇索引）
  - 组合索引（辅助索引）
    - 针对多列创建索引，索引中的列是有顺序的
    - 组合索引存储
    - 组合索引查找
    - 最左匹配原则
      - 组合索引（a,b,c)相当于创建三个索引（a;a,b;a,b,c）,在where条件里，必须有a，才能使用索引，a可以在条件最右侧，优化器会自动优化到最左侧
      - 书写SQL的顺序不一定是执行顺序，优化器会优化
      - 使用组合索引时，MySQL会一直向右查询，直到遇到（<,>,between,like）范围查询
      - 何时创建组合索引
        
        频繁出现在where条件中的的非主键列，建议创建组合索引
        
        频繁出现在order by和group by中的列，建议按照顺序去创建索引（顺序不一致用不到索引，优化器不能优化，因为order by是明确需求）
        
        常出现在select语句中列也建议创建组合索引（所以不建议使用*）
  - 覆盖索引
    - 防止了回表（一次回标，多次IO），辅助索引中查找到的一条记录，会进行一次回表
    - 主要是针对select中的列进行优化，如果查找的select列，就不想去主键索引中查找对应的列，查询数据了
    - - 红色标识的字段，均用到索引覆盖
      - 紫色框标识的由于有字段d，不在组合索引列，需要回表，没有用到覆盖索引
  - 索引下推ICP
    - 索引下推是为了减少回表次数
    - 索引下推设置参数：Index_Condition_PushDown
      - 展示MySQL优化器中优化参数开关 show variables like ‘optimizer_switch’
      - 关闭索引下推（ICP）set optimizer_switch='index_Condition_PushDown=off';
    - - ICP未开启
        
        存储引擎根据索引a=13，b>=15会匹配三条索引记录三条索引记录会涉及三次回标
        
        存储引擎将三条记录返回给Server层
        
        Server层进行c=5过滤两条记录
        
        Server层根据d=‘pdf’过滤一条记录
      - ICP开启
      1. 存储引擎根据索引a=13，b>=15会匹配三条记录
        
        存储引擎根据索引下推的条件c=5在辅助索引树中进行过滤两条记录涉及两次回表
        
        存储引擎层会将两条记录返回Server层
        
        Server层进行d='pdf'过滤一条记录
explain工具查看执行计划
- 执行explain查询计划：explain select * from table_name;
  - id：标识符（多张表时标识哪个先执行）
  - select_type：查询类型（子查询，关联查询）
    - 展示单位查询的查询类型
    - simple 简单查询不需要unton和子查询（列为查询语句）
    - primary 主查询（区分主表、关联表或者子表）
    - subquery 子查询
    - derived from字句中出现子查询，就是派生表
  - table：查询表名称
  - partitions：匹配的分区，表被分区时才会用到
  - type：join类型（单位查询的连接类型或访问类型）
  - possible_keys：此次查询中可能选用的索引
  - key：此次查询中确切使用到的索引
  - ref：哪个字段或常数与key一起使用
  - rows：显示查询一共扫描了多少行
  - filtered：表示此次查询条件所过滤的数据的百分比
  - extra：额外的信息
    - using idex 使用到覆盖索引
      - 没有using where 索引用来查找数据；
      - 如果同时出现using where表明索引用来查找键值
    - using where 表示对存储引擎层返回结果在Server层过滤，过滤字段无索引
    - using index condition 用到索引下推
    - using filesort 说名排序没有用到index 在Server层排序
索引失效
- 全值匹配
- 最佳左前缀原则（多列索引遵循此原则）
  - 带头索引死（不使用索引，全表扫）
  - 中间索引断（带头索引生效，其他索引失效）
- 不要在索引上计算会失效
- 范围条件右边的列索引失效（不要在索引中间使用范围比骄）
- 尽量使用覆盖索引（selelct 索引1，索引2 from）
- 索引字段上不要使用 != 会索引失效
- 索引字段不要判断空
- 索引字段使用like不以通配符开头
  - 使用通配符可以用覆盖索引，解决索引失效
- 索引字段字符串要加单引号
- 索引字段不要使用or
- 总结：全值匹配我最爱，最左前缀要遵守。带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效；Like百分写最右，覆盖索引不写星，不等空值还有or，索引失效要少用。
索引创建原则
- 索引需要创建的情况
  - 频繁出现在where、order排序、group by
  - select频繁出现的列，创建组合索引，覆盖索引
  - 多表join关联，on字段两边的字段要创建索引
- 索引创建的建议
  - 表记录少的不需要
  - 一个表索引记录不要过多
    - 空间占用
    - 时间占用更新变慢、增加优化器选择时间
  - 频繁更新字段不建议创建索引
  - 区分度低字段（男、女区分数据度不大）
  - InnoDB主键索引建议自增，避免使用长字段
  - 不建议使用无序值作为索引
  - 尽量创建组合索引
  - 字符太长，使用前缀索引
索引优缺点
- 优点
  - 提高检索效率，降低磁盘IO
  - 通过索引进行数据排序，降低排序成本
- 缺点
  - 占据额外的磁盘空间
  - 对写、更新操作不友好

MySQL锁应用

锁划分
- 按照锁粒度：、
  - 全局锁
  - 表级锁：开销小，加锁快；不会出现死锁；锁粒度大，出现冲突的概率高，并发度最小。
  - 行级锁：开销大，加锁慢；会出现死锁；锁粒度小，出现冲突概率低，并发度也高
- 按照锁的功能：排他写锁、共享读锁
  - 读锁特点：一旦会话加了不管哪种粒度的读锁，其他会话同等粒度下只能读（read）不能写（write）。
  - 写锁特点：一旦会话加了不管哪种粒度的写锁，其他会话同等粒度下不能读也不能写。
- 按照锁的实现方式：悲观锁（物理锁，真正锁）、乐观锁（版本等逻辑控制）
全局（数据库）锁
- 提供全局读锁
  - 对于整个数据库，其他会话只能读，不能写
  - flush tables with read lock;
- 适用场景：全库备份
- 被阻塞操作
  - 数据更新语句
  - DDL数据定义语句
  - 包含更新操作的事务
表级锁
- 由MySQL的Server层实现的（一种是表锁，一种是元数据锁）
- 添加表锁
  - lock table 表名称 read(write) ，表名称read(write).....
- 表锁
  - 查看锁 show status like 'table%';
    - table_locks_immediate : 产生表级锁定的次数（全库所有表）
  - 表读锁
    - - session1(会话1)对mylock表加表读锁,session2(会话2)只能读,不能写
      - session1(会话1)对mylock表加表读锁,dep表没有加表读/写锁,该表不能被访问
  - 表写锁
    - - session1(会话1)对mylock表加表读锁,其他会话不能读,也不能写
- 元数据锁
  - MySQL5.5引入, 称为MDL
  - 对表做CRUD操作称为读锁, 对表做结构变更(DDL)称为写锁
行级锁

行级锁是由存储引擎层实现的，主要讲解InnoDB的行锁
- 行级锁锁的某一行或者行之间的间隙
- InnoDB是通过给索引树上的叶子节点中的索引项加锁来实现
行级锁分类
- 共享读锁
  - select * from table_name where ... LOCK IN SHARE MODE --- 手动添加共享锁
  - select * from table --- 无锁
- 排他写锁
  - 自动添加 UPDATE、DELETE、INSERT InnoDB自动添加排他写锁
  - 手动添加 select * from table_name where .... for update
- 意向锁（表级锁 InnoDB）了解
  - 意向锁主要作用是为了【全表更新数据】时性能提升。否则在全表更新数据时，需要先检查该表是否在某些记录上面有行锁
  - 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
  - 意向排它锁（IX）：事务打算给数据行加行排它锁，事务再给一个数据行加排它锁前必须先取得该表的IX锁
- 查看行锁
- - - InnoDB_row_current_waits：当前正在等待锁定的数量
    - InnoDB_row_lock_time：从系统启动到现在锁定的总时长
    - InnoDB_row_lock_time_avg：每次等待锁花费的平均时长
    - InnoDB_row_lock_time_max：从系统启动到现在等待最长的一次所花的时长
    - InnoDB_row_lock_wait：从系统启动到现在总共等待的次数
锁定范围(行级锁是针对索引加锁，跟事物有关系)
- 以下都是再RR隔离级别下；RC隔离级别下一般都是记录索引，只有再外键约束检查以及唯一检查时会使用间隙锁封锁区间。
- 记录锁（record Locks）：锁定搜索中一条记录
  - 主键索引：主键索引树当前记录加索引
  - 辅助索引：命中记录的辅助索引项和主键索引项加记录锁，并且辅助索引项命中记录的两边枷锁。（RR隔离级别）
    - 命中记录：
    - 没有命中记录
- 间隙锁（Gap Locks）：锁住两个索引之间的区间（间隙），是一个左开右开区间；范围更新（< ，>...）、等值查询
  - 主键索引：等值查询或范围查询没有命中任何一条记录，这时where条件的间隙区间会被加间隙锁。（RR隔离级别）
  - 辅助索引：
- 临键锁（Next-key Locks）：间隙锁+紧邻间隙锁的下一个记录锁，左开右闭空间
  - 主键索引：范围查询，命中一条或多条记录时加临键锁，包含where条件的临键区间会加临键锁。
  - 辅助索引
- InnoDB行锁演示
  - 创建锁及索引
    - test_innodb_lock 表名称；test_innodb_index索引名称；a，b字段名称
    - create table test_innodb_lock (a int(11),b varchar(20)) engine=innodb;
    - create index test_innodb_index on test_innodb_index (a)
  - 行锁演示
    - session-1：set commint=0;
    - session-2：set commit=0;
    - session-1：update test_innodb_lock set b = '1' where a = 200;(更新，但不提交，对a=200这行加锁)
    - session-2：update test _innnodb_lock set b='2' where a = 200;(不更新，等待锁)、
    - session-1：commit；（提交更新）、
    - session-2：接开阻塞，更新正常进行
  - 无锁引行锁升级为表锁（现象跟表锁一直）
    - session-1：set commit=0;
    - session-2：set commit=2;
    - session-1: update test_innodb_lock set b=2 where b=200;(更新不提交)
    - session-2：update test_innodb_lock set b=3 where b=300;（阻塞，等待锁）
    - session-1：commit；（提交）
    - session-2：接触阻塞，并正常执行
  - 间隙锁带来的插入问题
    - session-1：set commit = 0；
    - session-2： set commit = 0；
    - session-1：update test_innodb_lock set b = a * 10 where a<4 and a > 1（更新，不提交，锁定（1，4）开区间）
    - session-2： insert into test_innodb_lock values(2,3000);(阻塞)
    - session-1：commit；
    - session-2：解锁，正常执行
  - 使用共同索引不同数据的阻塞
    - 数据中存在两条（a=1,b=b1;a=1;b=b2）
    - session-1：set commit = 0;
    - session-2：set commit = 0;
    - session-1：update test_innodb_lock set b = 'b2' where a=1;(更新不提交，锁定两条数据)
    - session-2：update test_innodb_lock set b = 'b5' where a = 1;(阻塞等待)
    - session-1：commit;
    - session-2：解锁，正常执行\
  - 死锁
    - session-1：set commit = 0 ;
    - session-2：set commit = 0;
    - session-1：update ttest_innodb_lock set b = 9 where a = 200;(更新，不提交，行锁)
    - session-2：update test_innodb_lock set b= 10 where a = 300;(更新，不提交，行锁)
    - session-1：update test_innodb_lock set b = 11 where a = 300;(等待session-2释放锁)
    - session-2：update test_innodb_lock set b= 13 where a = 200;(等待session-1释放锁)
    - session-1 和 session-2 进入死锁状态
    - 解决办法：rollback 回滚

事物和MVCC底层原理

事务
- 事务在存储引擎层实现（行锁也是）
事务四大特性
- 原子性（Atomicity）：同进同退
- 一致性（Consistent）：由原子性保证
- 隔离性（Isolation）：多个事务操作之间是要相互隔离的，导致脏读、幻读、不可重复读
- 持久性（Durable)：数据持久化到磁盘
事务开启
InnoDB的内存和磁盘结构
- InnoDB内存结构、
  - Buffer Pool缓冲池
    - 作用：将数据页（16k）和索引页（16k）缓存到Buffer Pool
    - innodb_buffer_pool_size：控制缓冲区大小；默认128M 1024 * 1024 / 16 = 8192页
    - LRU淘汰策略
      - Buffer Pool存储多个特链表
        
        空闲链表（free）：未使用缓存页
        
        脏页链表（flush）：发生更改的缓存页
        
        LRU链表（lru）：LRU链表中保存着所有加载到内存Buffer Pool的数据页和索引页。按照最少使用原则，最近使用的排在链表头部，最少使用的排在链表尾部
      - Buffer Pool分区
        
        Young区
        
        热数据，占空间5/8)
        
        old区
        
        冷数据，占空间3/8
        
        新数据首次进入，存在old区链表头部
        
        当数据的两次访问间隔小于一定阈值（innodb_old_blocks_time，默认 1s），会从old区加入到young区
        
        young和old比例调整参数
        
        innodb_old_blocks_pct,默认值37，old占37%
        
        show variables like ‘innodb-_old_blocks_pct’;
        
        set GLOBAL innodb_old_block_pct = 40;
      - LRU特点
        
        表头数据存的是热点数据
        
        young区和old区链表尾部存储的数据是冷数据
        
        新数据来了之后先进入old区的链表头部，但如果新进入数据没有意义，会造成劣币淘汰良币问题
    - 缓冲池选项和变量
      - 查看缓冲池相关参数：show variables like ‘%innodb_buffer_pool%’;
      - 查看服务器运行状态：show status like ‘%innodb_buffer_pool%’;
    - 自适应哈希索引
      - 热点索引页会自动在缓冲池中建立一个热点索引页的自适应hash，提高访问速度；默认开启
      - show variables like ‘innodb_adaptive_hash_index’;
    - change buffer
      - 一种特殊的数据结构，针对二级（辅助索引）索引页的优化措施
      - 针对insert update delete添加缓冲
        
        二级索引页的DML操作，并且这个索引页没有在Buffer Pool内，那么把这个操作存入change buffer
        
        下次需要加载这个二级索引页的时候，索引页被加载到Buffer Pool中，change Buffer内的更改合并到Buffer Pool
        
        随后当服务器操作空闲的时候，这个更改刷新到磁盘上
      - 参数
        
        控制change buffer中缓存哪些操作类型 innodb_change_bufferffering
        
        show variables like ‘innodb_change_buffering’
        
        all：默认全部缓冲
        
        none：不要缓冲任何操作
        
        inserts：缓冲插入操作
        
        deletes：缓冲删除标记操作
        
        changes：缓冲插入和删除标记操作
        
        purges：缓冲在后台发生的物理删除操作
        
        控制change buffer大小 innodb_change_buffer_max_size
        
        占缓冲池总大小的25%
        
        数据库大部分索引是二级索引（辅助索引），并且有大量插入，可以调大这个值
        
        数据库·大部分索引是主键索引，读多写少，可以调小这个值
  - Log Buffer（内存存储的是Redo Log Buffer）
    - 执行修改操作
      - 从磁盘读盘将索引页和数据页加载到InnoDB内存
      - 执行修改操作，InnoDB产生脏页（Buffer Pool的脏页链表），将修改操作记录到redoLog日志
      - redo log 落盘
        
        查看何时落盘参数innodb_flush_log_at_trx_commit
        
        Force Log At Commit（当事务提交时，强制事务落盘）、WAL（顺序预写redo log日志到 redo log Buffer）
        
        设置参数为0：MySQL每秒一次将数据从log buffer写入日志文件（OS Buffer）并同时fsync刷新到磁盘中；MySQL崩溃或者服务器宕机，此时内存中的数据会丢失，最多丢失1s；
        
        设置参数为1：每次事务提交时，MySQL将数据从log buffer 写入磁盘日志文件并同时fsync刷新到磁盘；此为系统默认，不会造成数据丢失，保证ACID
        
        设置参数为2：每次提交事务时，MySQL将数据从log buffer写入日志文件(OS Buffer)；MySQL每秒执行一次持久化。MySQL崩溃或者服务器宕机，此时内存中的数据会丢失，最多丢失1s；
        
        redo log落盘是顺序写
        
        redo log对应的物理文件位于数据库的数据目录下的ib_logfile1和ib_logfile2
        
        指定日志文件所在路劲g：innodb_log_group_home_dir=./
        
        两个日志文件循环写入：innodb_log_files_in_group=2
        
        每个重做日志大小：innodb_log_files_size=16777216（48M）
        
        redo log作用：就是为了当脏页落盘失败时，用来进行数据恢复
      - 脏页落盘
        
        脏页落盘是随机写
        
        保证脏页落盘的安全性WAL(预写redo log日志)
        
        何时脏页落盘（见下面标题 5）
        
        double write保证数据页的安全性
        
        MySQL和操作系统之间交互 16K
        
        操作系统和磁盘之间交互是 4K
    - bin log（是 server层实现的）
- InnoDB磁盘存储结构
  - 表空间
    - 系统表空间（共享表空间；磁盘文件名称：ibdata1）
      - 所有表共享一个表空间；只增不减的
      - 参数设置
        
        innodb_data_file_path=设置磁盘地址：大小：autoextend(自动扩展)
      - 组成
        
        InnoDB数据字典
        
        change buffer
        
        Double Write
        
        Undo logs
        
        Undo log保证事务的原子性、隔离性；保证原子性就是保证一致性
        
        redo log实现事务的持久性
        
        用户表空间
        
        也是独立表空间，只2存储表数据、索引和插入缓冲BITMAP页信息；其余回滚、双写缓冲还是放在系统表空间
    - 独占空间
      - 每个表一个表空间
      - innodb_file_per_table(0：关闭独占表空间，1：开启独占表空间；建议开启)
    - 通用空间
    - 临时表空间
    - Undo表空间
      - 默认关闭，数据存储到系统表空间；需要开启
      - 建议开启
  - redo log
    - 实现事务的持久性
    - 默认是2个相同大小的文件
    - redo log是循环使用的
    - redo log中记录的是数据页的物理修改信息、
    - redo log中的数据只是为了保证数据宕机或意外关机时，数据恢复的。平时没有用处
    - redo log根据checkpoint进行擦除
InnoDB的事务原理分析

事务原子性、一致性、持久性
- 内存数据落盘分析
- 脏页落盘过程
  - 持久性：事务提交的时候，数据必须持久化到磁盘中
  - 通过WAL（顺序预写redo log日志）和Force Log At Commit（当事务提交时，强制事务落盘）机制保证事务持久性
  - 通过redo log buffer来提升redo log 的写入效率（何时写通过innodb_flush_at_trx_commit 0\1\2）
  - checkPoint（检查点机制）
    - 检查点机制目的
      - 缩短数据库的恢复时间
      - 缓冲池不够用时，将脏页刷新到磁盘
      - 重做日志不可用时，刷新脏页
    - - redo log中的数据，对应的脏页都是没有落盘，脏页落盘成功之后，会清楚对应的redo log内容
      - LSN记录最老被更新的数据和最新被更新的数据；
      - LSN标记标记版本
    - CheckPoint分类
      - Sharp CheckPoit （清晰检查点）：在关闭数据库时，将buffer pool中的脏页全部刷新到磁盘中
      - Fuzzy CheckPoint（模糊检查点）：数据库运行时，不同时机，将部分脏页写入磁盘，仅刷新部分脏页到磁盘，避免一次刷新引起性能问题
      - Master Thread CheckPoint
        
        在Master Thread中，会以每秒或每10秒一次的频率，将部分脏页从内存中刷新到磁盘，这个过程是异步过程；正常用户线程对数据的操作不会被阻塞
      - FLUSH_LRU_LIST checkPoint
        
        当这个空间页面数量不足的时候，发生FLUSH_LRU_LIST checkPoint。在单独的page_cleaner线程中执行
        
        InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。
        
        可以通过innodb_lru_scan_depth控制LRU列表中可用页数的数量，该值默认为1024
      - Async/Sync Flush CheckPoint
      - Dirty Page too much
  - Double Write双写
    - 解决脏页落盘时部分写失效问题
    - - 第一步：将内存中的脏页（memcopy）拷贝到一块叫double write buffer的内存空间中，内存大小为2M
      - 第二步：将double write buffer顺序写入磁盘共享表空间（Doublewrite 连续的128个数据页），每次1M
      - 第三步：将内存double write buffer中的脏页随机写入磁盘数据文件
      - 第四步：当出现部分写失效问题的时候，会从DoubleWrite文件中恢复对应的数据。
- Undo Log
  - 保证原子性、一致性
  - 支持事务回滚
  - 支持事务隔离MVCC
  - 写undolog写的是undo page也是数据页，关于数据页的修改会产生redo log日志；redolog记录需要些的数据页是哪个
- 事务流程总结
  2. 事务开始
  3. 查询待更新的数据到内存，并加写锁
  4. 记录undo log相关的redo log到缓冲（记录redolog - undo log page（undo log是存储在buffer pool中））
  5. 记录undo log到缓冲（先做undo log 操作）
  6. 记录数据变更的redo log 到缓冲（记录redo log - 数据和索引page）
  7. 内存更新数据，形成脏页（再做内存页的修改操作）
  8. 如果崩溃则回滚
    1. 崩溃
    2. 重启
    3. 获取检查点信息
    4. 使用redo log恢复数据和undo log
    5. 根据undo log进行事务回滚
  9. 事务commit触发redo log刷磁盘，InnoDB每秒也会触发redo log刷磁盘（事务提交 - 触发redolog落盘）
  10. 如果崩溃则恢复后使用redo log恢复数据
  11. undo log页和脏页都依据checkpoint机制刷盘（页的落盘 doublewrite时机）
事务隔离性
- 隔离级别
  1. 未提交读（read uncommit/RU）
    - 脏读：一个事务读取到另一个事务未提交的数据
  2. 提交读（read commit/RC）
    - 不可重复读：一个事务读取到另一个事务已提交的update(隐式提交)；出现一个事务读取到两次数据不一致
  3. 可重复读（repeatable read/RR）
    - 幻读：一个事务读取到另一个事务已提交的delete或insert数据（隐式提交），导致对同一张表读取两次以上的结果不一致
    - MySQL的RR不存在幻读
  4. 串行化（serializable）
    - 以上3种隔离级别都允许对同一条记录进行读-读、读-写、写-读的并发操作，串行化不允许读-写，写-读操作，不存在并发问题
  5. 丢失更新
  6. 总结
    - MySQL一般用的隔离级别是RC和RR
    - 并发控制控制的两种方案：锁机制（serializable）和MVCC多版本控制（RC和RR）
- 版本链
  - 回滚链 /undo log
    - 根据行为的不同，undo log分为两种：insert undo log和update undo log
    - insert undo log
      - 隐藏列：数据库表中每行记录中都有隐藏列（RowId、事务ID、回滚指针）
      - 回滚操作通过回滚指针；inser undo log 的回滚指针是null
    - update undo log
      - 隐藏列：数据库表中每行记录中都有隐藏列（RowId、事务ID、回滚指针）
      - 回滚操作通过回滚指针；
  - 版本链
- MVCC总结
  - MVCC并发控制中，读操作分为当前读和快照读
  - 当前读和快照读
    - 快照读：读取的是记录的可见版本（有可能是历史版本）；简单的select操作，属于快照读，不加锁
      - select * from table where ?;
    - 当前读：读取的记录是最新版本，并且当前读返回的记录都会加锁，保证其他事务不会再并发修改这条记录；特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁
      - select * from table where ? lock in share mode；//共享锁(S)
      - select * from table where ? for update;//排他锁(X)
      - insert into table values(...);//排他锁（X）
      - update table set ? where ?;//排他锁（X）
      - delete from table where ?;//排他锁（X）
  - 一致性非锁定读
    - InnoDB存储引擎通过多版本控制（MVCC）获取当前数据库中行数据的行为。如果读取的行数据正在执行update 或delete操作，这时读取操作不会去等待行上的锁释放，InnoDB会去读取一个快照。
    - RC隔离级别：在读取快照的选择上不一样；RC会优先选择最新数据，如果行被锁定了，则读取行版本的最新一个快照；所以如果事务已经提交，在此隔离界别下上述SQL语句的结果时会不一样。
    - RR隔离级别：总是读取事务开始时的行数据，因此再次隔离级别下，SQL仍然获得相同数据。
- ReadView
  - 可读视图：在这个视图里面的记录的东西才能被快照读和一致性非锁定读读到
  - 核心问题：需要判断一下版本链中的哪个版本是当前事务可见的
  - ReadView 包含当前系统中还有哪些活跃的读写事务Id，并放到一张表里名称m_ids.
  - RC级别
    - 生成ReadView时机：在事务开启之后，每次读数据之前都生成ReadView
    - 数据读取过程：
      - 匹配规则：
        
        如果被访问（要查看）的版本的事务ID(trx_id)小于ReadView的m_ids列表中最小的事务id，表明该版本的事务在生成ReadView之前已经提交，该版本可以被当前事务访问
        
        如果被访问（要查看）的版本的事务ID(trx_id)大于ReadView的m_ids列表中最大的事务id，表明该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问
        
        如果被访问（要查看）的版本的事务ID(trx_id)在ReadView的m_ids列表最大值和最小值之间，则需要判断。如果该被访问的版本的事务ID在ReadView列表中找到相等的值，则说明是活跃事务，不可访问；反之则可以访问。
      1. 系统中有两个活跃事务（ID分别为100、200）正在执行，还有已提交事务（ID为80）
      2. 系统中又有一个新RC事务（ID为300）进行读操作
      3. 新RC事物（ID为300）生成ReadView
      4. ReadView记录两个活跃事务ID（m_ids分别为100，200）
      5. 新RC事物（ID为300）此时分别取访问事务（100,200,80)
      6. 访问100和200这两个事务时，这两个事务的ID都在ReadView记录的两个活跃事务之间，不可访问
      7. 访问100这个事务时，这个事务小于ReadView记录的两个事务，说明该事务已提交，可以访问。等到结果”刘备“。
  - RR级别：存在幻读，在MySQL中解决了幻读
    - 生成ReadView时机：事务开始之后，第一次读取数据时生成ReadView,后面不会再生成（解决幻读）
    - 第一次查询
    - 事务100提交，事务200修改未提交
    - 第二次查询
    - 参照RC流程分析出RR流程

事务总结

持久性（Durable）
- redo log（WAL预习机制 - redo log buffer）、force log at commit机制（0，1，2）
- 扩展点：脏页落盘、checkpoint检查点机制、double write双写机制
原子性(A)、一致性（Conistent）
- undo log：记录事务修改之前的状态（insert undo log/update undo log）
- InnoDB版本链
  - 事务回滚
  - MVCC多版本并发控制
隔离性（Isolation）
- 事务隔离级别：RU、RC、RR、Seriable，MySQL默认是RR
- 事务并发问题：RU（脏读）、RC（不可重复度）、RR（MySQL数据库在这个隔离级别上不存在幻读，其他数据存在幻读）
- MVCC：多并发版本控制，就是通过undo log 版本链和ReadView这个结构体实现；
  - RC和RR的MVCC实现是不一样的；
  - MVCC是为了解决隔离性不从而产生的并发问题，除了MVCC这种问题解决方案，还有就是锁的并发控制
  - MVCC的特点是读不加锁，读写不冲突。
- 快照读和当前读
- 一致性非锁定读：当一条记录正在被修改时，读取这条记录时，使用一致性非锁定读，不等锁定的释放，而选择该条记录的一个版本去读
- ReadView视图：一种数据结构，里面存储的是当前活跃事务ID的链表信息

InnoDB行锁原理分

问题：

t1 (id,name)

SQL1: select * from t1 where id = 10;

SQL2：delete from t1 where id = 10；

id主键+RC
- SQL2
- X锁：对主键索引树当前行加排他写锁
- 加载到InnoDB内存中加锁
id唯一索引+RC
- SQL2
- id不是主键索引，name是主键列
- 辅助索引树一条记录加X锁，主键索引树加X锁
id非唯一索引+RC
- SQL2
- id不是唯一索引，只是普通索引。主键是name列
- 在辅助索引树上多条索引加X锁，主键索引树对应记录加X锁
id无索引+RC
- SQL2
- id列上没有索引；主键是name列
- 对主键索引树的每行记录加X锁，跟加表锁不一样；
id主键+RR
- SQL2
- id列主键
- 对主键索引树当前索引加锁
id唯一索引+RR
- SQL2
- id唯一索引，主键列name
- 对唯一索引树当前索引记录加锁，对主键索引树当前记录加锁
id非唯一索引+RR
- SQL2
- id是非唯一索引，主键列name
- 辅助索引树加GAP锁和每行加X锁；对主键索引树每行加X锁，不加GAP锁；（主键索引树只有在范围查找时才加GAP锁，索引下推）
- 防止幻读，加间隙锁，不能再间隙内修改数据。
id无索引+RR
- SQL2
- id没有索引
- 对整个唯一索引树的行加X锁及加间隙锁。
Serilizable
- 简单SQL：select * from t1 where id = 10；
  - 在RC、RR隔离级别下是快照读，不加锁。
  - 在Serializable隔离级别下加锁，MVCC并发控制降级为Lock_Based CC.

一条复杂SQL的加锁分析

加锁分析

- 分析上图：
  - 在Repeat Read隔离级别下，由Index_key所确定的范围，被加上GAP索引；
  - Index_Filter锁给定的条件（userid='hdc'）何时过滤，视情况而定
    - MySQL5.6版本之前，不支持ICP(Index Condition Push)索引下推，因此在MySQL Server过滤
    - MySQL5.7版本之后，支持Index Condition Push
      - 若不开启ICP，在Index_Key范围内，不满足Index Filter的记录也需要X加锁
      - 若开启ICP，在Index_Key范围内，不满足Indx Filter的记录不需要加X锁。
  - 总结：需要被加载到MySQL Server层的记录，才需要被加锁
死锁的原理和分析
- 两个会话（session）执行两个SQL产生死锁
- 两个会话（session）执行一个SQL产生死锁
  - session1：update t2 set comment='abc' where name='hdc';
  - session2: select * from t2 where pubtime > 5 for update;
- 如何解决死锁
  - MySQL默认会主动探知死锁。并回滚某一个影响最小的事务，等待另一个事务执行完成之后，再重新执行该事务。
- 如何避免死锁
  1. 两个表的更新顺序，交替更新造成死锁
    - Transaction 1：更新表A -> 更新表B
    - Transaction 2：更新表B -> 更新表A
  2. 保持事务的轻量
    - 越是轻事务，占有越少的锁支援，发生几率小
  3. 提高运行的速度
    - 避免使用子查询，尽量使用主键等
  4. 尽量提交事务，减少持有锁的时间
    - 越早提交事务，锁越早释放
原理分析和总结

MySQL性能分析篇

索引性能分析步骤
- 收集慢SQL，
  1. 使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
  2. 使用information_scheman库的PROCESSLIST表实时收集当前执行比较慢的SQL-use information_scheman;
- 【查看执行计划】使用explain工具分析有问题的SQL的执行计划
- 最后可以使用【show profiles】查看有问题SQL的性能使用情况
慢查询日志
- 开启慢查询功能
  - 慢查询参数查看：show variables like “%slow_query%”;
  - 慢查询执行时间阈值：show variables like“%long_query_time%”；
- 临时开启慢查询日志
  - 在控制台执行set命令
    - 开始慢查询日志：set global slow_query_log = on;
    - 慢查询时间阈值：set global long_query_time=1;
    - 慢查询日志文件位置：set global slow_query_log_file='/a/b/c.log';
    - 慢查询记录是否使用索引：set global log_queries_not_using_indexes=1;
    - set global log_output='FILE,TABLE';
- 永久修改慢查询日志
  - 修改/etc/my.cnf配置文件，重启MySQL
    - slow_query_log=1
    - slow_query_log_file=/var/lib/mysql/slow.log
    - long_query_time=1
- 慢查询日志工具
  - mysqldumpslow -s t -t 5 -g 'sleep' /a/b/c.log
  - pt-query-digestpercona-toolkit工具
实时获取由性能问题的SQL
- 通过informatiom_schema.PROCESSLIST实时获取有性能问题的SQL
性能分析工具
- profile性能分析工具

MySQL性能优化分析

MySQL性能分析步骤
- 系统存在性能问题时（不只是数据库存在问题），只是该时间段开启慢查询日志，收集问题SQL
- 针对收集到的问题SQL语句，使用explain命令进行查询计划分析。
- profile 性能分析（硬件或者cpu级别的性能分析）
服务层面优化
- 硬件相关（了解）
- 操作系统相关（了解）
- MySQL配置参数相关
  - 将数据保存到内存中，保证从内存读数据
    - 设置足够大的innodb_bufffer_pool_size,将数据读取到内存中；建议innodb_buffer_log_size为总内存的3/4或4/5.
    - 怎么确定innodb_buffer_pool_size足够大：show Global status like ‘innodb_buffer_pool_%’;
  - 降低磁盘的写入次数
    - 使用足够大的写入缓存（log buffer）参数名称（innodb_log_file_size = 0.25 * innodb_buffer_pool_size）
    - 设置合适的force_flush_log_at_trx_commit(0,1,2)redo log buffer落盘机制
  - MySQL数据库配置优化
    - 表示缓冲池（Buffer Pool）大小 (innodb_buffer_pool_size)
    - redo log大小设置。innodb_log_file_size 过大，实例恢复时间长；过小，造成日志频繁落盘
    - 控制redo log（存储再log buffer）落盘时机 (innodb_flush_log_at_trx_commit=1)
    - binlog每提交一次事务同步写道磁盘中，可以设置为n（代表提交几次事务落盘一次）：sync_binlog = 1;
    - 脏页占innodb_buffer_pool_size比例，触发脏页落盘，推荐25%-50%。innodb_max_dirty_pages_pct=30%
    - 指定innodb共享表空间大小（innodb_data_file_path）：首先默认使用独占表空间，而undo log相关的数据还是存储到表空间，共享表空间是只增大不缩小的文件。指定共享表空间存储在哪些磁盘，共享表空间文件可以是多个
    - 全量日志建议关闭。general_log = 0
    - 后台进程最大IO性能指标：默认200，如果是SSD调整为5000-20000。innodb_io_capacity=200;
    - 慢查询阈值设置，单位秒。long_query_time=3；一般是根据非功能性需求来决定的。
    - MySQL主从复制形式：基于段复制的格式、基础行的格式、混合格式；行安全性高；binlog_format=row还需要一个配套参数
    - 调整连接数大小，默认200，一般生产建议2000，提高并发能力。max_connection=200,
SQL设计层面
- 数据库设计六范式
- 数据设计经验
  - 需求字段：根据需求文档和需求原型，一对一定出来的字段
  - 系统字段：为了帮助系统更好维护表数据，修改人、修改人时间、添加人、添加时间、逻辑删除字段
  - 状态流程字段：订单状态、支付状态、业务流程字段
- 具体优化方案
  1. 设计中间表，一般针对统计分析功能或者实时性不高的需求（OLAP、OLTP-统计分析）
  2. 为了减少关联查询，创建合理的冗余字段（冗余字段考虑数据一致性问题）
  3. 对字段太多的表，考虑拆表
  4. 对经常不使用的字段或者数据建议拆表
  5. 每张表建议都要有一个主键，而且主键类型最好是int类型，建议自增主键。
SQL语句
- 索引优化
  1. 经常作为where、order by、group by，select的字段添加索引
  2. 创建组合索引，注意组合索引的顺序，筛选力度大的放前面
  3. 尽量使用覆盖索引
  4. 索引长度尽量短，短索引节省索引空间；太长列建立前缀索引
  5. 主键尽量是自增的
  6. 更新频繁的列不建立索引；如果非要创建索引，那就要注意读写分离
  7. order by索引生效，遵循最左前缀原则。where条件之后的索引，优化器会优化，order by之后不会
- Limit优化
  1. 如果查询结果是一条，用limit 1，防止全表扫描
  2. 处理分页用limit （offsize，size）到后面偏移量比较大；
    - 使用条件查询结果再limit
    - 使用子查询
  3. 其他查询优化
    - 小表驱动大表（not in ；not exist的区别）
    - 避免全表扫描
    - 如果全表扫描比使用索引块则放弃索引。（典型场景是数据量比较小）、
    - 尽量不适用count(*)，使用count(主键） count(*)会过滤null值
    - join两个表时，尽量都创建索引，类型一致
    - where条件中尽量不使用1=1，not in语句
    - 尽量不使用MySQL内置函数，不创建索引
    - 在互联网项目中一般不建议使用外键

MySQL集群

集群搭建之主从复制
- ·　　配置好以后，主从同步开始，主数据库将更新的语句（数据库的CRUD、DDL）写入binlog，从服务器通过I/O线程去主数据库读取binlog日志，并且写入到从库的relay log（中继日志）中，然后从服务器的SQL Threa会一个个执行relay log里面的sql。
  - 解决数据单点问题、数据备份、数据恢复、数据库负载、异地容灾等
  - 主对外提供读写操作，从是用来备份
  - 主从复制的前提是基于binlog日志实现的，所以必须开启binlog日志
- 主从复制原理
  - binlog日志和relay日志
    - binlog默认不开启需要手动开启
    - binlog是事务提交才会写入，binlog日志是属于MySQL Server层的，不是存储引擎层实现的
    - binLog日志格式：基于行（row）的、基于段（statement）的、还有混合（mix）的
    - 建议使用row的，同时设置binlog_row_image属性full|blob|minimal。建议设置为minimal
- 主从同步延迟问题解决
  - 从服务器增加线程从主服务器读取binlog日志
  - 从机只作为备份用
分库分表
- 为什么分库分表
  - 表的数量达到几百上千表时，考虑分库
  - 表的数据达到几千万级，进行分库分表（IO瓶颈、网络瓶颈、索引瓶颈）
- 数据切分方案
  - 垂直切分：按照业务模块切分
    - 垂直分库
      - 基于业务分类，跟微服务治理概念类似，每个服务有自己独立的数据库
    - 垂直分表
      - 基于数据库表的列为依据切分，且一种大表拆小表的模式。
    - 优点
      - 业务间解耦，不同的业务数据进行独立的维护、监控、扩展
      - 在高并发场景中，一定程度上缓解了数据库的压力
    - 缺点
      - 提高开发的复杂度，由于业务的隔离性，很多无法直接访问的必须通过接口方式聚合数据
      - 分布式事务管理难度增加
      - 数据库还是存在单表数据量大的问题，需要配合水平分表
  - 水平切分：将一张大表按照一定的切分规则，按照行切分成不同的表或者切分到不同库
    - 库内分表
      - 数据库内将一类表切分成多个子表
    - 分库分表
      - 将切分出来的子表，分散到不同的数据库中，从而使得单个表得数据量变小，达到分布式的效果。
      - 优点
        
        解决高并发时单库数据量过大的问题，提升稳定性和负载能力
        
        业务系统改造的工作量不是很大
      - 缺点
        
        跨分片的事务一致性难以保证
        
        跨库的join关联查询性能查
        
        扩容的难度和维护量大
- 切分规则
  - 按照ID hash()取模
  - 按照日期范围
  - 按照范围
- 切分原则
  - 能不切则不切
  - 如果切分一定要选择合适的切分规则，提前规划好
  - 数据切分尽量通过数据冗余或者表分组来降低跨库join的可能
  - 由于数据库中间件对数据join实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量少使用多表join
分库分表解决的问题
- 分布式事务问题
  - 强一致性事务（同步）
  - 最终一致性事务（异步思想）
- 分布式主键ID生成方案
  - redis incr命令
  - 数据库（生成主键）
  - UUID
  - sonwflake算法（https://www.sohu.com/a/232008315_453160）
- 跨库join问题
  - 通过业务分析，将不同库的join查询拆分成多个select
  - 建立全局表（每个库都有一张相同的表，例如省市区、字典表）
  - 冗余字段
  - E-R分片（将有ER-关联关系的记录都存储到一个库中）
  - 最多支持跨两张表跨库的join
- 分库分表实现技术
  - sharding-jdbc（当当）
  - MyCAT（基于Cobar）