4、数据库设计：ER图设计、核心表结构（客户表、线索表、商机表、活动表）、索引优化策略

好，咱们进入第四章。数据库设计这块，说实话，是整套销售自动化系统的地基。你想想看，业务逻辑再花哨，数据存得乱七八糟，后面全是坑。我在好几个项目里都见过，前期图省事，表结构随便搞搞，结果上线三个月，查询慢得像蜗牛，改又不敢改，那叫一个痛苦。

这一章，咱们就聊聊ER图怎么画，四张核心表怎么设计，以及索引怎么优化才能扛住高并发。嗯，都是实战经验，你拿过去就能用。

4.1 ER图设计：先理清关系，再动手建表

我个人习惯，设计数据库之前，一定先画ER图。别急着写SQL，先把实体和关系理清楚。销售自动化系统里，核心实体就那么几个：客户、线索、商机、活动。

它们之间的关系，说白了就是一条转化链路：

• 线索 是潜在客户，可能来自网站、展会、朋友介绍。
• 线索经过跟进、验证，转化 成客户。
• 客户有购买意向后，创建 商机，也就是具体的销售机会。
• 围绕商机，会有一系列 活动，比如打电话、发邮件、开会。

这里有个关键点：一个客户可以有多个商机，一个商机可以有多个活动。但一个线索只能对应一个客户（转化后）。这种一对多、多对多的关系，在ER图上要标清楚。

我曾经见过一个团队，把线索和客户混在一张表里，结果数据冗余得一塌糊涂。你想想看，线索还没转化呢，客户字段全是空的，查询时还得加一堆IS NOT NULL条件，性能能好吗？所以，该拆就拆，别偷懒。

4.2 核心表结构：四张表，一个都不能少

好，关系理清了，咱们来看具体表结构。我直接给出DDL，你照着建就行。注意，我加了一些注释，方便你理解每个字段的用途。

4.2.1 客户表（customer）

客户表存的是已经验证过的、有明确联系方式的客户信息。这是最核心的表，所有业务都围着它转。

CREATE TABLE customer (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
    customer_name VARCHAR(100) NOT NULL COMMENT '客户名称（公司名或个人名）',
    phone VARCHAR(20) COMMENT '联系电话',
    email VARCHAR(100) COMMENT '邮箱',
    industry VARCHAR(50) COMMENT '所属行业',
    source VARCHAR(20) COMMENT '客户来源（如：线上注册、线下活动）',
    status TINYINT DEFAULT 1 COMMENT '状态：1-正常，0-禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_phone (phone),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';

这里要注意，phone 和 email 我加了索引，因为销售经常按手机号或邮箱查客户。嗯，这是高频查询字段，必须索引。

4.2.2 线索表（lead）

线索表存的是原始线索，可能信息不全。它和客户表是分开的，避免污染核心数据。

CREATE TABLE lead (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '线索ID',
    lead_name VARCHAR(100) NOT NULL COMMENT '线索名称',
    phone VARCHAR(20) COMMENT '联系电话',
    email VARCHAR(100) COMMENT '邮箱',
    source VARCHAR(20) COMMENT '线索来源（如：官网表单、广告投放）',
    status TINYINT DEFAULT 0 COMMENT '状态：0-未处理，1-已联系，2-已转化，3-已废弃',
    converted_customer_id BIGINT COMMENT '转化后的客户ID（关联customer.id）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_phone (phone),
    INDEX idx_status (status),
    INDEX idx_converted (converted_customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='线索表';

你看，converted_customer_id 这个字段，就是线索和客户之间的桥梁。线索一旦转化，就把客户ID写进去，状态改成2。这样查询已转化的线索时，直接关联客户表，非常清晰。

4.2.3 商机表（opportunity）

商机表记录的是具体的销售机会。比如，客户A要采购一套CRM系统，这就是一个商机。它关联客户表，同时记录金额、阶段、预计成交日期等。

CREATE TABLE opportunity (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '商机ID',
    customer_id BIGINT NOT NULL COMMENT '关联客户ID',
    opportunity_name VARCHAR(200) NOT NULL COMMENT '商机名称',
    amount DECIMAL(12,2) COMMENT '预计金额',
    stage TINYINT DEFAULT 1 COMMENT '销售阶段：1-初步接触，2-需求分析，3-方案报价，4-谈判，5-赢单，6-输单',
    probability TINYINT COMMENT '赢单概率（百分比）',
    expected_close_date DATE COMMENT '预计成交日期',
    assigned_to BIGINT COMMENT '负责人ID（关联用户表）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_customer_id (customer_id),
    INDEX idx_stage (stage),
    INDEX idx_assigned_to (assigned_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商机表';

这里有个细节：stage 字段我用TINYINT，而不是用VARCHAR存「初步接触」「需求分析」这些中文。为什么？因为销售阶段是固定的，用数字枚举，查询时直接走索引，快得很。显示的时候，前端再根据数字映射成中文就行。

4.2.4 活动表（activity）

活动表记录每一次跟进动作。比如，销售给客户打了电话、发了邮件、约了会议，都算一次活动。它关联商机表，也可以直接关联客户表（如果还没创建商机）。

CREATE TABLE activity (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '活动ID',
    opportunity_id BIGINT COMMENT '关联商机ID（可为空）',
    customer_id BIGINT COMMENT '关联客户ID（可为空）',
    activity_type TINYINT NOT NULL COMMENT '活动类型：1-电话，2-邮件，3-会议，4-拜访',
    content TEXT COMMENT '活动内容（记录沟通摘要）',
    activity_time DATETIME NOT NULL COMMENT '活动时间',
    created_by BIGINT COMMENT '创建人ID（关联用户表）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_opportunity_id (opportunity_id),
    INDEX idx_customer_id (customer_id),
    INDEX idx_activity_time (activity_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动表';

你可能会问，为什么 opportunity_id 和 customer_id 都可以为空？嗯，这是为了灵活性。有时候销售只是给客户打了个招呼，还没到商机阶段，活动就挂在客户ID上。等商机创建了，再关联商机ID。这样设计，不会漏掉任何一次跟进。

4.3 索引优化策略：别让查询成为瓶颈

表结构设计好了，索引就是性能的关键。我见过太多人，表建得漂漂亮亮，索引一个没加，结果数据一多，查询直接超时。下面我分享几个实战中总结的索引优化策略。

4.3.1 高频查询字段必加索引

哪些字段算高频？你想想看，销售每天查什么？

• 按手机号查客户 → phone 加索引
• 按状态查线索 → status 加索引
• 按负责人查商机 → assigned_to 加索引
• 按时间范围查活动 → activity_time 加索引

这些字段，我在上面的DDL里都已经加上了。但要注意，索引不是越多越好。每个索引都会增加写入时的开销。所以，只给真正频繁查询的字段加索引。

4.3.2 联合索引：覆盖更多查询场景

有时候，单个字段的索引不够用。比如，销售经常查「某个负责人名下，处于某个阶段的商机」。这时候，单字段索引 assigned_to 和 stage 都能用，但MySQL只能选一个。更好的做法是建一个联合索引：

ALTER TABLE opportunity ADD INDEX idx_assigned_stage (assigned_to, stage);

这样，查询 WHERE assigned_to = 100 AND stage = 3 时，直接走联合索引，效率翻倍。我曾经在一个项目里，把三个字段的联合索引加上后，查询时间从2秒降到了0.01秒，效果立竿见影。

4.3.3 避免索引失效的常见坑

索引建好了，但查询时没走索引，等于白建。我列几个常见的坑，你写SQL时注意避开：

• 不要在索引列上使用函数：比如 WHERE DATE(created_at) = '2024-01-01'，这样索引会失效。改成 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。
• 避免隐式类型转换：比如 phone 是VARCHAR，你查 WHERE phone = 13800138000（数字），MySQL会做类型转换，索引失效。记得加引号：WHERE phone = '13800138000'。
• LIKE查询注意通配符位置：WHERE customer_name LIKE '%张三%' 无法使用索引。如果必须模糊查询，考虑用全文索引（FULLTEXT）或者Elasticsearch。

嗯，这些坑我基本都踩过。有一次线上事故，就是因为一个隐式类型转换，导致全表扫描，数据库CPU直接飙到100%。从那以后，我写SQL都特别小心类型匹配。

4.3.4 定期维护索引

索引用久了，会产生碎片，影响性能。建议定期重建或优化索引：

-- 查看索引碎片情况
SELECT table_name, index_name, stat_value * @@innodb_page_size / 1024 / 1024 AS index_size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'opportunity';

-- 重建索引（MySQL 5.6+ 支持在线DDL）
ALTER TABLE opportunity ENGINE=InnoDB;

我一般建议，每个月做一次索引维护，尤其是在数据量大的表上。别等到出问题了再处理，那时候就晚了。

4.4 本章小结

好，这一章的内容就这些。咱们从ER图开始，理清了客户、线索、商机、活动之间的关系。然后给出了四张核心表的DDL，每张表都加了注释和索引。最后聊了索引优化的几个实战策略，包括联合索引、避免索引失效、定期维护等。

说实话，数据库设计没有银弹。每个系统都有自己的特点，但核心原则是一样的：先理关系，再建表，索引要精，查询要稳。你按照这个思路去设计，基本不会出大问题。

下一章，咱们聊聊后端接口设计，包括RESTful API规范、分页查询、权限控制等。到时候见。