简介通讯录管理系统数据库设计与实现完整文档面向数据库课程设计、毕业设计以及需要开发同类信息管理系统的学习者。文档以通讯录管理为业务场景梳理用户基本信息、联系人信息、分组信息及归属关系等核心数据给出数据字典、实体-联系图转换、关系模型、数据库模式与用户子模式设计并详细设计了 Worker、Linkman、Grouping、Own 等数据表的结构与字段类型还提供 SQL 建库建表语句可帮助读者掌握从需求分析到物理实施的全流程数据库设计方法。资源包内为一个 doc 文件大小 422KB文档结构按章节展开层级清晰便于直接参考或引用其中的建表语句和设计思路。目前已有 997 人学习下载适合正在完成课程设计、准备毕业设计或自学数据库设计的读者借鉴。1. 通讯录管理系统数据库设计与实现看起来无外乎增删改查、导入导出真正动手时才会发现坑都在数据层面同一人名下可以有多个电话一个联系人可以属于几个分组微信、钉钉、住宅号码的属性不同这些在界面上是“多一点输入框”到了数据库里就是一张表拆还是不能拆的抉择。做错这一步后面所有查询都要加条件、补逻辑SQL无法覆盖的角落还要靠代码打补丁。这套方案给出一份可落地的通讯录管理系统数据库设计与实现围绕联系人、电话、分组、标签、扩展属性来组织数据模型并覆盖建表、索引、去重、导出等环节。适合需要从零设计后台、或者接手老系统要重构数据表的工程师即便只是做一次课程设计这套模型也能直接搬走。2. 通讯录管理系统的实体拆解与关系表设计2.1 先画逻辑模型联系人的电话号码为什么要单独建表很多初版设计会把联系人和电话放同一张表列成name、phone1、phone2、phone3。这样看起来简单但一旦有人需要新增传真号或临时手机号就要改表结构分组和标签更没法多对多。做通讯录管理系统数据库设计时通常把联系人作为核心实体电话、地址、分组、标签都围绕它展开。标准模型包含以下几类对象联系人、联系方式、分组、联系人与分组的关系、标签及其关系、扩展字段。核心对象如下联系人联系人ID、姓名、性别、生日、备注联系方式联系方式ID、联系人ID、类型、号码、优先级分组分组ID、分组名、父分组ID联系人与分组的关系联系人ID、分组ID标签标签ID、标签名、颜色联系人与标签的关系联系人ID、标签ID使用关系模型表达至少是三张主表加两张关系表。下面是简化后的表清单表名字段示例说明contactid, name, gender, birth_date, remark, is_deleted联系人主表contact_wayid, contact_id, type, number, priority联系方式子表contact_groupid, name, parent_id分组表contact_group_relid, group_id, contact_id联系人与分组关联表contact_tagid, name, color标签表contact_tag_relid, tag_id, contact_id联系人与标签关联表这种结构把“一个联系人有多个号码”变成“一个联系方式记录归属一个联系人”。之后任何界面上的动态添加联系方式都对应contact_way表的insert不再需要给主表加列。分组和标签同理。2.2 用关系模式把通讯录管理系统数据库设计落到字段逻辑模型确定后需要把每种实体的字段、类型、约束写清楚。先看核心关系模式contact ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, natural_id VARCHAR(32) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, gender TINYINT NOT NULL DEFAULT 0, birth_date DATE, remark VARCHAR(500), is_deleted TINYINT NOT NULL DEFAULT 0 ); contact_way ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, contact_id BIGINT UNSIGNED NOT NULL, type VARCHAR(20) NOT NULL, number VARCHAR(100) NOT NULL, priority TINYINT NOT NULL DEFAULT 0, is_deleted TINYINT NOT NULL DEFAULT 0 ); contact_group ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, parent_id BIGINT UNSIGNED ); contact_group_rel ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, contact_id BIGINT UNSIGNED NOT NULL, group_id BIGINT UNSIGNED NOT NULL );逻辑说明每张表都保留独立主键关系表增加联合唯一键避免重复绑定contact_way表的type保存手机、座机、邮箱、地址等类型而不是用整数枚举硬编码priority数值越小联系人卡片里排序越靠前。parent_id为NULL时表示根分组能支撑多级树形结构。分组和标签看起来相似但管理逻辑不同分组由管理员维护通常控制部门归属和访问范围标签由用户自建适合描述“家人”“同事”“VIP”这类临时属性。两部分拆开比合并成一张“分类表”更灵活后续做权限隔离时可以直接基于group字段过滤。2.3 主键选择不要拿手机号做联系人主键通讯录写入和查询都以联系人为主主键选择直接影响索引效率和后续迁移。自增BIGINT顺序写入对InnoDB聚簇索引友好占用空间小。UUID/雪花ID分布式生成方便但无序插入会造成页分裂。手机号作为业务主键会变更、为空、重复不适合做主键。身份证号同理属于敏感信息只能作为普通扩展字段。常见做法是BIGINT自增主键加上natural_id唯一键。自增主键只负责InnoDB内部组织natural_id负责外部系统传递手机号可以在contact_way表上建立唯一键前提是保存时统一去掉空格、横线统一存储为纯数字形式。主键方案需要和分库分表方案一起提前确认否则后续切分数据时需要把自增主键替换为分布式ID。3. 用SQL实现通讯录表结构从DDL到约束细节3.1 联系人主表和联系方式表的可执行DDL下面这段建表语句在MySQL 8.0中可直接执行使用InnoDB引擎和utf8mb4字符集。CREATE TABLE contact ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 联系人ID, natural_id VARCHAR(32) NOT NULL COMMENT 业务编号外部可读, name VARCHAR(50) NOT NULL COMMENT 联系人姓名, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性别0未知1男2女, birth_date DATE DEFAULT NULL COMMENT 生日, remark VARCHAR(500) DEFAULT COMMENT 备注, is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除标记, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_natural_id (natural_id), KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT联系人主表;逻辑说明natural_id专门放业务编号外部系统用这个编号引用联系人避免主键id暴露后撞库name列只建普通索引因为重名是通讯录的常态is_deleted用于软删除后续所有查询都要带上is_deleted 0。update_time依赖MySQL的ON UPDATE CURRENT_TIMESTAMP可以简化代码层的时间戳维护。联系方式表用于承载一个联系人的多个号码CREATE TABLE contact_way ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, contact_id BIGINT UNSIGNED NOT NULL, type VARCHAR(20) NOT NULL DEFAULT phone COMMENT phone/mobile/email/address, number VARCHAR(100) NOT NULL COMMENT 号码或地址内容, priority TINYINT NOT NULL DEFAULT 0 COMMENT 排序权重越小越靠前, is_deleted TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_contact_id (contact_id), KEY idx_number (number) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT联系方式表;参数说明type用VARCHAR(20)而不是整数枚举虽然多占空间但可读性好新增联系方式类型不需要改代码和文档。number字段要放邮箱、微信号、家庭住址长度放到100。idx_contact_id用于按联系人ID查询所有联系方式idx_number为按号码反查联系人服务。contact_id没有建物理外键因为线上批量导入时物理外键会带来额外锁开销数据一致性改由应用层控制。3.2 字符集、排序规则和字段长度的取舍通讯录里会出现中文、分组名、外部导入的emoji字符集必须使用utf8mb4不要用utf8mb3或旧表的latin1。排序规则建议使用utf8mb4_0900_ai_ci不区分大小写和重音对通讯录这种以查找为主要场景的模型更友好。如果数据库还是MySQL 5.7使用utf8mb4_unicode_ci效果接近。字段长度要放到索引场景里一起衡量字段推荐类型说明contact.nameVARCHAR(100)容纳少数民族姓名和“张三财务”这类附加写法contact.remarkVARCHAR(500)记录来源、跟进信息足够contact_way.numberVARCHAR(100)同时兼容电话和邮箱、地址contact_group.nameVARCHAR(50)分组名一般不需要太长name定成VARCHAR(100)之后索引页能容纳的键值会少一些但通讯录单表数据量通常在百万级以下完全可以接受。number字段如果只存手机号定VARCHAR(20)就够了可一旦扩展了邮箱和地址就不够因此定100更稳。业务上如果要跑手机号段统计单独保存一个mobile_prefix字段存手机号前7位查询时用等值条件比用表达式拆解字段快得多。3.3 关联表外键和逻辑删除要一起设计联系人与分组的关系表需要处理多对多和数据去重CREATE TABLE contact_group_rel ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, contact_id BIGINT UNSIGNED NOT NULL, group_id BIGINT UNSIGNED NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_contact_group (contact_id, group_id), KEY idx_group_id (group_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT联系人和分组关联表;联合唯一键uk_contact_group防止同一联系人重复加入同一分组idx_group_id支撑按分组查询联系人列表。物理外键不建不意味着不处理数据孤儿。删除联系人时如果走软删除只需更新contact.is_deleted如果必须物理删除要在事务里先删除contact_group_rel、contact_tag_rel、contact_way再删除contact本身。标签关系表结构类似把group_id换成tag_id即可。4. 通讯录高频查询优化索引、LIKE与批量导入去重4.1 按姓名和手机号反查联系人的索引策略正向查找走contact.name索引精确匹配和前缀匹配都能命中B树索引但名字只记得中间某个字时LIKE %三%就失效了。反查手机号同理contact_way.number列有普通索引精确匹配能走包含匹配不能走。查询场景SQL写法索引表现姓名精确name 张三命中idx_name姓名前缀name LIKE 张%命中idx_name姓名包含name LIKE %三%索引失效考虑全文索引手机号精确number 13800138000命中idx_number手机号包含number LIKE %1234%索引失效使用mobile_hash针对包含查询常见做法是提供两个搜索入口手机号精确、姓名模糊。手机号栏位要求完整输入姓名栏允许模糊匹配。如果产品必须支持手机号后四位反查需要额外用mobile_hash或搜索引擎而不是硬扛LIKE。4.2 一个典型的全表扫描案例和改法搜索框经常出现这样的SQLSELECT c.id, c.name, w.number FROM contact c LEFT JOIN contact_way w ON w.contact_id c.id AND w.type mobile WHERE c.name LIKE %张% OR w.number LIKE %1234%;执行计划大概率全表扫描因为OR条件让MySQL难以同时使用两个索引。改法是把两个场景拆开用UNION合并SELECT c.id, c.name, w.number FROM contact c JOIN contact_way w ON w.contact_id c.id WHERE c.name LIKE %张% UNION SELECT c.id, c.name, w.number FROM contact c JOIN contact_way w ON w.contact_id c.id AND w.type mobile WHERE w.number LIKE %1234%;逻辑说明第一个子句处理姓名模糊%张%虽然无法使用索引但只在名字字段上扫描第二个子句使用contact_way.type mobile缩小范围再对number做模糊匹配。结果集通过UNION自动去重。如果数据量超过几十万行更好的方案是维护一张基于全文索引的搜索表或者把搜索切到独立搜索服务但那种复杂度和运维成本大部分通讯录后台用不上。4.3 批量导入联系人时的去重SQL与性能优化导入Excel时最麻烦的是重复手机号被当成新联系人。第一步在临时表里准备数据第二步根据号码去重后再插入CREATE TEMPORARY TABLE tmp_import_contact ( name VARCHAR(100), mobile VARCHAR(20), KEY idx_mobile (mobile) ); INSERT INTO tmp_import_contact VALUES (张三, 13800138000), (李四, 13900139000); INSERT INTO contact (natural_id, name) SELECT UUID(), name FROM tmp_import_contact; INSERT INTO contact_way (contact_id, type, number) SELECT c.id, mobile, temp.mobile FROM tmp_import_contact temp JOIN contact c ON c.name temp.name LEFT JOIN contact_way existing ON existing.contact_id c.id AND existing.number temp.mobile WHERE existing.id IS NULL;逻辑说明临时表建了idx_mobile索引JOIN时不再全表扫描。向contact表插入后用姓名回联拿到新生成的id再向联系方式表插入。LEFT JOIN ... WHERE existing.id IS NULL只插入不存在的号码。这里假定临时表里的姓名在contact表中能对应到最新记录如果同姓名有多个历史联系人这个逻辑就会出错。更稳妥的做法是在导入前先按mobile在contact_way里反查contact_id把已有联系人id直接写入临时表然后决定更新还是跳过。性能上大批量导入时每批500-1000行提交避免大事务。应用层配合JDBC的rewriteBatchedStatementstrue可以明显减少网络round trip。数据导入完成后跑一次ANALYZE TABLE contact更新统计信息让MySQL规划出正确的执行计划。5. 用视图和存储过程完成通讯录导出与应用收尾5.1 视图让业务代码变薄多处业务都要查询联系人及其默认手机号、分组名。每次写大段JOIN既不必要也容易不一致可以建立一个视图CREATE VIEW v_contact_main AS SELECT c.id, c.name, w.number AS mobile, g.name AS group_name FROM contact c LEFT JOIN contact_way w ON w.contact_id c.id AND w.type mobile LEFT JOIN contact_group_rel cgr ON cgr.contact_id c.id LEFT JOIN contact_group g ON g.id cgr.group_id WHERE c.is_deleted 0 AND w.is_deleted 0;查询代码只用SELECT * FROM v_contact_main WHERE name LIKE 张%排序在视图外做。视图不会缓存结果每次查询都会展开为底层SQL单层视图提升可读性多层嵌套视图会拖慢性能不宜滥用。5.2 一个导出通讯录专用存储过程这类系统经常需要按分组导出一份CSV。存储过程可以统一参数校验和权限控制。DELIMITER $$ CREATE PROCEDURE sp_export_contact_list(IN p_group_id BIGINT, IN p_include_deleted TINYINT) BEGIN IF p_include_deleted 0 THEN SELECT c.id, c.name, c.birth_date, w.number FROM contact c LEFT JOIN contact_way w ON w.contact_id c.id WHERE c.is_deleted 0 AND (p_group_id IS NULL OR c.id IN (SELECT contact_id FROM contact_group_rel WHERE group_id p_group_id)) ORDER BY c.id; ELSE SELECT c.id, c.name, c.birth_date, w.number FROM contact c LEFT JOIN contact_way w ON w.contact_id c.id WHERE (p_group_id IS NULL OR c.id IN (SELECT contact_id FROM contact_group_rel WHERE group_id p_group_id)) ORDER BY c.id; END IF; END$$ DELIMITER ;参数说明p_group_id传入NULL时导出全量联系人p_include_deleted控制是否包含已删除联系人的数据。调用方式CALL sp_export_contact_list(1, 0)。存储过程不负责生成CSV文件职责是返回结果集文件生成由后端代码读取结果集完成SQL层和文件层分离后更容易测试和改版。5.3 别忘了字段加密和导出审计通讯录数据在多数场景属于个人信息数据库设计阶段就要考虑脱敏和审计。联系方式字段如果必须完整显示至少不能让日志和慢查询记录里直接出现明文常见做法是手机号用AES_ENCRYPT加密存储同时保留mobile_hash用于精确查询。业务查询时按需解密导出功能还要记录操作人和导出时间。可以给contact_way表增加created_by和updated_by字段操作人ID由应用层写入再配一个operation_log表记录导出行为字段包括operator_id、export_time、filter_condition、row_count。这样万一发生信息泄露可以快速排查导出记录。数据库备份按每天全量、每小时增量来规划备份文件加密压缩每季度做一次恢复演练。通讯录管理系统数据库设计与实现做到最后一步不是建完表而是让“谁能看到通讯录”这件事可追踪。本文还有配套的精品资源点击获取 
长河君 · 2026/9/17 12:57:39 · 阅读 1.2k 通讯录管理系统数据库设计：从表结构到索引优化
简介通讯录管理系统数据库设计与实现完整文档面向数据库课程设计、毕业设计以及需要开发同类信息管理系统的学习者。文档以通讯录管理为业务场景梳理用户基本信息、联系人信息、分组信息及归属关系等核心数据给出数据字典、实体-联系图转换、关系模型、数据库模式与用户子模式设计并详细设计了 Worker、Linkman、Grouping、Own 等数据表的结构与字段类型还提供 SQL 建库建表语句可帮助读者掌握从需求分析到物理实施的全流程数据库设计方法。资源包内为一个 doc 文件大小 422KB文档结构按章节展开层级清晰便于直接参考或引用其中的建表语句和设计思路。目前已有 997 人学习下载适合正在完成课程设计、准备毕业设计或自学数据库设计的读者借鉴。1. 通讯录管理系统数据库设计与实现看起来无外乎增删改查、导入导出真正动手时才会发现坑都在数据层面同一人名下可以有多个电话一个联系人可以属于几个分组微信、钉钉、住宅号码的属性不同这些在界面上是“多一点输入框”到了数据库里就是一张表拆还是不能拆的抉择。做错这一步后面所有查询都要加条件、补逻辑SQL无法覆盖的角落还要靠代码打补丁。这套方案给出一份可落地的通讯录管理系统数据库设计与实现围绕联系人、电话、分组、标签、扩展属性来组织数据模型并覆盖建表、索引、去重、导出等环节。适合需要从零设计后台、或者接手老系统要重构数据表的工程师即便只是做一次课程设计这套模型也能直接搬走。2. 通讯录管理系统的实体拆解与关系表设计2.1 先画逻辑模型联系人的电话号码为什么要单独建表很多初版设计会把联系人和电话放同一张表列成name、phone1、phone2、phone3。这样看起来简单但一旦有人需要新增传真号或临时手机号就要改表结构分组和标签更没法多对多。做通讯录管理系统数据库设计时通常把联系人作为核心实体电话、地址、分组、标签都围绕它展开。标准模型包含以下几类对象联系人、联系方式、分组、联系人与分组的关系、标签及其关系、扩展字段。核心对象如下联系人联系人ID、姓名、性别、生日、备注联系方式联系方式ID、联系人ID、类型、号码、优先级分组分组ID、分组名、父分组ID联系人与分组的关系联系人ID、分组ID标签标签ID、标签名、颜色联系人与标签的关系联系人ID、标签ID使用关系模型表达至少是三张主表加两张关系表。下面是简化后的表清单表名字段示例说明contactid, name, gender, birth_date, remark, is_deleted联系人主表contact_wayid, contact_id, type, number, priority联系方式子表contact_groupid, name, parent_id分组表contact_group_relid, group_id, contact_id联系人与分组关联表contact_tagid, name, color标签表contact_tag_relid, tag_id, contact_id联系人与标签关联表这种结构把“一个联系人有多个号码”变成“一个联系方式记录归属一个联系人”。之后任何界面上的动态添加联系方式都对应contact_way表的insert不再需要给主表加列。分组和标签同理。2.2 用关系模式把通讯录管理系统数据库设计落到字段逻辑模型确定后需要把每种实体的字段、类型、约束写清楚。先看核心关系模式contact ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, natural_id VARCHAR(32) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, gender TINYINT NOT NULL DEFAULT 0, birth_date DATE, remark VARCHAR(500), is_deleted TINYINT NOT NULL DEFAULT 0 ); contact_way ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, contact_id BIGINT UNSIGNED NOT NULL, type VARCHAR(20) NOT NULL, number VARCHAR(100) NOT NULL, priority TINYINT NOT NULL DEFAULT 0, is_deleted TINYINT NOT NULL DEFAULT 0 ); contact_group ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, parent_id BIGINT UNSIGNED ); contact_group_rel ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, contact_id BIGINT UNSIGNED NOT NULL, group_id BIGINT UNSIGNED NOT NULL );逻辑说明每张表都保留独立主键关系表增加联合唯一键避免重复绑定contact_way表的type保存手机、座机、邮箱、地址等类型而不是用整数枚举硬编码priority数值越小联系人卡片里排序越靠前。parent_id为NULL时表示根分组能支撑多级树形结构。分组和标签看起来相似但管理逻辑不同分组由管理员维护通常控制部门归属和访问范围标签由用户自建适合描述“家人”“同事”“VIP”这类临时属性。两部分拆开比合并成一张“分类表”更灵活后续做权限隔离时可以直接基于group字段过滤。2.3 主键选择不要拿手机号做联系人主键通讯录写入和查询都以联系人为主主键选择直接影响索引效率和后续迁移。自增BIGINT顺序写入对InnoDB聚簇索引友好占用空间小。UUID/雪花ID分布式生成方便但无序插入会造成页分裂。手机号作为业务主键会变更、为空、重复不适合做主键。身份证号同理属于敏感信息只能作为普通扩展字段。常见做法是BIGINT自增主键加上natural_id唯一键。自增主键只负责InnoDB内部组织natural_id负责外部系统传递手机号可以在contact_way表上建立唯一键前提是保存时统一去掉空格、横线统一存储为纯数字形式。主键方案需要和分库分表方案一起提前确认否则后续切分数据时需要把自增主键替换为分布式ID。3. 用SQL实现通讯录表结构从DDL到约束细节3.1 联系人主表和联系方式表的可执行DDL下面这段建表语句在MySQL 8.0中可直接执行使用InnoDB引擎和utf8mb4字符集。CREATE TABLE contact ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 联系人ID, natural_id VARCHAR(32) NOT NULL COMMENT 业务编号外部可读, name VARCHAR(50) NOT NULL COMMENT 联系人姓名, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性别0未知1男2女, birth_date DATE DEFAULT NULL COMMENT 生日, remark VARCHAR(500) DEFAULT COMMENT 备注, is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除标记, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_natural_id (natural_id), KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT联系人主表;逻辑说明natural_id专门放业务编号外部系统用这个编号引用联系人避免主键id暴露后撞库name列只建普通索引因为重名是通讯录的常态is_deleted用于软删除后续所有查询都要带上is_deleted 0。update_time依赖MySQL的ON UPDATE CURRENT_TIMESTAMP可以简化代码层的时间戳维护。联系方式表用于承载一个联系人的多个号码CREATE TABLE contact_way ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, contact_id BIGINT UNSIGNED NOT NULL, type VARCHAR(20) NOT NULL DEFAULT phone COMMENT phone/mobile/email/address, number VARCHAR(100) NOT NULL COMMENT 号码或地址内容, priority TINYINT NOT NULL DEFAULT 0 COMMENT 排序权重越小越靠前, is_deleted TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_contact_id (contact_id), KEY idx_number (number) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT联系方式表;参数说明type用VARCHAR(20)而不是整数枚举虽然多占空间但可读性好新增联系方式类型不需要改代码和文档。number字段要放邮箱、微信号、家庭住址长度放到100。idx_contact_id用于按联系人ID查询所有联系方式idx_number为按号码反查联系人服务。contact_id没有建物理外键因为线上批量导入时物理外键会带来额外锁开销数据一致性改由应用层控制。3.2 字符集、排序规则和字段长度的取舍通讯录里会出现中文、分组名、外部导入的emoji字符集必须使用utf8mb4不要用utf8mb3或旧表的latin1。排序规则建议使用utf8mb4_0900_ai_ci不区分大小写和重音对通讯录这种以查找为主要场景的模型更友好。如果数据库还是MySQL 5.7使用utf8mb4_unicode_ci效果接近。字段长度要放到索引场景里一起衡量字段推荐类型说明contact.nameVARCHAR(100)容纳少数民族姓名和“张三财务”这类附加写法contact.remarkVARCHAR(500)记录来源、跟进信息足够contact_way.numberVARCHAR(100)同时兼容电话和邮箱、地址contact_group.nameVARCHAR(50)分组名一般不需要太长name定成VARCHAR(100)之后索引页能容纳的键值会少一些但通讯录单表数据量通常在百万级以下完全可以接受。number字段如果只存手机号定VARCHAR(20)就够了可一旦扩展了邮箱和地址就不够因此定100更稳。业务上如果要跑手机号段统计单独保存一个mobile_prefix字段存手机号前7位查询时用等值条件比用表达式拆解字段快得多。3.3 关联表外键和逻辑删除要一起设计联系人与分组的关系表需要处理多对多和数据去重CREATE TABLE contact_group_rel ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, contact_id BIGINT UNSIGNED NOT NULL, group_id BIGINT UNSIGNED NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_contact_group (contact_id, group_id), KEY idx_group_id (group_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT联系人和分组关联表;联合唯一键uk_contact_group防止同一联系人重复加入同一分组idx_group_id支撑按分组查询联系人列表。物理外键不建不意味着不处理数据孤儿。删除联系人时如果走软删除只需更新contact.is_deleted如果必须物理删除要在事务里先删除contact_group_rel、contact_tag_rel、contact_way再删除contact本身。标签关系表结构类似把group_id换成tag_id即可。4. 通讯录高频查询优化索引、LIKE与批量导入去重4.1 按姓名和手机号反查联系人的索引策略正向查找走contact.name索引精确匹配和前缀匹配都能命中B树索引但名字只记得中间某个字时LIKE %三%就失效了。反查手机号同理contact_way.number列有普通索引精确匹配能走包含匹配不能走。查询场景SQL写法索引表现姓名精确name 张三命中idx_name姓名前缀name LIKE 张%命中idx_name姓名包含name LIKE %三%索引失效考虑全文索引手机号精确number 13800138000命中idx_number手机号包含number LIKE %1234%索引失效使用mobile_hash针对包含查询常见做法是提供两个搜索入口手机号精确、姓名模糊。手机号栏位要求完整输入姓名栏允许模糊匹配。如果产品必须支持手机号后四位反查需要额外用mobile_hash或搜索引擎而不是硬扛LIKE。4.2 一个典型的全表扫描案例和改法搜索框经常出现这样的SQLSELECT c.id, c.name, w.number FROM contact c LEFT JOIN contact_way w ON w.contact_id c.id AND w.type mobile WHERE c.name LIKE %张% OR w.number LIKE %1234%;执行计划大概率全表扫描因为OR条件让MySQL难以同时使用两个索引。改法是把两个场景拆开用UNION合并SELECT c.id, c.name, w.number FROM contact c JOIN contact_way w ON w.contact_id c.id WHERE c.name LIKE %张% UNION SELECT c.id, c.name, w.number FROM contact c JOIN contact_way w ON w.contact_id c.id AND w.type mobile WHERE w.number LIKE %1234%;逻辑说明第一个子句处理姓名模糊%张%虽然无法使用索引但只在名字字段上扫描第二个子句使用contact_way.type mobile缩小范围再对number做模糊匹配。结果集通过UNION自动去重。如果数据量超过几十万行更好的方案是维护一张基于全文索引的搜索表或者把搜索切到独立搜索服务但那种复杂度和运维成本大部分通讯录后台用不上。4.3 批量导入联系人时的去重SQL与性能优化导入Excel时最麻烦的是重复手机号被当成新联系人。第一步在临时表里准备数据第二步根据号码去重后再插入CREATE TEMPORARY TABLE tmp_import_contact ( name VARCHAR(100), mobile VARCHAR(20), KEY idx_mobile (mobile) ); INSERT INTO tmp_import_contact VALUES (张三, 13800138000), (李四, 13900139000); INSERT INTO contact (natural_id, name) SELECT UUID(), name FROM tmp_import_contact; INSERT INTO contact_way (contact_id, type, number) SELECT c.id, mobile, temp.mobile FROM tmp_import_contact temp JOIN contact c ON c.name temp.name LEFT JOIN contact_way existing ON existing.contact_id c.id AND existing.number temp.mobile WHERE existing.id IS NULL;逻辑说明临时表建了idx_mobile索引JOIN时不再全表扫描。向contact表插入后用姓名回联拿到新生成的id再向联系方式表插入。LEFT JOIN ... WHERE existing.id IS NULL只插入不存在的号码。这里假定临时表里的姓名在contact表中能对应到最新记录如果同姓名有多个历史联系人这个逻辑就会出错。更稳妥的做法是在导入前先按mobile在contact_way里反查contact_id把已有联系人id直接写入临时表然后决定更新还是跳过。性能上大批量导入时每批500-1000行提交避免大事务。应用层配合JDBC的rewriteBatchedStatementstrue可以明显减少网络round trip。数据导入完成后跑一次ANALYZE TABLE contact更新统计信息让MySQL规划出正确的执行计划。5. 用视图和存储过程完成通讯录导出与应用收尾5.1 视图让业务代码变薄多处业务都要查询联系人及其默认手机号、分组名。每次写大段JOIN既不必要也容易不一致可以建立一个视图CREATE VIEW v_contact_main AS SELECT c.id, c.name, w.number AS mobile, g.name AS group_name FROM contact c LEFT JOIN contact_way w ON w.contact_id c.id AND w.type mobile LEFT JOIN contact_group_rel cgr ON cgr.contact_id c.id LEFT JOIN contact_group g ON g.id cgr.group_id WHERE c.is_deleted 0 AND w.is_deleted 0;查询代码只用SELECT * FROM v_contact_main WHERE name LIKE 张%排序在视图外做。视图不会缓存结果每次查询都会展开为底层SQL单层视图提升可读性多层嵌套视图会拖慢性能不宜滥用。5.2 一个导出通讯录专用存储过程这类系统经常需要按分组导出一份CSV。存储过程可以统一参数校验和权限控制。DELIMITER $$ CREATE PROCEDURE sp_export_contact_list(IN p_group_id BIGINT, IN p_include_deleted TINYINT) BEGIN IF p_include_deleted 0 THEN SELECT c.id, c.name, c.birth_date, w.number FROM contact c LEFT JOIN contact_way w ON w.contact_id c.id WHERE c.is_deleted 0 AND (p_group_id IS NULL OR c.id IN (SELECT contact_id FROM contact_group_rel WHERE group_id p_group_id)) ORDER BY c.id; ELSE SELECT c.id, c.name, c.birth_date, w.number FROM contact c LEFT JOIN contact_way w ON w.contact_id c.id WHERE (p_group_id IS NULL OR c.id IN (SELECT contact_id FROM contact_group_rel WHERE group_id p_group_id)) ORDER BY c.id; END IF; END$$ DELIMITER ;参数说明p_group_id传入NULL时导出全量联系人p_include_deleted控制是否包含已删除联系人的数据。调用方式CALL sp_export_contact_list(1, 0)。存储过程不负责生成CSV文件职责是返回结果集文件生成由后端代码读取结果集完成SQL层和文件层分离后更容易测试和改版。5.3 别忘了字段加密和导出审计通讯录数据在多数场景属于个人信息数据库设计阶段就要考虑脱敏和审计。联系方式字段如果必须完整显示至少不能让日志和慢查询记录里直接出现明文常见做法是手机号用AES_ENCRYPT加密存储同时保留mobile_hash用于精确查询。业务查询时按需解密导出功能还要记录操作人和导出时间。可以给contact_way表增加created_by和updated_by字段操作人ID由应用层写入再配一个operation_log表记录导出行为字段包括operator_id、export_time、filter_condition、row_count。这样万一发生信息泄露可以快速排查导出记录。数据库备份按每天全量、每小时增量来规划备份文件加密压缩每季度做一次恢复演练。通讯录管理系统数据库设计与实现做到最后一步不是建完表而是让“谁能看到通讯录”这件事可追踪。本文还有配套的精品资源点击获取