MySQL 性能优化

MySQL 性能优化需要从架构设计、索引优化、查询优化、配置调优等多个维度入手。以下是核心优化手段及具体示例：

---

一、索引优化（关键步骤）

1. ​​最左前缀匹配原则​​

• 联合索引 (a,b,c) 对 a、a+b、a+b+c 的查询有效，但对 b 或 c 单独查询无效。
• ​​示例​​：

  sql

  复制

• ALTER TABLE users ADD INDEX idx_name_age (name, age);

• -- 有效查询：WHERE name='Tom' AND age=25

• -- 无效查询：WHERE age=25

2. ​​覆盖索引（Avoid回表）​​

• 索引包含查询所需的所有字段，无需回表查询数据行。
• ​​示例​​：

  sql

  复制

• -- 表结构：users(id, name, age, email)

• ALTER TABLE users ADD INDEX idx_name_age (name, age);

• -- 查询只需 name 和 age，直接通过索引返回结果

• SELECT name, age FROM users WHERE name = 'Tom';

3. ​​避免冗余索引​​

• 重复索引或冗余索引会增加写操作开销。
• ​​示例​​：
  若已有 (a,b) 索引，再建 (a) 是冗余的。

---

二、查询优化（核心场景）

1. ​​避免 SELECT *​​

• 只查询必要字段，减少数据传输和内存消耗。
• ​​示例​​：

  sql

  复制

• -- 低效 SELECT * FROM orders; -- 高效 SELECT order_id, user_id, amount FROM orders;

2. ​​分页优化（大数据量）​​

• 使用 WHERE id > 上一页最大ID 替代 LIMIT offset, size。
• ​​示例​​：

  sql

  复制

• -- 低效（offset=100万时性能差）

• SELECT * FROM logs LIMIT 1000000, 20;

• -- 高效（记录上一页最后一条的ID）

• SELECT * FROM logs WHERE id > 1000000 LIMIT 20;

3. ​​JOIN 优化​​

• 确保关联字段有索引，小表驱动大表。
• ​​示例​​：

  sql

  复制

• -- 假设 users.id 是主键，orders.user_id 有索引

• SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE users.country = 'CN';

---

三、表结构设计（基础优化）

1. ​​数据类型选择​​

• 使用最小合适的数据类型，如 INT 代替 VARCHAR 存储 IP。
• ​​示例​​：

  sql

  复制

• -- 低效

• CREATE TABLE logs (ip VARCHAR(15));

• -- 高效（使用无符号整数存储IPv4）

• CREATE TABLE logs (ip INT UNSIGNED);

2. ​​分区表（按时间范围）​​

• 对历史数据分区，加速时间范围查询。
• ​​示例​​：

  sql

  复制

• CREATE TABLE orders ( id INT, order_date DATE, ... ) PARTITION BY RANGE COLUMNS(order_date) ( PARTITION p2023 VALUES LESS THAN ('2024-01-01'), PARTITION p2024 VALUES LESS THAN MAXVALUE );

---

四、配置调优（参数优化）

1. ​​InnoDB Buffer Pool​​

• 设置为物理内存的 70%~80%，缓存热点数据。
• ​​配置示例​​：

  ini

  复制

• innodb_buffer_pool_size = 16G # 根据服务器内存调整

2. ​​日志写入策略​​

• 平衡性能与安全性：innodb_flush_log_at_trx_commit=2（允许丢失最近1秒数据）。
• ​​配置示例​​：

  ini

  复制

• innodb_flush_log_at_trx_commit = 2

• sync_binlog = 1000

---

五、工具诊断（定位瓶颈）

1. ​​慢查询日志​​

• 启用并分析执行时间超过阈值的 SQL。
• ​​配置示例​​：

  ini

  复制

• slow_query_log = 1 long_query_time = 2 # 记录超过2秒的查询

2. ​​EXPLAIN 分析执行计划​​

• 检查是否走索引、扫描行数、临时表等。
• ​​示例​​：

  sql

  复制

• EXPLAIN SELECT * FROM users WHERE name = 'Tom';

• -- 查看 key 是否为 idx_name_age，type 是否为 ref

---

六、高级优化（架构层面）

1. ​​读写分离​​

• 主库处理写操作，从库处理读操作。
• ​​工具​​：MySQL 主从复制 + ProxySQL。

2. ​​分库分表​​

• 按业务拆分（如垂直分库）或按范围分表（如 sharding by user_id）。
• ​​示例​​：

  sql

  复制

• -- 用户ID取模分表

• CREATE TABLE orders_0 (...) PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);

---

总结

性能优化的核心在于减少磁盘 I/O、利用索引加速查询、合理设计表结构和配置参数。通过工具定位瓶颈（如慢查询日志、EXPLAIN），结合业务场景选择最优方案。例如，对高频查询字段建立覆盖索引，对大数据量表进行分区或分表，通过读写分离分散压力。