Mysql 笔记

mysql 执行步骤

show processlist；//显示所有进程

过程讲解：

总结

执行一条 SQL 查询语句，期间发生了什么？

连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
- 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
- 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
redo log 及 binlog

redo：

redo属于存储引擎层（如InnoDB),记录了数据的偏移，适当时间会更新到硬盘。环形结构。

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”

binlog：

binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”

更新时逻辑，两阶段提交。

先redo log（prepare阶段），再binlog，最后commit

隔离级别

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

尽量不要使用长事务

当没有比回滚日志更早的读视图（读视图在事务开启时创建）的时候，这个数据不会再有谁驱使它回滚了，这个回滚日志也就失去了用武之地，可以删除了

-- 查询超过60s的长事务
select * from information_schema.innodb_trx 
where TIME_TO_SEC(timediff(now(),trx_started))>60

MySQL 的事务启动方式有以下几种：

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

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

如何避免长事务对业务的影响？

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

确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

其次，从数据库端来看：

监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
Percona 的 pt-kill 这个工具不错，推荐使用；
在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

MVCC

基于以上，在可重复读和读已提交两个事务隔离级别中，在执行普通 SELECT 语句时都会获取 Read View，并针对行记录的版本链进行遍历，并遵循如下规则：

1）如果遇到某个版本的事务 id 等于当前事务 id，直接选取该版本（同一个事务修改的内容，哪怕是草稿态也要读取到）

2）遍历找到首个事务 id 不在 trx_list 的事务（代表该版本是已提交的最新版数据）作为选取的版本

而可重复读和读已提交的区别就在于：

• 【读已提交】 会在每次执行 SELECT 查询时，实时获取最新的 Read View 视图
• 【可重复读】 只在事务开启时获取一次 Read View，并在整个生命周期进行复用. 同时针对上述第 2）条，还需要保证选取版本的事务 id < low_limit_id. 这样就能屏蔽事务开启后其它并发事务的一切修改行为，进而保证当前事务视角的一致性
索引（上）

N叉树：

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

每个节点存放索引个数：

MySql默认一个节点的长度为16K，一个整数（bigint）字段索引的长度为 8B,另外每个索引还跟着6B的指向其子树的指针；所以16K/14B ≈ 1170

基于非主键索引的查询需要多扫描一棵索引树，即回表。因此，我们在应用中应该尽量使用主键查询

页分裂与合并：

新增数据可能页分裂，需申请新数据页，挪动部分数据过去，性能会受影响，空间利用率也会降低
删除数据可能页合并，当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程

尽量使用自增主键索引：

因为普通索引的叶子节点存放的是主键，所以主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，尽量使用自增主键

重建主键索引是否合理？

重建二级索引 k 的做法是合理的，可以达到省空间的目的。

但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替：

alter table T engine=InnoDB

索引（下）

覆盖索引

是否联合索引，都可覆盖

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

联合索引- 最左前缀原则

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引

联合索引-索引下推

我以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”

select * from tuser where name like '张%' and age=10 and ismale=1;

MySQL 5.6 之前，name满足后，回表，查询age、ismale是否满足。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。也就是name满足后，判断age是否满足，满足后再回表，减少回表次数

全局锁和表锁

全局读锁：

全局读锁①，会话结束时，锁会失效

Flush tables with read lock (FTWRL)

全局读锁②，可用来做主从库判断

-- 如果客户端发生异常，则数据库就会一直保持 readonly 状态，风险较大
set global readonly=true

mysqldump 数据备份

当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的

表锁：

锁1：

与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放.表锁的语法是：lock tables … read/write 锁2：

增删改查：加 MDL 读锁

结构变更操作：加MDL 写锁

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

名称解释：

DML：增删改数据

DDL：修改表结构的操作

MDL（meta data lock) ：元数据锁

如何安全地给小表加字段?

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务

要是请求很频繁：

这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程

MariaDB 已经合并了 AliSQL 的这个功能：

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

MySQL 的事务启动方式有以下几种：#

如何避免长事务对业务的影响？#

N叉树：#

每个节点存放索引个数：#

页分裂与合并：#

尽量使用自增主键索引：#

重建主键索引是否合理？#

覆盖索引#

联合索引- 最左前缀原则#

联合索引-索引下推#

全局读锁：#

mysqldump 数据备份#

表锁：#

名称解释：#