MySQL

1 MySQL架构原理

1.1 MySQL体系架构

1.2 MySQL运行机制

1.3 MySQL存储引擎

1.3.1 InnoDB和MyISAM对比

1.3.2 Undo Log

1.3.3 Redo Log和Binlog

Redo Log和Binlog是MySQL日志系统中非常重要的两种机制，也有很多相似之处，下面介绍下两者细 节和区别。

Redo Log日志

Binlog日志

2 MySQL索引原理

2.1 索引类型

2.1.1 普通索引

2.1.2 唯一索引

2.1.3 主键索引

2.1.4 复合索引

2.1.5 全文索引

2.2 索引原理

2.2.1 二分查找法

2.2.2 Hash结构

2.2.3 B+Tree结构

2.2.4 聚簇索引和辅助索引

2.3 索引分析与优化

2.3.1 EXPLAIN

2.3.2 回表查询

2.3.3 覆盖索引

2.3.4 最左前缀原则

2.3.5 LIKE查询

2.3.6 NULL查询

2.3.7 索引与排序

2.4 查询优化

2.4.1 慢查询定位

2.4.2 慢查询优化

2.4.3 分页查询优化

3 MySQL事务和锁

3.1 ACID 特性

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，
即所谓的 ACID：
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

3.1.1 原子性

原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

3.1.2 一致性

一致性：指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。一致性包括两方面的内 容，分别是约束一致性和数据一致性。

3.1.3 隔离性

隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并 发事务是隔离的。

3.1.4 持久性

持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不 应该对其有任何影响，不会丢失。

3.2 事务控制的演进

3.2.1 并发事务

3.2.2 排队

3.2.2 排他锁

3.2.3 读写锁

3.2.4 MVCC

MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的 数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。 多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。

3.3 事务隔离级别

3.3.1 隔离级别类型

3.3.2 MySQL隔离级别控制

3.4 锁机制和实战

3.4.1 锁分类

3.4.2 行锁原理

无索引加锁

3.4.3 悲观锁

3.4.4 乐观锁

3.4.5 死锁与解决方案

表级死锁

行级锁死锁


共享锁转换为排他锁

死锁排查

4 MySQL集群架构

4.1 集群架构设计

4.1.1 可用性设计

4.1.2 扩展性设计

4.1.3 一致性设计

4.2 主从模式

MySQL主从模式是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默 认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，从节点可以复制主数据库 中的所有数据库，或者特定的数据库，或者特定的表

4.2.1并行复制

4.2.1.1 MySQL 5.6并行复制原理

MySQL 5.6版本也支持所谓的并行复制，但是其并行只是基于库的。如果用户的MySQL数据库中是多个 库，对于从库复制的速度的确可以有比较大的帮助。

4.2.1.2 MySQL 5.7并行复制原理

4.2.1.3 并行复制监控

在使用了MTS后，复制的监控依旧可以通过SHOW SLAVE STATUS\G，但是MySQL 5.7在 performance_schema库中提供了很多元数据表，可以更详细的监控并行复制过程。

4.2.2 读写分离

4.2.2.1 读写分离引入时机

大多数互联网业务中，往往读多写少，这时候数据库的读会首先成为数据库的瓶颈。如果我们已经优化 了SQL，但是读依旧还是瓶颈时，这时就可以选择“读写分离”架构了。
读写分离首先需要将数据库分为主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之 间通过主从复制机制进行数据的同步，如图所示。

4.2.2.2 主从同步延迟

4.2.2.3 读写分离落地

4.3 双主模式

很多企业刚开始都是使用MySQL主从模式，一主多从、读写分离等。但是单主如果发生单点故障，从库 切换成主库还需要作改动。因此，如果是双主或者多主，就会增加MySQL入口，提升了主库的可用性。 因此随着业务的发展，数据库架构可以由主从模式演变为双主模式。双主模式是指两台服务器互为主 从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。

4.3.1 MMM架构

MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双 主故障切换 的第三方软件。MMM 使用Perl语言开发，虽然是双主架构，但是业务上同一时间只允许一 个节点进行写入操作。下图是基于MMM实现的双主高可用架构。

4.3.2 MHA架构

4.3.3 主备切换

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

4.4 分库分表

4.4.1 拆分方式

垂直分表就是将一张表中不常用的字段拆分到另一张表中，从而保证第一张表中的字段较少，避免 出现数据库跨页存储的问题，从而提升查询效率。
解决：一个表中字段过多，还有有些字段经常使用，有些字段不经常使用，或者还有text等字段信 息。可以考虑使用垂直分表方案。

4.4.2 主键策略

4.4.3 分片策略

4.4.4 扩容方案

5 分库分表实战及中间件

5.1 ShardingSphere实战

5.1.1 ShardingSphere

5.1.2 Sharding-JDBC

5.1.3 数据分片

5.1.4 读写分离

读写分离是通过主从的配置方式，将查询请求均匀的分散到多个数据副本，进一步的提升系统的处理能 力。

5.1.5 强制路由

在一些应用场景中，分片条件并不存在于SQL，而存在于外部业务逻辑。因此需要提供一种通过在外部 业务代码中指定路由配置的一种方式，在ShardingSphere中叫做Hint。如果使用Hint指定了强制分片 路由，那么SQL将会无视原有的分片逻辑，直接路由至指定的数据节点操作。
HintManager主要使用ThreadLocal管理分片键信息，进行hint强制路由。在代码中向HintManager添 加的配置信息只能在当前线程内有效。

5.1.6 数据脱敏

数据脱敏是指对某些敏感信息通过脱敏规则进行数据的变形，实现敏感隐私数据的可靠保护。涉及客户 安全数据或者一些商业性敏感数据，如身份证号、手机号、卡号、客户号等个人信息按照规定，都需要 进行数据脱敏。

• 在更新操作时，它通过对用户输入的SQL进行解析，并依据用户提供的脱敏配置对SQL进行改写， 从而实现对原文数据进行加密，并将密文数据存储到底层数据库。
• 在查询数据时，它又从数据库中取出密文数据，并对其解密，最终将解密后的原始数据返回给用 户。

5.1.7 分布式事务

5.1.7.1 Sharding-JDBC分布式事务

ShardingSphere整合了XA、Saga和Seata模式后，为分布式事务控制提供了极大的便利，我们可以在 应用程序编程时，采用以下统一模式进行使用。

5.1.8 SPI 加载剖析

在Apache ShardingSphere中，很多功能实现类的加载方式是通过SPI注入的方式完成的。 Service Provider Interface （SPI）是Java提供的一套被第三方实现或扩展的API，它可以用于实现框架扩展或 组件替换。

5.1.9 编排治理

编排治理模块提供配置中心/注册中心（以及规划中的元数据中心）、配置动态化、数据库熔断禁用、 调用链路等治理能力。

5.1.10 Sharding-Proxy

Sharding-Proxy是ShardingSphere的第二个产品，定位为透明化的数据库代理端，提供封装了数据库 二进制协议的服务端版本，用于完成对异构语言的支持。 目前先提供MySQL版本，它可以使用任何兼 容MySQL协议的访问客户端(如：MySQL Command Client, MySQL Workbench等操作数据，对DBA更 加友好。

5.2 Mycat

5.2.1 server.xml配置

server.xml几乎保存了所有 mycat 需要的系统配置信息。

5.2.1.1 user标签

5.2.1.2 firewall标签

5.2.1.3 全局序列号

5.2.1.3.1 本地文件

此方式 Mycat 将 sequence 配置到文件中，当使用到 sequence 中的配置后，Mycat 会更下 classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。

5.2.1.3.2 数据库方式

在数据库中建立一张表，存放 sequence 名称（name），sequence 当前值（current_value），步长 （increment） 等信息。

5.2.1.3.3 本地时间戳方式

5.2.1.3.4 分布式 ZK ID 生成器

5.2.1.3.5 ZK 递增方式

5.2.2 schema.xml配置

schema.xml 作为 Mycat 中重要的配置文件之一，管理着 Mycat 的逻辑库、表、分片节点、主机等信 息。

5.2.2.1 schema标签

schema 标签用于定义 Mycat 实例中的逻辑库，Mycat 可以有多个逻辑库，每个逻辑库都有自己的相关 配 置。可以使用 schema 标签来划分这些不同的逻辑库。

5.2.2.2 table标签

table标签定义了 Mycat 中的逻辑表，所有需要拆分的表都需要在这个标签中定义

5.2.2.3 dataNode标签

dataNode标签定义了 MyCat 中的分片节点，也就是我们通常说所的数据分片。

5.2.2.4 dataHost标签

5.2.2.5 heartbeat标签

5.2.2.6 writeHost和readHost标签

5.2.3 rule.xml配置

rule.xml用于定义Mycat的分片规则。

5.2.3.1 tableRule标签

5.2.3.2 function标签

5.2.4 Mycat实战

5.2.4.1 Mycat安装

5.2.4.2 分库分表

在rule.xml配置Mycat分库分表。

5.2.4.3 读写分离

在schema.xml文件中配置Mycat读写分离。使用前需要搭建MySQL主从架构，并实现主从复制， Mycat不负数据同步问题。

5.2.4.4 强制路由

一个查询 SQL 语句以/* !mycat * /注解来确定其是走读节点还是写节点。
/*! /
/# /
/* */

5.2.4.5 主从延时切换

5.2.5 Mycat事务

Mycat 目前没有出来跨分片的事务强一致性支持，单库内部可以保证事务的完整性，如果跨库事务， 在执行的时候任何分片出错，可以保证所有分片回滚，但是一旦应用发起 commit 指令，无法保证所有 分片都成功，考虑到某个分片挂的可能性不大所以称为弱 XA。

5.2.5.1 XA 事务使用

6 运维和第三方工具

6.1 Yearning

6.2 canal

6.3 DataX

6.4 percona-toolkit

MySQL数据库是轻量级、开源数据库的佼佼者，因此有很多功能强大第三方的衍生产品，如percona- toolkit，XtraBackup等。percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信 息，磁盘检测，分析慢查询日志，查找重复索引，实现表同步等等。

percona-toolkit工具是 MySQL一个重要分支产品percona的，它是一组命令的集合。下面给大家介绍 几个生产中常用的命令。

6.5 MySQLMTOP

6.6 ELK

6.7 Prometheus