零、为什么要优化

  • 系统的吞吐量瓶颈往往出现在数据库的访问速度上
  • 随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢
  • 数据是存放在磁盘上的,读写速度无法和内存相比

一、观察

MySQL优化≠SQL语句优化,理解这一点非常重要,虽然大部分时候我们都在调优SQL语句。

然而,MySQL的优化却是始于观察,而且有时候观察几分钟,几小时就能得出结论的,可能要观察一天以上。

这么做的目的很明显,就是为了帮助我们定位问题所在。
比如:MySQL的负载会在固定的某个时间节点突然暴涨,或者一请求某几个页面就产生了较为明显的延迟,甚至影响了随后的各个请求等。

观察的手段有多种多样,阿里云有强大的RDS控制台,也可以自建一套监控平台,最次的就是临时跑个shell脚本,收集MySQL运行状况。
观察的指标也不尽相同,最知名的 show status 命令列出的指标就能不下200个,所以观察也要有所取舍。
经常受人关注的指标有当前连接数以及最大连接数,当前运行的线程数,慢查询数量等。

二、分析

将观察的结果做进一步分析,也就形成了不同的解决思路。

可能是某个时间节点缓存失效,导致MySQL的负载激增,可以设法将缓存失效的时间节点尽可能均匀的分摊在一天24小时中,或者找个访问量较少的时段刷新缓存。

可能是SQL语句存在潜在问题,在某些情况下会有性能问题,可以用 show full processlist 定位是哪个库,也可以开启慢查询,直接定位到有问题的SQL语句,使用explain分析语句执行计划。可能加个索引能解决问题,也有可能join太多表,需要拆分查询,也有可能单表体量过大,要拆表了。

可能是机器本身性能问题,所谓“巧妇难为无米之炊”,这个时候要考虑扩容了。

三、解决

在分析阶段已经提及了大部分解决手段了,最后总结一下:

1、引入缓存,当然,这是一把双刃剑,要想用的恰如其分,还是需要一定的功力。缓存也分两方面的,一方面是MySQL的内部缓存机制,MySQL提供了多种缓存参数的配置,比如查询的结果集缓存,结果集排序的缓存,可根据实际情况进行调整。另一方面是MySQL之外的缓存,比如Redis+MySQL的架构,开启了Hibernate(Mybatis)的缓存功能。缓存的引入无非是想减轻MySQL的查询负担,但是必须在性能稳定性与数据时效性之间取得平衡。

2、SQL语句有性能问题,这种情况时有发生,通常是上线之前未能做一个完整的基准测试,而只是简单的功能性测试。当数据量积累到一定程度之后,SQL性能问题就集中爆发出来了。所以,在写完SQL之后,要养成explain的习惯,将潜在的性能问题扼杀在萌芽中。当然,我们也要避免“过度优化”,我们要预见得到一张表是读取次数多,还是更新次数多,数据量会不会爆发性增长,还是增长十分缓慢。当然,写SQL语句也要遵循一定的原则,比如什么时候用IN查询,什么时候用EXISTS谓词,在JOIN之前是不是可以精简一部分表数据,建立的索引能否正确派上用场……

3、必要的时候,可以对机器进行扩容,当然系统的整体架构也可以考虑进行优化,搭建MySQL集群,可靠性和可用性都能得到大幅提升。

四、补充:SQL范式

1NF

每一个分量必须是不可分的数据项。

特点:

  • 有主键,且主键不能为空。
  • 字段不能再分。

2NF

在范式一的基础上,且每一个非主属性完全函数依赖于码。

特点:

  • 满足第一范式。
  • 表中的每一个非主属性,必须完全依赖于本表码。
  • 只有当一个表中,主码由两个或以上的属性组成的时候,才会出现不符合第二范式的情况。

3NF

在满足第二范式的基础上,且每一个非主属性既不部分依赖于码也不传递依赖于码。

特点:

  • 满足第二范式。
  • 非主属性不能传递依赖于码。

** BCNF**

在满足第三范式的基础上,且不允许主键的一部分被另一部分或其它部分决定。

特点:

  • 满足第三范式。
  • 所有非主属性对每一个码都是完全函数依赖。
  • 所有的主属性对每一个不包含它的码,也是完全函数依赖。
  • 没有任何属性完全函数依赖于飞码的任何一组属性。

以上是对MySQL优化的框架性思考。