数据库优化、sql优化
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
查询能够充分利用索引,并且在大规模数据集上保持良好的性能。