一、整体优化策略（8 大维度）

1. 选择合适的存储引擎

• 核心原则：引擎作用于单个表，需根据表的业务场景选型（而非全局统一）
• 常用引擎：InnoDB（默认首选，支持事务、行锁、外键，适合高并发 / 数据一致性场景）、MyISAM（读多写少、无事务需求，如日志表）

2. 优化 MySQL 服务配置（my.ini 配置文件）

• 关键参数：
  • DEFAULT_STORAGE_ENGINE：指定默认存储引擎（推荐设为 InnoDB）
  • INNODB_BUFFER_POOL_SIZE：InnoDB 缓存池大小（建议设为物理内存的 50%-70%，提升数据缓存命中率）
  • 其他补充：max_connections（最大连接数）、query_cache_size（查询缓存，按需开启）等

3. 创建高效索引

• 核心目标：减少全表扫描，加速查询
• 设计原则：
  • 针对查询频繁的字段（where、join、order by 字段）建立索引
  • 避免过度索引（索引会降低写入性能）
  • 复合索引遵循「最左前缀原则」

4. 合理使用主外键

• 作用：保证数据完整性（外键关联父表主键）
• 注意：避免不必要的外键（高并发写入场景可适当舍弃，通过程序保证一致性）

5. 系统层优化（表结构 / 架构层面）

• 优化表结构与字段类型：
  • 字段类型尽量精准（如用 tinyint 代替 int，varchar 代替 char 存储变长字符串）
  • 避免 NULL 字段（用默认值替代，如 0 代替 NULL）
• 架构扩展：
  • 分库分表：解决单库 / 单表数据量过大问题（水平分表 / 垂直分表）
  • 读写分离：主库写入、从库查询，分散数据库压力

6. 查询语句优化（核心重点，详见下文）

7. 程序代码层面优化

• 使用缓存：将热点数据存入 Redis/Memcached，减少数据库查询
• 引入 NoSQL 数据库：非结构化 / 高并发读写场景（如用户行为日志）用 MongoDB 等，分流 MySQL 压力

8. 硬件与集群优化

• 提升服务器硬件：增加 CPU 核心、扩大物理内存、使用 SSD 硬盘（提升 IO 性能）
• 搭建数据库集群：主从复制、MGR（MySQL Group Replication），提高可用性与并发处理能力

二、查询语句优化（18 条实战准则）

1. 避免使用 SELECT *，明确指定字段名

• 反例：SELECT * FROM t;
• 正例：SELECT id, name, age FROM t;
• 理由：减少数据传输量，避免不必要字段加载，且利于索引覆盖查询

2. 避免 WHERE 子句中对字段做 NULL 判断

• 反例：SELECT * FROM t WHERE age IS NULL;
• 正例：字段默认设为 0，查询 SELECT * FROM t WHERE age = 0;
• 理由：NULL 判断会导致引擎放弃索引，触发全表扫描

3. 避免使用！= 或 <> 操作符

• 反例：SELECT * FROM t WHERE age != 99;
• 正例：SELECT * FROM t WHERE age > 99 OR age < 99;（等价逻辑，可走索引）
• 理由：!=/<>` 会导致全表扫描

4. 避免用 OR 连接查询条件，改用 UNION ALL

• 反例：SELECT * FROM t WHERE num = 10 OR num = 20;
• 正例：

  1 2 3

  SELECT * FROM t WHERE num = 10 UNION ALL SELECT * FROM t WHERE num = 20;

• 理由：OR 会触发全表扫描，UNION ALL 可分别走索引（注意：UNION 去重，UNION ALL 不去重，优先用后者）

5. 用 EXISTS 代替 IN，NOT EXISTS 代替 NOT IN；范围查询用 BETWEEN AND 代替 IN

• 场景 1：子查询关联查询

  • 反例：SELECT * FROM emp WHERE id IN (SELECT empId FROM kill_emp);

  • 正例：SELECT * FROM emp a WHERE EXISTS (SELECT 'x' FROM kill_emp WHERE empId = a.id);

    （注：EXISTS 检查存在性，效率高于 IN；’x’ 可替换为任意常量，无需实际字段）

• 场景 2：连续范围查询
  • 反例：SELECT * FROM t WHERE id IN (1,2,3,...,9);
  • 正例：SELECT * FROM t WHERE id BETWEEN 1 AND 9;

6. 避免 LIKE 前缀模糊匹配（% 开头）

• 反例：SELECT * FROM t WHERE name LIKE '%abc%';（全表扫描）
• 正例：SELECT * FROM t WHERE name LIKE 'abc%';（可走索引）
• 理由：前缀模糊匹配无法利用索引，后缀 / 中间模糊需借助全文索引

7. 避免对 WHERE 子句中的字段做表达式运算

• 反例：SELECT * FROM t WHERE num / 2 = 100;
• 正例：SELECT * FROM t WHERE num = 100 * 2;
• 理由：字段侧运算会导致引擎无法使用索引

8. 避免对 WHERE 子句中的字段使用函数

• 反例：SELECT * FROM t WHERE SUBSTRING(name, 1, 3) = 'abc';（查询以 abc 开头）
• 正例：SELECT * FROM t WHERE name LIKE 'abc%';
• 理由：函数操作会破坏字段索引结构，触发全表扫描

9. 禁止在 = 左边做函数 / 算数运算 / 表达式操作

• 核心原则：索引字段需保持「原生形态」出现在查询条件左侧，否则无法命中索引

10. 复合索引遵循「最左前缀原则」

• 示例：创建复合索引 idx_name_age (name, age)
• 有效查询：WHERE name = '张三'（匹配最左字段）、WHERE name = '张三' AND age = 20（顺序与索引一致）
• 无效查询：WHERE age = 20（跳过最左字段，无法走索引）

11. 优先使用数字型字段

• 场景：存储手机号、身份证号等含数字信息的内容
• 理由：数字型字段查询效率高于字符型（字符型需额外做字符编码转换）

12. 合理选择 CHAR 与 VARCHAR

• CHAR：固定长度字符串（如性别、手机号），查询快，浪费存储空间
• VARCHAR：变长字符串（如姓名、地址），节省空间，查询略慢（需计算长度）
• 原则：长度固定用 CHAR，长度不固定用 VARCHAR

13. 先过滤再分组（WHERE 代替 HAVING）

• 反例：SELECT COUNT(*), school FROM student GROUP BY school HAVING school LIKE '黑龙江%';
• 正例：SELECT COUNT(*), school FROM student WHERE school LIKE '黑龙江%' GROUP BY school;
• 理由：HAVING 是分组后过滤，WHERE 是分组前过滤，减少分组的数据量

14. 批量删除用 TRUNCATE 代替 DELETE（适合全表清空）

• 反例：DELETE FROM t;（逐行删除，日志量大，可回滚）
• 正例：TRUNCATE TABLE t;（快速清空，日志量小，不可回滚）
• 注意：仅适用于全表清空，需谨慎使用（无事务回滚机制）

15. 用表连接（JOIN）代替子查询

• 反例：SELECT ename, job, deptno FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE deptname = '开发部');
• 正例：SELECT a.ename, a.job, a.deptno FROM emp a JOIN dept b ON a.deptno = b.deptno WHERE b.deptname = '开发部';
• 理由：JOIN 效率高于子查询，MySQL 对 JOIN 优化更成熟

16. 用 >= 代替 >，<= 代替 <

• 反例：SELECT * FROM t WHERE age < 50;
• 正例：SELECT * FROM t WHERE age <= 49;（等价逻辑，索引效率更高）
• 理由：数据库索引对闭区间查询的优化更友好

17. 使用完全限定列名（含表别名）

• 反例：SELECT stuName, stuAge, schoolName FROM student a LEFT JOIN school b ON a.schoolId = b.id;（可能字段歧义）
• 正例：SELECT a.stuName, a.stuAge, b.schoolName FROM student a LEFT JOIN school b ON a.schoolId = b.id;
• 理由：明确字段归属表，避免多表连接时字段名冲突，提升查询解析效率

18. 多表连接时，小表在前，大表在后

• 反例：SELECT ename, dname FROM dept a JOIN emp b ON a.deptno = b.deptno;（dept 小表、emp 大表，顺序不合理）
• 正例：SELECT ename, dname FROM emp a JOIN dept b ON a.deptno = b.deptno;（大表在前，小表在后）
• 理由：MySQL 执行多表连接时，会先加载前表数据，小表在前可减少后续大表的匹配次数