MySQL 5.7 数据库设计实战：支撑10万用户的员工系统表结构与索引优化

MySQL优化数据库设计索引优化HR系统

于 2026-07-07 10:17:41 修改

·本内容遵循CC 4.0 BY-SA版权协议

MySQL 5.7 数据库设计实战：支撑10万用户的员工系统表结构与索引优化

当企业员工规模突破万人级别时，传统Excel式管理早已捉襟见肘。我曾参与过一个跨国企业的HR系统重构项目，最初的设计在3万用户量时就出现查询响应超时，经过三次架构迭代才最终稳定支持15万并发。本文将分享如何从数据库层面构建高可用员工管理系统，重点解析表结构设计与索引优化的实战经验。

1. 核心表结构设计

1.1 员工主表(employee)设计要点

SQL

CREATE TABLE `employee` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '雪花算法ID',

`employee_no` varchar(32) NOT NULL COMMENT '工号规则: DEPT+YYYYMM+4位序列',

`name` varchar(100) NOT NULL COMMENT '姓名+拼音索引',

`department_id` int(11) NOT NULL COMMENT '部门ID',

`position_id` int(11) NOT NULL COMMENT '职位ID',

`id_card` varchar(18) COLLATE utf8_bin DEFAULT NULL COMMENT '加密存储',

`mobile` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT 'AES加密',

`email` varchar(100) DEFAULT NULL,

`hire_date` date NOT NULL COMMENT '分区字段',

`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1在职 2离职',

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

UNIQUE KEY `uk_employee_no` (`employee_no`),

KEY `idx_department_position` (`department_id`,`position_id`),

KEY `idx_name_pinyin` (`name`(10))

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

PARTITION BY RANGE (YEAR(hire_date)) (

PARTITION p2020 VALUES LESS THAN (2021),

PARTITION p2021 VALUES LESS THAN (2022),

PARTITION p2022 VALUES LESS THAN (2023),

PARTITION pmax VALUES LESS THAN MAXVALUE

);

关键设计考量：

采用分区表按入职年份水平拆分，避免单表过大
敏感字段使用COLLATE utf8_bin+加密存储，平衡查询与安全
建立部门-职位联合索引，优化组织架构查询
姓名字段保留拼音前缀索引，支持模糊搜索

1.2 考勤表(attendance)的时序优化

SQL

CREATE TABLE `attendance` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`employee_id` bigint(20) NOT NULL,

`date` date NOT NULL COMMENT '分区字段',

`check_in` time DEFAULT NULL,

`check_out` time DEFAULT NULL,

`status` tinyint(4) NOT NULL COMMENT '1正常 2迟到 3早退...',

`remark` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`,`date`),

KEY `idx_employee_date` (`employee_id`,`date`),

KEY `idx_date_status` (`date`,`status`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

PARTITION BY RANGE (TO_DAYS(date)) (

PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),

PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),

/* 按月分区自动扩容 */

PARTITION pfuture VALUES LESS THAN MAXVALUE

);

提示：考勤数据具有强时间序列特征，按日分区配合双时间维度索引可显著提升统计效率

1.3 项目表(project)的关系建模

SQL

CREATE TABLE `project` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`owner_id` bigint(20) NOT NULL COMMENT '项目负责人',

`start_date` date NOT NULL,

`end_date` date DEFAULT NULL,

`status` enum('planning','ongoing','completed','cancelled') NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_status_dates` (`status`,`start_date`,`end_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `project_member` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`project_id` int(11) NOT NULL,

`employee_id` bigint(20) NOT NULL,

`role_type` tinyint(4) NOT NULL COMMENT '1负责人 2核心成员...',

`join_date` date NOT NULL,

`leave_date` date DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uk_project_employee` (`project_id`,`employee_id`),

KEY `idx_employee_projects` (`employee_id`,`join_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

多对多关系处理：

使用关联表project_member解耦项目与员工
建立双向索引优化两种查询场景：
- 查询项目成员列表（uk_project_employee）
- 查询员工参与项目（idx_employee_projects）

2. 索引深度优化策略

2.1 复合索引设计原则

在10万级用户系统中，索引设计需要遵循"三星索引"原则：

第一星：WHERE条件等值匹配列放最左

SQL

/* 优化前 */

ALTER TABLE employee ADD INDEX idx_name (name);

/* 优化后 */

ALTER TABLE employee ADD INDEX idx_dept_status (department_id, status);
第二星：ORDER BY/GROUP BY列加入索引

SQL

/* 优化排序查询 */

ALTER TABLE attendance ADD INDEX idx_employee_month (employee_id, date);
第三星：SELECT中的覆盖列

SQL

/* 覆盖索引示例 */

ALTER TABLE project ADD INDEX idx_status_owner (status, owner_id, name);

2.2 索引选择性优化

通过计算索引选择性决定是否建索引：

SQL

SELECT

COUNT(DISTINCT department_id)/COUNT(*) AS selectivity

FROM employee;

优化阈值参考：

选择性 > 0.2：适合建索引
0.1 < 选择性 ≤ 0.2：酌情考虑
选择性 ≤ 0.1：不应建索引

2.3 索引维护方案

在线索引变更方案：

BASH

# 使用pt-online-schema-change避免锁表

pt-online-schema-change \

--alter "ADD INDEX idx_performance (performance_score)" \

D=hr_db,t=employee \

--execute

索引监控脚本：

SQL

SELECT

object_schema,

object_name,

index_name,

rows_read,

rows_inserted,

rows_updated,

rows_deleted

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE index_name IS NOT NULL;

3. 高频查询优化实战

3.1 员工综合查询优化

典型场景：查询研发部绩效TOP 10的员工

SQL

/* 原始SQL */

EXPLAIN SELECT e.name, d.name AS dept, p.performance_score

FROM employee e

JOIN department d ON e.department_id = d.id

JOIN performance p ON e.id = p.employee_id

WHERE e.department_id = 5

ORDER BY p.performance_score DESC

LIMIT 10;

/* 优化方案 */

-- 1. 创建覆盖索引

ALTER TABLE performance ADD INDEX idx_department_performance (employee_id, performance_score);

-- 2. 改写SQL

EXPLAIN SELECT e.name, d.name AS dept, tmp.performance_score

FROM (

SELECT employee_id, performance_score

FROM performance

WHERE employee_id IN (

SELECT id FROM employee WHERE department_id = 5

)

ORDER BY performance_score DESC

LIMIT 10

) tmp

JOIN employee e ON tmp.employee_id = e.id

JOIN department d ON e.department_id = d.id;

3.2 考勤统计优化

月报统计SQL优化对比：

优化前	优化后
全表扫描attendance	分区裁剪只扫描当月分区
多次计算COUNT DISTINCT	使用预聚合中间表
实时计算迟到率	定时任务预计算指标

SQL

/* 预聚合表设计 */

CREATE TABLE attendance_stats (

stat_date date NOT NULL,

department_id int(11) NOT NULL,

total_count int(11) NOT NULL,

late_count int(11) NOT NULL,

PRIMARY KEY (stat_date, department_id)

) ENGINE=InnoDB;

/* 定时更新脚本 */

INSERT INTO attendance_stats

SELECT

DATE_FORMAT(date, '%Y-%m-01') AS stat_date,

e.department_id,

COUNT(*) AS total_count,

SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS late_count

FROM attendance a

JOIN employee e ON a.employee_id = e.id

WHERE date BETWEEN '2023-07-01' AND '2023-07-31'

GROUP BY stat_date, e.department_id

ON DUPLICATE KEY UPDATE

total_count = VALUES(total_count),

late_count = VALUES(late_count);

4. 性能压测与调优

4.1 基准测试方案

使用sysbench模拟10万用户并发：

BASH

# 准备测试数据

sysbench \

--db-driver=mysql \

--mysql-host=127.0.0.1 \

--mysql-port=3306 \

--mysql-user=test \

--mysql-password=test \

--mysql-db=hr_db \

--tables=5 \

--table-size=100000 \

oltp_read_write prepare

# 执行测试

sysbench \

--threads=200 \

--time=300 \

--report-interval=10 \

oltp_read_write run

4.2 关键参数调优

my.cnf核心配置：

INI

[mysqld]

# 连接配置

max_connections = 500

thread_cache_size = 50

# 内存配置

innodb_buffer_pool_size = 12G # 物理内存的70%

innodb_buffer_pool_instances = 8

# 日志配置

innodb_log_file_size = 2G

innodb_flush_log_at_trx_commit = 2

sync_binlog = 100

# 查询优化

query_cache_type = 0

table_open_cache = 4000

innodb_stats_on_metadata = 0

4.3 监控指标预警

关键指标阈值：

指标	警告阈值	严重阈值	检查方法
QPS	8000	10000	SHOW GLOBAL STATUS LIKE 'Questions'
连接数	max_connections*0.8	max_connections*0.9	SHOW STATUS LIKE 'Threads_connected'
慢查询	>1%	>5%	pt-query-digest slow.log
CPU使用率	70%	90%	top -H -p `pidof mysqld`