支付系统：数据库优化

系统设计
5242 words
Page content

背景

在支付系统：MySQL 分片 我们讨论了如何进行分库分表。

由于各种原因，我们的数据库负载会随着时间的推移而增加，比如：

  • 用户数量和流量的增长。
  • 数据大小随时间增加：随着数据大小的增加，数据库的负载也会增加。
  • 新功能的推出：这些可能是直接面向终端用户的产品功能，也可能是旨在为用户提供更好体验的内部优化。

在支付系统中，数据库则是最容易产生性能瓶颈的组件。 本文将讨论在支付系统中，我们为减轻数据库负载所做的一些优化。

数据库负载的三个主要部分

数据库负载主要有三个方面：

  • 读取 - read
  • 写入 - write
  • 数据量 - data volume

一般来讲，Web请求读比写多。我们的数据量在过去几年中有很大的增长，但是产品设计上允许我们将旧的数据打包，因此总的数据量并不是一个很大的问题。 因此，我们更多地致力于优化读取和写入，而不是数据量。以下各节将分别讨论上述内容。

定位性能瓶颈

读写操作可以通过多种方式产生负载，这篇博客的重点是高QPS（每秒查询数）查询 和 TPS (每秒事务数)。

首先是如何定位性能瓶颈？

一般使用show命令、慢查询日志、explain、profiling等进行分析。

  • 打印慢查询日志，找出执行慢的查询
  • 当服务器的负载增加时，使用SHOW PROCESSLIST来查看有问题的查询
  • EXPLAIN查看SELECT时索引命中的情况，分析查询语句和表结构

根据分析结果，优化索引和查询，优化表结构；这是后端小伙伴可以着手的部分。然后，还可以找DBA优化MySQL配置和硬件。

MySQL表结构优化

一个table中，对查询最重要是建立合适的索引。 如果没有合适的索引，或者索引没有足够的列，或者索引中列的顺序对查询来说不是最优的，查询可能会变慢并对数据库产生很大的负载。 在这种情况下，通常会修改索引以使其适应查询。

此外，以下是一些常见的MySQL数据表优化方法：

  1. 每张表都设置一个ID做为其主键

    • 推荐使用UNSIGNED INT，设置上AUTO_INCREMENT标志

  2. 把IP地址存成 UNSIGNED INT

    • 用VARCHAR(15)字段的字符串来存IP不划算
    • 如果用整形来存放只需要4个字节，并且可以有定长的字段

  3. 固定长度的表会更快

    • 固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快
    • 有VARCHAR，TEXT，BLOB字字段；这个表就不是“固定长度静态表”了

  4. 越小的列会越快

    • 对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈
    • 数据变得紧凑会，在一个内存页可以读取更多行数据，可以减少了对硬盘的访问

  5. 尽可能的使用 NOT NULL

    • NULL无法利用索引，且需要额外的空间
    • NULL在业务逻辑层需要额外的处理

  6. 大表水平分拆

    • 降低每个表的行数，加快查询
    • 比如可以按 order_tab.user_id % 1000` 分1000个表

  7. 多字段表垂直分拆

    • 把数据库中的表按列变成几张表
    • 降低表的复杂度和字段的数目

  8. 不适合建索引的情况

    • 区分度不高的字段，不适合建索引；消耗资源，对性能提升不大
    • 乱序的字段不适合作为索引，如MD5, UUID；否则，在插入数据时，更新索引需在B+树中大量移动结点，导致较多硬盘IO
    • 更新非常频繁的字段不适合创建索引；原因同上

  9. 不使用外键 - Foreign Key

    • MySQL外键的实现比较简单和粗糙，性能不佳
    • 引入外键后，不方便进行分表

  10. 尽量把数字定义成unsigned

  11. 尽量不使用ENUM

读取优化

优化方法

  1. 为搜索字段建索引，合理使用 （第一条，因为它最关键）

    • 在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引
    • 尽量保证索引简单，避免在同一列上加多个索引
    • 有时MySQL会选择错误的索引，可考虑使用USE INDEX
    • 查询字段存在索引中，考虑Cover Index

  2. 当只要一行数据时使用 LIMIT 1

  3. 避免 SELECT *；删除select子句中不必要的列（尤其是那些不在所使用的索引中的列），

    • 从数据库里读出越多的数据，那么查询就会变得越慢
    • 数据多会增加网络传输的负载

  4. 删除order by子句，并在业务代码里面进行排序（MySQL的CPU通常比服务器的CPU更宝贵）

  5. WHERE 子句

    • 避免对于null的判断，否则会导致全表扫描
    • 避免使用 != 或 <> 操作符，否则会造成后面的索引字段失效
    • 不使用 % 前缀模糊查询
      • 例如like “%name” 或者like “%name%”，这种查询会导致索引失效
      • 但是可以使用like “name%”
    • 避免进行表达式操作
      • select uid from user where age*2=36;会导致索引失效
    • 使用同类型进行比较，比如用 ‘123’ 和 ‘123’ 比，123 和 123 比

  6. LIMIT M,N节制使用

    • 随着表数据量的增加，使用LIMIT分页会越来越慢
    • 可以采用记录上次查询结果ID的方法实现翻页

  7. IN和OR

    • OR 改写成 IN：OR的效率是O(n)级别，IN 的效率是log(n)级别
    • IN包含的值不应过多，如果较多，产生的消耗也是比较大的，IN 的个数建议控制在 200 以内

  8. 避免COUNT(*)

    • 采用InnoDB时避免在整个表上使用count(*)，它可能会导致整个表hang住，因为count操作太耗时了
    • InnoDB是没有保存rows count的，因为在不同transaction中，看到的行可能不一样
    • InnoDB通过扫描索引来计数，count全表时，需要遍历全部索引；当索引不再内存中时，还需要从磁盘读取，会更慢更消耗资源
    • 如果只需要一个大概的行数，可以用SHOW TABLE STATUS
    • MyISAM引擎不一样，维护了count值，SELECT COUNT(*)能马上返回

  9. 尽可能避免复杂的join和子查询

    • 建议减少对大表的 join 查询，InnoDB 引擎会产生行锁；MyISAM 引擎会产生表锁，会导致其他写操作被阻塞
    • 尽量使用inner join，避免left join；没有其他过滤条件时，MySQL默认会自动选择小表作为驱动表

如果该查询提供的功能不再重要，甚至可以完全删除查询。

需要注意的事：即使有优化过的SQL和良好的模式，高QPS查询仍然可能在数据库上产生大量负载。

大范围扫表 - Large scans

当扫描大量行的查询变得有问题时，我们会修改它以使用LIMIT子句，或者使用分页。

低效缓存导致高QPS

缓存通常用于减少数据库的QPS（每秒查询数）。高QPS查询有时可能是低效缓存（甚至没有缓存）的一个迹象。

缓存键的选择会对缓存效率产生很大的影响：

  • 如果缓存键过于具体或狭窄，可能导致数据库的高QPS。
  • 如果缓存键过于宽泛，可能导致每次查询都要从数据库中拉取大量数据。

通过检查我们的高QPS查询，发现在很多情况下，低效的缓存是高QPS的原因。以下给出了一些例子。

DB数据不存在

一些高 QPS 查询在一维中查询稀疏数据集，并且大多数返回空结果。例如，我们有这样一个表，存储用户级别的支付设置：user_payment_setting_tab

实际大部分用户没有这样一个配置。因为DB数据不存在，所以查询完DB后，没有数据放到缓存；下一次请求，还是不能命中缓存，还是得去数据库查询，导致数据库read负载偏高。

处理方式：数据不存在的时候，直接返回一个默认值。这样下一次读取，可以读到缓存。

写入优化

CAS方式更新数据

Compare And Set（CAS），是一种常见的降低读写锁冲突，保证数据一致性，提高写入性能的方法。 使用CAS解决高并发时数据一致性问题，只需要在进行set操作时，compare初始值（通常是一个版本号），如果初始值变换，不允许set成功。

在 支付系统: 余额更新 一文中，我们讨论了如何通过CAS方式更新数据。

避免死锁

MySQL如何避免死锁？

  • 降低隔离级别
  • 优化SQL语句，减少锁的粒度
  • 优化表结构，减少锁的粒度

MySQL死锁例子1 - 循环等待

死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。 当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时，也可能会发生死锁。

假设我们有这样一个payment_tab：

CREATE TABLE `payment_tab` (
  `payment_id` bigint(20) unsigned NOT NULL, -- "{version:01}{transaction_id%base:04}{seq:014}"
  `transaction_id` bigint(20) unsigned NOT NULL,
  `payment_type` tinyint(3) unsigned NOT NULL,
  `amount` bigint(20) unsigned NOT NULL,
  `payment_status` tinyint(3) unsigned NOT NULL,
  `channel_id` int(11) unsigned NOT NULL,
  `channel_order_no` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `channel_order_status` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` int(11) unsigned NOT NULL,
  `update_time` int(11) unsigned NOT NULL,
  `ext_data` JSON,
  PRIMARY KEY (`payment_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_channel_order_no` (`channel_order_no`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

事务A和事务B同时执行：

  • 事务A：UPDATE payment_tab SET status = 0 WHERE payment_id = 1
  • 事务B：UPDATE payment_tab SET status = 2 WHERE payment_id = 2
  • 事务A：UPDATE payment_tab SET status = 2 WHERE payment_id = 2
  • 事务B：UPDATE payment_tab SET status = 1 WHERE payment_id = 1
  • 事务A：COMMIT
  • 事务B：COMMIT

分析：事务A和事务B都在更新payment_id为1和2的记录，事务A先更新payment_id为1的记录，事务B先更新payment_id为2的记录，然后事务A更新payment_id为2的记录，事务B更新payment_id为1的记录，这样就发生了死锁。

InnoDB目前处理死锁的方式是将持有最少行级排他锁的事务回滚。

MySQL死锁例子2 - 单行更新

  • – Let’s say: payment(id=1,ref=“abc”)
  • SQL 1: update payment_tab set ref=“xyz” where ref = “abc” order by user_id limit 1;
  • SQL 2: update payment_tab set ref=“def” where id=1;

Why does Single Row Update lead to Dead Lock?

Updating single row is not a single step. Before Update, DB need lock the index fisrt.

  • SQL 1 - step A: XLOCK on ref=“abc”, getting pk=1
  • SQL 2 - step B: XLOCK pk=1
  • SQL 1 - step C: XLOCK pk=1 -> waiting for B to release XLock
  • SQL 2 - step D: XLOCK on ref=“abc” -> waiting for A to release XLock

在我们的支付系统中，更新数据时，都是基于payment_id加一层cache lock，来避免出现这种情况。

MySQL死锁例子3 - Gap Lock 导致死锁

InnoDB使用索引来锁定行，因此锁定已有数据行似乎很简单——只需锁定该行的索引。

如果数据行不存在，假设两个请求同时到来。

两个SELECT ... FOR UPDATE将获得间隙锁（gap locks）而不是X（记录）锁，而间隙锁只阻止在这个间隙中不能再插入更多记录。

为什么从X锁变为间隙锁？

  • 如果记录存在，数据库将对索引（行）进行X锁定。这是一个理想的情况。
  • 如果记录不存在，无法锁定不存在的记录的索引。 如果你使用的是默认的隔离级别REPEATABLE READ，InnoDB也将使用间隙锁。 只要你知道要锁定的ID（甚至ID范围），那么InnoDB就可以锁定这个间隙，直到我们完成操作，间隙中就不能插入其他记录。

然而，如果我们在同一个间隙上有两个间隙锁，会发生什么？

这将导致死锁，并使一个事务回滚。

优化表使用的空间

MySQL表压缩

什么场景需要压缩？

磁盘空间达到瓶颈、存在大字段、读多写少的表。

默认情况下，所有表都是非压缩的。 数据库几乎都是IO负载型的，在CPU有大量余量的时候，磁盘 IO 的瓶颈就已经凸显出来。而数据的大量存储，尤其是日志/审计型数据，会导致磁盘空间快速增长。硬盘不够用也会在很多业务中凸显出来。在支付系统完成数据库分库之前，部分业务的MySQL磁盘用量甚至达到过90%。

表压缩主要是用在字符类型比较大的表上（VARCHAR，VARBINARY和BLOB和TEXT类型）。 通过消耗少量 CPU 资源，采用压缩来减少磁盘空间占用，以及优化IO和带宽。 尤其针对读多写少的业务，压缩会带来很多性能的提升。

压缩比率

压缩算法如果采用LZ77，压缩效率好点的话，压缩后的大小和未压缩的数据大小比如在25-50%左右，可以通过调节压缩程度（innodb_compression_level参数）来权衡压缩比和CPU使用率。

innodb_compression_level：默认值为6，可选值0-9，数值越大表示压缩程度越大，消耗的CPU也越多。

Compress压缩

  • COMPRESS 页压缩是 MySQL 5.7 版本之前提供的页压缩功能。 只要在创建表时指定ROW_FORMAT=COMPRESS，并设置通过选项 KEY_BLOCK_SIZE 设置压缩的比例。

  • MySQL 中的压缩都是基于页的压缩；虽然是通过选项 ROW_FORMAT 启用压缩功能，但这并不是记录级压缩，依然是根据页的维度进行压缩。

  • COMPRESS 页压缩就是将一个页压缩到指定大小。如 16K 的页压缩到 8K，若一个 16K 的页无法压缩到 8K，则会产生 2 个压缩后的 8K。

  • COMPRESS 页压缩的实现对性能的开销是巨大的，性能会有明显退化。

TPC 压缩

  • TPC（Transparent Page Compression）是 5.7 版本推出的一种新的页压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩。

  • TPC 压缩需要操作系统是否支持空洞特性。通常来说，当前常见的 Linux 操作系统都已支持空洞特性。

  • 由于空洞是文件系统的一个特性，利用空洞压缩只能压缩到文件系统的最小单位 4K，且其页压缩是 4K 对齐的。比如一个 16K 的页，压缩后为 7K，则实际占用空间 8K；压缩后为 3K，则实际占用空间是 4K；若压缩后是 13K，则占用空间依然为 16K。

  • 空洞压缩的另一个好处是，它对数据库性能的侵入几乎是无影响的（小于 20%），甚至可能还能有性能的提升。

表压缩在业务上的使用

总的来说，对一些对性能不敏感的业务表，例如日志表、审计表、监控表等，它们只对存储空间有要求，因此可以使用 COMPRESS 页压缩功能。

在一些较为核心的流水业务表上，可使用 TPC 压缩。 因为流水信息是一种非常核心的数据存储业务，通常伴随核心业务。如一笔电商交易，用户扣钱、下单、记流水，这就是一个核心业务的微模型。

所以，用户对流水表有性能需求。此外，流水又非常大，启用压缩功能可更为有效地存储数据。

此外，DDL尽量在非高峰期操作。防止对线上业务产生影响。

打包并删除旧数据

在业务层面，我们可以打包和删除旧的数据，在降低table数据行的数量。

这是另外一个话题，具体的设计和实现将在后面的blog中详细讨论。

小结

我们简要地描述了如何使用各种技术组合来优化我们数据库中的读取、写入和空间使用。 希望你也能找到至少一种有用的方法！