关于mysql:mysql知识归纳汇总

类型

1. mysql 的类型定义中，整形类型括号里的数字，指的是显示位数，与存储大小无关

2. 对于 varchar 和 char，前者是可变长，后者固定，其中的数字都代表字符长度，char 的长度在 0~255，varchar 存储字节数在 0~65535. 如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL 会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．

   例外：长度小于4个字符的 char 数据列不会被转换为 varchar 类型

explain 的应用

1. id：查问程序是从大到小顺次查问，id 雷同时从上到下查问，id能够为空，当且仅当 select_type 为 UNION_RESULT
2. type：const 和 eq_ref 都上用了主键或者惟一索引，它们的区别是 const 是只查一次就失去后果，而 eq_ref 要匹配屡次，因为有多条记录。
3. possible_keys 可能为 null，然而 key 不为 null，起因就是应用了全索引扫描。

4. key_len: 示意应用了索引的长度。长度由三个因素决定：

   1. 字符集
   2. 长度
   3. 是否为空

   字段类型	占用字节数
   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 存储引擎

1. 构造：分为聚簇索引和非聚簇索引，其中，除了主键之外，其余索引都是非聚簇索引。用的是 B + 树。
   为什么用 B + 树，而不是其余构造？先看看个别有什么构造：
   哈希表

   • 一种以 key-value 键值对存储数据的构造，通过散列运算，将 hash 后果作为文件指针，能够从索引文件中取得数据的文件指针，再到数据文件中获取到数据，依照这种构造，咱们很快能定位进去某一条数据的地位，查问的效率十分高，那么它的问题点在哪呢，那就是不反对范畴查问，分页或者大于、小于某个范畴的查问都是无奈反对的，只能反对固定的字段名 = 目标值的场景，同样也不适宜 Like 这种含糊查问，所以这种算法必定是不适宜作为数据库的索引的。
   • 不同的 key 值通过哈希函数运算，可能会呈现雷同的值，这个时候咱们称为哈希抵触。解决哈西抵触，咱们能够用链地址法，即把所有雷同的值放到一个链表里，这样无论有多少抵触，只是减少了链表的长度而已。

   有序列表

   • 查找和更新十分快，然而插入、删除代价很高，须要挪动前面的全副数据
   • 适宜做动态存储的索引，即不会批改的数据。

   树

   1. 二叉树
      二叉树的长处是查找速度十分快，工夫复杂度是 O(log2(n))，然而会呈现左倾或者右倾的问题而进化成链表；其次，因为一个节点只能有两个子节点，导致树的高度会变得十分高，在查问的时候须要屡次遍历，这样磁盘扫描会十分多，导致查问所需工夫减少。

   2. 均衡二叉树：红黑树

      • 红黑树是一种均衡二叉树，它继承了二叉树的长处，由解决了二叉树遇到的自增数据索引生效的问题，因为红黑树的会对树的构造进行调整，进行左旋或者右旋及色彩变换等操作，始终保障 左子节点数 < 父节点数 < 右子节点数
      • 因为一个节点只能有两个子节点，在数据量大的时候导致树的高度会变得十分高，在查问的时候须要屡次遍历，在磁盘寻址的时候十分不利，也是相当耗时的。
      • 查问效率不稳固，查问在根节点和在子节点相差很大。
      • 节点存储的数据太少，不能很好的利用操作系统和磁盘数据交换的个性，也没有很好的利用磁盘 IO 预读能力。操作系统和磁盘之间一次数据交换是以页为单位的，一页等于 4k，也就是每次 IO 交互操作系统会将 4K 的数据加载到内存中，然而在二叉树的每个节点的构造中只保留了一个关键字，一个数据区，两个子节点的援用，并不可能填满 4K 的数据量，也就是辛辛苦苦做了一次 IO 操作，却只加载了一个关键字，在数的高度很高，搜寻的数据又是在叶子节点，取一个关键字须要做很屡次的 IO

   3. 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 树放弃树的均衡的过程中，每次关键字的变动都会导致构造产生很大的变动，这个过程是特地浪费时间的。所以创立索引肯定要创立适合的索引，而不是把所有的字段都建设索引。

   4. B+ 树

      • B+ 树是 B 树的一个变种，它不再遵循 关键字个数 = 路数 -1 这个规定，数据的检索规定是采纳的左闭合取件，路数和关键字的个数关系为 1:1
      • B+ 树中的根结点和支节点中没有数据区，关键字对应的数据只保留在叶子节点中，所以只有叶子节点中的关键字数据区才会保留真正的数据内容或者数据对应的地址，然而在 B 树中，如果根结点命中，是间接返回的，B+ 树中，叶子节点不会保留子节点的援用
      • B+ 树的叶子节点是顺序排列的，并且相邻节点之间是程序援用的关系，叶子节点之间通过指针相连
      • B 树能解决的问题，B+ 树都能解决，且可能更好的解决，升高了树的高度，减少节点的数据存储量。
      • B+ 树的扫库和扫表能力更强，如果依据索引去对数据表扫描，B 树须要整颗树遍历，B+ 树只须要遍历所有的叶子节点
      • B+ 树的磁盘读写能力更强，根结点和支节点不保留数据区，所有的根结点和支节点在同样大小的状况下，保留的关键字更多，叶子结点不存子节点的援用，所以，B+ 树读写一次磁盘加载的关键字更多
      • B+ 树具备人造排序功能，而且查问效率更加稳固，查问 IO 的次数是稳固的

      以上就是抉择 B + 树的起因。
      而聚簇索引和非聚簇索引的区别在于，聚簇索引会把整行数据都保留，非聚簇索引只保留索引相干字段数据，还有主键的地址。因而个别状况下，如果非聚簇索引不是笼罩索引，须要回表查找，能力失去须要的后果。

2. 事务
   事务隔离级别与锁
   一共有以下四种隔离级别

   • 未提交读：会呈现脏读问题，即读到别的事务没有提交的数据。
   • 提交读：解决了脏读问题，然而会呈现不可反复读，就是在事务查问前后，如果别的事务提交了数据，会失去不一样后果。
   • 可反复读：解决了不可反复读，然而会呈现幻读，即查问前后，如果别的事务插入了数据，会导致插入雷同数据的时候报错，就像查问是假的，呈现了幻觉一样。
   • 串行化：齐全串行化的读，每次读都须要取得表级共享锁，读写互相都会阻塞

   不可反复读和幻读的区别：前者重点在于 update 和 delete，后者重点在于 insert。
   锁的类型有很多，分类领域也有很多，上面依据不同的分类简略形容一下。

   1. 乐观锁和乐观锁

      • 乐观锁
        正如其名，它指的是对数据被外界（包含本零碎以后的其余事务，以及来自内部零碎的事务处理）批改持激进态度，因而，在整个数据处理过程中，将数据处于锁定状态。
        在乐观锁的状况下，为了保障事务的隔离性，就须要一致性锁定读。读取数据时给加锁，其它事务无奈批改这些数据。批改删除数据时也要加锁，其它事务无奈读取这些数据。
      • 乐观锁
        绝对乐观锁而言，乐观锁机制采取了更加宽松的加锁机制。乐观锁大多数状况下依附数据库的锁机制实现，以保障操作最大水平的独占性。但随之而来的就是数据库性能的大量开销，特地是对长事务而言，这样的开销往往无奈接受。
        乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据减少一个版本标识，在基于数据库表的版本解决方案中，个别是通过为数据库表减少一个“version”字段来实现。读取出数据时，将此版本号一起读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的以后版本信息进行比对，如果提交的数据版本号大于数据库表以后版本号，则予以更新，否则认为是过期数据。

   2. 共享锁与排它锁

      • 共享锁
        也叫读锁，简称 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 锁关系矩阵如下，其中：+ 示意兼容，- 示意不兼容。
        

   3. 两段锁协定
      将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

      • 加锁阶段：在该阶段能够进行加锁操作。在对任何数据进行读操作之前要申请并取得 S 锁（共享锁，其它事务能够持续加共享锁，但不能加排它锁），在进行写操作之前要申请并取得 X 锁（排它锁，其它事务不能再取得任何锁）。加锁不胜利，则事务进入期待状态，直到加锁胜利才继续执行。
      • 解锁阶段：当事务开释了一个封闭当前，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。
      • 下面咱们讲到，如果查问时用到了索引，是会给数据加上行锁，然而如果查问没有用到事务，这时候就是加上表锁。然而在实际操作中，mysql 做了改良，当查问时，发现不属于过滤条件时，就会调用 unlock_row 办法，将不满足条件的记录开释锁 (违反了二段锁协定的束缚)。
        这样做，保障了最初只会持有满足条件记录上的锁，然而每条记录的加锁操作还是不能省略的。可见即便是 MySQL，为了效率也是会违反标准的。（参见《高性能 MySQL》中文第三版 p181）

   4. 行锁、gap 锁和 next-key 锁

      • 行锁 (record lock)
        单条索引记录上加锁，record lock 锁住的 永远是索引，而非记录自身，即便该表上没有任何索引，那么 innodb 会在后盾创立一个暗藏的汇集主键索引，那么锁住的就是这个暗藏的聚簇主键索引。所以说当一条 sql 没有走任何索引时，那么将会在每一条聚簇索引前面加 X 锁

      • gap 锁
        在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包含该索引记录自身。gap lock 的机制次要是解决可反复读模式下的幻读问题。
        gap lock 的前置条件：

        1. 事务隔离级别为 REPEATABLE-READ，innodb_locks_unsafe_for_binlog 参数为 0，且 sql 走的索引为非惟一索引
        2. 事务隔离级别为 REPEATABLE-READ，innodb_locks_unsafe_for_binlog 参数为 0，且 sql 是一个范畴的以后读操作，这时即便不是非惟一索引也会加 gap lock

      • next-key lock
        即 gap lock 与 record lock 的联合，即除了锁住记录自身，还要再锁住索引之间的间隙
        以下几种场景来剖析在 RR 级别下锁的执行

        
        演绎在一个表如下：
        

      • 死锁
        产生死锁的条件：

        1. 互斥条件：一个资源每次只能被一个过程应用；
        2. 申请与放弃条件：一个过程因申请资源而阻塞时，对已取得的资源放弃不放；
        3. 不剥夺条件：过程已取得的资源，在没应用完之前，不能强行剥夺；
        4. 循环期待条件：多个过程之间造成一种相互循环期待资源的关系。

        防止死锁的产生的一些倡议：

        1. 加锁程序统一
        2. 尽量基于 primary 或 unique key 更新数据
        3. 单次操作数据量不宜过多，波及表尽量少
        4. 缩小表上索引，缩小锁定资源
        5. 相干工具：pt-deadlock-logger

   5. mvcc

      • 以后读与快照读

        1. 以后读
           像 select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁) 这些操作都是一种 以后读 ，为什么叫以后读？就是它读取的是记录的 最新版本 ，读取时还要保障其余并发事务 不能批改以后记录 ，会对读取的记录进行 加锁
        2. 快照读（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

        1. Undo log 是 InnoDB MVCC 事务个性的重要组成部分。当咱们对记录做了变更操作时就会产生 undo 记录，Undo 记录默认被记录到零碎表空间 (ibdata) 中，但从 5.6 开始，也能够应用 独立的 Undo 表空间。
        2. Undo 记录中存储的是老版本数据，当一个旧的事务须要读取数据时，为了能读取到老版本的数据，须要顺着 undo 链找到满足其可见性的记录。当版本链很长时，通常能够认为这是个比拟耗时的操作（例如 bug#69812）。
        3. 大多数对数据的变更操作包含 INSERT/DELETE/UPDATE，其中 INSERT 操作 在事务提交前只对 以后事务可见 ，因而产生的 Undo 日志能够在 事务提交后 间接删除，而对于 UPDATE/DELETE 则须要保护多版本信息，在 InnoDB 里，UPDATE 和 DELETE 操作产生的 Undo 日志被归成一类，即 update_undo log

        4. 在回滚段中的 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)

        1. DB_TRX_ID < up_limit_id, 这种状况比拟好了解, 示意, 新事务在读取该行记录时, 该行记录的稳固事务 ID 是小于零碎以后所有沉闷的事务, 所以以后行稳固数据对新事务可见, 跳到步骤 5
        2. DB_TRX_ID=m_creator_trx_id 示意如果以后行事务 DB_TRX_ID 等于开启事务时的事务 id。简略来说，在同一个事务中 insert，update 的记录将可见。
        3. DB_TRX_ID >= low_limit_id，代表是以后行记录是在该事务生成 read view 后产生的（执行第二个 select 前就 commit），所以必定对以后事务不可见，跳到步骤 4
        4. DB_TRX_ID < low_limit_id，代表是以后行记录是在该事务生成 read view 时还沉闷的事务，如果 遍历 read view 中的 ids（up_limit_id 到 low_limit_id），存在 ids 之中证实事务曾经提交，所以不属于该事务可见，跳到步骤 4，否则可见（意味着，是以后事务 开始 的时候，该行记录对应事务 还没提交 ，但在 创立 read view 前 提交了，创立 read view 时沉闷的事务最晚的又在该行事务后）
        5. 从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的 undo-log 的版本号, 将它赋值该 DB_TRX_ID，而后跳到步骤 1 从新开始判断
        6. 将该可见行的值返回。

      • 可见性算法案例剖析

        1. 上面是一个十分简版的演示事务对某行记录的更新过程, 当然, InnoDB 引擎在外部要做的工作十分多
           
        2. 上面是一套比拟算法的利用过程
           

      • innodb 的 mvcc 与现实 mvcc 区别

        1. 个别咱们认为 MVCC 有上面几个特点：
           每行数据都存在一个版本，每次数据更新时都更新该版本
           批改时 Copy 出以后版本, 而后随便批改，各个事务之间 无烦扰
           保留时比拟版本号，如果胜利(commit)，则笼罩原记录, 失败则放弃 copy(rollback)
           就是每行都有版本号，保留时依据版本号决定是否胜利，听起来含有 乐观锁 的滋味, 因为这看起来正是，在提交的时候能力晓得到底是否提交胜利
        2. 而 InnoDB 实现 MVCC 的形式是:
           事务以 排他锁 的模式批改原始数据
           把批改前的数据寄存于 undo log，通过回滚指针与主数据关联
           批改胜利（commit）啥都不做，失败则复原 undo log 中的数据（rollback）
        3. 二者最实质的区别是: 当批改数据时 是否要排他锁定

        innodb 算不上真正的 mvcc，因为没有实现外围的多版本共存。其起因是现实 mvcc 对 多行数据 无能为力。
        譬如，如果事务 A 执行现实的 MVCC, 批改 Row1 胜利, 而批改 Row2 失败, 此时须要回滚 Row1, 但因为 Row1 没有被锁定, 其数据可能又被事务 B 所批改, 如果此时回滚 Row1 的内容，则会毁坏事务 B 的批改后果，导致事务 B 违反 ACID。这也正是所谓的 第一类更新失落 的状况

3. 特地的查问优化策略（待续）