一、实名认证业务背景与数据需求分析

实名认证是互联网服务的基础功能，涉及用户身份核验、合规性存储及快速查询。其核心需求包括：

1. 身份信息完整性：需存储姓名、身份证号、证件类型等基础数据；
2. 核验状态追踪：记录认证流程（待审核、通过、拒绝）及失败原因；
3. 合规性要求：满足《网络安全法》对个人信息存储期限的规定；
4. 性能需求：支持高并发查询（如登录时验证）及批量核验操作。

典型业务场景包括：用户注册时提交证件信息、后台人工审核、定期合规性复查。这些场景要求数据库设计兼顾存储效率与查询性能。

二、核心表结构设计

1. 基础信息表（user_identity）

CREATE TABLE user_identity (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '关联用户ID',
    real_name VARCHAR(50) NOT NULL COMMENT '真实姓名',
    id_card_no VARCHAR(18) NOT NULL COMMENT '身份证号',
    id_card_type TINYINT UNSIGNED DEFAULT 1 COMMENT '证件类型：1-身份证 2-护照 3-其他',
    issue_authority VARCHAR(100) COMMENT '发证机关',
    valid_date DATE COMMENT '有效期',
    status TINYINT UNSIGNED DEFAULT 0 COMMENT '状态：0-待审核 1-通过 2-拒绝',
    reject_reason VARCHAR(255) COMMENT '拒绝原因',
    verify_time DATETIME COMMENT '审核时间',
    operator_id BIGINT UNSIGNED COMMENT '审核人ID',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_user_id (user_id),
    UNIQUE KEY uk_id_card_no (id_card_no),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户实名认证信息表';

设计要点：

• 采用utf8mb4字符集支持完整Unicode字符（如少数民族姓名）
• 设置双唯一索引：user_id确保单用户单认证，id_card_no防止证件重复注册
• 状态字段使用TINYINT节省空间，配合索引加速状态查询

2. 证件影像表（identity_images）

CREATE TABLE identity_images (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    identity_id BIGINT UNSIGNED NOT NULL COMMENT '关联认证ID',
    image_type TINYINT UNSIGNED NOT NULL COMMENT '图片类型：1-身份证正面 2-身份证反面 3-手持证件',
    image_url VARCHAR(255) NOT NULL COMMENT '图片存储路径',
    image_hash CHAR(32) NOT NULL COMMENT '图片MD5哈希值',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_identity_id (identity_id),
    KEY idx_image_type (image_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实名认证证件图片表';

设计要点：

• 存储图片哈希值实现快速去重检测
• 通过identity_id外键关联主表，保持数据一致性
• 图片类型字段支持多证件上传场景

三、关键优化策略

1. 索引优化方案

• 复合索引设计：对高频查询条件（如status + create_time）建立复合索引

  ALTER TABLE user_identity ADD KEY idx_status_create (status, create_time);

• 索引选择性分析：身份证号字段选择性接近100%，适合建唯一索引
• 避免过度索引：对更新频繁的字段（如update_time）慎建索引

2. 数据安全设计

• 字段级加密：对id_card_no采用AES-256加密存储

  -- 加密函数示例（需在应用层实现）
  DELIMITER //
  CREATE FUNCTION encrypt_id_card(id_card VARCHAR(18)) 
  RETURNS VARCHAR(32) DETERMINISTIC
  BEGIN
    -- 实际实现需调用加密库
    RETURN HEX(AES_ENCRYPT(id_card, 'your-secret-key'));
  END //
  DELIMITER ;

• 访问控制：通过MySQL权限系统限制敏感字段查询权限

  REVOKE SELECT(id_card_no) ON db_name.user_identity FROM 'app_user'@'%';
  GRANT SELECT(user_id, real_name, status) ON db_name.user_identity TO 'app_user'@'%';

3. 扩展性设计

• 分表策略：按用户ID哈希分表，应对千万级数据量

  -- 示例分表逻辑（实际需在应用层实现）
  $table_suffix = crc32($user_id) % 16;
  $query = "SELECT * FROM user_identity_{$table_suffix} WHERE user_id = ?";

• 历史数据归档：设置自动归档任务，将通过认证超过2年的数据移至归档表

四、典型查询优化案例

1. 批量状态查询优化

-- 原始查询（全表扫描）
SELECT user_id, real_name FROM user_identity 
WHERE status = 1 AND create_time > '2023-01-01';
-- 优化后（使用复合索引）
SELECT user_id, real_name FROM user_identity 
FORCE INDEX(idx_status_create) 
WHERE status = 1 AND create_time > '2023-01-01';

2. 证件号快速核验

-- 使用唯一索引加速
SELECT COUNT(*) FROM user_identity 
WHERE id_card_no = ENCRYPT('目标身份证号', '加密密钥');

五、运维监控建议

1. 慢查询监控：重点关注包含id_card_no字段的查询
2. 表空间监控：设置自动报警，当表大小超过预设阈值时触发分表操作
3. 定期维护：每月执行ANALYZE TABLE user_identity更新统计信息

六、合规性注意事项

1. 存储期限：设置自动清理任务，对拒绝认证超过6个月的数据进行匿名化处理
2. 审计日志：记录所有对敏感字段的修改操作

   CREATE TABLE identity_audit (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    operator_id BIGINT UNSIGNED NOT NULL,
    operation_type TINYINT NOT NULL COMMENT '1-创建 2-更新 3-删除',
    table_name VARCHAR(50) NOT NULL,
    record_id BIGINT UNSIGNED NOT NULL,
    before_value TEXT,
    after_value TEXT,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
   );

通过上述设计，可构建出既满足业务需求又符合合规要求的实名认证系统数据库架构。实际实施时需根据具体业务规模调整分表策略，并定期进行压力测试验证性能指标。