Home 数据库 常见的数据库优化方法

常见的数据库优化方法

0 83

正文

数据库调优其实一般情况都是我们的SQL调优,SQL的调优就可以解决大部分问题了,当然也不排除SQL执行环节的调优。

数据库的组成可能很多小伙伴都忘记了,那我们再看一遍结构图吧。

我们所谓的调优也就是在执行器执行之前的分析器优化器阶段完成的,那我们开发工作中怎么去调优的呢?

帅丙一般在开发涉及SQL的业务都会去本地环境跑一遍SQL,用explain去看一下执行计划,看看分析的结果是否符合自己的预期,用没用到相关的索引,然后再去线上环境跑一下看看执行时间(这里只有查询语句,修改语句也无法在线上执行)。

遇SQL不决explain,但是这里就要说到第一个坑了。

排除缓存干扰

因为在MySQL8.0之前我们的数据库是存在缓存这样的情况的,我之前就被坑过,因为存在缓存,我发现我sql怎么执行都是很快,当然第一次其实不快但是我没注意到,以至于上线后因为缓存经常失效,导致rt(Response time)时高时低。

后面就发现了是缓存的问题,我们在执行SQL的时候,记得加上SQL NoCache去跑SQL,这样跑出来的时间就是真实的查询时间了。

我说一下为什么缓存会失效,而且是经常失效。

如果我们当前的MySQL版本支持缓存而且我们又开启了缓存,那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的,大家也看到我们的结构图了,一个请求会先去看缓存是否存在,不存在才会走解析器。

缓存失效比较频繁原因就是,只要我们一对表进行更新,那这个表所有的缓存都会被清空,其实我们很少存在不更新的表,特别是我之前的电商场景,可能静态表可以用到缓存,但是我们都走大数据离线分析,缓存也就没用了。

大家如果是8.0以上的版本就不用担心这个问题,如果是8.0之下的版本,记得排除缓存的干扰。

Explain

最开始提到了用执行计划去分析,我想explain是大家SQL调优都会回答到的吧。

因为这基本上是写SQL的必备操作,那我现在问大家一个我去阿里面试被问过的一个问题:explain你记得哪些字段,分别有什么含义?

当时我就回答上来三个,我默认大家都是有数据库基础的,所以每个我这里不具体讨论每个字段,怕大家忘记我贴一遍图大家自己回忆一下。

那我再问大家一下,你们认为统计这个统计的行数就是完全对的么?索引一定会走到最优索引么?

当然我都这么问了,你们肯定也知道结果了,行数只是一个接近的数字不是完全正确的,索引也不一定就是走最优的,是可能走错的。

我的总行数大概有10W行,但是我去用explain去分析sql的时候,就会发现只得到了9.4W,为啥行数只是个近视值呢?

看过基础章节的小伙伴都知道,MySQL中数据的单位都是页,MySQL又采用了采样统计的方法,采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。

我们数据是一直在变的,所以索引的统计信息也是会变的,会根据一个阈值,重新做统计。

至于MySQL索引可能走错也很好理解,如果走A索引要扫描100行,B所有只要20行,但是他可能选择走A索引,你可能会想MySQL是不是有病啊,其实不是的。

一般走错都是因为优化器在选择的时候发现,走A索引没有额外的代价,比如走B索引并不能直接拿到我们的值,还需要回到主键索引才可以拿到,多了一次回表的过程,这个也是会被优化器考虑进去的。

他发现走A索引不需要回表,没有额外的开销,所有他选错了。

如果是上面的统计信息错了,那简单,我们用analyze table tablename 就可以重新统计索引信息了,所以在实践中,如果你发现explain的结果预估的rows值跟实际情况差距比较大,可以采用这个方法来处理。

还有一个方法就是force index强制走正确的索引,或者优化SQL,最后实在不行,可以新建索引,或者删掉错误的索引。

SIMILAR ARTICLES

0 70

发表评论

发表评论