MySQL

数字函数

字符串函数

日期函数

聚合函数

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

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

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

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

插件式存储引擎

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

线程状态

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

查询缓存配置

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

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

词法分析器步骤

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

支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在`.frm`文件中，如果是共享表空间，数据和索引保存在 `innodb_data_home_dir` 和 `innodb_data_file_path`定义的表空间中，可以是多个文件。如果是独立表空间存储，每个表的数据和索引单独保存在` .ibd `中。

聚簇索引+非聚簇索引

支持表锁、行锁

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

访问快,不支持事务和外键。表结构保存在.`frm`文件中，表数据保存在`.MYD`文件中，索引保存在`.MYI`文件中。

非聚簇索引

表锁

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

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

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

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

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

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

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

预读机制

Page管理机制

缓冲页的哈希处理

预热（持久化）

多实例多分块

配置参数

查看Buffer Pool的状态信息

Innodb的缓冲池 & 查询缓存

在进行DML操作时，系统不会直接将变更刷新到磁盘中，而是会先将变更的页写入到缓冲区，经过一系列策略同步到磁盘。

此时分为两种情况：

1. 当更改的页存在于 Buffer Pool 的 lru 链表，则直接在缓冲池中修改这个页，这个页会变成脏页，链入到 flush list中，但并不马上刷盘；此时不涉及 change buffer 操作。
2. 当更改的页不存在于 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）

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

网上解释

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

具体细节查看该文档

InnoDB支不支持哈希索引？

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

刷盘机制

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

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

双写缓冲区（Doublewrite Buffer）

数据字典（InnoDB Data Dictionary）

撤销表空间由一个或多个包含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等。

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

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

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

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个连续页来申请。

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

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

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

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

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

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

结构

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

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

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

一个数据页中查找指定主键值的记录的过程分为两步：

• 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
• 通过记录的next_record属性遍历该槽所在的组中的各个记录。

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

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

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

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

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

 参数描述补充：

 节省空间

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

默认 4

`show variables like '%innodb_purge_threads%';`

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

默认 8

show variables like '%innodb_page_cleaners%

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

每1秒的操作

每10秒的操作

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

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

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

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

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

不更新主键的情况

更新主键情况

• 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中写

• write pos：是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开 头；
• checkpoint ：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
  
  

write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录（强制刷盘[结合LSN]），把 checkpoint 推进一下。

Log Buffer 空间不足

日志持久化策略

正常关闭服务器

MySQL的数据目录（使用SHOW VARIABLES LIKE 'datadir'查看）下默认有两个名为ib_logfile0和ib_logfile1的文件，log buffer中的日志默认情况下就是刷新到这两个磁盘文件中, 文件以 ib_logfile[数字格式] 

• innodb_log_group_home_dir，该参数指定了redo日志文件所在的目录，默认值就是当前的数据目录。
• innodb_log_file_size，该参数指定了每个redo日志文件的大小，默认值为48MB，
• innodb_log_files_in_group，该参数指定redo日志文件的个数，默认值为2，最大值为100。

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

SHOW ENGINE INNODB STATUS

• 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值。

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

通过mysqlbinlog工具来恢复数据

Log Event保存在一个`binlog_cache_mngr`数据结构中，在该结构中有两个缓冲区

• 一个是 `stmt_cache` ：用于存放不支持事务的信息；
• 一个是`trx_cache` ：用于存放支持事务的信息。

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

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

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

普通

唯一

主键

组合

外键

全文（FULLTEXT）

B+Tree 

Hash索引

R-tree 索引（空间索引）

Full-text （全文索引）

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

聚簇索引

非聚簇索引

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

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

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

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

show status like 'Com_______';

show status like 'Innodb_rows_%';

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

id 字段是 select查询的序列号：

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

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref

• system > const > eq_ref > ref > range > index > ALL

NULL

System

const

eq_ref

ref

fulltext

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

all

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

实际使用的索引

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

计算规则

扫描行的数量（预估值）

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

索引下推

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

索引合并算法

Sending data

select * from tb_item where id < 4;

select * from information_schema.optimizer_trace;

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

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

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

索引是索引少量数据的。

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

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

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

手动提交事务

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

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

排序算法

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

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

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

建议使用 union 替换 or

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

优化思路一

优化思路二

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

USE INDEX

IGNORE INDEX

FORCE INDEX

count效率

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

常见优化方法

表连接常见的三种算法

优化join查询效率

多表连接的成本分析

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

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

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

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

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

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

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

串行操作

CAS

LOCK IN SHARE MODE

FOR UPDATE

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

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

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

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

主键加锁

唯一键加锁

非唯一键加锁 加锁行为：对满足id=10条件的记录和主键分别加X锁，然后在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)- (11,f)范围分别加Gap Lock。

无索引（等价于表锁）

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

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

读读并发 、读写互斥

读视图

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

DB_TRX_ID：6byte，事务ID

DB_ROLL_PTR：7byte，回滚指针

DELETED_BIT：1byte

查询操作不会记录

分类

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

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

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

读视图的可见性算法

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

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句 ： update tb_book set status='1' , 如果是`STATEMENT 日志格式，在日志中会记录一行SQL文件； 如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更

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

通过 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" 之前产生的所有日志 。  

设置参数 --expire_logs_days=#,     此参数的含义是设置日志的过期天数， 过了指定的天数后日志将会被自动删除

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

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。

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

查询不需要的记录

总是取出全部列 （select *）

重复查询相同的数据

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

响应时间

扫描的行数和返回的行数

扫描的行数和访问类型

• 如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。
• 在完成这些子任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作,CPU操作和内存不足时导致的IO操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用
• 优化查询的目的就是减少和消除这些操作所花费的时间

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

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

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

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

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

CPU成本 = 所查询到的数量集的行数 * 0.2  + 0.01   【0.01是mysql的固定调节参数】

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

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

• 只有 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!。

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

系统变量optimizer_search_depth

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

SELECT * FROM mysql.server_cost

 SELECT * FROM mysql.engine_cost;

开启innodb_stats_auto_recalc

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

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

ANALYZE TABLE order_exp

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

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

从库

分库分表

分库分表

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

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

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

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

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

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

• 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，直到节点恢复正常

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

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，并开启主从复制

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

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

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

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

• - 主库A执行完 INSERT c=4 ，得到 (4,4) ，然后开始执行 主从切换
• - 主从之间有5S的同步延迟，从库B会先执行 INSERT c=5 ，得到 (4,5)
• - 从库B执行主库A传过来的binlog日志 INSERT c=4 ，得到 (5,4)
• - 主库A执行从库B传过来的binlog日志 INSERT c=5 ，得到 (5,5)
• - 此时主库A和从库B会有 两行 不一致的数据

根据特定字段的范围进行拆分，比如用户ID、订单时间、产品价格等。例如：{[1 - 100] => Cluster A, [101 - 199] => Cluster B}

• 优点：新的数据可以落在新的存储节点上，如果集群扩容，数据无需迁移。 
• 缺点：数据热点分布不均，数据冷热不均匀，导致节点负荷不均。

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

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

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

优缺点

应用场景

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

扩容步骤

优缺点

应用场景

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

垂直分库

垂直分表

水平分表

水平分库

站内信

用户表

流水表

三者区别

术语

分片算法

分片策略

分片策略配置

内置主键生成器

自定义主键生成器

Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库， 以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM 框架的使用。

• 适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使 用JDBC。
• 基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。 
• 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

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

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

垂直分表

广播表

分库分表

读写分离剖析

强制路由剖析

数据脱敏