开篇词

从源码分析学习原理

最佳实践，为什么？问题，如何解决，如何定位？

  • 正在使用 MySQL
  • 知道如何写出逻辑正确的 SQL 语句来实现业务目标
  • 不确定这个语句是不是最优的
  • 听说了一些使用数据库的最佳实践，更想了解为什么这么做
  • 使用的数据库偶尔会出问题，亟需了解如何更快速准确的定位问题
  • 解决问题

基础架构：一条SQL查询语句是如何执行的？

MySQL 极客时间 学习笔记 - 图1

Server 层

  1.  Server 层包括连接器，查询缓存，分析器，优化器，执行器等，涵盖大多数核心功能，以及所有的内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程，触发器，视图等。

连接器

mysql -h$ip -P$port -u$user -p

show processlist 显示连接信息

image.png

wait_timeout 默认 8小时，超时时间；

全部使用长连接 内存消耗问题 ，定期断开，或者5.7+ msyql_reset_connection 重新初始化连接资源；

:::info 配置 mysql 环境变量 PATH bin目录 :::

查询缓存

语句和结果 k-v

查询缓存利大于弊，失效非常频繁，可以按需使用，MySQL8.0+ 彻底移除查询缓存功能

分析器

SQL语句解析，先做 词法分析，语法分析，语法错误等。

优化器

决定选择使用哪个索引，多表关联时决定表的连接顺序等

执行器

首先判断有没有执行查询的权限
打开表，根据表的引擎定义，使用其提供的接口

慢查询日志
row_examined 字段，表示语句执行过程中扫描了多少行

存储引擎

负责数据的存储和提取，架构模式是插件式的，支持InnoDB，MyISAM，Memory等多个存储引擎。
InnoDB 从 MySQL 5.5.5 版本开始成为了默认存储引擎

小结与课后问题

《高性能mysql》里提到解析器和预处理器。
解析器 处理语法和解析查询, 生成一课对应的解析树 。
预处理器进一步检查解析树的合法。比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。

所以我觉得课后习题的错误应该发生在在分析器处理阶段

日志系统：一条SQL更新语句是如何执行的？

MySQL 可以恢复到半个月内任意一秒的状态！

重要的日志模块 redo log （重做日志）和 binlog （归档日志）

【1】redo log

write_ahead logging 先写日志，再写磁盘

通过redo log，InnoDB 就可以保证就是数据库发生异常重启，之前提交的记录都不会丢失，这个能力称之为
crash-safe

InnoDB的 redo log 是固定大小的；

redo log 是 InnoDB 引擎特有的日志；

【2】binlog

binlog 是MySQL 的 server 层实现的，所有引擎都可以使用，redo log 是物理日志，binlog 是逻辑日志，记录的是这个语句的原始逻辑。 redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的，不会覆盖以前的日志。

两阶段提交

innodb_flush_log_at_trx_commit 设置成1 表示每次事务的redo log 都直接持久化到磁盘
sync_binlog 设置成1 表示每次事务的binlog都持久化到磁盘

事务隔离：为什么你改了我还看不见

事务要保证一组数据库操作要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。MySQL原生的MyISAM引擎不支持事务。

ACID

  • atomicity 原子性
  • consistency 一致性
  • isolation 隔离性
  • durability 持久性

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read），不可重复读（non-repeatable read），幻
读（phantom read）的问题。“隔离级别”用来解决这些问题。

隔离的越严实，效率就会越低。
_
SQL 标准的事务隔离级别包括：

  1. 读未提交（read uncommitted）：一个事物还未提交，它做的变更就能被别的事务看到
  2. 读提交（read committed）：一个事务提交之后，它做的变更才会被其它事务看到
  3. 可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在此级别下，未提交变更对其他事务也是不可见的。
  4. 串行化（serializable）：对于同一记录，写会加“写锁”，读会加“读锁”。当出现读写锁冲突时，后访问的事务必须等待前一个事务执行完成，才能继续执行。

在实现上，数据可里面会创建一个试图，访问的时候是以视图的逻辑结果为准。在“可重复读”隔离级别下，这个试图是在事务启动时创建的，整个事务存在期间都在用这个试图。在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而串行化隔离级别下直接用加锁的方式来避免并行访问。

image.png

事务隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

回滚日志会在不需要的时候删除，即当系统中没有这个回滚日志更早的read-view的时候。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

事务的启动方式

  1. 显式启动事务语句，begin 或 start transaction。配套的提交语句是commit，回滚 rollback。
  2. set autocommit = 0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，并且并不会自动提交。这个事务持续存在知道你主动执行 commit 或 rollback 语句，或者断开连接。

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

  1. SELECT
  2.     *
  3. FROM
  4.     information_schema.innodb_trx
  5. WHERE
  6.     TIME_TO_SEC(
  7.         timediff(now(), trx_started)
  8.     ) > 60

:::info Q:一天一备和一周一备的对比？

A:好处是，最长恢复时间更短，在一天一备的模式里，最坏情况下需要应用一天的binlog，一周一备的最坏情况就要应用一周的binlog，更频繁的全量备份需要消耗更多存储空间。根据业务重要性取舍权衡。
_Q:如果你是业务开发负责人同时也是数据库负责人，有什么方案来避免或者处理长事务的情况？
_
XM:使用显式启动事务语句，监控长事务，扩展逻辑内存空间。

A：首先，从应用开发端来看

  1. 确认是否使用了 set autocommit = 0，测试环境，开启 MySQL 的 general_log，通过日志确认
  2. 确认是否有不必要的只读事务。
  3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。 其次，从数据库端来看：
  4. 监控 information——schema.Innodb_trx 表，设置长事务阈值，超过就报警或者kill
  5. Percona 的 pt-kill 工具
  6. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
  7. 如果使用的是 MySQL5.6 或者更新版本，把 innodb_undo_tablespaces 设置成2（或者更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。 :::

深入浅出索引

索引是数据可系统里面最重要的概念之一。
**
索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种。可以用于提高读写效率的数据结构很多，比如哈希表，有序数组和搜索树等。

哈希表是一种以键值对存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即value。哈希的思路就是把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

哈希表这种结构适用于只有等值查询的场景，比如 memcached 以及一些nosql 引擎。

有序数组在等值查询和范围查询场景中的性能都非常优秀，但是只适合用于静态存储引擎，因为更新数据成本很高。

二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子。二叉树是搜索效率最高的。

N叉树由于在读写上的性能有点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

不管是哈希表还是有序数组，或者N叉树，它们都是不断迭代，不断优化的产物或者解决方案。数据库技术发展到今天，跳表、LSM树等数据结构也被用于引擎设计中。

数据库底层存储的核心就是基于这些数据模型的。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

索引类型分为主键索引和非主键索引：
主键索引的叶子节点存的是整行数据，在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）；非主键索引的叶子结点内容是主键的值，非主键索引也被称为二级索引（secondary index）。

基于非主键索引的查询需要多扫描一颗索引树，回表，因此尽量使用主键查询。

索引维护

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

select * from T where k between 3 and 5

  1. 在k索引树上找到k=3的记录，取得ID=300
  2. 再到ID索引树上查到ID=300 对应的数据行
  3. k=5,ID=500
  4. ID=500对应的数据
  5. k索引树取值，不满足，循环结束

覆盖索引

覆盖索引，不需要回表，可以直接提供查询结果。

索引字段的维护总是有代价的，建立冗余索引来支持覆盖索引时就需要根据实际业务进行权衡。

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

For example：

结论：不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
**
在建立联合索引的时候，如何安排索引内的字段顺序。评估标准是索引的复用能力，第一原则是，如果通过调整顺序，可以少维护一个索引，那个这个顺序往往就是需要优先考虑采用的。同时还要考虑空间问题。

索引下推

MySQL5.6 引入的索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

小结

在满足语句需求的情况下，尽量少地访问资源是数据可设计的重要原则之一。
**
Q:课后习题：

  1. CREATE TABLE `geek` (
  2.   `a` int(11) NOT NULL,
  3.   `b` int(11) NOT NULL,
  4.   `c` int(11) NOT NULL,
  5.   `d` int(11) NOT NULL,
  6.   PRIMARY KEY (`a`,`b`),
  7.   KEY `c` (`c`),
  8.   KEY `ca` (`c`,`a`),
  9.   KEY `cb` (`c`,`b`)
  10. ) ENGINE=InnoDB;
  12. select * from geek where c=N order by a limit 1;
  13. select * from geek where c=N order by b limit 1;

补充：
重建主键，不论是删除主键还是创建主键，都会将整个表重建。
alter table T engine = InnoDB
_

全局锁和表锁

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁、行锁三类。

全局锁

全局锁就是对整个数据库实例加锁，Flush tables with read lock（FTWRL）。

表级锁

MySQL 里面表级锁有两种：表锁和元数据锁（meta data lock，MDL）

MDL 不需要显式使用，在访问一个表的时候会被自动加上。作用是保证读写的正确性。

小结

全局锁主要用在逻辑备份过程汇总，对于全部是 InnoDB引擎的库，建议使用 -single -transaction 参数；
表锁一般是在数据库引擎不支持行锁的时候才会别用到；
MDL 会直到事务提交才释放，在做表结构变更的时候，要注意不要锁住线上查询和更新。

行锁功过：怎么减少行锁对性能的影响？

MySQL 的行锁是在引擎层由各个引擎自己实现的。

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
**

死锁和死锁检测

  • 超时释放
  • 主动死锁检测

小结

如果事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁的申请时机往后放。

事务到底是隔离的还是不隔离的

如果是可重复读隔离级别，事务T启动的时候会创建一个视图read-view，之后事务T执行期间，即使有其他事务修改了数据，事务T看到的仍然跟在启动时看到的一样。也就是说，一个在可重复读隔离级别下执行的事务，好像与世无争，不受外界影响。

事务的启动时机

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果想要马上启动一个事务，可以使用 start transaction with consistend snapshot 这个命令。

默认 autocommit = 1；语句完成时自动提交。

在MySQL里，有两个“视图”的概念：

  • 一个是 view，它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 creat view … ，而它的查询方法与表一样。
  • 另一个是 InnoDB 在实现 MVCC 时用到的一致性视图，即 consistend read view，用于支持 RC （Read Committed，读提交）和 RR（ repeatable read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

MVCC/快照

InnoDB 里面每个事务有一个唯一的事务ID，叫做 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按照申请顺序严格递增的。

对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

  • 版本未提交，不可见
  • 版本已提交，但是是在视图创建后提交的，不可见
  • 版本已提交，而且是在视图创建前提交的，可见

规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
select 语句如果加锁，也是当前读。

  1. -- S 锁，共享锁
  2. mysql> select k from t where id=1 lock in share mode;
  3. -- X 锁，排他锁
  4. mysql> select k from t where id=1 for update;

可重复读的核心就是一致性读（consistend read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就要进入锁等待。

小结

InnoDB 的行数据有多个版本，每个版本有自己的额 row trx_id，每个事务或者语句有自己的一致性视图。