一、基础优化：硬件与系统配置

1. 硬件层面

• CPU：MySQL 对多核支持较好（如并行查询、事务处理），优先选择 高主频、多核心 的 CPU（如 Intel Xeon、AMD EPYC），避免单核性能不足导致的查询阻塞。
• 内存：MySQL 依赖内存缓存数据（如 InnoDB Buffer Pool），内存不足会导致频繁磁盘 IO。建议内存配置满足：
  物理内存 = InnoDB Buffer Pool + 操作系统预留（2-4G） + 其他服务内存，例如 16G 内存服务器，可分配 10-12G 给 Buffer Pool。
• 磁盘：磁盘 IO 是 MySQL 最常见的瓶颈，优先选择 SSD（固态硬盘），其随机读写速度是机械硬盘（HDD）的 10-100 倍；若用 HDD，建议使用 RAID 10（兼顾性能和冗余），避免单盘故障。

2. 系统配置（Linux 为例）

# /etc/sysctl.conf（核心参数）
net.core.somaxconn = 65535  # 允许的最大TCP连接队列，避免连接等待
net.ipv4.tcp_max_syn_backlog = 65535  # TCP三次握手的SYN队列大小
vm.swappiness = 0  # 禁用内存交换（避免内存数据写入磁盘，导致性能骤降）
fs.file-max = 1000000  # 系统允许的最大文件句柄数（MySQL需大量句柄）
innodb_flush_method = O_DIRECT  # InnoDB 直接写入磁盘，跳过操作系统缓存，减少双重缓存

# /etc/security/limits.conf（用户资源限制）
mysql soft nofile 65535  # MySQL用户软限制文件句柄数
mysql hard nofile 1000000  # MySQL用户硬限制文件句柄数

二、核心优化：MySQL 配置文件（my.cnf/my.ini）

三、结构优化：数据库与表设计

1. 数据库设计

• 避免过度分库分表：仅当单库数据量超 100G、单表超 10G 或并发超 5000 QPS 时，才考虑分库分表（如 Sharding-JDBC），否则增加复杂度。
• 隔离业务数据：不同业务（如用户、订单、商品）使用独立数据库，避免互相影响（如订单表慢查询阻塞用户表）。

2. 表设计

• 选择合适的数据类型：
  • 整数用 INT（4 字节）/ BIGINT（8 字节），避免用 VARCHAR 存身份证、手机号（无法索引）；
  • 字符串优先用 VARCHAR（变长，节省空间），固定长度用 CHAR（如性别 ' 男 '/' 女 '）；
  • 时间用 DATETIME（8 字节，存具体时间）/ TIMESTAMP（4 字节，存时间戳，范围小），避免用 VARCHAR（无法排序和范围查询）。
• 控制表字段数量：单表字段不超过 20 个，冗余字段（如订单表的 user_name）需权衡查询效率（避免 JOIN）和数据一致性（需同步更新）。
• 使用自增主键：InnoDB 表的主键是聚簇索引（数据按主键顺序存储），自增主键（INT AUTO_INCREMENT）可避免插入时索引碎片；避免用 UUID 作为主键（无序，导致频繁页分裂）。
• 分区表（大表优化）：若单表数据量超 5000 万行，可按时间（如订单表按月份分区）、范围（如用户表按 user_id 范围分区）拆分，减少查询扫描范围。

四、关键优化：索引设计

1. 索引类型选择

• 聚簇索引：InnoDB 唯一，即主键索引，数据与索引存储在一起，查询主键时直接返回数据。
• 非聚簇索引：普通索引、唯一索引等，仅存储索引值和主键，查询时需通过主键回表（即 “二次查询”），效率低于聚簇索引。
• 联合索引：多字段组合索引（如 (user_id, order_time)），需遵循 最左前缀原则（查询条件需包含左前缀字段，否则索引失效）。

2. 索引创建原则

• 高频查询字段优先：对 WHERE、JOIN、ORDER BY、GROUP BY 中的字段创建索引（如订单表的 user_id、order_time）。
• 避免冗余索引：若有联合索引 (a,b)，则无需单独创建 a 的索引（a 是左前缀，已被覆盖）。
• 控制索引数量：单表索引不超过 8 个，写入频繁的表（如日志表）索引更少（避免写入卡顿）。
• 小字段优先：索引字段长度越小，索引树占用空间越小，内存缓存的索引页越多，查询越快（如用 user_id 而非 user_name 做索引）。

3. 索引失效场景（重点规避）

• WHERE 子句中使用函数（如 DATE(order_time) = '2024-01-01'）、运算符（如 !=、<>、NOT IN）；
• 字符串不加引号（如 WHERE phone = 13800138000，若 phone 是 VARCHAR，会触发类型转换，索引失效）；
• 联合索引不满足最左前缀（如索引 (a,b)，查询 WHERE b = 1）；
• OR 连接的字段，若有一个无索引，则全部索引失效（如 WHERE a=1 OR b=2，b 无索引，则 a 的索引也失效）。

五、SQL 优化：提升查询效率

1. 用 EXPLAIN 分析 SQL

• type：索引使用类型，从好到差为 system > const > eq_ref > ref > range > ALL（ALL 表示全表扫描，需优化）；
• key：实际使用的索引，若为 NULL 表示未使用索引；
• rows：预估扫描的行数，越小越好；
• Extra：额外信息，如 Using filesort（磁盘排序，需优化）、Using temporary（临时表，需优化）、Using index（覆盖索引，优秀，无需回表）。

2. SQL 优化技巧

• 避免全表扫描：WHERE 子句需包含索引字段，避免 SELECT *（只查需要的字段，可能触发覆盖索引）；
• 优化 JOIN 操作：
  • 小表驱动大表（如 SELECT * FROM small_table s JOIN big_table b ON s.id = b.sid，减少循环次数）；
  • JOIN 字段需创建索引（避免全表匹配）；
• 优化排序和分组：
  • 排序字段（ORDER BY）尽量包含在联合索引中（如索引 (a,b)，ORDER BY b 可利用索引排序，避免 Using filesort）；
  • 避免 GROUP BY 无索引字段，同理可利用联合索引；
• 控制 LIMIT 分页：大表分页（如 LIMIT 100000, 20）会扫描前 100020 行再丢弃，优化方案：
  用主键过滤（WHERE id > 100000 LIMIT 20），前提是 id 连续；
• 避免 SELECT DISTINCT 和 UNION：DISTINCT 需去重，UNION 需合并排序，可用 GROUP BY 或 UNION ALL（无去重，效率高）替代。

六、缓存优化：减少数据库访问

1. MySQL 内置缓存

• InnoDB Buffer Pool：核心缓存，已在配置优化中提及，需确保缓存命中率（通过 SHOW ENGINE INNODB STATUS 查看，目标 95%+）；
• 查询缓存（已废弃）：MySQL 8.0 移除查询缓存，因缓存失效频繁（如更新表数据会清空该表所有缓存），不适合高写入场景。

2. 外部缓存（推荐）

• 缓存 key 设计：如 user:info:{user_id}、goods:detail:{goods_id}，避免 key 冲突；
• 过期时间：设置合理的过期时间（如商品信息缓存 1 小时），避免缓存雪崩（大量 key 同时过期）；
• 缓存穿透：对不存在的 key（如 user:info:999999）缓存空值，或用布隆过滤器过滤；
• 缓存击穿：热点 key（如热门商品）过期时，用互斥锁（Redis SETNX）避免大量请求穿透到数据库。

七、监控与排查：定位性能问题

1. MySQL 自带工具：
   • SHOW PROCESSLIST：查看当前连接状态，定位慢查询（Time 字段大的连接）；
   • SHOW STATUS：查看数据库状态（如 Connections 连接数、Slow_queries 慢查询数）；
   • EXPLAIN ANALYZE（MySQL 8.0.18+）：比 EXPLAIN 更精准，显示实际执行时间和行数。
2. 第三方工具：
   • 慢查询日志：开启慢查询日志（slow_query_log = 1，long_query_time = 1 秒），记录执行时间超 1 秒的 SQL，用 mysqldumpslow 工具分析；
   • Prometheus + Grafana：监控 MySQL 指标（连接数、QPS、缓存命中率、IO 使用率），可视化展示并设置告警；
   • Percona Toolkit：包含 pt-query-digest（分析慢查询日志）、pt-table-checksum（检查主从数据一致性）等实用工具。

总结：优化优先级

1. 紧急优化：先解决慢查询（通过 EXPLAIN 优化 SQL、加索引）、修复索引失效、调整缓存（如 Buffer Pool 不足）；
2. 中期优化：优化表结构（如分表、调整字段类型）、配置参数（如 innodb_log_file_size）；
3. 长期优化：硬件升级（SSD、内存）、分库分表（高并发场景）、引入缓存（Redis）。