SQL 优化笔记：提高查询速度

SQL 优化笔记：提高查询速度

SQL 优化是提升数据库性能的关键环节。以下是一些常见的 SQL 优化策略，包含每种方法的原因和建议，可以帮助提高查询速度和整体性能。

1. 使用索引

1.1 创建索引

原因：索引可以大大加快数据检索速度，通过创建索引，数据库可以更快地定位到所需数据，而无需扫描整个表。 建议：为查询中经常作为WHERE条件、JOIN条件或ORDER BY的列创建索引。

• 单列索引：适用于经常作为查询条件的单列。

  sqlCopy CodeCREATE INDEX idx_column_name ON table_name(column_name);
  

• 联合索引：适用于多个列一起作为查询条件的情况。

  sqlCopy CodeCREATE INDEX idx_column1_column2 ON table_name(column1, column2);
  

• 注意：索引会增加写入操作的开销，避免为每个查询创建过多索引。

1.2 索引选择

原因：选择性高的列（即唯一值多）能有效提高索引效率。 建议：

• 优先索引选择性高的列，如主键或唯一约束列。
• 避免过多索引：每增加一个索引都会带来额外的维护开销，特别是对于写操作（插入、更新、删除）。

2. 优化查询语句

2.1 使用合适的查询条件

原因：不必要的 SELECT * 或函数在 WHERE 子句中会导致全表扫描或失去索引效率。 建议：

• 选择需要的列：只查询实际需要的字段。

  sqlCopy CodeSELECT column1, column2 FROM table_name WHERE condition;
  

• 避免在 WHERE 子句中使用不必要的函数：函数调用可能会导致索引失效。

  sqlCopy Code-- 不推荐
  SELECT * FROM table_name WHERE YEAR(date_column) = 2023;
  
  -- 推荐
  SELECT * FROM table_name WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';
  

2.2 使用 JOIN 而非子查询

原因：JOIN 通常比子查询性能更优，尤其是在处理大数据集时。 建议：

• 使用 JOIN 替代子查询：在可能的情况下，尽量使用连接查询而非嵌套查询。

  sqlCopy Code-- 子查询
  SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE status = 'active');
  
  -- JOIN 查询
  SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.status = 'active';
  

2.3 小表驱动大表

原因：小表驱动大表，也就是说用小表的数据集驱动大表的数据集。
假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。
这时如果想查一下，所有有效的用户下过的订单列表。
可以使用in关键字实现：

select * from order
where user_id in (select id from user where status=1)

也可以使用exists关键字实现：

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。
为什么呢？
因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。
而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。
这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。
总结一下：
in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。
不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

2.4 优化分页查询

**原因：**当使用LIMIT和OFFSET进行分页时，随着页码的增加，查询性能会逐渐下降，因为数据库需要扫描越来越多的行来找到所需的起始点。
**建议：**使用基于索引的查询来优化分页，特别是当表很大时。例如，可以记录上一页最后一条记录的某个唯一标识符（如ID），并使用它作为下一页查询的起点。
例子：
优化前（随着页码增加性能下降）

SELECT * FROM user LIMIT 10 OFFSET 100;

优化后（使用上一页的最后一条记录的ID）

SELECT * FROM user WHERE id > LAST_SEEN_ID ORDER BY id LIMIT 10