数据库
2025-10-20 19:35:13 0 举报AI智能生成
数据相关知识总结，包括mysql，pg，数据库中间件：shardingjdbc, canal,mycat
作者其他创作
大纲/内容
数据库相关知识点
关系型数据基础知识
范式
第一范式
每个字段都是原子，不可再分割
关系型数据库最基本要求
第二范式
满足第一范式的前提下，每一行的数据只能与其中一列相关，记录需要有唯一标记
第三范式
满足第二范式的前提下，每个字段都是直接与主键过关系，其他字段之间不能存在传递关系
数据存储
mysql
事物
事物隔离级别
读未提交（Read Uncommitted）
一个事物可以读取其他事物没有提交的修改
问题
脏读
一个事务读到另一个事务 未提交 的修改。
读已提交（Read Committed）
一个事物只能读取到其他事物已经提交的数据
关键点：
影响的是 已有记录的内容
发生在 行被修改（UPDATE） 的场景
oracl数据库默认隔离级别
问题
不可重复度
一个事务内 多次读取同一行，结果不同（因为别的事务修改并提交了）。
可重复读（Repeatable Read）
事物在执行期间，获取获取同一行记录，结果都是一样的。
关键点：
影响的是 记录的存在性，不是已有记录的内容
发生在 插入或删除（INSERT/DELETE） 的场景
myslq默认事物隔离级别
问题
幻读
一个事务在某个范围内查询到的记录数 前后不一致（因为别的事务插入/删除了新行）。
串行化（Serializable）
同开启AB事物，A事物没有提交，B事物无法对A事物已经操作的过的任何其他数据进行操作
特点：所有事务 串行执行，通过对读的表加锁实现，最严格。
事物调度
并行
多个事物同时执行
串行
事物必须是一个一个的去执行，不能并发的去执行
并发事物控制
乐观控制
概念
每次访问数据的时候，都认为是查询操作，其他事物也能访问当前事物的数据；造成的数据不一致性，采取的方式是修改数据之后，对修改的数据进行回滚操作
特点
1.读操作过多的情况下，能够显著提高效率
2.采取时候回滚操作来来解决事物冲突问题
具体实现
通过在需要更新的表中设立相关的数据版本号来做操作
MySQL乐观锁使用场景
可在分布式场景下控制多个服务对相同数据的修改是线性执行的；
悲观控制
概念
每次访问数据的时候，都会对数据进行加锁操作
特点
1.采取的是提前预防冲突
2.严格的控制其他事物对当前事物的访问，避免大量的数据回滚
3.其他事物，在某些情况下还是可以访问当前数据
具体实现
通过数据库本身的锁机制来实现悲观控制
mysql悲观锁使用场景
单体服务的时候就实现对并发事物的悲观控制
概述
对并发（交叉）事物的控制的方式；数据本身对并发事物的控制都是悲观控制
ACID
基础概念
原子性
事物中发生多件事情，多件事情要么都成功，要么都失败
场景
A给B转钱，A成功的把钱给了B,B也成功的收到了A的钱
依赖 Undo Log（回滚日志） 实现
如果事务失败，InnoDB 可以根据 Undo Log 恢复数据到事务开始之前的状态。
日志内容
INSERT 操作的 Undo Log
记录如何删除新插入的行。
DELETE 操作的 Undo Log
记录被删除行的完整数据（行的所有列值）。
UPDATE 操作的 Undo Log
记录修改前的旧值。
反向记录sql的操作
一致性
一种状态到另外一种状态必须保证数据逻辑上的一致
场景
A给B转1000块钱，A的账户扣了1000块，B的账户多了1000块
依赖 原子性 + 隔离性 + 持久性 共同保障。
隔离性
当前事物不受其他事物影响
依赖 MVCC（多版本并发控制） + 行级锁 实现。
持久性
数据能够被持久化的存储在磁盘上
事物提交之后，该事物对应的修改数据都会被存储到磁盘上
依赖 Redo Log（重做日志） 实现：
事务提交时，先写 Redo Log，再写入磁盘，保证即使崩溃也能恢复。
日志内容
记录在物理页上面的数据变更
锁
锁的分类
按照粒度划分
库锁
加锁对象：整个数据库实例。
效果：整个库进入只读状态，常用于全库备份。
表锁
表锁是由MySQL本身来实现
表锁具体实现
意向共享锁
给数据行加行级锁之前需要取道该表的意向共享锁
意向排他锁
给数据行家排他锁之前需要取到该表的意向排他锁
概述
意向锁，锁是由innerDB自动添加，无需用户手动添加
行锁（innerDB实现）一个统称
记录锁
锁单条记录
间隙锁
锁范围（两个索引值之间的间隙（不含端点）
），防止幻读。
），防止幻读。
Next-key锁
记录锁 + 前一个间隙
锁分类说明
乐观锁，悲观锁都是在并发事物的一种控制方式，是一种思想，不是实实在在的锁
行锁，表锁也并非实实在在的锁，只是锁的粒度来划分的锁
二阶段锁
概述
关系型数据库的一种加锁原则；把锁的操作划分为两个阶段，加锁阶段，解锁阶段
实际过程
加锁阶段，解锁阶段互不相交，加锁阶段，只加锁，不解锁；解锁阶段，只解锁，不加锁
乐观锁，悲观锁
悲观锁
认为每次对数据库操作都会被并发修改，所以每次操作数据库都会加锁，阻塞其他修改
实现
行锁
SELECT ... FOR UPDATE
加上了排它锁，其他事务无法修改这行，直到事务提交或回滚
共享锁
SELECT ... LOCK IN SHARE MODE
行加共享锁（S锁），其他事务可以读，但不能修改
典型场景
库存扣减、银行转账等对数据安全要求高的场景。
特点
数据访问前加锁，保证安全。
并发性能低，高并发容易阻塞。
乐观锁
思想：假设大多数情况下不会发生冲突，数据操作前不加锁，提交时检查是否有冲突，如果有冲突则重试。
实现
版本号（Version）
在数据表中增加一个 version 字段，每次更新时 WHERE version=旧值，更新成功则 version+1，失败则说明被其他事务修改，重试
时间戳（Timestamp）
在数据表中增加 update_time 字段，更新时检查时间戳是否一致，一致则更新，失败则重试
典型场景
电商商品浏览量统计、点赞、评论等高并发更新。
特点：
数据访问前不加锁，性能高。
适合读多写少的场景。
索引
索引种类
使用方式分类
单列索引
主键索引
根据主键排序做的索引，特殊的唯一索引
唯一索引
强制让表中某个字段值不能重复
普通单列索引
根据某一列字段建立的索引
组合索引
把建立索引的字段值全部兼容在一起组成的索引
聚簇非聚簇
聚簇索引
数据和索引在物理存储上“聚合”在一起。
主键索引就是数据聚簇索引
非聚簇索引
索引和数据分开存储。
非主键索引全部都是属于聚簇索引
分类标准
根据索引的叶子节点中是否存放了整行数据
稀疏密集
稀疏索引
多条数据可以对应一条索引数据
非主键索引和有可能就是稀疏索引
密集索引
数据和索引是一一对应的
主键索引就是密集索引
索引数据结构
B+树
B+树的数据存储结构
数据库引擎不同B+树存储的数据不完全相同
innerDB的的B+树叶子节点存储的是全部的数据或者是主键id；
myisam的B+树叶子节点存储的时候该条数据地址值（不管是主键索引还是其他索引）
B+树和B树区别
B+树的非叶子节点存储的是节点之间的前后指向关系，并不存储实际的数据值；B树的只要是节点不管是叶子节点还是非叶子节点都会存储数据。
B+树的节点存储兄弟节点的地址值，可以直接指向兄弟节点；而B树是不可以的。
在相同数据层级，B+树能够存储更多的数据；而B树存储的数据少很多；
使用场景
myisam和innerDB索引都是使用B+树
hash索引
基于hash算法的散列索引
特点
查询单条数据查询时间的复杂度为01;
范围查询速度比较低
使用场景
memory引擎默认使用该中索引
全文索引
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
使用
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
索引开销
创建和维护索引都需要耗费时间和磁盘空间
修改语句操作的时候，需要对索引进行重新构建
索引相关概念
回表查询
查询的时候根据组合索引去查询，从索引中获取到的数据并未覆盖所需要 查询的全部字段，所以就需要那到索引中的id再回到表中去查询需要的字段，这就是回表
非索引下推
查询的时候，先根据组合索引中最左索引查询，拿到这些查询之后的数据的主键，再回表查询出整条数据，再从整条数据从按照其他条件做筛选；
索引下推
如果查询条件中包含了组合索引，那么就会优先使用组合索引查询，查询满足组合索引中最左索引的值，从这些筛选一遍的数据中再过滤出其他符合条件的数据，不用再次回表查询；
最左匹配原则
组合索引中，如果查询条件中并没有带上组合索引中最左边的索引，那么这个查询是不会先去查询组合索引
覆盖索引
查询的是主键索引字段，也就是查询的数据本身就是索引，直接从索引中拿数据，不需要再去根据主键回表查询其他数据
一次性非锁定读
就是普通的select
读取的是快照版本数据
一次性锁定读
在select 语句后面中加 for update 或者是 lock in share mode
读数据的时候会加锁，保证读取的数据不被其他事物修改
查询需走索引
数据库引擎
innerDB
文件结构
FRM文件
表结构文件
IBD文件
索引数据+表数据
锁
支持行锁，表锁
行锁
记录锁
通过锁定索引记录来实现；锁定某一个索引记录
使用场景
只用通过索引条件查询数据的时候，才会使用到
作用
间隙锁
通过索引记录来实现
对一定返回内数据进行加锁，即使这数据不存在也是会加锁
Select * from emp where empid > 100 for update;会对empid大于100的都加上间隙锁，即使记录不存在也会添加
使用场景
作用
Next-key锁
相当于一个记录锁+间隙锁
使用场景
作用
避免幻读
插入意向锁
插入数据之前，由insert操作设置的一种间隙锁
表锁
意向共享锁
给数据行加行级锁之前需要取道该表的意向共享锁
意向排他锁
给数据行家排他锁之前需要取到该表的意向排他锁
日志
redolog（innerDB引擎独有）
redolog日志类容
记录后事物执行之后的状态，用来记录未写入 datafile 的已成功的事物更新的数据；
记录对数据页的所有写操作，确保在系统崩溃后能够恢复事务的一致性与完整性
日志作用
在MySQL宕机时候，还有数据没有写入磁盘，可以通过重做日志重做，从而达到数据的持久化
日志生成
事物开始的时候就会有日志生成，随着事物的进行的过程中，日志逐渐写入redolog中
日志清理
当某个事物对应的数据写入 datafile中后，redolog的使命已经完成了，重做日志的空间就会被重用
undolog
undolog生成时间
数据库的每一次修改(insert,update,delete)操作,在落到磁盘之前都会先记录到undolog
undolog日志内容
记录信息修改之前数据
undolog日志回滚操作
根据undolog日志做数据库的逆向操作，原来的insert操作变成delete操作，delete变成insert，update变成逆向update
Myisam
文件结构
frm文件
存储的是表结构信息
MYD文件
存储的是数据行记录信息
MYI文件
存储索引对应的数据信息
myisam工作流程
根据索引
锁
支持表锁，不支持行锁
innerDB和Myisam区别
索引数据结构
myisam索引的B+树的叶子节点存储的是该条数据的地址值
innerDB索引的B+树的叶子节点存储的是该条数据的全部字段值（主键索引）
innerDB索引的非主键索引叶子节点存储的是id值（非主键索引）
锁区别
innerDB支持行锁，表锁；myisam支持表锁
事物
innerDB支持事物；myisam不支持事物（原因其实主要是myisam不支持行锁，间接导致不能支持事物）
是否支持全文索引
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
是否支持外键
innerDB支持外键；myisam不支持外键
主键是否必须
innerDB支持必须有主键；myisam可以没有主键
数据存储不同
innerDB数据存储frm、ibd文件；frm是表定义文件，ibd是数据文件
Myisam是frm、MYD、MYI；frm是表定义文件，myd是数据文件，myi是索引文件
innerDB数据索引数据和表数据是存储在一起；而myisam引擎索引数据和表数据是分开存储在两个文件
是否存储具体行数
innerDB不存储表的具体行数；myisam存储表的具体行数
是否支持表压缩
innerDB不支持表数据压缩；myisam支持表数据压缩，压缩后支持只读，修改操作需要加压才能操作；
memory
锁
支持表锁，不支持行锁
在内存中存在表数据，数据库重启之后，该引擎对应的数据就会消失
默认是使用hash索引
Merge
存储引擎允许将一组使用MyISAM存储引擎的并且表结构相同的数据表合并为一个表，方便了数据的查询
csv
其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件）
ARCHIVE
为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。
BLACKHOLE
用于临时禁止对数据库的应用程序输入
FEDERATED
能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。
EXAMPLE
可为快速创建定制的插件式存储引擎提供帮助
BDB
可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。
Cluster/NDB
MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
日志
binlog日志（二进制日志）
binlog日志作用
主从复制；主从复制就是为了读写分离
主从复制过程
1.主机每次完成实事物提交，完成数据更新之前，都会把变更的数据记录在binlog日志中
2.从机开启一个IO线程去从主机的binlog日志中读取数据，记录到自己的中继日志中；
3.从机开启线程，解析中继日志的事件，并执行，来做数据主从复制
数据库基于时间还原数据
读写分离；读写分离的前提就是主从复制
缓存一致性；模拟成从机，拉取binlog日志异步更新到缓存中的数据；
数据多备份
binlog日志格式
5.0之前
statment模式
以mysql事件形式记录的是实际执行的sql语句
5.0之后
row模式
以MySQL事件形式记录字段前后变化的值
binlog日志配置
max_binlog_size
日志文件的大小，默认1G
expire_logs_days
日志保留事件，默认是0，永久保留
记录所有的更新操作
查询日志
记录建立的客户端连接和执行的语句。
只记录查询相关的日志
更新日志
记录更改数据的语句。不赞成使用该日志。
5.1版本之前的日志，5.1版本之后被binlog日志替代
错误日志
记录内容
记录mysql启动关闭，以及运行过程中发生的错误信息
设置
错误日志默认是关闭
慢查询日志
记录内容
记录的是查询时间超过10秒（默认值）的sql语句
设置
慢查询日志默认是关闭
中继日志
场景用在主从复制中从服务器读取到主服务器的二进制日志存储起来就是中继日志，日志格式和二进制日志格式相同，都可以使用相同的binlog解析程序解析
innerDB独有日志
Redo Log（重做日志）
内容：记录的是数据页的物理修改，比如某一页上某个偏移量的数据被更新成了什么值。
使用场景
事务提交后还没把数据写入磁盘，MySQL 崩溃 → 根据 Redo Log 把数据恢复到提交时的状态。
举例
UPDATE user SET balance = balance - 100 WHERE id = 1;
Redo Log 里可能记录：在数据页 #123 偏移量 0x50，把值从 500 改为 400
Undo Log（回滚日志）
内容：记录的是数据的逻辑修改前的值，用于回滚。
使用场景：
回滚事务。
MVCC 读取时，提供历史版本。
举例
UPDATE user SET balance = balance - 100 WHERE id = 1;
Undo Log 里会记录：
balance 从 400 改回 500
MVCC版本控制
MVCC作用
1、解决读写冲突；
2、保证在事物并发条件下的读操作的效率，写操作的准确性。
InnoDB 存储引擎用来实现 高并发读写 + 事务隔离 的核心机制
为什么需要 MVCC？
多个事务并发访问同一行数据时，如果都用加锁，会导致阻塞严重。
MVCC 通过“版本快照”机制，让 读操作不用阻塞写操作，实现 非锁定读。
MVCC的作用
读写不互斥：快照读不加锁，读写可以并行。
避免脏读、不可重复读：靠 ReadView 判断数据可见性。
保证一致性：事务即使长时间运行，也能看到启动时的一致性视图。
MVCC控制分类
快照读
概述
读取记录的可见版本（可能是历史版本，也可能是当前版本）不用加锁
使用场景
不加锁的select操作
当前读
当前读就是读取的最新数据，而且还要保证其他事物不能修改当前数据
场景
加锁的读操作
select * from table where ? for update;
select * from table where ? lock in share mode;
当前写
概述
加锁读取记录的最新版本，保证其他事物不会并发修改这条记录；
使用场景
特殊的读操作
插入/更新/删除
insert into table values (…);
update table set ? where ?;
delete from table where ?;
因为修改操作之前，都是先需要查询到目标记录；
当前读，快照读，Mvcc关系
子主题
MVCC实现原理
undo日志
read view（读视图）
三个隐式字段
什么是mvcc
多数据版本并发控制，通过保存数据的多个版本，不阻塞读写操作。
目标：在高并发环境下，既保证事务的隔离性，又减少锁的开销。
MYSQL架构
server层
概述
所有跨存储引擎的功能都是在server层来实现的；包括存储过程，触发器，视图，函数,binlog日志
其他组件
存储过程
被用户定义的 sql语句的集合
触发器
某个时间所触发的操作；一种特殊的存储过程；
视图
基于基表的一种逻辑表或者虚拟表
五大组件
示意图
子主题
连接器
管理用户对数据库的连接
用户权限管理
优化器
根据语句对sql语句中能够做优化的SQL自动做优化
优化器分类
CBO
基于优化成本来做优化（主流的数据库都是这样）
RBO
基于规则，SQL语句经过分析器之后，会有多种执行方式，程序会判断选择一个效率最高的SQL
分析器
包括词法分析，语法分析
缓存模块
8.0之后就移除缓存模块
移除原因
1.缓存命中率低
2.内存空间宝贵
mysql缓存
mysql缓存查询到的数据永远是最新的数据，如果中件表有发生变化，则该缓存相关的数据就会被清空
缓存命中率比较低，最理想的情况下命中率最高13%
缓存工作
查询必须是完全相同的（逐字节相同）才能被认为是相同的
同样的字符串由于其他原因也可能会被认为是不一样的
缓存相关查询
指定从缓存中查询
SELECT SQL_CACHE id, name FROM customer;
前提是缓存是出于开启的状态
query_cache_type系统变量的值是ON或DEMAND，查询结果被缓存
指定不从缓存中查询
SELECT SQL_NO_CACHE id, name FROM customer;
缓存相关参数
have_query_cache
表示当前是否有使用缓存
query_cache_size
查询缓存大小
如果设置为0，则认为是禁用缓存
query_cache_limit
被缓存的查询结果最大值
默认值1MB
query_cache_min_res_unit
系统变量给查询缓存分配最小值
默认值是4KB
执行器
SQL语句真正的执行者
存储引擎
概述
主要负责数据的读取和存储，采用可替代式插件架构，支持各种数据库引擎。
概述
MySQL主要分为server层和存储引擎层
mysql主从复制
实现原理
mysql的主从复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)
并且主服务器开启了二进制日志
实现过程
复制过程中有三个线程在执行任务：一个线程在主服务器，负责将二进制日志的内容发送到从服务器（binlog dump线程）；
主从复制开始；从服务器上会创建一个io线程，连接主线程并记录二进制日志中的数据到中继日志；中间需要识别binlog dump线程 以获取二进制日志
从服务器会开启一个sql线程（第三个线程），从中继日志中读取日志，并执行日志，以同步数据；
主从复制条件
1、主服务器开启了二进制日志
2、主从服务器的mysql版本之间是可以相互兼容
由于历史原因：
1、二进制日志格式不一样
2、字符集，函数，时区处理不一样
3、主服务器上设置了相应的从服务器
mysql主从数据不一致如何处理？
不一致原因
主从复制延迟
从库在高并发下，压力大，从库比主库慢
复制失败或中断
网络抖动、从库宕机、SQL 冲突，导致部分 binlog 没同步成功。
复制模式不同
异步复制到从库，从库可能还没有更新同步到
半同步复制，只有有一个从库确认复制成功才返回，任然有延迟
全同步复制，全部从库复制成功才返回
常见解决办法
业务层处理
读写分离 + 强制读主
写操作之后强制读主库
场景
下单 → 立刻查订单详情 → 必须走主库。
其他不敏感的数据，走从库
数据库层处理
半同步复制
确保至少一个从库同步到位才返回给客户端，减少丢数据风险。
并行复制（Parallel Replication）
MySQL 5.7+ 支持多线程复制，按库/按表并行，提高同步速度。
架构层优化
监控复制延迟
发现延迟大 → 临时切读主库，或扩容从库。
引入中间件
使用 数据库中间件（如 MyCAT、ShardingSphere、ProxySQL） 自动感知主从延迟，动态路由请求。
避免强依赖从库一致性
对于强一致性要求的场景（支付、下单、库存），直接读主库。
实际落地策略（推荐组合拳）
强一致业务 → 写后强制读主。
弱一致业务 → 读从库 + 容忍延迟。
SQL语句
left join ,inner join ,right join full join
left join ,inner join ,right join 的区别
left join 做关联，以左表为主表，去右表当中匹配，如果左表中的数据未能匹配到右表中的数据，结果集中依旧会将左表的数据展示出来，未能匹配到的字段全部为空
right join 做关联，以右表为主表，去左表当中匹配，如果右表中的数据未能匹配到左表中的数据，结果集中依旧会将右表的数据展示出来，未能匹配到的字段全部为空
inner join 做关联，最明显就是左右两个表中只有关联上的数据才会展示出来，没有关联上的数据不会展示出来；
left join ,inner join ,right join full join 演示
原始数据
子主题
.inner join
子主题
.left join
子主题
.right join
子主题
full join
子主题
mysql模式
性能模式
系统模式
mysql客户端脚本和使用工具
myisampack
压缩MyISAM表以产生更小的只读表
mysql
交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具
mysqlaccess
检查访问主机名、用户名和数据库组合的权限的脚本
mysqladmin
执行管理操作的客户程序，例如创建或删除数据库，重载授权表，将表刷新到硬盘上，以及重新打开日志文件
mysqlbinlog
从二进制日志读取语句的工具。在二进制日志文件中包含的执行过的语句的日志可用来帮助从崩溃中恢复
mysqlcheck
检查、修复、分析以及优化表的表维护客户程序
mysqldump
将MySQL数据库转储到一个文件（例如SQL语句或tab分隔符文本文件）的客户程序
mysqlhotcopy
当服务器在运行时，快速备份MyISAM或ISAM表的工具
mysql import
使用LOAD DATA INFILE将文本文件导入相关表的客户程序
mysqlshow
显示数据库、表、列以及索引相关信息的客户程序
perror
显示系统或MySQL错误代码含义的工具
replace
更改文件中或标准输入中的字符串的实用工具
MYSQL其他知识点
Mysql事件
ReadView
mysql实战问题
mysql主从复制
实现原理
mysql的主从复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)
并且主服务器开启了二进制日志
实现过程
复制过程中有三个线程在执行任务：一个线程在主服务器，负责将二进制日志的内容发送到从服务器（binlog dump线程）；
主从复制开始；从服务器上会创建一个io线程，连接主线程并记录二进制日志中的数据到中继日志；中间需要识别binlog dump线程 以获取二进制日志
从服务器会开启一个sql线程（第三个线程），从中继日志中读取日志，并执行日志，以同步数据；
主从复制条件
1、主服务器开启了二进制日志
2、主从服务器的mysql版本之间是可以相互兼容
由于历史原因：
1、二进制日志格式不一样
2、字符集，函数，时区处理不一样
3、主服务器上设置了相应的从服务器
mysql主从同步延迟解决方案
定义
从库数据落后于主库数据
表现
从库的数据比主库数据旧
读取数据会有延迟
指标
Seconds_Behind_Master（SHOW SLAVE STATUS）从库执行 relay log 相对于主库的时间差（秒）
1、延迟原因
从库硬件水平不行，从库磁盘 IO 瓶颈，CPU 负载高，网络延迟
SQL 执行慢，从库执行复杂 SQL、JOIN 大表、UPDATE 批量操作慢
事务量大，主库高并发写入，binlog 从库处理不过来
锁竞争，从库执行事务时被锁阻塞
子主题
网络延迟，主库 binlog 传输到从库慢
2、解决方案
升级从库硬件
给表加索引，避免大事物一次性执行
分库分表 / 读写分离
增加从库的io线程，sql现成
调整复制策略为异步复制？
主库压力控制
异步复制和半同步复制的区别？
异步复制
主机提交事务后不需要等从机确认拉去到binlog日志，就直接返回到客户端
特点：
高性能：主库提交事务速度快。
数据可靠性低：主库宕机时，从库可能落后，存在数据丢失风险。
半同步
主机提交事务后，至少要等到有一个从机确认拉去到了binlog日志信息，才会返回到客户端，从库再异步执行事务落库
特点：
保证主库事务 binlog 至少被一个从库接收，减少数据丢失风险。
性能略低于异步复制，因为提交要等待从库 ack。
锁
myslq添加索引会不会锁表
肯定是会的
解决方式
前期设计的时候想好设计表的索引
mysql添加表字段会不会锁表
MyISAM 引擎
MyISAM 在执行 ALTER TABLE 时会复制整个表结构 + 数据，期间表是锁住的，读写都阻塞
innerDB
5.6之前版本
1、对原始表加写锁
2、按照原始表和执行语句的定义，重新定义一个空的临时表
3、对临时表进行添加索引（如果有
4、再将原始表中的数据逐条Copy到临时表中
5、当原始表中的所有记录都被Copy临时表后，将原始表进行删除。再将临时表命名为原始表表名
5.6版本以之后版本
1、对原始表加写锁
2、按照原始表和执行语句的定义，重新定义一个空的临时表。并申请rowlog的空间
3、拷贝原表数据到临时表，此时的表数据修改操作（增删改）都会存放在rowlog中。此时该表客户端可以进行操作的
4、原始表数据全部拷贝完成后，会将rowlog中的改动全部同步到临时表，这个过程客户端是不能操作的
5、再将临时表命名为原始表表名
MySQL调优
执行计划详解
id（sql执行顺序）
id相同，从上到下顺序执行
id不同，id值越大执行优先级越高，越先被执行
select_type查询类型
SIMPLE
简单的单表select查询
PRIMARY
子主题 1
SUBQUERY/MATERIALIZED
SUBQUERY
表示select或者是where中含有子查询
MATERIALIZED
表示where后面的in条件有子查询
UNION
被关联查询的语句；也就是关联查询的union中的第二个语句或者是union后面的select语句
UNION RESULT
表示关联查询的结果
table（查询涉及到的表）
直接显示表明
<unionM,N>
由M,N两个表关联产生的结果
<subqueryN>
有N表产生的中间结果集维表
type （重点，查询性能指标）
system
查询的是系统表
const
单表查询直接命中主键查询
demo
CREATE TABLE `user` ( `id` int(11) NOT NULL, `NAME` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');
explain select * from user where id=1;
eq_ref
关联查询用到主键索引做为关联字段
demo
CREATE TABLE `user` ( `id` int(11) NOT NULL, `NAME` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');CREATE TABLE `user_ex` ( `id` int(11) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);
EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;
ref
关联查询用到了非主键索引作为关联字段
demo
EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;
range
在索引上的范围查询
demo
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');insert into user values(4,'wangwu');insert into user values(5,'zhaoliu');
explain select * from user where id between 1 and 4;explain select * from user where id in(1,2,3);explain select * from user where id > 3;
index
查询是在所引述中扫描
demo
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');insert into user values(4,'wangwu');insert into user values(5,'zhaoliu');
explain count (*) from user;
ALL
全表扫描
demo
CREATE TABLE `user` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');CREATE TABLE `user_ex` ( `id` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);
explain select * from user,user_ex where user.id=user_ex.id;
查询的结果只的好坏顺序system > const > eq_ref > ref > range > index > ALL
CREATE TABLE `user` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');CREATE TABLE `user_ex` ( `id` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL, KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);
possible_key
查询过程中有可能用到的索引
key
实际使用的索引，如果为 NULL ，则没有使用索引
key_len
展示索引字段的实际长度
ref
显示该表的索引字段关联了哪个表的哪个字段
rows
估算查询所需要的记录需要读取的行数
filtered
返回的结果集行数占读取行数的百分比，越大越好
extra
展示其他重要信息
调优方向
1.sql调优
查询尽量覆盖到索引
1.在经常当做查询条件的字段上设置索引
未覆盖到索引情况
1.反向查询，!=,<> ,not in
2.前模糊查询
3.查询语句中在索引字段上做函数操作
4.关联字段虽然都是索引字段，如果数据类型不一致，走不了索引
5.where后面不带有效查询条件
6.建立联合索引，查询的时候不满足最左原则；
7.查询中对索引字段做运算操作
索引建立原则
索引长度限制
在字符串类型的字段上建立索引，需要指定索引的长度，一般10个就够了；
索引建立字段选取
1、必须要有主键索引
2、在经常做为查询条件，排序，分组，关联的字段上建立索引；
表的关联字段建立索引的时候确保两个表的数据类型是一样的
3、索引建立的字段应该是区分度非常高的字段；
索引数量
1、索引的数量不是越多越好，多了会增加索引构建维护成本；
2、不再使用或者使用非常少的索引需要删除索引
3、新建单列索引的时候，考虑和其他字段一起建立联合索引，减少索引个数
查询需要覆盖到索引
2.调整库，表结构
1、表的字段类型，字段长度合理
2、创建高性能的索引
3.系统配置优化
相关操作命令
查询服务器默认缓存区大小
mysqld --verbose --help命令生成所有mysqld选项和可配置变量的列表
SHOW VARIABLES;
查看当前运行的mysql服务器实际运行的值
SHOW STATUS；
运行服务器的统计和状态指标
系统变量和状态信息
mysqladmin variables
mysqladmin extended-status
优化核心参数
key_buffer_size
MYISAM引擎下面的索引缓存所占内存大小
table_cache
mysql同时打开表的数量
read_rnd_buffer_size
线程的缓冲区，注意不是线程的栈内存；
对GROUP BY或ORDER BY读取数据的时候会暂存读取的数据，
max_connections
最大连接数
默认151
可以适当设置大一些
thread_cache_size
线程池线程大小
查询相关
排序缓冲区
sort_buffer_size
默认为2MB
为排序或者分组的线程设置的缓冲区大小，可以提高排序分组的效率
关联查询缓冲区
join_buffer_size
默认为8Mb
为每个链接做联合查询操作的时候缓冲区的大小，提高关联查询效率
表的缓冲区
read_buffer_size
默认64k
每个线程扫描每一个表的锁分配的缓冲区大小
innerDB相关
innodb_buffer_pool_size
innerDB引擎下表和索引的最大缓存
可以缓存表数据还有索引数据
innodb_flush_log_at_trx_commit
innerDB日志刷盘策略
innodb_log_buffer_size
innerDB的失误日志缓冲区
4.硬件优化
数据分散到多个磁盘
提高搜索效率
将数据分布在多个磁盘上，提高单位时间内可以并行搜索磁盘次数
单个磁盘每秒大约1000次搜索
提高读写吞吐量
提高cpu频率
提高cpu缓存大小
概述：以上四个方向，效果最好的是SQL调优，以此往下效果越来越差，成本越来越高
sql优化实战
limit优化
demo
CREATE TABLE `test_news` (`id` int(11) NOT NULL AUTO_INCREMENT,`title` varchar(100) DEFAULT NULL COMMENT '文章标题',`content` longtext COMMENT '文章内容',`channel` int(11) DEFAULT NULL COMMENT '文章频道',`status` int(11) DEFAULT NULL COMMENT '状态，1正常，0关闭',`create_time` datetime DEFAULT NULL COMMENT '文章发布时间',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=513 DEFAULT CHARSET=utf8;
原始查询方式
select* from test_news limit 10000，10；
查询每页是10条，查询第10000页的数据；这种查询完全就是没有用到索引；需要扫描到10000条数据，再开始查询
优化方式1
select* from test_news where id >( select id from test_news limit10000,1) limit 10；
解析
1.子查询select id from test_news where limit10000,1；只查询一个id，根据主键就能查询到，不用回表查询；
2.拿到查询出来的id，获取到后面10个id，再用这10个id直接可以直接回表拿到所有的数据；
优化方式2
select* from test_news a join ( select id from test_news limit10000,10) b on a.id = b.id ；
解析
相当于自关联的方式获取到相应的id；
再通过id回表获取所有的数据
分组取前几条
demo
CREATE TABLE `test_news` (`id` int(11) NOT NULL AUTO_INCREMENT,`title` varchar(100) DEFAULT NULL COMMENT '文章标题',`content` longtext COMMENT '文章内容',`channel` int(11) DEFAULT NULL COMMENT '文章频道',`status` int(11) DEFAULT NULL COMMENT '状态，1正常，0关闭',`create_time` datetime DEFAULT NULL COMMENT '文章发布时间',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=513 DEFAULT CHARSET=utf8;
SELECT a.id, a.title, a.channel,a.create_time FROM test_news AS a LEFT JOIN test_news AS b ON a.channel = b.channelAND a. STATUS = b. STATUS AND a.id < b.id WHEREa. STATUS = 1 GROUP BY a.channel, a.id HAVINGcount(1) < 5 ORDER BY a.channel ASC, a.id DESC;
having
demo
sELECT CLASS,SUM(TOTAL_SCORES) FROM student_score GROUP BY CLASS HAVING SUM(TOTAL_SCORES)>200;
如何分析并解决 MySQL 慢查询日志中出现的性能瓶颈？
1.需要开启慢查询日志
2.收集慢查询的sql
关注执行时间，扫描行数
3.使用解释器分析执行计划
4.优化索引，sql，表结构，缓存，配置
数据库引擎
MyISAM 和 InnoDB 的区别？
事物
MyISAM：不支持事务。
InnoDB：支持事务（ACID），提供 BEGIN/COMMIT/ROLLBACK。
锁机制
MyISAM：表级锁（读锁、写锁），并发性能较差，适合读多写少的场景。
InnoDB：行级锁（也有表级锁），支持更高的并发，适合读写混合场景。
外键支持
MyISAM：不支持外键。
InnoDB：支持外键约束（FOREIGN KEY）。
崩溃恢复
MyISAM：崩溃后容易损坏，需要 myisamchk 修复，可靠性差。
InnoDB：有 redo log/undo log，支持崩溃恢复，可靠性更好。
存储结构
MyISAM：表文件由三个文件组成：
.frm（表结构）
.MYD（数据）
.MYI（索引）
InnoDB：
.frm（表结构）
数据和索引存储在共享表空间（ibdata）
索引实现
MyISAM：非聚簇索引，索引文件和数据文件分开，索引存放的是数据的物理地址。
InnoDB：聚簇索引（Clustered Index），主键索引和数据存储在一起，辅助索引存储主键值。
全文索引
MyISAM：较早支持全文索引（FULLTEXT）。
InnoDB：MySQL 5.6 之后才支持全文索引。
存储空间
MyISAM：相对较小，数据压缩能力强。
InnoDB：存储空间占用大，维护额外事务和索引开销。
适用场景
MyISAM：以读为主的应用，数据修改不频繁，日志、报表系统。
InnoDB：需要事务、安全性高、并发读写频繁的场景，绝大多数 OLTP 系统。
数据库设计优化
分库分表
分库分表：大规模数据库扩展的常用手段，主要目的是解决单库性能瓶颈、存储容量限制以及高并发问题
分库分表策略分类
垂直拆分
按照不同的业务来拆分库表
方式：
按功能拆表：例如把用户表、订单表、商品表拆到不同的库中。
按字段拆表：把大表的热点列和非热点列拆开，例如把 BLOB 字段单独拆表。
优点：
减少单库表大小，提高查询性能。
不同业务模块独立部署，便于管理。
缺点：
跨库查询复杂（需要应用层或中间件处理）。
水平拆分
按行拆分，把同一张表的数据按某个规则分到不同的库或表中。
方式：
按用户 ID 分库：user_id % 4 → 分到 4 个库
按时间分表：按月份/年份拆分表，如 order_202509, order_202510
优点：
单表行数减少，提高查询和写入性能。
支持数据分布式存储，便于扩展。
缺点：
跨分片查询复杂，需要中间件或应用层聚合。
读写分离
主库写，多个从库读
其他优化点
缓存
查询优化，表结构，mysql系统调参，硬件优化
故障与恢复
数据库宕机如何恢复？
数据库宕机可能是
硬件故障（磁盘坏、服务器死机）
软件异常（MySQL crash、操作系统异常）
误操作（误删表/数据）
解释
宕机 = 数据库不可用
仅仅是物理进程挂掉，也包括：
• 数据丢失或不可访问
• 大量锁导致响应慢
• 系统异常导致无法启动或服务挂起
• 数据丢失或不可访问
• 大量锁导致响应慢
• 系统异常导致无法启动或服务挂起
解决方式
从备份恢复
使用之前做的全量备份恢复数据库
使用日志恢复
利用 redo log / binlog / undo log 恢复事务未提交或丢失的数据
增量恢复
从最近一次备份 + 增量 binlog 恢复
适合恢复到宕机前的最新状态
冷备份与热备份的区别？
冷备份
定义：
在数据库 停止运行的情况下，对整个数据库文件系统进行备份
特点：
数据文件状态静态，备份时无需考虑并发事务
恢复简单，直接复制回原路径即可
缺点
需要停库，业务不可用
热备份
定义：
数据库运行中也可以进行备份，在线备份
实现方式：
MySQL：mysqldump、mysqlpump、Percona XtraBackup
支持增量/全量备份
特点：
业务不停机
需要考虑备份期间的事务一致性
复杂度高，需要 redo/undo 日志或 binlog 协助保证一致性
优点
在线备份，不影响业务
缺点
备份复杂，恢复时可能需要 binlog 回放
备份策略：
• 全量备份周期：每日/每周
• 增量备份周期：每小时或更频繁
• binlog 保留：保证可恢复到任意时间点
• 全量备份周期：每日/每周
• 增量备份周期：每小时或更频繁
• binlog 保留：保证可恢复到任意时间点
在实际情况中，如果发现线上环境mysql的cpu使用率过高，如何处理？
立即
在应用层针对非核心业务做限流和熔断处理
前提是有对应的限流和熔断措施
紧急情况
通过命令找到对应的长sql，然后杀掉长sql
临时
降连接、调参数
过渡
读写分离 / SQL 黑名单
后续
优化 SQL / 索引 / 参数
mysql缓存
mysql缓存查询到的数据永远是最新的数据，如果中件表有发生变化，则该缓存相关的数据就会被清空
缓存命中率比较低，最理想的情况下命中率最高13%
缓存工作
查询必须是完全相同的（逐字节相同）才能被认为是相同的
同样的字符串由于其他原因也可能会被认为是不一样的
缓存相关查询
指定从缓存中查询
SELECT SQL_CACHE id, name FROM customer;
前提是缓存是出于开启的状态
query_cache_type系统变量的值是ON或DEMAND，查询结果被缓存
指定不从缓存中查询
SELECT SQL_NO_CACHE id, name FROM customer;
缓存相关参数
have_query_cache
表示当前是否有使用缓存
query_cache_size
查询缓存大小
如果设置为0，则认为是禁用缓存
query_cache_limit
被缓存的查询结果最大值
默认值1MB
query_cache_min_res_unit
系统变量给查询缓存分配最小值
默认值是4KB
mysql异样工作场景
mysql链式复制
主从复制中，从机又是另外一台的主机；
从服务器本身也可以当做主服务器
MySQL客户端程序和实用工具
数据库中间件
mycat
Mycat 是一个 开源的数据库中间件，主要用于 分库分表、读写分离、SQL 路由。
Mycat 需要单独部署，独立的数据库中间件服务
核心作用
解决单库单表容量瓶颈，实现水平扩展和高可用。
Mycat 的原理
SQL 拦截与路由
客户端发起 SQL 请求 → 连接 Mycat
Mycat 拦截 SQL 并解析语法
根据 配置的分库分表规则，将 SQL 路由到对应物理库/表
Mycat分片策略
水平拆分（Sharding）：
按 ID、范围或哈希拆表
垂直拆分（Vertical Sharding）
按业务模块拆库
可以组合使用（垂直 + 水平）
聚合结果
对跨库/跨表查询，Mycat 将结果在中间件层进行聚合、排序、分页，返回给客户端
支持特性
读写分离
分布式事务（XA、柔性事务）
SQL 路由、结果合并
兼容 MySQL 协议，应用无需修改
使用场景
大数据量水平拆分
读写分离
分布式事务
业务模块拆分
Sharding-JDBC
Sharding-JDBC 的定位
轻量级 Java 分库分表中间件，工作在 JDBC 层，无需独立服务
核心功能
分库分表
读写分离
分布式事务（XA/柔性事务）
目标：让应用透明访问分库分表数据库
Sharding-JDBC 原理
SQL 拦截与路由
1. 应用依赖 Sharding-JDBC 的 JDBC 驱动或 DataSource
2. Sharding-JDBC 拦截 SQL
3. 根据配置的 分库分表策略 解析 SQL 的表和字段
4. 将 SQL 路由到对应的物理库/表
分片策略
水平分表（Sharding）：按 ID、哈希、范围拆分
垂直分表（垂直拆分）：按业务模块拆表
支持组合拆分策略
聚合与分页
对跨库查询结果，Sharding-JDBC 在应用层进行：
结果合并
排序
分页
对应用透明，无需改动 SQL 逻辑
读写分离
配置主库和从库
写操作路由到主库
读操作按权重路由到从库
canal
开源组件，由阿里巴巴开源
支持 MySQL、MariaDB、PostgreSQL 等
需要独立部署
核心作用
解析 MySQL（或其他数据库） Binlog，实现 数据增量同步
常用于
数据库同步到缓存（Redis）
数据库同步到搜索引擎（Elasticsearch）
数据库同步到数据仓库 / 消息队列（Kafka、RocketMQ）
Canal 原理
模拟 mysql从机，连接主机
获取到主机的binlog日志，解析binlog日志
分发增量数据
将解析后的变更数据发送到
消息队列（Kafka / RocketMQ）
下游数据库
搜索引擎（ES）
使用场景
缓存同步
数据库更新 → Canal 捕获 → 同步到 Redis
避免应用层直接操作缓存
搜索引擎同步
数据库更新 → Canal 捕获 → 同步到 Elasticsearch
实现数据库与 ES 实时一致
数据仓库 ETL
Canal 实时捕获变更 → 发送到 Kafka → 数据仓库（Hive / ClickHouse）
微服务异步通信
数据变更事件驱动业务流程
分库分表
sharding-jdbc
oneProxy
mycat
pg
0 条评论
下一页
