「瀚高数据库技术栈」瀚高数据库优化解决方案

瀚高数据库 2022-06-21

1766

前言

一个信息系统从前期的规划，到具体的建设，再到最终建成，往往耗时数月甚至数年时间。信息系统建成后，伴随着业务的发展会面临着系统的不断迭代，如旧业务的升级、新需求的加入等，这将导致信息系统的规模越来越大，在数据库层面表现为数据量不断增长、负载增加等，会带来存储空间不足、响应时间变长甚至无响应等一系列问题，此时就需要对信息系统进行优化，从而更好支撑用户的业务开展。

数据库的优化是信息系统优化的重中之重，涉及多个维度，如 SQL 语句优化、合理添加索引、分库分表、数据分区、数据库引擎加速机制、基于代价估算的查询优化策略、多版本并发控制技术、数据库参数配置优化、高效的数据缓冲机制、定期 Vacuum 和 Analyse、数据库应用设计优化等技术来提升数据库整体性能。

1. 数据库层面

数据库设计初期，建立好的数据结构对于后期性能优化至关重要。因为数据库结构是系统的基石，基础打不好，使用各种优化策略，也不能达到很完美的效果。

1.1 数据库结构优化

1.1.1 范式设计优化

（1）范式优化

合理的范式设计，可以使数据库结构明晰，且可以有效减少冗余，降低存储的压力。

（2）反范式优化

适当的反范式设计，如增加一定冗余，可以降低复杂查询时候关联的表数量等，提升查询效率。

1.1.2 库表设计优化

（1）列类型的选择

定长类型，在处理速度上要比变长类型快，但是相对较费存储空间；所以对存储空间要求不大，但在速度上有要求的可以使用定长类型，反之可以使用变长类型。

考虑每个表列相关的数据类型所需的最小存储空间，比如能使用 smallint 类型就不要用 integer 类型，这样索引字段可以被更快地读取，而且可以在1个数据页上放置更多的数据行，因而也就减少了 I/O 操作。

（2）主键与外键

主键：尽可能使用长度短的主键，如果可以使用外键做主键则更好。在主键上无需建单独的索引。

外键：外键会影响插入和更新性能，对于批量可靠数据的插入，建议先屏蔽外键检查。

对于数据量大的表，建议去掉外键，改由应用程序进行数据完整性检查。

尽可能用选用对应主表的主键作为外键，避免选择长度很大的主表唯一键作为外键。

外键是默认加上索引的。

（3）字段

选用字段长度最小；

优先使用定长型；

尽可能的定义 “NOT NULL”。

（4）索引

对于那些在查询中很少使用或者参考的列不应该创建索引。费空间；

对于那些只有很少数据值的列也不应该增加索引。映射太少；

对于那些定义为 text 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少；

当修改性能要求远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的；

被索引的字段的长度越小，该索引的效率越高；

被索引的字段中，值的重复越少，该索引的效率越高；

查询语句中，如果使用了 “group” 子句，根据其中字段出现的先后顺序建立多字段索引；

查询语句中，如果使用了 “distinct”，根据其中字段出现的先后顺序建立多字段索引；

where 子句中，出现对同一表中多个不同字段的 “and” 条件时，按照字段出现的先后顺序建立多字段索引；

where 子句中，出现对同一表中多个不同字段的 “or” 条件时，对重复值最少的字段建立单字段索引；

进行“内/外连接”查询时，对“连接字段”建立索引；

对“主键”的 “unique” 索引毫无意义，不要使用。对于一个 Primary Key 的列，数据库已经自动对其建立了 Unique Index ，无需重复再在上面建立索引了；

被索引字段尽可能的使用 “NOT NULL” 属性。

1.2 使用物化视图

物化视图是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。使用物化视图替代普通视图，可以提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中 SQL 语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新（refresh materialized view）。

物化视图适合于那些经常查询但数据变化不频繁的数据表。比如查询基础信息。对于数据有频繁更新的表，给数据库做物化视图更新带来比较大的压力。

1.3 分库分表

分库分表就是把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。当数据量和并发量不大时，可以采取单库单表模式进行数据库的部署。当数据量和并发量增大时，利用分库分表的方式保证数据库的高性能。

如下图所示：

分库分表的基本思想就要把一个数据库切分成多个部分放到不同的数据库 (server) 上，从而缓解单一数据库的性能问题。不太严格的讲，对于海量数据的数据库，如果是因为表多而数据多，适合使用垂直切分，即把关系紧密（比如同一模块）的表切分出来放在一个 server 上。如果表并不多，但每张表的数据非常多，这时候适合水平切分，即把表的数据按某种规则（比如按ID散列）切分到多个数据库 (server) 上。现实中更多是这两种情况混杂在一起，这时需要根据实际情况做出选择，也可能会综合使用垂直与水平切分，从而将原有数据库切分成类似矩阵一样可以无限扩充的数据库 (server) 阵列。

分库分表包括垂直切分、水平切分、垂直和水平联合切分。

1.3.1 垂直切分

数据的垂直切分，也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”（表）组成，垂直的将这些数据块切开，然后分散到多台数据库主机上面，这样的切分方法就是一个垂直（纵向）的数据切分。

所以，可以将数据库按照功能模块相关的表进行一次垂直拆分，每个模块涉及的表单独到一个数据库中，模块与模块之间的表关联都在应用系统端通过接口来处理。

如下图所示：

通过这样的垂直切分之后，之前只能通过一个数据库来提供的服务，就被分拆成四个数据库来提供服务，服务能力增加几倍。

1.3.2 水平切分

数据的水平切分主要是将某个访问表再按照某个字段的某种规则来分散到多个表中，每个表中含一部分数据。可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

对于我们的示例数据库来说，大部分的表都可以根据用户ID来进行水平的切分，不同用户相关的数据进行切分之后存放在不同的数据库中。如将所有用户ID通过2取模，然后分别存放于两个不同的数据库中，每个和用户ID关联上的表都可以这样切分。这样，基本上每个用户相关的数据，都在同一个数据库中，即使是需要关联，也可以非常简单的关联上。

我们可以通过下图来更为直观的展示水平切分相关信息：

1.3.3 垂直与水平联合切分

一般来说，数据库中的所有表很难通过某一个（或少数几个）字段全部关联起来，所以很难简单的仅仅通过数据的水平切分来解决所有问题。而垂直切分也只能解决部分问题，对于那些负载非常高的系统，即使仅仅只是单个表都无法通过单台数据库主机来承担其负载，我们必须结合“水平” 和 “垂直”两种切分方式同时使用，充分利用两者的优点，避开其缺点。

每一个应用系统的负载都是逐步增长上来，在开始遇到性能瓶颈的时候，往往都会选择先进行数据的垂直拆分，然而，随着业务的不断扩张，系统负载的持续增长，在系统稳定一段时期之后，经过了垂直拆分之后的数据库集群可能又再一次不堪重负，遇到了性能瓶颈。这时就必须要通过数据的水平切分的优势，来解决这里所遇到的问题。

下图展示了切分后的整个架构：

应对不同的应用场景时，也需要充分考虑到这两种切分方法各自的局限，以及各自的优势，在不同的时期（负载压力）使用不同的结合方式。

1.4 数据分区

为提升海量数据库中的大表（超大表）的读写性能，往往需要对数据进行分区，常见的分区方案包括：范围分区、列表分区、哈希分区。分区是把大表（超大表）横向或纵向分成若干个子表，采用分而治之的策略。从而在数据查询时避免全表扫描，降低 I/O 交互，提高读写效率。

范围分区：分区规则根据 KEY 按照不同的范围映射到不同的分区中，每个范围都有一个上下限值；

列表分区：分区规则根据 KEY 按照若干离散的值映射到不同的分区中；

哈希分区：分区规则根据 KEY 按照系统内部的哈希算法及分区数映射到不同的分区中。

分区给用户带来的价值：

（1）分区后，单个分区表都相对较小，可以保持在内存里，适合把热数据从大表拆分出来的场景；

（2）对于大范围查询场景，可以使用分区表扫描。减少大表索引带来的随机 BLOCK 扫描；

（3）对于删除大表来说，使用 Delete 会带来大量的 VACUUM 操作负担。使用分区表可以直接 DROP 分区，或者脱离子表和父表的继承关系。在适用场景下，利用上述分区方法，数据库系统能获得指数级的性能提升。

1.5 定期 Vacuum 和 Analyse

对元组的更新和删除，并不会真正清除相应的元组，而是对元组进行标记，在长时间的运行数据库后，表中会存在大量的被标记为删除的无用记录，不仅占用了额外的磁盘空间，而且会影响表的扫描时间。在数据库运行过程中，数据库管理员需要定期对数据库进行页面清理，通过 vacuum 检查数据库页面，回收被删除的元组以及指向它们的索引所占据的空间。

利用 analyse 会分析数据库中的数据信息，并更新系统表中记录的各个表的统计参数。准确的统计信息可以使数据库在执行 SQL 语句时选择最优的查询计划，从而提高运行速度。如果数据库系统中的数据发生了很大的变化，如大量的更新、插入、删除操作。需要运行 analyse 更新系统统计信息。

1.6 数据库参数优化

配置参数对系统性能表现有着直接的影响，在系统初始化时，都具有缺省值，但不一定适应用户应用场景而达到最优，用户可以根据实际情况对其进行调整，使得服务器的整体性能达到最优。

数据库的配置参数有很多，其中，对系统性能影响较为明显的参数主要分为三类：内存使用策略参数、线程使用策略参数及 I/O 策略参数。

例如：如果发现数据库在运行过程中频繁的进行 checkpoint 操作，表明数据库进行大量的 I/O 操作，需要调整中日志相关参数，必要时调整日志文件大小，添加比较大的日志文件以减少频繁切换日志文件的时间。

【附录A】（详见文章末）

是针对瀚高数据库以及 postgersql 系列数据库中常见的数据库参数的释义。

2. SQl 优化

2.1 合理的编写 SQL 语句

合理的编写 SQL 语句可以加快数据库的处理速度，从而提高系统的运行的运行速度。在拼写 SQL 时，尽可能的将 SQL 拆解为简单易读的 SQL，对于复杂逻辑可以借助程序来协同完成，一方面执行效率不一定低，另一方面也给未来的运维、修改带来便捷。如果设计原因导致关联表略多，考虑视图、拆解、辅助表的方式来简化查询，降低 SQL 复杂度，减少表关联查询的数量（少于5表），且尽可能少用子查询，视图嵌套不要超过2层。

（1）like，<> 操作符

需要注意的是采用 select * from customer where first_name like '%xxxx' 的查询语句是无法使用到索引的，即使 first_name 上建有索引，数据库仍然采用全表扫描。而这样的语句 select * from customer where first_name like 'xxxx%' 是可以采用索引扫描的，数据库管理员可以根据情况更改相应的 SQL 语句。

同样，<>操作符是无法使用到索引的，如果估计返回的结果占表中记录的比率很少，可以尝试用其他具有相同功能的操作符替代，如：a<> 0 改为 a>0 or a<0 。

（2）调整排序性能

大量的排序操作很昂贵，涉及到排序的操作比如 distinct，intersect，except，order by，group by，union 等。这里的原则是尽量减少排序操作：

不使用 distinct 和 group by 操作；

使用 union all 代替 union ；

利用索引扫描来避免排序。

（3）避免改变索引列的类型

当比较不同数据类型的数据时，数据库会自动对列进行简单的类型转化，当对索引列的类型作转换时会使索引失效。

数据库会自动将 SQL 查询语句：select id，value from test where value = 2 中的value 列转化为 number 类型，索引没有作用。正确的写法：select id，value from test where value = '2'。

（4）SELECT 子句中少用*

采用 select * 来查询表中数据是一个比较低效的方法，数据库在解析的过程中需要将*转化为所有的列名，这个工作是通过查询数据字典完成的，这就意味着将耗费更多的时间。所以 select 语句中应该将要查询的列的名称写出来。

（5）使用表的别名

当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个列上，这样就减少了解析的时间并减少了那些由列歧义而引起的语法错误。

（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null 可以在 num 上设置默认值0，确保表中 num 列没有 null 值，然后这样查询：select id from t where num=0 。

（7）很多时候用 exists 代替 in 是一个好的选择。

（8）用 Where 子句替换 HAVING 子句，因为 HAVING 只会在检索出所有记录之后才对结果集进行过滤。

2.2 针对场景的 SQL 优化建议

（1）去除 count 中的 order by 操作；

（2）将 limit 20 前置到到子查询内部，减少结果集数量；

（3）减少 count（*）内部运算操作；

（4）limit 20 前置，减少结果集操作；

（5）建议在 count 的子句中不要使用 order by操作；

（6）如果存的是数字列的，int 类型要比 varchar() 好；

（7）建议 count 子句中去除 order by 操作；

（8）日常管理的数据修改业务，最好能够去除 order by 语句，性能差多倍。

2.3 合理的创建索引

合理的使用索引能大大提高数据库系统的性能。索引虽说提高了访问速度，但太多索引会影响数据的更新操作需要数据库开发人员在使用哪些索引之间作出平衡。

索引的创建基本规则如下：

（1）定义主键的数据列一定要建立索引；

（2）定义有外键的数据列一定要建立索引；

（3）对于经常查询的数据列最好建立索引；

（4）对于需要在指定范围内的快速或频繁查询的数据列；

（5）经常用在 WHERE 子句中的数据列；

（6）经常出现在关键字 order by、group by、distinct 后面的字段，建立索引；如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用；

（7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引；

（8）对于定义为 text 、 image 和 bit 的数据类型的列不要建立索引；

（9）对于经常存取的列避免建立索引；

（10）限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作；

（11）对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用，因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

2.4 定位慢SQL并且优化

大致的解决步骤如下：

（1）查看慢日志，定位到具体的 SQL 语句。

（2）根据执行计划针对数据库 SQL 语句进行分析。

（3）根据结果对 SQL 语句进行修改或增加所需的索引。

（4）经过测试后达到效果后上线。

3.结合应用层优化

3.1 应用代码

分析相关的应用程序代码，找出相应的瓶颈，再考虑具体的优化策略。有一些性能问题，往往是代码写的不合理导致的，通过修改代码即可解决问题，比如 for 循环次数过多、作了很多无谓的条件判断、相同逻辑重复多次等。

3.2 合理使用连接池

在实际的开发应用场景中，结合应用系统的连接池使用必不可少。

（1）资源重用 (连接复用)

由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上，增进了系统环境的平稳性（减少内存碎片以级数据库临时进程、线程的数量）。

（2）更快的系统响应速度

数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池内备用。此时连接池的初始化操作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩减了系统整体响应时间。

（3）新的资源分配手段

对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接的配置，实现数据库连接技术。

（4）统一的连接管理，避免数据库连接泄露。

在较为完备的数据库连接池实现中，可根据预先的连接占用超时设定，强制收回被占用的连接，从而避免了常规数据库连接操作中可能出现的资源泄露。

4.操作系统层面优化

4.1 存储

生产环境通常使用固态磁盘，如目前使用广泛的 SATASSD 和 PCieSSD 。建议使用企业级 SSD ；或使用外部存储设备，例如 SAN（存储区域网络）和 NAS（网络接入存储）。

4.2 CPU

服务器在 BIOS 中如可设置 CPU 的性能模式，可能的模式有高性能模式、普通模式和节能模式，建议使用高性能模式。

4.3 内存

较大的内存明显降低服务器的 I/O 压力，缓解 CPU 的 I/O 等待时间，对数据库性能起着关键作用。需要密切监控容量和指标趋势，在适当的时候进行扩容和升级。

4.4 I/O调度算法

磁盘 I/O 通常是数据库服务器主要瓶颈，调整调度算法提高数据库服务器性能。对于数据库的读写操作，Linux 系统在收到数据库的请求时，内核并不立即执行请求，而是通过 I/O 调度算法，先尝试合并请求，再发送到块设备中。

4.5 预读参数

在内存中读取数据比从磁盘读取要快，增加 Linux 内核预读，对于大量顺序读取的操作，可有效减少 I/O 等待时间。

4.6 Swap

当内存不足时，操作系统会将虚拟内存写入磁盘进行内存交换，而数据库并不知道数据在磁盘中，这种情况下就会导致性能急剧下降，甚至造成生产故障。有些系统管理员会彻底禁用 Swap，但如果这样，一旦内存消耗完就会导致 OOM（内存溢出），数据库就会随之崩溃。

4.7 透明大页

（ TransparentHugePages，THP ）

数据库的内存访问模式往往是稀疏的而非连续的。当高阶内存碎片化比较严重时，分配 THP 页面会出现较高的延迟，若开启针对 THP 的直接内存规整功能，也会出现系统 CPU 使用率激增的现象，因此建议关闭 THP 。

4.8 NUMA 架构

NUMA（Non-Uniform Memory Access 不一致内存访问）是为了解决多 CPU 下内存访问冲突，启用 NUMA 之后，内核将要求多路 CPU 尽量从距离较近的系统内存节点（服务器的整体内存在 NUMA 架构下将被分成若干个节点），分配内存而不是在整个服务器可访问内存的范围内进行内存分配，因此在高内存占用压力下内存申请会触发内存频繁回收整理的机制，从而严重影响系统整体性能。所以一般将这个参数关闭。

4.9 虚拟内存参数

（dirty_ratio/dirty_background_ratio）

dirty_ratio 绝对的脏页百分比值限制。当脏的page cache 总量达到系统内存总量的这一百分比后，系统将开始使用 pdflush 操作将脏的 page cache 写入磁盘。默认值为20％，也就是说如果到达该值时可能会导致应用进程的 IO 等待，通常不需调整。

dirty_background_ratio 百分比值。当脏的 page cache 总量达到系统内存总量的这一百分比后，系统开始在后台将脏的 page cache 写入磁盘。默认值为10％，如果后台刷脏页的慢，而数据写的快就容易触发上面 dirty-ratio 的限制。通常不需调整。对于高性能 SSD，比如 NVMe 设备来说，设置较低的值有利于提高内存回收时的效率。

4.10 文件系统（ ext4/XFS ）

ext4 是第四代扩展文件系统，是 ext3 文件系统的后继版本，相比 ext3，ext4 取消子目录限制，支持的文件卷达到1EB，最大文件支持达到16TB，对于大数据存储足够，ext4 在 fsck（file system check）做了大量优化，相比 ext3 用时少很多。

XFS 是由 SGI 公司设计的优秀的64位文件系统。 XFS 在可扩展、并行 I/O、online 碎片整理、延迟性能分配等方面做了大量改进，被称为业界最先进的、最具可升级性的文件系统技术。最大文件系统支持8EB，但文件也支持16TB，CentOS 7 开始就用 XFS 作为默认的文件系统了，XFS 的性能也比 EXT4 好。

4.11 网络

关于网卡主要涉及到两个方面：一是网卡的稳定性；二是一网卡的速率。

（1）网卡的速率

选用千兆网卡还是万兆网卡还是看数据库服务器网卡流量和网络延迟情况，在目前的大多数流量情况下，千兆网卡足够用来对外提供服务，在允许的范围内可以使用万兆网卡。

（2）网卡的稳定性

网卡稳定提供服务是数据库服务器的基本要求，为了保障网络稳定性，一般都会采用双网卡做冗余，可以分为2种情况，一是双网卡做 bond4，这样任意一个网卡故障都不影响业务的正常运行。另外双网卡的作用可以一块网卡提供数据的读写服务器，另外一块儿网卡用于日常探活和 DB 备份，这样可以避免 DB 备份对一块儿网卡流量的影响。

【附录A】

参数名称

建议值

参数说明

Work_mem

(available_ram*0.25)/max_connections

即：

1/4的RAM的值

内部排序和哈希操作可使用的工作内存大小。业务逻辑的SQL中含有order by，group by、distinct等关键字的操作将使用该内存，如涉及这些操作的表或中间结果较大，要酌情增加该值。

Shared_buffers

服务器总内存的25%

设置数据库服务器将使用的共享内存缓冲区大小。

maintenance_work_mem

大于等于work_mem值

声明在维护性操作(比如 VACUUM，CREATE INDEX，ALTER TABLE ADD FOREIGN KEY 等)中使用的最大的内存数。在数据库数据载入后创建索引、主键、外键的过程中可以适当调大该值，提高速度。

full_page_writes

Off

如果设置为on系统会在checkpoint后页面的第一次修改时将该页面的所有内容都写回磁盘，增加了IO量，这样做的原因是防止操作系统或主机崩溃造成的脏页面写入不完整，也就是一个脏页面只有一部分被写回磁盘造成数据丢失。如果操作系统和服务器足够稳定，可以将该项设置为off。

wal_buffers

1024kB

日志缓冲区的大小，是共享缓冲区的一个部分。如果单位时间事务的数据修改数据量较大，或者开启了commit_delay，应该酌情增加该值。

checkpoint_timeout

1500min

checkpoint的时间间隔，根据系统写的负载设置，一般不要太频繁。可以和后台写线程配置相关参数配合使用。

autovacuum

Off

是否开启自动清理线程（如开启需要同时设置参数stats_start_collector = on，stats_row_level = on，），整理数据文件碎片、更新统计信息，如果系统中有大量的增删改操作，建议打开自动清理线程，这样一方面可以增加数据文件的物理连续性，减少磁盘的随机IO，一方面可以随时更新数据库的统计信息，使优化器可以选择最优的查询计划得到最好的查询性能。如果系统中只有只读的事务，那么关闭自动清理线程。

stats_start_collector

Off

是否开启统计线程，该线程会在事务提交后采集一些统计信息（不是优化器使用的统计信息），如果没有使用统计信息的需求，关闭它避免额外的开销。

stats_reset_on_server_start

Off

设置是否在服务器启动时清空已收集的统计信息。默认关闭，也就是统计信息在服务器多次重启过程中累加。这是一系统级参数，只能在服务器启动时设置。

stats_command_string

Off

设置是否统计每个会话执行的SQL命令。这个选项缺省是关闭的。请注意即使把它打开，这个信息也不是所有用户都可见的，只有数据库管理员和会话的所有者才能看到。这是一个会话级参数，只有数据库管理员可以改变这个设置。

stats_row_level

Off

设置是否统计行级别的信息。缺省是关闭的。这是一会话级参数。

default_statistics_target

默认值

统计信息直方图的bin的个数，如果表的列没有设置统计目标，则使用该参数指定的值作为统计目标。

random_page_cost

随机的获取一页的成本。

外键数据库系统数据库技术数据库服务器数据库分区

文章转载自瀚高数据库，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

「瀚高数据库技术栈」瀚高数据库优化解决方案

【附录A】

评论