数据库调优

数据库调优的目标

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

1. 通过用户的反馈去确定调优的目标：用户是服务对象，因此他们的反馈是最直接的。

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

3.通过服务器资源使用监控：通过监控服务器的 CPU、内存、I/O 等使用情况，可以实时了解服务器的性能使用，与历史情况进行对比。

4. 数据库内部状况监控：在数据库的监控中，活动会话监控是一个重要的指标。通过它，你可以清楚地了解数据库当前是否处于非常繁忙的状态，是否存在 SQL 堆积等。

数据调优的维度：

1. 选择适合的 DBMS

不同的DBMS有不同的特点，DBMS 的选择关系到了后面的整个设计过程，所以第一步就是要选择适合的 DBMS。

2. 优化表设计

①表结构要尽量遵循第三范式的原则。这样可以让数据结构更加清晰规范，减少冗余字段，同时也减少了在更新，插入和删除数据时等异常情况的发生。

②如果分析查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式进行优化。反范式采用空间换时间的方式，通过增加冗余字段提高查询的效率。

③表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。一般来说，如果字段可以采用数值类型就不要采用字符类型；字符长度要尽可能设计得短一些。

3. 优化逻辑查询

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

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

EXISTS 子查询和 IN 子查询的时候，会根据小表驱动大表的原则选择适合的子查询。

在 WHERE 子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。

eg：

SELECT ... FROM ... WHERE SUBSTRING(comment_text,1.3) = 'abc'

改写为

SELECT ... FROM ... WHERE comment_text  LIKE 'abc%'

4. 优化物理查询

物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对各种可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计划。这个部分的核心是高效地建立索引，并通过这些索引来做各种优化。

索引创建需要注意的情况：

1. 如果数据重复度高，就不需要创建索引。通常在重复度超过 10% 的情况下，可以不创建这个字段的索引。比如性别这个字段（取值为男和女）。

2. 要注意索引列的位置对索引使用的影响。比如我们在 WHERE 子句中对索引字段进行了表达式的计算，会造成这个字段的索引失效。

3. 要注意联合索引对索引使用的影响。我们在创建联合索引的时候会对多个字段创建索引，这时索引的顺序就很重要了。比如我们对字段 x, y, z 创建了索引，那么顺序是 (x,y,z) 还是 (z,y,x)，在执行的时候就会存在差别。

4. 要注意多个索引对索引使用的影响。索引不是越多越好，因为每个索引都需要存储空间，索引多也就意味着需要更多的存储空间。此外，过多的索引也会导致优化器在进行评估的时候增加了筛选出索引的计算时间，影响评估的效率。

5. 在物理查询优化阶段会根据数据表的索引情况和数据情况确定访问路径，这就决定了执行 SQL 时所需要消耗的资源，并决定这些查询所采用的路径：

① 单表查询：对于单表扫描来说，我们可以全表扫描所有的数据，也可以局部扫描。

②两张表的连接：常用的连接方式包括了嵌套循环连接、HASH 连接和合并连接。

③多张表的连接：多张数据表进行连接的时候，顺序很重要，因为不同的连接路径查询的效率不同，搜索空间也会不同。巨大的搜索空间会占用很多的资源，因此我们需要通过调整连接顺序，将搜索空间调整在一个可接收的范围内。

5. 使用 Redis 或 Memcached 作为缓存

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

键值存储数据库Redis 和 Memcached，它们都可以将数据存放到内存中。

两者相比Redis 支持持久化，可以让我们的数据保存在硬盘上，不过这样一来性能消耗也会比较大。而 Memcached 仅仅是内存存储，不支持持久化。并且Redis支持的数据类型比 Memcached 要多。

6. 库级优化

库级优化是站在数据库的维度上进行的优化策略，比如控制一个库中的数据表数量。或是采用主从架构来优化读写策略。

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

分库：

我们还可以对数据库分库分表。当数据量级达到亿级以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

垂直切分和水平切分：

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。

垂直分表：将一个表按照字段分成多表，每个表存储其中一部分字段。

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

采用垂直分表的形式，就是将一张数据表分拆成多张表，采用水平拆分的方式，就是将单张数据量大的表按照某个属性维度分成不同的小表。分拆在提升数据库性能的同时，也会增加维护和使用成本。

如果数据库中的数据表过多，可以采用垂直分库的方式，将关联的数据表部署在一个数据库上。如果数据表中的列过多，可以采用垂直分表的方式，将数据表分拆成多张，把经常一起使用的列放到同一张表里。

如果数据表中的数据达到了亿级以上，可以考虑水平分表，将大的数据表分拆成不同的子表，每张表保持相同的表结构。比如你可以按照年份来划分，把不同年份的数据放到不同的数据表中。2017 年、2018 年和 2019 年的数据就可以分别放到三张数据表中。