Mysql概述
体系结构 存储引擎
InnoDB存储引擎
插入缓冲两次写自适应哈希索引异步IO刷新临界页 MyISAM存储引擎 日志文件
错误日志重做日志（redo log）回滚日志（undo log）二进制日志（binlog）慢查询日志查询日志 InnoDB存储结构
InnoDB逻辑存储结构InnoDB物理存储结构 约束索引
B+树索引
聚集索引clustered index辅助索引Secondary Index
B+树索引的分裂Cardinality联合索引回表查询索引覆盖优化器 哈希索引全文检索
倒排索引 锁
锁的类型一致性非锁定读一致性锁定读行锁锁问题
脏读不可重复读幻读问题丢失更新 死锁 事务
四个特性
原子性atomicity一致性consistency隔离性isolation持久性durability 特性的实现
redo log 重做日志undo log 回滚日志 隔离级别
读未提交（read-uncommitted）不可重复读（read-committed）可重复读（repeatable-read）串行化（serializable）
MySQL数据库独有的插件式体系结构并了解到存储引擎是MySQL区别于其他数据库的一个最重要特性。存储引擎的好处是每个存储引擎都有各自的特点能够根据具体的应用建立不同存储引擎表。 设计目标主要面向在线事务处理OLTP的应用。 On-Line Transaction Processing联机事务处理过程(OLTP)，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。 支持事务行锁设计支持外键非锁定读即默认读取操作不会产生锁Mysql5.5.8的默认引擎多版本并发控制MVCCnext-key locking的策略来避免幻读phantom现象聚集索引 全文索引
特性： 通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此插入聚集索引Primary Key一般是顺序的不需要磁盘的随机读取。此类插入顺序较快。 在进行插入操作时数据页的存放还是按主键a进行顺序存放的但是对于非聚集索引叶子节点的插入不再是顺序的了这时就需要离散地访问非聚集索引页由于随机读取的存在而导致了插入操作性能下降。当然这并不是这个b字段上索引的错误而是因为B+树的特性决定了非聚集索引插入的离散性。 对于非聚集索引的插入或更新操作不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中若在则直接插入，若不在则先放入到一个Insert Buffer对象。数据库这个非聚集的索引已经插到叶子节点而实际并没有只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge合并操作这时通常能将多个插入合并到一个操作中因为在一个索引页中这就大大提高了对于非聚集索引插入的性能。 相关博客 当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。在部分写失效的情况下，我们在应用重做日志之前，需要原始页的一个副本，两次写就是为了解决这个问题，下面是它的原理图： InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。 异步IO来提高磁盘操作性能，可以进行IO Merge操作也就是将多个IO合并为1个IO这样可以提高IOPS的性能 InnoDB存储引擎还提供了Flush Neighbor Page刷新邻接页的特性。其工作原理为当刷新一个脏页时InnoDB存储引擎会检测该页所在区extent的所有页如果是脏页那么一起进行刷新。这样做的好处显而易见通过AIO可以将多个IO写入操作合并为一个IO操作故该工作机制在传统机械磁盘下有着显著的优势. 主要面向一些OLAP数据库应用。 特点： 不支持事务表锁设计支持全文索引缓冲池只缓存cache索引文件而不缓冲数据文件
对于MyISAM存储引擎表MySQL数据库只缓存其索引文件数据 NDB存储引擎 集群存储引擎Memory存储引擎 默认使用哈希索 引而不是我们熟悉的B+树索引Archive存储引擎 存储归档数据如日志信息 只支持INSERT和SELECt操作 提供高速的插入和压缩功能Federated存储引擎Maria存储引擎 MyISAM的后续版本 Maria存储引擎的特点是支持缓存数据和索引文件应用了行锁设计提供了MVCC功能支持事务和非事务安全的选项以及更好的BLOB字符类型的处理性能。
错误日志文件对MySQL的启动、运行、关闭过程进行了记录。遇到问题时应该首先查看该文件以便定位问题。 事务粒度,是innodb层产生的日志，晚于二进制日志。后面介绍 事务粒度,是innodb层产生的日志，晚于二进制日志。后面介绍 是整个数据库的粒度 主要作用： 恢复recovery某些数据的恢复需要二进制日志例如在一个数据库全备文件恢复后用户可以通过二进制日志进行point-in-time的恢复复制replication其原理与恢复类似通过复制和执行二进制日志使一台远程的MySQL数据库一般称为slave或standby与一台MySQL数据库一般称为master或primary进行实时同步审计audit用户可以通过二进制日志中的信息来进行审计判断是否有对数据库进行注入的攻击。
用户想记录SELECT和SHOW操作那只能使用查询日志而不是二进制日志。此外二进制日志还包括了执行数据库更改操作的时间等其他额外信息。 帮助定位可能存在问题的SQL语句从而进行SQL语句层面的优化。 查询日志记录了所有对MySQL数据库请求的信息无论这些请求是否得到了正确的执行。 InnoDB存储引擎支持以下三种索引: B+树索引全文索引哈希索引，自适应的，不能人为干预
B+树索引
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上由各叶子节点指针进行连接。 B+树索引就是传统意义上的索引这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+树索引的构造类似于二叉树根据键值Key_Value快速找到数据。B=balance. B+索引在数据库中有一个特点是高扇出性因此在数据库中B+树的高度一般都在2-4层这也就是说查找某一键值的行记录时最多只需要2到4次IO。而现在的磁盘每秒差不多在100次IO左右，2-3次意味着查询时间只需0.02-0.03秒。 聚集索引clustered index就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样每个数据页都通过一个双向链表来进行链接。 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。 辅助索引Secondary Index也称非聚集索引,叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签bookmark。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。 B+树索引页的分裂并不总是从页的中间记录开始。 并不是在所有的查询条件中出现的列都需要添加索引, 对于添加B+树索引, 要让访问的行数变小才有意义. 比如性别这样的字段, 一般只有两个取值: M或者F, 这时候他们的可取的范围是很小的, 称为低选择性 联合索引是指对表上的多个列进行索引。前面讨论的情况都是只对表上的一个列进行索引。联合索引的创建方法与单个索引创建的方法一样不同之处仅在于有多个索引列。从本质上来说联合索引也是一棵B+树不同的是联合索引的键值的数量不是1而是大于等于2。 使用非聚集索引时，需要扫码两遍索引树： 如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’，就是select的数据列只用从索引中就能够取得，不必从数据表中读取。 在某些情况下当执行EXPLAIN命令进行SQL语句的分析时会发现优化器并没有选择索引去查找数据而是通过扫描聚集索引也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。可以认为数据库内部有优化器对sql语句进行优化，例如select * from等操作需要全行数据，一般使用聚集索引扫描全表。 InnoDB存储引擎使用哈希算法来对字典进行查找其冲突机制采用链表方式哈希函数采用除法散列方式。 B+树索引的特点可以通过索引字段的前缀prefix进行查找。因为索引字段是排序的。例如，可以查询博客内容以xxx开头的文章并且只要content添加了B+树索引就能利用索引进行快速查询。然而实际这种查询不符合用户的要求，因为在更多的情况下用户需要查询的是博客内容包含单词xxx的文章。这种情况B+树索引无能为力。 全文检索通常使用倒排索引inverted index来实现。倒排索引同B+树引一样也是一种索引结构。它在辅助表auxiliary table中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。 锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。 共享锁S Lock允许事务读一行数据。排他锁X Lock允许事务删除或更新一行数据。意向共享锁IS Lock事务想要获得一张表中某几行的共享锁意向排他锁IX Lock事务想要获得一张表中某几行的排他锁 一致性非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过 行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放，相反地，InnoDB存储引擎会去读取行的一个快照数据。 脏读指的就是在不同的事务下当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。 不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还 不可重复读是读异常，但幻读则是写异常。不可重复读是读异常的意思是，如果你不多select几次，你是发现不了你曾经select过的数据行已经被其他人update过了。避免不可重复读主要靠一致性的非锁定读。 在默认的事务隔离级别下即REPEATABLE READ下InnoDB存储引擎采用Next-Key Locking机制来避免Phantom Problem幻像问题。这点可能不同于与其他的数据库如Oracle数据库因为其可能需要在SERIALIZABLE的事务隔离级别下才能解决Phantom Problem。Phantom Problem是指在同一事务下连续执行两次同样的SQL语句可能导致不同的结果第二次的SQL语句可能会返回之前不存在的行。 一个事务的更新操作会被另一个事务的更新操作所覆盖从而导致数据的不一致。 死锁是指两个或两个以上的事务在执行过程中因争夺锁资源而造成的一种互相等待的现象。若无外力作用事务都将无法推进下去。解决死锁问题最简单的方式是不要有等待将任何的等待都转化为回滚并且事务重新开始。 毫无疑问这的确可以避免死锁问题的产生。然而在线上环境中这可能导致并发性能的下降甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重因为这很难被发现并且浪费资源。解决死锁问题最简单的一种方法是超时即当两个事务互相等待时当一个等待时间超过设置的某一阈值时其中一个事务进行回滚另一个等待的事务就能继续进行。在InnoDB存储引擎中参数innodb_lock_wait_timeout用来设置超时的时间。 超时机制虽然简单但是其仅通过超时后对事务进行回滚的方式来处理或者说其是根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大如事务操作更新了很多行占用了较多的undo log这时采用FIFO的方式就显得不合适了因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。因此除了超时机制当前数据库还都普遍采用wait-for graph等待图的方式来进行死锁检测。较之超时的解决方案这是一种更为主动的死锁检测方 InnoDB存储引擎采用wait-for graph等待图的方式来进行死锁检测。构造出一张图而在这个图中若存在回路就代表存在死锁因此资源间相互发生等待。 原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功才算整个事务成功。事务中任何一个SQL语句执行失败已经执行成功的SQL语句也必须撤销数据库状态应该退回到执行事务前的状态。如果事务中的操作都是只读的要保持原子性是很简单的。一旦发生任何错误要么重试要么返回错误代码。因为只读操作不会改变系统中的任何相关部分。但是当事务中的操作需要改变系统中的状态时例如插入记录或更新记录那么情况可能就不像只读操作那么简单了。如果操作失败很有可能引起状态的变化因此必须要保护系统中并发用户访问受影响的部分数据。 隔离性由锁来实现；原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志用来保证事务的原子性和持久性。undo log用来保证事务的一致性。 innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。 undo log有两个作用：提供回滚和多个行版本控制(MVCC)。 在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。 undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。 undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。 另外，undo log也会产生redo log，因为undo log也要实现持久性保护。 InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ但是与标准SQL不同的是InnoDB存储引擎在REPEATABLE READ事务隔离级别下使用Next-Key Lock锁的算法因此避免幻读的产生。InnoDB存储引擎在默认的REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求即达到SQL标准的SERIALIZABLE隔离级别。 隔离级别越低事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是READ COMMITTED。
根据<
MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。需要特别注意的是存储引擎是基于表的而不是数据库。
特点：
注意 并不是所有的主键插入都是顺序的。若主键类是UUID这样的类 那么插入和辅助索引一样同样是随机的。即使主键是自增类型但是插入的是指定的值而不是NULL值那么同样可能导致插入并非连续的情况。
需要同时满足以下两个条件：索引是辅助索引secondary index索引不是唯一unique的。
相关博客
OLTP（on-line transaction processing）翻译为联机事务处理， OLAP（On-Line Analytical Processing）翻译为联机分析处理，从字面上来看OLTP是做事务处理，OLAP是做分析处理。从对数据库操作来看，OLTP主要是对数据的增删改，OLAP是对数据的查询。
文件的缓存交由操作系统本身来完成这与其他使用LRU算法缓存数据的大
部分数据库大不相同。
其他的引擎：
一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。
二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。
注：部分没有更新数据库的操作 如UPDATE后数据库没有改变 也会记录到二进制文件
可以在MySQL启动时设一个阈值将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都是存放在表空间中。默认情况下InnoDB存储引擎有一个共享表空间ibdata1，即所有数据都放在这个表空间内。表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的（index organized），数据段即为B+树的叶节点（上图的leaf node segment），索引段即为B+树的非叶节点（上图的non-leaf node segment）。并不是每个对象都有段。因此更准确地说，表空间是由分散的页和段组成。区是由64个连续的页组成的，每个页大小为16KB，即每个区的大小为1MB。对于大的数据段，InnoDB存储引擎最多每次可以申请4个区，以此来保证数据的顺序性能。同大多数数据库一样，InnoDB有页（page）的概念（也可以称为块），页是InnoDB磁盘管理的最小单位。InnoDB存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2～200行的记录，即7992行记录。
InnoDB物理存储结构
约束
索引
所有记录都在叶子节点上并且是顺序存放的，如果用户从最左边的叶子节点开始顺序遍历可以得到所有数据。
B+树的插入删除
注意:
B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存再在内存中进行查找最后得到要查找的数据。
扇出是指该模块直接调用的下级模块的个数。可以理解为单次判断分叉多。
聚集索引的存储并不是物理上连续的而是逻辑上连续的，对于主键的排序查找和范围查找速度非常快。（排序 order by limit N）.
辅助索引的存在并不影响数据在聚集索引中的组织因此每张表上可以有多个辅助索引。
当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说如果在一棵高度为3的辅助索引树中查找数据那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找最终找到一个完整的行数据所在的页因此一共需要6次逻辑IO访问以得到最终的一个数据页。
使用这个条件执行SQL, 可能得到的是表中50%的数据, 这样的索引是没有必要的. 而对于姓名, 这样的重复的可能性相对更小, 在此字段上建立索引是可行的.
Cardinality是通过采样完成的计算的, 而不是对所有的数据都参与计算.
Cardinality值查看索引是否是高选择性，表示索引中不重复记录数量的预估值。在实际应用中Cardinality/n_rows_in_table应尽可能地接近1。如果非常小那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时对这个字段添加B+树索引是非常有必要的。
可以看出联合索引是以定义靠前的键值依次排序的，所以使用联合索引的时候要注意。
（1）先通过普通索引定位到主键值；
（2）在通过聚集索引定位到行记录；
这就是回表查询
可以利用索引覆盖进行优化查询。将被查询的字段，建立到联合索引里去。
自适应哈希索引是数据库内部根据数据访问自建的，不能对其进行干预。对于范围查找无能为力。
两种表现形式:
形式1：
形式2：
InnoDB 采用fullinverted index的方式.
倒排索引需要将word存放到一张表中这个表称为Auxiliary Table辅助表。在InnoDB存储引擎中为了提高全文检索的并行性能共有6张Auxiliary Table目前每张表根据word的Latin编码进行分区。Auxiliary Table是持久的表存放于磁盘上。然而在InnoDB存储引擎的全文索引中还有另外一个重要的概念FTS Index Cache全文检索索引缓存其用来提高全文检索的性能。FTS Index Cache是一个红黑树结构其根据wordilist进行排序。
数据库系统使用锁是为了支持对共享资源进行并发访问提供数据的完整性和一致性。
InnoDB存储引擎提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销并可以同时得到并发性和一致性。
一致性非锁定读与一致性锁定读
一致性非锁定读
脏读现象在生产环境中并不常发生从上面的例子中就可以发现脏读发生的条件是需要事务的隔离级别为READ UNCOMMITTED而目前绝大部分的数据库都至少设置成READ COMMITTED。InnoDB存储引擎默认的事务隔离级别为READ REPEATABLE。
没有结束时另外一个事务也访问该同一数据集合并做了一些DML操作。因此在第一个事务中的两次读数据之间由于第二个事务的修改那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况这种情况称为不可重复读。不可重复读和脏读的区别是脏读是读到未提交的数据而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求。
幻读是写异常的意思是，如果不自己insert一下，你是发现不了其他人已经偷偷insert过相同的数据了。解决幻读主要靠间隙锁，即Next-Key Locking机制。
在当前数据库的任何隔离级别下都不会导致数据库理论意义上的丢失更新问题。这是因为即使是READ UNCOMMITTED的事务隔离级别对于行的DML操作需要对行或其他粗粒度级别的对象加锁。
解决办法就是在有意向更新时，加排他锁。
式。
redo和undo
redo log 是物理日志，记录内存的值的具体信息。如xxx号页xxx位置为xxx。二进制日志记录的是逻辑内容，即sql语句的内容。二进制日志是在整个数据库的上层产生，先于redo log被写入，也可用于系统恢复，redo log是在系统的innodb层产生
当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。
