那些年我们总爱琢磨的SQL优化技巧和那些小坑聊聊

记得刚入行那会儿,最怕的就是系统突然变慢，然后DBA（数据库管理员）走过来幽幽地说一句：“你的SQL把数据库跑挂了。”那时候才开始真正重视起SQL优化这件事，这么多年下来，积累了不少实战中摸爬滚打出来的经验，也踩过不少坑，今天就跟大伙儿聊聊这些。

那些立竿见影的优化技巧

1. *SELECT 是大忌，要用啥拿啥* 这是我师父教我的第一课，以前图省事，动不动就`SELECT ，把整张表的数据都捞出来，后来才发现，这问题太大了，网络传输的数据量暴增，尤其当表里有TEXTBLOB这种大字段时，简直是灾难，数据库引擎需要读取更多的数据页，增加了I/O压力，最关键的是，当使用了覆盖索引（这个概念后面简单提）时，如果你只查询索引列包含的字段，数据库甚至不用回表查数据，速度飞快，养成好习惯，需要哪些字段就明确写出来，比如SELECT id, name, email`。

   

2. 给查询条件加上合适的“目录”——索引 索引就像书的目录，能让你快速找到想要的内容，最常见的坑就是WHERE条件里的字段没索引，比如SELECT * FROM users WHERE phone_number = '138...'，如果phone_number没索引，数据库就得全表扫描，一行一行去比对，数据量一大肯定慢。 索引也不是万能的：

   • 索引不是越多越好，每建一个索引，写数据（INSERT, UPDATE, DELETE）的时候就要多维护一个索引结构，会影响写入性能，所以通常只在经常用于查询条件的列上建索引。
   • 小心索引失效，这是个大坑！
     • 对索引列做计算或函数操作：WHERE YEAR(create_time) = 2023，即使create_time有索引，也因为用了YEAR()函数而失效，应该写成WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。
     • 使用不等号：WHERE status != 1，这种有时会导致索引失效（不同数据库优化器策略不同，但多数情况下效率不高）。
     • 模糊查询开头用通配符：WHERE name LIKE '%张伟%'，这种索引是无效的，如果是LIKE '张伟%'，索引通常还是有效的。
     • 类型转换：如果字段是字符串类型，你却用数字去查WHERE phone_number = 13800138000，可能会导致索引失效，应该写成WHERE phone_number = '13800138000'。

3. JOIN连接要小心，先瘦身再牵手 多表关联（JOIN）查询是性能瓶颈的重灾区，一个重要的原则是：尽量先过滤，再关联。 比如你要查询订单和用户信息： SELECT o.order_id, u.user_name FROM orders o JOIN users u ON o.user_id = u.user_id WHERE o.amount > 100 AND u.city = '北京'; 更好的写法思路是，先分别对orders表和users表按条件进行筛选，得到两个结果集小的“临时表”，再让这两个小表进行关联，现代的数据库优化器通常会自动做这个优化（称为“谓词下推”），但我们写SQL时也要有这种意识，确保WHERE条件能够有效利用索引来减少参与JOIN的数据量，千万不要用两个全表（或者大量数据）去直接JOIN。

   

4. 分页查询的深坑与妙招 说到LIMIT分页，大家都会用LIMIT 10 OFFSET 20，但当OFFSET值非常大时（比如翻到第1000页），性能会急剧下降，因为数据库需要先扫描并跳过前1000页的所有记录，这非常耗时。 一个常见的优化技巧是使用游标分页（Cursor-based Pagination）或者叫“seek method”，不要用OFFSET，而是记录上一页最后一条记录的ID，然后查询WHERE id > 上一页最后ID ORDER BY id LIMIT 10，这样无论翻到第几页，速度都很快，这要求排序字段是唯一的且连续的。

那些容易忽略但很要命的小坑

1. ORM框架的“好心办坏事” 现在很多人用MyBatis、Hibernate这类ORM框架，它们虽然方便，但也会埋坑，比如著名的“N+1查询问题”：你想查询一个作者和他写的所有书，框架可能会先执行1条SQL查出作者列表，然后为列表里的每个作者再执行1条SQL去查他的书，如果有100个作者，就是1+100=101次查询！数据库连接和通信开销巨大，解决办法通常是使用“懒加载”或者让框架一次性通过JOIN查询出所有数据（比如MyBatis的<collection>关联查询）。

2. 事务不要开得太长太大 事务本身是好的，保证数据一致性，但有些人习惯在业务层一开始就开启事务，直到所有业务逻辑处理完才提交，这期间，事务持有的锁可能一直不释放，会严重阻塞其他操作。原则是：事务要短小精悍，尽快开始，尽快提交，只把必要的数据库操作放在事务里。

3. 字段的“性格”要摸清，NULL值惹麻烦 定义表结构时，尽量让字段设置为NOT NULL并给默认值（如空字符串、0），因为NULL值在进行条件判断（=, <, >）、排序、统计（COUNT）时行为都很特殊，容易出错，而且可能影响索引的使用效率，比如COUNT(column)不会统计NULL值，这有时会和预期不符。

4. 不要过度优化 最后这点很重要，优化要有针对性，一定是基于慢查询日志或者性能监控工具，找到真正的瓶颈所在再去优化，如果一张表就几百条数据，你给它建一堆索引，或者把一个简单的查询拆得无比复杂，反而增加了维护成本，得不偿失，记住一个原则：如果它不慢，就别动它。

SQL优化是个实践性非常强的话題,需要结合具体的数据库类型（MySQL、PostgreSQL等）、数据量、业务场景来分析，上面这些技巧和坑，算是这些年总结出来的一些普适性经验，希望能帮大家在琢磨SQL时少走点弯路。