mysql视频教程栏目介绍底层原理。

MYSQL

一条SQL执行过程

先看看一条查询SQL

一条 update SQL执行

update的执行 从

客户端 => ··· => 执行引擎

UPDATE

Innodb 架构

上一张 MYSQL 官方InnoDB架构图：

内部模块

连接器(JDBC、ODBC等) =>

[MYSQL 内部

[Connection Pool] (授权、线程复用、连接限制、内存检测等)
=>
[SQL Interface] (DML、DDL、Views等) [Parser] (Query Translation、Object privilege) [Optimizer] (Access Paths、 统计分析) [Caches & Buffers]
=>
[Pluggable Storage Engines]复制代码

]

=> [File]

内存结构

这里有个关键点，当我们去查询数据时候会先 拿着我们当前查询的

page

buffer pool

当前page

缓冲池

而如果是

update操作

Buffer

buffer pool

不一致

脏页

脏页数据

buffer

落盘IO

为了提高

update

架构图的缓冲池

change buffer

给change后的数据，做buffer的

unique index

change buffer

merge

落盘的IO

没有唯一索引的数据更新时

没有唯一索引的数据更新时

change buffer

唯一约束的字段

把所有数据读出来比对

写多读少

innodb_change_buffer_max_size

change buffer

buffer pool

问题又来了，merge是如何运作的

有四种情况：

有其他访问，访问到了当前页的数据，就会合并到磁盘 后台线程定时merge 系统正常shut down之前，merge一次

redo log

写满的时候，merge到磁盘

一、redo log是什么

谈到redo，就要谈到innodb的

crash safe

这样就可以在，当数据库崩溃的后，直接从

redo log

redo log 默认存储在两个文件中

ib_logfile0

ib_logfile1

固定大小的

这是因为

redo log

顺序读取

二、随机读写与顺序读写

看一张图

一般我们的数据都是分散在磁盘上的：

机械硬盘的读写顺序是：

定位到磁道 等待旋转到对应扇区 开始读写

固态读写：

直接定位到闪存芯片（这也是为啥固态比机械快） 开始读写

其实不管机械还是固态，我们去存储时，都是通过

文件系统

随机读写

顺序读写

随机读写存储的数据是分布在不同的

块

（默认 1block=8扇区=4K） 而顺序存储，顾名思义，数据是分布在

一串连续的块

中，这样读取速度就大大提升了

三、回到我们架构图

看到

buffer pool

Log Buffer

在这里，redo log具体的执行策略有三种：

不用写

Log Buffer

，只需要每秒写redo log 磁盘数据一次，性能高，但会造成数据 1s 内的一致性问题。适用于

强实时性

，

弱一致性

，比如

评论区评论

写

Log Buffer

，同时写入磁盘，性能最差，一致性最高。 适用于

弱实时性

，

强一致性

,比如

支付场景

写

Log Buffer

，同时写到

os buffer

（其会每秒调用

fsync

将数据刷入磁盘），性能好，安全性也高。这个是

实时性适中

一致性适中

的，比如

订单类

。

我们通过

innodb_flush_log_at_trx_commit

1

内存结构小结

Buffer Pool 用于加速读 Change Buffer 用于没有非唯一索引的加速写 Log Buffer 用于加速redo log写

自适应Hash索引

主要用于加快查询

页

。在查询时，Innodb通过监视索引搜索的机制来判断当前查询是否能走

Hash索引

。比如LIKE运算符和% 通配符就不能走。

硬盘结构

一、System Tablespace

存储在一个叫

ibdata1

InnoDB Data Dictionary，存储了元数据，比如表结构信息、索引等 Doublewrite Buffer 当

Buffer Pool

写入数据页时，不是直接写入到文件，而是先写入到这个区域。这样做的好处的是，一但操作系统，文件系统或者mysql挂掉，可以直接从这个

Buffer

中获取数据。 Change Buffer 当Mysql shut down的时候，修改就会被存储在磁盘这里 Undo Logs 记录事务修改操作

二、File-Per-Table Tablespaces

每一张表都有一张

.ibd

有了

每表文件表空间

可以使得

ALTER TABLE

与

TRUNCATE TABLE

性能得到很好的提升。比如

ALTER TABLE

，相较于对驻留在共享表空间中的表，在修改表时，会进行

表复制操作

，这可能会增加表空间占用的

磁盘空间量

。此类操作可能需要与表中的数据以及索引一样多的额外空间。该空间不会像

每表文件表空间

那样释放回操作系统。 可以在单独的存储设备上创建每表文件表空间数据文件，以进行I / O优化，空间管理或备份。这就意味着表数据与结构容易在不同数据库中迁移。 当发生数据损坏，备份或二进制日志不可用或无法重新启动MySQL服务器实例时，存储在单个表空间数据文件中的表可以节省时间并提高成功恢复的机会。

当然有优点就有缺陷：

存储空间的利用率低，会存在碎片，在

Drop table

的时候会影响性能（除非你自己管理了碎片） 因为每个表分成各自的表文件，操作系统不能同时进行

fsync

一次性刷入数据到文件中 mysqld会持续保持每个表文件的

文件句柄

， 以提供维持对文件的持续访问

三、General Tablespaces

通用表空间又叫

共享表空间

，他可以存储

多个表

的数据 如果存储相同数量的表，消耗的存储比

每表表空间

小

在MySQL 5.7.24中弃用了将表分区放置在常规表空间中的支持，并且在将来的MySQL版本中将不再支持。

四、Temporary Tablespaces

存储在一个叫

ibtmp1

五、Undo Tablespaces

提供修改操作的

原子性

，即当修改到一半，出现异常，可以通过Undo 日志回滚。 它存储了，事务开始前的原始数据与这次的修改操作。 Undo log 存在于回滚段（rollback segment）中，回滚段又存在

系统表空间``撤销表空间``临时表空间

中，如架构图所示。

Redo Log

前面已经介绍过

总结一下，我们执行一句update SQL 会发生什么

查询到我们要修改的那条数据，我们这里称做

origin

，返给执行器 在执行器中，修改数据，称为

modification

将

modification

刷入内存，

Buffer Pool

的

Change Buffer

引擎层：记录undo log （实现事务原子性） 引擎层：记录redo log （崩溃恢复使用） 服务层：记录bin log（记录DDL） 返回更新成功结果 数据等待被工作线程刷入磁盘

Bin log

说了

Undo

Redo

Bin log

这一个log和

innodb

引擎没有多大关系，我们前面说的那两种日志，都在是innodb引擎层的。而

Bin log

是处于

服务层

的。所以他能被各个引擎所通用 他的主要作用是什么呢？首先，

Bin log

是以事件的形式，记录了各个

DDL DML

语句，它是一种逻辑意义上的日志。 能够实现

主从复制

，

从

服务器拿到

主

服务器的

bin log

日志，然后执行。 做

数据恢复

，拿到某个时间段的日志，重新执行一遍。

跟随一条SQL语句完成全局预览后，我们来看看回过头来让SQL变得更加丰富，我们来添加一个

索引

试试

华丽的分割线

索引篇

要想彻底弄明白

InnoDB中的索引

文件存储级别

Innodb中将文件存储分为了四个级别

Pages, Extents, Segments, and Tablespaces

它们的关系是：

extent

1M

64

16KB

Page

4KB

8

512Byte

存储结构 B树变体 B+树

所以有时候，我们被要求主键为什么要是有序的原因就是，如果我们在一个有序的字段上，建立索引，然后插入数据。 在存储的时候，innodb就会按着顺序一个个存储到

页

但如果我们的字段是无序的，存储的位置就会在不同的页上。当我们的数据存储到一个已经被

存满

页

页分裂

碎片

几种不同的索引组织形式

聚簇索引，如上面

B+树

图所示，子节点上存储

行数据

，并且索引的

排列的顺序

和

索引键值顺序

一致的话就是

聚簇索引

。主键索引就是聚簇索引，除了主键索引，其他所以都是

辅助索引

辅助索引，如果我们创建了一个

辅助索引

，它的叶子节点上只存储

自己的值

和

主键索引的值

。这就意味着，如果我们通过辅助索引查询所有数据，就会先去查找

辅助索引

中的

主键键值

，然后再去

主键索引

里面，查到相关

数据

。这个过程称为

回表

rowid

如果没有

主键索引

怎么办呢？
没有主键，但是有一个 Unique key 而且都不是 null的，则会根据这个 key来创建

聚簇索引

。 那上面两种都没有呢，别担心，innodb自己维护了一个叫

rowid

的东西，根据这个id来创建

聚簇索引

索引如何起作用

搞清楚什么是索引，结构是什么之后。 我们来看看，什么时候我们要用到索引，理解了这些能更好的帮助我们创建正确高效的索引

离散度低不建索引，也就是数据之间相差不大的就没必要建立索引。（因为建立索引，在查询的时候，innodb大多数据都是相同的，我走索引 和全表没什么差别就会直接

全表查询

）。比如 性别字段。这样反而浪费了大量的存储空间。

联合字段索引，比如

idx(name, class_name)

当执行

select * from stu where class_name = xx and name = lzw

查询时，也能走

idx

这个索引的，因为优化器将SQL优化为了

name = lzw and class_name = xx

当需要有

select ··· where name = lzw

的时候，不需要创建一个单独的

name

索引，会直接走

idx

这个索引

覆盖索引

。如果我们此次查询的

所有数据

全都包含在索引里面了，就不需要再

回表

去查询了。比如：

select class_name from stu where name =lzw

索引条件下推(index_condition_pushdown)

有这样一条SQL，

select *  from stu where name = lzw and class_name like '%xx'

如果没有

索引条件下推

，因为后面是

like '%xx'

的查询条件，所以这里首先根据

name

走

idx联合索引

查询到几条数据后，再

回表

查询到

全量row数据

，然后在

server层

进行 like 过滤找到数据 如果有，则直接在

引擎层

对like也进行过滤了，相当于把

server层

这个过滤操作

下推到引擎层

了。如图所示：

建立索引注意事项

在where、order、join的on 使用次数多的时候，加上索引 离散度高的字段才能建立索引 联合索引把离散度高的放前面（因为首先根据第一个字段匹配，能迅速定位数据位置。） 频繁更新的字段不能建索引（造成

页分裂

，索引按顺序存储，如果存储页满了，再去插入就会造成页分裂） 使用比如replace、sum、count等

函数

的时候不会使用索引，所以没必要额外建 出现隐式转化的时候，比如字符串转int，也用不到索引 特别长的字段，可以截取前面几位创建索引（可以通过

select count(distinct left(name, 10))/count(*)

来看离散度，决定到底提取前几位）

优化器决定的

Cost Base Optimizer

弄懂了索引，我们就有能力打开 锁篇 的副本了

又一个华丽的分割线

锁篇

四大特性

先回顾一下我们耳熟能详的几个基本概念：

原子性（通过Undo log实现） 一致性 隔离性 持久性（崩溃恢复，Redo log + double write 实现）

读一致性问题应该由数据库的事务隔离级别来解决 （SQL92 标准）

前提，在一个事务中：

脏读（读到了别人还没有commit的数据，然后别人又回滚掉了） 不可重复读（第一次读取到了数据，然后别人修改commit了，再次去读取就读到了别人已经commit的数据） 幻读（在范围查询的时候，读到别人新添加的数据）

SQL92 标准规定： (并发度从左到右，依次降低)

Innodb中如何解决 可重复读(RR) 中产生幻读的情况

锁模型

LBCC (Lock Based Concurrency Control) 读之前加个锁，但这样可能会导致性能问题  => 读的时候加锁导致其他事务都不能读写了，性能低下 MVCC(Multi Version Concurrency Control) 读的时候记录当时快照，别人来读取快照就行 => 性能消耗，存储消耗

这两种方案在Innodb中结合使用。这里简要说明一下

RR 的 MVCC实现

RC的MVCC实现是对 同一个事务的多个读 创建一个版本

RR 是 同一个事务任何一条都创建一个版本

通过

MVCC

LBCC

不加锁

Serializable

串行

并发

下面我们来深入研究一下

InnoDB锁

RR

锁深入 MVCC在Innodb的实现

一、Innodb 的锁

Shared and Exclusive Locks 共享和排它锁 =>（S、X） Intention Locks 意向锁 => 这里指的是两把锁，其实就是

表级别

的 共享和排它锁 => (IS、IX)

上面这

四把锁

最基本锁的类型

Record Locks 记录锁 Gap Locks 间隙锁 Next-key Locks 临锁

这三把锁，理解成对于上面

四把锁

高阶锁

Insert Intention Locks 插入锁 AUTO-INC Locks 自增键锁 Predicate Locks for Spatial Indexes 专用于给Spatial Indexes用的

上面三把是额外扩展的锁

二、读写锁深入解释

要使用共享锁，在语句后面加上

lock in share mode

。排它锁默认

Insert、Update、Delete

会使用。显示使用在语句后加

for update

。 意向锁都是由数据库自己维护的。（主要作用是给表

打一个标记

，记录这个表是否被锁住了） => 如果没有这个锁，别的事务想锁住这张表的时候，就要去全表扫描是否有锁，效率太低。所以才会有意向锁的存在。

补充：Mysql中锁，到底锁的是什么

锁的是索引，那么这个时候可能有人要问了：那如果我不创建索引呢？

索引的存在，我们上面讲过了，这里再回顾一下，有下面几种情况

你建了一个 Primary key， 就是聚集索引 （存储的是

完整的数据

） 没有主键，但是有一个 Unique key 而是都不是 null的，则会根据这个 key来创建

聚簇索引

那上面两种都没有呢，别担心，innodb自己维护了一个叫

rowid

的东西，根据这个id来创建

聚簇索引

所以一个表里面，必然会存在一个索引，所以锁当然总有索引拿来锁住了。

当要给一张你没有显示创建

索引

加锁查询

锁整张表

辅助索引

select * from where name = ’xxx‘ for update

回表

辅助索引

主键索引

三、高阶锁深入解释

首先上三个概念，有这么一组数据：主键是  1，3，6，9 在存储时候有如下：x 1 x 3 x x 6 x x x 9 x···

记录锁，锁的是每个记录，也就是 1，3，6，9 间隙锁，锁的是记录间隙，每个

x

首先这三种锁都是

排它锁

当

select * from xxx where id = 3 for update

时，产生记录锁 当

select * from xxx where id = 5 for update

时，产生间隙锁 => 锁住了(3,6)，这里要格外注意一点：间隙锁之间是不冲突的。 当

select * from xxx where id = 5 for update

时，产生临键锁 => 锁住了(3,6], mysql默认使用临键锁，如果不满足 1 ，2 情况 则他的行锁的都是临键锁

Record Lock 行锁

Gap Lock 间隙锁

Gap Lock 和 Record Lock

Next-Key锁

RR级别

说到了锁那么必然逃不过要说一下死锁

发生死锁后的检查

show status like 'innodb_row_lock_%'

Innodb_row_lock_current_waits 当前正在有多少等待锁 Innodb_row_lock_time 一共等待了多少时间 Innodb_row_lock_time_avg 平均等多少时间 Innodb_row_lock_time_max 最大等多久 Innodb_row_lock_waits 一共出现过多少次等待

select * from information_schema.INNODB_TRX

能查看到当前正在运行和被锁住的事务

show full  processlist

=

select  *  from    information_schema.processlist

能查询出是

哪个用户

在哪台机器host的哪个端口上

连接哪个数据库

执行什么指令

的

状态与时间

死锁预防

保证访问数据的顺序 避免where的时候不用索引（这样会锁表，不仅死锁更容易产生，而且性能更加低下） 一个非常大的事务，拆成多个小的事务 尽量使用等值查询（就算用范围查询也要限定一个区间，而不要只开不闭，比如 id > 1 就锁住后面所有）

优化篇

分库分表

动态选择数据源

编码层 -- 实现 AbstracRoutingDataSource => 框架层 -- 实现 Mybatis Plugin => 驱动层 -- Sharding-JDBC(配置多个数据源，根据自定义实现的策略对数据进行分库分表存储)核心流程,SQL解析=>执行优化=>SQL数据库路由=>SQL改变(比如分表，改表名)=>SQL执行=>结果归并） => 代理层 -- Mycat（将所有与数据库的连接独立出来。全部由Mycat连接，其他服务访问Mycat获取数据） => 服务层 -- 特殊的SQL版本

MYSQL如何做优化

说到底我们学习这么多知识都是为了能更好使用MYSQL，那就让我们来实操一下，建立一个完整的优化体系

要想获得更好的查询性能，可以从这张

查询执行过程

一、客户端连接池

添加连接池，避免每次都

新建、销毁连接

我大概总结一下：

我们并发的执行SQL，并不会因为连接数量增多而变快。为什么呢？如果我有10000连接同时并发执行，难道不比你10个连接执行快得多吗? 答案是否定的，不仅不快反而越来越慢。
在计算机中，我们都知道只有

CPU

才能真正去执行

线程

。而操作系统因为用

时间分片

的技术，让我们以为一个

CPU内核

执行了

多个线程

。 但其实上一个

CPU

在某个

时间段

只能执行一个

线程

，所以无论我们怎么增加并发，

CPU

还是只能在这个时间段里处理这么多数据。 那就算

CPU

处理不了这么多数据，又怎么会变慢？因为

时间分片

，当多个线程看起来在

"同时执行"

，其实他们之间的

上下文切换

十分耗时 所以，一旦线程的数量超过了CPU核心的数量，再增加线程数系统就只会更慢，而不是更快。
当然，这只是其中最核心的原因，磁盘同样也会对速度有影响，同时也对我们连接数配置有影响。
比如我们用的机械硬盘，我们要通过旋转，寻址到某个位置，再进行

I/O

操作，这个时候，

CPU

就可以把时间，分片给其他

线程

，以提升处理效率和速度 所以，如果你用的是机械硬盘，我们通常可以多添加一些连接数，保持高并发 但如果你用的是 SSD 呢，因为

I/O

等待时间非常短，所以我们就不能添加过多连接数
通过来说你需要遵循这么一个公式：

线程数 = ((核心数 * 2) + 有效磁盘数)

。比如一台

i7 4core 1hard disk

的机器，就是 4 * 2 + 1 = 9 看到这个公式不知道大家是不是很眼熟，这不仅适用于数据库连接，也适用于任何

很多CPU计算和I/O的场景

比如：设置最大线程数等

二、数据库整体设计方案

第三方缓存

如果并发非常大，就不能让他们全打到数据库上，在客户端连接数据库查询时，添加如

Redis

集群方式部署数据库

既然我们一个数据库承受不了巨大的并发，那为什么不多添加几台机器呢？ 主从复制原理图

从图中我们不难看出、Mysql主从复制

读写分离

异步复制

Binary Log

relay log

slave

Binary Log Position

master info

不同方式的主从复制

上面这种

异步

事务

全同步复制，事务方式执行，主节点先写入，然后让所有slave写，必须要所有 从节点 把数据写完，才返回写成功，这样的话会大大影响写入的性能 半同步复制，只要有一个salve写入数据，就算成功。（如果需要半同步复制，主从节点都需要安装semisync_mater.so和 semisync_slave.so插件） GTID（global transaction identities）复制，主库并行复制的时候，从库也并行复制，解决主从同步复制延迟，实现自动的

failover

动作，即主节点挂掉，选举从节点后，能快速自动避免数据丢失。

集群高可用方案

主从 HAPrxoy + keeplive NDB Glaera Cluster for MySQL MHA（Master-Mater replication manager for MySQL），MMM（MySQL Master High Available） MGR（MySQL Group Replication） => MySQL Cluster

分表

对数据进行分类划分，分成不同表，减少对单一表造成过多

锁操作

表结构

设计合理字段类型 设计合理字段长度

三、优化器与执行引擎

慢日志

开启

show_query_log

long_query_time

mysqldumpslow /var/lib/mysql/mysql-slow.log

explain分析SQL

任何SQL在写完之后都应该

explain

1. 驱动表  -  比如滥用

left/right join

导致性能低下

使用

left/right join

会直接指定驱动表，在MYSQL中，默认使用

Nest loop join

进行表关联（即通过

驱动表

的结果集作为循环基础数据，然后通过此集合中的每一条数据筛选下一个关联表的数据，最后合并结果，得出我们常说的

临时表

）。 如果

驱动表

的数据是

百万千万

级别的，可想而知这联表查询得有多慢。但是反过来，如果以

小表

作为

驱动表

，借助

千万级表

的

索引

查询就能变得很快。 如果你不确定到底该用谁来作为

驱动表

，那么请交给优化器来决定，比如：

select xxx from table1, table2, table3 where ···

，优化器会将查询记录行数少的表作为驱动表。 如果你就是想自己指定

驱动表

，那么请拿好

Explain

武器，在

Explain

的结果中，第一个就是

基础驱动表

排序。同样的，对不同

表

排序也是有很大的性能差异，我们尽量对

驱动表

进行排序，而不要对

临时表，也就是合并后的结果集

进行排序。即执行计划中出现了

using temporary

，就需要进行优化。

2. 执行计划各参数含义

select_type（查询的类型）：

普通查询

和

复杂查询

（联合查询、子查询等）

SIMPLE

，查询不包含子查询或者UNION

PRIMARY

，如果查询包含

复杂查询

的子结构，那么就需要用到主键查询

SUBQUERY

，在

select

或者

where

中包含

子查询

DERIVED

，在

from

中包含子查询

UNION RESULT

，从

union

表查询子查询
table 使用到的表名 type（访问类型），找到所需行的方式，从上往下，查询速度

越来越快

const或者system

常量级别的扫描，查询表最快的一种，system是const的一种特殊情况（表中只有一条数据）

eq_ref

唯一性索引扫描

ref

非唯一性索引扫描

range

索引的范围扫描，比如 between、等范围查询

index

（index full）扫描全部索引树

ALL

扫描全表

NULL

，不需要访问表或者索引
possible_keys，给出使用哪个索引能找到表中的记录。这里被列出的索引

不一定使用

key：到底

哪一个索引被真正使用

到了。如果没有则为NULL key_len：使用的索引所占用的字节数 ref：哪个字段或者常数和

索引（key）

一起被使用 rows：一共扫描了多少行 filtered（百分比）：有多少数据在server层还进行了过滤 Extra：额外信息

only index

信息只需要从索引中查出，可能用到了覆盖索引，查询非常快

using where

如果查询没有使用索引，这里会在

server

层过滤再使用

where

来过滤结果集

impossible where

啥也没查出来

using filesort

，只要没有通过索引来排序，而是使用了其他排序的方式就是 filesort

using temporary

（需要通过临时表来对结果集进行暂时存储，然后再进行计算。）一般来说这种情况都是进行了

DISTINCT、排序、分组

using index condition

索引下推，上文讲过，就是把

server层

这个过滤操作

下推到引擎层

四、存储引擎

当仅仅是

插入与查询

比较多的时候，可以使用

MyISAM

存储引擎 当只是使用临时数据，可以使用

memory

当

插入、更新、查询

等并发数很多时，可以使用

InnoDB

总结

从五个层次回答MYSQL优化，由上至下

SQL与索引 存储引擎与表结构 数据库架构 MySQL配置 硬件与操作系统

除此之外，查数据慢，要不仅仅拘留于一味的 "优化" 数据库，而是要从业务应用层面去分析。比如对数据进行缓存，对请求进行限流等。

我们下篇文章见