跳至主要內容

MySQL高性能优化规范建议总结

JavaJuice数据库MySQL约 4380 字大约 15 分钟

数据库命名规范

1. 统一使用小写字母并用下划线分割

数据库对象（库名、表名、列名、索引名、触发器名等）必须使用小写字母，并使用 _ 进行单词分隔，例如：

  • 表名：user_profile，order_detail
  • 列名：user_id，created_at
  • 索引名：idx_user_profile_name

原因：

  • MySQL 在 Windows 和 macOS 上的表名不区分大小写，而在 Linux 上区分大小写，保持小写可以避免跨平台兼容性问题。
  • 统一规范，提高代码可读性，避免混淆。

2. 禁止使用 MySQL 保留关键字

MySQL 关键字（如 order、group、select 等）不能直接用于数据库对象的命名，避免查询时产生歧义。例如：

SELECT * FROM `order`; -- 错误示范（order 是关键字）

如果必须使用，需要加反引号（``），但尽量避免：

SELECT * FROM `order`; -- 需要加反引号，不推荐

最佳做法：

3. 数据库对象命名需见名识意，且长度不超过 32 个字符

  • 见名识意：确保数据库对象名称能够清晰表达其作用。例如：
    • customer_order（用户订单）比 co（不清晰）更易读。
    • product_inventory（产品库存）比 pi 更明确。
  • 不超过 32 个字符：保证名称不会过长，影响数据库性能和可读性。

4. 临时表和备份表命名规范

临时表（tmp_）

  • 以 tmp_ 为前缀，并以 日期 作为后缀，避免与正式表混淆。例如：
    CREATE TABLE tmp_order_20240208 LIKE order;

备份表（bak_）

  • 以 bak_ 为前缀，并使用 时间戳 作为后缀，防止与正式表混淆。例如：
    CREATE TABLE bak_user_profile_202402081230 AS SELECT * FROM user_profile;

注意：

  • 临时表通常用于数据处理中，操作完成后应及时清理。
  • 备份表应设置存储周期，定期清理，避免占用数据库空间。

5. 关联列的列名和数据类型必须一致

  • 在不同表中存储相同数据的列（通常是外键或关联列）必须保证列名和数据类型一致。例如：

    CREATE TABLE user (
    user_id BIGINT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE order (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,  -- 关联 user 表，数据类型应一致
    total_amount DECIMAL(10,2)
);

    原因：

  • 避免隐式类型转换：如果 user_id 在 user 表中是 BIGINT，而在 order 表中是 VARCHAR(20)，会导致索引失效，影响查询效率。

  • 提高可读性：统一的命名和类型方便开发和维护。

总结
规则说明示例
小写+下划线统一命名风格user_profile
避免关键字禁止使用 MySQL 关键字order_info（避免 order）
见名识意，不超32字符便于理解和管理customer_order
临时表 tmp_YYYYMMDD临时数据表，避免污染正式表tmp_sales_20240208
备份表 bak_YYYYMMDDHHMM备份表，包含时间戳bak_order_202402081230
关联列名和类型一致保证索引有效，提升查询性能user_id BIGINT

这样做可以提高数据库的可维护性、可读性，并优化查询性能。

数据库基本设计规范

1. 所有表必须使用 InnoDB 存储引擎

  • 原因：

    • 支持事务（ACID 事务特性）。
    • 支持行级锁，避免 MyISAM 的表级锁，提高并发性能。
    • 支持崩溃恢复，MyISAM 无法保证数据的完整性。
    • 支持外键，增强数据一致性。

  • 示例：

    CREATE TABLE user (
    user_id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

2. 数据库和表的字符集统一使用 UTF8

  • 原因：

    • 统一字符集可以避免乱码问题。
    • 不同字符集比较时会转换，影响索引效率。
    • 若需要存储 emoji 表情，必须使用 utf8mb4，而不是 utf8（utf8 只支持最多 3 字节，而 emoji 需要 4 字节）。

  • 示例：

    CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 所有表和字段必须添加注释

  • 原因：

    • 提高数据库的可读性。
    • 方便维护和管理，减少歧义。

  • 示例：

    CREATE TABLE user (
    user_id BIGINT PRIMARY KEY COMMENT '用户 ID',
    name VARCHAR(100) NOT NULL COMMENT '用户姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

4. 尽量控制单表数据量，建议控制在 500 万以内

  • 原因：

    • 超大表会影响查询性能，修改表结构时影响更大。
    • 备份、恢复操作时间增长，影响系统可用性。

  • 解决方案：

    • 历史数据归档（适用于日志表）。
    • 分库分表（适用于业务数据）。

5. 谨慎使用 MySQL 分区表

  • 原因：

    • 跨分区查询可能会变慢，不一定比普通表快。
    • 物理上是多个文件，逻辑上是一个表，管理复杂。
    • 不能使用外键。

  • 建议：

    • 优先使用物理分表，避免分区表带来的额外复杂性。

6. 经常一起使用的列放到一个表中

  • 原因：

    • 避免 多表 join 查询，提高性能。
    • 适当减少表拆分，提高数据访问效率。

  • 示例（合理的表设计）：

    CREATE TABLE order (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    order_status TINYINT NOT NULL COMMENT '订单状态'
);

    不要拆成 order_basic 和 order_status 两张表，避免额外的 join 操作。

7. 禁止在表中建立预留字段

  • 原因：
    • 难以做到见名知义，例如 col1, col2 无法表达实际含义。
    • 存储的数据类型无法确定，后续修改会导致数据不兼容。
    • 修改预留字段类型时会锁表，影响业务。

8. 禁止在数据库中存储大文件（如图片、视频）

  • 原因：

    • 影响数据库性能，导致查询变慢。
    • 数据库备份和恢复时间变长，影响运维。
    • 消耗大量数据库存储空间，成本更高。

  • 正确做法：

    • 将文件存储到文件服务器（如阿里云 OSS、七牛云等），数据库只存储文件 URL。

  • 示例：

    CREATE TABLE user_avatar (
    user_id BIGINT PRIMARY KEY,
    avatar_url VARCHAR(255) NOT NULL COMMENT '头像 URL'
);

9. 不要被数据库范式所束缚

  • 标准化设计（第三范式）：

    • 避免数据冗余，降低存储成本，减少数据异常。

  • 反范式设计：

    • 在高并发场景下，适当保留冗余数据，减少 Join，提高查询效率。

  • 示例（反范式适当冗余字段）：

    CREATE TABLE order (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    user_name VARCHAR(100) NOT NULL COMMENT '用户名称（冗余）',
    total_amount DECIMAL(10,2) NOT NULL
);

    这样可以避免在订单查询时 join user 表，提高性能。

10. 禁止在线上做数据库压力测试

  • 原因：

    • 会影响线上业务，可能导致服务不可用。
    • 可能触发锁表、死锁等问题。

  • 建议：

    • 在测试环境模拟压力测试。
    • 使用生产环境的镜像数据进行压测，但不要直接压测生产库。

11. 禁止从开发环境、测试环境直接连接生产数据库

  • 原因：

    • 安全隐患极大，容易误删或修改数据。
    • 生产环境数据属于核心资产，必须有严格的访问控制。

  • 建议：

    • 严格控制权限，仅 DBA 及少量授权人员可访问生产数据库。
    • 使用数据同步工具，如 MySQL binlog，进行数据同步到测试环境。
总结
规范说明示例
使用 InnoDB事务、行级锁、恢复性更好ENGINE=InnoDB
统一字符集为 UTF8MB4避免乱码，支持 emojiDEFAULT CHARSET=utf8mb4
所有表和字段必须有注释提高可读性和维护性COMMENT='用户表'
单表数据量 ≤ 500 万避免大表影响性能分库分表
谨慎使用 MySQL 分区物理多文件，管理复杂优先物理分表
避免过多 Join经常一起查询的列放一起order_status 放在 order 表
禁止预留字段预留字段影响可读性、数据类型不确定删除 col1, col2 预留字段
禁止存储大文件数据库存 URL，文件存 OSSavatar_url VARCHAR(255)
适当反范式为了高性能允许一定数据冗余user_name 冗余在 order 表
禁止线上压力测试避免影响生产业务使用测试环境模拟
禁止测试环境连生产库避免误操作，保障数据安全仅 DBA 有权限

按照这些规范设计数据库，可以提升系统的稳定性、可维护性，并提高查询性能。

索引设计规范

1. 限制每张表上的索引数量，建议单表索引不超过 5 个

  • 原因：
    • 索引并不是越多越好，索引可以提升查询速度，但会降低 INSERT、UPDATE、DELETE 的性能，因为每次写入数据时都需要维护索引。
    • MySQL 优化器 在执行 SQL 查询时，会评估多个索引，并选择最佳索引，索引过多会增加优化器的计算负担，影响查询性能。
  • 建议：
    • 确保索引真正能提高查询效率，而不是随意添加索引。
    • 优先考虑联合索引，而不是为每个字段创建单独的索引。

2. 禁止使用全文索引

  • 原因：
    • 全文索引不适用于 OLTP（联机事务处理）场景，而主要用于文本搜索（如文章、日志）。
    • 全文索引查询效率较低，大部分情况下可以用 ElasticSearch 或 Sphinx 替代。

3. 禁止给表中每一列都建立单独的索引

  • 原因：

    • MySQL 5.6 之前，一个 SQL 只能使用一个索引，多个单列索引可能无法发挥作用。
    • MySQL 5.6 之后支持索引合并，但仍然不如联合索引的查询效率高。

  • 正确做法：

    • 用联合索引替代多个单列索引，优先考虑 WHERE、ORDER BY、GROUP BY 中经常使用的列进行组合索引。

  • 示例（错误示范）：

    CREATE INDEX idx_name ON user(name);
CREATE INDEX idx_age ON user(age);

  • 示例（正确做法）：

    CREATE INDEX idx_name_age ON user(name, age);

4. 每个 InnoDB 表必须有主键

  • 原因：

    • InnoDB 使用主键索引组织数据存储，如果没有主键，则会自动创建一个隐藏的 6 字节 ROWID 作为主键。
    • 不建议使用更新频繁的列作为主键，否则会导致数据频繁移动，影响性能。
    • 不建议使用 UUID、MD5、HASH 作为主键，这些值是随机的，会导致数据写入时出现 页分裂（Page Split），影响查询效率。
    • 建议使用自增 ID 作为主键，保证数据按顺序写入，提高索引缓存命中率。

  • 错误示例（UUID 作为主键，影响写入性能）：

    CREATE TABLE user (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(100)
);

  • 正确示例（使用自增 ID 作为主键）：

    CREATE TABLE user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

5. 如何选择索引列的顺序

  • 最左前缀匹配原则：

    • 区分度最高的列放在最左侧，可以过滤最多的数据，提高查询效率。
    • 最常使用的查询列放在最左侧，以便索引被最大程度利用。

  • 示例（错误示范）：

    CREATE INDEX idx_age_name ON user(age, name);
    • 这样查询 WHERE name = '张三' 不会使用索引，因为 name 不是索引的最左侧字段。

  • 示例（正确示范）：

    CREATE INDEX idx_name_age ON user(name, age);
    • 这样可以支持 WHERE name = '张三' 和 WHERE name = '张三' AND age = 25 两种查询。

  • 区分度计算公式：

    SELECT COUNT(DISTINCT column) / COUNT(*) AS 区分度 FROM table;
    • 区分度越高（接近 1），该列作为索引的最左列越合适。

6. 避免建立冗余索引和重复索引

  • 重复索引示例（错误示范）：

    PRIMARY KEY (id);
CREATE INDEX idx_id ON table(id);
    • PRIMARY KEY 本身就是索引，idx_id 是重复索引，可以删除。

  • 冗余索引示例（错误示范）：

    CREATE INDEX idx_abc ON table(a, b, c);
CREATE INDEX idx_ab ON table(a, b);
CREATE INDEX idx_a ON table(a);
    • idx_ab 和 idx_a 已经包含在 idx_abc 中，不需要额外创建。

7. 使用覆盖索引提升查询效率

  • 覆盖索引：查询时所有字段都包含在索引里，无需回表查询数据。

  • 示例（错误示范，需要回表查询）：

    SELECT name FROM user WHERE age = 25;
    • age 是索引，但 name 不是，查询 name 需要回表。

  • 示例（正确示范，使用覆盖索引）：

    CREATE INDEX idx_age_name ON user(age, name);
SELECT name FROM user WHERE age = 25;  -- 直接从索引返回数据，无需回表

8. 避免使用外键约束

  • 建议：
    • 业务逻辑处理外键关系，而不是数据库层面使用 FOREIGN KEY 约束。
    • 手动在外键列上创建索引，保证查询效率。

SQL 开发规范（重要优化策略）

1. 禁止使用 SELECT *，必须指定查询字段

  • 原因：

    • 增加 CPU 计算量，消耗带宽。
    • 影响 MySQL 查询优化，无法使用覆盖索引。

  • 错误示范：

    SELECT * FROM user WHERE id = 1;

  • 正确示范：

    SELECT name, age FROM user WHERE id = 1;

2. 避免使用 LIKE '%关键字%'，索引会失效

  • 错误示范（索引失效）：

    SELECT * FROM user WHERE name LIKE '%张%';
    • % 在前会导致索引失效，全表扫描。

  • 正确示范（索引可用）：

    SELECT * FROM user WHERE name LIKE '张%';

3. 避免 WHERE 从句对索引列使用函数

  • 错误示范（索引失效）：

    WHERE YEAR(create_time) = 2023;

  • 正确示范：

    WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

4. 避免 ORDER BY RAND()

  • 错误示范（性能低下）：

    SELECT * FROM user ORDER BY RAND() LIMIT 10;

  • 正确示范：

    SELECT * FROM user WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM user))) LIMIT 10;
总结
规范说明
索引 ≤ 5 个避免索引过多影响查询优化
使用联合索引避免单列索引，遵循最左前缀原则
覆盖索引提高查询效率，减少回表操作
禁止 SELECT *指定查询字段，减少 CPU 计算
避免 %关键字%避免全表扫描，优化索引使用
禁止 ORDER BY RAND()影响性能，使用 id 方式优化

按照这些规范优化数据库，可以极大提升查询效率，提高 MySQL 的整体性能。

数据库操作行为规范

1. 超 100 万行的批量写（UPDATE、DELETE、INSERT）操作需分批执行

原因

  • 防止主从复制延迟：
    • 在主从复制环境中，大批量操作会导致从库执行延迟，影响读操作的实时性。
  • 减少 binlog 产生的日志量：
    • binlog 采用 ROW 格式时，会记录每一行的变更，大量写操作会导致 binlog 体积暴增，影响数据库的同步效率。
  • 避免长时间事务导致锁等待：
    • 大事务会导致 锁等待时间长，影响数据库的并发能力，并可能导致生产环境死锁。

解决方案

  • 采用 LIMIT + 分批处理
  • 使用 WHERE 结合主键范围
  • 避免全表扫描

示例（正确的分批 DELETE 方式）

-- 分批删除，每次删除 10000 条记录
DELETE FROM orders WHERE order_date < '2023-01-01' LIMIT 10000;
  • 优点：
    • 避免一次性锁住整张表。
    • 允许主从复制同步进行，减少主从复制延迟。

示例（正确的分批 UPDATE 方式）

-- 按主键范围更新数据，防止锁表
UPDATE users SET status = 'inactive' WHERE id BETWEEN 100000 AND 110000;

2. 对于大表，使用 pt-online-schema-change 修改表结构

问题

  • 直接 ALTER TABLE 可能锁表：
    • 在数据量较大的表上直接执行 ALTER TABLE 可能导致 锁表，使业务不可用。
  • 影响主从同步：
    • ALTER TABLE 操作会导致大事务，可能阻塞从库同步。

解决方案

使用 pt-online-schema-change（Percona Toolkit 工具），分批进行表结构修改。

示例（使用 pt-online-schema-change）

pt-online-schema-change --user=root --password=root --host=localhost \
--alter "ADD COLUMN status TINYINT DEFAULT 0" D=mydb,t=users --execute

  • 工作原理：

    1. 创建一个 新表，表结构与原表相同。
    2. 在新表上应用表结构变更。
    3. 通过触发器 同步新旧表数据。
    4. 数据同步完成后，重命名新表为原表。

  • 优点：

    • 避免锁表，确保业务不中断。
    • 减少主从复制压力。

3. 禁止为程序使用的数据库账号赋予 SUPER 权限

问题

  • SUPER 权限可用于 绕过 MySQL 连接数限制，但可能会被滥用：
    • 例如，达到最大连接数后，SUPER 用户仍能连接数据库。
    • 可能导致系统无法正确限制应用的连接数，影响数据库稳定性。

解决方案

  • SUPER 仅限 DBA 账号使用。
  • 程序账号应使用最小权限原则：
    CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';

4. 程序连接数据库账号必须遵循最小权限原则

原则

  • 程序账号只能访问一个数据库，不能跨库访问。
  • 程序账号不能有 DROP 权限，避免误删表。

示例（创建最小权限账号）

CREATE USER 'app_user'@'%' IDENTIFIED BY 'securepassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';
  • 不允许的权限：
    -- 禁止授予 DROP、SUPER、GRANT OPTION 权限
REVOKE DROP, ALTER, SUPER, GRANT OPTION ON mydb.* FROM 'app_user'@'%';
总结
规范说明
批量写操作分批进行避免主从复制延迟，减少事务锁定
大表变更使用 pt-online-schema-change避免锁表，防止业务停机
禁止程序账号使用 SUPER 权限避免数据库连接失控
程序账号使用最小权限只能访问指定数据库，不能有 DROP 权限

遵循这些规范，可以提升数据库的稳定性、安全性和可维护性。

推荐阅读

上次编辑于: 2025/2/8 20:47:33
贡献者: jiang