数据库优化、sql优化-优快云博客

本文链接：https://blog.youkuaiyun.com/jupiter_888/article/details/144349541

1、限制查询返回条数、要分页；
2、不要在sql里执行复杂逻辑，在程序里执行复杂逻辑；
3、建立合适的索引；
4、对于单表超过5000万条的数据要使用分布式数据库；如果业务简单，利用中间件读写分离、分库分表等。
5、减少join操作。可以用id查询关联表数据。
6、更新和删除用id主键更新,避免出现未建立索引的字段出现在where条件里导致锁表。
7、避免使用存储过程、外键等。
8、利用覆盖索引避免回表查询；
9、索引的有序性，比如a_b_c索引， order by c就不对，order by a就可以利用有序性；
10、varchar字段上建立索引，必须指定索引长度，每必要全字段建立索引，根据实际文本区分度决定索引长度即可；长度为20的索引区分度达到90%以上。
11、利用延迟关联或子查询优化超多分页场景；对超过特定阀值的页数改写sql;先快速定位需要获取的id段，然后再关联；
select a.* from tb1 a , (select id from tb1 where 条件 limit 100000,20) b where a.id = b.id;
可提升10倍性能。 10万条是1秒钟级别。

in 和not in
对于 NOT IN，推荐使用 NOT EXISTS 或 LEFT JOIN … WHERE … IS NULL，它们通常更高效且更可靠。
IN：通常可以很好地利用索引，尤其是在子查询返回少量行的情况下。但如果子查询返回大量行，性能可能会下降。
IN 和 NOT IN 子查询在 SQL 查询中可以使用索引，但它们的性能和索引利用情况取决于多个因素。以下是一些关键点，帮助你理解 IN 和 NOT IN 在不同情况下如何与索引交互：
not in 在大量数据时不会走索引的，在处理大量数据时，NOT IN 可能不会有效地利用索引，甚至可能导致全表扫描。

1. 索引的适用性

IN：当 IN 后面的列表是常量或子查询返回少量行时，数据库优化器通常能够有效地使用索引。特别是如果 IN 列表中的值是预先确定的（例如 (1, 2, 3)），并且这些值可以通过索引快速定位，那么查询性能会很好。
NOT IN：NOT IN 的情况稍微复杂一些。如果子查询可能返回 NULL 值，NOT IN 可能会导致意外的结果，并且可能会导致索引无法被有效利用。这是因为 NOT IN 遇到 NULL 时，整个条件会被评估为 FALSE，从而可能导致全表扫描。此外，NOT IN 通常会导致数据库执行更多的工作，因为它需要确保每一行都不在子查询结果中。

2. 子查询的性能

IN：如果子查询返回的结果集较小，IN 通常可以很好地利用索引。然而，如果子查询返回大量行，IN 的性能可能会下降，因为数据库需要对每个匹配的行进行检查。
NOT IN：NOT IN 在处理大量数据时尤其容易出现问题。即使子查询返回的结果集较小，NOT IN 也可能导致全表扫描，因为它需要确保每一行都不在子查询结果中。如果子查询返回 NULL，问题会更加严重。

3. 替代方案

为了提高性能，特别是在 NOT IN 情况下，可以考虑以下替代方案：

LEFT JOIN ... WHERE ... IS NULL：这是 NOT IN 的常见替代方案。它通过左连接来查找那些不在子查询结果中的行，并且通常比 NOT IN 更高效，尤其是当子查询返回 NULL 时。

示例：
```
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
```
NOT EXISTS：NOT EXISTS 是另一个常见的替代方案，它通常比 NOT IN 更高效，尤其是在子查询返回 NULL 时。NOT EXISTS 只需要找到一个匹配的行就停止搜索，而 NOT IN 需要检查所有行。

示例：
```
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
```

4. 索引优化建议

确保子查询返回的列上有索引：如果你使用 IN 或 NOT IN，确保子查询中涉及的列上有适当的索引。这可以帮助数据库更快地找到匹配的行。
避免子查询返回 NULL：如前所述，NOT IN 遇到 NULL 时会导致问题。你可以通过在子查询中添加 WHERE 子句来排除 NULL 值，或者使用 IS NOT NULL 来确保子查询不返回 NULL。

示例：
```
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL);
```
使用覆盖索引：如果子查询只涉及少数几列，可以创建覆盖索引（包含所有需要的列的索引），以减少 I/O 操作并提高查询性能。

5. 数据库优化器的行为

不同的数据库管理系统（DBMS）有不同的优化器行为。现代的 DBMS（如 MySQL、PostgreSQL、SQL Server 等）通常会尝试自动选择最优的执行计划，但有时它们可能不会总是做出最佳选择。你可以通过查看查询的执行计划（例如使用 EXPLAIN 或 EXPLAIN ANALYZE）来了解数据库是否正确使用了索引。

6. 总结

IN：通常可以很好地利用索引，尤其是在子查询返回少量行的情况下。但如果子查询返回大量行，性能可能会下降。
NOT IN：由于可能遇到 NULL 值的问题，NOT IN 的性能通常不如 NOT EXISTS 或 LEFT JOIN ... WHERE ... IS NULL。建议尽量避免使用 NOT IN，尤其是在子查询可能返回 NULL 的情况下。
替代方案：对于 NOT IN，推荐使用 NOT EXISTS 或 LEFT JOIN ... WHERE ... IS NULL，它们通常更高效且更可靠。

示例：`IN` 和 `NOT IN` 的性能对比

假设我们有两个表 customers 和 orders，你想找出那些没有下过任何订单的客户。

使用 `NOT IN`：

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);

使用 `NOT EXISTS`：

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

使用 `LEFT JOIN ... WHERE ... IS NULL`：

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

在这三种方法中，NOT EXISTS 和 LEFT JOIN ... WHERE ... IS NULL 通常比 NOT IN 更高效，尤其是在子查询可能返回 NULL 的情况下。

性能测试

为了确保你的查询在实际环境中表现良好，建议你：

使用 EXPLAIN 或 EXPLAIN ANALYZE：查看查询的执行计划，确认数据库是否正确使用了索引。
测试不同方案：比较 IN、NOT IN、NOT EXISTS 和 LEFT JOIN ... WHERE ... IS NULL 的执行时间，选择最适合你场景的方案。
监控查询性能：在生产环境中持续监控查询性能，确保索引和查询优化措施有效。

通过这些方法，你可以确保 IN 和 NOT IN 查询能够充分利用索引，并且在大规模数据集上保持良好的性能。