【MySQL进阶之路 | 高级篇】数据库调优

1. 调优的目标

• 尽可能节省系统资源，以便系统可以提供更大负荷的服务。
• 合理的结构设计和参数调整，以提高用户操作响应的速度。
• 减少系统的瓶颈，提高MySQL数据库整体的性能。

2. 如何定位调优的问题

不过随着用户量的不断增加，以及应用程序复杂度的提升，我们很难用更快去定义数据库调优的目标，因为i用户在不同时间段访问服务器遇到的瓶颈不同，比如双十一促销的时候会带来大规模的并发访问；还有用户在进行不同的业务操作时，数据库的事务处理和sql查询都会有所不同。因此我们还需要更精细的定位，去确定调优的目标。

用户的反馈

用户是我们服务的对象，因此他们的反馈是最直接的。虽然他们不会直接提出技术建议，但有些问题往往是用户第一时间发现的。

日志分析

我们呢可以通过查询数据库日志和操作系统日志的方式找出异常情况，通过它们来定位遇到的问题。

服务器资源使用情况

通过监控服务器的CPU，内存，IO使用情况，可以实时了解服务器的性能的使用，与历史情况做对比。

其他

除了活动会话监控外，我们也可以对事务，锁等待等进行监控，这些都可以帮助我们对数据库的运行状态有更全面的认识。

3. 调优的维度和步骤

我们需要调优的对象是整个数据库管理系统，它不仅包括sql查询，还包括数据库的部署变量，架构等。

第一步：选择适合的DBMS

如果对事务性处理以及安全性要求高的话，可以选择商业的数据库产品。这些数据库在事务处理和查询性能上都比较强。比如Oracle。那么单表存储上亿条数据是没有问题的。如果数据表设计的好，即使不采用分库分表，查询效率也不差。

除此之外，还可以采用开源的MySQL进行存储，如果进行事务处理的话可以选择InnoDb，非事务处理可以选择MyISAM。

MySQL阵营包括键值型数据库，文档型数据库，搜索引擎，列式存储，图形数据库。这些数据库的优缺点和使用场景各有不同。

第二步：优化表设计

选择了DBMS之后，我们就需要进行表设计了。而数据表的设计方式直接影响了后续的sql查询语句。RDBMS中，每个对象都可以定义为一张表，表与表之间的关系代表了对象之间的关系。如果用的是MySQL，我们还可以根据不同表的使用需求，选择不同的存储引擎。除此之外，还有一些优化的原则可以参考：

1. 表结构要尽量遵循三范式的原则。这样可以让数据结构更加清晰规则，减少冗余字段，同时也减少了在更新，插入和删除数据时等异常情况的发生。
2. 如果查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式化进行优化。反范式采用空间换时间的方式，通过增加冗余字段提高查询的效率。
3. 表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。一般来说，如果字段可以采用数值类型就不要采用字符类型；字符长度要尽可能设计的短一些。

数据表的结构设计很基础，也很关键。好的表结构可以在业务发展和用户量增加的情况下依然发挥作用，不好的表结构的设计会让数据表非常臃肿，查询效率会很低。

第三步：优化逻辑查询

当我们建立好数据表之后，就可以对数据表进行增删改查的操作了。这时我们首先需要考虑的是逻辑查询优化。

sql查询优化，可以分为逻辑查询优化和物理查询优化。逻辑查询优化就是通过改变sql语句的内容让sql执行效率更高效，采用的方式是对sql语句进行等价变换，对查询进行重写。

sql的查询重写包括了子查询优化，等价谓词重写，视图重写，条件简化，连接消除和嵌套连接消除等。

比如我们在讲解EXISTS子查询和IN子查询的时候，会根据小表驱动大表的原则选择适合的子查询。在where子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。

第四步：优化物理查询

物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对各种可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计划。在这个部分中，我们需要掌握的重点是对索引的创建和使用。

但索引不是万能的，我们需要根据实际情况来创建索引。

sql查询时需要对不同的数据表进行查询，因此在物理查询优化阶段也需要确定这些查询所采用的路径，具体的情况包括：

• 单表扫描：对于单表扫描来说，我们可以全表扫描所有的数据，也可以局部扫描。
• 两张表的连接：常见的连接方式包括了嵌套循环连接，hash连接和合成连接。
• 多张表的连接：多张数据表进行连接时，顺序很重要。因为i不同的连接路径查询的效率不同，搜索空间也会不同。我们在进行多表连接的时候，搜索空间可能会达到很高的数据量级，巨大的搜索空间显然会占用更多的资源，因此我们需要通过调整连接顺序，将搜索空间调整为一个可接收的范围。

第五步：使用Redis或Memcached作为缓存

除了可以对sql本身进行优化以外，我们还可以请外援提升查询的效率。

因为数据都是存放在数据库中，我们需要从数据库层中取出数据放到内存中进行业务逻辑的操作，当用户量增大的时候，如果频繁地进行数据的查询，会消耗数据库的很多资源。如果我们将常见的数据直接放入内存，就会大幅提升查询的效率。

键值存储数据库可以帮助我们解决这个问题。

常用的键值存储数据库有Redis或Memcached，它们都可以将数据存放到内存中。

从可靠性来说，Redis支持持久化，可以让我们的数据保存在硬盘上，不过这样一来性能消耗 也会比较大。而Memcached仅仅是内存存储，不支持持久化。

通常我们对于查询响应要求高的场景（响应时间短，吞吐量大），可以考虑内存数据库，毕竟术业有专攻。传统的RDBMS都是将数据存储在硬盘上，而内存数据库则存放在内存中，查询起来要快的多。不过使用不同的工具，也增加了开发人员的使用成本。

第六步：库级优化

库级优化是站在数据库的维度上进行的优化策略，比如控制一个库中的数据表数量。另外，单一的数据表总会遇到各种限制，不如取长补短，利用外援的方式。通过主从架构优化我们的读写策略，通过对数据库进行垂直或水平切分，突破单一数据库或数据表的访问限制，提升查询的性能。

1. 读写分离

如果读和写的业务量很大，并且它们都在同一个数据库服务器中进行操作，那么数据库的性能就会出现瓶颈，这时为了提升系统的性能，优化用户体验，我们可以采用读写分离的方式降低主数据库的负载，比如用主数据库（master）完成操作，用从数据库（slave）来完成读操作。

2. 分库分表

对数据库分库分表。当数量级达到千万级以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。如果你使用的是MySQL，就可以使用MySQL自带的分区表功能，当然你也可以考虑自己做垂直拆分（分库），水平拆分（分表），垂直+水平拆分（分库分表）。