MySQL 8.0 图书管理系统数据库实战：核心表设计与性能优化

1. 系统架构与数据库规划

图书管理系统作为典型的信息管理类应用，其数据库设计需要兼顾功能完整性与查询效率。我们采用MySQL 8.0作为数据库引擎，充分利用其窗口函数、CTE(公共表表达式)和原子DDL等新特性。系统主要包含以下核心模块：

• 图书信息管理
• 读者账户管理
• 借阅/归还业务处理
• 统计分析报表

数据库设计遵循第三范式(3NF)原则，同时针对高频查询场景做适当反范式化优化。系统整体架构采用分层设计：

应用层(Java/Python等) → 服务层 → 数据访问层 → MySQL 8.0数据库

2. 核心表结构设计与实现

2.1 图书信息表(book)

CREATE TABLE `book` (
  `book_id` VARCHAR(20) NOT NULL COMMENT '图书编号(ISBN+自增后缀)',
  `isbn` VARCHAR(17) NOT NULL COMMENT '国际标准书号',
  `title` VARCHAR(100) NOT NULL COMMENT '书名',
  `author` VARCHAR(50) NOT NULL COMMENT '作者',
  `publisher` VARCHAR(50) NOT NULL COMMENT '出版社',
  `publish_date` DATE NOT NULL COMMENT '出版日期',
  `category_id` INT NOT NULL COMMENT '分类ID',
  `price` DECIMAL(10,2) NOT NULL COMMENT '定价',
  `total_copies` INT NOT NULL DEFAULT 1 COMMENT '总副本数',
  `available_copies` INT NOT NULL COMMENT '可借副本数',
  `location` VARCHAR(30) COMMENT '馆藏位置',
  `cover_url` VARCHAR(255) COMMENT '封面图片URL',
  `description` TEXT COMMENT '图书简介',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`book_id`),
  UNIQUE KEY `idx_isbn` (`isbn`),
  KEY `idx_category` (`category_id`),
  KEY `idx_title` (`title`),
  KEY `idx_author` (`author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.2 读者信息表(reader)

CREATE TABLE `reader` (
  `reader_id` VARCHAR(20) NOT NULL COMMENT '读者证号',
  `name` VARCHAR(30) NOT NULL COMMENT '姓名',
  `gender` ENUM('M','F','O') NOT NULL COMMENT '性别',
  `birth_date` DATE COMMENT '出生日期',
  `phone` VARCHAR(20) NOT NULL COMMENT '联系电话',
  `email` VARCHAR(50) COMMENT '电子邮箱',
  `address` VARCHAR(100) COMMENT '联系地址',
  `id_card` VARCHAR(18) COMMENT '身份证号',
  `reader_type` TINYINT NOT NULL COMMENT '读者类型(1学生/2教师/3职工)',
  `max_borrow` INT NOT NULL DEFAULT 5 COMMENT '最大借阅量',
  `account_status` TINYINT NOT NULL DEFAULT 1 COMMENT '账户状态(1正常/2挂失/3注销)',
  `register_date` DATE NOT NULL COMMENT '注册日期',
  `expire_date` DATE NOT NULL COMMENT '有效期至',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`reader_id`),
  UNIQUE KEY `idx_id_card` (`id_card`),
  KEY `idx_phone` (`phone`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.3 借阅记录表(borrow_record)

CREATE TABLE `borrow_record` (
  `record_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `book_id` VARCHAR(20) NOT NULL COMMENT '图书ID',
  `reader_id` VARCHAR(20) NOT NULL COMMENT '读者ID',
  `borrow_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '借出日期',
  `due_date` DATETIME NOT NULL COMMENT '应还日期',
  `return_date` DATETIME COMMENT '实际归还日期',
  `renew_count` TINYINT NOT NULL DEFAULT 0 COMMENT '续借次数',
  `operator_id` VARCHAR(20) NOT NULL COMMENT '操作员ID',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(1借出/2已还/3逾期/4丢失)',
  `fine_amount` DECIMAL(10,2) 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 (`record_id`),
  KEY `idx_book_id` (`book_id`),
  KEY `idx_reader_id` (`reader_id`),
  KEY `idx_due_date` (`due_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.4 图书分类表(category)

CREATE TABLE `category` (
  `category_id` INT NOT NULL AUTO_INCREMENT COMMENT '分类ID',
  `category_name` VARCHAR(30) NOT NULL COMMENT '分类名称',
  `parent_id` INT DEFAULT NULL COMMENT '父分类ID',
  `category_code` VARCHAR(20) NOT NULL COMMENT '分类代码',
  `description` VARCHAR(200) COMMENT '分类描述',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `idx_category_code` (`category_code`),
  KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.5 系统用户表(sys_user)

CREATE TABLE `sys_user` (
  `user_id` VARCHAR(20) NOT NULL COMMENT '用户ID',
  `username` VARCHAR(30) NOT NULL COMMENT '用户名',
  `password` VARCHAR(100) NOT NULL COMMENT '加密密码',
  `real_name` VARCHAR(30) NOT NULL COMMENT '真实姓名',
  `phone` VARCHAR(20) NOT NULL COMMENT '联系电话',
  `email` VARCHAR(50) COMMENT '电子邮箱',
  `avatar` VARCHAR(255) COMMENT '头像URL',
  `role_id` INT NOT NULL COMMENT '角色ID',
  `last_login` DATETIME COMMENT '最后登录时间',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(1启用/0禁用)',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `idx_username` (`username`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

3. 关键视图设计与查询优化

3.1 未归还图书视图(v_unreturned_books)

CREATE VIEW v_unreturned_books AS
SELECT 
  r.record_id,
  b.book_id,
  b.title,
  b.isbn,
  rd.reader_id,
  rd.name AS reader_name,
  r.borrow_date,
  r.due_date,
  DATEDIFF(r.due_date, CURRENT_DATE()) AS remaining_days,
  CASE 
    WHEN r.due_date < CURRENT_DATE() THEN DATEDIFF(CURRENT_DATE(), r.due_date)
    ELSE 0
  END AS overdue_days,
  CASE
    WHEN r.due_date < CURRENT_DATE() THEN 
      LEAST(DATEDIFF(CURRENT_DATE(), r.due_date) * 0.5, 50)
    ELSE 0
  END AS estimated_fine
FROM borrow_record r
JOIN book b ON r.book_id = b.book_id
JOIN reader rd ON r.reader_id = rd.reader_id
WHERE r.return_date IS NULL;

3.2 读者借阅统计视图(v_reader_borrow_stats)

CREATE VIEW v_reader_borrow_stats AS
WITH reader_stats AS (
  SELECT 
    reader_id,
    COUNT(*) AS total_borrowed,
    SUM(CASE WHEN return_date IS NULL THEN 1 ELSE 0 END) AS current_borrowed,
    SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) AS total_overdue,
    MAX(CASE WHEN status = 3 THEN DATEDIFF(return_date, due_date) ELSE 0 END) AS max_overdue_days,
    SUM(fine_amount) AS total_fines
  FROM borrow_record
  GROUP BY reader_id
)
SELECT 
  r.reader_id,
  r.name,
  r.reader_type,
  r.max_borrow,
  rs.total_borrowed,
  rs.current_borrowed,
  (r.max_borrow - rs.current_borrowed) AS remaining_quota,
  rs.total_overdue,
  rs.max_overdue_days,
  rs.total_fines,
  CASE 
    WHEN rs.total_overdue > 3 THEN '高风险'
    WHEN rs.total_overdue > 0 THEN '中风险'
    ELSE '低风险'
  END AS risk_level
FROM reader r
LEFT JOIN reader_stats rs ON r.reader_id = rs.reader_id;

3.3 图书借阅排行视图(v_book_borrow_rank)

CREATE VIEW v_book_borrow_rank AS
SELECT 
  b.book_id,
  b.title,
  b.author,
  b.publisher,
  c.category_name,
  b.total_copies,
  b.available_copies,
  COUNT(br.record_id) AS borrow_count,
  RANK() OVER (ORDER BY COUNT(br.record_id) DESC) AS borrow_rank,
  AVG(DATEDIFF(COALESCE(br.return_date, CURRENT_DATE), br.borrow_date)) AS avg_borrow_days
FROM book b
LEFT JOIN borrow_record br ON b.book_id = br.book_id
LEFT JOIN category c ON b.category_id = c.category_id
GROUP BY b.book_id, b.title, b.author, b.publisher, c.category_name, b.total_copies, b.available_copies;

4. 事务处理与并发控制

4.1 借书事务处理

DELIMITER //
CREATE PROCEDURE sp_borrow_book(
  IN p_book_id VARCHAR(20),
  IN p_reader_id VARCHAR(20),
  IN p_operator_id VARCHAR(20),
  OUT p_result INT
)
BEGIN
  DECLARE v_available INT;
  DECLARE v_max_borrow INT;
  DECLARE v_current_borrow INT;
  DECLARE v_reader_status INT;
  
  START TRANSACTION;
  
  -- 检查读者状态
  SELECT account_status, max_borrow INTO v_reader_status, v_max_borrow
  FROM reader WHERE reader_id = p_reader_id FOR UPDATE;
  
  IF v_reader_status != 1 THEN
    SET p_result = -1; -- 读者账户异常
    ROLLBACK;
    LEAVE PROCEDURE;
  END IF;
  
  -- 检查读者当前借阅量
  SELECT COUNT(*) INTO v_current_borrow 
  FROM borrow_record 
  WHERE reader_id = p_reader_id AND return_date IS NULL;
  
  IF v_current_borrow >= v_max_borrow THEN
    SET p_result = -2; -- 超过最大借阅量
    ROLLBACK;
    LEAVE PROCEDURE;
  END IF;
  
  -- 检查图书可用性
  SELECT available_copies INTO v_available
  FROM book WHERE book_id = p_book_id FOR UPDATE;
  
  IF v_available <= 0 THEN
    SET p_result = -3; -- 图书不可借
    ROLLBACK;
    LEAVE PROCEDURE;
  END IF;
  
  -- 执行借书操作
  INSERT INTO borrow_record(book_id, reader_id, borrow_date, due_date, operator_id)
  VALUES(p_book_id, p_reader_id, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), p_operator_id);
  
  -- 更新图书库存
  UPDATE book 
  SET available_copies = available_copies - 1
  WHERE book_id = p_book_id;
  
  SET p_result = 1; -- 借书成功
  COMMIT;
END //
DELIMITER ;

4.2 还书事务处理

DELIMITER //
CREATE PROCEDURE sp_return_book(
  IN p_record_id BIGINT,
  IN p_operator_id VARCHAR(20),
  IN p_condition TINYINT, -- 1完好 2损坏 3丢失
  OUT p_fine_amount DECIMAL(10,2),
  OUT p_result INT
)
BEGIN
  DECLARE v_book_id VARCHAR(20);
  DECLARE v_due_date DATETIME;
  DECLARE v_overdue_days INT;
  DECLARE v_lost_fine DECIMAL(10,2);
  
  START TRANSACTION;
  
  -- 获取借阅记录信息
  SELECT book_id, due_date INTO v_book_id, v_due_date
  FROM borrow_record 
  WHERE record_id = p_record_id AND return_date IS NULL FOR UPDATE;
  
  IF v_book_id IS NULL THEN
    SET p_result = -1; -- 记录不存在或已归还
    ROLLBACK;
    LEAVE PROCEDURE;
  END IF;
  
  -- 计算逾期罚款
  SET v_overdue_days = DATEDIFF(NOW(), v_due_date);
  IF v_overdue_days > 0 THEN
    SET p_fine_amount = LEAST(v_overdue_days * 0.5, 50); -- 每天0.5元，最高50元
  ELSE
    SET p_fine_amount = 0;
  END IF;
  
  -- 处理图书损坏或丢失
  IF p_condition = 2 THEN
    SET p_fine_amount = p_fine_amount + 20; -- 损坏罚款20元
  ELSEIF p_condition = 3 THEN
    -- 获取图书价格
    SELECT price INTO v_lost_fine FROM book WHERE book_id = v_book_id;
    SET p_fine_amount = p_fine_amount + v_lost_fine * 2; -- 丢失按原价2倍赔偿
  END IF;
  
  -- 更新借阅记录
  UPDATE borrow_record
  SET 
    return_date = NOW(),
    status = CASE 
      WHEN p_condition = 3 THEN 4 
      WHEN v_overdue_days > 0 THEN 3 
      ELSE 2 
    END,
    fine_amount = p_fine_amount,
    operator_id = p_operator_id
  WHERE record_id = p_record_id;
  
  -- 更新图书库存(非丢失情况)
  IF p_condition != 3 THEN
    UPDATE book 
    SET available_copies = available_copies + 1
    WHERE book_id = v_book_id;
  END IF;
  
  SET p_result = 1; -- 还书成功
  COMMIT;
END //
DELIMITER ;

5. 索引优化与性能调优

5.1 关键索引设计

除了表结构中已定义的索引外，针对特定查询场景添加以下复合索引：

-- 读者借阅历史查询优化
CREATE INDEX idx_reader_borrow ON borrow_record(reader_id, borrow_date DESC);

-- 图书借阅统计查询优化
CREATE INDEX idx_book_borrow ON borrow_record(book_id, borrow_date);

-- 逾期图书查询优化
CREATE INDEX idx_overdue_books ON borrow_record(status, due_date) 
WHERE status IN (1,3) AND return_date IS NULL;

5.2 查询性能优化示例

高频查询1：获取读者当前借阅情况

EXPLAIN ANALYZE
SELECT b.title, b.author, br.borrow_date, br.due_date, 
       DATEDIFF(br.due_date, CURRENT_DATE()) AS remaining_days
FROM borrow_record br
JOIN book b ON br.book_id = b.book_id
WHERE br.reader_id = 'R20230001' AND br.return_date IS NULL
ORDER BY br.due_date;

优化建议 ：确保reader_id和return_date上有索引，使用覆盖索引避免回表。

高频查询2：搜索图书并显示可借状态

EXPLAIN ANALYZE
SELECT b.book_id, b.title, b.author, b.available_copies,
       (SELECT COUNT(*) FROM borrow_record br 
        WHERE br.book_id = b.book_id AND br.return_date IS NULL) AS borrowed_count
FROM book b
WHERE b.title LIKE '%数据库%' OR b.author LIKE '%王珊%'
ORDER BY b.title
LIMIT 20;

优化建议 ：对title和author字段建立全文索引，使用MATCH AGAINST替代LIKE：

ALTER TABLE book ADD FULLTEXT INDEX ft_title_author (title, author);

SELECT b.book_id, b.title, b.author, b.available_copies,
       (SELECT COUNT(*) FROM borrow_record br 
        WHERE br.book_id = b.book_id AND br.return_date IS NULL) AS borrowed_count
FROM book b
WHERE MATCH(b.title, b.author) AGAINST('+数据库 +王珊' IN BOOLEAN MODE)
ORDER BY b.title
LIMIT 20;

6. 数据安全与备份策略

6.1 敏感数据加密

对读者身份证号等敏感信息采用AES加密存储：

-- 加密函数
DELIMITER //
CREATE FUNCTION fn_encrypt(p_text VARCHAR(255)) RETURNS VARBINARY(255)
DETERMINISTIC
BEGIN
  RETURN AES_ENCRYPT(p_text, 'your-secret-key-123');
END //
DELIMITER ;

-- 解密函数
DELIMITER //
CREATE FUNCTION fn_decrypt(p_cipher VARBINARY(255)) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
  RETURN AES_DECRYPT(p_cipher, 'your-secret-key-123');
END //
DELIMITER ;

-- 使用示例
UPDATE reader SET id_card = fn_encrypt('110101199003077654');
SELECT name, fn_decrypt(id_card) AS id_card FROM reader;

6.2 定时备份方案

配置MySQL定时全量备份+binlog增量备份：

# 全量备份脚本(每周日)
mysqldump -u root -p your_password library_db \
  --single-transaction \
  --flush-logs \
  --master-data=2 \
  --routines \
  --triggers \
  --events \
  > /backup/library_full_$(date +%Y%m%d).sql

# 每日binlog备份
mysqladmin -u root -p flush-logs
cp /var/lib/mysql/mysql-bin.?????? /backup/binlog/

6.3 审计日志表设计

CREATE TABLE audit_log (
  log_id BIGINT NOT NULL AUTO_INCREMENT,
  user_id VARCHAR(20) COMMENT '操作用户ID',
  action VARCHAR(50) NOT NULL COMMENT '操作类型',
  table_name VARCHAR(50) NOT NULL COMMENT '操作表名',
  record_id VARCHAR(50) COMMENT '记录ID',
  old_value JSON COMMENT '旧值',
  new_value JSON COMMENT '新值',
  ip_address VARCHAR(50) COMMENT 'IP地址',
  user_agent VARCHAR(255) COMMENT '用户代理',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (log_id),
  KEY idx_action_time (action, create_time),
  KEY idx_user_time (user_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

7. 高级特性应用

7.1 使用窗口函数生成月度报表

-- 每月图书借阅统计
SELECT 
  YEAR(borrow_date) AS year,
  MONTH(borrow_date) AS month,
  COUNT(*) AS borrow_count,
  COUNT(DISTINCT reader_id) AS active_readers,
  COUNT(DISTINCT book_id) AS unique_books,
  SUM(CASE WHEN return_date > due_date THEN 1 ELSE 0 END) AS overdue_count,
  ROUND(SUM(CASE WHEN return_date > due_date THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS overdue_rate,
  RANK() OVER (ORDER BY COUNT(*) DESC) AS month_rank
FROM borrow_record
GROUP BY YEAR(borrow_date), MONTH(borrow_date)
ORDER BY year DESC, month DESC;

7.2 使用CTE递归查询分类层级

-- 递归查询分类及其所有子分类
WITH RECURSIVE category_tree AS (
  -- 基础查询：选择顶级分类
  SELECT category_id, category_name, parent_id, category_code, 1 AS level
  FROM category
  WHERE parent_id IS NULL
  
  UNION ALL
  
  -- 递归查询：连接子分类
  SELECT c.category_id, c.category_name, c.parent_id, c.category_code, ct.level + 1
  FROM category c
  JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT 
  category_id,
  CONCAT(REPEAT('    ', level - 1), category_name) AS tree_display,
  category_code,
  level
FROM category_tree
ORDER BY level, category_name;

7.3 使用生成列自动计算字段

-- 添加虚拟生成列计算图书年龄
ALTER TABLE book ADD COLUMN book_age TINYINT AS (
  TIMESTAMPDIFF(YEAR, publish_date, CURRENT_DATE)
) VIRTUAL COMMENT '出版年限';

-- 添加索引优化按年龄段的查询
CREATE INDEX idx_book_age ON book(book_age);