SQL 优化案例

1. SQL 优化案例

示例 1：避免全表扫描

问题： 在查询用户注册日期为特定年份的用户时，使用了如下 SQL：

SELECT * FROM users WHERE YEAR(registration_date) = 2023;

由于使用了函数 YEAR()，这将导致全表扫描。

优化方案： 将查询条件改为范围查询，并确保 registration_date 列上有索引：

SELECT * FROM users WHERE registration_date >= '2023-01-01' AND registration_date < '2024-01-01';

这样可以利用索引，提高查询效率。

示例 2：使用索引覆盖

问题： 在订单查询中，查询条件为：

SELECT order_id FROM orders WHERE customer_id = 1001;

如果 customer_id 上有索引，但只返回 order_id，会导致回表。

优化方案： 创建覆盖索引：

CREATE INDEX idx_customer_id ON orders (customer_id, order_id);

然后查询：

SELECT order_id FROM orders USE INDEX (idx_customer_id) WHERE customer_id = 1001;

这样可以直接从索引中获取结果，避免回表操作。

示例 3：拆分复杂查询

问题： 在查询某个产品的销售记录时，使用了复杂的联表查询：

SELECT p.product_name, s.sale_date FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE p.category_id = 5 AND s.sale_amount > 1000;

如果数据量大，可能导致性能问题。

优化方案： 拆分查询为两个简单查询，减少一次性查询的数据量：

SELECT product_id FROM products WHERE category_id = 5;

然后将结果用于第二个查询：

SELECT sale_date FROM sales WHERE product_id IN (/* product_id list */) AND sale_amount > 1000;

这样可以减少内存消耗和查询时间。

示例 4：使用 EXISTS 替代 IN

问题： 在某些情况下，使用 IN 可能导致性能下降：

SELECT * FROM products WHERE product_id IN (SELECT product_id FROM sales WHERE sale_amount > 1000);

优化方案： 使用 EXISTS 进行替代：

SELECT * FROM products p WHERE EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.product_id AND s.sale_amount > 1000);

EXISTS 通常在处理子查询时性能更佳。

示例 5：合理使用 JOIN

问题： 在一个大型数据库中，使用了不必要的 JOIN：

SELECT o.order_id, c.customer_name FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped';

如果只需要订单 ID，可以不需要 JOIN。

优化方案： 仅查询需要的字段：

SELECT order_id FROM orders WHERE status = 'shipped';

减少不必要的 JOIN 操作，提升性能。

示例 6：避免不必要的 DISTINCT

问题： 在某些情况下，使用 DISTINCT 可能导致性能下降：

SELECT DISTINCT customer_id FROM orders;

如果 customer_id 列上有索引，使用 DISTINCT 会导致额外的排序开销。

优化方案： 可以使用 GROUP BY 替代：

SELECT customer_id FROM orders GROUP BY customer_id;

在某些情况下，这样的查询可能更高效。

示例 7：使用 UNION ALL 替代 UNION

问题： 在合并多个查询结果时，使用了 UNION：

SELECT product_id FROM products WHERE status = 'active'
UNION
SELECT product_id FROM products WHERE status = 'inactive';

UNION 会去重，增加了性能开销。

优化方案： 如果不需要去重，可以使用 UNION ALL：

SELECT product_id FROM products WHERE status = 'active'
UNION ALL
SELECT product_id FROM products WHERE status = 'inactive';

这样可以提高查询性能。

示例 8：使用合适的索引类型

问题： 在搜索中，使用了不适合的索引类型，如在文本字段上使用 B+树索引。

优化方案： 对于文本搜索，使用 FULLTEXT 索引：

ALTER TABLE articles ADD FULLTEXT(title, content);

然后使用 MATCH AGAINST 进行搜索：

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('关键词' IN NATURAL LANGUAGE MODE);

2. 索引优化示例

示例 1：定期分析索引使用情况

问题： 在一个大型用户管理系统中，发现某些索引很少被使用，导致维护成本高。

优化方案： 通过查询日志或性能监控工具，分析索引的使用情况。发现某个不常用的索引：

SHOW INDEX FROM users;

如果某个索引在过去的几个月内没有被使用，可以考虑删除该索引，降低维护成本。

示例 2：合理设计组合索引

问题： 在产品搜索中，用户经常根据 category_id 和 price 进行过滤，但只在 category_id 上创建了索引。

优化方案： 创建组合索引以提高查询效率：

CREATE INDEX idx_category_price ON products (category_id, price);

这样，当用户执行如下查询时，可以充分利用索引：

SELECT * FROM products WHERE category_id = 1 AND price < 100;

示例 3：避免频繁更新字段创建索引

问题： 在一个日志表中，某个字段 status 经常被更新，但该字段上却创建了索引，导致写入性能下降。

优化方案： 考虑删除该字段的索引，或者将索引创建在不常更新的字段上，以提高写入性能。