MySQL 相关最全的知识点
2022-02-21 18:23:53 0 举报AI智能生成
MySQL 相关最全的知识点
作者其他创作
大纲/内容
分库分表
分表 解决单表的压力
Range(按实践范围划分)
使用场景
业务上只查近三个月的数据
缺点
无法解决集中写入瓶颈问题
优点
单表大小可控，天然水平扩展
Hash
使用场景
使用历史数据的场景
要点
选好需要 sharding 的字段
Range + Hash
分库 解决数据库的压力
注意点
分表后唯一ID问题(不能使用自增主键)
时间戳+分库分表的 sharding 字段+随机数
优点
方便，成本低
可排序，时间戳在最前面
自带分库规则，这里自带分库分表的 sharding 字段，所以在查询的时候
无需带上分表的字段，直接用唯一 ID 查就行
无需带上分表的字段，直接用唯一 ID 查就行
UUID
优点
本地生成，不需要 RPC，低延时
扩展性好，基本没有性能上限
缺点
无法保证趋势递增
UUID 过长，不易存储
雪花算法统一生成主键 ID
优点
分布式生成，无单点
趋势递增，生成效率快
缺点
没有全局时钟情况下，只能保证趋势递增
通过 NTP 进行时钟同步时，可能会出现重复 ID
数据间隙较大
proxy 服务+数据库分段获取 ID
分多少表合适
数量为 2 的 N 次方，因为在取模的这种分表模式下
即便是今后再需要分表影响的数据也会尽量小
即便是今后再需要分表影响的数据也会尽量小
全表扫描
查询时没用到 sharding 字段，会导致全表扫描
尽量引导业务作出调整，如果对于报表业务无法
修改，我们可以利用多线程来进行查询并汇总
尽量引导业务作出调整，如果对于报表业务无法
修改，我们可以利用多线程来进行查询并汇总
什么时候分表
某张表的数据量达到千万或者上亿，同时日增数据量在2%以上
数字不是绝对的，关键在于对这张表的写入和查询影响到了正常业务执行
比如查询速度明显下降，数据库整体 IO 居高不下
比如查询速度明显下降，数据库整体 IO 居高不下
数据迁移
分表改造上线后，新产生的数据写入分表
对历史数据操作走老表
当新数据产生足够多时，几乎所有操作都针对新表，再将数据迁移过来
历史数据归档
事务隔离级别
读未提交
没有视图的概念，都是返回最新的
读已提交
有视图的概念，读取最近一次提交的事务的数据
可重复读
有视图的概念，读取开启事务时最近一次提交事务的数据
序列化
事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁
索引
Mysql 中 Innodb 引擎中索引底层用的是 B+树
优化流程
explain
id
select的 Id，有多少个 select 就有多少 id，按照 id 顺序执行（id 越大越优先执行）
select_type
PRIMARY
复杂查询中最外层的 Select
SIMPLE
简单查询，查询不包含子查询和 union
DERIVED
衍生类型表，包含在 from 语句中的子查询，会将其结果存放在临时表中
SUBQUERY
子查询，包含在 select 中的子查询
table
表示 explain 一行正在访问哪个表
type
MySQL决定如何查找表中的行，查找数据行记录的大概范围
字段(优劣程度，依次降低)
system
const
eq_ref
ref
range
index
扫描全表索引，从索引中取所有数据
ALL
全表扫描，从硬盘中读取
possible_keys
key
SQL 执行用到了哪些索引
key_len
ref
rows
扫描多少行记录
extra
排除缓存 sql nocache
看一下行数对不对，不对可以用 analyze table t 矫正
添加索引，索引不一定是最优的，force index 强制走某个索引（不建议使用）
存在回表的情况
覆盖索引避免回表
最左前缀原则
合理安排联合索引的顺序
索引优化器
小表驱动大表
聚集索引(主键索引)
索引的叶子节点存放的一行数据
非聚集索引(非主键索引)
索引的叶子节点存放的是聚集索引的值
索引维护
页满了，页分裂，页利用率下降
数据删除，页合并
自增，只追加可以不考虑页分裂
索引长度
索引选择
普通索引
唯一索引
覆盖索引
最左前缀原则
索引空间问题
MVVC
介绍概念
提高并发的技术，最早的数据库，只有读读可以并发，引入多版本控制以后，只有写写才会相互阻塞
生效条件
隔离级别为 READ COMMITTED
隔离级别为 REPEATABLE READ
存储引擎为 InnoDB
实现原理
每行记录会有两个隐藏列
DATA_TRX_ID
记录最近更新这条行记录的事务 ID，是递增的
DATA_ROLL_PTR
表示指向该行回滚段（rollback segment）的指针
InnoDB 通过此指针找到之前的版本数据
旧版本数据存储在undo log 中
锁
行锁
共享锁
select ... in share mode
排它锁
select ... for update
更新或者删除操作
间隙锁
前开后开区间
间隙锁与行锁合称为next-key lock
前开后闭区间
可重读的隔离级别下才会有
表锁
日志
redo log
WAL 技术，先写日志，再写磁盘
InnoDB 引擎独有的
物理日志，记录在某个数据页做了什么修改
循环写，空间会用完
bin log
整个 Server 层实现的
逻辑日志，记录这个语句的原始逻辑，比如给 ID=2 这一行的 c 字段加 1
追加写，不会覆盖以前的日志
三种格式
statement
记录是 sql 原文
缺点
容易造成主备不一致
row
记录了真实操作的主键 id
缺点
占用空间
mixed
系统自己判断是用 statement 还是 row
undo log
配合 MVCC 记录的是老版本的数据
事务回滚
主备
利用 binlog 日志进行主备同步
问题
主备延迟导致过期读
对于必须要最新结果的强制走主库
0 条评论
下一页
