类型
- mysql的类型定义中，整形类型括号里的数字，指的是显示位数，与存储大小无关
对于varchar和char，前者是可变长，后者固定，其中的数字都代表字符长度，char的长度在0~255，varchar存储字节数在0~65535.如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．
例外：长度小于４个字符的char数据列不会被转换为varchar类型
explain的使用
- id：查询顺序是从大到小依次查询，id相同时从上到下查询，id可以为空，当且仅当 select_type为 UNION_RESULT
- type：const和eq_ref都上用了主键或者唯一索引，它们的区别是const是只查一次就得到结果，而eq_ref要匹配多次，因为有多条记录。
- possible_keys可能为null，但是key不为null，原因就是使用了全索引扫描。
key_len:表示使用了索引的长度。长度由三个因素决定：
- 字符集
- 长度
- 是否为空
字段类型 占用字节数 char(n) n varchar(n) n+2 tinyint 1 smallint 2 int 4 bigint 8 date 3 timestamp 4 datetime 8 如果字段允许空则还要加一个字节。
如果有一个索引是联合索引 (a,b) a、b都是varchar(30)非null，编码是UTF8 那么如果索引都用上 长度为30*3+2+30*3+2=184 3是utf8占用3个字节
innodb存储引擎
结构：分为聚簇索引和非聚簇索引，其中，除了主键之外，其他索引都是非聚簇索引。用的是B+树。
为什么用B+树，而不是其他结构？先看看一般有什么结构：
哈希表- 一种以key-value键值对存储数据的结构，通过散列运算，将hash结果作为文件指针，可以从索引文件中获得数据的文件指针，再到数据文件中获取到数据，按照这种结构，我们很快能定位出来某一条数据的位置，查询的效率非常高，那么它的问题点在哪呢，那就是不支持范围查询，分页或者大于、小于某个范围的查询都是无法支持的，只能支持固定的字段名 = 目标值的场景，同样也不适合Like这种模糊查询，所以这种算法肯定是不适合作为数据库的索引的。
- 不同的key值通过哈希函数运算，可能会出现相同的值，这个时候我们称为哈希冲突。解决哈西冲突，我们可以用链地址法，即把所有相同的值放到一个链表里，这样无论有多少冲突，只是增加了链表的长度而已。
有序列表
- 查找和更新非常快，但是插入、删除代价很高，需要移动后面的全部数据
- 适合做静态存储的索引，即不会修改的数据。
树
- 二叉树
二叉树的优点是查找速度非常快，时间复杂度是O(log2(n))，但是会出现左倾或者右倾的问题而退化成链表；其次，由于一个节点只能有两个子节点，导致树的高度会变得非常高，在查询的时候需要多次遍历，这样磁盘扫描会非常多，导致查询所需时间增加。 平衡二叉树：红黑树
- 红黑树是一种平衡二叉树，它继承了二叉树的优点，由解决了二叉树遇到的自增数据索引失效的问题，因为红黑树的会对树的结构进行调整，进行左旋或者右旋及颜色变换等操作，始终保证 左子节点数<父节点数<右子节点数
- 由于一个节点只能有两个子节点，在数据量大的时候导致树的高度会变得非常高，在查询的时候需要多次遍历，在磁盘寻址的时候非常不利，也是相当耗时的。
- 查询效率不稳定，查询在根节点和在子节点相差很大。
- 节点存储的数据太少，不能很好的利用操作系统和磁盘数据交换的特性，也没有很好的利用磁盘IO预读能力。操作系统和磁盘之间一次数据交换是以页为单位的，一页等于4k，也就是每次IO交互操作系统会将4K的数据加载到内存中，但是在二叉树的每个节点的结构中只保存了一个关键字，一个数据区，两个子节点的引用，并不能够填满4K的数据量，也就是辛辛苦苦做了一次IO操作，却只加载了一个关键字，在数的高度很高，搜索的数据又是在叶子节点，取一个关键字需要做很多次的IO
B树（多路平衡查找树（Balance Tree））
- B Tree是一个绝对平衡树，所有的叶子节点在同一个高度
上面的这个树是一个 2-3树（每个节点存储2个关键字，有3路），每个节点保存的关键字个数和路数关系为：关键字个数=路数-1 - Mysql为了能更好的利用磁盘的预读能力，将页的大小设置为16K，就是将一个节点（磁盘块）的大小设置为16K，一次IO将一个节点（16K）内容加载到内存中，假设关键字的类型是int，4个字节，每个关键字对应的数据区也为4个字节，不考虑子节点应用的情况下，上图中每个节点大约能够存储（16*1000）/8 = 2000个关键字，那么对应的就是2001个路数，对于这种有2001个路数的B树，三层的高度能够搜索到的关键字的个数是远远大于普通的二叉树的。
- 在B树保持树的平衡的过程中，每次关键字的变化都会导致结构发生很大的变化，这个过程是特别浪费时间的。所以创建索引一定要创建合适的索引，而不是把所有的字段都建立索引。
- B Tree是一个绝对平衡树，所有的叶子节点在同一个高度
B+树
- B+树是B树的一个变种，它不再遵循 关键字个数=路数-1 这个规则，数据的检索规则是采用的左闭合取件，路数和关键字的个数关系为1:1
- B+树中的根结点和支节点中没有数据区，关键字对应的数据只保存在叶子节点中，所以只有叶子节点中的关键字数据区才会保存真正的数据内容或者数据对应的地址，但是在B树中，如果根结点命中，是直接返回的，B+树中，叶子节点不会保存子节点的引用
- B+树的叶子节点是顺序排列的，并且相邻节点之间是顺序引用的关系，叶子节点之间通过指针相连
- B树能解决的问题，B+树都能解决，且能够更好的解决，降低了树的高度，增加节点的数据存储量。
- B+树的扫库和扫表能力更强，如果根据索引去对数据表扫描，B树需要整颗树遍历，B+树只需要遍历所有的叶子节点
- B+树的磁盘读写能力更强，根结点和支节点不保存数据区，所有的根结点和支节点在同样大小的情况下，保存的关键字更多，叶子结点不存子节点的引用，所以，B+树读写一次磁盘加载的关键字更多
- B+树具有天然排序功能，而且查询效率更加稳定，查询IO的次数是稳定的
以上就是选择B+树的原因。
而聚簇索引和非聚簇索引的区别在于，聚簇索引会把整行数据都保存，非聚簇索引只保存索引相关字段数据，还有主键的地址。因此一般情况下，如果非聚簇索引不是覆盖索引，需要回表查找，才能得到需要的结果。
事务
事务隔离级别与锁
一共有以下四种隔离级别- 未提交读：会出现脏读问题，即读到别的事务没有提交的数据。
- 提交读：解决了脏读问题，但是会出现不可重复读，就是在事务查询前后，如果别的事务提交了数据，会得到不一样结果。
- 可重复读：解决了不可重复读，但是会出现幻读，即查询前后，如果别的事务插入了数据，会导致插入相同数据的时候报错，就像查询是假的，出现了幻觉一样。
- 串行化：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞
不可重复读和幻读的区别：前者重点在于update和delete，后者重点在于insert。
锁的类型有很多，分类范畴也有很多，下面根据不同的分类简单描述一下。乐观锁和悲观锁
- 悲观锁
正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。
在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。 - 乐观锁
相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。
乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。
- 悲观锁
共享锁与排它锁
- 共享锁
也叫读锁，简称S锁，原理：一个事务获取了一个数据行的共享锁，其他事务能获得该行对应的共享锁，但不能获得排他锁，即一个事务在读取一个数据行的时候，其他事务也可以读，但不能对该数据行进行增删改
设置共享锁：SELECT ... LOCK IN SHARE MODE - 排它锁
也叫写锁，简称x锁，原理：一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁（排他锁或者共享锁），即一个事务在读取一个数据行的时候，其他事务不能对该数据行进行增删改查
设置排他锁：SELECT ... FOR UPDATE - 意向共享锁和意向排它锁
意向共享锁，简称IS，其作用在于：通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加共享锁，那么此时innodb会先找到这张表，对该表加意向共享锁之后，再对记录A添加共享锁。
意向排他锁，简称IX，其作用在于：通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加排他锁，那么此时innodb会先找到这张表，对该表加意向排他锁之后，再对记录A添加排他锁。
意向共享锁和意向排他锁都是系统自动添加和自动释放的，整个过程无需人工干预。
多个意向锁可以和行级锁共存，因为意向锁的作用是为了对需要给表加锁的时候，更高效的处理能否加锁的处理，对行锁没有影响。 - 自增锁(AUTO-INC Locks)
特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值。在insert结束后立即释放。我们可以执行show engine innodb statusG来查看自增锁的状态信息。 - 在自增锁的使用过程中，有一个核心参数，需要关注一下，即innodb_autoinc_lock_mode,它有0、1、2三个值。保持默认就行。具体的含义可以参考官方文档。
InnoDB锁关系矩阵如下，其中：+ 表示兼容，- 表示不兼容。
- 共享锁
两段锁协议
将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）- 加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
- 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。
- 上面我们讲到，如果查询时用到了索引，是会给数据加上行锁，但是如果查询没有用到事务，这时候就是加上表锁。但是在实际操作中，mysql做了改进，当查询时，发现不属于过滤条件时，就会调用unlock_row方法，将不满足条件的记录释放锁 (违背了二段锁协议的约束)。
这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。可见即使是MySQL，为了效率也是会违反规范的。（参见《高性能MySQL》中文第三版p181）
行锁、gap锁和next-key锁
- 行锁(record lock)
单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚簇主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚簇索引后面加X锁 gap锁
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。gap lock的机制主要是解决可重复读模式下的幻读问题。
gap lock的前置条件：- 事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql走的索引为非唯一索引
- 事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql是一个范围的当前读操作，这时即使不是非唯一索引也会加gap lock
next-key lock
即gap lock与record lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙
以下几种场景来分析在RR级别下锁的执行
归纳在一个表如下：死锁
产生死锁的条件：- 互斥条件：一个资源每次只能被一个进程使用；
- 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
- 不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺；
- 循环等待条件：多个进程之间形成一种互相循环等待资源的关系。
避免死锁的产生的一些建议：
- 加锁顺序一致
- 尽量基于primary或unique key更新数据
- 单次操作数据量不宜过多，涉及表尽量少
- 减少表上索引，减少锁定资源
- 相关工具：pt-deadlock-logger
- 行锁(record lock)
mvcc
当前读与快照读
- 当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁 - 快照读（snapshot）
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
而像在RR和RC隔离级别，它们的产生的快照时机不同导致了隔离结果的区别：RR事务在begin/start transaction之后的第一条select读操作后, 会创建一个快照(read view), 将当前系统中活跃的其他事务记录记录起来，而RC事务中每条select语句都会创建一个快照(read view）。
- 当前读
隐式字段
InnoDB存储引擎在数据库每行数据的后面添加了三个字段：- 6字节的事务ID(DB_TRX_ID)字段: 用来标识最近一次对本行记录做修改(insert|update)的事务的标识符, 即最后一次修改(insert|update)本行记录的事务id。至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted, 并非真正删除。
- 7字节的回滚指针(DB_ROLL_PTR)字段: 指写入回滚段(rollback segment)的 undo log record (撤销日志记录的记录)。如果一行记录被更新, 则 undo log record 包含 '重建该行记录被更新之前的内容' 所必须的信息。
- 6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。
undo log
- Undo log是InnoDB MVCC事务特性的重要组成部分。当我们对记录做了变更操作时就会产生undo记录，Undo记录默认被记录到系统表空间(ibdata)中，但从5.6开始，也可以使用独立的Undo 表空间。
- Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作（例如bug#69812）。
- 大多数对数据的变更操作包括INSERT/DELETE/UPDATE，其中INSERT操作在事务提交前只对当前事务可见，因此产生的Undo日志可以在事务提交后直接删除，而对于UPDATE/DELETE则需要维护多版本信息，在InnoDB里，UPDATE和DELETE操作产生的Undo日志被归成一类，即update_undo log
在回滚段中的undo logs分为: insert undo log 和 update undo log
insert undo log : 事务对insert新记录时产生的undolog, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
update undo log : 事务对记录进行delete和update操作时产生的undo log, 不仅在事务回滚时需要, 一致性读也需要，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除purge
从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。 为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。
read view(读视图)
- Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)
- 所以我们知道 Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
- Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本
可见性比较算法
设当前新开事务id为 new_id
当前新开事务创建的快照read view 中最早的事务id为up_limit_id, 最迟的事务id为low_limit_id(注意这个low_limit_id=未开启的事务id=当前最大事务id+1)- DB_TRX_ID < up_limit_id, 这种情况比较好理解, 表示, 新事务在读取该行记录时, 该行记录的稳定事务ID是小于系统当前所有活跃的事务, 所以当前行稳定数据对新事务可见, 跳到步骤5
- DB_TRX_ID=m_creator_trx_id 表示如果当前行事务DB_TRX_ID等于开启事务时的事务id。简单来说，在同一个事务中insert，update的记录将可见。
- DB_TRX_ID >= low_limit_id，代表是当前行记录是在该事务生成read view后产生的（执行第二个select前就commit），所以肯定对当前事务不可见，跳到步骤4
- DB_TRX_ID < low_limit_id，代表是当前行记录是在该事务生成read view时还活跃的事务，如果遍历read view中的ids（up_limit_id到low_limit_id），存在ids之中证明事务已经提交，所以不属于该事务可见，跳到步骤4，否则可见（意味着，是当前事务开始的时候，该行记录对应事务还没提交，但在创建read view前提交了，创建read view时活跃的事务最晚的又在该行事务后）
- 从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的undo-log的版本号, 将它赋值该 DB_TRX_ID ，然后跳到步骤1重新开始判断
- 将该可见行的值返回。
可见性算法案例分析
- 下面是一个非常简版的演示事务对某行记录的更新过程, 当然, InnoDB引擎在内部要做的工作非常多
- 下面是一套比较算法的应用过程
- 下面是一个非常简版的演示事务对某行记录的更新过程, 当然, InnoDB引擎在内部要做的工作非常多
innodb的mvcc与理想mvcc区别
- 一般我们认为MVCC有下面几个特点：
每行数据都存在一个版本，每次数据更新时都更新该版本
修改时Copy出当前版本, 然后随意修改，各个事务之间无干扰
保存时比较版本号，如果成功(commit)，则覆盖原记录, 失败则放弃copy(rollback)
就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道, 因为这看起来正是，在提交的时候才能知道到底能否提交成功 - 而InnoDB实现MVCC的方式是:
事务以排他锁的形式修改原始数据
把修改前的数据存放于undo log，通过回滚指针与主数据关联
修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback） - 二者最本质的区别是: 当修改数据时是否要排他锁定
innodb算不上真正的mvcc，因为没有实现核心的多版本共存。其原因是理想mvcc对多行数据无能为力。
譬如，如果事务A执行理想的MVCC, 修改Row1成功, 而修改Row2失败, 此时需要回滚Row1, 但因为Row1没有被锁定, 其数据可能又被事务B所修改, 如果此时回滚Row1的内容，则会破坏事务B的修改结果，导致事务B违反ACID。 这也正是所谓的第一类更新丢失的情况- 一般我们认为MVCC有下面几个特点：
- 特别的查询优化策略（待续）
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。