【MySQL】MySQL从入门到放弃

---

---

声明

本文内容来源于博主在学习掘金博主“竹子爱熊猫”竹哥的 Mysql专栏种所做的总结与整理。

特别说明：
本文中包含的大部分图片直接引用了原文中的图片，所有图片版权归原作者所有。
本文中的部分代码示例与思路均来源于学习过程中的整理与实践，若有雷同之处，敬请谅解。
本文的目的是分享学习过程中的理解与心得，若有不妥之处，请随时指出。

版权声明：
文章的原创部分属于博主本人，转载请注明出处。
由于部分内容参考了竹哥的专栏，若想了解更多详细信息，请访问原文
再次感谢竹哥的辛勤创作和分享，也感谢读者的阅读与支持！

MYSQL

SQL: 标准结构化查询语言(Structured Query Language)简称.

一,架构

• 网络连接层 主要是数据库连接池,负责处理所有客户端接入的工作.
• 系统服务层 主要包含 SQL接口、解析器、优化器以及缓存缓冲区四块区域.
• 存储引擎层 MySQL支持的各存储引擎,如InnoDB、MyISAM等.
• 文件系统层 涵盖了所有的日志,以及数据、索引文件,位于系统硬盘上.

1.1.网络连接层

MySQL的连接一般都是基于TCP/IP协议建立网络连接.还支持另一种连接方式,Unix系统下的Socket直连(使用较少).
连接 mysql tcp三次握手

mysql -h 127.0.0.1 -uroot -p123456  

支持ssl加密连接

通过show processlist;命令查询所有正在运行的线程:

• Id 当前线程的ID值,可以利用这个ID,使用kill强杀线程.
• User 当前线程维护的数据库连接,与之对应的用户是谁.
• Host 与当前线程保持连接关系的客户端地址(IP+Port).
• db 目前线程在哪个数据库中执行SQL.
• Command 当前线程正在执行的SQL类型,.
  • Create DB 正在执行创建数据库的操作.
  • Drop DB 正在执行删除数据库的操作.
  • Execute 正在执行预编译的SQL(PreparedStatement).
  • Close Stmt 正在关闭一个PreparedStatement.
  • Query 正在执行普通的SQL语句.
  • Sleep 正在等待客户端发送SQL语句.
  • Quit 当前客户端正在退出连接.
  • Shutdown 正在关闭MySQL服务端.
• Time 表示当前线程处于目前状态的时间,单位是秒.
• State 表示当前线程的状态
  • Updating 当前正在执行update语句,匹配数据做修改操作.
  • Sleeping 正在等待客户端发送新的SQL语句.
  • Starting 目前正在处理客户端的请求.
  • Checking table 目前正在表中查询数据.
  • Locked 当前线程被阻塞,其他线程获取了执行需要的锁资源.
  • Sending Data 目前执行完成了Select语句,正在将结果返回给客户端.
• Info 一般记录当前线程正在执行的SQL,默认显示前一百个字符,查看完整的SQL可以使用show full processlist;命令.

数据库连接池

复用线程,管理线程,限制最大连接数.
连接池的最大线程数可以通过参数 max-connections 来控制:
命令:

show variables like '%max_connections%';    查询目前DB的最大连接数.
    set GLOBAL max_connections = 200;           修改数据库的最大连接数为指定值.
    show status like "Threads%";                对客户端的连接数进行统计
        Threads_cached 目前空闲的数据库连接数.
        Threads_connected 当前数据库存活的数据库连接数.
        Threads_created MySQL-Server运行至今,累计创建的连接数.
        Threads_running 目前正在执行的数据库连接数.

1.2.系统服务层

MySQL大多数核心功能都位于这一层: 客户端SQL请求解析、语义分析、查询优化、缓存以及所有的内置函数,
跨引擎的功能都在这一层实现: 存储过程、触发器和视图等一系列服务.

1.2.1.SQL接口

SQL语句的类型:

• DML 数据库操作语句,比如 update、delete、insert等.
• DDL 数据库定义语句,比如 create、alter、drop等.
• DQL 数据库查询语句,比如最常见的 select.
• DCL 数据库控制语句,比如 grant、revoke等控制权限的语句.
• TCL 事务控制语句,例如 commit、rollback、setpoint 等语句.

1.2.2.存储过程

编写好的一段较为常用或复杂 SQL语句,然后存储起来,经过编译、优化,完成后,这个“过程”会被嵌入到MySQL中.

1.2.3.触发器

一种特殊的存储过程,存储过程需要手动调用后才可执行,而触发器可由某个事件主动触发执行.
MySQL支持 INSERT、UPDATE、DELETE 三种事件触发,也可以通过 AFTER、BEFORE 语句声明触发的时机是在操作执行之前还是执行之后.

1.2.4.解析器

解析器的作用主要是做词法分析、语义分析、语法树生成…验证SQL语句是否正确,以及将SQL语句解析成MySQL能看懂的机器码指令.
select sql解析顺序: from on join where group-by having select distinct order-by limit

1.2.5.优化器

优化器的主要职责在于生成执行计划,比如选择最合适的索引,选择最合适的join方式等,最终会选择出一套最优的执行计划.
优化器生成了执行计划后,维护当前连接的线程会负责根据计划去执行SQL,执行的过程 实际上是在调用存储引擎所提供的API.

1.2.6.缓存,缓冲

缓冲区的设计主要是为了通过内存的速度来弥补磁盘速度较慢对数据库造成的性能影响
库中读取某页数据操作时,会先将从磁盘读到的页存放在缓冲区中,后续操作相同页的时候,可以基于内存操作.
对数据库进行写操作时,先从缓冲区中查询是否有要操作的页:

• 如果有,则直接对内存中的数据页进行操作(修改、删除等),对缓冲区中的数据操作完成后,返回成功的信息,
• MySQL 在后台利用 Checkpoint 检查点机制,将内存中更新的数据刷写到磁盘.
  InnoDB的缓冲区叫做innodb_buffer_pool,而MyISAM则叫做key_buffer.

1.3.存储引擎层

最为常用的则是 InnoDB 与 MyISAM引擎

    show variables like '%storage_engine%';     查看当前所使用的引擎.  

存储引擎是 MySQL数据库中与磁盘文件交互的子系统,引擎负责数据管理,库表管理、索引管理等.

1.4.文件系统层

基于机器物理磁盘的一个文件系统,分为 日志模块,数据模块
包含了配置文件、库表结构文件、数据文件、索引文件、日志文件等,与上层的存储引擎做交互,负责数据的最终存储与持久化工作.

1.4.1.日志模块

七种常用的日志类型

常用日志类型	名称	作用
bin-log	二进制日志	MySQL 数据库的所有写操作(增删改)
redo-log	重做重写日志	MySQL 崩溃时,对于未落盘的操作会记录在这里面,用于重启时重新落盘(InnoDB专有)
undo-log	撤销回滚日志	记录事务开始前[修改数据]的备份,用于回滚事务(InnoDB专有)
error-log	错误日志	记录MySQL启动、运行、停止时的错误信息
general-log	常规日志	记录MySQL收到的每一个查询或SQL命令
slow-log	慢查询日志	记录执行时间较长的SQL
relay-log	中继日志	用于主从复制做数据拷贝

1.4.2.数据模块

MySQL的所有数据最终都会落盘,不同的数据存储的格式不同,MySQL中常见的数据文件类型.

文件	描述
db.opt文件	记录当前数据库使用的字符集和验证规则等信息
.frm文件	存储表结构的元数据信息文件,每张表都会有
.MYD文件	存储表中所有数据的文件(MyISAM引擎独有)
.MYI文件	存储表中索引信息的文件(MyISAM引擎独有)
.ibd文件	存储表数据和索引信息的文件(InnoDB引擎独有)
.ibdata文件	存储共享表空间的数据和索引的文件(InnoDB引擎独有)
.ibdata1文件	存储MySQL系统(自带)表数据及结构的文件
.ib_logfile0/.ib_logfile1文件	用于故障数据恢复时的日志文件
.cnf/.ini	MySQL的配置文件,Windows下是.ini,其他系统大多为.cnf

二,SQL 执行

2.1.执行流程

连接数据库

读操作执行流程:
执行查询SQL时,MySQL不关心大多数的普通查询,但慢查询SQL除外,当一条查询SQL的执行时长超过规定的时间限制,就会被记录到慢查询日志中.

写操作执行流程:
任何一条写 SQL都是有状态的,只要是会对数据库发生更改的SQL,执行时都会被记录在日志中.

• 首先所有的写SQL在执行之前都会将历史SQL,记录在undo-log撤销/回滚日志中(并使用隐藏字段回滚指针指向对应undo.log记录)
• 然后记录redo-log日志.(InnoDB引擎专属),主要是为了保证事务的原子性和持久性,记录 写SQL的 事务过程,
  • 如果宕机,重启时就可以通过redo_log日志恢复更新的数据.
  • 在 写SQL 正式执行之前,先记录一条 prepare 状态的日志,表示当前 SQL准备执行,
  • 执行完成并且事务提交后,日志记录的状态更改为 commit 状态.
• 还会记录bin-log日志,和redo-log 都是记录对数据库发生更改的SQL,(redo-log是InnoDB引擎专属),bin-log日志是 MySQL自带日志

2.2.刷盘

无论是什么日志,都需要在磁盘中存储,而本身写SQL在磁盘中写表数据效率就较低了,此时还需写入多种日志,效率定然会更低.
大部分日志记录 先写到缓冲区中,然后再异步刷写到磁盘中.
redo-log日志在内存中有redo_log缓冲区中,bin-log日志同理,当需要记录日志时,都是先写到内存中的缓冲区.

内存中的日志数据何时会刷写到磁盘呢？由刷盘策略来决定.

• redo-log日志刷盘策略 innodb_flush_log_at_trx_commit 参数控制,
  • 0:有事务提交的情况下,每间隔一秒时间刷写一次日志到磁盘.
  • 1:每次提交事务时,都刷写一次日志到磁盘(性能最差,最安全,默认策略).
  • 2:每当事务提交时,把日志记录放到内核缓冲区,刷写的时机交给 OS控制(性能最佳).
• bin-log日志刷盘策略 sync_binlog 参数控制:
  • 0:同 innodb_flush_log_at_trx_commit 参数2.把日志记录放到内核缓冲区,刷写的时机交给 OS控制(性能最佳).
  • 1:同 innodb_flush_log_at_trx_commit 参数1,每次提交事务都会刷盘(默认).

2.3.返回

查询SQL 的 数据是逐条返回的模式,所有数据全部查出来之后再一次性返回(返回到SQL接口),可能导致撑满内存;
写SQL 执行完成后,仅返回执行状态、受影响的行数以及执行耗时;

三.库表设计

设计DB库表结构时,也有一些共同需要遵守的规范,这些规范在数据库设计中被称为“范式”.

• 数据库三大范式(1NF、2NF、3NF)
• 第四范式(4NF)和第五范式:完美范式(5NF)
• 巴斯-科德范式(BCNF)
• 反范式设计

键(由一个或者多个属性组成):

• 超键 唯一标识一条记录的属性集叫做超键
• 候选键 超键中不包括多余的属性,这个超键就是一个候选键
• 主键 用户从候选键中选择的一个 作为主键
• 外键 其他表的主键

属性:

• 主属性 包含在任意候选键中的属性都称之为主属性
• 非主属性 不包含在任何一个候选键中的属性

码:

• 码 可以标识一个元组(记录)的属性
• 主码 主键
• 候选码 候选键

3.1.三大范式

• 第一范式 1NF
  确保原子性 存储的数据具备不可再分性.
• 第二范式 2NF
  确保唯一性 1NF基础上 每一条数据记录,都是可唯一标识的,数据库表的每一列都完全依赖主键(不能只依赖主键的一部分),
  非码属性(不能标识记录的属性)必须依赖于候选码(可唯一标识记录的属性).
• 第三范式 3NF
  确保独立性 2NF基础上 不能与除主键外的字段存在依赖,每个字段之间没有就依赖,都是独立的.

范式的优缺点:

• 优点:
  数据的标准化有助于消除数据库中的数据冗余.
  第三范式通常被认为在性能,扩展性和数据完整性方面达到了最好的平衡.
• 缺点:
  降低了查询效率,范式等级越高,设计出来的表就越多,进行数据查询的时候就可能需要关联多张表,不仅代价昂贵,而且可能会使得一些索引失效.

3.2.巴斯-科德范式(BCNF 3.5NF),第四范式(4NF),第五范式(5NF 完美范式)

• 巴斯-科德范式(BCNF 3.5NF)
  3NF基础上 任何主属性不能对其他主键子集存在依赖.(联合主键中的某列值,不能与联合主键中的其他列存在依赖关系)

• 第四范式 4NF
  BCNF基础上 不存在多值依赖
  多值依赖:表中的字段之间存在一对多的关系,也就是一个字段的具体值会由多个字段来决定(一个表中至少需要有三个独立的字段才会出现多值依赖问题)

• 第五范式 5NF
  4NF的基础上 消除表中的连接依赖,直到表中的连接依赖都是主键所蕴含的.

• 域键范式
  略

3.3.反范式

违反数据库范式的设计方法,就被称之为 反范式设计.
范式只是提出设计的标准,实际设计的时候,可能为了性能和读取效率违反范式的原则,通过增加少量的冗余或重复的数据来提高数据库的读取性能,
减少关联查询,实现空间换时间的目的.

增加冗余冗余字段要符合条件:

1. 冗余字段不需要经常进行修改
2. 冗余字段查询的时候不可或缺

四,索引

索引是帮助MySQL高效获取数据的 排好序 的 数据结构,

4.1.创建索引

-- 创建表时添加
CREATE TABLE tableName(  
 .....,
  * INDEX [indexName] (columnName(length))  
);

ALTER TABLE tableName ADD * INDEX indexName(columnName(length) [ASC|DESC]) USING HASH;
CREATE * INDEX indexName ON tableName (columnName(length) [ASC|DESC]) USING HASH;

• indexName 当前创建的索引,创建成功后叫啥名字.
• tableName 要在哪张表上创建一个索引,这里指定表名.
• columnName 要为表中的哪个字段创建索引,这里指定字段名.
• length 如果字段存储的值过长,选用值的前多少个字符创建索引.
• ASC|DESC 指定索引的排序方式,ASC是升序,DESC是降序,默认ASC.
• *:索引类型
  • UNIQUE INDEX: 唯一索引;
  • PRIMARY KEY: 主键索引;
  • FULLTEXT INDEX: 全文索引;
  • SPATIAL KEY: 空间索引;`

4.2.查询删除

    查询索引 
    SHOW INDEX FROM tableName;
        Table           索引属于那张表.
        Non_unique      是否属于唯一索引,0代表是的,1代表不是.
        Key_name        索引的名字.
        Seq_in_index    如果当前是联合索引,目前字段在联合索引中排第几个.
        Column_name     当前索引是位于哪个字段上建立的.
        Collation       字段值存储在索引中方式,A表示有序存储,NULL表无序.
        Cardinality     索引的散列程度,也就是索引中存储了多少个不同的值.
        Sub_part        索引使用了字段值的多少个字符建立,NULL表示全部.
        Packed          存储字段值时以什么方式压缩,NULL表示未压缩,
        Null            值是否存在NULL值,YES表示存在.
        Index_type      索引的结构(BTREE, FULLTEXT, HASH, RTREE).
        Comment         备注信息.
        Index_comment
        Visible
        Expression
    删除索引
        DROP INDEX indexName ON tableName;
    强制为SELECT 语句指定索引 (一条查询语句在有多个索引可以检索数据时,显式指定一个索引,减少优化器选择索引的耗时) 一般别用!!!
        SELECT * FROM table_name FORCE INDEX(index_name) WHERE.....;     

4.3.索引分类

聚簇索引、非聚簇索引、
唯一索引、主键索引、联合索引、全文索引、
单列索引、多列索引、复合索引、
普通索引、二级索引、辅助索引、次级索引、有序索引、
B+Tree索引、R-Tree索引、T-Tree索引、Hash索引、
空间索引、前缀索引…

4.3.1.按数据结构划分

B+Tree类型

MySQL中最常用的索引结构,大部分引擎支持,有序!!!

Hash类型

大部分存储引擎都支持,字段值不重复的情况下查询最快,无序!!!

R-Tree类型

MyISAM引擎支持,也就是空间索引的默认结构类型.

T-Tree类型

NDB-Cluster引擎支持,主要用于MySQL-Cluster服务中.

4.3.2.按字段数量划分

单列索引是指索引是基于一个字段建立的

• 唯一索引 指索引中的索引节点值不允许重复,一般配合唯一约束使用.
• 主键索引 主键索引是一种特殊的唯一索引,和普通唯一索引的区别在于不允许有空值.
• 普通索引 通过KEY、INDEX关键字创建的索引就是普通索引.

多列索引(组合索引、联合索引!!、复合索引、多值索引…):

由多个字段组合建立的索引(最左前缀,只有当查询条件中了包含了多列索引的第一个字段时,才能使用多列索引)

• 前缀索引:
  如果字段存储的值过长,选用值的前多少个字符创建索引 length 字段;
  ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]) USING HASH;
• 联合索引:
  • 可以使用INDEX关键字,让多个列组成一个普通联合索引;
  • 可以使用UNIQUE INDEX关键字,让多个列组成一个唯一联合索引;
  • 可以使用FULLTEXT INDEX关键字,让多个列组成一个全文联合索引;

4.3.3.按功能逻辑划分

普通索引,唯一索引,主键索引,全文索引(5.7前仅MyISAM支持),空间索引(仅MyISAM)

主键索引

不能使用create语句创建,不能使用 INDEX;一般都在建表DDL语句内创建;

全文索引

全文索引引入MySQL后,代替之前的 like% 模糊查询,效率更高.
只能创建在CHAR、VARCHAR、TEXT等这些文本类型字段上,而且使用全文索引查询时,条件字符数量必须大于3才生效.

ALTER TABLE tname ADD FULLTEXT INDEX indexName(columnName) WITH PARSER NGRAM;
    MyISAM引擎,5.7后InnoDB引擎支持全文索引
    CHAR等文本字段;
    支持中文需要指定解析器 with parser ngram;
    
    全文索引有自己的语法,优化器不能自动选择.
    最小搜索长度和最大搜索长度,全文索引参数,可通过 show variables like '%ft%';命令查询
        ft_min_word_len 使用MyISAM引擎的表中,全文索引最小搜索长度.默认4;
        ft_max_word_len 使用MyISAM引擎的表中,全文索引最大搜索长度.默认84;
        ft_query_expansion_limit MyISAM中使用with query expansion搜索的最大匹配数.默认20;
        innodb_ft_min_token_size InnoDB引擎的表中,全文索引最小搜索长度.默认3;
        innodb_ft_max_token_size InnoDB引擎的表中,全文索引最大搜索长度.默认84;

全文索引中有两个专门用于检索的关键字:

• MATCH(column)、 负责指定要搜索的列,这里要指定创建全文索引的字段
• AGAINST(关键字), 指定要搜索的关键字
  支持三种搜索模式:
  • 自然语言模式(默认搜索模式)

    SELECT * FROM `tname` WHERE MATCH(column) AGAINST('text');
    

  • 布尔搜索模式

    SELECT * FROM `tname` WHERE MATCH(column) AGAINST('+textX -textY' IN BOOLEAN MODE);
    

    • 表示必须匹配的行数据必须要包含相应关键字.
    • 和上面的+相反,表示匹配的数据不能包含相应的关键字.
      > 提升指定关键字的相关性,在查询结果中靠前显示.
      < 降低指定关键字的相关性,在查询结果中靠后显示.
      ~ 表示允许出现指定关键字,但出现时相关性为负.
      * 表示以该关键字开头的词语,如A*,可以匹配A、AB、ABC…
      “” 双引号中的关键字作为整体,检索时不允许再分词.
      “X Y”@n “” 包含的多个词语之间的距离必须要在n之间,单位-字节.
  • 查询拓展搜索

    SELECT * FROM `tname` WHERE MATCH(column) AGAINST('text' WITH QUERY EXPANSION);
    

    先会根据指定的关键字进行一次全文检索.
    第二阶段对指定的关键进行分词,然后再进行一次全文检索.

空间索引:

仅有MyISAM支持空间索引;
在 MySQL中总共支持 GEOMETRY、POINT、LINESTRING、POLYGON 四种空间数据类型,空间索引基于这些类型的字段建立.
空间索引必须要建立在类型为GEOMETRY、POINT、LINESTRING、POLYGON的字段上;
sql ALTER TABLE tableName ADD SPATIAL KEY indexName(columnName);

4.3.4.按存储方式划分

聚簇索引: 聚集索引、簇类索引.
非聚簇索引: 非聚集索引、非簇类索引、二级索引、辅助索引、次级索引.

连续并不是指索引节点,而是指索引数据和表数据.

聚簇索引: 索引与表数据在连续空间 逻辑上连续且物理空间上的连续(数组)

叶子节点存储的是索引所在行的完整数据.
InnoDB必须要有,且只有一个聚集索引

1. 如果表定义了PK,则PK就是聚集索引
2. 如果表没有定义PK,则第一个 NOT NULL UNIQUE 列是聚集索引
3. 否则,InnoDB会创建一个隐藏的row-id作为聚集索引

非聚簇索引: 索引与表数据不在同一连续空间 逻辑上的连续,物理空间上不连续(链表)

叶子节点存储行记录的聚簇索引值.

区别:

• 聚簇索引中,表数据和索引数据是按照相同顺序存储的,非聚簇索引不是.
• 聚簇索引在一张表中只能有一个,非聚簇索引则可以存在多个.
• 聚簇索引在逻辑+物理上都是连续的,非聚簇索引则仅是逻辑上的连续.
• 聚簇索引中找到了索引键就找到了行数据,非聚簇索引一般还需要做一次回表查询.(除非覆盖索引 查询的数据包含在索引中)

InnoDB的非聚簇索引与MyISAM索引(myisam都是非聚簇索引)的区别:
InnoDB中的非聚簇索引是 以聚簇索引的索引键(叶子结点数据),与具体的行数据建立关联关系的.
MyISAM中的索引是 以行数据的地址指针,与具体的行数据建立关联关系的.

由于 MyISAM 引擎中的索引可以根据指针直接获取数据,不需要做二次回表查询.

4.4.索引优劣

优点:

• 数据表的查询速度直线提升,数据量越大时效果越明显.
• 通过创建唯一索引,可以确保数据表中的数据唯一性,无需额外建立唯一约束.
• 在使用分组和排序时,同样可以显著减少SQL查询的分组和排序的时间.
• 连表查询时,基于主/外键字段上建立索引,可以带来十分明显的性能提升.
• 索引默认是B+Tree有序结构,基于索引字段做范围查询时,效率会明显提高.
• 从MySQL整体架构而言,减少了查询SQL的执行时间,提高了数据库整体吞吐量.

缺点:

• 建立索引会生成本地磁盘文件,需要额外的空间存储索引数据
• 写入数据时,需要额外操作维护索引结构.
• 写入数据时维护索引需要额外的时间开销,执行写SQL时效率会降低,性能会下降.

4.4.1.主键索引缺陷

使用有序主键
不能使用UUID,UUID是无序的,如果使用UUID作为主键,那么每当插入一条新数据,都有可能需要挪动树结构与数据(主键索引为聚簇索引).

4.4.2.联合索引缺陷

联合索引 查询使用时必须包含联合索引的第一个字段.

4.4.3.前缀索引缺陷

前缀索引 利用一个字段前N个字符创建索引,相较于使用一个完整字段创建索引,前缀索引能够更加节省存储空间,
MySQL无法通过前缀索引来完成 ORDER BY、GROUP BY 等分组排序工作,也无法完成覆盖扫描等操作.

4.4.4.全文索引缺陷

全文索引基于分词实现,对一个字段建立全文索引后,MySQL会对该字段做分词处理,分词结果也会被存储在全文索引中,全文索引的文件会很大.
由于全文索引对每个字段值都会做分词,当修改字段值后,不会立马自动更新全文索引,此时需要写存储过程,并调用 手动更新全文索引中的数据.
全文索引对中文支持不够友好,类似于英文可以直接通过符号、空格来分词,但中文无法精准的对一段文字做分词,存在精准度问题.

4.4.5.唯一索引缺陷

插入前会检查一遍表中是否存在相同的数据.相比普通索引慢.

4.4.6.哈希索引缺陷

数据结构为Hash类型的索引,只需要经过一次哈希计算就可获取到数据.
致命问题在于无序,无法基于哈希索引的字段做排序、分组等工作.

4.5.索引应用

如果表中存在聚簇索引,对其他字段建立的索引,都是辅助索引,其节点上的值,存储的并非一条完整的行数据,而是聚簇索引的索引字段值.
回表操作: 使用索引为辅助索引,先使用辅助索引存储的聚簇索引字段的值,再去聚簇索引查询具体的数据.

4.5.1.索引建立规则

• 经常频繁用作查询条件的字段
• 表的主外键或连表字段,必须建立索引(大幅度提升连表查询的性能)
• 建立索引的字段,区分性要足够高(提高索引的检索效率)
• 建立索引的字段,值不应该过长(较长的字段可以选择前缀索引)
• 建立联合索引,遵循最左前缀原则,将多个字段之间按优先级顺序组合,考虑优先级,查询频率最高的字段应当放首位 !!!
• 经常根据范围取值、排序、分组的字段应建立索引,(索引有序,能加快排序时间)
• 唯一索引,如果不会利用索引字段排序,可以使用Hash结构索引.
• 尽量使用联合索引代替单值索引,联合索引比多个单值索引查询效率要高.
• 值经常会修改字段,不合适建立索引,防止每次改变后需维护索引结构
• 字段存在大量的重复值时,不适合建立索引,区分性不高
• 索引不能参与计算,经常带函数查询的字段,不适合建立索引.
• 索引数量一般控制在3,最多不能超过5.
• 表的数据较少,不应建立索引,数据量不大时,维护索引开销更大.
• 索引的字段值无序时,不推荐建立索引,因为会造成页分裂,尤其是主键索引.

4.5.2.执行分析工具

EXPLAIN:分析sql执行状况

EXPLAIN	SELECT * FROM tname
EXPLAIN SLEECT * FROM tname WHERE MATCH(column) AGAINST('str')

• id 表示一个查询中各个子查询的执行顺序;
  id相同 执行顺序由上至下.
  id不同,id值越大优先级越高,越先被执行.
  id为null时表示一个结果集,不需要使用它查询,常出现在包含union等查询语句中. 最后执行.
• select_type 具体查询类型
  • SIMPLE 简单查询,没有union和子查询
  • PRIMARY 最外层查询,存在子查询的语句的最外层为primary
  • SUBQUERY 映射为子查询,在select或 where字句中包含的查询
  • DERIVED 派生表 子查询,from字句中包含的查询
  • DEPENDENT SUBQUERY 复杂SQL中的第一个select子查询(依赖于外部查询的结果集)
  • UNCACHEABLE SUBQUERY 不缓存结果集的子查询语句
  • UNION 联合UNION,union后的查询语句
  • UNION RESULT 从UNION中获取的结果集
  • DEPENDENT UNION 含义同上,基于外部查询的结果集查询
  • UNCACHEABLE UNION 含义同上,查询出的结果集不会加入缓存
  • MATERIALIZED 采用物化的方式执行的包含派生表的查询语句
• table 查询使用的表,从衍生表(子查询等)中查询数据时,显示对应的执行计划id
  tableName:基于磁盘中已创建的某张表查询
  derivenN:基于id=N的查询结果集,进一步检索数据
  unionM,N:会出现在查询类型为UNION RESULT的计划中,表示结果由id=M,N…的查询组成
  subqueryN:基于id=N的子查询结果,进一步进行数据检索
• partitions 表分区、表创建的时候可以指定通过那个列进行表分区
• type 查询访问类型,连接类型
  性能: all < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < fulltext < ref < eq_ref < const < system < null
  常见: system > const > eq_ref > ref > fulltext > range > index > all
  • all 全表查询,将遍历全表以找到匹配的行
  • index 全索引查询,index与ALL区别为index类型只遍历索引树
  • range 索引字段进行范围查询 between、<、>、in…等操作
  • index_subquery 在子查询中使用非主键索引(ref),同range,基于非主键、唯一索引字段进行in操作
  • unique_subquery 在子查询中使用主键索引(eq_ref),执行基于主键索引 进行in操作的’子查询语句’
  • index_merge 多条件查询时,组合使用多个索引来检索数据
  • ref_or_null 非主键索引做条件查询时,索引字段允许为null时
  • fulltext 使用全文索引
  • ref 使用非唯一索引
  • eq_ref 类似ref,使用唯一索引.
  • const 主键索引
  • system 仅有一行数据,const的特例
  • null 表中无数据,不经过检索
• possible_keys 可能使用哪个索引在表中找到记录
  查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用(该查询可以利用的索引,如果没有任何索引显示 null)
• key 实际使用的索引
  MySQL实际决定使用的键(索引),必然包含在possible_keys中
• key_len 索引中使用的字段长度
  key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的,一般为索引字段长度.
  不损失精确性的情况下,长度越短越好.
  前缀索引,前缀索引声明的前N个字节来检索数据.
  联合索引,当前SQL会用到的索引字段长度,可能不是全匹配的情况
  索引字段值允许为空,索引字段长度+1.
• ref 查询时会用到的常量或字段
  const:代表目前 基于主键字段值 或 数据库已有的常量(如null)查询数据.
  select… where 主键字段 = 主键值;
  select… where 索引字段 is null;
  func:代表当与索引字段匹配的值是一个函数
  select… where 索引字段 = 函数(值);
  字段名:基于该字段查询数据.
• rows MYSQL执行预计查询的行数
• filtered 表中不会扫描的数据百分比
• Extra MySQL解决查询的详细信息
  性能排序 Using index > NULL > Using index condition > Using where > Using where;Using index > Using join buffer >
  Using filesort > Using MRR > Using index for skip scan > Using temporary >
  Start temporary,End temporary > FirstMatch
  1. Using index 使用覆盖索引
  2. Using where 使用了用where子句来过滤结果集,无法从索引中获取数据,需要回表拿表数据.
  3. using index condition:使用了索引下推优化.表示查询条件使用到了联合索引的前面几个字段,要返回的列未完全被索引覆盖,需要回表.
  4. Using filesort 使用文件排序,使用非索引列进行排序时出现,非常消耗性能,尽量优化!
  5. Using temporary 使用了临时表
  6. select tables optimized away:表示在索引字段上使用了聚合函数.
  7. Using where;Using index: 要返回的数据在索引中包含,但并不是索引的前导列,需要回表获取数据.
  8. NULL:查询的数据未被索引覆盖,但where条件中用到了主键,可以直接读取表数据.
  9. No tables used:查询语句中不存在from子句,如desc table_name;
  10. Using join buffer (Block Nested Loop):连接查询时驱动表不能有效的通过索引加快访问速度时,会使用join-buffer来加快访问速度,在内存中完成Loop匹配.
  11. Impossible WHERE:where后的条件永远不可能成立,如where 1!=1.
  12. Impossible WHERE noticed after reading const tables:基于唯一索引 查询不存在的值.
  13. const row not found: 表中不存在数据时.
  14. distinct: 去重查询时,找到某个值的第一个值时,将查找该值的工作从去重操作中移除.
  15. Start temporary, End temporary: 临时表用于 Duplicate Weed out 半连接策略,也就是用来进行 semi-join 去重.
  16. Using MRR: 使用了 MRR 机制读取数据.
  17. Using index for skip scan: 使用了 索引跳跃扫描机制 读取数据.
  18. Using index for group-by: 执行分组或去重工作时,可以基于某个索引处理.
  19. FirstMatch: 对子查询语句进行 Semi-join 优化策略.

4.5.3.索引失效

1. 查询中带有OR会导致索引失效
2. like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作;可以尾部带%
3. 字符串不加单引号索引失效
4. 索引字段参与计算,会导致索引失效而转向全表扫描,不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)
   where id-1 = 1; ×
   where substring(name,0.1) = ‘…’ ×
5. 违背最左前缀原则失效
6. 不同字段对比失效
   where name = sex; ×
7. 反向范围查询失效
   NOT IN、NOT LIKE、IS NOT NULL、!=、<>… ×
8. 联合索引 范围查询右边失效

4.5.4.索引使用方式(避免索引失效)

1. OR,可以使用 多SQL 或 子查询 代替.
2. 模糊查询不要以 %开头,可以建立全文索引.
3. 注意字段的数据类型,隐式转换可能会导致索引失效.
4. 不要让索引字段执行计算工作,将计算工作放在客户端中.
5. 索引字段尽量不要使用计算类函数,一定要使用时将函数计算放在=后面.
6. 多条件 查询SQL注意最左匹配原则.
7. 多个字段对比查询,可以拆分为连表查询,使用临时表代替.
8. 不要使用反范围性的查询条件,大部分都会让索引失效.

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

4.5.5.索引覆盖

只访问索引字段的查询 [索引列和查询列一致],不需要额外访问聚簇索引,避免回表.

4.5.6.索引下推ICP(Index Condition Pushdown)查询优化机制

索引下推:将Server层筛选数据的工作,下推到引擎层处理.
如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,
然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器.
索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数.

不使用索引下推的查询过程:

1. 获取下一行,读取索引信息,根据索引将整行数据读取出来.
2. 通过where条件判断当前数据是否符合条件,符合返回数据.

使用索引下推的查询过程:

1. 获取下一行的索引信息.
2. 检查索引中存储的列信息是否符合索引条件,符合将整行数据读取出来,不符合跳过读取下一行.
3. 用剩余的判断条件,判断此行数据是否符合要求,符合要求返回数据.

5.6+默认开启,管理命令:

set optimizer_switch='index_condition_pushdown=off|on'; 

4.5.7.多量程读机制MRR(Multi-Range Read)

5.6+默认开启.
MRR 机制中,对于辅助索引中查询出的ID,会将其放到缓冲区的 read_rnd_buffer 中,
等索引检索工作完成后,或者缓冲区中的数据达到 read_rnd_buffer_size 大小时,
MySQL 对缓冲区中的数据排序,得到一个有序的ID集合:rest_sort,再根据 顺序IO去聚簇/主键索引 中回表查询数据.

管理命令:

SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';

4.5.8.索引跳跃扫描(Index Skip Scan)

SQL的查询条件中必须要包含联合索引的第一个字段并不是100%遵循的.
在8.0+版本中加入了一个新的优化机制 索引跳跃式扫描.
跳跃扫描并非 “跳过了”第一个字段,而是 优化器 重构SQL,对联合索引中的第一个字段的值去重,基于去重后的值全部拼接起来查一遍.

SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
... ...
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";

限制,多表联查时、SQL条件中有分组操作、DISTINCT去重… 时无法触发.

管理命令: 选择开启或关闭跳跃式扫描机制

set @@optimizer_switch = 'skip_scan=off|on';

4.6.索引结构

为什么选B+Tree

局部性原理:
假设目前有三块内存页相连的,CPU此刻在操作1页中的数据,一般同一类数据都会放入到物理相连的内存地址上存储,
对于2,3这两页有可能在接下来的时间内被操作,则会提前将其载入到高速缓冲区(L1/L2/L3),叫做利用 局部性原理“预读”数据

不支持数组、链表、队列 等结构,都是按序并排存储,走索引等价于走全表,并未带来查询时的效率提升,反而额外的存储开销.

• 二叉搜索树
  索引的字段值按顺序增长的,二叉树会转变为链表结构.
  树结构在磁盘中各节点的数据并不连续,无法利用局部性原理.
• 红黑树
  数据量多时,依旧会有很大的高度.
  每个节点中只存储一个数据,节点之间不连续的,无法利用局部性原理.
• B-Tree
  节点可以存储多个数据.对比红黑树树高更低,检索数据更快,能够充分利用局部性原理减少IO次数,但对于大范围查询的需求,依旧需要通过多次磁盘IO来检索数据.
• B+Tree:
  节点可以存储多个数据.
  最下面的一排节点之间,都存在一个单向指针,指向下一个节点所在的位置.
  B+Tree 的叶节点,在MySQL中不存储数据,仅存储指向叶子节点的指针,
  能够让一个叶节点中存储更多的元素,从而确保树的高度不会由于数据增长而变得很高.
  B+Tree 的叶子节点,会存储实际的数据,聚簇索引中直接存储对应的行数据,非聚簇索引中则存储指向主键/聚簇索引的字段值.
  每个叶子节点之间都有一根单向指针指向下一个节点,叶子节点之间形成一个单向链表结构,方便范围取值.
  Mysql 将叶子节点间又增加了指向前一节点的指针,关系优化为双向链表结构,即可以快速按正序进行范围查询,而可以快速按倒序进行范围操作,在某些业务场景下又能进一步提升整体性能！

索引存储文件

MyISAM存储引擎(非聚集)

一张表三个文件

• tbName.frm 表结构文件
• tbName.MYD 表数据文件
• tbName.MYI 表索引文件 叶子节点存储的是索引 所在行数据的磁盘地址

select id = n
先在MYI文件找到 n 节点,使用对应磁盘文件地址在MYD文件中获取数据

InnoDB存储引擎

一张表两个文件

• tbName.frm 表结构文件
• tbName.ibd 索引+数据

为什么InnoDB必须建主键,并使用 整型 自增 主键?
如果没有主键,且没有 非空唯一 的字段,则会使用隐藏的row-id作为聚集索引;无法直接使用聚集索引查询,且mysql性能会缺失;
整型主键: 类似 uuid的字符串类型是逐位比较,只有最后一位不同时,效率会有影响;整型比较更快.
自增主键: 自增 b+树永远向后增加节点; 非自增可能会放入中间位置,会分裂节点,造成树的经常调整;

五,事务

事务是一种机制,一个操作序列,包含了一组数据库操作命令,把所有的命令作为整体向系统提交或撤销操作请求,即这一组数据库命令要么都执行,要么都不执行.

5.1.事务ACID原则

• 原子性atomicity:要么都成功,要么都失败. 由undo_log日志 实现;
• 一致性consistency:事务前后数据一致. 业务代码保证;
• 隔离性/独立性isolation:多用户访问数据库(多事务并发),用户的事务不被其他用户的事务干扰; 基于MySQL的锁机制和MVCC机制实现
• 持久性durability:事务提交后不可逆; 持久性由redo_log日志实现;
  redo 磁盘顺序写,提高写并发; ibd是以表为单位的,redo仅一个文件,所有写操作都向redo写,实现持久化和提高写并发;
  BufferPool 提高读并发;

5.2.手动管理事务

start transaction | begin | begin work;开启一个事务
commit;				提交一个事务
rollback;			回滚一个事务
set autocommit =0; 	关闭自动提交
set autocommit =1;	恢复自动提交

5.3.事务回滚点

其中一部分执行成功后,就算后续SQL执行失败也照样提交,可以借助事务回滚点实现.
事务回滚点的命令:
savepoint point_name 添加一个事务回滚点
rollback to point_name 回滚到指定的事务回滚点

回滚到事务点后不代表事务结束,只是事务内发生了一次回滚,如果要结束当前这个事务,还依旧需要通过 commit|rollback; 命令处理.

5.4.事务问题与事务隔离机制

事务问题

• 脏读(Dirty Read):一个事务读取了另一个事务未提交的数据;另一事务回滚,即发生脏读;
• 不可重复读(Non-repeatable read):一个事务多次读取结果不同;长事务执行中另一事务更新了数据;
• 幻读(Phantom Read):同一个事务内多次查询返回的结果数量不一样.事务两次读取中间时,其他事务插入了新数据;
• 脏写: 其他事务已经修改了数据,但是本事务不知道;更新数据,导致其他事务的执行结果丢失;脏写;

隔离级别

• 读未提交 READ UNCOMMITTED:可以读取其他事务未提交的数据;
  存在脏读、不可重复读、幻读;
  实现:写互斥锁实现,读不互斥;

• 读已提交 READ COMMITTED:只可以读取事务已经提交的数据;
  SqlServer,oracle默认 解决脏读;
  存在不可重复读、幻读;
  实现:写互斥锁,读操作使用了 MVCC多版本并发控制的技术,MVCC机制不会让另一个事务读取正在修改的数据,而是读取上一次提交的数据.
  当A正在更新数据但还未提交时,事务B开始读取数据,此时MVCC机制则会基于表数据的快照创建ReadView,然后读取原本表中上一次提交的老数据.
  等事务A提交之后,事务B再次读取数据,此时MVCC机制又会创建一个新的ReadView,然后读取到最新的已提交的数据,
  此时事务B中两次读到的数据并不一致,因此出现了不可重复读问题.–MVCC机制在读已提交级别,每次读取都创建新的ReadView

• 可重复读 REPEATABLE-READ:读取后生成快照,只可以读取本事务修改的数据;
  mysql默认.
  存在幻读;
  实现: MVCC多版本并发控制的技术,
  在一个事务中,只有第一次执行查询会创建一个ReadView,在这个事务的生命周期内,所有的查询都会从这个ReadView中读取数据,
  确保了一个事务中多次读取相同数据是一致的.

• 串行 SERIALIZABLE:所有的事务依次逐个执行,最高的隔离级别,完全服从ACID的隔离级别.事务之间就完全不可能产生干扰
  实现:
  操作同一张表的事务只能一个一个执行,事务在执行前需要先获取表级别的锁资源,拿到锁资源的事务才能执行,其余事务则陷入阻塞,等待当前事务释放锁.

设置事务级别:

查询隔离级别:
    SELECT @@tx_isolation; 或
    
    show variables like '%tx_isolation%';
    8.0+:
        select @@global.transaction_isolation,@@transaction_isolation;
        SHOW VARIABLES LIKE '%transaction_isolation%';
设置隔离级别
    SET tx_isolation='read-uncommitted'   
        set transaction isolation level READ UNCOMMITTED;  当前连接生效
    SET tx_isolation='read-committed'
        实现  语句级快照 
    SET tx_isolation='repeatable-read'
        实现  事务级快照
    SET tx_isolation='serializable'
    8.0+:
        set session transaction isolation level READ UNCOMMITTED; 8.0 当前连接生效
        set global transaction isolation level READ COMMITTED; 全局生效

实现后台加锁  
    读锁  select... lock in shar mode;
    写锁  select/update/insert/delete... for update;

5.5.事务实现原理

MySQL的事务机制是基于日志实现的:
redo-log |重做重写日志 |MySQL 崩溃时,对于未落盘的操作会记录在这里面,用于重启时重新落盘(InnoDB专有)
undo-logs |撤销回滚日志 |记录事务开始前[修改数据]的备份,用于回滚事务

redo-log是一种 WAL(Write-ahead logging)预写式日志,在SQL执行前会先记录一条prepare状态的日志,然后再执行数据的写操作.

MySQL是基于磁盘的,但磁盘的写入速度相较内存而言会较慢,MySQL-InnoDB 引擎中不会直接将数据写入到磁盘文件中,先写到 BufferPool 缓冲区中,
当SQL被成功写入到缓冲区后,紧接着会将 redo-log 相应的记录改为 commit 状态,然后再由 MySQL刷盘机制 落盘.

默认情况下,一条SQL会被当成一个事务,数据写入到缓冲区后,就代表执行成功,自动修改日志记录为 commit 状态,由MySQL后台线程 刷盘.

多条sql执行过程:

1. 当 MySQL执行时, start transaction; 命令,先关闭自动提交.
2. 在 redo-log 中为第一条SQL语句,记录一条 prepare 状态的日志,
   然后再生成对应的 撤销日志 并记录到 undo-log 中,然后执行SQL,将要写入的数据先更新到缓冲区. (存在异议,redo-log,undo-log哪个先记录)
3. 对第二条 SQL语句做相同处理,如果有更多条SQL则逐条依次做相同处理…
4. 碰到 rollback、commit 命令时,对所有写 SQL 做相应处理.
   commit 将当前事务中所有的SQL的 redo-log日志改为 commit状态,由 MySQL 后台线程做刷盘,将缓冲区中的数据落入磁盘存储.
   rollback 在 undo-log 日志中找到对应的 撤销SQL 执行,将缓冲区内更新过的数据全部还原.

多版本并发控制 MVCC(Multi-Version Concurrency Control)
做到读写不阻塞,主要通过 undo-log 实现;实现 1.语句级快照;2.事务级快照;

六,锁

锁机制 是为了解决并发事务带来的问题,主要确保数据库中,多条工作线程并行执行时的数据安全性.

6.1.锁的分类

锁机制 由存储引擎负责实现的, 不同的存储引擎,支持的锁也不同.

6.1.1.粒度划分

6.1.1.1.表锁

• 全局锁:加上全局锁之后,整个数据库只能允许读,不允许做任何写操作
• 元数据锁/MDL锁:基于表的元数据加锁,加锁后表不允许其他事务操作
• 意向锁:InnoDB中 为了支持多粒度的锁,为了兼容行锁、表锁而设计的
• 自增锁/AUTO-INC锁:提升自增ID的并发插入性能而设计的

不同的存储引擎的表锁在使用方式上也有不同.
InnoDB是一个支持多粒度锁的存储引擎,它的锁机制是基于聚簇索引实现的,当SQL执行时,
如果能在聚簇索引命中数据,则加的是行锁,
如无法命中聚簇索引的数据则加的是表锁.

select * from `tname` for update;

MyISAM引擎不支持聚簇索引,需要使用额外的语法

-- myisam引擎中获取读锁(具备读-读可共享特性)
lock tables `table_name` read;
-- myisam引擎中获取写锁(具备写-读、写-写排他特性)
lock tables `table_name` write;
-- 查看目前库中创建过的表锁(in_use>0表示目前正在使用的表锁)
show open tables where in_use > 0;
-- 释放已获取到的锁 (需要自己手动释放锁,否则死锁)
unlock tables;

元数据锁 MDL锁

表锁 排他锁.
更改表结构时使用;
表都会存在一个.frm文件,主要存储表的结构(DDL语句),MDL锁基于.frm文件中的元数据加锁.
更改表结构时加锁,在MDL锁加锁后,整张表不允许其他事务做任何操作.

意向锁

为了支持多粒度的锁,为了兼容行锁、表锁而设计.
行锁锁了一条数据,此时令一事务需要加表锁,需要遍历所有记录是否加了行锁?遍历过程中,又有新事务加锁还再遍历一遍?
当事务数据加行锁前,就会先加一个表级别的意向锁.当另一事务尝试获取表级锁时,先查看表上是否有意向锁,再判断是否冲突,存在冲突阻塞等待.

自增锁

自增锁主要负责维护并发事务下自增列的顺序.
自增锁是一种特殊的表锁,仅为具备 AUTO_INCREMENT 自增字段的表服务.
自增锁有不同的级别,通过 innodb_autoinc_lock_mode 参数控制:

• innodb_autoinc_lock_mode = 0:传统模式.
  阻塞等待.
• innodb_autoinc_lock_mode = 1:连续模式(MySQL8.0以前的默认模式).
  对于能够提前确定数量的插入语句,则不会再获取自增锁,直接分配范围自增值,改为使用一种轻量级锁 互斥锁Mutex-Lock 来防止自增值重复分配.
• innodb_autoinc_lock_mode = 2:交错模式(MySQL8.0之后的默认模式).
  在交错插入模式中,对于 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT、LOAD DATA 等一系列插入语句,不再使用表级自增锁,
  全都使用 互斥锁 Mutex-Lock 来确保安全性.

插入语句分类:

• 普通插入:指通过INSERT INTO table_name(…) VALUES(…)这种方式插入.
• 批量插入:指通过INSERT… SELECT…这种方式批量插入查询出的数据.
• 混合插入:指通过INSERT INTO table_name(id,…) VALUES(1…),(NULL,…),(3…)一部分指定ID,一部分不指定.

全局锁

全局锁 基于整个数据库来加锁;
加上全局锁之后,整个数据库只能允许读,不允许做任何写操作,一般全局锁是在对整库做数据备份时使用.

-- 获取全局锁的命令
FLUSH TABLES WITH READ LOCK;
-- 释放全局锁的命令
UNLOCK TABLES;

6.1.1.2.行锁

MySQL诸多的存储引擎中,仅有InnoDB引擎支持行锁(InnoDB支持聚簇索引,InnoDB中如果能够命中索引数据,就会加行锁,无法命中则会加表锁)

• 记录锁/Record锁:行锁.
• 间隙锁/Gap锁:InnoDB中解决幻读问题的一种锁机制.
• 临建锁/Next-Key锁:间隙锁的升级版,同时具备 记录锁+间隙锁 的功能.

行锁的粒度粗化

内存中分配了一块空间存储锁对象,当区域满了后,就会将行锁粗化为表锁.
做范围性写操作时,要加的行锁较多时,行锁开销会较大,会粗化成表锁.

记录锁

记录锁即为行锁.

-- 获取行级别的 共享锁
select * from `table` where id = 1 lock in share mode;
-- 获取行级别的 排他锁
select * from `table` where id = 1 for update;

间隙锁

行锁的补充,主要是用来解决幻读问题.

间隙:
表相连的两条数据,ID字段之间从x跳到了x+n x~x+n 两者之间的范围则被称为“间隙”,间隙锁 主要锁定的是这块范围.

当对不存在的数据加锁后,默认锁定前后两条数据之间的区间,当其他事务再尝试向该区间插入数据时,就会陷入阻塞,只有当持有间隙锁的事务结束后,才能继续执行插入操作.
假设数据库只有id 为 1,10的数据

begin ;
select * from `tname` where id = 2 lock in share mode; //或加上 间隙锁(2~9)
commit;

此时另外的事务创建id为2~9区间的数据会阻塞等待.

加在表 ID=1的数据上,锁定的区域就是 (-∞ ~ 1).如果加在ID=10之后,锁定的区域就是(10 ~ +∞),即10之后到无穷大的区域.

临键锁 next-key lock

行锁 排他锁.
一种由记录锁+间隙锁组成的锁.即锁定 ‘左开右闭’ 的区间,也会锁定当前行数据.

在 InnoDB中,除开一些特殊情况外,当尝试对一条数据加锁时,默认加的是临键锁,而并非行锁、间隙锁!!

插入意向锁(隐式锁)

当事务执行插入语句阻塞时,就会生成一个插入意向锁,表示当前事务想对一个区间插入数据(目前的事务处于等待插入意向锁的状态)

当持有原本持有临建锁的事务提交后,当前事务即可以获取插入意向锁,然后执行插入操作,当此时如若又来一个新的事务,也要在该区间中插入数据,新的事务不会阻塞!!

能够被执行的插入语句,通过了唯一检测,因此插入时可以让多事务并发执行,同时如果设置了自增ID,也会通过自增锁确保安全性,
所以当多个事务要向一个区间插入数据时,插入意向锁是不会排斥其他事务的,故插入意向锁也是一种共享锁.

6.1.1.3.页锁

以页为粒度,锁住的是一页数据.
页面锁是 BerkeleyDB存储引擎(BDB) 支持的一种锁粒度,由于 BDB引擎 被 Oracle收购, MySQL5.1 以后不再直接性的支持BDB.

6.1.2.互斥划分

1. 共享锁 / S锁: 不同事务之间不会相互排斥、可以同时获取的锁.

   -- 通过关键字来使用共享锁
   SELECT... LOCK IN SHARE MODE;
   -- MySQL8.0 优化写法
   SELECT... FOR SHARE;
   

2. 排他锁/X锁: 不同事务之间会相互排斥、同时只能允许一个事务获取的锁.

   -- 获取排他锁:
   SELECT... FOR UPTATE;
   

   当两个事务同时获取排他锁,尝试读取一条相同的数据时,一个事务就会陷入阻塞,直至另一个事务结束才能继续;
   当另一个事务不获取排他锁读数据,而是以普通的方式读数据,这种方式则可以立刻执行.
   第二个事务中尝试通过加共享锁的方式读取这条数据,依旧会陷入阻塞状态.

3. 共享排他锁 / SX锁:MySQL5.7.2版本中新引入的锁,针对于 ‘悲观写入’ 加锁,主要是解决 SMO 带来的问题.
   SX锁的特性:不会阻塞 S锁,但是会阻塞 X、SX 锁.
   SQL执行概念:

   • 读取操作: 基于B+Tree去读取某条或多条行记录.
   • 乐观写入: 不会改变B+Tree的索引键,仅会更改索引值,只修改其他字段的数据,不会引起节点分裂.
   • 悲观写入: 会改变 B+Tree的结构,会造成节点分裂,比如无序插入、修改索引键的字段值.

   5.7前,一旦有操作导致了树结构发生变化,就会对整棵树加上’排他锁’,阻塞所有读写;
   SMO: 在SQL执行期间一旦更新操作 触发B+Tree叶子节点分裂,就会对整棵B+Tree加’排他锁’.会导致所有的读写操作都被阻塞,其影响巨大.

   MySQL5.7后,读操作执行:

   1. 对 B+Tree 加共享锁.
   2. 在基于 树检索数据的过程中,对于 走过的叶节点 加 共享锁.
   3. 找到需要读取的目标叶子节点后,先加 叶子结点 的 共享锁,释放 步骤2 上加的所有共享锁.
   4. 读取节点中的数据,读取完成后释放对应叶子节点上的共享锁.

   MySQL5.7中乐观写入的执行流程:

   1. 先会对B+Tree加共享锁.
   2. 在基于树 检索修改位置 的过程中,对于走过的叶节点会加共享锁.
   3. 找到需要 写入数据的目标叶子节点 后,加排他锁,释放 步骤2 上加的所有共享锁.
   4. 修改目标叶子节点中的数据后,释放对应叶子节点上的排他锁.

   MySQL5.7中悲观写入的执行流程:

   1. 对 B+Tree加 共享排他锁SX.当前事务执行过程中会阻塞其他尝试更改树结构的事务.
   2. 遍历查找需要写入数据的目标叶子节点,找到后对其分支加上排他锁,释放 1 中加的SX锁.
   3. 执行 SMO操作,也就是执行悲观写入操作,完成后释放 步骤2 中在分支上加的排他锁.

6.1.3.操作类型划分

1. 读锁:查询数据时使用的锁.
2. 写锁:执行插入、删除、修改、DDL语句时使用的锁.

6.1.4.加锁方式划分

1. 显示锁:编写SQL语句时,手动指定加锁的粒度.
2. 隐式锁:执行SQL语句时,根据隔离级别自动为SQL操作加锁.

6.1.5.按思想划分

1. 乐观锁:每次执行前认为自己会成功,因此先尝试执行.
   乐观锁机制,一般都是基于CAS思想实现.MySQL中则可以通过version版本号+CAS的形式实现乐观锁.

       UPDATE `tname` SET version = version + 1... WHERE... AND version = version;
   

   乐观锁都会配合轮询重试机制,比如上述执行失败后,再次执行相同语句,直到成功为止.
   写操作的并发较高时,就容易导致一个事务长时间一直在重试执行,从而导致客户端的响应尤为缓慢.

2. 悲观锁:每次执行前都认为自己无法成功,因此会先获取锁,然后再执行.
   每次执行前必须获取到锁,然后才能继续往下执行.

6.2.MVCC-多版本并发控制技术

MVCC (Multi-Version Concurrency Control)
只有InnoDB实现了MVCC机制.

MYSQL加锁后会让一部分事务串行化,并发事务串行化会使其效率更低.
MVCC机制 以另一种形式解决了并发事务造成的问题.

并发事务中又会分为四种情况,分别是读-读、写-写、读-写、写-读:

• 读读:多事务读取同一条数据.
• 写写:多事务同时更新同一条数据,存在更新覆盖问题.脏写.
• 读写/写读:脏读、不可重复读、幻读都出自于这种场景.

为了防止一些小概率事件(脏读、不可重复读、幻读,脏写),就将所有操纵同一数据的并发读写事务串行化,并不合理.
MVCC机制 在线程安全问题和加锁串行化之间做了一定取舍,达到了很好地平衡,即防止了脏读、不可重复读及幻读问题的出现,又无需对并发读-写事务加锁处理.

6.2.1.实现依赖

InnoDB + 隔离级别 RC读已提交/RR可重复读 支持MVCC

思想:事务问题都是基于 最新版本的数据并发操作才会出现,如果读、写的事务操作的同一个版本, 写操作走新版本,读操作走老版本,无论执行写操作的事务做什么,都不会影响读的事务.

MySQL中仅在RC读已提交级别、RR可重复读级别才会使用MVCC机制

• RU读未提交级别,允许存在脏读问题、允许一个事务读取另一个事务未提交的数据.
• Serializable串行化级别将所有的并发事务串行化处理,不存在所谓的多线程并发问题.

实现方式:主要通过 隐藏字段、Undo-log日志、ReadView 实现!!!

1.隐藏字段

1.行ID-ROW_ID(6Bytes)

row_id
InnoDB引擎 未定义主键、唯一索引时,其实默认也会存在聚簇索引,默认的row_id的聚簇索引上层无法使用,仅提供给InnoDB构建树结构存储表数据.

2.删除标识 - Deleted_Bit(1Bytes)

对于一条delete语句而言,当执行后并不会立马删除表的数据,而是将数据的 Deleted_Bit删除标识改为 1/true.
作用: 有利于聚簇索引
删除表数据时,有可能会破坏索引树原本的结构,导致出现叶子节点合并的情况.事务回滚时,又需重新插入这条数据,再次插入时又会破坏前面的结构,导致叶子节点分裂.

执行 delete语句就删除真实的表数据,由于事务回滚的问题,就很有可能导致聚簇索引树发生两次结构调整.
当执行delete语句时,只改变将隐藏字段中的删除标识改为 1/true,如果后出现回滚,直接将其标识再改回0/false即可,避免索引树的结构调整.

MySQL中 存在 purger 线程的概念,防止“已删除”的数据占用过多的磁盘空间,purger 线程会 自动清理 Deleted_Bit=1/true的行数据.

为了确保清理数据时不会影响 MVCC的正常工作,purger线程自身也会维护一个 ReadView,
如果某条数据的Deleted_Bit=true,并且 事务id TRX_ID 对 purge线程 的 ReadView 可见,那么这条数据是可以被安全清除的.

3.最近更新的事务ID-TRX_ID(6Bytes)

transaction_id

MySQL对于创建的事务(包含写入),都会为其分配一个事务ID,事务ID同样遵循顺序递增的特性.仅包含select查询语句的事务,则分配的事务ID=0.
对于手动开启的事务,MySQL都会为其分配事务ID,哪怕仅有select操作.

4.回滚指针-ROLL_PTR(7Bytes)

rollback_pointer

当一个事务对一条数据做了改动后,都会将旧版本的数据放到 Undo-log 日志中,
rollback_pointer就是一个地址指针,指向 Undo-log 中旧版本的数据,
当需要回滚事务时,回滚指针来找到改动前的旧版本数据,MVCC机制也利用回滚指针,实现了行数据的多版本.

2.Undo-log日志

    SELECT * FROM `users` WHERE user_id = 1;
    +---------+-----------+----------+----------+---------------------+
    | user_id | user_name | user_sex | password | register_time       |
    +---------+-----------+----------+----------+---------------------+
    |       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
    +---------+-----------+----------+----------+---------------------+
    
    UPDATE `users` SET user_name = "竹子" WHERE user_id = 1;
    UPDATE `users` SET user_sex = "男" WHERE user_id = 1;

不同的旧版本数据,会以roll_ptr回滚指针作为链接点,然后将所有的旧版本数据组成一个单向链表(头插法)

详细过程:

1. 对要修改的行数据加上排他锁.
2. 将原本的旧数据拷贝到 Undo-log 的 rollback Segment区域.
3. 对表数据上的记录进行修改, ‘修改完成后’ 将隐藏字段中的 trx_id 改为当前事务ID.
4. 将隐藏字段中的 roll_ptr 指向 Undo-log 中对应的旧数据,并在提交事务后释放锁.

Undo-log 中记录的旧版本数据,同样会占用空间,在事务提交后也会移除,
移除的工作同样由 purger线程 负责,purger 线程维护 ReadView 作为判断依据,决定何时移除Undo记录.

3.ReadView 读视图

问题: T2事务要查询一条行数据,此时这条行数据正在被T1事务写,代表着数据可能存在多个旧版本数据,事务在查询时,应该读这条数据的哪个版本？
用 ReadView 来做多版本的并发控制,根据 查询时机 选择 当前事务可见的 旧版本数据 读取.

RR级别下:一个事务启动后,首次 select操作时,MVCC 生成一个数据库 当前的 ReadView,通常事务与ReadView属于一对一的关系.

一个事务在尝试读取一条数据时,MVCC基于当前 MySQL的运行状态生成的快照,即ReadView.
在快照中记录着 当前所有活跃事务(还在执行的事务,即未提交/回滚的事务)的ID.
一般包含四个核心内容:

• creator_trx_id: 创建ReadView的事务ID.
• trx_ids: 生成ReadView时,系统内活跃的事务ID列表.
• up_limit_id: 活跃的事务列表中,最小的事务ID.
• low_limit_id: 生成当ReadView时,系统中要给下一个事务分配的ID值.

目前数据库中共有 T1~T5这五个事务,T1、T2、T4还在执行,T3已经回滚,T5已经提交.
此时一条查询语句执行时,利用 MVCC机制生成一个 ReadView,单纯select语句事务事务ID为0.

{
    "creator_trx_id" : "0",
    "trx_ids" : "[1.2.4]",
    "up_limit_id" : "1",
    "low_limit_id" : "6"
}

6.2.2.实现原理

MVCC机制主要通过隐藏字段、Undo-log日志、ReadView实现!!!

• 隐藏字段:
  • 主键id row_id
  • 删除标识 deleted_bit
  • 最新修改事务id trx_id
  • 回滚指针 roll_ptr
• undo-log: 撤销日志,实现数据的多版本
• readView:读视图,借此实现多版本并发控制
  • creator_trx_id: 创建 ReadView 的事务ID.
  • trx_ids: 生成 ReadView时,系统内活跃的事务ID列表.
  • up_limit_id: 活跃的事务列表中,最小的事务ID.
  • low_limit_id: 生成当ReadView时,系统中要给下一个事务分配的ID值.

Undo-log主要实现数据的多版本,ReadView则主要实现多版本的并发控制.

• 当事务尝试修改数据时,会将旧数据放入Undo-log日志中.
• 当事务尝试查询数据时,MVCC会生成一个ReadView快照.

T1、T2两个并发事务,T1目前在修改ID=1的这条数据,而T2则准备查询这条数据,T2执行流程:
先获取数据的行数据隐藏列,经过与readView读视图数据的对比判断,决定 查询数据的事务 能不能访问最新版的数据.

1. 事务中出现select语句,根据MySQL的当前情况生成一个ReadView.
2. 判断行数据中的隐藏列 trx_id(最近更新这条数据的事务ID) 与 ReadView.creator_trx_id(创建ReadView的事务ID) 是否相同:
   • 相同: 创建 ReadView 和 修改行数据 的事务是同一个,可以读取最新版本数据.
   • 不相同: 目前要查询的数据是被其他事务修改过的,继续执行.
3. 判断隐藏列 trx_id 是否小于 ReadView.up_limit_id(活跃的事务列表中最小的事务ID):
   • 小于: 代表 改动行数据的事务(trx_id) 在 创建快照前就已结束,可以读取最新版本数据.
   • 不小于: 代表 改动行数据的事务(trx_id) 还在执行,继续执行.
4. 判断隐藏列 trx_id 是否小于 ReadView.low_limit_id(下一个事务分配的ID值) 这个值:
   • 大于或等于: 代表 改动行数据的事务 是 生成快照后才开启的,不能访问最新版数据.
   • 小于: 代表 改动行数据的事务ID在 up_limit_id、low_limit_id之间,继续执行.
5. 如果隐藏列 trx_id 是否在 trx_ids中:
   • 在: 表示 改动行数据的事务目前依旧在执行,不能访问最新版数据.
   • 不在: 表示 改动行数据的事务已经结束,可以访问最新版的数据.

Undo-log日志中存在版本链,获取哪个版本的数据?
根据隐藏列 roll_ptr 回滚指针 遍历列表,检索到最合适的一条数据并返回.即 隐藏列 trx_id 不在 ReadView.trx_ids 活跃事务列表 中.(否则代表事务未提交)

幻读解决原理:
T1事务查询数据时,突然多出来的数据,判断新数据的事务状态:

• tx_id 大于 ReadView.low_limit_id,表示是在事务之后新增的数据.

不同级别的隔离机制不同.RC读已提交/RR可重复读的MVCC不同:

• RC级别下,MVCC机制是会在每次select语句执行前,都会生成一个ReadView.(后续select,会查询到其他事务提交的数据,造成不可重复读)
• RR级别中,一个事务只会在首次执行select语句时生成快照,后续所有的select操作都会基于这个ReadView来判断.(不存在不可重复读)

6.3.死锁

并发事务竞争共享资源造成互相等待的僵持现象.

6.3.1.Mysql自动检测死锁

当死锁问题出现时,MySQL会自动检测并介入,强制回滚结束一个事务,打破死锁僵局,让另一个事务能继续执行.

死锁解决方案:
锁超时机制:超出一定时间自动放弃等待并返回.
外力介入:将其中某事务强制结束,其他事务继续运行.

6.3.2.锁超时机制

show variables like 'innodb_lock_wait_timeout';

innodb默认超时时间 50s,高并发情况下时间太长,会导致大量事务阻塞.参数调小,也可能会影响正常事务等待锁的时间.

6.3.3.死锁监测算法

死锁监测算法
专门用于检测死锁问题,该算法会对于目前库中所有活跃的事务生成等待图 wait-for graph.当两事务之间出现闭环,Mysql会强制介入,回滚其中一个事务,解除死锁.

wait-for graph算法被启用后,MySQL收集两个信息:

• 锁的信息链表: 目前持有每个锁的事务是谁.
• 事务等待链表: 阻塞的事务要等待的锁是谁.

当某事务阻塞等待某个锁时,触发一次 死锁检测算法:

1. 以当前事务为起点,从 锁信息链表 中找到对应锁的持有者,
2. 在事务等待链表 中查找,持有锁的事务是否等待获取其他锁,
3. 经过循环,查看是否出现闭环,出现闭环则回滚事务.

具体回滚哪个事务,根据undo-log日志,选择回滚量最小的事务.
可以通过 innodb_deadlock_detect=on|off 参数,控制是否开启死锁检测机制.(在MySQL高版本中默认开启)

6.3.4.避免死锁

在业务允许的情况下,尽量缩短一个事务持有锁的时间、减小锁的粒度以及锁的数量.

1. 合理设计索引,减小锁的粒度.
2. 业务允许情况下,隔离级别调低.
3. 耗时较长的事务,放到特定时间执行.
4. 拆分大事务,缩短持有锁的时间.

6.4.锁的内存结构

Synchronized 基于 Monitor 实现;
ReentrantLock 基于 AQS 实现;

• 锁的事务信息:生成锁的事务的指针.

• 索引信息:行锁特有,记录加锁的行数据属于哪个索引和节点 的指针.

• 锁粒度信息:不同粒度的锁,其中存储的信息也并不同,
  表锁,记录是对哪张表加锁,以及表的一些信息.
  行锁,其中记录信息更多,有三个较为重要的:

  • Space ID: 加锁的行数据所在 表空间ID.
  • Page Number: 加锁的行数据所在 页号.
  • n_bits: 使用的比特位,对于一页数据中,加了多少个锁.

• 锁类型信息:
  锁结构的类型,采用 32bit 的 type_mode 表示,可以拆为 lock_mode、lock_type、rec_lock_type 三部分.
  

  • lock_mode:锁的模式,使用低四位.
    • 0000/0:表示当前锁结构是共享意向锁,即IS锁.
    • 0001/1:表示当前锁结构是排他意向锁,即IX锁.
    • 0010/2:表示当前锁结构是共享锁,即S锁.
    • 0011/3:表示当前锁结构是排他锁,即X锁.
    • 0100/4:表示当前锁结构是自增锁,即AUTO-INC锁.
  • lock_type:锁的类型,使用低位中的5~8位.
    • LOCK_TABLE:当第5个比特位是1时,表示目前是表级锁.
    • LOCK_REC:当第6个比特位是1时,表示目前是行级锁.
  • is_waiting:表示目前锁处于等待状态还是持有状态,使用低位中的第9位.
    • 0:表示is_waiting=false,即当前锁无需阻塞等待,是持有状态.
    • 1:表示is_waiting=true,即当前锁需要阻塞,是等待状态.
  • rec_lock_type:行锁的具体类型,使用其余位.
    • LOCK_ORDINARY: 当高23位全零时,表示目前是临键锁.
    • LOCK_GAP: 当第10位是1时,表示目前是间隙锁.
    • LOCK_REC_NOT_GAP: 当第11位是1时,表示目前是记录锁.
    • LOCK_INSERT_INTENTION: 当第12位是1时,表示目前是插入意向锁.
    • 其他的锁类型,会使用其他位.

• 其他信息:
  一些用于辅助锁机制的信息,如死锁检测机制中的「事务等待链表、锁的信息链表」,
  每一个事务和锁的持有、等待关系,在这里存储,将所有的事务、锁连接起来.

• 锁的比特位:
  一个比特数组,表的记录数量+2长度.行锁中,间隙锁可以锁定无穷小、无穷大,比特中,首位表示无穷小、末位表示无穷大.
  7条记录的 比特位信息为 000000000 共九位.
  对ID=2、3、6这三条数据加锁,比特数组就会变为 001100100.

6.5.InnoDB的锁实现

如果一个事务同时需要对表中的1000条数据加锁,SqlServer 在行记录上加锁, 会生成1000个锁结构.
Mysql 对事务加锁:

• 对表中不同行记录加锁的事务相同
• 需要加锁的记录在同一个页面中
• 事务加锁的类型都是相同的
• 锁的等待状态相同
  当以上条件满足时,满足条件的行记录加的是同一把锁.

6.6.获取/释放锁的过程

当事务需要获取某个行锁时,首先查看内存中是否存在这条数据的锁结构

1. 如果存在则生成一个锁结构,将其is_waiting对应的比特位改为1,表示目前事务在阻塞等待获取该锁.
   当其他事务释放锁后,会唤醒当前阻塞的事务,然后会将其is_waiting改为0,执行SQL.
2. 不存在则立即执行.

释放锁 由MySQL自己完成,当事务结束后 自动释放,释放时 查看内存中是否有锁结构正在等待获取目前释放的锁,如果有则唤醒对应的线程/事务.
锁的释放时机，在不同的隔离级别中也并不相同.在“读未提交”级别中，SQL执行完成后就立马释放锁.在“可重复读”级别中，事务结束后才会释放.

6.7.锁机制与隔离级别的实现

1. RU 读未提交
   读操作不加锁,写操作加排他锁.
   读取到其他事务的提交,脏读.

2. RC 读已提交
   读操作使用MVCC 机制.每次SELECT生成快照.写操作加排它锁.
   RC 级别下 MVCC 每次select都会创建新的readView,会读取到已提交的数据.
   两次读取结果不一致,不可重复读.

3. RR 可重复读
   读操作使用MVCC机制,首次SELECT生成快照,写操作加排它锁.
   读操作方案:

   1. 读操作加临键锁(读操作时,不允许其他事务修改数据),不允许 读写并存. ×
   2. RR级别下 MVCC 机制 只在第一次select时生成ReadView,多次读取结果一致.允许读写并存.(不仅解决不可重复读,还’基本’解决了幻读问题,看不到新增的数据)

   未完全解决 幻读 问题,
   t1事务读取数据 del_flag = 0的数据,存在id 1,2,3 的数据.
   t2事务新增用户 id为 4.密码 666666.
   t1事务读取数据 del_flag = 0的数据,存在id 123 的数据.(隐藏列 trx_id 大于 ReadView.low_limit_id(下一个事务分配的ID值),不可见)
   t1事务修改用户 del_flag = 0的数据,密码为 123456.
   此时若t2未提交,阻塞等待.
   此时若t2已提交,则会更新t2新增的数据.
   所以RR级别 只解决了幻读内读的问题,读取之后再更新会更新掉幻影数据.

4. Serializable
   读操作加共享锁,写操作加临键锁(锁定当前行数据以及左右区开间内的数据).

七,日志

常用日志类型	名称	作用
binlog	二进制日志	MySQL 数据库的所有写操作(增删改)
redo-log	重做重写日志	MySQL 崩溃时,对于未落盘的操作会记录在这里面,用于重启时重新落盘(InnoDB专有)
undo-log	撤销回滚日志	记录事务开始前[修改数据]的备份,用于回滚事务
error-log	错误日志	记录MySQL启动、运行、停止时的错误信息
general-log	常规日志	记录MySQL收到的每一个查询或SQL命令
slow-log	慢查询日志	记录执行时间较长的SQL
relay-log	中继日志	用于主从复制做数据拷贝

7.1.undo-log 撤销/回滚日志

InnoDB引擎独有的,用于回滚日志,和MVCC机制 读取数据.
主要用于实现事务ACID原则中的原子性和MVCC机制.

InnoDB 默认将 Undo-log 存储在 xx.ibdata 共享表数据文件当中,默认采用段的形式存储.

当一个事务尝试写某行表数据时,

• 将旧数据拷贝到 xx.ibdata文件中,
• 将表中行数据的隐藏字段:roll_ptr回滚指针会指向 xx.ibdata文件中的旧数据
• 再写表上的数据

共享表数据文件中,有一块区域名为 Rollback Segment 回滚段,每个回滚段中有 1024个 Undo-log Segment,
每个Undo段可存储一条旧数据,执行写SQL时,Undo-log就是写入到这些段.

MySQL5.5版本前,默认只有一个 Rollback Segment,
MySQL5.5版本后,默认有128个回滚段,即支持128*1024条Undo记录同时存在

当一个事务需要回滚时,本质上不会以执行反SQL的模式还原数据,
而是将roll_ptr回滚指针指向的Undo记录,从xx.ibdata共享表数据文件中拷贝到 xx.ibd 表数据文件,覆盖掉原本改动过的数据.
insert操作,不会产生Undo旧记录,隐藏字段中的roll_ptr=null,直接用null覆盖插入的新记录.

InnoDB在MySQL启动时,会在内存中构建一个 BufferPool 缓冲池,主要存放两类东西:

• 数据相关的缓冲,如索引、锁、表数据等,
• 各种日志的缓冲,如Undo、Bin、Redo…等日志.

当一条写SQL执行时,工作线程直接写磁盘太影响效率,会写在 undo_log_buffer 缓冲区中,写进缓冲区后由后台线程去刷写磁盘.

事务提交了,不会立马删除Undo记录,旧记录的删除工作,InnoDB中有专门的 purger 线程负责,
purger线程 内部会维护一个 ReadView 作为判断依据,决定何时移除Undo记录.(可能会有其他事务在通过快照,读Undo版本链中的旧数据)

参数

innodb_max_undo_log_size:   本地磁盘文件中,Undo-log的最大值,默认1GB.
innodb_rollback_segments:   指定回滚段的数量,默认为1个. 

innodb_undo_directory:      指定Undo-log的存放目录,默认放在.ibdata文件中.
innodb_undo_logs:           指定回滚段的数量,默认为128个.
innodb_undo_tablespaces:    指定Undo-log分成几个文件来存储,必须开启innodb_undo_directory参数.
innodb_undo_log_truncate:   是否开启Undo-log的在线压缩功能,即日志文件超过大小一半时自动压缩,默认OFF关闭.

7.2.redo-log 重做日志

InnoDB引擎独有的,用来实现数据的恢复.
主要用于实现事务原则中的持久性,确保事务提交后就不会丢失.

MySQL绝大部分引擎都 是基于磁盘存储数据的,但若每次读写数据都走磁盘,其效率必然十分低下.
因此InnoDB引擎在设计时,当 MySQL启动后就会在内存中创建一个 BufferPool,运行过程中会将大量操作汇集在内存中进行,先写到内存中,然后由后台线程再刷写到磁盘.
使用 BufferPool 提升了 MySQL 整体的读写性能,但基于内存的 随着机器的宕机、重启,其中保存的数据会消失,
未使用redo-log时:当事务向内存中写入数据后,MySQL突然宕机了,未刷写到磁盘的数据会丢失.
这违背了事务ACID原则中的持久性,所以 Redo-log的出现就是为了解决该问题.
Redo-log 是一种预写式日志,即在向内存写入数据前,会先写日志,当后续数据未被刷写到磁盘、MySQL崩溃时,可以通过日志来恢复数据,确保所有提交的事务都会被持久化.
工作线程执行SQL前,写的 Redo-log 日志,写在了内存中的 redo_log_buffer 缓冲区.

redo-log的刷盘策略

内存中的日志数据何时会刷写到磁盘呢？由刷盘策略来决定.
redo-log日志刷盘策略 innodb_flush_log_at_trx_commit 参数控制:

• 0:有事务提交的情况下,每间隔一秒时间刷写一次日志到磁盘.
• 1:每次提交事务时,都刷写一次日志到磁盘(性能最差,最安全,默认策略).
• 2:每当事务提交时,把日志记录放到内核缓冲区,刷写的时机交给 OS控制(性能最佳).

默认处于 1 级别,每次提交事务时都会刷盘,当一个事务执行成功后,Redo-log日志绝对会被刷写到磁盘中,无需担心会出现丢失风险.

记录日志的时候MySQL宕机了,代表着SQL都没执行成功,MySQL也不会向客户端返回任何信息.
导致客户端连接超时,一般客户端都会有超时补偿机制的,因此用户的操作依旧不会丢失.

Redo-log要写磁盘,那为何不在写redo日志的时候直接把数据也写到磁盘里？

1. 日志比数据先落入磁盘,MySQL崩溃 可以通过日志恢复数据.
2. 写日志时以追加形式写到末尾.而写数据时 需要计算数据位置.
   顺序写入的速度会比随机写入快,日志落盘后返回,比数据落盘后返回要快,对于客户端而言,响应时间会更短.

参数

innodb_flush_log_at_trx_commit:     设置redo_log_buffer的刷盘策略,默认每次提交事务都刷盘.
innodb_log_group_home_dir:          指定redo-log日志文件的保存路径,默认为./.
innodb_log_buffer_size:             指定redo_log_buffer缓冲区的大小,默认为16MB.
innodb_log_files_in_group:          指定redo日志的磁盘文件个数,默认为2个.
innodb_log_file_size:               指定redo日志的每个磁盘文件的大小限制,默认为48MB.

redo文件个数为什么是两个?
MySQL通过来回写这两个文件的形式记录Redo-log日志,用两个日志文件组成一个环形.

• write pos:指针用来表示当前Redo-log文件写到了哪个位置.
• check point:指针表示目前哪些Redo-log记录已经失效且可以被擦除(覆盖).

红色区域,代表是可以写入日志记录的可用空间(已经落盘的记录对应的redo-log记录),蓝色区域则表示日志落盘但数据还未落盘的记录.

当 write pos指针 追上check point指针 时,红色区域消失,代表Redo-log文件满了,当 MySQL执行写操作时就会被阻塞,
会触发 checkpoint 刷盘机制,将 redo-log 记录对应的事务数据,全部刷写到磁盘中的表数据文件后,阻塞的写事务才能继续执行.

checkpoint机制的系统参数

innodb_log_write_ahead_size:设置checkpoint刷盘机制每次落盘动作的大小,默认为8K.
设置必须为4k的整数倍,与 read-on-write 问题有关.
innodb_log_compressed_pages:是否对Redo日志开启页压缩机制,默认ON,这跟InnoDB的页压缩技术有关.
innodb_log_checksums:Redo日志完整性效验机制,默认开启.
必须开启,否则有可能刷写数据时,只刷一半,出现类似于“网络粘包”的问题.

为什么redo-log二阶段提交?

redo-log(prepare):在写入准备状态的redo记录时宕机,事务还未提交,不会影响一致性.
bin-log:在写bin记录时崩溃,重启后会根据redo记录中的事务ID,回滚前面已写入的数据.
redo-log(commit):在bin-log写入成功后,写redo(commit)记录时崩溃,bin-log中已经写入成功了,
所以从机也可以同步数据,因此重启时再次提交事务,写入一条redo(commit)记录即可.

7.3.bin-log二进制日志(变更日志)

bin-log是MySQL-Server级别的日志,所有存储引擎都能用的日志.
主要结合 redo-log 实现事务原则中的一致性,确保事务提交前后数据一致性.

记录所有对数据库表结构变更和表数据修改的操作,对于select、show这类读操作并不会记录.
写bin-log日志时,也会先写缓冲区,然后由后台线程去刷盘.

记录日志时,MySQL写入的是二进制数据,而并非字符数据,须通过MySQL提供的 mysqlbinlog 工具解析查看.

7.3.1.bin-log缓冲区

bin-log的缓冲区 与 redo-log、undo-log的缓冲区不同,
redo-log、undo-log日志缓冲区位于InnoDB创建的共享BufferPool中,
而 bin_log_buffer 位于每条线程中的.

原因:
MySQL 设计时要兼容所有引擎,直接将bin-log的缓冲区设计在线程的工作内存中,能够让所有存储引擎通用,
并且不同线程/事务之间,写的都是自己工作内存中的bin-log缓冲,因此并发执行时也不会冲突.

bin-log刷盘策略 通过 sync_binlog 参数控制:

• 0:同 innodb_flush_log_at_trx_commit 参数2.把日志记录放到内核缓冲区,刷写的时机交给 OS控制(性能最佳).
• 1:同 innodb_flush_log_at_trx_commit 参数1,每次提交事务都会刷盘(默认).

7.3.2.bin-log格式

bin-log的本地日志文件,采用的是追加写的模式,也就是一直向文件末尾写入新的日志记录,当一个日志文件写满后,会创建一个新的bin-log日志文件.
文件命名为 mysql-bin.00000x.

show binary logs; 命令查看已有的 bin-log日志文件.  

为什么有redo-log还要有bin-log?
先有bin-log,后有redo-log.
MySQL 官方引擎最初是 MyISAM,MyISAM 中并未设计记录变更操作的日志,记录变更操作由 MySQL-Server来通过Bin-log完成.
MyISAM不支持事务,所以 MySQL-Server设计的 Bin-log 无法用于灾难恢复,
因此 InnoDB在设计时,又重新设计出Redo-log日志,可以利用该日志实现 crash-safe 灾难恢复能力,确保任何事务提交后数据都不会丢失.

redo-log、Bin-log的区别:

1. 生效范围不同,Redo-log是InnoDB专用的,Bin-log是所有引擎通用.
2. 写入方式不同,Redo-log是用两个文件循环写,Bin-log是不断创建新文件追加写.
3. 文件格式不同,Redo-log中记录的都是变更后的数据,Bin-log会记录变更SQL语句.
4. 使用场景不同,Redo-log主要实现故障情况下的数据恢复,Bin-log则用于数据灾备、同步.

bin-log的本地文件中 存储的日志记录有 Statment、Row、Mixed 三种格式.

Statment存储模式

每一条会对数据库 产生变更 的SQL语句都会记录到bin-log中.
记录SQL时,还会记录一下SQL的上下文信息,如执行时间、事务ID、日志量…

优点:
不会产生太大的日志量,节约空间,恢复数据时因为数据量小,所以磁盘IO次数少,因此性能较好.同时做主备等高可用架构时,数据同步较小,比较节省带宽.
缺陷:
恢复数据、主从同步数据时,有时会出现数据不一致的情况,如SQL中使用了sysdate()、now().
sql insert into `user` values(11."name","男","111",sysdate());
当主从架构之间做数据同步时,sysdate()函数会获取机器的当前时间,主机和从机执行 SQL时间不同,因此会导致数据,在主机和从机的用户表中,时间会出现不一致.

Row存储模式

Row模式 不再记录每条造成变更的SQL语句,而是记录 具体哪一个分区中的、哪一个页中的、哪一行数据被修改,将其更改后的值记录到bin-log日志中.
优点:不会出现主从数据不一致的情况.
缺陷:表中有800W数据,现在对 ID<600W 的所有数据进行了修改操作,就意味着会有600W条记录写入bin-log日志,磁盘IO、网络带宽开销会很高.

Mixed存储模式

混合模式,Statment、Row的结合,Statment模式会导致数据出现不一致,Row模式数据量又会很大,
Mixed模式结合了两者的优劣势,对可以复制的 SQL 采用 Statment 模式记录,对无法复制的 SQL采用Row记录.
保留了Statment模式的数据量小,又具备Row模式的数据精准性.

Redis的 RDB、AOF 持久化模式,对应 MySQL的 Row,Statment模式,而Redis4.0引入了混合持久化机制,MySQL5.1版本引入混合日志模式.

7.3.3.参数

log_bin:            是否开启bin-log日志,默认ON开启.
log_bin_basename:   设置bin-log日志的存储目录和文件名前缀,默认为 .../data/binlog.xxxxxx.
log_bin_index:      设置bin-log索引文件的存储位置,因为本地有多个日志文件,需要用索引来确定目前该操作的日志文件.
binlog_format:      指定bin-log日志记录的存储方式,可选Statment、Row、Mixed.
max_binlog_size:    设置bin-log本地单个文件的最大限制,最多只能调整到1GB.
binlog_cache_size:  设置为每条线程的工作内存,分配多大的bin-log缓冲区.
sync_binlog:        控制bin-log日志的刷盘频率.
binlog_do_db:       设置后,只会收集指定库的bin-log日志,默认所有库都会记录.
其他

7.4.error-log错误日志

error-log MySQL由于非外在因素(断电、硬件损坏…)导致崩溃时,辅助线上排错的日志.
涵盖了MySQL-Server的启动、停止运行的时间,以及报错的诊断信息,也包括了错误、警告和提示等多个级别的日志详情.
默认开启,无法手动关闭！

在MySQL故障的情况下,打开error-log文件,然后搜索Error、Waiting级别的日志记录,然后参考诊断信息即可.

参数:
可以通过 log-error 参数,来手动指定保存的位置与文件名.
通过SHOW VARIABLES LIKE ‘log_error’;命令来查看错误日志位置.

7.5.slow-log慢查询日志

slow-log:系统响应缓慢时,用于定位问题SQL的日志,其中记录了查询时间较长的SQL.

当一条SQL执行的时间 超过规定阈值后,耗时的SQL就会被记录在慢查询日志中,当线下出现响应缓慢的问题时,
直接通过查看慢查询日志定位问题,定位到产生问题的SQL,
用 explain 工具去生成SQL的执行计划,根据生成的执行计划来判断为什么耗时长.

慢查询SQL的监控,MySQL默认关闭!!! 默认不会记录慢查询日志,为了后续线上问题排查,项目上线前一般开启！

参数:

    slow_query_log:          设置是否开启慢查询日志,默认OFF关闭.
    slow_query_log_file:    指定慢查询日志的存储目录及文件名.
    long_query_time:        指定查询SQL的阈值,单位s,默认10s
        set global long_query_time = 10;

慢查询日志在内存中没有缓冲区,每次记录慢查询SQL,都必须触发磁盘IO来完成.
阈值设的太小,容易使 MySQL性能下降；
如果太大,会导致无法检测到问题SQL.
先开启 general-log,观察后实际的业务情况后再决定阈值.

日志结构:

• Time 时间
• User@Host 用户与主机
• Query_time 查询时间
• Lock_time 等待锁时间
• Rows_sent 结果集行数
• Rows_examined 扫描行数
• use dbname 使用的库
• SET timestamp 时间戳
• 具体SQL语句

7.6.general-log查询日志

MySQL 会向 general-log 中写入所有收到的查询命令,如select、show等.

参数:

    general_log:        是否开启查询日志,默认OFF关闭.
    general_log_file:   指定查询日志的存储路径和文件名(默认在库的目录下,主机名+.log).

可以先开启普通查询日志,然后压测所有业务,紧接着再分析日志中SQL的平均耗时,再根据正常的SQL执行时间,设置一个偏大的慢查询阈值.

生产上线,一定关闭普通查询日志!!

7.7.relay-log中继日志

relay-log:搭建 MySQL 高可用热备架构时,用于同步数据的辅助日志.

relay log在单库中见不到.
relay-log中继日志仅存在主从架构中的’从机’上,仅仅只是作为主从同步数据的“中转站”.
主从架构中的 从机数据 基本上都是复制主机 bin-log 日志同步过来的,从主机复制过来的bin-log数据放在relay-log日志中.

主机的 增量数据 被复制到中继日志后,从机的线程不断从 relay-log 日志 中读取数据并 更新自身的数据,
relay-log的结构和bin-log完全相同,同样 存在一个 xx-relaybin.index 索引文件,
以及多个 xx-relaybin.00001、xx-relaybin.00002…数据文件.

八,内存

MySQL启动后内存结构 分为 MySQL工作组件、工作线程本地内存、MySQL共享内存、存储引擎缓冲区四大板块.

线程本地内存 为线程私有,其他为共有.

8.1.MySQL-Server工作组件

MySQL-Server 包含 管理服务&工具组件,连接池,SQL接口组件,解析器,优化器,缓冲区.

MySQL会在启动时,会先将这些工作组件初始化到内存中,方便后续处理客户端的操作.
连接池存储的实际上就是 MySQL内部的连接对象,包含了 客户端连接信息,(如客户端IP、登录的用户、所连接的DB…等信息),同时对象在内部会绑定一条工作线程.
可以理解成是一个线程池！MySQL复用连接的本质,是在复用线程,当新客户端连接时,首先会根据客户端信息为其创建连接对象,然后再复用连接池中的空闲线程.

8.2.工作线程本地内存

• thread_stack: 线程堆栈,暂时存储运行的SQL语句及运算数据,类似Java虚拟机栈.
• sort_buffer: 排序缓冲区,执行排序SQL时,存放排序后数据的临时缓冲区.
• join_buffer: 连接缓冲区,连表查询时,存放符合连表查询条件的 数据临时缓冲区.
• read_buffer: 顺序读缓冲区,MySQL磁盘IO一次读一页数据, 顺序IO的 数据临时缓冲区.
• read_rnd_buffer: 随机读缓冲区,当基于无序字段查询数据时,存放随机读到的数据.
• net_buffer: 网络连接缓冲区,存放当前线程对应的客户端连接信息.
• tmp_table: 内存临时表,当SQL中用到了临时表时,存放临时表的结构及数据.
• bulk_insert_buffer: MyISAM 批量插入缓冲区,批量insert时,存放临时数据的缓冲区.
• bin_log_buffer: bin-log日志缓冲区,日志篇提到过的,bin-log的缓冲区被设计在工作线程的本地内存中.

已上数据均为一条线程在执行SQL时产生的临时数据,其他线程用不到另一条线程的临时数据,放在线程本地内存中能够提升多线程并发执行的性能.

8.3.MySQL共享内存

• Key Buffer: MyISAM 表的索引缓冲区,提升 MyISAM表 的索引读写速度.
• Query Cache: 查询缓存区,缓冲SQL的查询结果,提升 热点SQL的数据检索效率.8.x移除.
• Thread Cache: 线程缓存区,存放工作线程运行期间需要被共享的临时数据.
• Table Cache: 表数据文件的 文件描述符(指向文件的指针)缓存,提升数据表的打开效率.
• Table Definition Cache: 表结构文件的 文件描述符 缓存,提升结构表的打开效率.

MySQL8.x为什么移除了查询缓存?

QueryCache查询缓存: 利用热点探测技术,对于频繁执行的查询SQL,直接将结果缓存在内存中,再次查询相同数据,无需走经过磁盘,直接从查询缓存中获取数据并返回.

查询缓存以 SQL语句 哈希值来作为Key,空格等字符都会影响hash结果,会被认为是不同语句.

1. 缓存命中率低: 几乎大部分SQL都无法从查询缓存中获得数据.
2. 占用内存高: 将大量查询结果放入到内存中,会占用至少几百MB的内存.
3. 增加查询步骤: 查询表之前会先查一次缓存,查询后会将结果放入缓存,额外开销.
4. 缓存维护成本不小,需要LRU算法淘汰缓存,每次更新、插入、删除数据时,都要清空缓存中对应的数据.
5. InnoDB引擎 构建出的缓冲区中,存在类似的功能,与查询缓存存在冲突.
6. 项目一般会使用缓存中间件做业务缓存.

8.4.InnoDB存储引擎缓冲区

缓冲池,用来提升数据库整体的读写性能.

• Data Page: 数据缓冲页,缓冲磁盘的表数据,将读写操作转移到内存进行.
• Index Page: 索引缓冲页,所有已创建的索引根节点,都会放入到内存,提升索引效率.
• Lock Space: 锁空间,主要是存放所有创建出的锁对象,参考MySQL锁机制实现原理.
• Dict Info: 数据字典,主要用来存储 InnoDB引擎 自带的系统表.
• redo_log_buffer: redo-log缓冲区,存放 写SQL执行时写入的redo记录.
• undo_log_buffer: undo-log缓冲区,存放 写SQL执行时写入的undo记录.
• Adaptivity Hash: 自适应哈希索引,为热点索引页创建相应的哈希索引.
• Insert Buffer: 写入缓冲区,对于insert的数据,会先放在此缓冲区,定期刷写磁盘.
• Lru List: 内存淘汰页列表,对于整个缓冲池的内存管理列表.
• Free List: 空闲内存列表,记录着目前未被使用的内存页.
• Flush List: 脏页内存列表,主要记录未落盘的数据.

8.5.InnoDB核心BufferPool

InnoDB引擎 几乎将所有操作都放在了内存中完成,与之相关的Buffer Pool会占用多大内存呢？
MySQL5.6-,默认42MB,MySQL5.6+,默认128MB.
通过指令查询:

-- 134217728
show global variables like "%innodb_buffer_pool_size%";

8.5.1.数据页DataPage

InnoDB引擎为了方便读取,将磁盘中的数据划分为一个个的「页」,每个页的默认大小为16KB.
以页作为内存和磁盘交互的基本单位,InnoDB的缓冲池也会以页作为单位.
意味着: 当InnoDB拿到申请的连续内存后,会按照16KB的尺寸将整块空间,划分成一个个的缓冲页.

MySQL启动时,划分出的缓冲页都属于空闲页,未使用的内存.运行过程中会将磁盘中的数据页,逐渐载入内存中.
磁盘中的表数据是以16KB作为单位划分,内存中的缓冲页也是16KB.
因此 发生一次磁盘IO读到的数据(读一页磁盘数据),会放入到一个缓冲页中存储,承载磁盘数据的缓冲页即为数据页.

优势:

• 读数据时: 在数据页中有,直接从内存中读取数据并返回,不再去磁盘检索数据.(具备「查询缓存」的功能)
• 写数据时: 先修改数据页的数据,修改后会标记相应的数据页,然后直接返回,由后台线程完成数据落盘工作.

内存充足的情况下,InnoDB会试图将磁盘中的所有表数据全部载入内存.
InnoDB会有一套完善的内存管理与淘汰机制,以此防止内存溢出风险.

8.5.2.索引缓冲页IndexPage

内存数据页没有命中数据时,走磁盘读取数据.
SQL可以命中索引时,会查找索引根节点所在位置.
MySQL启动时,将当前库中所有已存在的索引,其根节点放入到索引缓冲页中.
索引的根节点只有16KB,将索引的根节点载入内存后,对于需要走索引查询的SQL,直接以相应的索引根节点为起始,根据查找数据,避免了全盘查找索引根节点.

Index Page 用来存放载入的索引数据缓冲,称之为索引页.
随着运行时间的增长,也会将一些 非根节点的访问频率较高的索引页 载入内存中.

8.5.3.锁空间LockSpace

锁是基于事务实现,每个事务会生成自己的锁结构,锁空间就是专门用来存储锁结构的一块内存区域.
会存储一些并发事务的链表,例如死锁检测时需要的「事务等待链表、锁的信息链表」等.
锁空间一般都是有大小限制的,当锁空间内存不足时,就会导致行锁粗化成表锁,以此来减少锁结构的数量,释放一定程度上的内存,但并发冲突就会变高！

行锁的粒度粗化:
LockSpace区域满了后,就会将行锁粗化为表锁.做范围性写操作时,要加的行锁较多时,行锁开销会较大,会粗化成表锁.

8.5.4.数据字典DictInfo

辅助InnoDB运行用的数据字典.
InnoDB引擎中 ‘主要’ 存在 SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS 四张系统表,主要用来维护用户定义的所有表的各种信息.

• SYS_TABLES: 存储所有引擎为InnoDB的 表信息
  • ID 表的ID号
  • NAME 表名称
  • N_COLS 一张表的字段数量
  • TYPE 一张表所使用的存储引擎、编码格式、压缩算法、排序规则等
  • SPACE 一张表所位于的表空间
• SYS_COLUMNS: 存储所有用户定义的表字段信息
  • TABLE_ID: 字段所属表id
  • POS: 字段在表中第几列
  • NAME: 字段名称
  • MTYPE: 字段数据类型
  • PRTYPE: 字段精度值
  • LEN: 字段存储长度限制
• SYS_INDEXES: 存储所有InnoDB引擎表的索引信息.
  • TABLE_ID: 索引所属表.
  • ID: 索引ID号.
  • NAME: 索引名称.
  • N_FIELDS: 索引字段数量.
  • TYPE: 索引的类型,如唯一、联合、全文、主键索引等.
  • SPACE: 索引数据所位于的表空间位置.
  • PAGE_NO: 索引对应的B+Tree根节点位置.
• SYS_FIELDS: 存储所有索引的定义信息.
  • INDEX_ID: 索引字段属于哪个索引.
  • POS: 索引字段位于索引的第几列.
  • COL_NAME: 索引字段的名称.
    表位于.ibdata文件中,MySQL启动时开始加载,载入内存的 DictInfo 区域,利用 show语句查询表的结构信息时,在DictInfo中检索数据.

8.5.5.日志缓存区LogBuffer

存在两个日志缓冲区:

• undo_log_buffer
• redo_log_buffer
  作用主要是用来提升日志记录的写入速度,执行SQL时直接往磁盘写日志,效率太低,因此会先写缓冲区,再由后台线程去刷盘.

bin-log不在缓冲区: bin_log_buffer 位于工作线程本地内存中.

原因:
MySQL 设计时要兼容所有引擎,将bin-log的缓冲区设计在线程的工作内存中,能够让所有存储引擎通用,
并且不同线程/事务之间,都是写自己工作内存中的bin-log缓冲,因此并发执行时也不会冲突.

8.5.6.自适应哈希索引 Adaptivity Hash

建立索引时,只能选用一种数据结构来作为索引的底层结构:
哈希算法查找数据的效率非常高,在没有哈希冲突的情况下复杂度为O(1).但数据无序的,不方便’排序查询’.
B+Tree检索数据的效率,取决于树的高度.有序.

自适应哈希索引的技术: 在MySQL运行过程中,InnoDB引擎会对表上的索引做监控.
如果某些数据经常走索引查询,InnoDB就会为其建立一个哈希索引,以此来提升数据检索的效率,并且减少走B+Tree带来的开销.
由于哈希索引是运行过程中,InnoDB 根据B+Tree的索引 查询次数来建立的,被称为自适应哈希索引.

普通哈希索引在创建索引时将 结构声明为Hash结构,以索引字段的整表数据建立哈希.
自适应哈希索引是 根据缓冲池的 B+Tree 构造,只会基于热点数据构建,无需对整表都建立哈希索引,建立的速度非常快.

自适应哈希索引在 InnoDB中是默认开启的,该技术能让MySQL的整体性能翻倍.
通过手动调整 innodb_adaptive_hash_index 参数来控制关闭. ×

自适应哈希索引的使用情况,可以通过 show engine innodb status \G; 命令查看.
哈希索引由于自身特性的原因,仅可用于等值查询.无法支持排序、范围查询.

8.5.7.写入缓冲区InsertBuffer

5.5前写入缓冲只对insert操作生效,MySQL5.5之后对于insert、delete、update语句都可生效.
写入缓冲出现的原因,是为了解决内存没有目标修改数据时,避免直接操作磁盘.

当一条写入语句执行时,流程如下:

1. 变更的数据页是否在内存中.
2. 内存中有对应的数据页,直接变更缓冲区中的数据页,完成标记后则直接返回.
3. 内存中没有对应的数据页,将要变更的数据放入到 ‘写入缓冲区’ 中,然后返回.

不管内存中是否存在相应的数据页,InnoDB都不会走磁盘写数据,而是直接在内存中完成所有操作?
并非所有的写入动作,都可以在内存中完成: 当插入的数据字段不能具备唯一约束或唯一索引 时,需要磁盘操作.
插入前需要判断 字段值 是否唯一.(主键自增ID除外,自增序列由 MySQL-Server 维护,不会出现重复值) .

对于 存在唯一索引、或者表的主键不是自增ID时,插入语句执行过程:

• 先向聚簇索引中,插入一条相应的行记录(数据).
• 对于非聚簇索引,都插入一个新的索引键,并将值指向聚簇索引中插入的主键值.

对于次级索引的维护 会用到写入缓冲区,将要插入的索引键放在 写入缓冲区.

写入缓冲区 触发后台线程数据刷盘时机:

• 当SQL需要用到对应的索引键查询数据时.
• 当「写入缓冲区」内存空间不足时.
• 当距离上一次刷盘的时间,间隔达到一定程度(默认10s).
• MySQL-Server正在关闭时.

8.6.InnoDB缓冲区内存管理

InnoDB 在启动时,将连续的内存划分为一块块的缓冲页.

8.6.1.缓冲页控制块

8.6.2.寻找 空闲页/刷盘页

找寻空闲页/需要刷盘页 遍历?
Free链表与Flush链表:

• Free链表: 记录空闲缓冲页,为了使用时能更快地找到空闲缓冲页.

• Flush链表: 记录标记过的缓冲页,为了刷盘时能够更快地找到变更数据页.
  

• head: 指向空闲链表的第一个控制块指针.

• tail: 指向空闲链表的最后一个控制块指针.

• count: 记录空闲链表的节点数量.

8.6.3.内存数据页淘汰机制 LRU链表

对于可淘汰的数据页,也会被组合成一个LRU(least recently used 最近最少使用)淘汰链表.
空闲页和标记页不会纳入淘汰范围:

• 空闲页: 没有被使用,内存都是空白.
• 标记页: 被标记过的缓冲页中,存在数据还未落盘,淘汰数据会丢失.

末位淘汰机制

机制:

1. 当一条线程来读写数据时,命中了缓冲区中的某个数据页,那就直接将该页挪到LRU链表最前面.
2. 当未命中缓冲数据页时,需要走磁盘载入数据页,此时内存不够的情况下,会淘汰链表末尾的数据页.

缺陷:

• 利用局部性原理预读失效时,会导致数据页常驻缓冲区.
• 查询数据量过大时,会导致缓冲区中的热点数据全部被替换,导致缓冲池被“污染”.

预读失效问题

局部性原理预读数据 机制:
程序读取某块数据时,区域附近的数据也很有可能被读取,因此MySQL在读取数据时,默认会使用局部性思想预读数据.
读取一个数据时,默认会将其附近的16KB数据一次性全部载入内存.

预读失效: MySQL利用 局部性原理预读载入的数据,但在接下来时间内并未被使用.

InnoDB 对末位淘汰机制优化,将整个LRU算法划分为old、young两个区域组成.
LRU链表被划分为两个区域后,从磁盘中预读的数据页,只加入到old区域的头部,当数据页被真正访问时,将其插入young区的头部.
如果预读的这页在后续一直没有被访问,就从old区域移除,不会影响young区域中的热点数据.
young区 用来存储真正的热点数据页,old区 存放有可能成为热点数据页的“候选人”,当需要淘汰缓冲页时,会优先淘汰old区中的数据页.
young、old两个区域在LRU链表中的占比,默认为63:37.可以通过 innodb_old_blocks_pc 参数,调整old区在整个LRU链表中的占比.

缓冲池污染问题

缓冲池污染:
查询大量数据的过程可能会导致Buffer Pool里面的所有热点数据全部被换出.
有线程访问原本热点数据时,由于缓冲区中的数据页被换出,因此就会产生大量的磁盘IO.
InnoDB为 引入了一种新的技术:名为young区晋升限制.

一个数据页想从old区晋升到young区,必须要在old区中存活一定时间,默认为1s/1000ms,通过参数innodb_old_blocks_time调整.
进入young区,就必须达成两个条件:

1. 在old区中停留的时间超过了1000ms.
2. 在old区中,1000ms后有线程再次访问了这个数据页.

九.存储引擎

MySQL是一款支持拔插式引擎的数据库.
存储引擎: MyISAM、InnoDB、Merge、Memory(HEAP)、BDB(BerkeleyDB)、Example、Federated、Archive、CSV、Blackhole…

9.1.常用命令

show create table table_name; --查看表的存储引擎.
create table .... ENGINE=InnoDB; --创建表时指定存储引擎.
alter table table_name ENGINE=MyISAM; --修改表的存储引擎.
mysql_convert_table_format --user=user_name --password=user_pwd --engine=MyISAM database_name; --批量修改库内表的存储引擎

9.2.MyISAM与InnoDB对比

存储方式

• myisam 表结构文件,行数据文件,表索引文件.
• innodb 表结构文件,行数据与索引数据文件.

索引支持

• myisam 表数据与索引分开存储,仅支持非聚簇索引.
• innodb 支持聚簇索引.但使用非聚簇索引未触发覆盖索引时,需要回表查询.

事务支持

• myisam 不支持
• innodb 借助undo-log实现事务机制

故障恢复

• myisam 不支持数据故障恢复
• innodb redo-log 事务提交后,灾难情况都支持故障恢复.

锁粒度行锁支持

• myisam 不支持聚簇索引,无法实现行锁.(多索引时,只能锁住单索引内的数据,所以不支持行锁)
• innodb 支持聚簇索引,并发时,只需要锁住聚簇索引就可以.

并发性能

• myisam 仅支持表锁.写操作加排它锁,不支持读写共存.性能比innodb低.
• innodb 支持行锁.并推出了MVCC多版本并发控制技术,对于读-写共存的场景支持并发执行.

内存利用

• innodb 利用内存+异步刷盘,提前响应.

存储方式

MyISAM引擎的表三个磁盘文件:

• .frm: 表的结构信息.
• .MYD: 表的行数据.
• .MYI: 表的索引数据.
  InnoDB引擎的表有两个文件:
• .frm: 表的结构信息.
• .ibd: 表的行数据和索引数据.

索引支持

MyISAM引擎 将表分为frm,MYD,MYI三个文件放在磁盘存储,表数据和索引数据分别放在MYD,MYI文件中,所以注定了MyISAM引擎只支持非聚簇索引.
InnoDB引擎的表数据、索引数据都放在 ibd文件中存储,因此InnoDB支持聚簇索引.

聚簇索引: 物理逻辑连续; 连续:索引数据和表数据.
非聚簇: 逻辑连续,物理不连续;

不支持聚簇索引无论走任何索引,只需要一遍查询即可获得数据.
InnoDB引擎的表中,不走聚簇索引(或覆盖索引)查询数据,都需要经过两遍(回表)查询才能获得数据. 但不意味着MyISAM引擎查数据’都’比InnoDB快.

事务支持

MyISAM并未设计类似的技术,在启动时不会在内存中构建undo_log_buffer缓冲区,磁盘中也没有相应的日志文件,因此MyISAM并不支持事务机制.
InnoDB存储引擎的表,可以借助undo-log日志实现事务机制,支持多条SQL组成一个事务,可以保证发生异常的情况下,组成这个事务的SQL到底回滚还是提交.

故障恢复

MyISAM并没有InnoDB引擎可靠,在InnoDB中只要事务提交,就能确保数据永远不丢失,但MyISAM不行.

InnoDB引擎由于redo-log日志的存在,因此只要事务提交,发生各种灾难情况都可以用redo-log日志来恢复数据.
MyISAM引擎同并不支持数据的故障恢复,当一条SQL将数据写入到了缓冲区后,SQL还未被写到bin-log日志,此时机器宕机,重启之后由于数据在宕机前未落盘,数据丢失无法找回.

锁粒度行锁支持

MyISAM引擎不支持聚簇索引,无法实现行锁,出现多条线程同时读写数据时,只能锁住整张表.
InnoDB支持聚簇索引,每个索引最终都会指向聚簇索引中的索引键,出现并发事务时,InnoDB只需要锁住聚簇索引的数据即可,因此并发性能更高.

MyISAM引擎的表存在多个索引,索引文件存在多个索引树,且是平级关系.
假设MyISAM要实现行锁,当要对一行数据加锁时,可以锁定一棵树中某一个数据,但无法锁定其他树的行数据.
基于不同索引查询数据时,可能会导致一行数据上加多个锁,导致多条线程同时操作一个数据.并发执行造成脏读、幻读、不可重复读系列问题.所以无法实现行锁.

InnoDB引擎支持聚簇索引,索引有主次之分,所有的次级索引的索引值存储聚簇索引的索引键,当对一行数据加锁时,只需要锁定聚簇索引的数据即可.

并发性能

MyISAM仅支持表锁,InnoDB同时支持表锁、行锁.
锁的粒度越小,并发冲突的概率也就越低,因此并发支撑就越高.InnoDB引擎的并发支远超MyISAM.

InnoDB引擎还基于隐藏字段+undo-log日志版本链+事务快照,推出了MVCC多版本并发控制技术,对于读-写共存的场景支持并发执行.
MyISAM只支持表锁,对于写操作需要加排它锁.不支持读写共存.

内存利用程度

InnoDB引擎,当内存足够大,会将磁盘中的所有数据,全部载入内存,然后所有客户端的读写请求,基本上无需再走磁盘来完成,都采用异步IO的方式完成.
即先写内存+后台线程刷写的方式执行,后台线程的刷盘动作,对客户端而言不会有任何感知,在写完内存之后就会直接向客户端返回.

• InnoDB缓冲池的数据页,可以当做数据缓存使用,如果数据页中有的数据,可以直接从内存中读取返回.
  MyISAM 完全依赖于MySQL Server的「查询缓存」做到这个功能.
• InnoDB引擎设计了redo-log日志,可以用于故障恢复.
  MyISAM 企图通过MySQL Server的bin-log日志实现这个功能.
• InnoDB创造了一个写入缓冲区,用于减少写操作执行时磁盘IO.
  MyISAM引擎 依赖于MySQL Server在工作线程中设计的 bulk_insert_buffer 批量插入缓冲区来实现类似的功能.

9.3.MyISAM引擎的优点

统计表总数的优化

select count(*) from `table_name`;

MyISAM引擎中会记录表的行数,当执行count()时,MyISAM引擎的表,可以直接获取之前统计的值并返回.
InnoDB引擎中是不具备,会触发全表扫描.
仅适用于统计全表数据量,不支持where条件.

删除数据/表的优化

delete from `table_name`;

MyISAM会直接重新创建表数据文件.对于delete过的数据不会立即删除,先隐藏,后续定时删除或手动删除.
手动删除:

optimize table `table_name`;

InnoDB一行行删除数据,因此对于清空表数据的操作,MyISAM比InnoDB快上无数倍.

CRUD速度更快

InnoDB的表必须有聚簇索引.在基于非聚簇索引查找数据,未触发覆盖索引,需要经过一次回表才能得到数据.插入数据、修改数据时,都需要维护聚簇索引和非聚簇索引之间的关系.

MyISAM引擎中,索引之间都是独立的,索引中存储的是直接指向行数据的地址,走任何索引,都仅需一次即可获得数据,无需做回表查询.
写数据时,也不需要维护不同索引之间的关系.因此MyISAM在’理论’上,读写数据的效率会高于InnoDB引擎.

对比单个客户端连接的读写性能,MyISAM远超于InnoDB引擎.InnoDB需要维护聚簇索引.
MyISAM每个索引都是独立的,插入表数据直接追加在表数据文件的末尾,修改数据也不需要维护其他索引和聚簇索引的关系.
MyISAM引擎仅支持表锁:
当连接数的增加,工作线程会不断增加,CPU使用核数也会不断增加,而InnoDB的性能会逐步上升,但MyISAM引擎基本上没有太大变化,一直很低.

压缩

数据量过大,磁盘空间不足.
MyISAM引擎 通过 myisampack 工具对数据表进行压缩,至少数据缩小一半,但压缩后的数据只可读！

5.7版本中,特性移植到了InnoDB引擎(不用压缩!使用分库分表):

• innodb_compression_level: 压缩级别1~9,越高压缩效果越好,但压缩速度也越慢.
• innodb_compression_failure_threshold_pct: 当压缩失败的数据页超出该比例时,会加入数据填充来减小失败率,为0表示禁止填充.
• innodb_compression_pad_pct_max: 一个数据页中最大允许填充多少比例的空白数据.
• innodb_log_compressed_pages: 控制是否对redo-log日志的数据也开启压缩机制.
• innodb_cmp_per_index_enabled: 是否对索引文件开启压缩机制.

MyISAM应用场景

MyISAM引擎 适用于不需要事务、并发冲突低、读操作多的表,例如文章表、帖子表、字典表…

MySQL利用主从架构,实现读写分离时的场景,
主库会承载insert/update/delete请求,从库承载select请求.
读写分离的场景中,从库的表结构可以改为MyISAM引擎,基于MyISAM的索引查询数据,不需要经过回表查询,速度更快！
库上只会有读请求,不会存在任何外部的写请求,所以支持并发读取.
从库后台线程来写入数据时,只会有少数几条线程执行写入工作,冲突不会太大,不会由于表锁引起大量阻塞.

十.存储过程与触发器

完成特定功能的SQL语句集合.
将常用且复杂的SQL语句预先写好存储起来,经过MySQL编译解析、执行优化后存储在数据库中.当需要使用存储过程,根据名称调用即可.
优点:

• 复用性: 可以在程序中被反复调用,不必重新编写.库表结构发生更改时,只需修改存储过程,无需修改业务代码.
• 灵活性: 存储过程可以用流程控制语句编写,支持定义变量,可以完成复杂条件查询和运算.
• 省资源: 存储过程保存在MySQL中,当客户端调用存储过程时,只需要通过网络传送存储过程的调用语句和参数,从而可降低网络负载.
• 高性能: 存储过程执行多次后,会将SQL语句编译成机器码驻留在 线程缓冲区.以后调用只需要从缓冲区中执行机器码,无需再次编译执行,提高了系统的效率和性能.
• 安全性: 不同的存储过程,可根据权限设置执行的用户.存储过程编写好之后,对于客户端而言是黑盒的,因此减小了SQL被暴露的风险.
  缺点:
  CPU开销大,内存占用高,维护性差.

应用场景:

• 插入测试数据;
• 对数据做批处理;
• 一条SQL无法完成的、需要应用程序介入处理的业务,SQL较长时;

10.1.语法

定义:

    DELIMITER $
    -- 创建的语法:指定名称、入参、出参
    CREATE 
        PROCEDURE 存储过程名称(出/入类型 参数名1 参数类型1.....)
        [...说明... ]
    -- 表示开始编写存储过程体
    BEGIN
        -- 具体组成存储过程的SQL语句....
    -- 存储过程结束
    END $
    DELIMITER ;

DELIMITER $,修改结束标识,表示以$作为结束标识.(防止与SQL结束标识’;‘冲突,过程结束修改回’;')
CREATE PROCEDURE… 创建存储过程
BEGIN… END 存储过程SQL语句集合

SQL的存储过程 入参出参 ,依赖于IN、OUT、INOUT三个关键字来区分:

• 没有入参也没有出参,代表无参无返回类型.
• 仅定义了带有 IN 类型的参数,表示有参无返回类型.
• 仅定义了带有 OUT 类型的参数,表示无参有返回类型.
• 同时定义了带有IN,OUT,INOUT类型的参数,表示有参有返回类型.

参数名必须在参数类型的前面,参数类型需要定义长度,否则低版本的MySQL会出现不兼容的问题.
参数名尽量与字段名不同!!!

说明:(通常使用默认值)

1. LANGUAGE SQL
   存储过程中的过程体是否由SQL语句组成.
2. [NOT] DETERMINISTIC
   存储过程的返回值是否为固定的,DETERMINISTIC表示为固定的,默认为非固定NOT DETERMINISTIC.
3. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
   过程体使用SQL语句的限制
   • CONTAINS SQL 表示当前存储过程包含SQL,但不包含读写数据的SQL语句
   • NO SQL 表示当前存储过程中不包含任何SQL语句
   • READS SQL DATA 表示当前存储过程中包含读数据的SQL语句
   • MODIFIES SQL DATA 表示当前存储过程中包含写数据的SQL语句
4. SQL SECURITY { DEFINER | INVOKER
   哪些用户可以调用当前创建的存储过程
   • DEFINER 表示只有定义当前存储过程的用户才能调用
   • INVOKER 表示任何具备访问权限的用户都能调用
5. COMMENT ‘…’
   注释信息,可描述当前创建的存储过程

调用:
存储过程都是通过CALL命令来调用;

示例:

• 无参

      ...PROCEDURE get_all_userInfo()...
      call get_all_userInfo();
  

• 入参

      ...PROCEDURE get_user_register_time(IN uname varchar(255))...
      call get_user_register_time("yj");
  

• 入参出参

      ...PROCEDURE get_user_salary(IN uid int,OUT userSalary decimal(10.2))...
      call get_user_salary(1.@userSalary);
      select @userSalary;
  

• 使用变量

     ...PROCEDURE get_user_id(INOUT parameters varchar(255))...
     set @parameters = "yj";
     call get_user_id(@parameters);
     select @parameters;
  

变量

• 系统变量
  一般系统变量来自于MySQL编译期 和 my.ini配置文件中.
  MySQL的系统变量也会分为两类,一类是全局级变量(global),一类是会话级变量.

      -- 查看某个系统变量
      select @@xxx;
      -- 修改某个系统变量
      set @@xxx = "xxx";
  

• 用户变量
  用户自定义的变量

      set @变量名称 = 变量值;
      select @变量名称;
  

• 局部变量
  局部变量只对当前存储过程体有效
  局部变量必须要写在BEGIN、END之间.定义:
  DECLARE 变量名称 数据类型 default 默认值;

      -- 赋值方式一
      SET message = 变量值;
      SET message := 变量值;
      -- 赋值方式二
      select 字段名或函数 into message from 表名;
  

流程控制

IF判断与CASE分支

    IF语法:   
        IF 条件判断 THEN
            -- 分支操作.....
        ELSEIF 条件判断 THEN
            -- 分支操作.....
        ELSE
            -- 分支操作.....
        END IF
    
    CASE语法:
        CASE 变量
            WHEN 值1 THEN
                -- 分支操作1....
            WHEN 值2 THEN
                -- 分支操作2....
           .....
            ELSE
                -- 分支操作n....
        END CASE;
        或
        CASE
            WHEN 条件判断1 THEN
                -- 分支操作1....
            WHEN 条件判断2 THEN
                -- 分支操作2....
           .....
            ELSE
                -- 分支操作n....
        END CASE;

循环

存储过程中支持LOOP、WHILE、REPEAT三类循环

    LOOP语法:
        循环名称:LOOP
            -- 循环体....
        END LOOP 循环名称; ## 可以基于循环名称来跳出循环

    WHILE语法: 循环名称可以不写
        【循环名称】:WHILE 循环条件 DO
            -- 循环体....
        END WHILE 【循环名称】;  

    REPEAT语法: REPEAT 有专门控制循环结束的语法,当 UNTIL关键字后的条件为真,循环终止.
        【循环名称】:REPEAT
            -- 循环体....
        UNTIL 结束循环的条件判断 
        END REPEAT 【循环名称】;

跳转

LEAVE 结束循环体
ITERATE 跳出本次循环

游标

游标 对一个结果集中的数据按条处理.查询结果集合使用游标可以对该集合中的数据逐条处理.
使用游标步骤:

    -- 声明(创建)游标
    DECLARE 游标名称 CURSOR FOR select...;
    -- 打开游标
    OPEN 游标名称;
    -- 使用游标
    FETCH 游标名称 INTO 变量名称;
    -- 关闭游标
    CLOSE 游标名称; 

10.2.使用存储过程

MyBatis作 为操作数据库的ORM框架 调用存储过程:  

    <mapper>
        <parameterMap type="根据存储过程决定" id="命名">
            <parameter property="存储过程参数1" jdbcType="数据类型" mode="IN"/>
            <parameter property="存储过程参数2" jdbcType="数据类型" mode="IN"/>
            <parameter property="存储过程参数3" jdbcType="数据类型" mode="OUT"/>
        </parameterMap>
        <insert id="Dao接口名" parameterMap="命名" statementType="CALLABLE">
            {call 存储过程名(?, ?, ?)}
        </insert >
    </mapper>

10.3.管理存储过程

查看修改删除

    SHOW PROCEDURE STATUS;      查看当前数据库中的所有存储过程.
    SHOW PROCEDURE STATUS WHERE db = '库名' AND NAME = '过程名';     查看指定库中的某个存储过程.
    SHOW CREATE PROCEDURE 存储过程名;        查看某个存储过程的源码.
    ALTER PROCEDURE 存储过程名称....     修改某个存储过程的特性.
    DROP PROCEDURE 存储过程名;       删除某个存储过程.
    select * from 表名.Routines where routine_type = "PROCEDURE"; 查看某张表的所有存储过程
    select * from 表名.Routines where routine_name = "过程名" AND routine_type = "PROCEDURE"; 查看某张表的某个存储过程

10.4.触发器

5.0.2版本后支持触发器,触发器的触发条件是以事件为单位.

创建触发器:

    DELIMITER $
    CREATE TRIGGER 触发器名称
        {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名
    FOR EACH ROW
        BEGIN
            -- 触发器的逻辑(代码块);  
        END $
    DELIMITER ;

每一个触发器 共有 插入、修改以及删除 三种触发事件可选,可以选择将触发器放在事件开始前或结束后执行.触发器创建后附着在一张表上.

关键字: NEW,OLD
NEW表示新数据,OLD表示老数据.

• insert插入事件:NEW表示当前插入的这条行数据.
• update修改事件:NEW表示修改后的数据,OLD表示修改前的数据.
• delete删除事件:OLD表示删除前的老数据.

示例:

    DELIMITER $    
    CREATE TRIGGER users_update_before
        BEFORE UPDATE ON users
    FOR EACH ROW
        BEGIN
            DECLARE new_name varchar(255);
            DECLARE old_name varchar(255);
            -- 通过 NEW 关键字拿到修改后的新数据
            SET new_name := NEW.user_name;
            -- 可以通过 OLD 关键字拿到修改前的数据
            SET old_name := OLD.user_name;
        END $
    DELIMITER ;

触发器管理:

    SHOW TRIGGERS;                          查看当前数据库中定义的所有触发器.
    SHOW CREATE TRIGGER 触发器名称;          查看当前库中指定名称的触发器.
    SELECT * FROM information_schema.TRIGGERS;      查看MySQL所有已定义的触发器.
    DROP TRIGGER IF EXISTS 触发器名称;       删除某个指定的触发器.

十一,命令

附录./mysql_command.md

十二,调优

架构优化、前端调优、中间件调优、网关调优、容器调优、JVM调优、接口调优、服务器调优、数据库调优
优化可以分为三类:

1. 结构/架构优化
   优化应用系统整体架构 做到性能提升的目的.
   如:读写分离、集群热备、分布式架构、引入缓存/消息/搜索中间件、分库分表、中台架构(大数据中台、基础设施中台)等.
2. 配置/参数优化
   调整应用系统中 各层面的配置文件、启动参数达到优化性能的目标.
   如:JVM、服务器、数据库、操作系统、中间件、容器、网关参数调整等.
3. 代码/操作优化
   开发者编写程序时,从代码、操作方面进行调节,达到效率更高的初衷.
   如:代码中使用更优秀的算法思想/设计模式、SQL优化、对中间件的操作优化等.

Mysql性能优化方向

1. 客户端与连接层的优化 调整 客户端DB连接池参数 和 DB连接层参数.
2. MySQL结构的优化 合理的设计库表结构,表中字段根据业务选择合适的数据类型、索引.
3. MySQL参数优化 调整参数的默认值,根据业务将各类参数调整到合适的大小.
4. 整体架构优化 引入中间件减轻数据库压力,优化MySQL架构提高可用性.
5. 编码层优化 根据库表结构、索引结构优化业务SQL语句,提高索引命中率.

优化性能收益排序为 4>2>5>3>1

12.1.Mysql连接层优化策略

一个用户请求最终会在 Java程序 中分配一条线程处理,最终会变成一条SQL发往MySQL执行,
Java程序、MySQL-Server之间是通过 建立网络连接 的方式进行通信,连接在MySQL中被称为数据库连接,本质上在MySQL内部也是一条条工作线程负责执行SQL语句.

数据库连接数越大,内部创建出的工作线程会越多,线程越多代表需要的CPU配置得更高,但服务器的CPU核心有限,
为了其他线程能够正常执行,CPU以 时间片调度 的模式工作,不同核心在不同线程间反复切换执行,
由于线程数远超核心数,会导致线程上下文切换的开销,远大于线程执行的开销.

连接池又会分为 客户端连接池、服务端连接池:

• 客户端连接池是指 Java维护的数据库连接对象,如 C3P0、DBCP、Druid、HikariCP… 等连接池.
• 服务端连接池是指 MySQL-Server 的连接层中维护的连接池,用来实现线程复用. 无需关心.

MySQL 实例一般情况下只为单个项目提供服务,当把应用程序的连接数做了限制,也就限制了服务端的连接数.

为何不将 MySQL最大连接数 和 客户端连接池最大连接数 保持一致？
可能数据库实例不仅为单个项目提供服务,通过终端工具远程连接MySQL可能导致MySQL连接数爆满,造成终端连接MySQL.

12.1.1.单库情况下连接池推荐配置 – 最大连接数,等待队列的容量

客户端的连接池大小: PostgresSQL 提供的计算公式:

最大连接数 = (CPU核心数 * 2) + 有效磁盘数

有效磁盘数 指 SSD固态硬盘. 硬盘是SSD类型,发生磁盘IO基本上不会产生阻塞.
SSD相较于机械硬盘,没有磁片,无需经过旋转磁面的方式寻址,所以SSD硬盘的情况下可以+1.

C3P0、DBCP、Druid、HikariCP… 等连接池本质上是线程池,对于线程池,想要处理足够高的并发,应该再配备一个较大的等待队列,
当池中无可用连接时,其他的用户请求/待执行的SQL语句加入队列中阻塞等待.

大事务会影响其他正常的SQL,最好再单独开辟连接池,为大事务或执行耗时较长的SQL提供服务.

MySQL最大连接数

    set max_connections = n;  

12.1.2.单库情况下偶发高峰业务连接数配置 – 常驻连接数,空闲连接存活时间

偶发高峰类业务 常驻线程数不适合太多.
并发来临时会导致创建大量连接,并发过后一直保持数据库连接会导致资源被占用,
可以将最大连接数按推荐公式配置,常驻连接数则可以配成 CPU核数+1.同时缩短连接的存活时间,及时释放空闲的数据库连接,以此确保资源的合理分配.

12.1.3.分库分表下连接数配置

对于读写分离、双主双写、分库分表的情况下,多个MySQL节点 意味着拥有多台服务器的硬件资源,需要根据每个节点的硬件配置 规划合理的连接数.

12.2.MySql结构优化方案

表结构、字段结构以及索引结构

12.2.1.表结构优化

1. 字段数量不要太多
   InnoDB 引擎 将数据操作放到内存中完成,当一张表的字段数量越多,能载入内存的数据页会越少,
   当操作时数据不在内存,需要磁盘中读取数据,会很大程度上影响MySQL性能.
2. 经常做连表查询的字段,可以适当的表中冗余.
3. 表中必须要有主键,主键最好是顺序递增的数值类型.
4. 对于实时性要求不高的数据建立中间表.
   统计数据时,通常情况下会基于多表做联查,以此确保统计所需的数据.
   若对实时性的要求没那么高,可以在库中建立相应的中间表,每日定期更新中间表的数据,达到减小连表查询的开销,同时也能进一步提升查询速度.
5. 根据业务特性为每张不同的表选择合适的存储引擎.
   存储引擎这要在 InnoDB、MyISAM 之间做抉择.
   对于一些经常查询,很少发生变更的表,就可以选择 MyISAM 引擎,比如字典表、标签表、权限表…,读远大于写的表中,MyISAM性能表现会更佳.
   其他的表使用默认的InnoDB引擎.

12.2.2.字段结构优化

选择合适的数据类型

原则:

1. 在保证足够使用的范围内,选择最小数据类型(占用更少的磁盘、内存和CPU缓存,处理速度也会更快)
2. 避免索引字段值为 NULL,定义字段时应尽可能使用 NOT NULL 关键字,(字段空值过多会影响索引性能)
3. 尽量使用 最简单的类型 代替 复杂的类型(如IP的存储可以使用int而并非varchar,因为简单的数据类型,操作时通常需要的CPU资源更少)

例如:

1. 对于会限制长度的字段,不要无脑用varchar,使用char类型更好.
2. 对于一些不会拥有太多数据的表,主键 ID 的类型可以从int换成 tinyint、smallint、mediumint.
3. 对于日期字段,不要使用字符串类型,应该选择 datetime、timestamp 一般情况下最好为后者.
4. 对于固定值的字段,如 性别、状态、省份、国籍 等字段,可以选择使用数值型代替字符串,如果必须使用字符串类型,最好使用 enum枚举类型 代替 varchar类型.

12.2.3.索引结构优化

根据业务创建更合适的索引

1. 索引字段的组成尽量选择多个,一个表中需要建立多个索引,适当根据业务将多个单列索引组合成联合索引
   (可以节省磁盘空间,还可以充分使用索引覆盖的方式查询数据,能够在一定程度上提升数据库的整体性能)
2. 对于值较长的字段尽量建立前缀索引(选用字段值的前N个字符创建索引)
   索引字段值越小,单个 B+Tree的节点中能存储的索引键会越多(树的度),一个节点存下的索引键越多,索引树越矮,查询性能越高
3. 合理的索引类型
   经常做模糊查询的字段,可以建立全文索引来代替普通索引.
   基于普通索引做 like 查询会导致索引失效,采用全文索引的方式做模糊查询效率会更高更快,并且全文索引的功能更为强大.
4. 合理的索引结构的
   不会做范围查询的字段上建立索引时,可以选用 hash结构 代替 B+Tree结构( Hash结构的索引是所有数据结构中最快的,散列度足够的情况下,复杂度仅为O(1) ).

详情参考 4.5

12.3.Mysql参数优化

调优参数可以在启动之后通过 set global @@xxx = xxx的方式调整
但最好还是直接修改 my.ini/my.conf 配置文件. 让参数在每次启动时都生效,避免了每次重启手动调整.

在MySQL中，InnoDB引擎的各种缓冲区和工作线程各自的缓冲区默认大小:

• 缓冲池(buffer pool):默认大小为128MB.
• 日志缓冲区(log buffer):默认大小为8MB.
• 排序缓冲区(sort buffer):默认大小为256KB.
• 读取缓存(read buffer):默认大小为128KB.
• 写入缓存(write buffer):默认大小为128KB.
• 工作线程池(thread pool):默认大小为8.

针对20G内存的服务器，可以考虑对这些缓冲区进行如下设置:

• 缓冲池(buffer pool):可以将其设置为总内存的70%-80%，即14GB-16GB左右.
• 日志缓冲区(log buffer):可以适当调整为32MB-64MB.
• 排序缓冲区(sort buffer):可以设置为512KB-1MB.
• 读取缓存(read buffer):可以设置为1MB-2MB.
• 写入缓存(write buffer):可以设置为1MB-2MB.

12.3.1.调整InnoDB缓冲区

缓冲区分配 最佳比例应该控制在服务器的内存70~75%左右,innodb_buffer_pool_size = nM

为InnoDB的缓冲区分配了足够的大小后,运行期间InnoDB会根据实际情况,自动调整内部各区域中的数据,如热点数据页、自适应哈希索引…

当InnoDB缓冲区空间大于1GB时,InnoDB自动将缓冲区划分为多个实例空间,多线程并发执行时,可以减少并发冲突.
MySQL官方建议每个缓冲区实例必须大于1GB.

假设缓冲区共计拥有40GB内存,将缓冲区实例设置为 innodb_buffer_pool_instances = 20 个比较合适.

12.3.2.调整工作线程缓冲区

调大 sort_buffer、 read_buffer、 join_buffer.均属于线程私有区域.

sort_buffer_size 排序缓冲区大小,影响group by、order by…等排序操作.
read_buffer_size 读取缓冲区大小,影响select…查询操作的性能.
join_buffer_size 联查缓冲区大小,影响join多表联查的性能.

根据机器内存 nGB 设置为 nMB~2nMB.控制在64MB以下.

对于排序查询的操作可以调整参数: max_length_for_sort_data

• 如果排序字段值的最大长度小于 max_length_for_sort_data,会将所有要排序的字段值载入内存排序;
• 如果排序字段值的最大长度大于 max_length_for_sort_data,会分批一批的加载排序字段值进内存,边加载边排序;
  可以适当调大该参数的值,最好交给Mysql自己控制!

12.3.3.调整临时表空间

调整 tmp_table_size、 max_heap_table_size 参数.限制临时表可用的内存空间.

当创建的临时表空间占用超过 tmp_table_size 时,会将其他新创建的临时表转到磁盘中创建,
十分违背临时表的设计初衷,创建临时表的目的就是用来加快查询速度,把临时表放到磁盘中去反而多了一步开销.

根据 show global status like ‘created_tmp%’; 的统计信息来决定.

Created_tmp_disk_tables / Created_tmp_tables * 100% = 120%.

需要反复重启MySQL以及压测,因此比较费时间.

12.3.4.调整空闲线程存活时间

查看数据库连接的峰值:

show global status like 'Max_used_connections';

空闲连接的超时时间 wait_timeout、 interactive_timeout 两个参数必须一同设置,否则不生效.
MySQL内部默认为 8h,连接断开后,默认将工作线程缓存八小时后再销毁.
可以手动调整成 30min~1h 左右,让无用的连接能及时释放,减少资源的占用.

12.4.架构优化

架构优化收益最大.

12.4.1.引入缓存中间件解决读压力

Redis

12.4.2.引入消息中间件解决写压力

引入MQ消息中间件做削峰填谷.

下单业务通常由「提交订单、支付费用、扣减库存、修改订单状态、添加发票记录、添加附赠服务…」这一系列操作组成,
其中「提交订单、支付费用」属于核心业务,当用户下单时可以发往MySQL执行落库操作,
对于「扣减库存、修改订单状态、添加发票记录、添加附赠服务…」操作则可以发往MQ,当写入MQ成功,则直接返回客户端下单成功,
后续再由消费线程去按需拉取后执行.

12.4.3.MySQL自身架构优化

MySQL的架构优化方案,分别是指三种:主从架构、双主架构、分库分表架构.

主从复制

适用读大于写的业务.
在主从架构的模式下,实现读写分离.

读操作并不会变更数据,对于读请求可以分发到从节点上处理.
对于会引发数据变更的写请求,则分发到主节点处理,这样从而能够进一步提升MySQL的整体性能.
主节点的数据变更后,从节点也会基于bin-log日志去同步数据,但是存在延迟.

双主双写热备

基本不用!

写大于读的项目业务,双主双写(双主热备)方案才是最佳选择.
两个节点互为主从,两者之间相互同步数据,同时都具备处理读/写请求的能力,当出现数据库的读/写操作时,可以将请求抛给其中任意一个节点处理.

一定要手动设置自增步长和起始值确保主键的唯一性

两个节点互为主从可以实现双主双写,三个节点,四个节点呢？
可以不过没必要,当需要上三主、四主…的项目,直接就做分库分表更实在,多主模式存在弊端:存储容量的上限+木桶效应.
多主模式中的每个节点都会存储完整的数据.因为木桶效应,扩充容量需要所有节点扩充.所以基本不用.

分库分表

一种分布式存储的思想

垂直分库:根据业务属性的不同,创建不同的数据库,由不同的业务连接不同的数据库,各自之间数据分开存储,节点之间数据不同步.
提高了数据库的整体吞吐量和并发能力,也不存在之前的存储容量木桶问题.

详情 十五,分库分表

12.5.SQL优化

优化建立在不违背业务需求的情况下
减小查询的数据量、提升SQL的索引命中率

12.5.1.语句优化

查询尽量不使用*

分析成本变高:SQL在执行前都会经过分析器解析,当使用*时,需要先解析出当前要查询的表上 * 代表的字段,会额外增加解析成本.
网络开销变大:返回数据时需要经过网络传输.
内存占用变高:查询一条数据时都会将其结果集放入到BufferPool的数据缓冲页中.

联表数量

确定ON或者USING子句中是否有索引.
确保GROUP BY和ORDER BY只有一个表中的列,MySQL才有可能使用索引.

多表查询/子查询 以小驱大

用关联查询替代.
优化GROUP BY和DISTINCT.
这两种查询据可以使用索引来优化,是有效的优化方法关联查询中,使用标识列分组的效率更高.
如果不需要ORDER BY,进行GROUP BY时加ORDER BY NULL,MySQL不会再进行文件排序.
WITH ROLLUP 超级聚合,可以挪到应用程序处理.

使用尽可能小的查询是好的,但是有时将一个大的查询分解为多个小的查询是很有必要的.
分解关联查询,让缓存的效率更高.
执行单个查询可以减少锁的竞争.
在应用层做关联更容易对数据库进行拆分.
查询效率会有大幅提升.
较少冗余记录.

like模糊查询不能使用左模糊或全模糊,应使用全文索引

查询不对字段做空值判断. is null/is not null 会导致索引失效

不在条件查询 ‘=’ 前做运算或函数. 索引失效

select * from users where trim(user_name) = “xxx”;

慎用 !=、!<>、not in、not like、or… 会导致索引失效

union all 代替 or;
UNION ALL的效率高于UNION.

避免频繁创/销毁临时表

将大事务拆为小事务执行

业务层面减少大量数据查询返回

避免深分页

select xx from table limit 100000,10;
相当于查询第1W页数据,在MySQL的实际执行过程中,首先会查询出100010条数据,然后丢弃掉前面的10W条数据,将最后的10条数据返回,极其浪费资源.
若数据有序, select xx from table where 有序字段 >= n limit 10;

类似于select * from table where age > 20 limit 1000000,10 这种查询其实也是有可以优化的余地的.
若age字段有索引,虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快.
可以修改为 select * from table where id in(select id from table where age > 20 limit 1000000,10).
如果ID连续,可以select * from table where id > 1000000 limit 10.

如果按时间排序查询,使用limit n (不要使用limit m, n 页数多了之后效率低)然后记录最后一条的时间,下次从最后一条的时间开始查询;
例:
原始:select * from product limit 866613,20
优化:SELECT * FROM product WHERE ID >= (select id from product limit 866613,1) limit 20
SELECT * FROM product a JOIN (select id from product limit 866613,20) b ON a.ID = b.id

从需求的角度减少深分页,不做类似的需求(直接跳转到几百万页之后的具体某一页)只允许逐页查看或者按照给定的路线走,这样可预测,可缓存.
以及防止ID泄漏且连续被人恶意攻击.
主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.
或者使用ES中间件.

不要使用缩写写法

user_name userName
user_name as userName
隐式的这种写法在MySQL底层都需要做一次转换

联合索引查询确保最左前缀原则,索引跳跃扫描机制存在较大开销.

批量操作禁止循环.

明确返回一条数据的查询 使用 limit 1;匹配到一条数据时就会停止扫描.

where子句

1. 优先考虑索引,在where 和order by的列上建立合适的索引
2. where内 尽量避免null值判断,否则将导致引擎放弃使用索引而进行全表扫描;设置默认值,根据默认值来判断
3. 避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描.
4. 量避免在 where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描
   如:select id from t where num=10 or num=20
   优化查询 select id from t where num=10 union all select id from t where num=20
5. in 和 not in 也要慎用,否则会导致全表扫描
   select id from t where num in(1.2.3)
   对于连续的数值,能用between就不要用in select id from t where num between 1 and 3
6. like ‘%…’ 模糊匹配,可以考虑全文索引
7. 如果在 where 子句中使用参数,也会导致全表扫描.
   因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时；
   它必须在编译时进行选择.然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项.
   如下面语句将进行全表扫描 select id from t where num=@num
   可以改为强制查询使用索引 select id from t with(index(索引名)) where num=@num
8. 量避免在 where 子句中对字段(子句中的“=”左边)进行表达式或函数操作,这将导致引擎放弃使用索引而进行全表扫描.
   表达式:
   select id from t where num/2=100
   改为:select id from t where num=100*2
   函数:
   select id from t where substring(name,1.3)=’abc’
   name以abc开头的id应改为: select id from t where name like ‘abc%’

12.5.2.索引优化

explain分析
4.5.2.执行分析工具
4.5.索引应用

SQL写法进行优化,对于无法应用索引,或导致出现大数据量检索的语句,改为精准匹配的语句.
对于合适的字段上建立索引,确保经常作为查询条件的字段,可以命中索引去检索数据.

12.6.问题排查

12.6.1.慢查询处理

开启慢查询日志

锁阻塞导致执行超出慢查询阈值的SQL:
show status like ‘innodb_row_lock_%’; 命令查询MySQL整体的锁状态:

• Innodb_row_lock_current_waits 当前正在阻塞等待锁的事务数量.
• Innodb_row_lock_time MySQL启动到现在,所有事务总共阻塞等待的总时长.
• Innodb_row_lock_time_avg 平均每次事务阻塞等待锁时,其平均阻塞时长.
• Innodb_row_lock_time_max MySQL启动至今,最长的一次阻塞时间.
• Innodb_row_lock_waits MySQL启动到现在,所有事务总共阻塞等待的总次数.

其他 SQL优化

12.6.2.客户端连接异常

• 数据库现有连接数,超出 MySQL最大连接数,新连接会异常.
• 客户端数据库连接池 与 MySQL版本不匹配.
• 客户端 超时时间过小,可能导致出现连接中断.
• MySQL、Java程序 所部署的机器 不位于同一个网段,机器之间网络通信故障.
• 部署MySQL的机器资源被耗尽,如CPU、硬盘过高,导致MySQL没有资源分配给新连接.

12.6.3.死锁

死锁

SHOW ENGINE INNODB STATUS\G; 查看InnoDB存储引擎的运行状态日志.
LATEST DETECTED DEADLOCK 死锁区域的日志

12.6.4.服务器CPU100%

解决流程:

1. 找到CPU过高的服务器. top命令
2. 定位到具体的进程.
3. 定位到进程中具体的线程. top -Hp [PID]
4. 查看线程正在执行的代码逻辑.
5. 从代码层面优化.

12.6.5.MYSQL磁盘IO达到100%利用率

原因:

1. 突然 大批量 变更库中数据,需要执行大量写入操作,如主从数据同步时.
2. MySQL处理的整体并发过高,磁盘I/O频率跟不上,读写速率过慢.
3. 内存中的 BufferPool 缓冲池过小,大量读写操作需要落入磁盘处理,导致磁盘利用率过高.
4. 频繁创建和销毁临时表,导致内存无法存储临时表数据,转到磁盘存储,导致磁盘飙升.
5. 执行某些SQL时从磁盘加载海量数据,如多表联查,数据较大,最终导致IO打满.
6. 日志刷盘频率过高.

解决:

1. 磁盘不是SSD材质,将磁盘升级成固态硬盘,MySQL对SSD硬盘做了特殊优化.
2. 项目中记得引入 Redis 降低读压力,引入 MQ 对写操作做流量削峰.
3. 调大内存中 BufferPool 缓冲池的大小,设置成机器内存的70~75%左右.
4. SQL语句时减少多张大表联查,不要频繁地使用和销毁临时表.

十三,版本特性

1.MySQL5.6

2.MySQL5.7

3.MySQL8.0

十四,表分区

partitioning
5.1版本中开始支持了表分区技术.
MySQL中 数据库实例 的 数据,都会放在磁盘上来存储,默认的存储位置查看:

show variables like '%datadir%';

14.1.概念

未分区的 表文件都是以完整的形式存储在磁盘中.
表分区后 将一张表的数据拆分成多个磁盘文件存储:当需要去检索数据时,无需对完整的数据文件全部扫描,只需对某分区文件进行扫描,
做能够在一定程度上提升性能.
一个盘符/分区的存储空间是有限的,表数据增长到单磁盘区中存不下时,就会出现相关的错误信息.
表分区技术,可以将一张大表的数据,拆分到不同的磁盘分区中存放,可以打破存储容量的限制.
表分区前后的区别,就类似于数组和链表的区别:
分区前 逻辑,物理空间 都连续.
分区后 逻辑上是连续的,但物理空间上不一定连续.

优势:

1. 表分区技术可以打破单个磁盘分区的容量限制.
2. 对于失效(历史)数据,可以通过快速删除分区的方式清理,效率高.
3. 能够提升检索数据的性能.
4. 支持聚合函数的并行执行,可以分别统计各分区的数据做汇总.
5. 更好的数据管理性和可用性,一个表文件受损,不会影响其他分区文件中的表数据.

适用场景:
OLTP: 在线事务处理,通常的C端项目,负责基本的增删改查操作.
OLAP: 在线分析处理,主要负责 统计分析,数据汇总,如用户画像分析、报表统计等.

对于OLAP类型的业务,表分区可以很好的提高查询性能,在线分析都需要返回大量的数据,分区后(一般按时间分区),只需扫描对应的分区即可.
在OLTP业务中,中小型项目中一般不会产生太多的数据,大部分是通过索引返回有限数据.

当单库的数据过多时,表分区技术其实派不上太大的用场.
因为当数据达到一定量级时,要么会选择分库分表,要么选择分布式数据库(TiDB),最后再配合大数据技术辅助,做历史数据归档.

14.2.分区方式

表分区技术中只支持水平划分.(只支持对数据表,以行作为粒度进行划分).

MySQL数据库中总共支持range、list、hash、key、sub、columns这六种分区类型.最常用range.

14.2.1.Range分区

按照范围分区.仅支持以整数型字段作为分区键,以日期字段来做数据分区,可将其转换为int格式的时间戳.
把某字段作为分区键,实现分区.
如按主键分区:

• 第一个分区: -∞ ~ 100000
• 第二个分区: 100001 ~ 200000
• 第三个分区: 200001 ~ +∞

14.2.2.List分区

列表分区(枚举分区),为每个分区分配指定值.只支持整数字段作为分区键.
当插入的数据是这个分区的指定值时,数据就会插入到对应的分区中.
如以性别字段为例,0表示男,1表示女.插入其他数据,分区中找不到对应的值会报错!!!

14.2.3.Hash分区

哈希分区支持方式:

• 常规哈希:基于某个整数型字段,直接做取模,最后根据余数来决定数据的分区.
• 线性哈希:基于某个字段的哈希值,进行取模运算,最后根据余数来决定数据的分区.

常规哈希只能基于整数型字段对数据做划分,线性哈希不限制字段的类型,只要能够通过MySQL哈希函数,转换出哈希值的字段类型都可以作为分区键.

14.2.4.Key分区

类似Hash分区,key分区不在限制字段的数据类型.
hash分区中要么字段本身是整数类型,要么字段经过哈希函数处理后,得到一个整数的哈希值.

key分区中,不支持text、blob两种类型外,其他类型的字段都可以作为分区键.

key分区中 可以不显式指定分区键,MySQL会自动选择.都遵循的规则:

• 优先选择主键字段.
• 主键不存在,选择一个非空的唯一字段.
• 不存在唯一字段,选择除 text、blob 类型外的任意字段.

14.2.5.Sub分区 子分区

Sub分区又称子分区.分区嵌套.基于表分区后的结果,进一步做分区处理.(基于一个分区再做分区).
一张表可以基于日期中的年份做分区,基于年份做了分区后,还可以基于年分区进一步做月分区.

要求每个一级分区下的二级分区数量都一致,同时二级分区的类型只能为 hash、key 类型!!!

14.2.6.Columns分区 列分区

range、list分区的变种.
columns分区可以使 range、list 的分区键由多个字段来组成,同时支持的字段类型也相对更丰富,但这种分区法一般用的极少.

14.3.使用限制

1. 单张表最多只能创建8192个分区,MySQL5.6版本前 1024个.
2. 所有的表分区必须保持相同的存储引擎,Merge、CSV、Federated… 等引擎不支持表分区.
3. 对表做了分区后,无法再对表上的其他字段建立唯一索引!!!
4. 表中存在 主键、唯一键的情况下,分区键的字段必须为主键或唯一键的部分或全部字段.
5. 5.1前,分区键只能选择 整数型字段,或支持哈希函数处理 的字段.
6. 分区表中无法创建外键.

14.4.创建表分区

创建表分区一般有两种方式,一种是直接在创建表时声明,另一种则是通过alter方式创建.

14.4.1.Range分区实操

-- 创建一张 range 表,同时依据 r_id 字段划分四个分区
create table `range`(
	`r_id` int not null,
	`r_name` varchar(10),
	primary key (`r_id`) using btree
) 
partition by range(r_id)(
	partition p1 values less than (100000),
	partition p2 values less than (200000),
	partition p3 values less than (300000),
	partition p4 values less than maxvalue
);
-- 向表中分别插入四条不同范围值的数据
insert into `range` values
    (1."1"),
    (100001."2"),
    (200001."3"),
    (999999."4");
-- 查询 range 表中不同分区的数据量
select partition_name,table_rows 
from information_schema.partitions 
where table_name = 'range';

分区结果:

分区文件:

14.4.2.List分区实操

-- 创建一张 list_part 表,同时根据 l_sex 性别字段做分区
create table `list_part`(
	`l_id` int not null,
	`l_name` varchar(10),
	`l_sex` int not null
) 
partition by list(l_sex)(
	partition p1 values in (0),
	partition p2 values in (1)
);
-- 插入两条性别为男(l_sex=0)、一条性别为女(l_sex=1)的数据
insert into `list_part` values(1."1",0),(2."2",1),(3."3",0);
-- 查询 list_part 表中不同分区的数据量
select partition_name as "分区名称",table_rows as "数据行数"
from information_schema.partitions 
where table_name = 'list_part';

插入一条分区键范围中,不存在的数据时,就会抛出错误信息.

14.4.3.Hash分区实操

常规Hash分区

-- 创建一张 hash 表,并选用 h_id 作为分区键,划分为三个分区
create table `hash`(
	`h_id` int not null,
	`h_name` varchar(10)
) 
partition by hash(h_id)
partitions 3;

线性Hash分区

create table `linear_hash`(
	`lh_id` int not null,
	`lh_name` varchar(10)
) 
partition by linear hash(lh_id)
partitions 3;

使用字符串作为分区键,那么必须要找一个能够将字符串转换为整数哈希值的函数: hash(哈希值函数(字符串字段))

14.4.4.Key分区实操

key分区 强制使用 整数类型之外 的字段作为分区键.实际就是内部调用了hash函数.

-- 创建一张 key 表,选用字符串类型的字段: k_name 作为分区键
create table `key`(
	`k_id` int,
	`k_name` varchar(10) not null
) 
partition by key(k_name)
partitions 3;

也支持线性hash,使用 linear key(columnName)

14.4.5.Sub分区实操

分区嵌套.基于一次分区的结果上再次进行分区.

-- 创建一张 sub 表,基于年份进行范围分区,再基于月份进行哈希分区
create table `sub`(
	register_time datetime 
) 
partition by range(year(register_time))
subpartition by hash(month(register_time))
(
	partition p1 values less than (2000)(
		subpartition p1_s1.
		subpartition p1_s2
	),
	partition p2 values less than (2020)(
		subpartition p2_s1.
		subpartition p2_s2
	),
	partition p3 values less than maxvalue(
		subpartition p3_s1.
		subpartition p3_s2
	)
);
-- 插入八条测试数据
insert into sub values
	("1998-11-02 23:22:59"),
	("2000-08-11 14:14:39"),
	("2001-10-27 13:33:14"),
	("2008-04-22 12:44:25"),
	("2009-06-15 00:24:58"),
	("2019-12-07 01:21:24"),
	("2022-04-01 17:11:14"),
	("2025-01-09 16:36:01");
-- 查询各个子分区中的数据行数
select   partition_name as "父分区名称", subpartition_name as "子分区名称", table_rows as "子分区行数"
from  information_schema.partitions 
where  table_name = 'sub';

年份range划分3分区,月份模二运算进入子hash分区.
分区信息:

分区文件:

14.5.其他分区命令

-- 查询一张表父子分区的数据量
select  partition_name as "父分区名称", subpartition_name as "子分区名称", table_rows as "子分区行数"
from  information_schema.partitions 
where  table_name = 'tname';
-- 查询MySQL中所有表分区的信息
select * from information_schema.partitions;
-- 查询一张表某个分区中的所有数据
select * from tname partition (分区名);
-- 对于一张已存在的表添加分区
alter table tname reorganize partition 分区名 into (
	partition 分区名1 values less than (范围) data directory = "/xxx/xxx/xxx",
	partition 分区名2 values less than (范围) data directory = "/xxx/xxx/xxx",
	......
);
-- 将多个分区合并成一个分区
alter table 表明 reorganize partition 分区名1.分区名2... into (
    partition 新分区名 values less than (范围)
);
-- 清空一个分区中的所有数据
alter table tname truncate partition 分区名;
-- 删除一个表的指定分区
alter table tname drop partition 分区名;
-- 重建一张表的分区
alter table tname rebuild partition 分区名;
-- 分析一个表分区
alter table tname analyze partition 分区名;
-- 优化一个表分区
alter table tname optimize partition 分区名;
-- 检查一个表分区
alter table tname check partition 分区名;
-- 修复一个表分区
alter table tname repair partition 分区名;
-- 减少hash、key分区方式的 n 个分区
alter table tname coalesce partition n;
-- 将一张表的分区切换到另一张表
alter table tname1 exchange partition 分区名 with table tname2;
-- 移除一张表的所有分区
alter table tname remove partitioning;

十五,分库分表

15.1.为什么分库分表

1. 请求数太高
2. 数据查询慢
3. 数据量太大
4. 单体架构:单一故障影响全局
5. 数据库瓶颈
   1. IO瓶颈
      1. 磁盘IO瓶颈
      2. 网络IO瓶颈
   2. CPU瓶颈

随着业务的发展,单库压力过高出现已上一系列问题.

分类:

1. 垂直分表
2. 水平分表
3. 垂直分库
4. 水平分库

15.2.分表方案

表字段过多时,考虑垂直分表方案,将多余的字段拆分到不同的表中存储.
表数据过多时,或数据增长速率过快,考虑通过水平分表方案,降低单表的数据行数.
分表操作只建立在单库压力不高,但是单表查询效率低下的情况适用!!!

15.2.1.垂直分表

结构不同,数据不同(表级别)
表字段过多时,
单行数据越大,缓冲区中能放下的热点数据页会越少,当读写操作无法在内存中定位到相应的数据页,会产生大量的磁盘IO;
致磁盘IO次数增多,影响数据的读写效率;
结果集响应时还会占用大量网络带宽,影响数据的传输效率;

一般根据冷热字段来对表进行拆分:能很好的控制表中单行数据的体积.

15.2.2.水平分表

结构相同,数据不同(表级别)

随着数据不断增长,当达到千万级别时,就会出现明显的查询效率下降的问题.
水平分表能够很好的控制单表的数据行数:一般要求控制在500-1200W之间为一张表.

拆分之后的水平表究竟会存储哪个范围的数据,这要根据水平分表的策略来决定(年月季周…)
一般表分区的场景可以用水平分表替代.

15.3.分库方案

垂直分库本质上就是按业务分库,是 分布式/微服务架构中业务独享库 的概念.
水平分库是对同一个节点作横向拓展,是高可用集群的概念.
优点-高可用、高性能、高稳定:

• 最大的性能收益,吞吐量随机器数量呈直线性增长.
• 最大程度上保障存储层的高可用,任意节点宕机都不会影响整体业务的运转.
• 高容错率,当一个库中的结构存在问题需要重构时,无需将所有业务停机更新.
• 高稳定性,分库+完善的监控重启策略后,基本确保线上无需人工介入管理.

15.3.1.垂直分库

结构不同,数据不同(库级别)

项目开发过程中,为了方便团队分工合作和后续管理维护,通常都会对单个项目划分模块,
按照业务属性的不同,将一个大的项目拆分为不同的模块,每个业务模块在数据库中创建对应的表.

15.3.2.水平分库

结构相同,数据不同(库级别)
垂直分库将访问压力分发到不同的库处理后,在极大程度上提升了数据层的负荷能力.
但如果某类业务的并发数依旧很高,远超出了单个数据库节点的处理瓶颈,可通过水平分库的方案,来提升某类业务库的抗并发吞吐量.

根据压力的不同,分配不同的机器数量,从而使得不同库的抗压性都能满足对应的业务需求,
类似于分布式/微服务项目中,对单个服务做集群 保证高可用的策略.

15.3.3.其他分库方案

主从复制、读写分离、双主热备等方案.
12.4.3.MySQL自身架构优化

一般在考虑选用分库方案时,优先考虑使用主从、双主的方案.
依旧无法提供系统所需的吞吐量时再考虑选择 垂直分库方案,按照业务属性去划分库结构,最后考虑选择水平分库方案.

15.4.分库分表存在的问题

15.4.1.垂直分表问题

试图读取一条完整数据时,需要连接多个表来获取,设置好映射字段.
当增、删、改数据时,操作多张表,要保证操作的原子性,手动开启事务.

15.4.2.水平分表问题

多表联查问题

1. 如果分表数量固定,对所有表进行连接查询,但性能开销较大,不如不分表.
2. 分表数量会随时间不断变多,先根据分表规则,确定要连接的表后再查询.
3. 连表查询只需要从中获取1~3个字段,直接设计冗余字段,避免连表查询.

增删改数据问题

操作前确定好具体的表.批量变更数据依旧需要先定位到具体分表.

聚合操作问题

进行 sum()、count()…、order by、group by…等聚合操作:

1. 放入三方中间件,依赖于第三方中间件完成,如ES. √ 常用
2. 常用的聚合数据放入Redis缓存中,后续从Redis中获取.
3. 从所有表中统计出各自的数据,在Java中作聚合操作. √ 常用

15.4.3.垂直分库问题

跨库join问题

1. 冗余字段,常用字段放到需要要数据的表中,避免跨库连表.
2. 同步数据,通过 广播表/网络表/全局表 将对应的表数据 同步一份 到相应库中.
3. 设计库表拆分时创建 ER绑定表,具备主外键的表放在一个库,保证数据落到同一数据库.
4. Java系统中组装数据,调用对方服务接口的形式获取数据,在程序中组装后返回. √常用

分布式事务问题

零容忍,必须解决分布式事务问题.
单体事务: InnoDB 事务机制是 建立在 Undo-log日志 的基础上完成,一个事务的所有变更前的数据,都记录在同一个Undo-log日志中,
当需要回滚时就直接用 Undo-log 中的旧数据覆盖变更过的数据.
  垂直分库之后,存在多个 MySQL节点,存在多个Undo-log日志,不同库的变更操作会记录在各自的 Undo-log日志中,
当某个操作执行失败需要回滚时,仅能够回滚自身库变更过的数据,无权回滚其他库的事务,
此时就必须要 事务管理者 介入解决分布式事务问题.

分布式事务解决方案:

1. Best Efforts 1PC模式.
2. XA 2PC、3PC模式.
3. TTC事务补偿模式.
4. MQ最终一致性事务模式

高并发性能瓶颈

过高地并发流量,单一单节点模式部署依然无法解决所存在的性能瓶颈,水平分库.

15.4.4.水平分库问题

水平分库虽然带来的性能收益巨大,产生的问题也最多.

聚合操作和联表问题

与15.4.2水平分表的多表联查与聚合操作类似.
多表联查先确定连接的表.
聚合操作使用中间件Es或查出数据后Java流处理.

数据分页问题

1. 常用的分页数据提前聚合到 ES或中间表,按时更新分页数据. 常用√
2. 大数据技术搭建数据中台,所有子库数据汇聚到数据中台,后续分页数据直接从中获取. 最佳√,成本高
3. 从所有子库中先拿到数据,然后在Service层再做过滤处理. 拓展性低,代码侵入性高.

ID主键唯一性

1. 设置数据库自增机制的 起始值和步长,控制不同节点的ID交叉增长,保证唯一性. 限制拓展性.
2. 业务系统中利用特殊算法生成 有序的分布式ID,比如雪花算法、Snowflake算法等.
3. 第三方中间件生产ID,使用 Redis的incr命令、或创建独立的库专门做自增ID工作.

数据落库问题

插入的数据落到哪个库?
数据分片规则:

1. 随机分片 随机分发写数据的请求,查询时需要读取全部节点才能拿取数据,一般不用.
2. 连续分片 每个节点负责存储一个范围内的数据,如DB1:1_{500W、DB2:500}1000W…
3. 取模分片 通过 整数型ID值 与 水平库的节点数量 做取模运算,最终得到数据落入的节点.
4. 一致性哈希 根据 某个具备唯一特性的字段值 计算哈希值,再通过哈希值做取模分片.

流量迁移

从单库切换到分库分表模式,数据如何迁移才能保证线上业务不受影响?
脚本将老库的数据同步到分库分表后的各个节点中,条件允许先上灰度发布.
做好版本回滚支持,如果迁移流量后出现问题,可以快捷切换回之前的老库.

容量规划

根据业务流量、并发情况决定,根据实际情况先做垂直分库,再对于核心库做水平分库,水平分库的节点数量保证是2的整倍数,方便后续扩容.

节点扩容

当节点的数量发生改变时,可能会影响数据分片的路由规则.

• 水平双倍扩容法
• 异步双写扩容法

水平双倍扩容法

双倍扩容法对节点进行扩容,原先节点数必须为2的整数倍.路由规则必须要为数值取模法、或Hash取模法.否则扩容难度直线提升.

进阶做法-从库升级法,给原本每个节点都配置一个从库,同步主节点的所有数据,当需要扩容时将从库升级为主节点.节点扩容口需要修改数据源配置.

路由算法从 %2 -> %4.库从DB0.DB01从,DB1.DB11从 -> DB0.DB01.DB1.DB11全主.
无需做数据迁移,DB01.DB11中包含DB0与DB1的数据.
在DB01/DB11从节点转主节点前,DB0与DB01数据完全相同,DB01转为主节点,节点包含了其他节点的冗余数据,后仅需要后续在业务低发时间清除多余数据即可.

从库升级法比较浪费机器资源,可以使用传统的双倍扩容法.每次扩容之后,手动从原本的库中将分片数据迁移,数据量较大时,迁移数据的时间会较长,所以只能做离线迁移.
在离线迁移的过程中,线上数据可能发生变更,离线迁移后需要核对数据的一致性.

异步双写扩容法

需要扩容时的情况,新数据依旧写入到老库中,写完之后同步给MQ一份,由MQ的消费者将新数据写到新库中,
同时新库同步老库中原有数据,持续到所有旧数据全部同步完成.
再以老库作为校验基准,核对数据无误后, 再将模式切换为扩容后的分库模式:

切换分库模式后,在业务代码中去掉 双写逻辑,改为 路由分片 逻辑:

1. 修改应用服务代码,增加 MQ双写方案,配置新库同步老库数据,部署.
2. 新库同步复制老库中所有老数据.
3. 老库中的所有数据全部同步完成后,以老库作为校验基准,校对新库中的数据.
4. 数据无误后,修改应用配置和代码,将双写改为路由分片,再次部署.

异步双写扩容法,适用于垂直分库后的第一次单节点扩容.水平双倍扩容法,则适用于水平分库后的后续扩容.

多维度查询

水平分库后呢,执行SQL使用哪个库是根据路由键和路由算法来决定.
使用路由键查询数据没有问题,但通过其他字段查询时,就无法通过路由键定位具体DB.

• 淘宝方案: 对于订单库实现了 多库多维 路由键拆分,使用三个水平库集群,三个分库集群中数据完全相同,使用不同的路由键,满足不同维度查询数据的业务需求.
• 数据量小,可以通过 ES维护路由键的二级索引,如id1:name1.id2:name2;基于非路由键字段查询时,先从ES中查到路由键值,再根据路由键查询数据库的数据.

外键约束问题 !

逻辑上的主外键关系,经过水平分库后,所有的读写操作会基于路由键去分片.
问题:
业务产生 id=1的一条主表记录,详情两条记录(id=1.2),但是id=2的详情记录无法与id=1的主表记录hash到同一库中.
必须要能够确保 外键所在的数据记录,必须与 主键数据记录 落到同一个库中去,否则无法通过主键值查询到完整的数据.

通过绑定表实现.

15.5.分库后访问问题

分库分表后 存在多个数据源,程序如何合理访问数据库,编写配置和代码.

编码层: 代码中通过框架提供的 数据源动态切换类 实现 如 Spring.AbstractRoutingDataSource类.
框架层: 一般的ORM框架也会提供 切换数据源的实现类 如MyBatis.Interceptor接口拦截SQL实现.
驱动层: 在 DBC驱动层拦截SQL语句,改写SQL实现 如Sharding-JDBC框架的原理就是如此.
代理层: 使用数据库的业务服务都 连接代理中间件,由中间件来决定落库位置 如MyCat实现.
服务层: 使用分布式数据库,自带分库分表功能 如TiDB、OceanBase…

成熟的方案:

工程(依赖、Jar包、不需要独立部署,可集成在业务项目中):

• 淘宝网: TDDL
• 蘑菇街: TSharding
• 当当网: Sharding-Sphere-JDBC
  进程(中间件、需要独立部署的第三方进程):
• 早年最热门、基于阿里Cobar二开的MyCat
• 阿里B2B: Cobar
• 奇虎360: Atlas
• 58同城: Oceanus
• 谷歌开源: Vitess
• 当当网: Sharding-Sphere-Proxy

首先 Sharding-Sphere框架,
早年只有Sharding-JDBC 驱动层分库分表,后续推出了代理层的 Sharding-Proxy中间件,最终合并成立了Sharding-Sphere项目.

15.6.单表数据巨增实战

每日新增大量数据,如何处理?
分库? × 个别表数据量大,分库没有必要.
表分区? × 单表随时间推移,数据量会越来越大.
水平分表 √ 单体架构 + 单库 + 按月水平分表

见_ytools模块

水平分表

• 能够自动按月创建一张月份账单表,从而将每月的流水数据写入进去.
• 写入数据时,能够根据当前的日期,选择对应的月份账单表并插入数据.

自动创建表

1. 创建存储过程;
2. 创建定时任务;
3. 开启定时任务;

数据写入

• 根据月份获取表名接口;
• 根据表名写入数据;

查询数据

• 流水号查询
  获取流水号携带时间戳.使用时间戳获取表名并查询.
• 范围查询
  1. 根据用时间范围,得到日期内的所有表名
  2. 动态生成对应的查询语句
     • 单月 between start to end;
     • 多月 union all 所有表,最后表使用 ; 结尾.首表和尾表做时间控制.

十六.主从复制架构 AP

主从架构中必须有一个主节点,以及一个或多个从节点.

所有的数据都会先写入到主,接着其他从节点会复制主节点上的增量数据,从而保证数据的最终一致性.
使用主从复制方案,可以进一步提升数据库的可用性和性能:
1, 在主节点故障时,从节点能自动切换成主节点,继续对外提供服务.
2, 提供数据备份的功能,当主节点的数据发生损坏时,从节点中依旧保存着完整数据.
3, 基于主从实现读写分离,主节点负责处理写请求,从节点处理读请求,进一步提升性能.
问题:

1. 木桶效应:一个主从集群中所有节点的容量,受限于存储容量最低的服务器.
2. 数据一致性:同步数据的过程是基于网络传输完成的,存在延迟.
3. 脑裂问题: 从节点会通过心跳机制,发送网络包来判断主机是否存活,网络故障情况下会产生多主.
   (如果将所有节点都部署在同一网段,基本上不会出现集群脑裂问题)

16.1.主从复制 数据同步方式

异步复制、同步复制、半同步复制、增强式半同步复制/无损复制

同步与异步流程

MySQL数据复制的过程就是基于Bin-log二进制/变更日志完成的,MySQL复制数据的过程是异步的方式.
Zookeeper中采用的是同步复制方案(半同步模式).因为同步复制方案能够确保数据100%不丢失,满足数据的强一致性.

半同步复制 after-commit

半同步模式:数据先写入到主节点,然后主节点向所有从节点发送写入数据请求.
无需等待所有从节点全部写入完成后再返回,只要有一个从节点写入成功并返回了ACK,直接向客户端返回写入成功.
既能够保证性能,又能够确保数据不丢失.

防止长时间收不到ACK:
rpl_semi_sync_master_timeout 参数 控制超时时间,其默认值是10000ms/10s.
超时时间内未收到ACK,则切换为异步模式,后续网络正常再切换回半同步模式.

Zookeeper中,要求收到一半从节点以上的ACK时,算作写入数据成功,向客户端返回写入成功.(ZAB一致性协议)

after-commit: 主库在未收到从库的ACK前,虽然不会给客户端返回成功,
但本质上在MySQL中会提交事务,也就是主库中的其他事务是可以看见对应数据的.
当此时宕机,会导致旧主上能查询出的数据,在新主(原从库)上无法查询到.

无损复制(增强式半同步复制) after-sync

Mysql5.7+ 不再使用半同步复制.使用半同步复制时,自动使用无损复制模式.
无损复制中等待ACK的动作会放到事务提交前进行,而传统半同步复制中,等待ACK的动作会放到事务提交后进行.
当主库未收到从库的ACK前,不会在主库上提交事务,保证了主从节点的数据强一致性,解决了 after-commit 中存在的问题.

延迟复制

支持从库数据的延迟同步,当从库上的I/O线程,获取到主库的Bin-log日志后,从节点的SQL线程并不会立刻解析日志执行,等待时间可以配置,一般建议设为3~6小时之间.

延迟复制好处:
防止误删操作,在主库上误删了大量数据,从库并非立即执行同步,可以及时通过从节点上的数据回滚数据.
缺点:
无法再对主从架构做读写分离.
场景:
仅适用于作为备库的节点使用.

并行复制

GTID(Global Transaction ID)(全局事务标识符)复制 5.6

传统的主从架构中,当需要发生主从切换时,需要手动找到Bin-log的POS同步点,然后执行 change master to [new-master-pos] 命令,将其他从节点指向新主库.
但每个从节点可能同步数据的进度都不一致,每个从节点都需要去找到它上次的POS点,然后指向新主库.

GTID 由节点 UUID+事务ID 组成,MySQL在第一次启动时利用 UUID随机生成一个server_id,MySQL对每一个写事务都分配一个顺序递增的值作为事务ID,
GTID格式为server_uuid:trx_id.

当主库的事务GTID后,发生 主从切换就 无需手动寻点,仅执行 change master to master_auto_position = 1 命令即可,
它会自动去新主库上寻找数据的同步点,MySQL自身 断点复制 功能.

工作过程:

1. master在更新数据时,为每一个 写事务分配一个全局的GTID,并记录到Bin-log中.
2. slave节点的I/O线程 拉取数据时,将读到的记录写到 relay-log中继日志 中,并设置gtid_next值.
3. slave节点的SQL线程 执行前,读取gtid_next值得知接下来该解析哪条日志并执行.
4. slave节点的SQL线程 执行时,对比自身的Bin-log日志中是否有对应的GTID:
   • 有: GTID对应的事务已经执行过,slave 自动忽略掉这条记录.
   • 没有: SQL解析该GTID对应的 relay-log 记录并执行,再将GTID记录到Bin-log.

当发生主从切换,MySQL首先会选择距离master的GTID最近的从节点作为新主,
然后将其他从节点转变为新主的从库,根据自身 gtid_next值,去新主的日志文件中做对比,然后找到各自的同步点,继续从新主中复制数据.

新主的从节点会比其他从节点的数据要完善,新主中的GTID值是最大的.

由于GTID复制是基于事务来实现的,不支持事务的存储引擎无法使用GTID机制,所以GTID机制基本上只对InnoDB引擎生效.

组复制

组复制基于GTID复制.
组复制是指将一组并行执行的事务,全部放入到一个GTID中记录,后续从节点同步数据时,会一次性读取这一组事务解析并执行.

传统的GTID值由 节点ID+事务ID 组成:12EEA4RD6-45AC-667B-33DD-CCC55EF718D:88.
组复制的GTID通过逗号分隔:12EEA4RD6-45AC-667B-33DD-CCC55EF718D:89, 12EEA4RD6-45AC-667B-33DD-CCC55EF718D:89-94, …

MySQL提交事务时内部会调用ordered_commit函数:
首先会将事务加入等待事务组,接着会经过三个核心步骤:FLUSH、SYNC、COMMIT,也对应三个队列,工作原理都大致相同:

1. 某个事务进入FLUSH队列时,队列为空,则事务会担任“队长”的角色.
2. 后续事务进入队列,队列不为空,会将提交工作委托给队长来完成.
3. 队长不会无限制等待队员到来,从队长加入的时间点开始,当超出 binlog_group_commit_sync_delay 规定的时间后,就会进行一次组提交.

同一时刻只允许一组事务工作,当有另外一组事务提交时,需要等待上一组事务提交完成.
做组提交工作时,会将当前事务组的内容记录到Bin-log日志中,同时会将这组事务记录成一个 GTID,不同事务之间通过,逗号分隔(实际过程更为复杂).

并行复制 MTS机制

5.6并行复制引入了并行复制的思想(为了防止锁冲突以及并发顺序问题-基于库级别的并行复制),多主一从架构中才会用到:一个从节点对应多个主节点时,有几个主节点就开几条SQL线程去解析并写入数据.

5.7.19 能够在同一时间内提交的事务,不存在锁冲突,可以开启多条线程同时执行一个组中不同的事务.
主库上是如何并发写入数据的,从库也会开启对应的线程数去并发写入.
这种机制命名为 enhanced multi-threaded slave增强型多线程从属服务器,简称MTS机制.

为了兼容5.6版本中的并行复制,又多加入了一个 slave-parallel-type 参数:

• DATABASE:默认的并行复制模式,表示基于库级别的来完成并行复制.
• LOGICAL_CLOCK:表示基于组提交的方式来完成并行复制.

MTS机制 能够在很大程度上提升从库复制数据的速度,能够让从库的数据实时性提升.
无损复制模式中,主节点需要等待从节点的ACK才会真正提交事务,从库使用并行复制后,能够在一定程度上解决从库的复制延迟问题.

5.7中的并行复制,在一定程度上解决了原有的从库延迟问题.
但如果一个新的从节点加入集群时,因为要从头开始同步数据,依旧存在效率问题.
8.0中,对于并行复制技术提出了 基于writeSet的MTS技术:
即多个事务之间,只要变更的数据记录没有重叠,也就是操作的数据没有冲突,无需在一个事务组内,也可以支持并发执行.

16.2.主从复制原理

一般数据同步有两种方式:

• 主节点推送: 当主节点出现数据变更时,主动向自身注册的所有从节点推送新数据写入.
• 从节点拉取: 从节点定期去询问一次主节点是否有数据更新,有则拉取新数据写入.
  MySQL方式:
  

16.3.数据格式–Binlog格式

Binlog格式:

• Statement: 记录每一条会对数据库产生变更操作的SQL语句(默认格式) 日期函数存在问题.
• Row: 记录具体出现变更的数据(也会包含数据所在的分区以及所位于的数据页). 批量语句不合适.
• Mixed: Statement、Row的结合版,可复制的记录SQL语句,不可复制的记录具体数据. 不会出现数据不一致

16.4.架构实现

主从机制实现:一主一从/多从、双主/多主、多主一从、级联复制四种架构

16.4.1.一主一从/多从架构

传统的主从复制模型
适用于: 读多于写.

从节点的所有数据都源自于主节点.一般都会基于此实现读写分离.
相较于单机节点而言,读写请求分发到了不同的节点处理,能够在性能上进一步提升,吞吐量大幅提升.

16.4.2.双主/多主架构

读写请求的比例相似,且并发量超出了单库的承载阈值,可以选用双主/多主架构.

两个节点互为主从,两者之间相互同步数据,同时都具备处理读/写请求的能力,当出现数据库的读/写操作时,可以将请求抛给其中任意一个节点处理.

一定要手动设置自增步长和起始值确保主键的唯一性

16.4.3.多主一从架构

对于写大于读的场景,可以选用多主一从架构

多个主节点解决了写请求导致压力过大的问题,同时从库中还有完整数据,也不会因为拆分主库而影响读操作.

16.4.4.级联复制架构

级联复制架构中,存在两层从库,属于一主多从架构的升级.
一个主节点存在多个从节点时,多个从节点都会同时去主节点拉取新数据,数据量较大时,导致主节点的I/O负载过高.
级联复制架构解决了多个从库会对主库造成太大压力的问题.

第一层从库只有一个节点,负责从主库上拉取最新的数据.
第二层的多个从库 从一层的从库中拉取数据.能够这在从库较多的情况下,降低数据同步对主节点的性能影响.

16.5.主从一致性问题

读写分离方案,将写请求分发给主库、读请求分发给从库处理.
虽然充分发挥从库所在机器的性能,但由于从库需要处理读取数据的请求,主从节点之间的数据会有一定延时.
对于关键性的数据,主库上修改之后,去从库上读取,很有可能读取到的是修改前的数据.

四种解决方案:

• 业务逻辑改变
• 复制方式更改
• 数据库架构调整
• 引入第三方中间件

业务逻辑改变

增加中间状态,如审核中等,避免重复操作.
不适用于一些对数据实时性要求较高的场景.

复制方式更改

四种数据同步复制的方式,全同步、异步、半同步与无损复制.
MySQL默认异步复制模式.即主节点写入数据后会立即返回成功的状态给客户端.性能达到最佳,如果对实时性要求较高,可以改为全同步或半同步模式.

性能下降严重.

调整数据库架构

无法接受主从架构带来的短期数据不一致,可以升级服务器硬件,并将架构恢复成单库架构,所有读写操作都走单库完成,不会出现数据不一致.
单库无法承载客户端的访问压力时,可将整体架构升级到分库分表架构,制定好分片策略和路由键,每次读写数据都根据业务不同,操作不同的库.

如果项目的业务规模达不到分库分表的规模,会导致性能过剩,浪费机器性能.(可以通过容器化技术解决,分库分表会导致更多的问题产生)

引入三方中间件

引入Canal中间件来监控主节点的Bin-log日志.
Canal是由阿里巴巴开源的一项技术.
Canal,会监控主节点的Bin-log日志,当发生变更时,就拉取数据,然后推送给从节点写入.
Canal监听变更、拉取数据、推送数据都需要时间,存在一定开销,但是比从库去主库上拉取速度更快.

一般企业内部都会引入Canal来解决数据不一致问题,因为它不仅仅只能解决主从延迟问题,还能解决MySQL-ES、MySQL-Redis…等多种数据不一致的场景.

十七.分布式事务

分布式事务 涉及两个或多个数据库源的事务,即跨越多台同类或异类数据库的事务(由每台数据库的本地事务组成的),
分布式事务旨在保证这些本地事务的所有操作的ACID,使事务可以跨越多台数据库.

1. 2PC,3PC模式 ×
   数据库支持的 2PC【2 phase commit 二阶提交】,又叫做 XA Transactions.
   第一阶段 事务协调器要求每个涉及到事务的数据库 预提交(precommit)此操作,并反映是否可以提交.
   第二阶段 都可以提交后,事务协调器要求每个数据库提交数据.
   其中,如果有任何一个数据库否决此次提交或提交失败,那么所有数据库都会被要求回滚它们在此事务中的那部分信息.

   XA 协议比较简单,而且一旦商业数据库实现了 XA 协议,使用分布式事务的成本也比较低.
   XA !!!性能不理想,特别是在交易下单链路,往往并发量很高,XA 无法满足高并发场景
   XA 目前在商业数据库支持的比较理想,在mysql数据库中支持的不太理想,mysql的XA实现,
   没有记录prepare阶段日志,主备切换回导致主库与备库数据不一致.
   许多 nosql 也没有支持 XA,这让 XA 的应用场景变得非常狭隘.
   优点 尽量保证了数据的强一致,适合对数据强一致要求很高的关键领域.
   缺点 实现复杂,牺牲了可用性,对性能影响较大,涉及多次节点间的网络通信,通信时间太长,不适合高并发高性能场景.

   3PC,引入了超时机制(无论协调者还是参与者,在向对方发送请求后,若长时间未收到回应则做出相应处理)

2. 柔性事务-TCC 事务补偿型方案 (自己编写所有逻辑,非常多补偿代码) ×
   刚性事务 遵循 ACID 原则,强一致性.
   柔性事务 遵循 BASE 理论,最终一致性；
   与刚性事务不同,柔性事务允许一定时间内,不同节点的数据不一致,但要求最终一致.
   一阶段 prepare 行为 调用 自定义 的 prepare 逻辑.
   二阶段 commit 行为 调用 自定义 的 commit 逻辑.
   二阶段 rollback 行为 调用 自定义 的 rollback 逻辑.
   所谓 TCC 模式,是指支持把 自定义 的分支事务纳入到全局事务的管理中.

   其他补偿方式
   支付宝交易接口,一般会在支付宝的回调页面和接口里,解密参数,然后调用系统中更新交易状态相关的服务,将订单更新为付款成功.
   同时,只有当回调页面中输出了标识业务处理成功相应状态码时,支付宝才会停止回调请求.
   否则,支付宝会每间隔一段时间后,再向客户方发起回调请求,直到输出成功标识为止.

3. 柔性事务-最大努力通知型方案
   按规律进行通知,不保证数据一定能通知成功,但会提供可查询操作接口进行核对.这种方案主要用在与第三方系统通讯时,
   调用微信或支付宝支付后的支付结果通知.这种方案也是结合 MQ 进行实现,通过 MQ 发送 http 请求,设置最大通知次数.
   达到通知次数后即不再通知.

4. 柔性事务-可靠消息+最终一致性方案(异步确保型)本地消息表
   实现 业务处理服务在业务事务提交之前,向实时消息服务请求发送消息,实时消息服务只记录消息数据,而不是真正的发送.
   业务处理服务在业务事务提交之后,向实时消息服务确认发送.只有在得到确认发送指令后,实时消息服务才会真正发送.

   1. 做好消息确认机制(publisher,consumer【手动 ack】)
   2. 每一个发送的消息都在数据库做好记录.定期将失败的消息再次发送一遍

          CREATE TABLE `mq_message` (  
          `message_id` char(32) NOT NULL,  
          `content` text,  
          `to_exchane` varchar(255) DEFAULT NULL,  
          `routing_key` varchar(255) DEFAULT NULL,  
          `class_type` varchar(255) DEFAULT NULL,  
          `message_status` int(1) DEFAULT '0' COMMENT '0-新建 1-已发送 2-错误抵达 3-已抵达',  
          `create_time` datetime DEFAULT NULL,  
          `update_time` datetime DEFAULT NULL,  
           PRIMARY KEY (`message_id`)  
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 0