Mysql面试全解
2021-07-08 11:07:49 4 举报AI智能生成
Mysql重要概念、常见问题、常见面试点全罗列
作者其他创作
大纲/内容
更新Sql如何执行
日志
innodb引擎层日志：redo log
Mysql server层日志：binlog
WAL：whrite ahead logging
两阶段提交
解决Mysql问题经历
索引统计信息cardinate不准确导致的问题，通过analyze table
常见问题
如何安全的给表加字段？
怎么解决热点行更新导致的性能问题呢？
并发时如何安排事务语句？
分析在各隔离级别下，更新没加索引的行时锁的表现？
选择唯一索引还是普通索引？
如何给字符串加索引？
为什么删除表数据，表文件大小不变？
count(*)还是count(1)还是count(id)
为什么Mysql为抖一下？
索引失效案例
自增主键为什么会不连续？
常用概念
explain
rows显示最多扫描多少行
limit
执行器判断是否需要next
cardinate
基数统计(区分度)：通过抽样数据页平均值计算得出，
数据更新量到达一定阈值会触发重新统计
数据更新量到达一定阈值会触发重新统计
InnoDB刷脏页机制
后台线程自动刷
数据页LRU淘汰时刷
刷脏页两种极端场景：
1.一个查询要淘汰的脏页太多，导致响应时间明显边长；
2.redo log写满，更新全部堵住；
1.一个查询要淘汰的脏页太多，导致响应时间明显边长；
2.redo log写满，更新全部堵住；
刷脏页控制策略
innodb_flush_neighbors连坐机制
InnoDB缓冲池
Mysql的核心理念就是能用内存就用内存，无论是读写数据还是log，
因此buffer pool实际上就是一个内存管理器的理念存在的
因此buffer pool实际上就是一个内存管理器的理念存在的
online DDL
order by
排序机制
排序优化
filesort
全字段排序
rowid排序
temporary
磁盘临时表
内存临时表
MRR
join
一条查询Sql工作过程
连接器
与客户端建立链接、获取权限、管理维持链接，连接默认8小时，断开需要重连
尽量使用长连接
Mysql执行过程中的临时内存建立在连接对象内，可能导致OOM
Mysql查询缓存
分析器
词法分析
语法分析
优化器
作用：在表里有多个索引的时候，决定使用哪个索引
选错索引的解决方法：
1：强制走索引；
2：通过修改sql引导优化器；
3：增加或删除索引绕过问题；
1：强制走索引；
2：通过修改sql引导优化器；
3：增加或删除索引绕过问题；
执行器
limit 是执行器得到结果之后判断是否需要 Next，不能决定优化器的索引选择
事务隔离
事务隔离级别
ru：读未提交
rc：读已提交
每个语句都生成一致性视图
语句执行过程中加上的行锁，在语句执行完成后，
就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交
就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交
rr：可重复读
场景：如对账期间不希望看到最新的数据
1.核心：一致性读，根据row trx_id和一致性视图确定数据版本的可见性；
2.事务更新时当前读；
3.当前行的行锁被占，需要等待锁；
2.事务更新时当前读；
3.当前行的行锁被占，需要等待锁；
事务开启时生成一致性视图
可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，
都是在事务提交或者回滚的时候才释放的
都是在事务提交或者回滚的时候才释放的
serilazable：串行化
事务隔离的实现
每条记录被更新时都会记录一条回滚记录，
其他事务要看到可重复读的结果都是通过回滚记录回滚得到的
其他事务要看到可重复读的结果都是通过回滚记录回滚得到的
MVCC：多版本并发控制
数据库的每条记录可能存在多个版本
用于支持RC和RR隔离级别的实现
没有物理结构，用于在事务期间决定“能看到什么数据”
快照是基于整库的，基于“所有数据都有多个版本”特性来秒级创建
实现：InnoDB为每个事务创建一个数组，用以保存这个事务启动瞬间当前正在活跃的所有事务id
数据版本的可见性规则
更新数据都是先读后写的，不能在历史版本上去更新，所以这个读只能读最新值，称为当前读；
select ... lock in share mode;
select ... for update;
update
事务id
每个事务都有一个唯一自增的transaction id
1. 每行数据都有多个版本，每次事务更新数据时，会生成新的数据版本
并把trasaction id赋值给该行的数据版本的事务id：row trx_id
2. 新的数据版本可以拿到旧的数据版本；
3. 旧的数据版本是通过新的数据版本+undo log一步一步还原得到；
并把trasaction id赋值给该行的数据版本的事务id：row trx_id
2. 新的数据版本可以拿到旧的数据版本；
3. 旧的数据版本是通过新的数据版本+undo log一步一步还原得到；
生成事务id
1. begin transaction后等到第一个语句执行时生成；
2.start transaction with consistent snapshot 立即生成；
1. begin transaction后等到第一个语句执行时生成；
2.start transaction with consistent snapshot 立即生成；
索引
索引的常见数据结构
Hash表
优势：添加、删除快
劣势：索引无序导致区间查询慢，需要扫全表
适用场景：等值查询
劣势：索引无序导致区间查询慢，需要扫全表
适用场景：等值查询
有序数组
优势：等值查询和区间查询很快，等值查询时O(logn)
劣势：更新慢
适用场景：静态表
劣势：更新慢
适用场景：静态表
搜索树
为什么不用平衡二叉树？
为什么要用N叉树？
InnoDB分叉树是怎么计算的?
B+树
InnoDB索引模型
表按照主键顺序以索引的形式存放，也叫索引组织表
主键索引(聚簇索引)
非主键索引(二级索引)
普通索引
change buffer
唯一索引
回表：先查二级索引在查主键索引，多扫描一次索引树
索引维护
为什么推荐使用主键索引？
索引常见问题
分析sql扫描行数, 表T中k是普通索引
select * from T where k between 3 and 5;
select * from T where k between 3 and 5;
如何避免回表？
覆盖索引
建立联合索引时如何安排字段顺序？
索引长度越大，数据页存放的索引值越少，查找效果越差
最左前缀原则
索引下推
前缀索引
场景：字符串取区分度高的前缀部分作为索引，能够节省空间
前缀索引会影响覆盖索引：
1.通过前缀索引匹配到数据后，需要回表确认字段是否完全一致，
2.覆盖索引的优化就是为了减少回表
1.通过前缀索引匹配到数据后，需要回表确认字段是否完全一致，
2.覆盖索引的优化就是为了减少回表
劣势：增加查询扫描次数
锁
锁范围分类
全局锁(库锁)
使用场景：数据库逻辑备份
FTWRL：flush table with read lock; <->unlock tables；
表级锁
表锁
lock tables T read/write;<->unlock tables;
元数据锁MDL
MDL锁是针对元数据的，无需显式使用，自动添加
读锁不互斥，允许同时DML，阻塞所有DDL操作
写锁、读写锁互斥，只有拥有锁的线程可以DDL、DML，
其他线程阻塞DDL和DML操作
其他线程阻塞DDL和DML操作
申请MDL锁的操作会形成一个队列，
队列中写锁获取优先级高于读锁，一旦线程在申请写锁，后续所有操作都将阻塞
队列中写锁获取优先级高于读锁，一旦线程在申请写锁，后续所有操作都将阻塞
行级锁
间隙锁
gap lock 前开后开区间()
next key lock = gap lock +行锁，前开后闭区间(]
rr级别下加锁原则
结构
表空间
逻辑容器，可以划分为 系统表空间、用户、临时、撤销
nnoDB有两种表空间类型：共享表空间和独立表空间
段
1.段是数据库的分配单位，且不要求段中的区与区必须连续。
2.不同数据库对象以不同的段形式存在，也就是说创建一个表时会创建一个表段，创建一个索引时会创建一个索引段。
3.一个段只能属于一个表空间。
2.不同数据库对象以不同的段形式存在，也就是说创建一个表时会创建一个表段，创建一个索引时会创建一个索引段。
3.一个段只能属于一个表空间。
区
一个区默认分配64个连续的页，1M
页
数据库IO最小单位，默认16K
行
自由主题
0 条评论
下一页
