MySQL笔记（基础篇）

目录

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

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

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

深入浅出索引

全局锁和表锁 ：给表加个字段怎么有这么多阻碍

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

---

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

第一步，连接器，连接到数据库

分为长连接和短连接，建议使用长连接，但是长连接随着执行过程会造成内存占用太大，解决方法如下：

• 定期断开长连接
• 在5.7版本后使用mysql_reset_connection 来重新初始化连接资源

第二步，查询缓存，如果有缓存则直接返回

建议不使用缓存（将query_cache_type 设置成 DEMAND），因为只要表一更新，就会清空缓存，命中率低

第三步，分析器，做词法分析和语法分析

用于判断SQL语句是否正确

第四步，优化器，选择合适的索引

第五步，执行器

判断是否有权限执行查询，循环取数据，直到表的最后一行

---

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

redo log（重做日志）	binlog（归档日志）
InnoDB引擎特有，保证数据库异常重启是之前提交的记录不会丢失（crash-safe）	Server层实现的，所有引擎都可使用
物理日志，“在某个数据页做了什么修改”	逻辑日志，“给ID=2的字段a+1”
循环写，空间用完后要更新到磁盘	追加写，可以切换到下一个文件继续写，不会覆盖

 

Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

二阶段提交：redo log分prepare和commit状态，分2次完成

innodb_flush_log_at_trx_commit= 1 ，表示每次事务的 redo log 都直接持久化到磁盘

sync_binlog = 1 ，表示每次事务的 binlog 都持久化到磁盘

---

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

ACID：原子性、一致性、隔离性、持久性

多个事务同时进行可能出现问题：脏读、不可重复读、幻读

隔离级别：读未提交、读提交、可重复读、串行化

一致性视图：可重复读会在事务开始时创建，读提交会在SQL执行时创建

transaction-isolation 的值设置成 READ-COMMITTED，设置隔离级别为读提交

每条记录在执行更新时都会同时记录一条回滚记录，当没有事务再使用到回滚日志时，删除

不要使用长事务，会有大量的回滚日志占用空间

set autocommit=1，自动提交，通过显示语句的方式来启动事务

---

深入浅出索引

InnoDB使用B+树索引模型，每个索引对应一颗B+树

主键索引（聚簇索引）的叶子节点存放的是数据

菲主键索引（二级索引）的叶子节点存放的是主键值

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

回表：基于非主键索引的查询语句，需要先通过非主键索引树获取主键，然后用主键去主键索引树获取数据

页分裂与合并：在数据插入、删除过程中，需要移动操作数据行后面的数据，这可能需要申请一个新页或合并页

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

对于上表，执行如下的查询语句

select * from T where k between 3 and 5

执行流程如下：

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，取得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

如果执行的语句是

select ID from T where k between 3 and 5

因为ID已经在k索引上，不需要再去ID索引上查找数据，因此可以直接返回数据，不用回表，这称为覆盖索引

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

索引下推优化：MySQL5.6，在索引遍历过程中，对索引包含的字段先进行判断，看是否满足条件，从而减少回表

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

无索引下推执行过程

有索引下推执行过程

 

---

全局锁和表锁 ：给表加个字段怎么有这么多阻碍

MySQL里的锁可以大致分为：全局锁、表级锁（表锁、元数据锁meta data lock，MDL）、行锁

全局锁，用于数据备份：

Flush tables with read lock; (FTWRL)

表锁：

lock tables … read/write

unlock tables

元数据锁：MDL 不需要显式使用，在访问一个表的时候会被自动加上，一般在不支持行锁时才会使用

当对一个表做增删改查操作的时候，加 MDL 读锁

当对一个表做结构变更操作的时候，加 MDL 写锁

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

如何安全地给小表加字段

• 解决长事务。事务不提交，就会一直占着MDL锁，通过information_schema 库的 innodb_trx 表可以查看正在执行的长事务
• 在alter table语句设置等待时间

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

---

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

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

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

死锁：

事务 A 在等待事务 B 释放 id=2 的行锁，事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放

两种策略处理死锁：

1. 等待超时，通过参数innodb_lock_wait_timeout设置（默认50s）
2. 开启死锁检测，将参数 innodb_deadlock_detect 设置为 on（默认on）

死锁检测：每当一个事务被锁时，检测它锁依赖的线程有没有被别人锁住，循环，最后判断是否出现循环等待

每当一个事务加入时，都要判断是否因为自己的加入造成了死锁，这是一个时间复杂度O（n）的操作，如果有1000个事务同时操作一行，那么死锁检测是100万量级的，会导致CPU利用率很高，但执行的事务数却很低

可以通过将一行数据改成多行来减少并发，比如公司账户总额，可以放在10行数据里，这10行数据的总和就是公司账户总额，在操作时随机分配一行进行操作

---

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

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。

如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令

事务B查到的k的值是 3

事务A查到的k的值是 1

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）

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

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。

另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现

“快照”在 MVCC 里是怎么工作的

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

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。