MYSQL表结构优化场景

合理设计表字段类型

根据字段的实际用途选择最合适的数据类型。例如 char、varchar()、int、bigint，datetime 还是 timestamp

创建合适的索引

主键索引：每个表都应该有一个主键，主键字段通常是唯一的且不可为空。
唯一索引：如果某个字段的值必须唯一，可以为其创建唯一索引。
普通索引：为经常用于查询条件的字段创建普通索引，可以提高查询效率。
复合索引：如果查询条件涉及多个字段，可以创建复合索引。例如，对于查询条件 WHERE a = ? AND b = ?，可以创建复合索引 (a, b)。

表分区

水平分区：将表中的数据按行分割到多个分区中，可以提高查询效率，尤其是在处理大量数据时。例如，可以根据日期或用户 ID 进行分区。
垂直分区：将表中的字段按列分割到多个表中，可以减少表的宽度，提高查询效率。例如，将不常用的字段移到单独的表中。

归一化与反归一化

归一化（范式）：将数据分解到多个表中，减少数据冗余，提高数据一致性。例如，将用户信息和订单信息分别存储在不同的表中。
反归一化（反范式）：在某些情况下，为了提高查询效率，可以适当冗余一些数据。例如，在订单表中冗余用户的基本信息，减少表连接操作。

## 合理使用外键

外键约束：外键可以确保数据的完整性，但在某些情况下可能会降低性能。如果性能是首要考虑因素，可以考虑在应用层实现数据完整性约束。
外键的维护成本：外键会增加插入、更新和删除操作的开销，尤其是在表数据量较大时。

优化表的存储引擎

InnoDB：支持事务、行级锁和外键，适合需要高并发和事务支持的场景。
MyISAM：不支持事务，但查询速度较快，适合读多写少的场景。

分表分库

分表：当表的数据量过大时，可以将表拆分成多个子表，每个子表存储部分数据。例如，按日期或用户 ID 分表。
分库：将不同的业务数据存储在不同的数据库中，可以减少单个数据库的压力，提高系统的可扩展性。

监控和分析

性能监控：使用 MySQL 的性能监控工具（如 SHOW STATUS、SHOW GLOBAL STATUS）监控数据库的性能指标。
慢查询日志：启用慢查询日志，记录执行时间较长的查询语句，分析并优化这些查询。

示例：优化一个用户表

假设有一个用户表 users，包含以下字段：

优化前的表结构

1
2
3
4
5
6
7


CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL,
    last_login DATETIME
);

优化后的表结构

字段类型优化：
- 将 username 和 email 的长度调整为合理的值（如 50 和 100）。
- 使用 TIMESTAMP 替代 DATETIME，节省空间。
索引优化：
- 为 username 和 email 添加唯一索引。
- 为 last_login 添加普通索引，方便按最后登录时间查询。
表分区：
- 按 created_at 进行水平分区，按年或月分区。
存储引擎选择：
- 使用 InnoDB 存储引擎，支持事务和行级锁。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL,
    last_login TIMESTAMP,
    INDEX (last_login)
) ENGINE=InnoDB PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);