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