柠檬剧场 · 数据库优化分析报告

MySQL 8.0 数据库结构、索引、字段设计全面诊断
📅 2026-07-08 🗄️ MySQL (Aliyun RDS) 📊 191 张表 💾 SQL 导出: 175MB

1 数据库概览

191
数据表 (fa_ 前缀 183 + 无前缀 8)
110
表仅有主键,无二级索引 (57%)
26
表使用 utf8 字符集 (应为 utf8mb4)
309
INSERT 数据批次
369
总索引数 (186 PK + 165 KEY + 18 UK)
6
表使用 ROW_FORMAT=COMPACT

1.1 Top 10 最大数据量表

表名估算行数二级索引用途
fa_ad_data_report1,430,2251 (create_time+slot_id+adn)广告数据报表
fa_coin307,5403 (order_sn, user_id, source)金币记录
fa_user_money_log236,7310 (仅PK)会员余额变动表
fa_ad_income118,0777 (user_id+ecpm, sign, trans_id...)看广告收益
fa_ad_risk111,5921 (user_id+risk_level)点击率统计
fa_juese23,4610活跃指标
fa_ad_record20,8890ECPM 记录
fa_video_look14,8140浏览记录
fa_admin_log10,5840管理员日志
fa_user_whitelist9,1840用户白名单

2 🔴 严重问题:缺失索引

110 张表 (57%) 仅有主键,没有任何二级索引。以下为影响最大的几张表:

表名行数缺失索引的列影响场景优先级
fa_user_money_log 236,731 user_id, createtime, type 用户钱包明细查询、收益统计、间推分成计算 (GapClosing) P0 立即
fa_paylog ~5,000+ out_trade_no, uid, createtime 支付回调查单、用户订单列表 P0 立即
fa_ad_data_report 1,430,225 缺少 user_id、缺少 adn 单独索引 广告收益按用户/平台查询 P1 本周
fa_juese 23,461 user_id, createtime 用户活跃指标查询 P1 本周
fa_video_look 14,814 user_id, vid, createtime 用户观看记录、视频观看统计 P1 本周
fa_admin_log 10,584 admin_id, createtime 管理员操作审计 P2
fa_user ~6,269 缺少 invite、pid、parent_agent 索引 邀请码查询、上下级关系查询 P1 本周
P0 立即修复:fa_user_money_log 在 UserService.php 的 children()、GapClosing() 方法中频繁按 user_id + createtime + type 组合查询,236K 行全表扫描每次耗时数秒。
建议 DDL:
ALTER TABLE fa_user_money_log ADD INDEX idx_uid_ctime_type (user_id, createtime, type);
ALTER TABLE fa_paylog ADD UNIQUE INDEX idx_out_trade_no (out_trade_no);
ALTER TABLE fa_paylog ADD INDEX idx_uid_ctime (uid, createtime);

3 🔴 大表设计问题

3.1 fa_user 表:90+ 列的"上帝表"

fa_user 表包含 90+ 个字段,混杂了 6 种不同的业务概念:

列组字段数问题
认证信息realname, idcard, phone_number, is_auth, verification 等高频查询不需要的敏感字段拉低性能
资产字段money, coin, beans, ad_money, withdraw_money, freeze_money, wait_money 等 12 个每次更新资产都锁整行;应拆到独立资产表
设备信息device_type, device_brand, device_platform, os_version, app_version, device_id, device_now_id 等仅登录时写入,但占用每行空间
微信相关wxxopenid, gzhopenid, wxappopenid, unionid, openid 等 5 个大部分用户只有 1 个有值
佣金相关yong_rate, yong_two_rate, commission, parent_agent, user_type 等仅代理用户需要
优化方案:将 fa_user 垂直拆分为:
fa_user (核心: id, nickname, avatar, mobile, status) — 高频读取
fa_user_asset (money, coin, beans, ad_money, withdraw_money...) — 高频写入
fa_user_auth (realname, idcard, phone_number, is_auth) — 低频读取
fa_user_device (已存在 fa_user_device 表,但字段仍在 fa_user 中重复)

3.2 fa_videolist 表:utf8 + json 类型的混用

  • 该表使用 CHARSET=utf8 (非 utf8mb4),但其他表大多用 utf8mb4 — 跨表 JOIN 时字符集不一致导致索引失效
  • type 列使用 json 类型存储分类 — 无法建索引,JSON 查询效率低
  • csj 列 text 存穿山甲数据 — 建议拆到独立表或 JSON 列
  • 70+ 个字段中大量 medium/fixed 类型混用

3.3 fa_ad_income 表:复合主键 + 数值存为 varchar

  • PRIMARY KEY (id, createtime) — 复合主键导致二级索引体积翻倍 (二级索引存储完整 PK)
  • ecpm, estimated_earnings 等数值字段使用 varchar — 无法有效比较和聚合
  • errorCode, errorMsg — 错误信息存储在生产主表,增加行体积
  • 表包含 33 个字段,建议拆分为热数据 (核心收益) 和冷数据 (错误日志)

4 🟡 字符集与排序规则不一致

字符集表数问题
utf8mb4165✅ 正确
utf826需修复 MySQL 的 utf8 实为 utf8mb3,不支持 emoji 和部分中文字符

受影响的 26 张表包括:fa_video, fa_videolist, fa_user_group, fa_user_rule, fa_user_type, fa_ad_income6, fa_fengxian_record, fa_fengxian_record_log, fa_share_template, fa_agent_settlement 等。

修复:ALTER TABLE fa_video CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
注意:fa_videolist 的 type 列是 JSON 类型,ALTER 前需先检查兼容性。

此外,排序规则 (COLLATE) 也不统一:utf8mb4_unicode_ci、utf8mb4_bin、utf8mb4_general_ci 混用。跨 COLLATE 的 JOIN 会导致索引失效。

5 🟡 数据类型不当

问题涉及表详情建议
数值存为 varchar fa_ad_data_report estimated_earnings varchar(30), ecpm varchar(30) 改为 decimal(10,4)
IP 地址存为 varchar(50) fa_user loginip varchar(50), joinip varchar(50) 改为 varchar(45) (IPv6 最长) 或 VARBINARY(16)
密码仅 32 字节 fa_user password varchar(32) — 仅够 MD5 升级到 varchar(255) 以支持 bcrypt/argon2
int(1) / tinyint(1) 混用 多表 istel int(1), isrz int(1) — int(1) 仍占 4 字节 改为 tinyint(1)
datetime 用 int 存储 全部表 createtime int(10) — Unix 时间戳 无法使用 MySQL 日期函数直接查询;建议保留但确保索引覆盖
varchar 无长度限制 多表 varchar(255) 用于短字段 (如 status, type) 缩减为实际所需长度如 varchar(30)

6 🟡 日志表膨胀风险

以下日志/流水表持续增长,当前已较大,需制定归档策略:

表当前行数增长速率建议
fa_ad_data_report1,430,225高 (每小时写入)按月分表 或 迁移到 ClickHouse
fa_coin307,540高按季度归档,6个月前数据迁移
fa_user_money_log236,731高按季度归档,保留近6个月热数据
fa_ad_income118,077中按季度归档
fa_ad_risk111,592中按季度归档
推荐方案:广告数据报表 (fa_ad_data_report) 是典型的 OLAP 场景,建议定期 ETL 到 ClickHouse 进行分析,MySQL 中仅保留最近 30 天热数据。流水表 (fa_user_money_log, fa_coin) 按月分表 + 定期归档到对象存储。

7 冗余与废弃

问题详情
无前缀表app, gb, coin_history, coin_state 等 8 张表无 fa_ 前缀,疑似临时表或误导入
备份残留fa_tixian_config_copy1 — 明显的复制备份表,应清理
重复索引fa_ad_income 中 idx_new_sign_id 和 idx_sign_id 索引相同列的不同命名
前缀索引fa_user.loginip 使用前缀索引 KEY idx_loginip (loginip(15)),但 loginip 定义为 varchar(50),前缀 15 可能不够区分
无用字段fa_user.wxxopenid_bak (白名单备份) — 业务逻辑已注释但仍占用空间

8 优化优先级矩阵

#优化项影响风险优先级
1fa_user_money_log 添加 (user_id, createtime, type) 联合索引高低P0
2fa_paylog 添加 out_trade_no 唯一索引高低P0
3fa_user 添加 invite, pid, parent_agent 索引高低P0
426 张 utf8 表转换为 utf8mb4中中P1
5fa_ad_data_report 数值字段改为 decimal中低P1
6fa_user 垂直拆分 (资产/认证/设备)高高P2
7流水日志表分表/归档策略中中P1
8清理无前缀表 + 备份表 (fa_tixian_config_copy1)低低P1
9fa_ad_income 复合主键优化中高P2
10int(1) → tinyint(1) 统一低低P2
11启用 RDS 慢查询日志 + 定期审查中低P1
12fa_user.password 扩展为 varchar(255) 支持 bcrypt中中P2

9 RDS 配置建议

配置项当前建议
慢查询日志未确认slow_query_log=ON, long_query_time=0.5 — 当前 236K 行全表扫描必定触发
查询缓存未知MySQL 8.0 已移除 Query Cache,建议应用层用 Redis 缓存热点查询
InnoDB Buffer Pool未知建议设为可用内存的 70%,至少能容纳 fa_user 全表 + 热点索引
连接数未知PHP-FPM 模式下连接数 = worker 数,建议使用连接池代理 (ProxySQL)
备份策略未知RDS 自动备份 + Binlog 开启 (用于 CDC),保留 7 天
读写分离未启用 (deploy=0)启用 RDS 只读实例,报表查询走只读

10 总结

核心发现

  1. 57% 的表无二级索引 — 这是最大的性能隐患。其中 fa_user_money_log (236K行) 和 fa_paylog 的索引缺失直接影响支付和收益查询性能。
  2. fa_user 表过于臃肿 — 90+ 字段混合了认证、资产、设备、佣金等多种概念,InnoDB 行溢出风险高。
  3. 字符集不统一 — 26 张表仍使用 utf8 (utf8mb3),与 165 张 utf8mb4 表 JOIN 时索引可能失效。
  4. 日志表缺乏归档 — fa_ad_data_report 已接近 150 万行且持续增长,需引入 OLAP 引擎或分表策略。
  5. 数据类型选择不当 — 数值存 varchar、int(1) 浪费空间、密码字段仅 32 字节等问题普遍存在。

实施建议

P0 项目 (3 个索引添加) 可在业务低峰期 10 分钟内在线完成 (MySQL 8.0 支持 ALTER TABLE ... ALGORITHM=INPLACE)。

P1 项目建议在1-2 周内逐步实施,重点关注字符集转换和归档策略。

P2 项目可在后续迭代中结合代码重构同步进行。

报告生成: 2026-07-08 | 分析源: video_lemon_2026-06-12_09-28-19_mysql_data_aGzxq.sql (175MB) | 表数: 191

基于 SQL 导出静态分析。AUTO_INCREMENT 值作为行数估算,实际行数可能有偏差。