sql优化经验

一、SQL优化的整体思路

SQL优化主要从以下几个层面展开：

1. 表的设计优化
2. 索引优化（参考索引创建原则与索引失效）
3. SQL语句优化
4. 主从复制、读写分离
5. 分库分表

---

二、表的设计优化

1. 数值类型选择：根据实际情况选择合适的数值类型，如tinyint、int、bigint，避免过度使用大类型占用空间。
2. 字符串类型选择：
   • char：定长类型，存储效率高，适合长度固定的字段（如手机号、固定编码）。
   • varchar：可变长度类型，存储更灵活，但效率稍低，适合长度不固定的字段。

---

三、SQL语句优化

1. 避免使用SELECT *

• 原因：
  • 增加不必要的字段数据传输，占用网络带宽。
  • 可能导致无法使用覆盖索引，触发回表查询，降低性能。
  • 后续表结构变更（如新增字段）可能影响查询结果。
• 优化方式：明确指定需要查询的字段名称。

2. 避免索引失效的写法

• 不违反复合索引的最左前缀法则。
• 避免在索引列上进行运算操作（如函数、表达式）。
• 避免以%开头的LIKE模糊查询。
• 字符串类型字段查询时添加单引号，避免隐式类型转换。

3. UNION与UNION ALL的区别与优化

• UNION ALL：直接合并多个查询结果，不进行去重和排序操作，效率更高。
• UNION：合并结果后会进行去重和排序，额外消耗CPU和内存资源，效率较低。
• 优化建议：在无需去重的场景下，优先使用UNION ALL替代UNION。

4. 避免在WHERE子句中对字段进行表达式操作

• 定义：在WHERE条件中对字段使用函数、算术运算等表达式，如WHERE SUBSTRING(name, 3, 2) = '科技'。
• 原因：表达式操作会改变字段的值，导致MySQL无法匹配索引中的有序数据，触发全表扫描，索引失效。
• 优化方式：将表达式运算移到查询外，或调整查询条件，直接使用字段原始值进行匹配。

5. JOIN优化

• 优先使用INNER JOIN，而非LEFT JOIN/RIGHT JOIN，内连接会自动优化表的关联顺序。
• 若必须使用LEFT JOIN/RIGHT JOIN，需以小表为驱动表，减少关联次数。

---

四、主从复制与读写分离

• 适用场景：读操作远多于写操作的系统，避免写操作影响查询效率。
• 实现方式：
  1. 主库（Master）负责写操作，从库（Slave）通过主从复制同步主库数据。
  2. 应用通过数据库中间件实现读写分离，读请求分发到从库，写请求分发到主库。

---

面试相关问题

1. 问：SQL优化的整体思路是什么？ 答：从表设计、索引、SQL语句、主从复制/读写分离、分库分表五个层面展开，优先优化表结构和索引，再优化SQL语句，高并发场景引入读写分离和分库分表。

2. 问：为什么要避免使用SELECT *？ 答：SELECT *会查询所有字段，增加数据传输量，且可能无法使用覆盖索引，触发回表查询，降低性能；同时表结构变更时可能影响查询结果。

3. 问：UNION和UNION ALL有什么区别？为什么优先使用UNION ALL？ 答：UNION会对结果去重和排序，效率低；UNION ALL直接合并结果，无额外操作，效率高。在无需去重的场景下，优先使用UNION ALL。

4. 问：什么是在索引列上的表达式操作？为什么会导致索引失效？ 答：指在WHERE条件中对索引列使用函数、算术运算等表达式，如SUBSTRING(name, 3, 2) = '科技'。表达式会改变字段值，MySQL无法匹配索引中的有序数据，触发全表扫描，索引失效。

5. 问：JOIN查询如何优化？ 答：优先使用INNER JOIN；若使用LEFT JOIN/RIGHT JOIN，以小表为驱动表；确保关联字段建立索引，避免全表扫描。