当前位置: 首页 > article >正文

数据库优化、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秒钟级别。

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

1. 索引的适用性

  • IN:当 IN 后面的列表是常量或子查询返回少量行时,数据库优化器通常能够有效地使用索引。特别是如果 IN 列表中的值是预先确定的(例如 (1, 2, 3)),并且这些值可以通过索引快速定位,那么查询性能会很好。

  • NOT INNOT IN 的情况稍微复杂一些。如果子查询可能返回 NULL 值,NOT IN 可能会导致意外的结果,并且可能会导致索引无法被有效利用。这是因为 NOT IN 遇到 NULL 时,整个条件会被评估为 FALSE,从而可能导致全表扫描。此外,NOT IN 通常会导致数据库执行更多的工作,因为它需要确保每一行都不在子查询结果中。

2. 子查询的性能

  • IN:如果子查询返回的结果集较小,IN 通常可以很好地利用索引。然而,如果子查询返回大量行,IN 的性能可能会下降,因为数据库需要对每个匹配的行进行检查。

  • NOT INNOT 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 EXISTSNOT 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. 索引优化建议

  • 确保子查询返回的列上有索引:如果你使用 INNOT 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 等)通常会尝试自动选择最优的执行计划,但有时它们可能不会总是做出最佳选择。你可以通过查看查询的执行计划(例如使用 EXPLAINEXPLAIN ANALYZE)来了解数据库是否正确使用了索引。

6. 总结

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

示例:INNOT IN 的性能对比

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

使用 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 EXISTSLEFT JOIN ... WHERE ... IS NULL 通常比 NOT IN 更高效,尤其是在子查询可能返回 NULL 的情况下。

性能测试

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

  1. 使用 EXPLAINEXPLAIN ANALYZE:查看查询的执行计划,确认数据库是否正确使用了索引。
  2. 测试不同方案:比较 INNOT INNOT EXISTSLEFT JOIN ... WHERE ... IS NULL 的执行时间,选择最适合你场景的方案。
  3. 监控查询性能:在生产环境中持续监控查询性能,确保索引和查询优化措施有效。

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


http://www.kler.cn/a/429948.html

相关文章:

  • 奇异值分解推导——把任意n维度矢量,从vi基分量对应映射到ui基分量。所以分解后,V转置是提取矢量中属于V的列的分量。
  • 磁盘阵列服务器和普通服务器的区别
  • 在微信小程序中引入字体样式
  • 杂谈随笔-关于unity开发游戏
  • 如何让Google快速收录你的页面?
  • (0基础保姆教程)-JavaEE开课啦!--13课程(Interception拦截器)-完结
  • 如何使用 Docker Compose 安装 Memos 自托管笔记应用
  • 非MFC工程实现消息映射
  • 最小二乘法原理
  • couchbase 支持的数据格式以及与数据湖的对比
  • 【Appium】AttributeError: ‘NoneType‘ object has no attribute ‘to_capabilities‘
  • nginx不允许静态文件被post请求显示405 not allowed
  • DLL注入(AppInit_DLLs)
  • 24/12/9 算法笔记<强化学习> TD3
  • CSS学习记录07
  • 专业135+总分400+华中科技大学824信号与系统考研经验华科电子信息与通信工程,真题,大纲,参考书。
  • Claude:高效智能的AI语言模型
  • 【日常记录-Java】查看Maven本地仓库的位置
  • MySQL | 尚硅谷 | 第12章_MySQL数据类型精讲
  • python爬虫--某房源网站验证码破解