MySQL学习总结 （InnoDB）

主要内容：

1. 存储结构
2. 索引
3. 锁
4. 事务

存储结构

1. 表

• 索引组织表：表是根据主键顺序组织存放的。如果表中没有非空惟一索引，引擎会自动创建一个6字节大小的指针。
• 主键的索引是定义索引的顺序，而不是建表时列的顺序。
• 表空间：逻辑结构的最高层，所有的数据都存放在表空间中。
• 段：表空间由各个段组成，常见的段有数据段、索引段、回滚段等。
• 数据即索引 ，索引即数据。
• 区：区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。
• 引擎页的大小为16KB,即一个区中一共有64个连续的页。
• 页（也叫块）：是InnoDB磁盘管理的最小单位。默认每个页的大小为16KB.新版本中可以设置为4，8，16k。
• 行：数据是按行存储。每个页最多允许存放7992行记录。

2. 行记录格式

• Compact行记录是在MySQL5.0中引入。一个页中存放的数据越多，其性能就越高。
• 变长字段的长度不能超过2字节，因为VARCHAR类型的最大长度为65535。
• 行溢出数据：一般认为，BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。
• 通过实际测试发现能存放VARCHAR类型的最大长度为65532.
• MySQL官方手册中定义的65535长度是指所有VARCHAR列的长度总和，如果列的长度总和超过这个长度，依然无法创建
• VARCHAR（N）,CHAR(N) N是指字符的长度，不是字节的长度。

3. 页结构

• B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。
• 约束和索引的区别：结束是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物流存储的方式。

索引

1. B+树中的B代表的是balance(平衡)，而不是binary(二叉)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。
2. 对于某一条具体的记录的查询是通过对Page Directory进行二分查找得到的。
3. 平衡二叉树的定义如下：首先符合二叉树查找 的定义，其次必须满足任何节点的两个子树的高度最大差为1。
4. 聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存入的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。
5. 索引组织表中的数据也是索引的一部分。每个数据页都通过一个双向链表来进链接 。
6. 辅助索引：叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引 行中还包含了一个书签，用来告诉InnoDB存储引擎哪里可以找到与索引 相对应的行数据。
7. 当通过辅助索引来查找数据时，InnoDB会遍历辅助索引并通过叶级别指针获得指向主键索引的主键，再通过主键索引 来找到一个完整的行记录。
8. Microsoft SQL Server 有一种称为堆表的表类型，即行数据的存储按照插入的顺序存放。
9. Cardinality:表示索引 中惟一值（不重复记录）的数目的估计值。这个值非常关键，优化器会根据这个值来判断是否使用这个索引 。但是这个值不是实时更新的，即并非每次索引的更新都会更新该值，原因是代价过大，所以这个值是一个估计值。 也把这个值称为可选择性。引擎通常通过采样的方式来完成Cardinality的统计。
10. 覆盖索引：即从辅助索引就可以等到查询的记录，不需要查询聚集索引中的记录。

通常查询索引列或者count值会用覆盖索引

1. 优化器不使用索引：在范围查找或者JOIN链接操作时，有可能不使用索引 。
2. 强制使用索引 ：FORCE INDEX
3. 索引提示：USE INDEX
4. 如果确定指定某个索引来完成查询，那么最可靠的是FORCE INDEX，而不是USE INDEX
5. Multi-Range Read(MRR):为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。具体做法：在查询辅助索引时，首先根据等到的查询结果，按照主键进行的顺序，并按照主键排序的顺序进行书签查找 （explain 会有Using MRR）
6. Index Condition Pushdown(ICP)优化：优化之前-查询索引，先根据索引来查找 记录，然后再根据where条件来过滤记录。 优化之后-在取出 索引的同时，判断是否可以进行where条件的过滤，即将where的部分过滤操作放在地了存储引擎层。在一些查询条件下，可以大大减少上层SQL层对记录的索取(fetch),从而提高数据库性能。（explain 会有 Using index condition）
7. 哈希算法：采用链表的方式解决冲突，除法散列。
8. 自适应哈希索引：是数据库自身创建并使用的，不能对其进行干预。

锁

1. 锁机制：是数据库系统区别于文件系统的一个关键特性。提供数据的完整性与一致性。
2. InnoBD存储引擎不需要锁升级，因为一个锁和多个锁的开销是相同的。
3. InnoDB实现了两种锁：共享锁（S Lock）与排他锁（X Lock）。
4. 共享锁：允许事务读一行数据。排他锁：允许事务删除或者更新一行数据。X与S都是行锁
5. InnoDB存储引擎支持意向锁设计比较简练，即意向锁就是表级别的锁。设计目的就是为了在一个事务中提示下一行将被请求的锁类型。分为：意向共享锁（IS Lock,事务想要 获得一张表中某几行的共享锁）,意向排他锁（IX Lock事务想要 获得一张表中某几行的排他锁）
6. 因为InnoDB存储引擎支持的是行级别的锁，所以意向锁不会阻塞除全表扫以外的任何请求。
7. 一致性非锁定读：通过多版本控制 的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或者UPDATE操作，这里读取操作不会因此等待行上的锁释放。引擎会读取行的一个快照数据。
8. 一致性锁定读：SELECT ... FOR UPDATE （对读取的行记录加一个X锁）或者 SELECT ... LOCK IN SHARE MODE（对读取的行记录加一个S锁，其它事务可以向被锁的行加S锁，如果加X锁，则会被阻塞）
9. 行锁3种算法：Record Lock-单个行记录上锁。Gap Lock-间隙锁，锁定一个范围，但不包含记录本身。Next-Key Lock:Gap+Record
10. Gap Lock:为了阻止多个事务将记录插入到同一范围内，这个导致Phantom Problem（幻读）产生。可以通过：事务隔离级别设置为RC关闭Gap Lock.
11. Phantom Problem：指在同一事务下，连续执行两次同样的SQL语句，可能导致不同的结果 ，第二次的SQL语句可能会返回之前不存在的行。
12. 脏页：在缓冲池中已经被修改的页，但是还没有提交刷新到磁盘中。日志都已经写入到重做日志文件中。脏数据 ：指事务对缓冲池中的行记录的修改，并且还没有被提交。
13. 不可重复读：在一个事务内读取一组数据，在这个事务还没有结束时，另外的事务对这组数据 进行了修改并提交了事务，此时，第一个事务再次读取数据，可能和第一次读取的数据 不一致。
14. 不可重复读与脏读的区别：脏读是读取未提交的数据 ，不可重复读是读取已经提交的数据。
15. InnoDB不会回滚超时引发的错误异常。但是发现死锁后会回滚。

事务

1. ACID:原子性(automicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)。
2. 原子性、一致性、持久性通过数据库的redo log 和undo log来完成。redo log是重做日志，保证事务的原子性和持久性。undo 保证事务的一致性。redo恢复提交事务修改的页操作，undo回滚行记录到某个待定版本。两者记录的内容不同，redo通过是物理操作，记录的是页的物流修改操作。undo是逻辑日志，根据系统自动记录进行记录。
3. binlog与redo log的不同：binlog是在MySQL数据库的上层产生，不仅针对InnoDB，任何存储引擎对于数据的更改都会产生binlog. binlog是一种逻辑日志，记录的是对应的SQL语句。redo log是物理格式日志，记录的是对每个页的修改。
4. binlog只在事务提交时一次写入，redo log是不停地写入，与事务提交顺序不同。
5. undo是逻辑日志，只是将数据库逻辑地恢复到原来的样子。所有修改被取消了，但是数据结构和页本身在回滚之后可能大不一样了。除了回滚，undo的另外一个作用是MVCC，undo log会伴随着redo log产生，因为undo log需要持久化。
6. 四种隔离级别：READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ /SERIALIZABLE
7. MySQL总是自动提交的。