MySQL 问题总结

什么是MVCC？ 说说MySQL实现MVCC的原理？

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。
对于「读已提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读已提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个Read View，然后整个事务期间都在用这个 Read View。
基础知识：
creator_trx_id：创建该Read View的事务的事务id
m_ids：创建Read View时，当前数据库中 活跃且未提交的事务的id列表。活跃事务”指的就是，启动了但还没提交的事务。
min_trx_id：创建Read View时，当时数据库中活跃且未提交的事务中最小的事务的id，也就是 m_ids 的最小值。
max_trx_id：创建Read View时，当前数据库中应该给下一个事务的id值，也就是全局事务中最大的事务 id 值 + 1；
聚簇索引记录中的两个隐藏列：trx_id和roll_pointer
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8MLAi8bT-1677727928848)(en-resource://database/2289:1)]

• trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里
• roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。
  规则：
• 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
• 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
• 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
  如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及关系？

Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的；
Undo log 记录的是数据操作前的样子
redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）

MyISam和InnoDB区别区别：

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务； 2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
2. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
   4.InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；那么为什么InnoDB没有了这个变量呢？ 因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。 如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
3. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
4. MyISAM表格可以被压缩后进行查询操作
5. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁； InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
   8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
   9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI。 Innodb：frm是表定义文件，ibd是数据文件；Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

说下MySQL的索引有哪些吧？

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
按物理存储分类可分为：聚簇索引、二级索引（辅助索引|非聚簇索引）。
按字段特性分类可分为：主键索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

什么是B+树？为什么B+树成为主要的SQL数据库的索引实现？

什么是B+Tree？
B-Tree又叫做B树，和平衡二叉树不同的地方在于B树是多叉树(平衡多路查找树)；B+Tree是B树的变种，基于BTree 和叶子节点顺序访问指针进行实现，它具有BTree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。在 B+Tree 中，一个节点中的 key 从左到右非递减排列。
为什么B+树成为主要的SQL数据库的索引实现？
二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点。当每次插入的元素都是二叉查找树中最大的元素，二叉查找树就会退化成了一条链表，查找数据的时间复杂度变成了 O(n)；为了解决二叉查找树会在极端情况下退化成链表的问题，后面就有人提出平衡二叉查找树（AVL 树），
主要是在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度差不能超过 1（自平衡），然而不管平衡二叉查找树还是红黑树，都会随着插入的元素增多，而导致树的高度变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率（根本原因是因为它们都是二叉树，也就是每个节点只能保存 2 个子节点）。
为了解决降低树的高度的问题，后面就出来了B树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。因为节点数据量的问题，B树过渡到了B+树。
索引的核心是，提高每次io的有效数据，因为io相对于cpu、内存来讲，速度差着数量级。B+Tree是B TREE的变种，B TREE能解决的问题，B+TREE也能够解决（降低树的高度，增大节点存储数据量）

1. B+树的中间节点不存储卫星数据，所以同样大小的磁盘页可以容纳更多的节点元素，如此一来，相同数量的数据下，B+树就相对来说要更加矮胖些，磁盘IO的次数更少
2. 由于只有叶子节点才保存卫星数据，B+树每次查询都要到叶子节点；而B树每次查询则不一样，最好的情况是根节点，最坏的情况是叶子节点，没有B+树稳定
3. B+树天然具备排序功能，由于B+树所有的叶子节点数据构成了一个有序链表，在查询范围区间数据的时候会更加方便，数据紧密性很好高；
4. B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，而B树需要对每一层进行遍历，所以B+树更有利于全表扫描；
   公众号

事务的ACID属性为保证数据库的一致性，在事务处理之前和之后，都要遵循某些属性，也就是大家耳熟能详的ACID属性

• 原子性（Atomicity）:即不可分割性，事务中的操作要么全不做，要么全做；通过 undo log （重做日志）来保证的；
• 一致性（Consistency）:一个事务在执行前后，数据库必须满足正确的状态，满足完整性约束；通过其余三个特点实现
• 隔离性（Isolation）:多个事务并发执行时，一个事务的执行不应该影响其它事务的执行；通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 持久性（Durability）:事务处理完成后，对数据的修改是永久性的，即便系统故障也不会丢失；通过 redo log（重做日志）来保证的

并发事务处理带来的问题

1. 脏读：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。概括为：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
2. 不可重复读： 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现 其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。概括为：事务A读取到了事务B已经提交的修改的数据。(核心思想是读到了已经提交的数据，但是和第一次读到的不一样)
3. 幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。概括为：事务A读取到了事务B提交的新增数据，不符合隔离性

数据库隔离级别

• 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
• 串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

隔离级别	脏读	不可重复读	幻读
读未提交(Read Uncommitted)	可能	可能	可能
读已提交(Read Committed)	不可能	可能	可能
可重复读（Repeatable Read）	不可能	不可能	可能
串行读（Serializable）	不可能	不可能	不可能

MySQL 锁的类型有哪些呢？

MySQL锁可以按模式分类为：乐观锁与悲观锁。
按粒度分可以分为全局锁、表级锁、页级锁、行级锁。
按属性可以分为：共享锁、排它锁。
按状态分为：意向共享锁、意向排它锁。
按算法分为：间隙锁、临键锁、记录锁。
按粒度
全局锁：对整个数据库实例加锁，应用在全库逻辑备份
表锁：一种是表锁，一种是元数据锁（meta data lock，MDL)。MDL 不需要显式使用，在访问一个表的时候会被自动加上，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。风险点在于加了写DML，之后的读DML全部被阻塞
页级锁：是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁。
行级锁：级锁是粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。MySQL中只有InnoDB支持行级锁，行级锁分为共享锁和排他锁。记录锁（锁的是索引，如果不是索引，加的排它锁就不是rl）、间隙锁（范围内的没有索引的空间）与 Next-Key Lock（前面的合体，锁住行和之间的空隙-左开右闭）。锁的是索引，索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
深入了解mysql–gap locks,Next-Key Locks
按照模式
乐观锁：乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。
适用于读多写少。

#通过加了一个版本号
update set status=支付成功,version=version+1 where id=#{id} and version=#{ version};

悲观锁：具有强烈的独占和排他特性，每次去拿数据的时候都认为别人会修改，在整个数据处理过程中，将数据处于锁定状态。
适用于并发量不大、写入操作比较频繁、数据一致性比较高的场景。
在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交（这样多个更新不是一块锁了）。
共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴
悲观锁
按照属性
共享锁：又称之为读锁，简称S锁。如果事务T对数据A加上排它锁后，就只允许事务T对数据A的读取、修改，其他事务对数据A不能再加任何锁，也不能读取和修改数据A，直到事务T释放A上的排它锁。通过普通读和共享读都可以读出来数据。

select …lock in share mode

排它锁：又称之为写锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读写，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。
MySQL InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。提交事务或回滚事务就会释放锁。

select …for update

按状态分
意向共享锁、意向排它锁。意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。
当有事务A有行锁时，MySQL会自动为该表添加意向锁，事务B如果想申请整个表的写锁，那么不需要遍历每一行判断是否存在行锁，而直接判断是否存在意向锁，增强性能。
注意：普通查询没有任何的锁机制
MySQL锁

MySQL主从复制？

主要涉及三个线程: binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。
• I/O 线程 : 负责从主服务器上读取二进制日志，并写入从服务器的中继日志中。
• SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句
  全同步复制主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。
  半同步复制和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成

MySQL读写分离方案?

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
• 增加冗余，提高可用性。
  读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器

分库分表怎么做的？

确定userId 作为sharding key，通过基因法（id构成最后3位决定，取模8；4位，取模16），保证其它非sharding key 。hash一致性分库分表、
直接32库（逻辑库）32表1024张表，固然可以一步到位，但是对于小公司来说前期根本用不上，浪费机器且增加系统复杂度，所以还是循序渐进，按照一致性hash算法的思想，先确定总的节点数为32
基因法：分库基因，假如通过 uid 分库，分为 8 个库，采用 uid%8 的方式进行路由，此时是由 uid 的最后 3bit 来决定这行 User 数据具体落到哪个库上，那么这 3bit 可以看为分库基因。
一致性hash