数据库的性能优化

SayHe110
305 阅读13分钟

前言

在我们写业务逻辑时，我们真的有在意一条查询的执行效率吗，是不是只有在某个功能慢的实在影响到用户使用了，才会去对某条 SQL 或者某个表优化。

虽然文章标题是数据库的性能优化，没有指明某个数据库，但是其实对于开发来说主要是优化的思想，而不是针对某个数据库，虽然不同数据库有一点点的不同，但是同样可以帮助我们在使用不同的数据库时，写出高质量的 SQL 语句，建立合理的高性能的索引。

本篇文章主要通过以下几个方面来介绍如何对一个数据库做性能优化：

  • SQL 优化
  • 逻辑结构优化（表优化、索引优化）
  • 配置优化

文章顺序从简单到深入，SQL 优化其实是相对简单的，不需要理解其他方面的知识，只是在开发时注意即可，关于配置相关的优化，大家可以自行选择观看（其实了解一点还是挺不错的）。

优化准则

其实优化的思路有很多，比较常用的两种有：

  • 第一种：The fastest way to do something is dont do it. 有点粗暴，从另外一个角度去理解这句话，就是把一些没有必要的步骤去掉；
  • 第二种：做同样一件事情，要想更快有很多种方法，最简单的方法就是增加高配置机器，让数据库运行在性能更高的硬件上，这种方式是财大气粗的选择方式，还有就是优化算法，如何让 SQL 走到更优的执行计划上；

在第一种优化思路，可以从代码层面来处理，比如减少 N+1 等；第二种呢，加机器当然不是本篇文章的目的，所以本篇文章主要介绍优化 SQL 以及表结构让查询走上更优的执行计划。

SQL 优化

下面是一些常用的 SQL 语句优化技巧，大家在实际使用中可以考虑一下自己的 SQL 是否还有优化的空间。当然并没有列举所有的优化思路，只是给我们提供不同的优化思路。

通用的 SQL 优化

尽量避免全表扫描

放在第一条，是因为它真的很重要！！！其实我们有很多种方法来避免，但是很多开发者并不在意。

如果我们需要的数据存储在最后一个位置，且表中数据很多，那么数据库需要从第一个开始查，直到查到最后一个才找到符合条件的数据，这样会消耗大量的查询时间，所以我们可以考虑在查询条件的列上建立索引来帮助数据库快速查找。

使用 EXISTS 代替 IN

在使用 exists 关键字时，优化器会默认它只需要搜索到 1 条命中目标就不会再搜了，而 in 则不同，最差的情况是可能会全表扫描。

当然并不是所有场景都是直接使用 exists 来代替 in ，在外表数据量大内表数据量小的情况下，in 比 exists 性能更高；相反，exists 适合于外表小而内表大的情况。

数值字段类型

这个应该是常识了，只含有数值信息的字段尽量设计成数值型，而不是设置字符型，如果设计成字符型的话会降低查询和连接的性能，并会增加存储开销。

不要使用 *

在查询时，若我们知道我们要使用哪些列时，我们最好指定列，而不是使用 * 来查询。

LIKE 语句

若我们知道关键词的前缀时，最好不要这样使用 %关键词%，这样的查询必然会全表扫描，尽量不要在关键词前面加 % 查询，这样的查询就可能会走上索引。

LIMIT 语句

在业务中若在查询某个值是否存在，使用到了 LIKE '关键词%' 这样的查询时，我们可以增加一个 LIMIT 1 来终止数据库进行扫描整个表或者索引。

表的别名

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

经常进行一些范围查询

当某个查询经常需要做范围查询时，可以考虑使用 cluster table_name using index_name 让表中行的物理存储顺序与索引的顺序一致，来达到提高查询效率。

SQL 语句能实现的事情就不要循环实现

当我们向批量插入数据时，有时会循环来插入，我们可以通过 SQL 的拼接来达到批量插入的效果，现在的 ORM 框架其实都会提供批量插入，他们最终执行的其实就是将参数拼接成一条 SQL 来执行。

更新多张表

在存储过程或事务中更新多张表时，应该总是以相同的顺序去更新，这样可以避免死锁。

逻辑结构优化（表优化、索引优化）

表优化（PgSQL）

TOAST

PgSQL 使用固定页面的大小（8KB），并且不允许元祖跨越多个页面，因此，不可能直接存储非常大的字段值，为了突破这个限制，需要将大字段值压缩和/或分解成多个物理行，这种技术成为 TOAST(The Oversized-Attribute Storage Technique 超大属性存储技术)

目前有以下 4 中 TOAST 策略:

  • PLAIN：避免压缩和行外存储
  • EXTENDED：允许压缩和行外存储
  • EXTERNA：允许行外存储，单不允许压缩
  • MAIN：允许压缩，但不允许行外存储

TOAST 的优化思路：

  • 如果 CPU 是瓶颈，则不使用压缩
  • 如果想节省空间，则使用压缩
  • PgSQL 11 可以指定内容超过多少时进行行外存储：alter table test_table set(toast_tuple_target=128)

调整表的 fillfactor 参数

fillfactor：填充因子是一个从 10 到 100 的整数，用于设置在插入数据时，在一个数据块中填充百分之多少的空间后就不再填充了，另一部分空间预留作更新时使用。比如，设置为 60 ，则表示向一个数据块中插入的数据占该块的 60% 空间后，就不再向该数据块插入数据，而保留的 40% 空间，就是为了更新数据时使用。

Heap-Only Tuple 技术，会在原数据行与新行之间建一个链表，这样一来，就不需要更新索引了，索引项仍会指向原数据行，但通过原数据行与新行之间的链表仍然可以找到最新的行。因为 Heap-Only Tuple 的链表不能跨数据块，如果新行必须插入一个新的数据块中，则无法使用 Heap-Only Tuple 技术，这时就需要更新表中上全部索引，这就造成了很大的开销，所以对于更新频繁的表需要设置一个较小的 fillfactor 值。

alter table test_table set(fillfactor=80)

分表

当数据量较大时我们不应该局限于一张表中，如果一张表数据量达到了几千万，就算你建立了较优的索引，那你的 SQL 还是无法避免很慢的情况，这时我们就可以考虑将一张表切分成多张表了，分表有很多种，大家可以在网上随意的搜，分表的文章有很多。

我最喜欢的是按时间来切分，或者主键来切分，大家可以根据实际场景来考虑，一般在设计表时就可以计算或者预估一下存储的数据量，这样在写逻辑代码时可以考虑到分表的情况，不需要后续再来改代码。

索引优化

关于索引大家可以看下之前写的一篇文章，关于索引的一点点基础知识

索引是一种从表中快速检索出较少行的有效方式，如果需要从表中检索出较少的行，我们就需要考虑在查询条件上建立索引，这样可以利用索引把所需的数据快速检索出来。

若建立了索引，并且若只需要查询索引列时，一定不要将其他列也查询出来，这样可以避免回表的开销。

那我们需要在哪些情况下建立索引呢？

  • 特别小的表不需要建立索引，浪费索引维护开销和存储空间；
  • 经常与其他表进行连接的字段；
  • 经常出现在 where 子句中的字段，特别是大表的字段；
  • 经常出现在 order by 子句中的字段；
  • 经常出现在 group by 子句中的字段；
  • 对于查询中很少使用的列不应该创建索引；
  • 应该建立在选择性高的字段上，而不是建立在 “性别” 类字段上；
  • 应该建立在小字段上，而不是大文本字段上，若一定要建，也需要考虑索引类型；
  • 若建了 （A，B）两个字段上的组合索引，通常就不要再建 A 字段的单字段索引；
  • 正确选择复合字段的主列字段（第一列），一般是选择性较好的字段作为第一列；
  • 如果复合索引所包含的字段超过 3 个，那么就要仔细考虑其必要性；
  • 频繁进行数据操作的表，不要建立太多索引；
  • 删除无用的索引；

PgSQL中的索引

在建立索引时我们可以选择不同的索引来获得良好的性能收益。

  • B 树索引：使用最广泛的索引类型，B 树索引既可以用于等值查询，也可以用于范围查询，还可以用于检索 NULL 值，还可以加速一些排序操作和聚合操作；
  • 哈希索引：只能用于等值查询，当索引键是一个较长的字符串时，B 树索引占用空间较大，而哈希索引则占用空间较小；
  • GIN 索引：通用逆向索引（Generalized Inverted Indexe），PgSQL 的全文索引就是使用 GIN 索引，另对于数组类型或 JSON 类型也可以使用 GIN 索引；
  • GiST 索引：通用搜索树索引（Generalized Search Tree），这是一种索引框架，即允许你建立普通平衡树结构，除了用于等值和范围比较之外，还能支持包含 （@>）、重叠（&&）等复杂运算，它们更多地用于索引几何数据类型，也可用于全文检索；
  • BRIN 索引：块范围索引，当插入的数据与插入的次序有线性相关性时，我们就可以建 BRIN 索引；

配置优化（PgSQL）

这部分主要介绍通过对配置的设置来达到性能调优的效果。

内存配置优化

PgSQL 与内存有关的配置参数：

  • shared_buffers: 共享缓存区的大小，主要做数据块的缓存
  • work_mem: 为每个进程单独分配的内存，主要用于排序、Hash 等操作
  • maintence_work_mem: 为每个进程单独分配的内存，主要是进行维护操作时需要的内存，如：create index、alter table add foreign key 等操作需要的内存
  • temp_buffers: 指定临时表的缓存的大小，这是为每个不同的进程单独分配的内存，不在共享内存中
  • wal_buffers: 指定 WAL 日志缓存的大小，默认 -1 即会根据 shared_buffer 的大小自动设置
  • huge_pages: 是否使用大页，默认设置时 try 表示尽量使用大页，如果操作系统未开启大页或分配的大页内存太小，数据库虽仍然能启动，但不再使用大页内存

shared_buffers, 共享缓存区的大小，因为要在多个进程中共享，所以必须使用共享内存技术来存放。PgSQL 的数据文件都在文件系统中，操作系统的文件系统也有缓存，这有可能会导致数据库的数据块除了在 PgSQL 的共享内存中有一个副本以外，在文件系统的缓存中也有一个副本，因此造成内存利用率不高，这就是 PgSQL 中的 Double Buffering 问题，为了减少双缓存问题带来的影响，通常使用设置较小的 shared_buffer ，将大多数内存给文件系统缓存使用来解决。

大页内存配置

对一些连接数很大且内存较大的 PgSQL 数据库，强烈建议配置大页，这不仅是因为大页的性能会高一些，也是为了避免页表过大。操作系统把逻辑地址映射成物理地址时，需要把映射关系也存储到一个内存中，这部分内存就是页表。

在 64 位机器上，每个 4K 页需要占用大约 8 字节的内存，一台 48 GB 内存的机器，如果分配了 24 GB 共享内存，则每个进程的页表大小约为 （24GB/4K） * 8 = 48 MB，如果服务器连接上 500 个进程，页表的大小将是 500 * 48 = 24 GB，这回立刻占满机器上所有内存，因而产生很多的问题。

当然并不是每个新连接一连接后面进程的页表就会马上分配 48 MB，当进程需要建立逻辑地址与物理地址之间的关系时才会分配，所以进程占用的页表空间是缓慢增加的，但最终还是可能会占用很大的页表内存。

基于共享内存的多进程架构的程序都会存在这个问题， Oracle 同样存在，Oracle 也是需要大页来解决该问题，在 PgSQL 数据库中通过 huge_pages = try 设置来让 PgSQL 尝试使用大页，如果操作系统没有配置大页或配置的大页小于 PgSQL 需要的大页内存，那么 PgSQL 在分配大页失败后，会使用普通内存，如果把 huge_pages 设置为 on ，分配大页失败后，PgSQL 也会启动失败。

预写式日志写优化

预写式日志（WAL）是对数据文件进行修改（通过是表或索引的数据文件）时，先把这些操作记录到日志中，数据文件修改后的脏页不必马上刷新到磁盘中，如果出现系统崩溃，可以重做记录在日志中的操作来恢复数据库。

一些与 WAL 相关的参数会影响数据库的性能，可以调整 WAL 相关参数来优化数据库的性能。

总结

本篇文章主要介绍 SQL 优化、表的优化、以及配置文件相关的优化，为大家在写 SQL 、建表时提供一些优化的思路，关于配置文件的优化如果非必要还是让专业的 DBA 来做吧，毕竟术业有专攻嘛，但是我们了解一点配置来做性能调优还是有必要的。

参考

《PostgreSQL 修炼之道》

avatar
文章
阅读
粉丝