类型
- 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有上面几个特点：
- 特地的查问优化策略（待续）