mysql实战_Mysql实战45讲

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

MySql 逻辑架构图

image.png

image.png

执行 create table 建表的时候，如果不指定引擎类型，默认使用InnoDB。

不同的存储引擎共用一个Server 层。

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

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

一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建连接才会使用新的权限设置。

查看连接信息

image.png

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是8小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。

查询缓存

image.png

image.png

mysql> select SQL_CACHE * from T where id = 10

MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

分析器

词法分析(识别关键字，操作，表名，列名)

语法分析 (判断是否符合语法)

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

执行器

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限。如果没有，就会返回没有权限的错误

。如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去调用这个引擎提供的接口。

在数据库的慢查询日志中看到一个rows_examined 的字段，表示这个语句执行过程中扫描了多少行，这个值就是在执行器每次调用引擎获取数据航的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

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

查询语句的那一套流程，更新语句也是同样会走一遍。

mysql> create table T(ID int primary key, c int);

mysql> update T set c=c+1 where ID=2;

执行语句前要先连接数据库，这是连接器的工作。

在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。

分析器会通过词法和语法解析知道这是一条更新语句。

优化器决定要使用 ID 这个索引。

执行器负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log 和binlog。

redo log

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时再写账本。

image.png

image.png

image.png

binlog

MySQL 整体来看，其实就有两块：一块是 Server 层,它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关事宜。

粉板 redo log 是 InnoDB 引擎特有的日志，而server层也有自己的日志，称为 binlog(归档日志)。

redolog 与 binlog的区别：

image.png

有了对这两个日志的概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

image.png

image.png

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog,或者采用反过来的顺序。我们看看这两种方式会有什么问题。

image.png

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

binlog作用

恢复数据库(全量备份 + 备份时间点后的binglog)

扩容搭建备库(全量备份 + 备份时间点后的binglog)

同步数据

image.png

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

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败

在 MySQL 中，事务支持是在引擎层实现的。MySQL 原生的 MyISAM 引擎就不支持事务，这也是MyISAM 被 InnoDB 取代的重要原因之一。

ACID(Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性)。

image.png

image.png

image.png

mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

image.png

image.png

你可以在 information_schema 库的innodb_trx 这个表中查询长事务，比如下面这个语句查询超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

04 | 深入浅出索引(上)

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

索引的常见模型

哈希表

哈希表是一种以键 - 值(key-value)存储数据的结构,输入待查找的值即 key，就可以找到其对应的值即 Value.

用一个哈希函数把 key 换算成一个确定的位置，然后把 把 value 放在数组的这个位置。

多个 key 值经过哈希函数的换算，会出现同一个值的情况(哈希冲突)。处理这种情况的一种方法是，拉出一个链表。

增删改，等值查询很快， 但因为不是有序的，哈希索引做区间查询的速度是很慢的。

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

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

有序数组索引只适用于静态存储引擎。

二叉搜索树

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子节点。查询复杂度是O(logN)。

当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(logN)。

image.png

覆盖索引

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

最左前缀原则

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

查询条件匹配复合索引最左的N个字段，就可以使用该联合索引。

字符串查询条件可以根据字符串最左M个字符，来匹配索引。

在建立联合索引的时候，如何安排索引内的字段顺序？

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

例如，有a,b 联合查询的需求，又有b独立查询的需求，这时应该建联合索引(b,a),而不是(a,b) 。

索引条件下推(index condition pushdown)

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

例如有联合索引(a,b), 查询条件是a=x and b=y and c=z, 在Mysql5.6前，先在联合索引中找到a=x的所有记录，然后回表在主键索引中匹配b,c条件。利用索引下推则可以直接在索引(a,b)中匹配a,b 条件，减少回表的记录数。

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

image.png

image.png

同步过程中，如果加全局锁，关联的表数据(例如订单、余额)会有一致性问题。

image.png

image.png

image.png

image.png

事务中的 MDL 锁(读锁、写锁)，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

image.png

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

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB替代的重要原因之一。

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

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

死锁和死锁检测

image.png

image.png

image.png