最近读了一本书《mysql是怎样运行的》，读完后在大体上对mysql的运行有一定的了解。在以前，我对mysql有以下的为什么：

• InnoDB中的表空间、段、区和页是什么？
• redo log为什么就能实现事务的持久性？
• 到底什么是意向锁？意向锁有什么用？
• mysql中的外连接、内连接到底是什么？
• 事务中的一致性到底是什么意思？一致性和原子性有什么不一样？

现在我对这些为什么都有了答案，下面说说我看书后的个人理解。

以下都是以InnoDB而言。

问题：InnoDB中的表空间、段、区和页是什么？

什么是页？为什么要有页？

• 假设没有页，mysql和磁盘间的交互是这样的： 每当有一条数据改动，都要进行磁盘IO。如果修改的数据很多，那么要访问多次磁盘，性能急剧下降。
• 此时就会有一个想法“那么如果在访问磁盘时，能一次性修改多条数据就好了”。 所以有了页。在一页中可以存储多条数据。
• 有了页之后，mysql和磁盘间的交互是以页为单位的。而不是一条数据为单位。那么就能提升性能。

什么是表空间？为什么要有表空间？

• 假设没有表空间，数据是存放在页中的，如果要定位某一条数据，就要遍历所有的页，性能低。
• 此时就会有一个想法“如果给这些页弄一个类似于目录的东西，这样就能快速定位到数据所在的页了”。所以有了表空间。一个表空间可以存放多个页。
• 表空间是一个抽象的概念，对应着文件系统上一个或多个文件。
• 表空间是用来管理页的，一个表空间由许许多多个页组成。数据存放在某个表空间的某个页中。
• 表空间有许多类型，如系统表空间、独立表空间、通用表空间、undo表空间、临时表空间。最常用的是系统表空间和独立表空间
  • 系统表空间： 在mysql5-6之间，mysql表中所有的数据都是存放在系统表空间中的。
  • 独立表空间： 在mysql7以后，一个mysql表就对应着一个独立表空间。

什么是区？为什么要有区？

• 首先，一个页的大小是16K，而一个表空间可以存64T的数据。因此如果单靠一个表空间就想管理全部页，那么是很困难的，这种做法类似于1个人直接管理1个亿的团队。
• 此时就会有一个想法“既然一个表空间不好管理所有的页，那么可以委派出去，建立几个管理层，形成表空间-->管理层-->页的管理关系就好了”，所以有了区。
• 其次，在InnoDB中，数据是存放在代表聚集索引的B+树的叶子结点内的。一个叶子结点就是一个页。而mysql对B+树进行了改进，使得叶子结点之间形成一个双向链表。因此要进行范围查询的话，只需要找到最小满足条件的记录所在的叶子结点，然后沿着双链表遍历即可。那么问题就来了，如果叶子结点（页）的之间的物理位置距离特别远，那么遍历双向链表就是随机IO，性能低。
• 此时就会又有一个想法“如果B+树中的叶子结点的物理位置是相邻的，那么就不会产生随机IO，而是顺序IO”，所以有了区。
• 一个区保证了64个页的物理位置连续，因此在这个区内对页面进行范围查询时是顺序IO。
• 一个区能存64个页，也就是一个区默认1M大小。

什么是段？为什么要有段？

• 首先，如果把B+树段所有叶子结点和非叶子结点都放到一个区内，假设区内的 非叶子结点的数量 > 叶子结点的数量 那么即使有了区，但是进行范围查询时，性能也大打折扣，因此要扫描的页太少了。
• 此时就有一个想法“如果把叶子结点和非叶子结点单独放一个区就好了”，所以有了段。
• 此次，如果一个范围查询中涉及到了多个区，假设区之间的物理位置很远，遍历区时就是随机IO，性能低。
• 此时又有一个想法“让B+树中结点所在的区之间物理位置连续就好了”，所以有了段。
• 在一个段中，其所有的区物理位置连续且都存放相同类似的页，也就是说一个索引会有两段，一个叶子结点段，一个非叶子结点段。
• 注意：并不是所有的区都会被段管理。有一些区是直接被表空间管理的。

有了段之后带来的问题： 一个表默认都会有聚集索引，那么也就是默认有两个段，而一个段是以区为单位去分配内存的，一个区默认占1M存储空间，那么一个普通的小表也需要用到2M的存储空间？

分析： 问题的原因在于段是只有一个结点类型的区，一个段内的页只存储同种类型的数据，即使有空闲页，那么不会另为他用。

解决： 使用碎片区。

• 碎片区也就是不纯粹的区，里面可以存叶子结点和非叶子结点。
• 也就是在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：
  • 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
  • 当某个段已经占用了32个碎片区的页之后，就会以完整的区为单位来分配存储空间。

有了碎片区以后，段不能仅定义为是某些区的集合，更精确的应该是某些家散的页面以及一些完整的区的集合。

总结；

• 在InnoDB中，存储数据的单位是页，但是由于页过多，因此有了表空间来进行管理。
• 但是表空间管理不过来这么多页，因此有了区来对页进行直接管理，而表空间对区进行直接管理。
• 如果B+树中的结点都堆到一个区内，性能会下降，因此有了段，段对区进行直接管理，而表空间对段进行直接管理。
• 但是由于并不是所有的区都会被段管理。有一些区是直接被表空间管理的。所有形成了以下两条管理链：
  • 表空间-->区-->页
  • 表空间-->段-->区-->页

表空间、段、区、页与B+树的联系：

• 在创建好一张表后，默认会有聚集索引，因此B+树存在，因此会有叶子结点段和非叶子结点段。
• 表中数据量少时，插入一条数据，会以碎片区中的页来分配存储空间。
• 表中数据量大后，插入一条数据，会先分配一个区，然后再从区中的页来非配器存储空间。
• 这张表的所有段、区、页都归表空间直接或间接管理。

问题：redo log为什么就能实现事务的持久性？

什么是Buffer pool？

• 如果mysql每次改动数据都直接去修改磁盘中的数据，即有一条数据出现改动就要访问一次磁盘，那么收到磁盘IO的影响，性能是很低的。
• 此时有个想法“如果把需要修改的数据提前缓存起来，要修改时直接改，改完之后，过一段时间后，修改过的数据可能有多条，那么此时再统一应用到磁盘上就好了”，所以有了Buffer pool。
• 每次访问mysql时，都是先访问buffer pool中的数据页。如果要对某条记录进行修改，那么就会先修改buffer pool中缓存好的数据页。等一段时间后，Buffer pool通过后台线程再把变更过的数据（脏页）刷新到磁盘中。

为什么要有redo log文件？

• 假设没有redo log，在事务提交后，Buffer pool中缓存的数据是已经修改完毕了，但是磁盘中真正的数据还没继续修改，操作结果返回给用户。一段时间后，脏页才会被刷新到磁盘中，如果刷新时出现问题（例如刷着刷着，mysql宕机了）或者还没等到Buffer pool刷新数据时，mysql就已经宕机了，就会出现数据不一致了，用户收到修改成功的结果，而磁盘上的数据并没有修改。事务的持久性就被破坏了。
• 此时有个想法“如果把事务中对数据所做的操作给记录下来，在mysql重启后，重新执行这些记录，这样就不怕因Buffer pool中的数据没有刷新到磁盘上而导致事务的持久性被破坏了”，因此有了redo log文件。
• 事务持久性被破坏的原因在于提交事务（事务中对数据进行了修改）的时间 与 刷新Buffer pool数据到磁盘上的时间不一致。这段时间间隔内，可能会出现各种问题，导致Buffer pool的数据丢失，从而造成了明明修改了，但是磁盘上的数据没有变动的现象。
• 那么只要消除这个时间间隔，事务的持久性就能得到保障了。也就是说在提交事务时就把该事务对数据所做的操作给记录到redo log文件中，那么就不怕隔了一段时间后Buffer pool刷新脏页时，或Buffer pool还没刷新脏页时因为各种问题，导致的数据不一致了。因为在事务提交的瞬间，redo log文件就已经在磁盘中记录了其对数据的操作。

什么是redo log buffer？为什么要有redo log buffer？

• 开启一个事务后，每对数据进行一次修改，都会生成一条redo log日志。也就是说一个事务可能会产生多个redo log日志，而redo log日志是要记录到磁盘上的redo lo文件中的，那么在事务中每进行一次数据修改，就访问磁盘，对磁盘上的redo log日志进行写操作。性能很低。
• 此时有个想法“如果在事务未提交时先把其生成的redo log日志缓存起来，等事务提交的瞬间在记录到磁盘上的redo log文件就好了”，所以有了redo log buffer。
• 事务提交的瞬间会把redo log buffer中的redo log刷新到redo log日志中，因为是顺序IO，速度极快，所以不必担心还没刷新时就出现了mysql宕机，导致redo log日志丢失，从而事务持久性被破坏的问题。

问题：到底什么是意向锁？意向锁有什么用？

什么是X锁、S锁？

• InnoDB的锁根据粒度分为全局锁、表锁、行锁。
• 而根据锁的类型分为了独占锁（X锁），共享锁（S锁）。
  • 独占锁（X锁）：为写操作而存在。X锁与X锁互斥，X锁与S锁互斥。
  • 共享锁（S锁）：为读操作而存在。S锁之间不互斥。
• 因此表锁可以有X型表锁、S型表锁，而行锁也可以有X型行锁、S型行锁。

什么是意向锁？为什么要有意向锁？

• 在一个事务A中当对表中的某条记录加了行锁（X型或S型）后，若其他事务B想对该表加表锁了。那么假设事务A加的是X型行锁，而事务B加S或X型表锁。事务B的加锁操作是会被阻塞的。因为X型行锁的存在。那么问题来了，事务B加表锁时怎么知道这个表里有没有行锁？如果有，行锁有几个？行锁的类型又是什么？
• 假设没有意向锁，事务B只能遍历整张表，才能知道这张表有多少个行锁以及其对应的类型。如果这张表数据量大的情况下，全表扫描的性能是很低的。
• 此时有个想法“为这张表设立一个标志位，事务对记录加行锁时就修改标志位，等到有事务加表锁时，检查一下这个标志位就好了”，所以有了意向锁。
• 按粒度来划分，意向锁属于表锁。意向锁分为两种：
  • 共享意向锁（IS锁）：在事务加S型行锁时，会给表加上一个IS锁。
  • 独占意向锁（IX锁）：在事务加X型行锁时，会给表加上一个IX锁。
• 意向锁仅仅是为了事务在加表锁（X型或S型）时可以快速判断表中的记录是否有行锁，从而决定该事务能否加锁成功。因此
  • IX锁和IS锁不互斥（意向锁之间不互斥）
  • IX锁、IS锁和S型行所、X型行锁都不互斥（意向锁和行锁不互斥）
  • IX锁和S型表锁、X型表锁互斥，IS锁和S型表锁不互斥（意向锁和表锁可能互斥）

总结：

如果表中存在意向锁（IX或IS型），那么也意味着有事务在对行进行加锁，此时如果另一个事务要加表级锁，就要判断表级锁和任意一个意向锁是否互斥。

• 假设存在多个意向锁（既有意向排他锁，也有意向共享锁），那么此时是不可能加表级共享锁和表级排他锁的。
• 假设存在多个意向锁（只有意向共享锁），那么此时只能加表级共享锁，不能加表级排他锁。
• 假设存在多个意向锁（只有意向排他锁），那么此时是不能加表级共享锁和表级排他锁的。

问题：mysql中的外连接、内连接到底是什么？

什么是连接？

• 在mysql中，进行两个表之间的连接就是让一个表中的每条记录与另一个表中的每条记录拼接，组成一个结果集（笛卡尔积）。

什么是驱动表？什么是被驱动表？

• 连接的本质就是从一个表A中查询出一条记录，然后与另一个表B中的所有匹配的记录分别进行拼接。重复这个过程，直到表A中的记录都与表B中的记录拼接完毕为止。
• 而这个表A就是驱动表，表B就是被驱动表。

整个连接的过程就类似一个双层for循环，外层的for就是驱动表，内层的for就是被驱动表。

for(int i='a';i<='b';i++){ //这一层for就类似遍历表A
  for(int j='c';j<='d';j++){  //这一层for就类型遍历表B
    	res=i+j;  //进行连接
  }
}

什么是外连接？什么是内连接？

• 首先，在没有外连接之前，所有的连接都是内连接。
• 内连接： 驱动表中的记录在被驱动表中找不到匹配的记录时，那么就不会拼接，也不会加入结果集。
• 但是有一些需求，要求：驱动表中的记录即使在被驱动表中找不到匹配的记录，也要加入结果集。因此有了外连接。
• 外连接：驱动表中的记录在被驱动表中找不到匹配的记录时，仍然会进行拼接，会对让驱动表的记录与null进行拼接（被驱动表有多少列，就拼接多少个null），然后加入结果集。根据选取的驱动表不同，外连接分为两种：
  • 左外连接： 左侧的表为驱动表。
  • 右外连接： 右侧的表为驱动表。

为什么外连接要用on？on和where子句的关系是什么？

• 对于外连接来说，会把驱动表的所有记录都与被驱动表进行连接，然后加入结果集。假设没有on子句，有时候希望驱动表中的记录在被驱动表中找不到匹配的记录，要加入结果集（也就是外连接的语法，外连接的效果）；有时候又希望驱动表中的记录在被驱动表中找不到匹配的记录时，不加入结果集（也就是外连接的语法，内连接的效果）。
• 此时有个想法“控制怎么连接是由过滤条件where子句来决定的，那么把where进行拆分就好了”，因此有了on子句。
• on子句作为过滤条件，on子句保证了外连接是最纯正的外连接，实现的是外连接的语法，外连接的效果。
• 如果实现外连接的语法，内连接的效果。此时再用where子句过滤掉驱动表中匹配不上的记录，这样就好了。
• 因此如果on子句与where子句同时出现，也就是先用on过滤，保证最纯的外连接，然后再用where过滤，进一步加工结果集。

由于on子句是为了在外连接时，驱动表中的记录在被驱动表找不到匹配记录时是否要加入结果集，从而把外连接的结果集是否要转换成内连接的结果集的场景下提出的，因此在内连接中，on与where等价。

问题：事务中的一致性到底是什么意思？一致性和原子性有什么不一样？

什么是数据的一致性？

• 首先，数据库世界是对现实世界的一种映射。现实世界的一个状态转移，对应着数据库的一组操作。这组操作就是事务啊！！！为了让数据库操作符合现实世界中状态的转移的规则，因此有了事务的ACID特性。
• 事务的四大特性：原子性、隔离性、持久性、一致性。前三个都很好理解，就一致性很难理解。
• 我认为一致性对应的就是现实世界中的“能量守恒”，在现实世界中有能量的消耗，必然会有能量的增长。在数据库世界中也如此，有数据的减少，必然就有数据的增加。
• 数据库是现实是世界的一个映射，现实世界存在的约束在数据库中要有所体现，如果数据库中的数据全部符合现实世界中的约束，那么这些数据是符合一致性的。
• 举个例子： 转账，在现实世界中一个人的余额减少必定会有一个人的余额增加。因此无形中有个约束”参与转账的账户的总余额不变“，也就是说，一个人转账，另一个人必定会收到对应的金额，不会出现收不到，收少了，收多了的情况。映射到数据库中也就是一条记录中某个值的减少，必然会有一条记录某个值的增加。
• 一致性最求的是结果，而不是过程。也就是说只要结果符合约束就是满足一致性。即使过程中是否满足原子性、隔离性、持久性都不是满足一致性的必然因素。

一致性和原子性有什么不一样？

• 原子性和一致性的的侧重点不同，原子性关注状态，要么全部成功，要么全部失败，不存在部分成功的状态。而一致性关注数据的可见性，中间状态的数据对外部不可见，只有最初状态和最终状态的数据对外可见
• 我个人认为原子性和一致性的区别就是：一个是操作 一个是数据；一个是过程 一个是结果；一个是状态 一个是属性。