mysql知识点整理
2022-10-20 18:27:55 0 举报AI智能生成
涉及mysql基础：连接，三范式；以及一些区别项：innodb和myIsam存储引擎的区别；索引、锁、日志、mvcc、事务、分库分表等重要知识。面试必备/背
作者其他创作
大纲/内容
慢sql优化
避免不必要的列
索引优化
尽量使用覆盖索引
尽量使用联合索引
不在索引上、列做函数计算
尽量不使用!=和<>
join优化
小表驱动大表
优化子查询
适当做冗余
不用join太多表
排序优化
利用索引字段扫描的顺序排序
union优化
条件下推
每个union都加上查询条件
日志
分类
错误日志
对mysql启动、关闭、运行过程做了记录，帮助mysql定位问题
慢查询日志
用来记录超过定义时长的查询语句的
通过慢查询，可以查找出哪些查询语句的执行效率很低
一般查询日志
记录了所有数据库请求信息
binlog二进制文件
记录DDL、DML语句，除了select和show之外
以事件形式记录，并保存在二进制文件
使用场景
主从复制
恢复数据
innodb引擎特有
redo log重做日志
mysql数据以页为单位，redo log记录在某个数据页上做的修改
让mysql拥有崩溃恢复能力
比如mysql实例挂了或宕机，重启时，innodb存储引擎使用redo log 恢复数据
保证数据的持久性与完整性
undo log回滚日志
作用：对数据进行回滚
当事务对数据库进行修改，innodb引擎不仅会记录redo log,还会生成对应的undo log,
如果事务执行失败会调用rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前
bin log和redo log的区别
存储引擎
binlog记录所有与数据库有关的日志记录，包括innodb和myisam等存储引擎
redo log只记录innodb引擎的日志
内容不同
binlog记录关于一个事务的具体操作内容，是逻辑日志
redo log记录的是关于每个页（page)的更改的物理情况
写入时间不同
bin log是事务提交前写入
redo log在事务执行过程中，会不断写入
写入方式不同
binlog是追加写入的，不会覆盖
redo log是循环写入和擦除的，会覆盖
redo log如何写入磁盘
redo log缓冲区
redo log不是直接落入到磁盘的，而是先写到缓冲区
刷盘时机
缓冲区空间不足时
写入容量占到总容量大约一半时
事务提交时
后台线程输入时
正常关闭服务器
磁盘满时
命令执行顺序
from
on
join
where
group by
having
select
distinct
order by
limit
最左前缀原则
MVCC
多版本并发控制
维护数据历史版本
关键点
隐式字段
事务id
回滚日志指针
undo log
版本链
快照读&当前读
Read View
生成时机
读已提交
每次读取数据前都生成一个read View
不可重复读
第一次读取数据时生成一个read View
内容
m_ids：活跃事务id列表
min_trx_id：最小的活跃事务id
max_trx_id：下一个事务id
cretor_trx_id:生成readView的事务
判断逻辑
作用
读已提交、可重复读都只能读取已经提交的事务所修改的记录
版本链中某个版本的修改未提交，则这个版本不能被读取
需要确定版本链中哪个版本能被当前事务读取
流程
每次变动都把undo日志记录下来
并用回滚指针指向undo log
对该条记录的修改日志串联起来，就是版本链
读写分离
主从架构
一主一从
一主多从
基本实现
数据库服务器搭建主从服务器
数据库主机负责读写操作，从机负责读操作
主机通过复制将数据同步到从机
业务服务器将写操作发给主机，读操作发给从机
分配服务器
程序代码封装
抽象一个数据访问层
实现读写操作分离和数据库服务器连接的管理
中间件封装
主从复制
主机数据写入，更新bin log
主机dump线程把bin log推给从机
从机IO线程接收主机binlog，记录到relay log
从机sql线程读取并执行relay log，并记录自己的bin log
复制延时
原因
大并发更新操作，从机读取的binlog线程仅一个
某个sql在从服务器上执行时间稍长，或者某个sql要进行锁表，导致主机的sql大量积压，未被同步到从机
解决
根据业务指定主从服务器读取数据
实时性高、重要业务指定在主服务器读取
实时性不高，非关键业务指定在从服务器读取
连接
内连接
外连接
左连接
右连接
交叉连接
笛卡尔积
三范式
数据表不可再分
非主键需完全依赖于主键，不能不分依赖
比如订单表里存了商品信息（商品价格、商品类型），那就需要把订单id和商品id作为联合主键，才能符合第二范式
非主键只能依赖于主键，而不依赖非主键
比如订单表存用户地址信息
作用
控制数据库冗余，节省空间
一般会反范式，冗余数据，提供性能。空间换时间
区别
varchar和char的区间
varchar
可变长字符串
最多存放字符个数，65532
存取慢，因为长度不固定
不占据多余空间
char
定长字符串，固定长度
最多存放字符个数，255
存取快
占据空间
blob和text
blob
存储二进制数据
没有字符集
text
存储大字符串
有字符集，且根据字符集的校对规则进行排序和比较
datetime和timestamp
相同
存储时间的表现格式一致
YYYY-MM-DD HH:MM:SS
都包含日期和时间
都可以存储微秒
不同
日期范围
datetime是1000-01-01 ~ 9999-12-31
timestamp是1970-01-01 ~ 2038-01-09
时区
datetime与时区无关
timestamp存储和显示都依赖于时区
存储空间
datetime是8字节
timestamp是4字节
默认值
datetime默认是null
timestamp是当前时间
in和exists
逻辑不同
in是外表和内表哈希连接
exists是对外表loop查询，每次loop再对内表查询
性能
两表数据量差不多，in和exists性能差不多
内表大，用exists
内表小，用in
not in 和not exists
not in全表扫描，没用索引
not exists走索引
union和union All
union all不会合并重复的记录行
union效率比union all高
count
count(1）
忽略所有行，1代表代码行，不忽略null
count(*)
包括所有列，不忽略null
count(列名)
只包括列名那一列，统计结果忽略null
执行速度
有主键时，用count(主键)
没有主键，用count(1)
表中只有1列，用count(*)
myIsam和innodb
存储结构
每个myIsam在磁盘中存储为3个文件
innodb 所有表存储在一个数据文件（可能是多个，或者是独立的表空间文件）
innodb受限于操作系统文件大小。一般是2GB
索引
myIsam是非聚簇索引，结构是B树
Innodb是聚簇索引，结构是B+树
效率
外键支持
myIsam不支持外键
innodb支持外键
事务支持
myIsam不支持事务
innodb支持事务，具有事务、回滚和崩溃修复能力的事务安全特效
表行数
myisam保存了表的总行数，如果select count(*) from 会直接取这个值。但是加条件的话，会遍历整张表
innodb会遍历表得到表行数
主键必须
myisam允许表没有任何索引和主键
innodb如果没有设定主键或者非空唯一索引，就会自动生成一个主键，数据是主索引的一部分，附加索引保存的是主索引的值
最小粒度锁
myIsam仅支持表锁，更新时会锁住整张表，导致其它查询和更新都阻塞
innodb支持行锁
索引
分类
基本使用
主键索引
innodb主键是默认索引
数据列不允许重复
不允许null
一个表只能有一个主键
唯一索引
数据列不允许重复
允许null
一个表允许多个列创建唯一索引
普通索引
无唯一性限制
允许NULL值
组合索引
多列值组成一个索引
组合搜索
效率大于索引合并
覆盖索引
避免回表
数据结构
哈希索引
B树索引
B+树索引
物理存储
聚簇索引
一张表只能有1个聚簇索引
数据和键值在一棵树上，叶子节点就是数据
物理顺序按照索引排序
非聚簇索引
一张表可以有多个非聚簇索引
数据和键值不在一颗树上
物理顺序不按照索引排序
为什么能加快查询
mysql通过BTREE算法生成索引文件，查找数据库时，找到索引文件进行遍历
在较小的索引数据里查找，然后映射到对应的数据，能大幅度提升查找效率
B+树的结构
一个父节点可以有多个子节点，树的高度最高为3层，降低IO
非叶子节点不存储真实数据，存储指引搜索方向的数据项
扫表能力强，如果要全表扫描，只用扫描叶子结点，不需要遍历整颗树
效率、IO稳定，每次都是在叶子结点拿到数据
磁盘读写能力强，因为根节点、枝节点不保存数据，所以一个节点能够保存更多关键字，一次磁盘加载的关键字更多，IO次数更少
叶子结点之间使用双向链表
支持范围查找
排序能力更强，因为叶子结点有下一个数据区的指针，数据形成链表
二叉树
二叉树会退化为链表，不稳定
平衡二叉树
每个节点只能存储一个键值和数据，磁盘IO次数多
B+树可以存放更多节点，树高度降低，减少磁盘IO
hash索引
B+树可以范围查询，hash不支持
B+树可以排序，hash不支持
B+树支持联合索引最左侧原则，hash不支持
hash的等值查询效率高于B+树
B+树能模糊查询，hash不适合模糊查询
锁
加锁机制
乐观锁
悲观锁
锁粒度
行锁
开销大
加锁速度慢
锁冲突发生概率小
锁定粒度小
并发度高
页锁
介于行锁和表锁之间
表锁
开销小
加锁快
锁冲突概率大
锁粒度大
并发低
锁模式
记录锁
使用唯一性索引（唯一索引和聚簇索引）等值查询且精准匹配到一条记录时
间隙锁
范围查询
等值查询
且没有命中记录时
解决幻读问题
next-key临键锁
记录锁和间隙锁的结合
范围查询且命中部分
唯一性索引等值查询，会退化成记录锁
没有匹配到任何记录时，退化成间隙锁
解决幻读问题
意向锁
为了支持innodb的多粒度锁
解决表锁和行锁共存问题
表上有意向排它锁之后，其它事务去加锁就会阻塞
插入意向锁
兼容性
共享锁
排它锁
事务
特性
A原子性
redo log保证
C一致性
undo log
I 隔离性
MVCC实现
D持久性
redo log保证
隔离级别
读未提交
问题
脏读
幻读
不可重复读
实现
读不加锁
读已提交
问题
不可重复读
实现
MVCC
ReadView
每次读取数据都生成一个readView
每个事务只能读取它能看到的版本
可重复读
问题
幻读
实现
MVCC
Read View
第一次读数据生成
每个事务只能读取它能看到的版本
串行化
实现
读写都加锁
分库分表
水平分表
路由方式
范围路由
难点
分段大小选取上
分段太小，导致切分后子表数量过多
分段太大可能导致单表依然存在性能问题
一般建议分段大小在100万到2000万之间
优点
随着数据的增加平滑扩充新表
缺点
分布不均匀
hash路由
难点
初始表数量的选取
表数量太多维护麻烦
表数量太少可能导致单表性能存在问题
用了hash路由后，很难增加子表，所有数据需要重分布
优点
表分布较均匀
缺点
扩充表麻烦
配置路由
路由表
用一张独立的表记录路由信息
垂直分表
问题
跨库join
在业务代码中进行关联数据
适当冗余字段
数据异构，需要跨库join的数据异构到ES
事务
需要分布式事务解决
跨节点的count，order by,group by以及聚合函数问题
数据迁移、容量规划、扩容等问题
ID问题
数据库表被切分，不能再依赖数据库自身的主键生成机制
需要一些手段保证全局主键唯一
设置自增步数，三张表步长是3
UUID，但是不连续的主键插入会导致严重的页分裂，性能差
分布式ID，雪花算法
0 条评论
下一页
