mysql
2021-11-16 09:17:06 0 举报直击面试--mysql 全景 索引优化、B+树、mysql 优化
作者其他创作
大纲/内容
1.基本的一些概念
表
字段
子主题
索引
b+tree 索引
B+树
原因：层数一般都是维护在2-4层
并且保持顺序性来提高区间查询的效率
或者是二叉搜索树特性
左边节点比右边的节点小
索引 主节点记录大小
只有叶子节点会记录索引的字段的数据
随着数据量增加，索引文件也会变大，导致了查询变慢
最后的数据块中保持相邻区块的指针，有点类似一个链表了，方便最后数据查询
索引默认是字典序进行排序的
索引最好设置称为单调递增的
避免出现页分裂、页合并的情况的
每一页是4K
innodb_page_size
唯一索引
不会回表
普通索引
会产生回表，覆盖索引
就是select *
会很容易导致回表
myIsam
索引存储直接是行位置，所以不存在这个回表
适用与select 密集型的
组合索引
最左匹配原则
hash 索引
全文索引
FullText
搜索引擎---倒排索引
就是要考虑索引字段的大小
磁盘占用空间，谁小用谁，索引文件太大的话，也会导致索引变化，尽量索引字段要小一些
习惯性要去看sql的执行计划
极可能在const-range
避免all 出现filesort
公司这边的例子是啥？
索引下推
就是我们在做sql 优化的时候，一定要尽量降低最后的回表的数据
先检索出主键，并且基于当前集合 进行过滤，把主键集合的最小结果集返回，让这个最小的结果集 去进行回表
覆盖索引
基本结构
连接器
分析器
优化器
执行器
存储引擎
提供读写接口
基本连接数量有多少
决定于服务器
内存
可用的文件描述符
每个连接占用的内存
每个连接占用的时间
一般msql 在正常的情况下，就是负载比较低，响应比较快的情况下，至少是500-1000个连接，最高是可以支持一万个 每秒中
慢查询日志
主要包含
查询时间
获取锁的时间
查询的行数
发送给用户的行数
常规日志
默认关闭
按sql 接收的顺序写
主要记录mysql的操作日志
binlog
就是主要是保持是对于数据的更新操作
用处
数据备份
主备模型的时候，同步的话就是同步binlog
数据恢复
在数据异常挂机的时候，是通过binlog 来恢复时间点的数据
写入的时机
sql 之后完成之后，但是锁还没有释放
记录的格式
statement
row
如果事务隔离基本是
read_committed
read_uncommitted
mixed
追加写的方式
redolog
用来记录事务执行过程的
持久性
主要是体现在宕机的情况下，可以通过redolog 保证，事务持久性，就是在数据还没有刷盘
WAL
write ahead log
预写日志
当发生数据修改的时
innodb 先把记录写到 redo log 中去，再去更新内存数据，此时 更新操作才算完成
存储引擎自己再找个时机去刷盘
因此在每次机器异常的时候，可以从redo log 中回复，crash-safe
固定大小，循环写入
满了就要先把前面的log 写入数据库，然后在重新写入
undolog
用来实现事务的回滚的，是确保事务的原子性的
undolog 是逻辑日志
如果是删除，就在日志中写入一条插入语句
如果是插入，就会在日志中写入一条删除语句
如果是更新，就插入一条相反的更新语句
mvcc
多版本并发控制
存储引擎的一些区别
myisam
select 密集型de
insert/update 密集型的
子主题
sql 执行顺序
了解这个执行顺序 才可以更好的去做sql 优化
2.具体的一些实际的引用
表设计
三范式
1范式
每个字段都是原子的，不能再分
比如用户表
id
user_name
2范式
要求 非主键字段是要依赖与主键的
比如用户表的基础上，添加了角色
id
user_name
role_id
3范式
在上面，再把 role ID 和id 关联抽离出来
添加上user_role
id
user_id
role_id
反范式
添加一些冗余的字段，来满足需求查询，提高查询的效率，降低join的次数
行转列
join
having
group by
3.mysql 的优化
单表优化
实际的经验就是
1.加索引
单个字段添加索引
2.利用索引查询主键，然后在查询数据
避免数据大量回表操作
3.把一些不常用的索引删除掉
4.sql 优化
有些时候，要么出现了大量的回表
要么出现了索引无效的情况
比如说是 、索引列参与了计算
复合索引，查询条件没有做到最左原则，就是最左边的查询条件 和索引最左边的字段不吻合
出现or
like 用了左%
经常用的排序字段没有添加索引
explain sql分析
需要把性能 提高到const -range 阶段
避免全表扫描
查询时间太久了，会导致，数据库等待的时间太长，引起数据库卡顿，进而引发了生成问题
梳理业务、把一些不要的字段拿掉
数据量是在用户的实名认证记录
300来万把。
按时间排序查询
出现了filesort
添加了一个实名认证的时间，用户ID的一个联合索引
先通过时间索引查询出结果集合的对应的主键ID，再通过主键ID的方式来完成剩余的 数据组建
避免出现全表扫描
尽可能的走主键索引
大型的性能优化
单库分表
分表之后怎么查询数据库
分库分表
分表之后怎么查询数据库
主要是涉及跨区查询
按时间
按其他的字段
分布式一致性ID
redis 生成
zookeeper 临时节点生成
需要mysql的代理
集群
为什么要集群
防止出现单点故障
主备
读写分离
主从
按业务分库
分布式查询
这个优化之后，出现问题
1.怎么定位表
单库分表
2.怎么定位数据库
分库分表
锁
行锁
select * from a where id =1 for update;
粒度小，并发量大，容易死锁
表锁
乐观锁
悲观锁
如何避免死锁
意向锁
就是解决后续事务需要添加表锁的情况下，当前事务是行锁，会让后续事务进行全表查询，来查询具体行锁的位置，导致效率底下，并且无效的占用cpu、内存资源
那么通过在进行行锁前添加意向锁，后续查询的到存在意向锁，呢么就直接进入组素阶段，等就好了。
表级别的锁
感觉mysql 不是瞎几把优化，基本上应该是从开发的时候，就是要定好格调，那些是经常需要用到的查询、排序、字段。不然数据量大起来的，就是是一个费时费力的过程。
先做好单表的优化
索引优化， 避免回表查询、索引下推。这些能解决绝大部分的问题
单表做不了，那么就考虑集群
公司钱少，数据不是很重要的
集群不起来的。
先搞个临时节点，做同步，切换。 把数据库的表，从新规划下索引、然后再把数据同步回来。
是在是要考虑 集群了
也是先从业务出发
是主从、还是需要主备、还是一主多从、多主、多从
那么就是还要把查询规划好
要不要用中间件
不用，就是在业务上通过硬编码的方式来处理
通过市场来分开查询
通过时间来分开查询
分页的时候，处于跨区的范围的，就是拆成两条sql 查询，再合并在一起
怎么识别读库的？
自己用Spring 实现一下就好了。多数据源嘛。
不难
读取使用myIsam 引擎
这个会让查询提速
因为myIsam的索引，是直接指向数据位置的
降低IO操作
binnodb 执行的主键，在从主键指引到数据位置
用中间件的好处，就是不用写代码
想用mycat
也可以做负载均衡
也方便后面添加新库进入集群
垂直分表，对于业务的代码的修改是很大很大的
直接可能会重普通的数据库事务，演变成分布式事务
一般是一主、多从
涉及到整体的架构
应该多个redis--mysql 做主从
集群一般都是性能有需求，那么应该是多个redis 进行缓存 提高性能
然后 通过后面的简单轮询redis 数据，进行数据同步上去
不然直接打到mysql 是不合理的
应该坚持mysql 写少，读多。
4.底层的一些原理
Sql 解析器
5.mysql的事务
ACID
原子性
undo log
回滚日志，要么成功，要么失败
持久性
redo log
记录已经提交的事务的是日志
追加写的模式，保证顺序IO写入，高效稳定
隔离性
通过隔离级别
读未提交
幻读、脏读、不可重复读
读已提交
解决脏读
可重复读
解决脏读、不可重复读、幻读
就是在快照度的情况下避免了幻读
不能把快照读和当前读的结果进行比较，因为是两种不通的方式。
串行化
一致性
这个是目标，上面的保证这个目标的手段，加上就是事务提交后，没有违反数据库的约束这些。
职业：本科
0 条评论
下一页
