mysql

mysql

服务层

概述：

服务层对数据库连接及用户权限验证及sql语句校验的过程，最终对符合要求的sql优化解析交给存储引擎执行。

流程图示：

执行流程：

1、负责外部应用的数据库连接及用户权限验证（用户登陆后权限调整必须用户二次登陆才可以生效）。
2、sql语句首先查询缓存（mysql8.0取消缓存机制缓存失效得不偿失）。
3、解析器对sql语句校验及解析。
4、优化器对解析后的sql重新排列，多种执行策略。
5、执行器，对优化后的sql，权限验证并选择执行策略并交给执行引擎执行。

执行图示：

存储引擎层

​ 负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎(经常用的也是这个)。

sql解析顺序

日志

binlog：二进制日志，属于服务层不区分存储引擎，集群环境中同步数据就是依赖二进制日志,需要手动开启。
relaylog：中继日志，集群数据同步时暂存主节点的发送的日志并记录日志主节点的二进制文件位置。
undolog：innodb引擎所有记录事物提交的操作版本日志，MVCC多版本并发控制基于此实现，只记录事物执行insert、update和delete前后快照。
redolog：用来记录的是事务的最新结果。

bin log

binlog记录所有数据库结构变更，（create、alter table）以及表数据修改（insert、update、delete）的二进制日志。

三种模式：

statement模式

该模式下只会记录引起数据结构变更的语句

优势：不记录实际的sql语句所以I/O开销小，性能较好

劣势：有些操作不稳定，UUID随着事务的回滚，依赖binlog回放的数据与原数据必然不同。

row模式

该模式下，记录每次操作数据的源数据与修改后的目标数据。

优势：可以精准的还原数据，从而保证数据的安全可靠，复制和恢复数据可以并发执行。

劣势：同理详细的日志必然体积很大，对于修改频繁的数据或者字短恢复很消耗性能。阅读需要特殊指令读取数据。

MIXED模式

上述两种模式混合使用

绝大部分操作使用Statement模式

使用UUID、表的存储引擎为inndb、使用insert delay使用临时表等情况。

主从同步：

示例图

1、主节点开启binlog

2、从节点开启一个线程扮演mysql客户端通过mysql协议，请求主节点二进制文件中的事件。

3、主节点启动一个线程dump thread，检查自己的二进制文件中的事件，跟请方的位置对比，如果没有携带请求位置，主节点则把二进制文件全部发送。

4、从节点接收后把文件放在中继日志relay log，并记录修改请求主节点二进制文件的位置。

5、从节点在启动一个线程，sql thread 把relay中的事件读取消费还原。

mysql复制的方式是异步的，并且复制的时候有并行复制的能力。主库把文件发送从库就不管了，假如主库挂掉，从库处理relay日志失败。就会导致数据丢失。

全量同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半量同步复制

半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

redo log

是一种策略，在数据更新的时候不是为了节约时间和资源，对更新操作进行数据内存更新后直接把数据修改记录临时写在redolog中。等mysql空闲的时候再把数据重新写到磁盘中。这种方式也称之为write head logging，先写日志后刷磁盘。

redolog的数据存储方式的特殊性（类似转盘）在数据存储不下的时候需要将部分数据写入磁盘，擦出部分数据。有write pos 和check point两个指针。

• write ps：记录写的位置。
• check point：记录要擦除的位置。

当两者位置快一样时，说明空间已满。

bin log 和 redo log区别

1. bin log 是服务层，redo log 是innodb层面所有。
2. bin log 根据不同模式记录的是数据逻辑的操作：比如给ID=2这一行的c字段加1，redo log 是物理日志：记录在数据页上的修改操作。
3. redo log 是循环写入，空间固定会用完。bin log是追加写入，文件多个达到一定大小会切换到下一个。

二阶段提交

概念：为了保证bin log和redo log的一致性。

步骤：

• 准备和写入

  redo log先把数据写入磁盘、bin log 准备写入。

  redo log写入成功后，bin log 写入磁盘

• 提交

  bin log 写入磁盘后，在记录事务提交成功操作

如何保证一致性

当redo log写入成功后 出现错误bin log没有写入成功，没有提交。回滚，通过bin log 回放数据，结果一致。

等bin log 提交成功 提交前出现错误。redo log 和bin log都完整所以重启后直接提交操作。结果数据一致。

undo log

一般逻辑日志，分为两种

1. insert undo log

   事务在insert的时候产生的事务在事务提交后就会被丢弃。

2. update undo log

   事务在update或delete的时候产生的日志，不仅在回滚而且在快照读的时候也需要，不能随意删除。只有在不涉及该日志的时候才会被purge删除。

MVCC

简介：

多版本并发控制是mysql在行锁的基础上对进行优化，减少开销提高并发效率。在并发对数据库读或者写的时候，对正在事务内部处理的数据做多版本管理，避免写操作时行锁带来的阻塞。

须知：

首先我们需要了解的是mysql事务性操作的过程，mysql会帮我们维持一个全局性的事务ID，在数据插入、删除、修改的时候都会对数据的修改隐式维护两个字段，一个DB_TRX_ID（当前数据行的版本ID）和DB_ROLL_PT（数据行的删除ID）两个字段

update：过程简述，在数据行存在更新操作的时候数据的执行步骤是先对数据行的复制,然后间原行数据的删除版本号设置为当前全局事务ID，新的行数据的数据行版本号也设置为当前全局事务ID，删除版本号为NULL。

MVCC原理

数据查询的时候，第一种在查询的时候根据当前版本号（也会赋予查询语句一个全局事务ID）版本好小的全局事务ID数据行并且删除ID必须为NULL（数据未被删除）第二种是查询事务版本ID大于等前查询的版本号。

在MVCC中实现多版本的并发控制依赖的就是innodb引擎下undolog日志，对事物的前后的快照版本保持。事务可以通过undolog版本快照对数据回滚。

数据脏页

简介：

内存中的数据和磁盘数据页不一致时我们称内存页为数据脏页。

脏页数据产生原因：

数据操作过程：mysql在数据写操作的时候为了提高数据写入效率，通过存储引擎innodb就将数据内存直接更新，记录操作写入redolog（不进行磁盘持久化）完成数据插入，后期空闲时通过记录同步到磁盘。redolog是顺序写磁盘速度要比直接更新具体的数据操作要快很多。

数据不一致：因为redolog的空间有限，在高并发数据下redolog空间写满，数据没有同步磁盘导致数据磁盘和空间不一致。

触发数redolog同步磁盘操作：

1、redolog写满时，此时需要将checkpoint向前推进，推进的这部分日志对应的脏页刷入到磁盘，此时所有的更新全部阻塞，此时写的性能变为0，必须待刷一部分脏页后才能更新。
2、系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘。
3、MySQL认为空闲的时间，这种没有性能问题。
4、mysql正常关闭之前，会把所有脏页刷入磁盘，不存在性能问题。

思考：第二点系统内存不足。

mysql为了避免每次在读写数据时访问磁盘增加 IO 开销，Innodb 存储引擎通过把相应的数据页和索引页加载到内存的缓冲池（buffer pool V8.0被废除）中来提高读写速度。

然后按照最近最少使用原则来保留缓冲池中的缓存数据，那么当要读入的数据页不在内存中时，就需要到缓冲池中申请一个数据页，但缓冲池中数据页是一定的，当数据页达到上限时此时就需要把最久不使用的数据页从内存中淘汰掉。

但如果淘汰的是脏页呢，那么就需要把脏页刷到磁盘里才能进行复用。

导致结果等我们查询数据结果再换缓存中未命中而需要淘汰的缺是脏页数据集时会面临数据同步磁盘，导致查询结果较慢。

索引

概述

索引是为了数据查询的效率，类似于查找查找汉语字典一样的，索引目录存在。

优势

1、唯一索引可以保证每一行数据的唯一性

2、提高查询速度

3、加速表与表的连接

4、显著的减少查询中分组和排序的时间

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

1、创建跟维护都需要耗时

2、创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作

3、 索引需要磁盘的空间进行存储，磁盘占用也很快。

4、当对表中的数据进行CRUD的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作性能

常见数据结构类型：

哈希、有序数组、搜索树

哈希：K-V的数据结构，只适合等值查询，不适合范围查询。

有序数组：只适合静态搜索，涉及插入和删除的时候比较麻烦。

搜索树：按照数据结构中的二叉树存储数据，不过此时是B+树，广泛应用在存储引擎中。

索引种类

1. 主键索引：叶子节点保存的是数据信息。注意：当发生主键冲突事务回滚可能导致不连续。
2. 唯一索引：唯一列生成的索引，不允许重复值但是可以有NULL。
3. 普通索引（联合索引、覆盖索引、二级索引、非主键索引）：非主键索引也被称为二级索引，叶子结点的指向是主键索引（重新走主键索引查询）。覆盖索引是值索引列包含覆盖查询列。组合索引：ABCD列一起创建索引，最长允许16个，需要满足左前缀才能生效。

引发的概念：

索引下推

指的是在mysql5.6后在索引遍历的时候，对查询字短先在索引中筛选。对不符合索引的字短回表查询，从而减少回表次数。

存储引擎索引区别

MyISAM：不支持聚簇索引，就是子节点不存放整行数据。

InnoDB：支持聚簇索引。

B+树比起B树的优势：

B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比B树更加矮胖，IO次数更少。

B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。

B+树中更有利于对数据扫描，可以避免B树的回溯扫描。

创建索引的原则

1. 不是越多越好，而是应该结合业务查询选择合适的创建。
2. 更新频繁的不适合创建。
3. 唯一索引应该选择重复率小的数据创建，业务数据唯一的也应该选择的唯一索引。
4. 经常group by 或者 order by 的数据应该创建索引。
5. 条件查询的条件字短应该创建索引。

索引失效场景

1. 不满足最左原则
2. like查询不满足右侧模糊
3. 隐式类型转化
4. or语句
5. 索引列参与计算

事务

ACID的四个特性

1. 原子性（Atomicity）：把多个操作放到一个事务中，保证这些操作要么都成功，要么都不成功
2. 一致性（Consistency）：理解成一串对数据进行操作的程序执行下来，不会对数据产生不好的影响，比如凭空产生，或消失
3. 隔离性（Isolation，又称独立性）：隔离性的意思就是多个事务之间互相不干扰，即使是并发事务的情况下，他们只是两个并发执行没有交集，互不影响的东西；当然实现中，也不一定需要这么完整隔离性，即不一定需要这么的互不干扰，有时候还是允许有部分干扰的。所以MySQL可以支持4种事务隔离性
4. 持久性（Durability）：当某个操作操作完毕了，那么结果就是这样了，并且这个操作会持久化到日志记录中

存在的问题

1. 脏读(dirty read)：B事务更改数据还未提交，A事务已经看到并且用了。B事务如果回滚，则A事务做错了
2. 不可重复读(non-repeatable read)：不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了，只需要锁住满足条件的记录
3. 幻读(phantom read)：事务A先修改了某个表的所有纪录的状态字段为已处理，未提交；事务B也在此时新增了一条未处理的记录，并提交了；事务A随后查询记录，却发现有一条记录是未处理的造成幻读现象，幻读仅专指新插入的行。幻读会造成语义上的问题跟数据一致性问题。
4. 串行读（不可重复度）：事务1进行两次读操作，第一次读取了某一数据，此时事务2对这条数据进行了修改，事务1进行第二次读操作，此时读到的数据与第一次读到数据结果不一致，即为不可重复读。用间隙锁。

事务的隔离级别 ：为解决数据可能存在的问题

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。

读已提交(Oracle默认)：别人改update数据的事务已经提交，我在我的事务中才能读到。

可重复读(MySQL默认)：别人改insert数据的事务已经提交，我在我的事务中也不去读，以此保证重复读一致性。

串行：我的事务尚未提交，别人就别想改数据。

注意：随着隔离级别升高效率越低，安全性越高。

标准跟实现：

上面都是关于问题的标准，但是每一种数据库都有不同的实现，比如MySQL InnDB 默认为RR级别，但是不会出现幻读。
因为当事务A更新了所有记录的某个字段，此时事务A会获得对这个表的表锁，因为事务A还没有提交，所以事务A获得的锁没有释放，此时事务B在该表插入新记录，会因为无法获得该表的锁，则导致插入操作被阻塞。只有事务A提交了事务后，释放了锁，事务B才能进行接下去的操作。所以可以说   MySQL的RR级别的隔离是已经实现解决了脏读，不可重复读和幻读的。

mysql锁

无论是Java的并发编程还是数据库并发操作为了数据安全性都会涉及到锁，设计人员或者研发人员引入了乐观锁和悲观锁这是一种锁的设计模式。

悲观锁：

优点：适合写多读少的环境下，虽然性能不高但是安全性高。

缺点：加锁安全必然带来的性能开销。

乐观锁：

优点：适合写少读多的环境，虽然性能很高但是可能会引起数据安全，尽量避免。

缺点：在写多读少的并发场景下，即在写操作竞争激烈的情况下，会导致CAS多次重试，冲突频率过高，导致开销比悲观锁更高。

场景分析

读-读：没问题

读-写：有隔离性问题，可能遇到脏读、虚幻读、不可重复读。

写-写：可能更新丢失问题，第一类更新丢失、第二类更新丢失。A｜B事务，1⃣️A事务回滚覆盖B事务提交操作，2⃣️A事务覆盖B事务提交操作。

mysql种的锁

锁类别

全局锁

对数据库实例加锁，逻辑意义上最大的锁。

表锁

MySQL最常使用的MYISAM与INNODB都支持表级锁定。

页锁

是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。

行锁

mysql中锁粒度最小的一种锁，表示只针对当前操作的 行 进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大行级锁不一定比表级锁要好：锁的粒度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁还要扫描找到对应的行对其上锁，这样的代价其实是比较高的，所以表锁和行锁各有所长。

Innodb锁

共享锁和排他锁 (Shared and Exclusive Locks)

意向锁（Intention Locks）

记录锁（Record Locks）

间隙锁（Gap Locks）

临键锁 （Next-Key Locks）

插入意向锁（Insert Intention Locks）

主键自增锁 (AUTO-INC Locks)

空间索引断言锁（Predicate Locks for Spatial Indexes）

行锁的共享锁

为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了S 锁。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

行锁排它锁

为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到for update。相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

间隙锁

1、加锁的基本单位是 NextKeyLock，是前开后闭区间。
2、查找过程中访问到的对象才会加锁。
3、索引上的等值查询，给唯一索引加锁的时候，NextKeyLock退化为行锁。
4、索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NextKeyLock退化为间隙锁。
5、唯一索引上的范围查询会访问到不满足条件的第一个值为止。

MyISAM中的锁

1. 虽然MySQL支持表，页，行三级锁定，但MyISAM存储引擎  只支持表锁 。所以MyISAM的加锁相对比较开销低，但数据操作的并发性能相对就不高。但如果写操作都是尾插入，那还是可以支持一定程度的读写并发
2. 从MyISAM所支持的锁中也可以看出，MyISAM是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合，一般工程中也用的较少。

MySQL InnoDB下的当前读和快照读

当前读

1、像select lock in share mode(共享锁)、select for update 、update、insert、delete(排他锁)这些操作都是一种当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

2、当前读可以认为是悲观锁的具体功能实现

快照读

1、不加锁的select就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC，可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

2、快照读就是MVCC思想在MySQL的具体非阻塞读功能实现，MVCC的目的就是为了实现读-写冲突不加锁，提高并发读写性能，而这个读指的就是快照读。

3、快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。

缓冲池

概述：

1、存在的意义是加速查询 
2、缓冲池(buffer pool) 是一种常见的降低磁盘访问 的机制；
3、缓冲池通常以页(page 16K)为单位缓存数据；
4、缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；
5、InnoDB对普通LRU进行了优化：将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，该页被访问，才进入新生代，以解决预读失效的问题页被访问。且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题	

执行顺序：

预读失效：

预读机制会把数据页提前读到内存中，但是最终mysql没有从引擎中获取数据。称为预读失效。

缓存污染：

sql涉及到大量数据，可能会把缓存中的数据替换出去，导致大量热数据被替换出去。反而导致mysql性能下降，称为缓存污染。

解决办法：加入老生代停留时间窗口策略后，短时间内被大量加载的页，并不会立刻插入新生代头部，而是优先淘汰那些，短期内

仅访问了一次的页。

table瘦身

空洞

MySQL执行delete命令其实只是把记录的位置，或者数据页标记为了可复用，但磁盘文件的大小是不会变的。通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是空洞。插入时候引发分裂同样会产生空洞。

重建表思路

1、新建一个跟A表结构相同的表B

2、按照主键ID将A数据一行行读取同步到表B

3、用表B替换表A实现效果上的瘦身

重建表指令

1、alter table A engine=InnoDB，慎重用，牛逼的DBA都用下面的开源工具。

2、推荐Github：gh-ost

sql join 统计 随机查询

jion示例

exit和in的区别

1、in查询时首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。

2、子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据。

3、两表大小相当，in 和 exists 差别不大。内表大，用 exists 效率较高；内表小，用 in 效率较高。

4、查询用not in 那么内外表都进行全表扫描，没有用到索引；而not exists 的子查询依然能用到表上的索引。not exists比not in要快。

优化

SQL优化主要分4个方向：SQL语句跟索引、表结构、系统配置、硬件。

总优化思路就是最大化利用索引、尽可能避免全表扫描、减少无效数据的查询：

1、减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。

2、返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。

3、减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用 。

4、分表分区：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。

5、减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。

优化举例

1、合理建立覆盖索引：可以有效减少回表。

2、union，or，in都能命中索引，建议使用in

3、负向条件(!=、<>、not in、not exists、not like 等) 索引不会使用索引，建议用in。

4、在列上进行运算或使用函数会使索引失效，从而进行全表扫描

5、小心隐式类型转换，原字符串用整型会触发CAST函数导致索引失效。原int用字符串则会走索引。

6、不建议使用%前缀模糊查询。

7、多表关联查询时，小表在前，大表在后。在 MySQL 中，执行 from 后的表关联查询是从左往右执行的(Oracle 相反)，第一张表会涉及到全表扫描。

8、调整 Where 字句中的连接顺序，MySQL 采用从左往右，自上而下的顺序解析 where 子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

SQL调优大致思路：

1. 先用慢查询日志定位具体需要优化的sql
2. 使用 explain执行计划查看索引使用情况
3. 重点关注(一般情况下根据这4列就能找到索引问题)：

• key（查看有没有使用索引）
• key_len（查看索引使用是否充分）
• type（查看索引类型）
• Extra（查看附加信息：排序、临时表、where条件为false等）

sql执行语句分析

Explain字段解释

• all：全表扫描。
• index：通过索引对全表扫描，通过索引次序进行扫描，避免排序但是开销也很大。
• range：只检索指定范围内的行，使用一个索引来选择行。key行显示使用那个索引。
• ref：连接不能基于关键字选择单个行，可能查询到多个符合条件的行。
• eq_ref：类似ref，区别在于使用的是否为唯一索引。对于每个索引值，表中有一个记录匹配。多表连接中使用primary key 或者unique key 作为关联条件，索引不重复。
• const/system：用常数比较primary key，查询表中只有一行。
• null：mysql在优化过程中分解语句，执行时甚至不用访问表或者索引，例如从索引中选择最小值，则可以通过单独索引完成。

优化

表结构优化：

1、尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED 。

2、VARCHAR的长度只分配真正需要的空间 。

3、尽量使用TIMESTAMP而非DATETIME 。

4、单表不要有太多字段，建议在20以内。

5、避免使用NULL字段，很难查询优化且占用额外索引空间。字符串默认为''。

分库分表：

分库分表分为垂直和水平两个方式，一般是先垂直后水平。

1、垂直分库：将应用分为若干模块，比如订单模块、用户模块、商品模块、支付模块等等。其实就是微服务的理念。

2、垂直分表：一般将不常用字段跟数据较大的字段做拆分。

3、水平分表：根据场景选择什么字段作分表字段，比如淘宝日订单1000万，用userId作分表字段，数据查询支持到最近6个月的订单，超过6个月的做归档处理，那么6个月的数据量就是18亿，分1024张表，每个表存200W数据，hash(userId)%100找到对应表格。

4、ID生成器：分布式ID需要跨库全局唯一方便查询存储-检索数据，确保唯一性跟数字递增性。