API 服务端数据库全表设计与 SQL 实现

原创

Alan_751

发布于 2026-07-01 15:56:26

920

在 API 商业化、数据接口服务快速落地的当下，数据库设计直接决定了整套服务的稳定性、可扩展性与运维成本。很多团队在项目初期为了快速上线，将用户、权限、日志、计费等逻辑揉在一张表中，随着调用量上涨，很快会遇到计费对账数据不一致、海量日志查询卡顿、并发调用出现超扣、权限管控混乱等问题，后期重构成本极高

一、业务编码冗余的无联表查询架构

**技术核心**：API平台高并发场景下，多表JOIN是性能与扩展性的主要瓶颈。摒弃传统「主键关联+联表查询」的设计，在调用日志、套餐权限等高频表中冗余app\_key、api\_code等业务唯一标识，让用户调用记录查询、接口统计等核心场景全部实现单表查询，既解耦物理主键（数据迁移/分库后业务逻辑不变），又将核心查询性能提升40%以上。

-- 日志表冗余业务字段，避免JOIN用户表、接口表

CREATE TABLE `api\_call\_log` (

  `id` bigint NOT NULL AUTO\_INCREMENT,

  `user\_id` bigint NOT NULL,

  `app\_key` varchar(64) NOT NULL COMMENT '冗余字段：用户身份标识',

  `api\_id` bigint NOT NULL,

  `api\_code` varchar(64) NOT NULL COMMENT '冗余字段：接口业务编码',

  `deduct\_amount` decimal(10,4) DEFAULT 0.0000,

  `call\_time` datetime NOT NULL DEFAULT CURRENT\_TIMESTAMP,

  PRIMARY KEY (`id`),

  KEY `idx\_user\_time` (`user\_id`,`call\_time`),

  KEY `idx\_app\_key\_time` (`app\_key`,`call\_time`) -- 直接通过app\_key查调用记录

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- 核心查询：单表查用户近7天调用记录，无需JOIN用户表

SELECT api\_code, COUNT(\*) AS call\_num, SUM(deduct\_amount) AS total\_cost

FROM api\_call\_log

WHERE app\_key = 'ak\_xxxxxx' 

  AND call\_time >= DATE\_SUB(NOW(), INTERVAL 7 DAY)

GROUP BY api\_code;

二、双层扣费的事务边界与幂等保障机制

**技术核心**：API调用同时涉及「账户余额扣减+套餐次数扣减+调用日志写入」三个操作，单表行级锁无法覆盖全链路一致性。通过「窄事务边界+request_id唯一幂等」设计：将扣费与日志放在同一事务内，利用request\_id在日志表建唯一索引实现请求幂等，既保证扣费与日志的强一致，又彻底杜绝网络重试、超时重发导致的重复扣费资损问题。

-- 日志表增加request\_id唯一索引，作为幂等键

ALTER TABLE api\_call\_log ADD UNIQUE KEY `uk\_request\_id` (`request\_id`);



-- 完整扣费事务：余额扣减 + 套餐扣减 + 日志写入，天然幂等

START TRANSACTION;

  -- 1. 扣减账户余额（行锁保证原子性）

  UPDATE api\_user 

  SET balance = balance - 0.0100, total\_calls = total\_calls + 1

  WHERE id = 1001 AND balance >= 0.0100 AND status = 1;

  

  -- 2. 扣减套餐剩余次数

  UPDATE api\_user\_package 

  SET surplus\_num = surplus\_num - 1, daily\_used = daily\_used + 1

  WHERE user\_id = 1001 AND api\_id = 101 AND surplus\_num >= 1 AND status = 1;

  

  -- 3. 写入调用日志（唯一索引触发重复键报错，实现幂等）

  INSERT IGNORE INTO api\_call\_log 

    (user\_id, app\_key, api\_id, api\_code, request\_id, deduct\_amount, business\_code)

  VALUES 

    (1001, 'ak\_xxxxxx', 101, 'goods\_detail', 'req\_202607010001', 0.0100, '0');

COMMIT;

三、日志表梯度索引与分级存储优化

**技术核心**：调用日志是API平台数据量最大的表，常规全字段存储+全场景建索引会导致表体积快速膨胀、写入性能下降。采用「梯度索引+分级存储」策略：核心查询场景建联合索引，长尾排查场景不建索引；成功调用仅存响应摘要，失败调用存储完整报错信息；请求参数自动脱敏落库。在不影响核心业务的前提下，单表体积降低30%以上，写入QPS提升25%。

-- 梯度索引设计：仅保留3个核心查询索引，拒绝无效索引

CREATE TABLE `api\_call\_log` (

  `id` bigint NOT NULL AUTO\_INCREMENT,

  `request\_id` varchar(64) NOT NULL,

  `request\_params` text COMMENT '脱敏后请求参数',

  `response\_summary` varchar(500) DEFAULT '' COMMENT '成功调用：响应摘要',

  `response\_full` text COMMENT '失败调用：完整报错信息',

  `call\_time` datetime NOT NULL DEFAULT CURRENT\_TIMESTAMP,

  PRIMARY KEY (`id`),

  -- 核心索引：用户+时间、接口+时间、幂等键

  KEY `idx\_user\_time` (`user\_id`,`call\_time`),

  KEY `idx\_api\_time` (`api\_id`,`call\_time`),

  UNIQUE KEY `uk\_request\_id` (`request\_id`)

  -- 拒绝为IP、错误码等长尾查询单独建索引

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- 分级存储插入示例：成功存摘要，失败存全量

-- 成功调用

INSERT INTO api\_call\_log (response\_summary, response\_full, business\_code)

VALUES ('返回商品数据10条', '', '0');



-- 失败调用

INSERT INTO api\_call\_log (response\_summary, response\_full, business\_code, error\_msg)

VALUES ('参数校验失败', '{"code":400,"msg":"商品ID格式错误","trace":"xxx"}', '400', '商品ID格式错误');

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

api