mysql学习笔记：基础架构

一条查询SQL的执行

连接器

• 执行命令链接数据库。
• 注意事项
  • mysql临时使用的资源保存在连接对象中，大量使用长连接会导致数据库oom，表现为mysql异常重启。解决
  • 定期断开；执行占内存的大查询后断开
  • 5.7版本之后，使用reset命令可以重置连接道初始化状态

查询缓存

• 建立好连接，执行select语句会先查询缓存。如果之前执行过该语句，直接命中缓存，返回，效率很高
• 大多数时候缓存都是关闭的。mysql在表数据有更新时就会清空缓存，所以大多数时候缓存没啥用。除非长时间不会更新的表，才适合用缓存。
• 8.0之后彻底将缓存删掉了

分析器

• 词法分析。分析sql字符串。
• 语法分析。看sql有没有错

优化器

• 多个索引，多表联合时，选取最优方案

执行器

• 权限判断
• 执行查询语句
• 在慢查询日志中可以看到rows_examined 字段表示每条sql语句扫描了多少行。可以分析效率，但和引擎具体扫过行数不完全相同

一条更新SQL的执行

首先建立连接；

更新操作会先清空表的缓存；

词法分析，语法分析

优化器，执行器

两个重点：redo log 和 binlog

重要的日志模块：redo log

• WAL技术：先写日志，再写磁盘。有记录需要更新时，先写到redo log，然后更新内存。对外就展示更新成功了。mysql闲了再同步到磁盘
• 
• 参考上图。redo log是一个环。两个指针用来定位。写指针表示当前写到哪了。读指针表示当前读到哪了。写指针到读指针之间的内容就是可以利用的。如果写满了，读指针就开始工作，同步一部分数据到磁盘，将读指针向后推，流出可写的空间。
• 即使MySQL异常重启，之前的提交记录也会保存，不会丢失

重要的日志模块：binlog

两个日志对比

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

怎样让数据库恢复到半个月内任意一秒的状态？

• 寻找最近一次的整库备份。
• 从备份时刻开始，重放binlog日志，直到需要恢复的那一秒

两阶段提交

• 更新语句执行完，先写redo log ，写成功后处理 prepare阶段
• 然后写 bin log，写完再提交事务，处理commit 阶段
• 两阶段提交是为了保证 redo log 和binlog逻辑一致

事务

隔离性与隔离级别

• 读未提交：一个事物还未提交时，它做的变更可以被其他事务看见
• 都提交：一个事物提交后，他做的变更才能被其他事务看见
• 可重复读：一个事物在执行过程中看到的数据，总是和事务启动时看到的是一样的。（可重复读隔离级别下，该事务未提交的变更对其他事务不可见）
• 串行化：对同一记录，读写都会加锁，一个一个执行。

在数据访问时，数据库会为每条sql创建一个视图。执行事务期间，以视图数据为准。读未提交 隔离级别直接返回记录上最新的值；读提交 是在sql执行前创建视图；可重复读 是在事务开始时创建视图；串行化没有视图概念。

事务隔离的实现

利用mvcc（多版本并发控制）达到事务隔离的目的

• 在MySQL中，每条记录更新，都会对应一条回滚操作的日志。每个事务对该记录的访问，根据不同视图位置，回滚到不同值。
• 当没有视图指向历史记录时，对应的回滚日志就会被删除
• 不建议使用长事务。长事务会导致系统中存储很老的事务视图，因此很老的回滚日志都会被保存

事物的启动方式

• 显示启动事务。begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
• set autocommit=0，这个命令会将这个线程的自动提交关掉。执行一条sql就会启动事务，且不会自动提交。直到主动执行 commit 或者 rollback

建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

• commit work and chain 提交事务并开启下一个事务。可以省一次begin交互

• 可以在 information_schema 库的 innodb_trx 这个表中查询长事务

索引

索引的出现是为了提高数据查询的效率，就想书的目录一样。

常见的索引模型

哈希表、有序数组、搜索树

• 哈希表：就是键值对。输入key，查到对应的value。实现思路：利用哈希函数把key换算成一个确定的位置，将值存到该位置对应的数组中。
  • 哈希值会冲突，拉链法解决
  • 无序，区间查询很慢
  • 新加元素方便，直接向链表后追加
  • 适用于等值查询的场景
• 有序数组：将数据按照一定顺序存入数组
  • 查询效率高，二分法查询即可
  • 支持范围查询，二分法查找范围第一个，向后遍历取n个即可
  • 更新数据麻烦，插入或删除一个，就需要移动后边所有
  • 适用于静态存储引擎
• N叉树：查询修改性能优，适配磁盘的访问模式。
• 跳表
• LSM树

InnoDB的索引模型

好文当赏
InnoDB选用B+树作为索引模型。B+树能够很好的配合磁盘的读写特性，减少单词查询的磁盘访问次数。

B树结构

B树每个节点保存多个索引，所有的节点都会存储数据，父节点中的元素不会在子节点出现

B+树结构

只有叶子节点存储元素，叶子节点之间使用双向指针连接起来，方便范围查找

MySQL每次从磁盘读取一页（默认16k），根据索引大小很容易可以计算得到B+树每个节点保存的索引数量。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

• 每一个索引对应在InnoDB中就是一颗B+树

• 建一张表
  mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

• 
  按叶子节点内容，索引分为主键索引和非主键索引

• 主键索引的叶子节点存的是整行数据，主键索引也被称为聚簇索引

• 非主键索引的内容一般是主键的值，也被称为二级索引

主键索引和普通索引查询的区别

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

非主键索引的查询要多扫描一颗索引树。

索引维护

以上图为例

• 插入ID值 700，直接在R5后边添加。如果插入ID值为400，则需要挪动后边的数据
• 页分裂：R5所在数据页已经满了，则需要新申请数据页，然后移动部分数据过去
  • 性能自然会受影响
  • 还会影响数据页的利用率。整体下降50%
• 页合并：数据被删除，利用率很低时，就会将两个数据页进行合并。分裂的逆过程

自增主键的场景

适合用自增主键的场景：

• 插入新记录时不指定主键的值，默认取当前最大值加一。这样符合递增插入，每次插入都是追加操作，不会挪动其他数据，不会导致叶子节点分裂
• 如果主键太长（如身份证号），则合适用自增主键节省空间。
  不合适的场景：
• 只有一个索引，且该索引必须是唯一索引。典型的KV场景
  • 没有其他索引，不用考虑其他所有叶子节点大小的问题
  • 尽量使用主键索引可以避免回表

索引优化

• 避免回表

覆盖索引

索引中的数据包含了要查询的数据，称之为覆盖索引，可以减少树的查询次数，避免回表，提升性能

考虑是否需要将数据冗余到索引。建立索引是有代价的，创建树占内存。

最左前缀原则

不管是联合索引中的几个索引，还是字符串索引的字符。只要满足最左前缀，都可以使用索引

联合索引可以复用。如果联合索引不能满足时，尝试调整索引顺序。然后给数据小的字段单独建立索引。

索引下推

在多个查询条件时，数据库按第一个条件查到主键，然后依次回表确认其余条件是否满足

MySQL在5.6之后引入索引下推。第一个条件筛选完之后，对索引中包含的元素先进行条件判断，可以有效减少部分回表。

锁

MySQL的锁按作用范围，分为全局锁，表锁和行锁

全局锁

• 全局锁就是对整个数据库实例加锁
• 典型使用场景：全库逻辑备份。通过让整个数据库只读，然后进行备份
• 在所有表支持事务的情况下，可以在可重复读隔离级别下创建事务进行备份。由于mvcc，备份的所有库逻辑一致。数据库也可正常对外提供服务
• 不建议使用 set global readonly=true 的方式
  • readonly字段有时候会用来做其他逻辑，比如判断主存库
  • 客户端异常时，加全局库会主动释放。而readonly状态则会一直保持

表锁

MySQL表锁有两种：表锁和元数据锁（metadata lock）

• 表锁：lock tables … read/write。可以主动释放锁，也可等连接断开时释放。在有更细粒度的锁的引擎中，一般不使用，影响面比较大
• MDL锁：在访问一个表时会被自动加上。为了保证表结构安全。
  • 增删改查操作时（DML），加元数据锁的读锁
  • 修改表结构时（DDL），加元数据锁的写锁
  • 读锁之间不互斥，可以多个线程访问同一张表
  • 写读，写写互斥。保证表结构安全。加字段操作只能一个一个来。
  • 给一个表加MDL锁，在语句执行时申请，在事务提交时才会释放

给一个表加字段，修改字段，加索引需要全表扫描

如何安全的给小表加字段？

• 处理掉长事务。否则长事务不提交，就会一直占用MDL锁
• 如果请求频繁，刚处理掉又建立连接。应该给修改表结构的语句设置过期时间，在一定时间内获取MDL写锁，获取到则正常修改，获取不到就过期，不要影响后续任务。可以再重复尝试这个命令。

行锁

• myISAM不支持行锁。

• InnoDB行锁是在需要的时候加上，等到事务结束才会释放。

  • 两阶段锁协议
  • 如果事务中需要锁多个行，把最可能冲突的锁尽量往后放

死锁和死锁检测

两钟策略

• 直接进入等待，直到超时。
  • 这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  • InnoDB默认50s。
  • 超时时间不好把控。
• 发起死锁检测，主动回滚死锁链中的某一事物，解开死锁。
  • 将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
  • 代价很高，每次事务加锁都要检测会不会死锁，占用cpu
    • 确认业务没有并发风险，可以关掉死锁检测。
    • 控制并发度，将死锁检测消耗控制在低成本

MVCC

• 在事务启动的时候，会创建一个当前库的快照，即视图。

• 每个事务的视图，不是真的copy一份，而是通过回滚日志算出来的。每个事务都会有一个按申请顺序递增的事务ID，InnoDB会为每个事务构造一个数组，用来保存事务启动时，活跃的事务ID。根据事务ID和可见性规则（下边三条），可以得到该视图下，某个数据可见的的值

  • 每次更新数据，就会以该事务ID作为该数据版本号。
  • 根据可见性，回滚数据到当前视图可见的数据版本。

• 

• 版本未提交，不可见；

• 版本已提交，但是是在视图创建后提交的，不可见；

• 版本已提交，而且是在视图创建前提交的，可见。

更新逻辑

• 更新数据不会取当前视图的数据进行操作，而是会读取当前最新值进行更新，否则会覆盖别的事务修改。成为“当前读”

• 查询数据如果加锁，也会读当前值。

• 如果A事务修改值，还未提交。B事务去做修改，修改前需要“当前读”，需要加锁。A事务修改完还未提交，即未释放行锁。B事务会阻塞，直到获取锁才能进行修改（一句话：如果当前修改的记录的行锁被其他事务占用，则会进入锁等待）

读提交和可重复读的区别

• 可重复读：事务开始时创建一致性视图。后续普通查询都需要使用该一致性试图（当前读除外）。只承认在事务启动前就提交的数据
• 读提交：每一个语句执行前都会重新计算视图。承认在语句执行前提交成功的数据
• 当前读：总是取最新数据