MYSQL面试系列-03

MYSQL面试系列-03

–10. 说说你知道的索引算法
BTree, Hash
Mysql 中使用的是BTree 的变种 B+树索引

BTree 索引即可用于等值查询，也可用户范围查询

Hash 索引只能用于等值查询

–11. 事物的四大特性(ACID)介绍一下，在mysql中4大特性的体现是由哪个功能模块完成？

原子性：操作数据库的一系列操作，事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
UNDO日志保证事务的原子性

一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
整体功能实现都是为了一致性的实现

隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
锁和隔离级别实现innodb的隔离性

持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
REDO 日志可以保证innodb的持久性

–12.什么是事务的隔离级别？MySQL的默认隔离级别是什么？
为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，
这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读或幻读.

READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是不可重复读或幻读仍有可能发生。

REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
而innodb存储引擎可以利用NextKeyLock解决幻读问题。

SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，
也就是说，该级别可以防止脏读、不可重复读以及幻读。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用RR

–13.什么是脏读？幻读？不可重复读？
脏读(Drity Read)：读到了其他事务未提交的数据叫做脏读。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间另一个事务更新的原有的数据，注重更新逻辑。

幻读(Phantom Read):在一个事务的两次查询中数据条数不一致，例如有一个事务查询了几行(Row)数据，而另一个事务却在此时插入了新的几列数据，
先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。主要针对的是插入数据的逻辑。

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

–14. MySQL 加锁处理分析
https://www.bilibili.com/video/BV11K421y7TF/?spm_id_from=pageDriver&vd_source=98d4a1bb64fff3d3cace7ee54357de77
锁分类
表锁、页锁、行锁
记录锁 永远加在索引的叶子节点上，就算表没有建索引，也会隐式创建一个索引。如果where条件是二级索引，那么记录锁会加在二级索引和对应的聚簇索引上。
where条件为主键 在表加IX锁，主键相应记录加记录锁
where条件为二级索引 在表加IX锁，二级索引相应记录加记录锁，主键相应记录加记录锁
无where条件 在表加IX锁，所有记录加锁
where条件不是索引列 在表加IX锁，第一阶段所有记录加锁，第二阶段满足要求的记录加锁
gap锁间隙锁或者范围锁 防止其他事务在一个范围内修改数据，基于RR隔离级别，能够解决幻读问题。
where条件为主键 在表加IX锁，主键相应记录范围加gap锁
where条件为二级索引 在表加IX锁，二级索引相应记录加GAP锁

后码锁 在记录锁和间隙GAP锁后加上Next-Key lock.隔离级别必须是RR
where条件为二级索引,且记录不为空 加后码锁（行锁+gap锁+nk锁）

记录锁/gap锁/后码锁小结：
RC级别

1. where条件为唯一索引(主键)，且记录不为空，则加记录锁
2. where条件为非唯一索引，且记录不为空，则加记录锁
   RR级别
   1.where条件为唯一索引(主键)，且记录不为空，则加记录锁
   2.where条件为非唯一索引，且记录为空，则加GAP锁
   3.where条件为非唯一索引，且记录不为空，则加后码锁

插入意向锁2型GAP锁

乐观锁
悲观锁
意向锁
共享锁
排它锁

–锁相关的问题
15.1 死锁和锁阻塞有什么区别
死锁是参与者相互持有对方需要的锁资源而导致大家都处于锁等待状态的现象。
锁阻塞是由于所需要的锁，被其他对象持有，而发生的等待，不存在互相阻塞

15.2如何查看和处理生产上的存在的死锁
死锁相关参数
innodb_deadlock_detect --开启死锁检测(该参数默认开启，检测到死锁存在，自动杀掉其中某个会话，解锁)
innodb_print_all_deadlocks --打印死锁信息到错误日志中
如何查看死锁信息
1)show engine innodb status\G; 会输出数据库中出现的最后一次死锁信息。
2)开启 innodb_print_all_deadlocks 参数(该参数默认关闭)，会把死锁信息记录到error日志中。

15.3 如何解决死锁
开启innodb_deadlock_detect，mysql会自动杀死死锁中某一个会话，自动进行解锁。但是在业务设计时我们应该遵循一些原则，尽量减少死锁
比如，1)按同样的顺序加锁；2)尽量避免大事务；2)事务中持锁多的语句靠后执行，减少持锁时间；

15.4 如何查看和处理生产上的锁阻塞问题
1)如何查看锁阻塞
可以通过如下视图查看实例中是否存在锁阻塞情况
information_schema.innodb_lock_waits
information_schema.innodb_locks
2)如何处理锁阻塞问题
1)按同样的顺序加锁；2)尽量避免大事务；2)事务中持锁多的语句靠后执行，减少持锁时间；

–16.perconal tools相关问题
16.1 innodb 大表表结构变更方案或者pt-online-change-schema原理
工作原理：
如果表有外键，除非使用alter-foreign-keys-method指定特定的值，否则工具不予执行。
(1)创建一个和你要执行alter操作的表一样的空表结构。
(2)执行表结构修改
(3)在原表上创建触发器把新增变更同步到新表
(4)把原表中历史数据copy到表结构修改后的表。
(5)copy完成以后，用rename table新表代替原表，默认删除原表。

16.2 #了解了pt-online-change-schema后，请回答如下问题：
1)为什么需要pt-online-change-schema 工具？
2)pt-online-change-schema 工具有什么缺点？

工具优势及缺陷
优势
1)可以控制主从延迟(变更过程中监控从库延迟，达到阈值则暂停从原表中继续copy数据，但是无法暂停触发器操作)
2)可以避免online ddl 中长时间持有表的排他元数据锁，导致该表其他操作都被阻塞的情况
3)可以随时终止变更操作(但是该终止并未做到无损)
缺陷
1)无法主动暂停变更操作
2)变更时间长
3)变更需要额外磁盘空间(大概3倍表的磁盘空间)
4)主动终止变更，或者达到critical-load终止变更，会删除新表，有可能导致 opeing tables 等待
5)死锁问题
16.3 gh-ost 是由 Github 开发的 Online DDL 工具，使用 binlog(可以从主库/备库上拉取 binlog，过滤之后重新应用到主库上去)
代替触发器来做增量数据同步，这样可以降低主库的负载，异步的执行。

优势:
1)可完全暂停
2)可动态控制
3)可以选定表的cut-over时间

劣势:
1)变更表dml频繁时，使用 gh-ost 变更时间明显增加

16.4 pt-table-checksum
https://blog.csdn.net/shaochenshuo/article/details/56009092

pt-table-checksum 作用
主要用来检查主从数据是否一致，原理即在主库执行把表每行的列通过concat函数进行拼接，然后对拼接的值进行hash，
并把该动作通过binlog传递到从库，从而在从库上也执行相关操作(pt-table-checksum会自动设置所在会话的binlog_format=statment)。
默认主库上每对一个表完成checksum操作后，会等待所有从库对该表完成checksum，然后比对主从库上该表checksum值是否一致来判断数据是否一致。

pt-table-checksum的使用有如下限制：
1)pt-table_checksum需要主库的binlog_format=statement，在开始工作前，它会自动设置会话的binlog_format=statement，
但是如果你的环境是级联架构，中间主库的binlog_format不会被修改(因为参数修改不会记录到binlog)，
所以这种情况下，你可能就无法检查中间主库和它的从库的数据是否一致。
pt-table-checksum在进行checksum前会先检查所有mysql服务的binlog_format(可以通过—no-check-binlog-format)

2)pt-table-checksum默认主库要检查的表在从库都存在，并且同主库表有相同的表结构。
如果要检查的表在从库不存在，或者表结构同主库不一致，那么对该表的checksum会破坏从库复制线程。

16.5 pt-table-sync 原理
https://blog.csdn.net/shaochenshuo/article/details/56009234

对两个库不一致的数据进行同步，他能够自动发现两个实例间不一致的数据，然后进行sync操作，
pt-table-sync无法同步表结构，和索引等对象，只能同步数据。
也可以用来对两个不在一个主从拓扑实例，进行数据sync。

NOTE1:如果是sync主从数据，只有当需要sync的表都有唯一键(主键或唯一索引)，才能使用–sync-to-master and/or --replicate。
(没有唯一键，则只能在desitination上直接修改，而指定–sync-to-master and/or –replicate时只能在主库上修改)，
如果sync主从时没有指定–replicate或者–sync-to-master则所有修改都在从库上执行(不论表上是否有唯一键)
NOTE2:如果pt-table_sync 指定了–sync-to-master 或者 --replicate参数，那么需要主库是基于语句格式的复制(SBR)，
所以该工具可能会修改当前会话的binlog_format=STATEMENT(这需要用户具有super权限)

由于整个系列内容还是挺多的，我看了一下50000字左右，所以建议大家直接去资源站免费下载，具体地址：
https://download.csdn.net/download/king01299/89752708
还请大家多好评，收藏，评论，关注，一键三连哦！！！