mysql基础~基础知识问答

一 二阶段提交与事务
1 目的 为了保证主从一致性
2 二阶段提交

• 事务进入prepare阶段，在这个阶段将redo,undo日志进行刷盘，redo中写入xid
• binlog的prepare和commit阶段 ，在commit阶段会将binlog进行刷盘，binlog中写入xid
• 事务进入commit阶段，清除undo日志，事务在存储引擎层提交，客户端返回成功  

  我们可以发现，事务的binlog是先于事务提交的，收到binlog再反查主库，是有可能查不到数据的

3  恢复细节

• 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
• 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
  • 如果是，则提交事务；
  • 否则，回滚事务.
• 通过redo中的xid去binlog中寻找对应的xid，如果存在则代表事务完成，否则就代表事务不完成需要回滚

二 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的历史版本快照

三 隔离级别

RR 不存在不可重复性读的特点

  1 不可重复性读

    描述: 一个事务内读取表中的某一行数据，多次读取结果不同，重点是修改，只需要锁住符合条件的数据

    解决方式: MVCC机制

  2 幻读

    描述: 一个事务内同一查询读取表中某个范围的数据,多次读取结果不同 ，重点是新增或者删除，需要锁住符合条件以及相近的记录

    解决方式: gap锁定范围的方式阻止插入，防止幻读的产生

  3 锁

1. innodb默认加锁方式是next-key_lock，左开右闭，针对条件查询
2. 对于普通 select 语句，innodb 不会加任何锁。如果想在select操作的时候加上 S锁 或者 X锁，需要我们手动加锁。对于 update、delete 和 insert 语句， 会自动给涉及数据集加排它锁

四 双1 参数

innodb_flush_log_at_trx_commit 调用 fsync()
0 代表mysql每秒刷新到文件系统缓存,然后触发os 刷新(fsync())到硬盘,mysqld进程或者linux的崩溃会导致上一秒钟所有事务数据的丢失。
1 代表mysql每个事务都会写入文件系统缓存刷新到硬盘, 当mysql/linux出现故障时最多损失一个事务(依赖mysql刷新函数,不依赖于系统刷新函数)
2 代表mysql每个事务都会刷新到文件系统缓存,然后os 每秒刷新缓存到硬盘 当mysql挂掉后 最多损失一个事务，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失
sync_binlog

0 依赖操作系统进行sync到文件中
1 每次提交事务都会去强制sync到文件中
N 每提交N次事务会去强制sync到文件中

补充
fsync()和fdatasync()是系统级别的刷新函数,都是安全刷新到磁盘返回才代表事务成功,调整参数的目的是触发频率的不同而已

五  半同步复制

  after_commit 5.6
   1 客户端提交事务
    2 存储层提交事务
    3 sync binlog->slave
    4 收到ack-信息
    5 客户端返回-commitOK
after_sync 5.7(无损复制)
   1 客户端提交事务
   2 sync binlog->slave
   3 收到ack-信息(独立ack应答线程)
   4 存储引擎层提交事务
   5 客户端返回-commitOK
总结
  1 after_commit 在于先在存储引擎提交事务再等待ack信息,假如在发送binlog的时候就发生切换,但是这时候主库已经提交了事务,主库数据丢失,after_sync模式不存在事务丢失现象,但是会增加整体耗时
   2 在存储引擎层提交事务,当前事务看不到,但是其他事务是能发现的

六MySQL online_ddl

一 分类
   1 copy-server-临时表(不可见文件,阻塞读写,kill后会释放空间)
   2 inplace-innodb层(不阻塞读写)
      1 norebuild-在原表进行操作,消耗成本低,由于不涉及表的重建，除创建添加索引，会产生部分二级索引的写入操作外，其余操作均只修改元数据项，即只在原表路径下产生.frm文件，不会申请row log，不会消耗过多的IO，速度通常很快。
      2 rebuild-生成新的临时表,消耗成本高,INPLACE的rebuild table方式和COPY的rebuild table方式类似，都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。
二 常见操作
     1 index操作-inplace-norebuild-(增加索引耗时长主要是为了构建非聚集索引,索引列越大,构建时间越长,但是相比rebuild成本小很多)
     2 column操作
       1 增减普通列-inplace-rebuild
       2 更改列类型-inplace-rebuild-阻塞写
       3 varchar类型扩和缩
          inplace-rebuild 需要5.7+扩目标<256(字节) 否则就会锁表-走COPY
       4 更改列默认值-inplace-norebuild-元数据
   3 自增列和主键
     1 增加主键-inplace-rebuild
     2 增加自增列-inplace-rebuild-阻塞写

 总结 除了针对索引的操作和表和列的元数据操作,剩下的均需要rebuild,不支持inplace的是需要copy

七  ICP特性
   1 在innodb存储引擎层实现索引的二次过滤,ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数
   2 ICP前 index key->table filter
      ICP后 index key->index filter->table filter
   3 只能用于range​、 ref​、 eq_ref、ref_or_null访问方法
     只能用于二级索引(也叫辅助索引)

     并非只有联合索引才可能用到ICP特性,单列索引的in or 也可能用到
  4 补充说明
    Index Key ：MySQL是用来确定扫描的数据范围，实际就是可以利用到的MySQL索引部分，体现在Key Length。
    Index Filter：MySQL用来确定哪些数据是可以用索引去过滤，在启用ICP后，可以用上索引的部分。
   Table Filter：MySQL无法用索引过滤，回表取回行数据后，到server层进行数据过滤。

 八 mysql查询

   1 using index是利用覆盖索引,using where是在server层进行过滤,和是否回表没关系,所以是否回表只需要关注using index即可
   2 using temporary
    1 用到结果集存放子表数据就会出现临时表
    2 先使用内存临时表,如果超过tmp_table_size大小,就使用磁盘临时表
    3 可以观察Created_tmp_tables状态变量值会增加
    4 常见于group by,多表联查情况
3 using filesort
  1 涉及到没有使用索引的排序字段一定会出现using filesort
  2 参数sort_buffer_size,超过参数的限制,则会使用磁盘临时文件进行排序
  3 排序的对象包括临时表和不使用临时表情况,所以(using filesort 和using tempoary并不是一起出现 ）
  4 针对临时表的排序 会先使用using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果。
  5 MySQL filesort有两种使用模式:
    模式1: sort的item保存了所需要的所有字段，排序完成后，没有必要再回表扫描。
    模式2: sort的item仅包括，待排序完成后，根据rowid查询所需要的columns。
4  多表JOIN查询

  1 简单的嵌套查询-simple  Nested-Loop Join

  2 基于缓存的嵌套循环-Block Nested-Loop Join

  3 基于索引的嵌套循环-Index Nested-Loop Join

  4 基于hash的嵌套循环-hash Nested-Loop Join

NLJ和BNL
  NLJ 从驱动表取一条记录,然后同被驱动表进行索引过滤结果,再取下一条记录以此类推

 MYSQL

• Index Nested-Loop Join：利用被驱动表的索引进行过滤
• Block Nested-Loop Join：被驱动表上没有可用的索引,它会先把驱动表的数据读入线程内存join_buffer中(无序数组)，再扫描被驱动表，把被驱动表的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
• hash Nested-Loop Join: 有两个阶段 1 是构建阶段 构建hash表根据每行join字段构建hash值  2 是探测阶段 拿着之前构建的hash表 针对被驱动表每行join字段hash值进行计算 实现精确匹配

   hash join 针对索引无效(索引失效和没有索引),取代BNL(join_buffer内的无序数组,解决无法一次性定位的问题)

九 change buffer
   1 InnoDB在进行DML操作非聚集非唯一索引时，会先判断要操作的数据页是不是在Buffer Pool中，如果不在就会先放到Change Buffer进行操作，然后再以一定的频率将数据和辅助索引数据页进行merge。这时候通常都能将多个操作合并到一次操作，减少了IO操作，尤其是辅助索引的操作大部分都是IO操作，可以大大提高DML性能
  2 什么情况下change_buffer会失效
    1 如果 delete、update 是以普通二级索引做为筛选条件,不使用change buffer
    2 根据普通二级索引进行数据查询不可避免读取数据页
    3 对于唯一二级索引(unique key)，由于索引记录具有唯一性，因此无法缓存插入操作，但可以缓存删除操作
  3 一些要点
    1 change buffer会定期刷新到系统表空间,会定期merge

 4 适合场景
   1   数据库大部分是非唯一索引；
   2   业务是写多读少，或者不是写后立刻读取；

十  MDL锁

 普通的增删查改 申请的MDL读锁

 针对表的DDL操作 申请的MDL写锁

 读写锁本身是互斥的.所以可能出现waiting for meta lock

 十一  MYSQL内存结构

     

 

    buffer_pool(data page(叶子节点页),index page(非叶子节点页,实际也相当于数据页的一种,存放实际数据页的目录信息,指向某些具体的数据页),change buffer,data dicitionary,ahi,lock info)  double_write  redo_log_buffer 

 

 

 十二 刷新脏页时期

   刷脏机制: 1  后台线程周期性刷新  2 LRU列表中少于1024个可用空闲页 3  mysql shutdown 4 到达脏页阈值(max_dirty_pages_pct)  5 redo日志不可用时,需要强制刷脏

   刷脏速率: 通过 innodb_io_capacity 来控制刷新脏页的速率, 可以通过fio压测磁盘获取IOPS值

十三 mysql索引

1 innodb根据存储方式分为聚集索引，要么非聚集索引,聚集索引将数据存储在叶子节点,非叶子节点记叶子节点存储的是对应的主键
2 按照字段特性分为 主键索引 唯一性索引 普通索引 全文索引
3 按照字段个数分为 单列索引和联合索引

十四 gh_ost问题

    1 增加唯一性索引前需要进行唯一值检测,否则会导致重复数据丢失,pt-osc不存在此类问题

十五 分布式数据库

 1  分布式数据库-tidb架构
   TiDB 是 Server 计算层，主要负责 SQL 的解析、制定查询计划、生成执行器。
   TiKV 是分布式 Key-Value 存储引擎，用来存储真正的数据，简而言之，TiKV 是 TiDB 的存储引擎。
   PD 是 TiDB 集群的管理组件，负责存储 TiKV 的元数据，同时也负责分配时间戳以及对 TiKV 做负载均衡调度。
2  分布式数据库-相比MYSQL优势
  1 用户无需关心利用何种分片方法,能实现分布式的数据分布
  2 运维无需关心中间件的高可用方法-能实现服务级别的高可用
  3 有很强的扩展性,能实现节点的扩容和缩容
  4 能实现分布式的资源消耗,比如每个分片节点的分布查询然后进行汇总
  5 后端汇总数据支持数据查询的spark服务

十六 死锁

回滚的成本

• 事务的权重，回滚的undo记录越多，产生的影响就会越大，就不会选择这样的事务进行回滚
• 当事务权重一致是，会选择事务等待队列等待时间短的事务进行回滚

 并发插入死锁

• 事务一插入唯一值后需要针对唯一性索引添加排他锁，事务二 三插入意向锁需要申请排它锁，等待事务一回滚后，事务二和三获得共享锁，但是无法获取需要的排它锁

死锁日志

• lock_mode X locks gap before rec 表示X型gap锁
• lock_mode X locks rec but not gap 标识X型记录锁
• lock_mode X locks gap before rec insert intention waiting 插入意向锁(共享锁) 它解决的问题：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此

十七 B+树与B树

     不同点

• 数据只存在叶子节点中,顺序存储.非叶子节点只存储主键信息
• 叶子节点之间通过指针连接

      优势

• 数据顺序存放，所以无论是区间还是顺序扫描都更快。
• 非叶子节点不存储数据，因此几乎都能放在内存中，搜索效率更高
• 单节点中可存储的数据更多，平均扫描I/O请求树更少(B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少(页大小是固定的16K),指针少的情况下要保存大量数据，只能增加树的高度，导致IO 操作变多，查询性能变差)
• 平均查询效率稳定（每次查询都从根结点到叶子结点，查询路径长度相同）

      缺点 

• 新增数据不是按顺序递增时，索引树需要重新排列，容易造成碎片和页分裂情况  

十八 并行复制

mysql并行复制的原理是基于组提交事务实现的，在同一时间戳内提交的事务，在binlog中是记录相同last_commited的事务，不存在锁冲突，都可以进行并行回放。一句话总结就是主库怎么执行，从库就会怎么执行，在高并发场景下提高了应用事务的效率，减少延时出现的概率 Coordinator线程负责判断事务是否可以并行执行，如果可以并行就把事务分发给WorkThread线程执行。也可以设置主库延时提交，用来提高并行复制的效率(理论上)

基于库 基于组提交 基于writeset

  参数 slave-parallel-type='LOGICAL_CLOCK'

         slave-parallel-workers=8 设置works线程
         slave_preserve_commit_order=ON 设置在从库有序回放(从库需要开启binlog才能支持,建议开启)
         binlog_transaction_dependency_tracking(设置在主库)=commit_order(依赖group commit,默认) writeset(会对事务处理的行数据哈希出一个writeset值，放到一个哈希表里，如果两个事务先后提交，但是处理的行数据没有冲突，即wirteset不一样，就可以有同样的last_committed，在从库可以并行执行)

十九 组提交

1 组提交的目的 1 避免每个事务提交都会锁定一次binlog 2 实现并行复制,实际来讲就是减少锁的成本,提高并发
2 二阶段的binlog组提交
  分为三个阶段(Flush 阶段、Sync 阶段、Commit 阶段)完成binlog 组提交
  flush阶段 完成redo的组提交-flush redo log,write binlog-flush_log_commit=1
  Sync阶段 完成binlog的组提交-sync sync_binlog的参数决定了单个队列内的事务刷新到磁盘的方式 可能是N个 可能是1个 也可能依赖操作系统
  Commit 依次在存储引擎层进行commit-遍历队列中的事务，逐一进行innodb commitcon

二十 慢日志收集系统

  1 percona-digest   2  elk 日志分析系统 3 pmm2.0-clickhouse