首页  思维导图  详情

MySQL

2023-05-21 11:31:15   0  举报





AI智能生成

为你推荐

查看更多



mysql之mvcc与BufferPool缓存机制

MySQL

数据库 MySQL

MySQL

mysql技术梳理

MySQL

MYSQL

MySQL

作者其他创作

大纲/内容

MySQL

关系型数据库

概念

DDL/DML/DQL/DCL

SQL执行顺序

正则表达式使用

数字函数

字符串函数

日期函数

聚合函数

MySQL常用函数

常用SQL技巧

SQL分类

主键

非空

唯一

外键

约束

笛卡尔积

内连接查

左外连接

右外连接

全外连接【现在Mysql不支持这种语法】

外连接查询

子查询

自关联查询

多表设计 & 查询

视图是一种虚拟存在的数据表，作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可

好处

创建

查询

修改

删除

使用

视图

存储过程和函数是事先编译并存储在数据库中的一段SQL语句的集合

提高代码的复用性

减少数据在数据库和服务器之间的传输减轻服务器的压力，提高处理数据的效率

减少sql语句编译的次数（sql被执行需要被编译成机器码）

减少代码层面的业务逻辑

函数有返回值

存储过程没有返回值（ `参数的out其实也相当于是返回数据了`）

存储过程和函数的区别

存储过程： call 存储过程名（参数）存储函数： select 存储函数名（参数）

调用方式

存储过程 & 函数

概述

示例

触发器

基础

支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库

客户端连接

连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。

第一层：网络连接层

管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等SQL接口：接受SQL命令，并且返回查询结果。查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等。查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询

第二层：核心服务层

插件式存储引擎

第三层：存储引擎层

第四层：系统文件层

体系架构

过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是半双工

show processlist; //查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的 show full processlist;` 查看更详细的信息

线程状态

① 建立连接

查询语句使用SQL_NO_CACHE

查询的结果大于query_cache_limit设置

查询中有一些不确定的参数，比如now()

开启查询缓存，以下SQL也不能缓存

启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句

show variables like '%query_cache%';` //查看查询缓存是否启用，空间大小，限制等默认 1M

show status like 'Qcache%';` //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

查询缓存配置

查询缓存在5.7默认关闭、在8.0被废除了。

② 查询缓存

将客户端发送的SQL进行语法解析，生成\"解析树

1、词法分析2、语法分析3、语义分析4、构造执行树5、生成执行计划6、计划的执行

生成的语法树

SQL语句的分析分为词法分析与语法分析，mysql的词法分析由MySQLLex[MySQL自己实现的]完成，语法分析由Bison生成

词法分析器步骤

③ 解析器（Parser）

根据“解析树”生成最优的执行计划，MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）

④ 查询优化器（Optimizer）

⑤ 查询执行引擎负责执行 SQL 语句

运行机制

font color=\"#b71c1c\

show engines

show variables like '%storage_engine%' ；查看Mysql数据库默认的存储引擎

聚簇索引+非聚簇索引

支持表锁、行锁

从 MySQL5.0开始默认限制是256TB

InnoDB

非聚簇索引

表锁

MyISAM

常用的索引引擎

存储引擎

将 Undo日志表空间从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用户自行指定文件大小和数量。

增加了 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据

Buffer Pool 大小可以动态修改，无需重启数据库实例。

5.7

将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来了，以前需要 ibdata中数据字典与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了。

temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建索引，这样加快了处理的速度。

用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中。

将Doublewrite Buffer从共享表空间ibdata中也分离出来了。

8.0

图示

线性预读

随机预读

查询预读算法的有效性

预读机制

空闲page，未被使用

free page

被使用page，数据没有被修改过

clean page

脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致

dirty page

类型

表示空闲缓冲区，管理free page

free list

flush list

lru list

链表结构

midPoint

cold_block_time

减少热页在链表移动

改进型LRU算法

Page管理机制

缓冲页的哈希处理

预热（持久化）

Buffer Pool 分块（chunk）是mysql 5.7.5之后的特性，该特性是指一个buffer pool实例是由多个块组成，每个块的块内空间是连续的，块与块之间是离散的

在 mysql 5.7.5之前，为buffer pool申请内存空间是整个buffer pool 实例都是连续的

buffer pool分块是为了方便用户可以在mysql运行期间能够调整buffer pool的大小（`innodb_buffer_pool_size`）。假设，整个buffer pool都是连续的，如果用户增大buffer pool的大小，系统必须分配一个比原来 buffer pool 更大的连续空间，再将原来buffer pool的数据拷贝到新空间，这个CPU时间开销无疑是巨大的。但是使用了分块存储的方式，当想要增大 buffer pool 的大小时，系统只需多申请一个块或者多个块的空间，并将这些块链入这个buffer pool实例中即可

一个块的大小由参数`innodb_buffer_pool_chunk_size`控制，默认一个chunk为128M

Buffer Pool 分块

多实例多分块

配置参数

SHOW ENGINE INNODB STATUS

查看Buffer Pool的状态信息

前者属于存储引擎层，后者属于服务层，前者是缓存已经读取过的页，后者是缓存查询语句和查询结果的映射关系，后者想要命中缓存必须要做到下一次用相同的sql语句查询

Innodb的缓冲池 & 查询缓存

Buffer Pool

在进行DML操作时，系统不会直接将变更刷新到磁盘中，而是会先将变更的页写入到缓冲区，经过一系列策略同步到磁盘。此时分为两种情况：当更改的页存在于 Buffer Pool 的 lru 链表，则直接在缓冲池中修改这个页，这个页会变成脏页，链入到 flush list中，但并不马上刷盘；此时不涉及 change buffer 操作。当更改的页不存在于 Buffer Pool 的 lru 链表，就要先从磁盘读取要修改的数据页到Buffer Pool后再修改（数据不可能在磁盘中直接更改，肯定要读到内存，在内存中修改）。但为了避免修改操作引发的磁盘读IO，系统会将DML操作记录到 change buffer中，并不马上刷盘。等下次对这些修改的页进行查询时，由于lru链表不存在该页，会从磁盘读取（磁盘页是更改前的数据），为了避免读到脏数据，该磁盘页会和 change buffer中的更改合并后才链入到 lru链表。如果未来一段时间都不会查询到这个修改了的页，也会有 insert buffer thread 定时将change buffer 的数据合并到磁盘页中。

ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。参数innodb_change_buffer_max_size; （ show variables like '%innodb_change_buffer_max_size%' 、 select @@innodb_change_buffer_max_size）

如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在 ChangeBuffer操作

写缓冲区，仅适用于非唯一普通索引页

网上解释

Change Buffer

用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应Hash索引。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引

具体细节查看该文档

InnoDB支不支持哈希索引？

Adaptive Hash Index 自适应哈希索引

记录InnoDB Redo和Undo日志，LogBuffer空间满了，会自动写入磁盘。可以通过将`innodb_log_buffer_size参数调大，减少磁盘IO频率，默认16M

innodb_flush_log_at_trx_commit 参数控制日志刷新行为，默认为10 ：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer-->OS cache[操作系统缓存]，刷盘OS cache-->磁盘文件），最多丢失1秒数据1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

刷盘机制

Buffer Log日志缓冲

锁信息

内存结构（Buffer Pool缓冲池）

`Mysql 5.6版本`默认所有InnoDB的所有表数据会放在一个系统表空间（ibdata1）`5.7版本`之后，每个表的数据默认单独放到一个独立表空间内。但每张表的独立表空间只存放数据页、索引页和写缓冲BitMap页，其他信息如回滚页、插入缓冲索引页、二次写缓冲仍放在系统表空间。所以即使每个表的数据单独放到自己的独立表空间，系统表空间也会不断增大。

innboDB页结构中有一个页号值，是4字节

为什么一个表空间只能存储2（32）-1 个页？

一个表空间最大存放 2(32) -1 页，每个页16KB，最大存储64TB数据

双写缓冲区（Doublewrite Buffer）

innoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件（.frm文件）中存储的信息重叠。

数据字典（InnoDB Data Dictionary）

系统表空间（System Tablespace）

独立表空间（File-Per-Table Tablespaces）

撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。 InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0。参数值为0表示使用系统表空间ibdata1;大于0表示使用undo表空间undo_001、 undo_002等。

撤销表空间（Undo Tablespace）

临时表空间（Temporay Tablespace）

通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于 mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式

通用表空间（General Tablespace）

重做日志（Redo Log）

撤销日志（Undo Logs）

磁盘结构

BufferPool

Doublewrite Buffer 双写缓冲区

innoDB引擎的三大特性

InndDB存储结构

表空间，用于存储多个`ibd`数据文件，用于存储表的记录和索引。一个文件包含多个段

Mysql 5.6版本默认所有InnoDB的所有表数据会放在一个系统表空间 ibdata1。5.7版本之后，每个表的数据默认单独放到一个独立表空间内。但每张表的独立表空间只存放数据页、索引页和写缓冲BitMap页，其他信息如回滚页、插入缓冲索引页、二次写缓冲仍放在系统表空间。所以即使每个表的数据单独放到自己的独立表空间，系统表空间也会不断增大

Tablespace

Segment表现的是一个逻辑概念，并不对应表空间中的连续物理区域，可以看做是区、页的附加标注信息

用于管理多个Extent，分为数据段（Leaf node segment）【B+树的叶子节点页】、索引段（Non-leaf node segment）【B+树的非叶子节点页】、回滚段（Rollback segment）。一个表至少会有1个数据段和1个索引段。每多创建一个索引，会多两个segment（即数据段和索引段）

段申请空闲内存空间时会按一个区申请（1 extend = 64 pages，1extend大小为1M）。但是innodb的一个表初始大小为96K，而不是1M，因为每个段一开始不会直接申请一个区，而是先用若干个碎片页存放数据，用完这些也才按1个区64个连续页来申请。

Segment

当数据的增多区的数量会越来越多，为看方便管理区，所以引入的区组的概念，`每个区组管理256个区(即256MB)`。区组的首个区的前四页和其他的区不同。

第一个组的最开始的4个页的类型是固定的：用来登记整个表空间的一些整体属性以及本组所有的区被称为FSP_HDR，也就是extent 0 ~ extent 255这256个区，整个表空间只有一个FSP_HDR其余各组最开始的2个页面的类型是固定的，一个XDES类型，用来登记本组256个区的属性，FSP_HDR类型的页面其实和XDES类型的页面的作用类似，只不过FSP_HDR类型的页面还会额外存储一些表空间的属性。

区组

包含64个连续的页，大小为`1M`。当表空间不足，需要分配新的页资源，直接分配一个区。

一次性分配多个页，使用页的分布在尽可能的在逻辑和物理保持顺序（可以在范围查询时使用到顺序IO），避免随机IO

需要要以区为什么分配单位？

Extent

页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页。

数据页是指B+树的叶子节点页，索引页是指B+树非叶子节点页

结构

Page

行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointers）等信息

Row

文件存储类型

Antelope：先前未命名的，最原始的InnoDB文件格式，它支持两种行格式：COMPACT和 REDUNDANT，MySQL 5.6及其以前版本默认格式为Antelope

Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED 和 DYNAMIC，5.7版本默认格式

File文件格式（File-Format）

我们的记录按照主键从小到大的顺序形成了一个单链表，记录被删除，则从这个链表上摘除。

User Records

Page Directory主要是解决记录链表的查找问题

一个数据页中查找指定主键值的记录的过程分为两步：通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。通过记录的next_record属性遍历该槽所在的组中的各个记录。

Page Directory

页结构的第二部分，这个部分占用固定的56个字节，专门存储各种状态信息。InnoDB为了能得到一个数据页中存储的记录的状态信息，比如本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等

Page Header

File Header针对各种类型的页都通用，也就是说不同类型的页都会以File Header作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说页的类型，这个页的编号是多少，它的上一个页、下一个页是谁，页的校验和等等，这个部分占用固定的38个字节。同时通过上一个页、下一个页建立一个双向链表把许许多多的页就串联起来，而无需这些页在物理上真正连着。但是并不是所有类型的页都有上一个和下一个页的属性，数据页是有这两个属性的，所以所有的数据页其实是一个双向链表。

File Header

InnoDB存储引擎会把数据存储到磁盘上，但是磁盘速度太慢，需要以页为单位把【页 16KB，磁盘读写 4KB】数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候中断电了咋办？

前4个字节代表页的校验和：这个部分是和File Header中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trailer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。

后4个字节：代表页面被最后修改时对应的日志序列位置（LSN），这个也和校验页的完整性有关。

为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），InnoDB每个页的尾部都加了一个File Trailer部分，这个部分由8个字节组成

file Trailer与File Header类似，都是所有类型的页通用的

File Trailer

索引页（数据页）格式

参数描述补充：

DB_ROW_ID 不一定有

溢出页

节省空间

为什么要有NULL值列表？

Row行格式

InnoDB数据文件

IO Thread

事务提交之后，其使用的undo日志将不再需要，因此需要Purge Thread回收已经分配的undo 页

默认 4

`show variables like '%innodb_purge_threads%';`

Purge Thread

脏数据刷新到磁盘，脏数据刷盘后相应的redo log也就可以覆盖，即可以同步数据，又能达到redo log循环使用的目的。会调用write thread线程处理

默认 8

show variables like '%innodb_page_cleaners%

Page Cleaner Thread

Master thread是InnoDB的主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。包含：脏页的刷盘（page cleaner thread）、undo页回收（purge thread）、redo日志刷盘（log thread）、合并写缓冲等。内部有两个主处理，分别是每隔1秒和10秒处理

刷新日志缓冲区，刷到磁盘合并写缓冲区数据，根据IO读写压力来决定是否操作刷新脏页数据到磁盘，根据脏页比例达到75%才操作（`innodb_max_dirty_pages_pct`， `innodb_io_capacity`）

每1秒的操作

刷新脏页数据到磁盘合并写缓冲区数据刷新日志缓冲区删除无用的undo页

每10秒的操作

Master Thread

线程模型

介绍

只读事务：在只读事务中不可以对普通的表（其他事务也能访问到的表）进行增、删、改操作，但可以对用户临时表做增、删、改操作。对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务id，否则的话是不分配事务id的。读写事务：对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务id，否则的话也是不分配事务id的

分配时机

服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务id时，就会把该变量的值当作事务id分配给该事务，并且把该变量自增1。每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为5的页面中一个称之为Max Trx ID的属性处，这个属性占用8个字节的存储空间。当系统下一次重新启动时，会将上边提到的Max Trx ID属性加载到内存中，将该值加上256之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于Max Trx ID属性值）。

事务id生成机制

事务Id

FIL_PAGE_UNDO_LOG类型的页是专门用来存储undo日志的FIL_PAGE_UNDO_LOG页面可以从系统表空间中分配，也可以从一种专门存放undo日志的表空间，也就是所谓的undo tablespace中分配

INSERT操作对应的undo日志

页中的记录会根据记录头信息中的next_record属性组成一个单向链表，我们把这个链表称之为正常记录链表；被删除的记录其实也会根据记录头信息中的next_record属性组成一个链表，只不过这个链表中的记录占用的存储空间可以被重新利用，所以也称这个链表为垃圾链表。Page Header部分有一个称之为PAGE_FREE的属性，它指向由被删除记录组成的垃圾链表中的头节点

MVCC的功能

为啥会有这种奇怪的中间状态呢？

① 将记录的delete_mask标识位设置为1，这个阶段称之为delete mark，并没有被加入到垃圾链表。也就是此时记录处于一个中间状态。在删除语句所在的事务提交之前，被删除的记录一直都处于这种所谓的中间状态。

② 当该删除语句所在的事务提交之后，会有专门的线程【Purge Thread】后来真正的把记录删除掉。所谓真正的删除就是把该记录从正常记录链表中移除，并且加入到垃圾链表中，然后还要调整一些页面的其他信息，比如页面中的用户记录数量PAGE_N_RECS、上次插入记录的位置PAGE_LAST_INSERT、垃圾链表头节点的指针PAGE_FREE、页面中可重用的字节数量PAGE_GARBAGE、还有页目录的一些信息等等。这个阶段称之为purge

删除的过程需要经历两个阶段

从上边的描述中我们也可以看出来，在删除语句所在的事务提交之前，只会经历阶段一，也就是delete mark阶段（提交之后我们就不用回滚了，所以只需考虑对删除操作的阶段一做的影响进行回滚）。InnoDB中就会产生一种称之为TRX_UNDO_DEL_MARK_REC类型的undo日志。

DELETE操作对应的undo日志

更新后的列和更新前的列占用的存储空间都一样大，那么就可以进行就地更新

就地更新（in-place update）

有任何一个被更新的列更新前和更新后占用的存储空间大小不一致，那么就需要先把这条旧的记录从聚簇索引页面中删除掉（真正删除【把这条记录从正常记录链表中移除并加入到垃圾链表中，并且修改页面中相应的统计信息（比如PAGE_FREE、PAGE_GARBAGE等这些信息）】），然后再根据更新后列的值创建一条新的记录插入到页面中

先删除掉旧记录，再插入新记录

不更新主键的情况

将旧记录进行delete mark操作创建一条新记录

更新主键情况

UPDATE操作对应的undo日志

undo 日志格式

实现事务的原子性

实现多版本控制（MVCC）

作用

UndoLog

随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生 Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以`重用（被覆盖写入）`。

redo日志占用的空间非常小：存储表空间ID、页号、偏移量以及需要更新的值所需的存储空间是很小的redo日志是顺序写入磁盘的：在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序IO

优势

type：该条redo日志的类型，redo日志设计大约有53种不同的类型日志。space ID：表空间ID。page number：页号。data：该条redo日志的具体内容。

redo 日志格式

InnoDB为了更好的进行系统崩溃恢复，把redo日志都放在了大小为512字节的块（block）中在服务器启动时就向操作系统申请了一大片称之为redo log buffer的连续内存空间，翻译成中文就是redo日志缓冲区，我们也可以简称为log buffer。这片内存空间被划分成若干个连续的redo log block，我们可以通过启动参数innodb_log_buffer_size来指定log buffer的大小，该启动参数的默认值为16MB。向log buffer中写入redo日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空间用完之后再往下一个block中写

redo log block 和日志缓冲区

write pos：是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；checkpoint ：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录（强制刷盘[结合LSN]），把 checkpoint 推进一下。

写入机制（顺序循环，覆盖写入）

log buffer空间不足时，log buffer的大小是有限的（通过系统变量innodb_log_buffer_size指定），如果不停的往这个有限大小的log buffer里塞入日志，很快它就会被填满。InnoDB认为如果当前写入log buffer的redo日志量已经占满了log buffer总容量的大约一半左右，就需要把这些日志刷新到磁盘上

Log Buffer 空间不足

日志持久化策略

正常关闭服务器

日志刷盘时机

MySQL的数据目录（使用font color=\"#ed9745\

redo日志文件组中的每个文件大小都一样，格式也一样，都是由两部分组成：前2048个字节，也就是前4个block是用来存储一些管理信息的。

redo日志文件组

redo 日志写入过程

flushed_to_disk_lsn：将要刷新到磁盘中的redo日志量的全局变量 buf_next_to_write：已经刷新到磁盘上的redo日志文件当有新的redo日志写入到log buffer时，首先lsn的值会增长，但flushed_to_disk_lsn不变，随后随着不断有log buffer中的日志被刷新到磁盘上，flushed_to_disk_lsn的值也跟着增长。如果两者的值相同时，说明log buffer中的所有redo日志都已经刷新到磁盘中了

Log sequence number：代表系统中的lsn值，也就是当前系统已经写入的redo日志量，包括写入log buffer中的日志。Log flushed up to：代表flushed_to_disk_lsn的值，也就是当前系统已经写入磁盘的redo日志量。Pages flushed up to：代表flush链表中被最早修改的那个页面对应的oldest_modification属性值。Last checkpoint at：当前系统的checkpoint_lsn值。

查看系统中的各种LSN值

Log Sequence Number

工作原理

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认为`ib_logfile0`和`ib_logfile1`。可以通过下面一组参数控制Redo Log存储：

show variables like '%innodb_log%';

Redo Log相关配置参数

Redo Log

在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到 Binlog后实现数据恢复达到主从数据一致性

主从同步

通过mysqlbinlog工具来恢复数据

数据恢复

ROW

STATMENT

MIXED

行类型

MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合

Binlog文件结构

Binlog文件操作

1. 根据记录模式和操作触发event事件生成log event（事件触发执行机制）

Log Event保存在一个`binlog_cache_mngr`数据结构中，在该结构中有两个缓冲区一个是 `stmt_cache` ：用于存放不支持事务的信息；一个是`trx_cache` ：用于存放支持事务的信息。

2. 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个(独立的)缓冲区

不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在 binlog文件中是连续的，中间不会插入其他事务的log event。

3. 事务在提交阶段会将产生的log event写入到外部binlog文件中。

写入机制

Redo Log 和 Bin Log区别

Bin Log

事务执行

MySQL崩溃恢复后，首先会获取日志检查点信息，随后根据日志检查点信息使用Redo Log进行恢复。MySQL崩溃或者宕机时事务未提交，则接下来使用Undo Log回滚数据。如果在MySQL崩溃或者宕机时事务已经提交，则用Redo Log恢复数据即可。

MySQL可以根据redo日志中的各种LSN值，来确定恢复的起点和终点。然后将redo日志中的数据，以哈希表的形式，将一个页面下的放到哈希表的一个槽中。之后就可以遍历哈希表，因为对同一个页面进行修改的redo日志都放在了一个槽里(以页面hash为key)，所以可以一次性将一个页面修复好（避免了很多读取页面的随机IO）。并且通过各种机制，避免无谓的页面修复，比如已经刷新的页面，进而提升崩溃恢复的速度。

恢复机制

崩溃后的恢复为什么不用binlog？

事务恢复

数据库崩溃重启后，需要先从redo log中把未落盘的脏页数据恢复回来，重新写入磁盘，保证用户的数据不丢失。当然，在崩溃恢复中还需要把未提交的事务进行回滚操作。由于回滚操作需要undo log日志支持，undo log日志的完整性和可靠性需要redo log日志来保证，所以数据库崩溃需要先做redo log数据恢复，然后做undo log回滚。事务进行过程中，每次sql语句执行，都会记录undo log和redo log，然后更新数据形成脏页。事务执行COMMIT操作时，会将本事务相关的所有redo log进行落盘，只有所有的redo log落盘成功，才算COMMIT成功。然后内存中的undo log和脏页按照同样的规则进行落盘。如果此时发生崩溃，则只使用redo log恢复数据

Redo日志和Undo日志的关系？

1）当事务提交时InnoDB存储引擎进行prepare操作。2）MySQL上层会将数据库、数据表和数据表中的数据的更新操作写入BinLog文件。3）InnoDB存储引擎将事务日志写入Redo Log文件中。

两阶段事务2PC

同时写Redo和Binlog怎么保持一致？

事务执行的流程

所有的修改都先被写入到日志中，然后再被应用到系统中。通常包含redo和undo两部分信息undo日志保证事务的原子性(A)redo日志保证数据的一致性（I），当系统crash重启之后用来恢复数据

WAL【Write-Ahead Logging，预写式日志】，先写日志，再写磁盘。

只有在日志记录全部都安全落盘，数据库在日志中看到代表事务成功提交的“提交记录”（Commit Record）后，才会根据日志上的信息对真正的数据进行修改，修改完成后，再在日志中加入一条“结束记录”（End Record）表示事务已完成持久化。两者的区别是，WAL允许在事务提交之前，提前写入变动数据，而Commit Logging则不行；WAL中有undo日志，Commit Logging没有。阿里的OceanBase则是使用的Commit Logging来实现事务。

Commit Logging（提交日志）

Shadow Paging 的大体思路是对数据的变动会写到硬盘的数据中，但并不是直接就地修改原先的数据，而是先将数据复制一份副本，保留原数据，修改副本数据。在事务过程中，被修改的数据会同时存在两份，一份是修改前的数据，一份是修改后的数据，这也是“影子”（Shadow）这个名字的由来。当事务成功提交，所有数据的修改都成功持久化之后，最后一步是去修改数据的引用指针，将引用从原数据改为新复制出来修改后的副本，最后的“修改指针”这个操作将被认为是原子操作，现代磁盘的写操作可以认为在硬件上保证了不会出现“改了半个值”的现象。所以 Shadow Paging 也可以保证原子性和持久性。Shadow Paging 实现事务要比 Commit Logging 更加简单，但涉及隔离性与并发锁时，Shadow Paging 实现的事务并发能力就相对有限，因此在高性能的数据库中应用不多。常用的轻量级数据库 SQLite Version 3 采用的事务机制就是 Shadow Paging。

Shadow Paging（影子分页）

事务日志类型

一个写语句的执行流程

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？

架构原理

索引的本质就是排好序的数据结构

普通

引列的值必须唯一，但允许有空值(多个)。如果是组合索引，则列值组合必须唯一

一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引

组合

快速匹配全部文档的方式

InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持

全文（FULLTEXT）

功能

InnoDB/MyISAM存储引擎默认的索引类型

B+Tree

MySQL中Memory存储引擎默认支持的索引类型

InnoDB自适应哈希索引（内存结构中），InnoDB自适应哈希索引是为看提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基础B+Tree索引在创建一个Hash索引，使得内存中的B+Tree索引具备哈希索引的功能，即能快速定值访问频繁访问的索引页。（`在使用Hash索引访问时，一次性查询就能定位数据，等值查询效率优于B+Tree`）自适应Hash索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式为某些热点页建立哈希索引来加速访问，另外InnoDB自适应哈希索引的功能，用具只能选择开启或关闭，无法进行人工干涉。

show variables like '%innodb_adaptive%' // 查询自适应Hash索引的状态

Hash索引

空间索引是`MyISAM`引擎的一个特殊索引类型，主要用于地理空间数据类型

R-tree 索引（空间索引）

全文索引也是 MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引

Full-text （全文索引）

InnoDB/MyISAM/MEMORY三种存储引擎对各种索引类型的支持

数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

font color=\"#e74f4c\

默认是主键索引如果一张表没有`主键索引`，它会选择第一个唯一索引作为聚簇索引。如果也没有唯一索引，mysql会自动生成一个 6字节`rowid`。

索引结构

聚簇索引

数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

可有可无，可以存在多个

存储方式

分类

回表指的就是非聚集索引从叶子节点拿到数据(主键的键值)之后，还需要再根据主键键值去扫描主键索引的B+树，这种操作就叫做回表，也就是说他需要扫描两颗B+树，这也就是为什么在InnoDB中主键索引的效率相比较其他索引是最高的。

回表会进行IO操作，算是一个比较重的操作

回表

一个查询只需要查询当前索引的值和主键的值，而不需要查其他数据，不需要回表，直接就可以返回

覆盖索引

这是因为聚簇索引采用的是平衡二叉树算法，而且每个节点都保存了该主键所对应行的数据，假设插入数据的主键是自增长的，那么根据二叉树算法会很快的把该数据添加到某个节点下，而其他的节点不用动；但是如果插入的是不规则的数据，那么每次插入都会改变二叉树之前的数据状态。从而导致了页分裂

页分裂的目的就是保证：后一个数据页中的所有行主键值比前一个数据页中主键值大。

为什么会产生页分裂？

页分裂

组合索引没有加order by 也可能会决定你的查询出来的结果顺序

组合索引的结构

基础理论：二分查找 + Hash结构 + B+树

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。InnoDB存储引擎中有`页（Page）的概念，页是其磁盘管理的最小单位`。InnoDB存储引擎中默认每个页的大小为16KB。InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小`16KB`，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

磁盘存储

多路平衡搜索树

BTree

BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致`每个节点（即一个页）`能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在`B+Tree`中，有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度

非叶子节点只存储键值信息 ===> 非叶子节点存储的数据越多，树的高度越低所有叶子节点（`页`）之间都有一个连接指针。 ===> 双向列表数据记录都存放在叶子节点中。 ===> `B+树的查询花费的时间更加稳定

B+Tree相对于BTree区别

B+Tree`上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 `B+Tree` 进行两种查找运算【有范围】对于主键的范围查找和分页查找【有顺序】从根节点开始，进行随机查找

Innodb`中存储空间管理的最小单位是`页`，页的默认空间是16KB，每个页中存放了数据。`页与页`之间是通过双向链表来连接的。索引页中的数据都会按照主键的值从小到大排列并用`单向链`表连接起来

B+Tree

对索引的key进行一次hash计算就可以定位出数组存储的位置

很多时候Hash索引要比 B+ 树索引更高效

仅仅满足 \"=\" “in”，不支持范围查询

Hash冲突

Hash

一致性 & 节省存储空间

为什么非主键索引结构叶子结点存储的是主键值

索引的结构

索引的建立过程

核心思想：尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询，保证这种大数据量表的查询尽可能多的都能充分利用索引，这样就能保证你的查询速度和性能了

索引的区分度很高索引的排序和业务查询的排序相同索引的字段完全满足查询的需要（覆盖索引）

三星索引

创建索引原则

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息session ：当前连接global：自服务器上次启动至今

show status like 'Com_______';

查看整个数据库的指令执行的次数

show status like 'Innodb_rows_%';

查看 InnoDb表执行语句的行数

查询SQL的执行频率

慢查询日志

慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

show processlist

定位低效率执行SQL

id 字段是 select查询的序列号：id 相同表示加载表的顺序是从上到下id 不同id值越大，优先级越高，越先被执行id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行

select_type

table

一般来说，我们需要保证查询至少达到 range 级别，最好达到refsystem > const > eq_ref > ref > range > index > ALL

NULL

System

const

eq_ref

ref

全表索引

fulltext

与ref类似，但是包括NULL

ref_or_null

索引合并

index_merge

unique _subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 `IN 子查询`转换为 `EXISTS` 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery

unique_subquery

index_subquery 与 unique_subquery 类似，只不过访问⼦查询中的表时使⽤的是普通的索引：

index_subquery

range

扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些

扫描了整张表的索引

index

all

type

显示可能应用在这张表的索引

possible_keys

实际使用的索引

key

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。越短执行效率越高

字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4char(n)：n*字符集长度 varchar(n)：n * 字符集长度 + 2字节 2字节是表示字符串的长度

字符串类型

TINYINT：1个字节 SMALLINT：2个字节 MEDIUMINT：3个字节 INT、FLOAT：4个字节 BIGINT、DOUBLE：8个字节

数值类型

DATE：3个字节 TIMESTAMP：4个字节 DATETIME：8个字节

时间类型

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

字段属性

计算规则

key_len

扫描行的数量（预估值）

row

partitions

row * filterd / 100 可以估算出将要和explain中前一个表进行连接的行数

filtered

没有使用索引下推

使用索引下推

只能用于 range、ref、ef_ref、ref_or_null 访问方法

只能用于InndoDB和MyISAM存储引擎及其分区表

对于存储引擎来说索引下推只适用于二级索引

索引下推的目的是为了减少回表次数，也就是要减少IO操作，对于聚簇索引来说，数据和索引是在一起的，不存在回表说法。

引用了子查询的条件不能下推，

引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数

索引下推的使用条件

索引下推

extra

一般来说 select 语句只会使用一个二级索引来加快查询，即使在where条件中使用了多个索引的字段，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并/index merge

SELECT * FROM order_exp WHERE order_no = 'a' AND expire_time = 'b';

交集合并的处理过程：将 order_no 和 expire_time 两个二级索引筛选的结果进行汇总取交集，在进行回表

只读取一个二级索引的成本：按照某个搜索条件读取一个二级索引，根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条

读取多个二级索引之后取交集成本：按照不同的搜索条件分别读取不同的二级索引，将从多个二级索引得到的主键值取交集，然后进行回表操作

虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

为什么这么处理？

二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况

等值匹配

主键列可以是范围匹配

什么情况下才会出现？（必要条件）

Intersection合并（交集合并）

SELECT * FROM order_exp WHERE order_no = 'a' OR expire_time = 'b'

SELECT * FROM order_exp WHERE insert_time = 'a' AND order_status = 'b' AND expire_time = 'c' OR (order_no = 'a' AND expire_time = 'b');优化器可能采用这样的方式来执行这个查询：先按照搜索条件order_no = 'a' AND expire_time = 'b'从索引idx_order_no和idx_expire_time中使用Intersection索引合并的方式得到一个主键集合。再按照搜索条件 insert_time = 'a' AND order_status = 'b' AND expire_time = 'c'从联合索引u_idx_day_status中得到另一个主键集合。采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集

使用Intersection索引合并的搜索条件

Union合并（并集合并）

SELECT * FROM order_exp WHERE order_no< 'a' OR expire_time> 'z'

先根据order_no< 'a'条件从idx_order_no二级索引中获取记录，并按照记录的主键值进行排序再根据expire_time> 'z'条件从idx_expire_time二级索引中获取记录，并按照记录的主键值进行排序因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

Sort-Union合并

SELECT * FROM order_exp WHERE order_no= 'a' And expire_time= 'z';

这个查询之所以可能使用Intersection索引合并的方式执行，还不是因为idx_order_no和idx_expire_time是两个单独的B+树索引，要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了

联合索引替代Intersection索引合并

索引合并算法

explain分析执行计划

Mysql从 `5.0.37` 版本开始增加了对 show profiles 和 show profile语句的支持

查看是否支持 profile: select @@have_profiling;

查看SQL语句执行的耗时： show profiles

表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端。由于在 Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态

Sending data

可以查看到该SQL执行过程中每个线程的状态和消耗的时间： show profile for query [query_id]

在获取到最消耗时间的线程状态后，MySQL支持进一步选择 all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间

show profile 分析SQL【查询SQL语句的耗时分析】

MySQL 5.6提供了对SQL的跟踪 trace

-- 开启trace 的追踪日志信息SET optimizer_trace=\"enabled=on\

select * from tb_item where id < 4;

执行语句

select * from information_schema.optimizer_trace;

检查information_schema.optimizer_trace

trace分析优化器

索引优化步骤

查询从索引的`左前列开始，并且`跳过索引中的列

最左前缀法则

范围查询右边的列，不使用索引

禁止在索引列运算

字符串类型不加 '

尽可能使用覆盖索引，禁止select *

以 %开头的覆盖索引不会使索引失效，其中包含任意一个非索引列，索引就会失效

like KK%相当于=常量，%KK和%KK% 相当于范围

%开头like

索引是索引少量数据的。

在这个案例中，数据有12条，11条为北京市，只有一条为西安市。

如果MySQL评估使用索引比全表更慢，则不使用索引

or 后面使用索引会失效(前面可以)

in 走索引， not in 不走索引

is NULL ， is NOT NULL 有时索引失效。

MySQL可以在含有NULL的列上使用索引（单独或者组合索引）

如果MySQ表的某一列含有NULL值，那么包含该列的索引是否有效？

避免索引失效

插入按照顺序进行查询，避免发生页分裂

手动提交事务

优化大数据量 load

insert

尽量减少额外的排序，通过索引直接返回有序数据where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同，并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现`FileSort

排序是在排序缓冲区(sort buffer)中进行

MySQL4.1 之前使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作

两次扫描算法（双路排序）

一次性取出满足条件的所有字段，然后在排序缓存区 sort buffer 中排序后直接输出结果集。排序时内存开销较大（如果查询的数据超出了缓存sort buffer，会导致多次磁盘读取操作，并创建临时表，最后产生多次IO，反而增加了负担），但是排序效率比两次扫描算法要高

一次扫描算法：（单路排序）

MySQL 通过比较系统变量 max_length_for_sort_data[默认 1M]的大小和Query语句取出的字段总大小，来判定是否那种排序算法，如果 `max_length_for_sort_data` 更大，那么使用单路排序；否则使用双路排序可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序缓存区的大小，提高排序的效率。

单路排序

双路排序

使用 trace 查看filesort排序

排序算法

Filesort 优化

group by分组时会默认对结果进行排序，执行order by null禁止排序

group by

使用多表联查来替换子查询

连接(Join)查询之所以更有效率一些（ reg > index），是因为MySQL不需要在内存中创建临时表完成这个逻辑上需要两个步骤的查询工作

优化子查询

建议使用 union 替换 or

先查询当前数据行前之前的记录，然后进行`排序`，然后将对应起始索引之前的数据进行了丢弃，所以查询效率慢

为什么大数据量下的分页会慢

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容（使用覆盖索引，不需要回表查询）

font color=\"#ed9745\

优化思路一

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询

主键必需自增并且 id没有断层

优化思路二

优化分页查询

在SQL语句中加入一些人为的提示来达到优化操作的目的

提供希望MySQL去参考的索引列表（建议具体使用的索引（但是mysql不一定采用）），就可以让MySQL不再考虑其他可用的索引。

USE INDEX

忽略一个或者多个索引，则可以使用` ignore index` 作为 hint

IGNORE INDEX

如果mysql判断使用索引（`use index`）不如全表扫描，就会走全表扫描。可以使用 force index 来强制mysql使用指定的索引

FORCE INDEX

使用SQL提示

count(*) = count(1) > count(辅助索引字段) > count(id) [前四个差不太多] > count(非索引字段)

count(*) 被MySQL查询优化器改写成了count(0)

count(*) 会优先选择辅助索引，假如没有辅助索引的存在，就会走主键索引。

count效率

count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行

对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算

查询mysql自己维护的总行数（仅 MyIsam）

大数据量count sql从数据库层面是很难优化的。可以查询的数据放入到`Redis`中，或者也可以`使用一张表专门存储数据的个数`

如果想知道预估值 show table status like '表名';

常见优化方法

count问题

Simple Nested Loop Join 简单嵌套循环连接（NLJ）算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集（优化器一般会优先选择小表做驱动表）

索引嵌套循环是使用索引减少扫描的次数来提高效率的，所以要求非驱动表上必须有索引才行。在查询的时候，驱动表(User) 会根据关联字段的索引进行查询，当索引上找到符合的值，才会进行回表查询。如果非驱动表(User_info)的关联字段(user_id)是主键的话，查询效率会非常高(主键索引结构的叶子结点包含了完整的行数据(InnoDB))，如果不是主键，每次匹配到索引后都需要进行一次回表查询(根据二级索引(非主键索引)的主键ID进行回表查询)，性能肯定弱于主键的查询

Index Nested-Loop Join 索引嵌套循环连接（NLJ）算法

不使用BNL算法

使用BNL算法

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了，每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小是join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了非驱动表的访问频率

驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下font color=\"#e74f4c\

Block Nested-Loop Join 基于块的嵌套循环连接（BNL）算法

表连接常见的三种算法

让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引（大表一定要做索引）

关联字段加索引（减少内层表的循环匹配次数）

写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

小表驱动大表（其本质就是减少外层循环的数据数量）

驱动表尽可能检出的数据少点（字段越少，join buffer 所缓存的数据就越多）

增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）

优化join查询效率

只有 AB、BA这两种连接顺序。其实相当于2 × 1 = 2种连接顺序。对于三表连接，比如表A、表B、表C进行连接有ABC、ACB、BAC、BCA、CAB、CBA这么6种连接顺序。其实相当于3 × 2 × 1 = 6种连接顺序。对于四表连接的话，则会有4 × 3 × 2 × 1 = 24种连接顺序。对于n表连接的话，则有 n × (n-1) × (n-2) × ··· × 1种连接顺序，就是n的阶乘种连接顺序，也就是n!。

阿里规约要求尽可能不要超过三个表

多表连接的成本分析

Join原理[内连接] & 优化

对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。NULL列需要增加额外空间来记录其值是否为NULL。对于MyISAM表，每一个空列额外占用一位，四舍五入到最接近的字节。然MySQL可以在含有NULL的列上使用索引（单独或者组合索引），但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值

NULL查询

SQL优化

# 查看当前会话的使用索引情况show status like 'Handler_read%'; # 查看全局的使用索引情况show global status like 'Handler_read%';

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引

查询索引使用情况

索引使用

font color=\"#314aa4\

由于索引是以页为单位的，一页的大小为16k。在回表的时候，需要返回主键索引的id，就会产生一个问题，就是在读取数据的时候，可能会产生一个随机IO 。由于在读取磁盘的数据时，假设磁盘的扇区只有一个，每次可以读取磁盘的512个字节，那么读取一页数据就需要读取 16 × 1024 ÷ 512 = 32 次。而索引存储在磁盘上，每个回表携带一个id，假设数据都是第一次读取，在不考虑bufferpool的情况下，那么就需要从磁盘中读取一页数据，那么每一个回表的数据就要与磁盘交互32多次，而如果存在多个回表的的主键id不在一个目录页上面，那么就需要 32乘以回表的个数，这样就大大的降低了查询的效率。这就解释了为什么有时候发现一条sql语句在该字段加了索引，where后面也有这个字段，但是通过explain执行发现他不走索引，而是走的全表扫描，这主要就是因为再回表时产生的这个随机IO 的原因

二级索引的回表流程

Disk-Sweep Multi-Range Read (MRR，多范围读取)，功能在使用二级索引做范围扫描的过程中，先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作，这样就可以减少磁盘的交互次数，并且磁盘内一页的数据大小是按顺序排好的，那么在通过这个有序的页查数据时，就可以将这种随机IO 转换成顺序IO二级索引虽然是顺序的，但是他们所对应的key确不是（随机io）

如果数据已经加载在bufferpool中，那么MRR主要是为了减少这个回表的次数，如果数据在bufferpool中不存在，那么不仅仅减少了回表的次数，同时也减少了随机IO，减少磁盘的交互数

MMR

MMR(批量回表)

密集索引：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引。稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息机器主键。

myIsam存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引innodb存储引擎：有且只有一个密集索引。所以，密集索引就是innodb存储引擎里的聚簇索引，稀疏索引就是innodb存储引擎里的普通二级索引。

密集索引和稀疏索引

索引

共享锁（读锁 / s锁） [Share]

排它锁（写锁 / x锁） [eXclusive]

意向读锁、意向写锁，属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS或IX锁。

当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低

IS锁、IX锁

操作

开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

行锁

锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般

页锁

粒度

串行操作

悲观锁

CAS

乐观锁

使用方式

MYISAY在执行查询语句SELECT前，自动给所涉及的表加读锁，写操作时自动给锁涉及的表加写锁InnoDB在执行查询语句SELECT前，因为有MVCC机制不会加锁，但是写操作会加行锁

MyISAM 存储引擎只支持表锁

读读不阻塞，读写阻塞

show open tables:

查看锁的锁定情况：show status like 'Table_locks%';

查看锁竞争的情况

MyISAM/InnoDB表锁

行锁是加在索引上的(需要避免索引失效)

无索引（索引失效）行锁升级为表锁

LOCK IN SHARE MODE

共享锁/读锁/S锁

FOR UPDATE

对于UPDATE、DELETE 和 INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)

排它锁/写锁/X锁

行锁模式

用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁；对于键值在条件范围内但并不存在的记录，叫做` \"间隙（GAP）\"， InnoDB也会对这个 \"间隙\" 加锁，这种锁机制就是所谓的间隙锁

间隙锁

show status like 'innodb_row_lock%';

查询INFORMATION_SCHEMA系统库锁相关数据库

InnoDB 行锁争用情况

尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁

尽可能的缩小锁定范围，避免造成不必要的锁定

尽可能减少索引条件，及索引范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度（大事务：锁的资源太多了）

尽可使用低级别事务隔离（但是需要业务层面满足需求）

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率

优化

InnoDB行锁是通过对索引数据页上的记录加锁实现的

只有RR级别有间隙锁

RecordLock锁：记录锁，锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）

GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）

Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

实现算法

主键加锁

唯一键加锁

无索引（等价于表锁）

加锁场景

行锁的原理

InnoDB行锁

用户A--》A表（表锁）--》B表（表锁）用户B--》B表（表锁）--》A表（表锁）

表级死锁

原因一：如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁

原因二：两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁

行级死锁

事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A释放其共享锁

共享锁转换为排他锁

1、查看近期死锁日志信息；2、使用explain查看下SQL执行计划查看锁状态变量

show engine innodb status 查看最近一次死锁记录

死锁排查

死锁

锁

所有的修改都先被写入到日志中，然后再被应用到系统中。通常包含redo和undo两部分信息

WAL【Write-Ahead Logging】，先写日志，再写磁盘。

C是最主要的，C是目的，AID 是手段A： undo logI: 锁D: redo log

ACID

脏读

不可重复读

幻读

并发引发的问题

Serializable是给每一个操作（读写）都加上锁

四种隔离级别

排队

排它锁/互斥锁

读读并发、读写互斥

读写锁

MVCC

事务控制的演进

InnoDB事务提交采用的是两阶段提交模式

四大特性

MVCC（Multi Version Concurrency Control）被称为多版本控制，font color=\"#e74f4c\

RC RR

适用

MVCC已经实现了读读、读写、写读并发处理，如果想解决写写冲突，可以采用下面两种方案

读视图

Undo Log + Read View + 隐式字段

如果数据表没有主键，InnoDB会自动以DB_ROW_ID生成一个聚簇索引

DB_ROW_ID：6byte，隐含的自增ID（隐藏主键）

最近修改（修改、插入）事务ID：记录创建这条记录以及最后一次修改该记录的事务的ID，是一个指针

DB_TRX_ID：6byte，事务ID

回滚指针，指向这条记录的上一个版本（上一个版本存储于rollback segment里）

DB_ROLL_PTR：7byte，回滚指针

记录被更新或删除并不代表真的删除，而是删除flag变了，相当于记录一次逻辑删除。

DELETED_BIT：1byte

隐藏字段

查询操作不会记录

只需要记录主键的id，回滚通过id进行删除

insert

要把修改这条记录前的旧值都记录下来，在回滚的时候再把这条记录的值更新为旧值

update

删除一条记录时，至少要把这条记录中的全部内容都记录下来，这样在之后回滚的时候再重新将这些内容组成的记录插入到表中就好了。删除操作都只是设置一下老记录的 DELETE_BIT，并不是真正将其删除为了节省磁盘空间，InnoDB有专门的 purge（清除）线程来清理 DELETED_BIT 为 true 的记录。为了不影响MVCC的正常工作purge线程自己也维护了一个 read view（这个 read view相当于当前系统中最老活跃的事务的 read view）。如果某个记录的DELETED_BIT 为 true，并且 DB_TRX_ID（最后一个操作的事务ID）相对于 purge线程的 read view 可见，那么这条记录一定是可以被安全清除的

delete

MVCC有实质上帮助的是 update undo log， Undo log 存储于 rollback segment 中的旧纪录链

Undo Log

事务进行快照读操作的时候生成的读视图在当前事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID（当每个事务开启时，都会被分配一个ID，这个ID是默认递增的，所以事务越新，ID越大）

read View 主要是用来做可见性判断的

将被修改的数据的最新记录中的 DB_TRX_ID（当前事务ID）取出来，与系统当前其它活跃事务的ID去对比（由 Read View 维护），如果 DB_TRX_ID 跟 Read View 的属性做了某些比较之后不符合可见性，那就通过DB_ROLL_PRT回滚指针去取出 undo log 中的 DB_TRX_ID再比较，也就是说遍历 undo log 链表的 DB_TRX_ID 找到特定条件的事务ID的版本，那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本

trx_list：未提交事务 ID 列表，用来维护 Read View 生成时刻系统正处于活跃状态的事务ID。

up_limit_id：记录 trx_list 事务ID列表中最小的ID，也就是最初修改该记录的事务。

low_limit_id：Read View 生成时刻系统尚未分配的下一个事务ID，也就是等于目前出现过的最大事务ID + 1。

全局属性

对比流程

读视图的可见性算法

Read View 读视图

实现原理

在RR 级别下的某个事务对某条记录的第一次快照读会创建一个快照以及Read View，记录当前系统中活跃的其它事务，此后在调用快照读的时候，还是用的同一个 Read View，所以只要当前事务在其它事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见。即 RR 级别下，快照读生成 Read View 时，Read View 会记录所有当前其它所有活跃事务的快照，这些食物的修改对于当前事务都是不可见的，而早于 Read View 创建的事务所作的修改均可见。

在 RC 级别下，事务中，每次快照都都会生成一个新的 Read View 和最新快照，这就是我们在 RC级别下的事务中可以看到别的事务提交更新的原因。

RC，RR级别下快照读的区别

多版本并发控制（MVCC）

事务

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志

show variables like 'log_error%';

查看日志位置指令

错误日志

记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的

开启binlog日志

该日志格式在日志文件中记录的都是SQL语句（statement）(不包含select语句)，每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次

STATEMENT

MySQL默认的日志格式，即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点

日志格式

由于日志以二进制方式存储，不能直接读取，需要用mysqlbinlog工具来查看

mysqlbinlog mysqlbing.000001；

日志读取

对于比较繁忙的系统，由于每天生成日志量大，这些日志如果长时间不清楚，将会占用大量的磁盘空间

通过 Reset Master 指令删除全部 binlog 日志，删除之后，日志编号，将从 xxxx.000001重新开始

purge master logs to 'mysqlbin.******' 该命令将删除 ``` ******``` 编号之前的所有日志。

purge master logs before 'yyyy-mm-dd hh24:mi:ss' 该命令将删除日志为 \"yyyy-mm-dd hh24:mi:ss\" 之前产生的所有日志。

设置参数 font color=\"#ed9745\

删除方式

日志删除

二进制日志 binlog

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。

配置

查询日志文件

查询日志

慢查询日志记录了所有执行时间超过参数 `long_query_time` 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0，精度可以到微秒

慢日志默认是关闭的

如果查询没有走索引也可以记录： show VARIABLES like '%log_queries_not_using_indexes%'

指定路径：font color=\"#ed9745\

文件的位置和格式

直接 cat 对应的日志文件

mysqldumpslow工具

文件的读取

日志

MySQL数据库的最大连接数量，默认值是 151

Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

max_connection

MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到 max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5），但最大不超过900（DD 3000）如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值

back_log

用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定

table_open_cache

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，可控制 MySQL 缓存客户服务线程的数量

thread_cache_size

设置InnoDB 事务等待行锁的时间，默认值是`50ms` ，可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起；对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作

innodb_lock_wait_timeout

并发参数的调整

参考阿里SQL规范

如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。建议使用TINYINT代替ENUM、BITENUM、SET。避免使用整数的显示宽度，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

id` TINYINT(2) UNSIGNED ZEROFILL

这里的长度并非是TINYINT类型存储的最大长度，而是显示的最大长度。可以使用 ZEROFILL 填充0

INT显示宽度

建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。TIMESTAMP是UTC时间戳，与时区相关。DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。

日期时间

尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。BLOB和TEXT都不能有默认值。字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。

字符串

数据类型选择

SQL规范

高性能表结构及索引设计最佳实践

内核查询优化规则详解

以在进行优化时，首先需要关注和优化的应该是架构，如果架构不合理，即使是DBA能做的事情其实是也是比较有限的。对于架构调优，在系统设计时首先需要充分考虑业务的实际情况，是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做；然后考虑写的并发量有多大，是否需要采用分布式；最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失。作为金字塔的底部的架构调优，采用更适合业务场景的架构能最大程度地提升系统的扩展性和可用性。在设计中进行垂直拆分能尽量解耦应用的依赖，对读压力比较大的业务进行读写分离能保证读性能线性扩展，而对于读写并发压力比较大的业务在MySQL上也有采用读写分离的大量案例。在底层硬件系统、SQL语句和参数都基本定型的情况下，单个MySQL数据库能提供的性能、扩展性等就基本定型了。但是通过架构设计和优化，却能承载几倍、几十倍甚至百倍于单个MySQL数据库能力的业务请求能力。对于MySQL调优，需要确认业务表结构设计是否合理，SQL语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引等等。最后确定系统、硬件有哪些地方需要优化，系统瓶颈在哪里，哪些系统参数需要调整优化，进程资源限制是否提到足够高；在硬件方面是否需要更换为具有更高I/O性能的存储硬件，是否需要升级内存、CPU、网络等。

Mysql 性能优化金字塔

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，一般通过下面两个步骤来分析总是很有效:确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。确认MySQL服务器层是否在分析大量超过需要的数据行。

查询不需要的记录

总是取出全部列（select *）

重复查询相同的数据

业务层-请求了不需要的数据

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下:

响应时间是两个部分之和:服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间—-可能是等I/O操作完成，也可能是等待行锁，等等

响应时间

典型： limit 10000.10

扫描的行数和返回的行数

扫描的行数和访问类型

执行层-是否在扫描额外的记录

优化SQL查询方法论

一个复杂查询还是多个简单查询

切分查询

分解关联查询

重构SQL查询的方法论

为什么查询速度慢？

MySQL客户端/服务器通信

生命周期中的查询优化处理

查询执行引擎

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。

这样处理有两个好处﹔服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让 MySQL客户端第一时间获得返回的结果。结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输。

返回结果给客户端

从MYSQL执行全流程考虑性能优化

查询性能优化

Mysql内核查询优化规则

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

I/O成本（1.0）

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本

CPU成本（0.2）

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。（注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2）

为什么是成本？

trace分析器查cost

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：根据搜索条件，找出所有可能使用的索引计算全表扫描的代价计算使用不同索引执行查询的代价对比各种执行方案的代价，找出成本最低的那一个

IO成本 = 所查询到数量集所占用的 page 数 * 1.0 + 1 【1是mysql的固定调节参数】

b style=\

每次回表操作都相当于访问一个页面，相当于1个IO成本

in 查询有几个值说明有几个单点区间，每一个单点区间 = 1个IO成本

简单的算法

单表查询成本

语法：EXPLAIN FORMAT=JSON [sql 语句]

EXPLAIN输出成本

细节参考 Join 原理

提前结束某种顺序的成本评估

为了防止无穷无尽的分析各种连接顺序的成本，MySQL提出了系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间

系统变量optimizer_search_depth

即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以MySQL干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level来控制到底是不是用这些启发式规则。

根据某些规则压根儿就不考虑某些连接顺序

连接查询的成本

成本常数保存在mysql系统表中

mysql 8.0

SELECT * FROM mysql.server_cost

mysql.server_cost

mysql 8.0

SELECT * FROM mysql.engine_cost;

mysql.engine_cost

操作磁盘的成本特别高，所以我们在写SQL的时候需要尽可能的减少与磁盘操作排序、distinct、union（数量小会放在内存，数量大会在磁盘）

调节成本常数

图灵-MYSL笔记

Mysql内核查询成本计算

永久性的统计数据，这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。非永久性的统计数据，这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

font color=\"#000000\

系统变量： innodb_stats_persistent

统计数据存储方式

当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里：表统计 & 索引统计SHOW TABLES FROM mysql LIKE 'innodb%';

表统计（innodb_table_stats）

索引统计（innodb_index_stats）

基于磁盘的永久性统计数据

开启innodb_stats_auto_recalc

如果发生变动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新innodb_table_stats和innodb_index_stats表

定期更新统计数据

ANALYZE TABLE order_exp

手动调用ANALYZE TABLE语句来更新统计信息

更新统计数据

InnoDB中的统计数据

Mysql优化

可用性扩展性一致性

架构设计理念

保证高可用的方法是冗余。但是数据冗余带来的问题是数据一致性问题站点高可用，冗余站点服务高可用，冗余服务数据高可用，冗余数据

写操作高可用需要自行处理

主从模式

互为主从，有双主双写、双主单写两种方式，建议使用双主单写

双主模式

可用性设计

从库过多会引发主库性能损耗。

从库

可以分为垂直拆分和水平拆分，垂直拆分可以缓解部分压力，水平拆分理论上可以无限扩展

分库分表

如何扩展以提高读性能

如何扩展以提高写性能

扩展性的设计

一致性主要考虑集群中各数据库数据同步以及同步延迟问题

不使用从库：扩展读性能问题需要单独考虑，否则容易出现系统瓶颈。增加访问路由层：可以先得到主从同步最长时间t，在数据发生修改后的t时间内，先访问主库。

一致性的设计

集群架构的设计

主库宕机后，数据可能丢失（可能数据还没有同步到从库）

从库只有一个SQL Thread，主库写压力大，复制很可能延时

存在问题

异步复制

MySQL让Master在某一个时间点等待Slave节点的 ACK（Acknowledge character）消息，接收到ACK消息后才进行事务提交

Mysql主从复制事务写入流程

主库等待从库写入 relay log 并返回 ACK 后才进行 Engine Commit

5.5 after-commit 半同步复制

5.7 after-sync 增强半同步

半同步复制（解决数据丢失问题）

在从库中有两个线程IO Thread和SQL Thread，都是单线程模式工作，因此有了延迟问题，我们可以采用多线程机制来加强，减少从库复制延迟。（IO Thread多线程意义不大，主要指的是SQL Thread多线程）

5.6 基于库

MySQL 5.7是通过对事务进行分组，当事务提交时，它们将在单个操作中写入到二进制日志中。如果多个事务能同时提交成功，那么它们意味着没有冲突，因此可以在Slave上并行执行，所以通过在主库上的二进制日志中添加组提交信息

在MySQL 5.7版本中，其设计方式是将组提交的信息存放在GTID中。为了避免用户没有开启GTID功能（gtid_mode=OFF），MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型 ANONYMOUS_GTID_LOG_EVENT通过mysqlbinlog工具分析binlog日志，就可以发现组提交的内部信息。

MySQL 5.7二进制日志较之原来的二进制日志内容多了last_committed和 sequence_number，last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放

如何知道事务是否在同一组中，生成的Binlog内容如何告诉Slave哪些事务是可以并行复制的？

5.7 基于组提交

8.0 基于write-set

并行复制（解决从库复制延迟问题）

并行复制配置与调优

主从架构

写后立刻读

二次查询

根据业务特殊处理

主从同步延迟问题

程序员在代码中封装数据库的操作，代码中可以根据操作类型进行路由分配，增删改时操作主库，查询时操作从库

基于编程和配置实现（应用端）

MySQL Proxy：是官方提供的MySQL中间件产品可以实现负载平衡、读写分离等。 MyCat： MyCat是一款基于阿里开源产品Cobar而研发的，基于 Java 语言编写的开源数据库中间件。ShardingSphere： ShardingSphere是一套开源的分布式数据库中间件解决方案，它由ShardingJDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。已经在2020 年4月16日从Apache孵化器毕业，成为Apache顶级项目。Atlas：Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个数据库中间件。（MySQL Proxy的封装）Amoeba：变形虫，该开源框架于2008年开始发布一款 Amoeba for MySQL软件。

基于服务器端代理实现（服务器端）

读写分离方案

读写分离

从主从模式演变为双主模式，建议用双主单写，再引入高可用组件Keepalived和MMM等工具，实现主库故障切换

推荐使用双主单写

双主双写需要处理的问题

支持双主故障切换，但是业务上同一时间只允许一个节点进行写入操作

当 writer节点出现故障，程序会自动移除该节点上的VIP 写操作切换到 Master2，并将Master2设置为writer 将所有Slave节点会指向Master2

除了管理双主节点，MMM 也会管理 Slave 节点，在出现宕机、复制延迟或复制错误，MMM 会移除该节点的 VIP，直到节点恢复正常

MMM故障处理机制

monitor：监控集群内数据库的状态，在出现异常时发布切换命令，一般和数据库分开部署。agent：运行在每个 MySQL 服务器上的代理进程，monitor 命令的执行者，完成监控的探针工作和具体服务设置，例如设置 VIP（虚拟IP）、指向新同步节点。

MMM监控机制

MMM（Master-Master Replication Manager for MySQL）架构

在MySQL故障切换过程中，MHA能做到在30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。MHA还支持在线快速将Master切换到其他主机，通常只需0.5－2秒

MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）

MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台 slave节点上。负责检测master是否宕机、控制故障转移、检查MySQL复制状况等。MHA Node运行在每台MySQL服务器上，不管是Master角色，还是Slave角色，都称为Node，是被监控管理的对象节点，负责保存和复制master的二进制日志、识别差异的中继日志事件并将其差异的事件应用于其他的slave、清除中继日志。

把宕机master的binlog保存下来根据binlog位置点找到最新的slave 用最新slave的relay log修复其它slave 将保存下来的binlog在最新的slave上恢复将最新的slave提升为master 将其它slave重新指向新提升的master，并开启主从复制

MHA故障处理机制

自动故障转移快主库崩溃不存在数据一致性问题（可能存在数据缺失）性能优秀，支持半同步复制和异步复制一个Manager监控节点可以监控多个集群

MHA 优点

MHA（Master High Availability）架构

主备切换过程一般由专门的HA高可用组件完成，但是切换过程中会存在短时间不可用，因为在切换过程中某一时刻主库A和从库B都处于只读状态

- 判断从库B的Seconds_Behind_Master值，当小于某个值才继续下一步- 把主库A改为只读状态（readonly=true）- 等待从库B的Seconds_Behind_Master值降为 0- 把从库B改为可读写状态（readonly=false）- 把业务请求切换至从库B

可靠性优先（主从切换完毕）

不等主从同步完成，直接把业务请求切换至从库B ，并且让从库B可读写，这样几乎不存在不可用时间，但可能会数据不一致。

可用性优先（不等主从同步完毕）

主备切换

集群架构

垂直拆分

水平拆分

拆分方式

在分布式存储系统中，数据需要分散存储在多台设备上，分片就是把数据库横向扩展到多个数据库服务器上的一种有效的方式，其主要目的就是为**突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题

范围分片

优点：实现简单，数据分配比较均匀，不容易出现冷热不均，负荷不均的情况。缺点：扩容时会产生大量的数据迁移，比如从n台设备扩容到n+1，绝大部分数据需要重新分配和迁移。

哈希取模

一致性Hash是将数据按照特征值映射到一个首尾相接的Hash环上，同时也将节点（按照IP地址或者机器名Hash）映射到这个环上。对于数据，从数据在环上的位置开始，顺时针找到的第一个节点即为数据的存储节点。Hash环示意图与数据的分布如下

一致性Hash在增加或者删除节点的时候，受到影响的数据是比较有限的，只会影响到Hash环相邻的节点，不会发生大规模的数据迁移。

一致性哈希

分片策略

分片

- 数据迁移问题 - 分片规则改变 - 数据同步、时间点、数据一致性

横向扩容需要考虑的问题

优点：简单缺点：停止服务，缺乏高可用程序员压力山大，需要在指定时间完成如果有问题没有及时测试出来启动了服务，运行后发现问题，数据会丢失一部分，难以回滚。

优缺点

小型网站大部分游戏对高可用要求不高的服务

应用场景

停机扩容

平滑扩容方案能够实现n库扩2n库的平滑扩容，增加数据库服务能力，降低单库一半的数据量。其核心原理是：成倍扩容，避免数据迁移

① 数据库扩容的过程中，如果想要持续对外提供服务，保证服务的可用性，平滑扩容方案是最好的选择。平滑扩容就是将数据库数量扩容成原来的2倍，比如：由2个数据库扩容到4个数据库，具体步骤如下：新增2个数据库配置双主进行数据同步（先测试、后上线）

② 数据同步完成之后，配置双主双写（同步因为有延迟，如果时时刻刻都有写和更新操作，会存在不准确问题）

③ 数据同步完成后，删除双主同步，修改数据库配置，并重启；

④ 此时已经扩容完成，但此时的数据并没有减少，新增的数据库跟旧的数据库一样多的数据，此时还需要写一个程序，清空数据库中多余的数据，如：User1去除 uid % 4 = 2的数据； User3去除 uid % 4 = 0的数据； User2去除 uid % 4 = 3的数据； User4去除 uid % 4 = 1的数据；

扩容步骤

优点：扩容期间，服务正常进行，保证高可用相对停机扩容，时间长，项目组压力没那么大，出错率低扩容期间遇到问题，随时解决，不怕影响线上服务可以将每个数据库数据量减少一半缺点：程序复杂、配置双主同步、双主双写、检测数据同步等后期数据库扩容，比如成千上万，代价比较高

大型网站对高可用要求高的服务

平滑扩容

扩容方案

扩容

用户请求量太大：单服务器TPS、内存、IO都是有上限的，需要将请求打散分布到多个服务器。单库数据量太大：单个数据库处理能力有限；单库所在服务器的磁盘空间有限；单库上的操作IO有瓶颈。单表数据量太大：查询、插入、更新操作都会变慢，在加字段、加索引、机器迁移都会产生高负载，影响服务。

遇到的问题

微服务架构时，业务切割得足够独立，数据也会按照业务切分，保证业务数据隔离，大大提升了数据库的吞吐能力

垂直分库

表中字段太多且包含大字段的时候，在查询时对数据库的IO、内存会受到影响，同时更新数据时，产生的binlog文件会很大，MySOL在主从同步时也会有延迟的风险。

垂直分表

针对数据量巨大的单张表(比如订单表)，按照规则把一张表的数据切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈

RANGE

用户ID取模

HASH

水平分表规则

水平分表

不跨库、不跨表，保证同一类的数据都在同一个服务器上面。数据在切分之前，需要考虑如何高效的进行数据获取，如果每次查询都要跨越多个节点，就需要谨慎使

水平分库规则

水平分库

用户维度：用户只能看到发送给自己的消息，其他用户是不可见的，这种情况下是按照用户ID hash分库，在用户查看历史记录翻页查询时，所有的查询请求都在同一个库内

站内信

用户表

时间维度：可以根据每天新增的流水来判断，选择按照年份分库，还是按照月份分库，甚至也可以按照日期分库

流水表

拆分案例

分库分表实例

ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。

三者区别

Sharding-JDBC：被定位为轻量级Java框架，在Java的JDBC层提供的额外服务，以`jar包`形式使用。Sharding-Proxy：被定位为透明化的`数据库代理端`，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。Sharding-Sidecar：被定位为Kubernetes或Mesos的`云原生数据库代理`，以DaemonSet的形式代理所有对数据库的访问。

简介

真实表：数据库中真实存在的物理表。例如b_order0、b_order1

逻辑表：在分片之后，同一类表结构的名称（总称）。例如b_order

数据节点：在分片之后，由数据源和数据表组成。例如ds0.b_order1

指的是分片规则一致的关系表（主表、子表），例如b_order和b_order_item，均按照 order_id分片，则此两个表互为绑定表关系。绑定表之间的`多表关联查询不会出现笛卡尔积关联`，可以提升关联查询效率

绑定表

在使用中，有些表没必要做分片，例如字典表、省份信息等，因为他们数据量不大，而且这种表可能需要与海量数据的表进行关联查询。广播表会在不同的数据节点上进行存储，存储的表结构和数据完全相同

广播表

术语

精确分片算法PreciseShardingAlgorithm：用于处理使用单一键作为分片键的=与IN进行分片的场景。

范围分片算法RangeShardingAlgorithm：用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景

复合分片算法ComplexKeysShardingAlgorithm：用于处理使用多键作为分片键进行分片的场景，多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。

Hint分片算法HintShardingAlgorithm：用于处理使用Hint行分片的场景。对于分片字段非SQL决定，而由其他外置条件决定的场景，可使用SQL Hint灵活的注入分片字段。例：内部系统，按照员工登录主键分库，而数据库中并无此字段。SQL Hint支持通过Java API和SQL注释两种方式使用。

分片算法

分片策略包含分片键和分片算法，真正可用于分片操作的是分片键 + 分片算法，也就是分片策略

标准分片策略StandardShardingStrategy

复合分片策略ComplexShardingStrategy

只支持单分片键。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发。如: t_user_$-> {u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7

行表达式分片策略InlineShardingStrategy

通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略

Hint分片策略HintShardingStrategy

不分片策略NoneShardingStrategy

数据源分片策略：用于配置数据被分配的目标数据源表分片策略：用于配置数据被分配的目标表，由于表存在与数据源内，所以表分片策略是依赖数据源分片策略结果的。

分片策略配置

核心概念

流程剖析

SQL使用规范

Inline 行表达式

UUID ：采用UUID.randomUUID()的方式产生分布式主键。 SNOWFLAKE ：在分片规则配置模块可配置每个表的主键生成策略，默认使用雪花算法，生成64bit的长整型数据。

内置主键生成器

自定义主键生成器

分布式主键

数据分配剖析

分库、分表读写分离分片策略分布式主键

数据分片

标准化的事务接口 XA强一致性事务柔性事务（Sega、Seata）

分布式事务

配置动态化编排和治理数据脱敏可视化链路追踪

数据库治理

主要功能

做的事情就是根据我们的配置生成规则，在将规则和数据源进行耦合

内部结构

根据配置的信息生成Configuration对象通过Factory会将Configuration对象转化为Rule对象通过Factory会将Rule对象与DataSource对象封装 Sharding-JDBC使用DataSource进行分库分表和读写分离操作

启动流程

使用过程

分表 + 读写分离

分库分表 + 读写分离

应用方案

读写分离剖析

强制路由剖析

ShardingSphere提供的Encrypt-JDBC和业务代码部署在一起。业务方需面向Encrypt-JDBC进行JDBC编程Encrypt-JDBC将用户发起的SQL进行拦截，并通过SQL语法解析器进行解析、理解SQL行为，再依据用户传入的脱敏规则，找出需要脱敏的字段和所使用的加解密器对目标字段进行加解密处理后，再与底层数据库进行交互。

数据脱敏整体流程

数据源配置：指DataSource的配置信息加密器配置：指使用什么加密策略进行加解密。目前ShardingSphere内置了两种加解密策略： AES/MD5脱敏表配置：指定哪个列用于存储密文数据（cipherColumn）、哪个列用于存储明文数据（plainColumn）以及用户想使用哪个列进行SQL编写（logicColumn）查询属性的配置：当底层数据库表里同时存储了明文数据、密文数据后，该属性开关用于决定是直接查询数据库表里的明文数据进行返回，还是查询密文数据通过Encrypt-JDBC解密后返回。

脱敏规则

脱敏处理流程

加密策略解析

数据脱敏

案例配置

Sharding-JDBC

可以理解为全局的 Sharding JDBC，Sharding JDBC只针对于某一个应用服务

Sharding-Proxy

CAP

BASE

2PC

3PC