八股文-MySQL

MySQL
,

ACID

  • A: atomicity
  • C: consistency
  • I: isolation
  • D: durability

Isolation Level

  • SERIALIZABLE
  • REPEATABLE READ: 允许幻读 (所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）)
  • READ COMMITTED: 允许不可重复读 (即事务A在将n从0累加到10的过程中，B无法看到n的中间值，之中只能看到10。同时有事务C进行从10到20的累加，此时B在同一个事务内再次读时，读到的是20。)
  • READ UNCOMMITTED: 允许脏读 (例如A将变量n从0累加到10才提交事务，此时B可能读到n变量从0到10之间的所有中间值。)

MVCC

InnoDB通过两个隐藏列trx_id、roll_pointer实现。如果表中没有主键和非NULL唯一键时，则还会有第三个隐藏的主键列row_id。
多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。

undo log的作用

  • 事务回滚时，保证原子性和一致性
  • 用于MVCC快照读。

binlog是Server层日志，记录了除了查询语句(select、show)之外的所有的 DDL 和 DML 语句。也就意味着我们基本上所有对数据库的操作变更都会记录到binlog里面。binlog以事件形式记录，还记录了语句所执行的消耗的时间。binlog有三种记录格式，分别是ROW、STATEMENT、MIXED。

  1. ROW： 基于变更的数据行进行记录，如果一个update语句修改一百行数据，那么这种模式下就会记录100行对应的记录日志。
  2. STATEMENT：基于SQL语句级别的记录日志，相对于ROW模式，STATEMENT模式下只会记录这个update 的语句。所以此模式下会非常节省日志空间，也避免着大量的IO操作。
  3. MIXED： 混合模式，此模式是ROW模式和STATEMENT模式的混合体，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。
    binlog 写入策略由sync_binlog参数控制。

redo log是引擎层(innodb)的日志，目标是支持innodb的“事务”的特性，属于物理日志，记录的是“在某个数据页上做了什么修改”。由参数innodb_flush_log_at_trx_commit控制。

事务的原子性和持久性则是通过redo log 和undo log来保障的。

redo log 也属于引擎层(innodb)的日志。在Mysql里数据每次修改前，都首先会把修改之前的数据作为历史保存一份到undo log里面的，数据里面会记录操作该数据的事务ID，然后我们可以通过事务ID来对数据进行回滚。

Read view

read view的字段属性：

  • low_limit_id：high water mark，大于等于view->low_limit_id的事务对于view都是不可见的
  • up_limit_id：low water mark，小于view->up_limit_id的事务对于view一定是可见的
  • low_limit_no：trx_no小于view->low_limit_no的undo log对于view是可以purge的
  • rw_trx_ids：读写事务数组

RR隔离级别（除了Gap锁之外）和RC隔离级别的差别是创建snapshot时机不同。 RR隔离级别是在事务开始时刻，确切地说是第一个读操作创建read view的；RC隔离级别是在每个语句开始时刻创建read view的。

快照读和当前读

consistent read（部分文档写法：快照读）：读取的是记录数据的可见版本（有旧的版本）。不加锁，普通的select语句都是快照读。
locking reads（部分文档写法：当前读）：读取的是记录数据的最新版本，显式加锁的都是当前读。

幻读问题

使用MVCC解决了快照读情况下的幻读问题
使用Next-Key Lock（行锁+GAP锁）解决了当前读情况下的幻读问题

Serializable完全串行化的读，每次读都需要获得表级共享锁，读加共享锁，写加排他锁， 读写互斥，读写相互都会阻塞

例外情况：UPDATE语句执行之后，会将当前记录上存储的事务信息更新为当前的事务，而当前事务所做的任何更新，对本事务所有SELECT查询都变的可见，因此最后输出的结果是UPDATE执行后更新的所有记录。（官方不算bug）

查询执行路径

  1. 客户端发送一条查询给服务器。
  2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
  4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

优化器

选择错误执行计划的原因

  • 统计信息不准确
  • 执行计划中的成本估算不等同于实际执行的成本
  • MySQL的最优可能和你想的最优不一样
  • MySQL从不考虑其他并发执行的查询
  • MySQL不会考虑不受其控制的操作的成本，例如执行存储过程或者用户自定义函数的成本

优化类型

  • 重新定义关联表的顺序
  • 将外连接转化成内连接
  • 使用等价变换规则
  • 优化COUNT()、MIN()和MAX()
  • 预估并转化为常数表达式
  • 覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据
  • 子查询优化
  • 提前终止查询：LIMIT子句 或 例如发现了一个不成立的条件
  • 等值传播
  • 列表IN()的比较：MySQL中IN()不等同于多个OR条件的子句，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件

索引

索引类型

B-Tree索引

叶子节点指针指向其他叶子节点数据。
B-Tree索引适用于全键值、键值范围或键前缀查找。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。
自适应哈希索引（adaptive hash index）：当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

聚簇索引

聚簇索引是一种数据存储方式。InnoDB的聚簇索引在同一个结构中保存B-Tree索引和数据行。叶子页包含了行的全部数据，但是节点页只包含了索引列。
二级索引（非聚簇索引）叶子节点包含了引用行的主键列，访问需要两次索引查找
InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。