SQL 多条件筛选如何优化？

37次阅读

答案是优化SQL多条件筛选需合理使用组合索引、覆盖索引，避免函数操作和OR导致的索引失效，优先使用IN、EXISTS、BETWEEN等高效语法，结合数据类型与数据库配置调优，减少全表扫描。

SQL 多条件筛选如何优化？

SQL 多条件筛选的优化关键在于如何让数据库高效地利用索引，避免全表扫描。核心思路是：尽可能让数据库引擎在读取数据之前就过滤掉大部分不符合条件的数据，减少后续处理的数据量。

解决方案

索引优化：
- 组合索引： 针对常用的筛选条件，创建组合索引。例如，
```
WHERE category = 'A' AND price > 100
```
  ，可以创建
```
(category, price)
```
  的组合索引。索引列的顺序也很重要，通常将区分度高的列放在前面。
- 覆盖索引： 如果查询只需要索引中的列，那么数据库可以直接从索引中获取数据，而不需要回表查询。例如，
```
SELECT category, price FROM products WHERE category = 'A' AND price > 100
```
  ，如果
```
(category, price)
```
  是组合索引，那么它就是一个覆盖索引。
- 避免在索引列上使用函数或表达式：
```
WHERE YEAR(date) = 2023
```
  会导致索引失效，应改为
```
WHERE date >= '2023-01-01' AND date < '2024-01-01'
```
  。
- 分析慢查询： 使用数据库的慢查询日志，找出执行效率低的 SQL 语句，然后针对性地进行索引优化。
SQL 语句优化：
- 避免
  
  OR
  
  ：
```
OR
```
  往往会导致索引失效。可以使用
```
UNION ALL
```
  或改写 SQL 语句。例如，
```
WHERE category = 'A' OR price > 100
```
  ，可以改为
```
(SELECT * FROM products WHERE category = 'A') UNION ALL (SELECT * FROM products WHERE price > 100)
```
  。当然，具体效果要看数据分布和数据库版本，
```
OR
```
  在某些情况下也能被优化。
- 使用
  
  IN
  
  代替多个
  
  OR
  
  ：
```
WHERE category IN ('A', 'B', 'C')
```
  比
```
WHERE category = 'A' OR category = 'B' OR category = 'C'
```
  效率更高。
- *使用
  
  EXISTS
  
  代替 `COUNT()
```
：**  如果只需要判断是否存在满足条件的记录，使用
```
  EXISTS
```
比
```
  COUNT(*)
```
效率更高。
```
  IF EXISTS (SELECT 1 FROM products WHERE category = ‘A’)`。
- 优化
  
  LIKE
  
  查询：
```
LIKE 'abc%'
```
  可以使用索引，而
```
LIKE '%abc'
```
  则不能。尽量避免使用前导模糊匹配。
- 使用
  
  BETWEEN
  
  代替范围查询：
```
WHERE price BETWEEN 100 AND 200
```
  比
```
WHERE price >= 100 AND price <= 200
```
  更简洁，也可能更高效。
- 减少不必要的列：
```
SELECT *
```
  会读取所有列，如果只需要部分列，应该明确指定列名。
数据类型优化：
- 使用合适的数据类型： 选择占用空间小、效率高的数据类型。例如，如果只需要存储布尔值，使用
```
BOOLEAN
```
  类型。
- 避免使用
  
  NULL
  
  ：
```
NULL
```
  会增加查询的复杂度，尽量避免使用
```
NULL
```
  值。可以使用默认值代替
```
NULL
```
  。
数据库配置优化：
- 调整数据库参数： 根据服务器的硬件配置和业务特点，调整数据库的参数，例如
```
buffer pool size
```
  、
```
query cache size
```
  等。
- 定期维护数据库： 定期进行碎片整理、索引重建等操作，提高数据库的性能。

如何选择合适的索引？

选择索引是一门艺术，需要结合实际情况进行权衡。一般来说，可以遵循以下原则：

选择区分度高的列： 区分度越高，索引的效果越好。例如，
```
gender
```
列的区分度很低，而
```
email
```
列的区分度很高。
选择经常用于筛选的列： 如果一个列经常用于
```
WHERE
```
子句中，那么它就适合创建索引。
考虑查询的频率： 对于查询频率高的 SQL 语句，可以考虑创建索引。
考虑索引的维护成本： 索引会增加数据插入、更新、删除的成本，需要权衡索引的收益和成本。

组合索引的顺序如何确定？

组合索引的顺序也很重要，它会影响索引的使用效率。一般来说，可以遵循以下原则：

区分度高的列放在前面： 这样可以更快地过滤掉不符合条件的数据。
经常用于范围查询的列放在后面： 范围查询会导致索引失效，将它放在后面可以减少索引失效的影响。
考虑最左前缀原则： 如果创建了
```
(A, B, C)
```
的组合索引，那么
```
(A)
```
、
```
(A, B)
```
也可以使用该索引。