

首页  思维笔记  详情



mysql

2021-11-16 09:17:06   0  举报





直击面试--mysql 全景索引优化、B+树、mysql 优化

mysql

sql优化

B+树

索引

模版推荐

作者其他创作

大纲/内容

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范式

每个字段都是原子的，不能再分

比如用户表

user_name

2范式

要求非主键字段是要依赖与主键的

比如用户表的基础上，添加了角色

user_name

role_id

3范式

在上面，再把 role ID 和id 关联抽离出来

添加上user_role

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写入，高效稳定

隔离性

通过隔离级别

读未提交

幻读、脏读、不可重复读

读已提交

解决脏读

可重复读

解决脏读、不可重复读、幻读

就是在快照度的情况下避免了幻读

不能把快照读和当前读的结果进行比较，因为是两种不通的方式。

串行化

一致性

这个是目标，上面的保证这个目标的手段，加上就是事务提交后，没有违反数据库的约束这些。

 Collect

Get Started

Mysql全局优化与8.0主从复制原理分析与高可用集群实战

 Collect

Get Started

Mysql是怎样运行的

 Collect

Get Started

折800运用mysql binlog的需求

 Collect

Get Started

知识图谱(RocketMQ.MySQL,JVM,Redis)





0 条评论

下一页