【深入浅出MySQL】「性能调优」高性能查询优化MySQL的SQL语句编写

server/2024/12/15 5:43:15/

高性能查询优化MySQL的SQL语句编写准则这里写目录标题

  • 总体优化大纲
    • (1)优化查询性能:通过索引降低全表扫描频率
      • 优化方向
      • 案例介绍
      • 问题分析
      • 解决方案
        • 建立复合索引
        • 建立单独索引
    • (2)优化数据表与查询:合理使用非NULL约束与默认值
      • 优化方向
        • 避免`NULL`值带来的潜在问题
        • 案例介绍
      • 解决方案
        • 建表的配置问题
        • 查询的操作处理
    • (3)优化SQL查询:避免在WHERE子句中使用非索引操作符
      • 解决方案
    • (4)优化查询性能:使用UNION替代OR条件以减少全表扫描
      • 案例介绍
      • 优化方向
      • 案例介绍
    • (5)优化查询性能:合理使用BETWEEN替代IN以避免全表扫描
      • 优化方向
      • 案例介绍

总体优化大纲

在这里插入图片描述

(1)优化查询性能:通过索引降低全表扫描频率

提升数据库查询性能的途径时,一个关键的战略就是降低全表扫描的频次。因为全表扫描往往会消耗显著的计算资源,从而导致查询过程变得迟缓且效率低下,为了有效地提高查询的响应速度和整体性能

优化方向

那些在where筛选条件和order by排序操作中频繁使用的列。确保这些列上已经构建了恰当的索引,这样可以帮助数据库系统更快地定位所需的数据,避免不必要的全表遍历,从而达到优化查询性能的目的。

案例介绍

举一个案例,例如,我们有一个名为products的表,用于存储电子商务网站上的商品信息。这个表包含数百万条记录,并且经常需要进行商品搜索和排序操作。

查询试图找出品牌为"Apple",类别为"Phones"的商品,并按价格升序排序,最后只返回前10条记录。

sql">SELECT *  FROM products
WHERE brand = 'Apple' AND category = 'Phones'
ORDER BY price ASC ;

由于缺少适当的索引,这些查询经常触发全表扫描,导致性能问题。

问题分析

  • 性能瓶颈:在表上进行全表扫描是非常耗时的,特别是在表中有数百万条记录时。
  • 索引缺失:该查询中的where子句使用了brand和category字段,而order by子句使用了price字段。由于这些字段上缺少索引,查询性能受到了严重影响

解决方案

建立复合索引

由于查询条件同时涉及brandcategory字段,并且它们是通过AND连接的,因此一个复合索引可能是一个好选择。但请注意,复合索引的顺序很重要。由于brand的值可能相对较少(例如,只有几个不同的品牌),而category的值可能更多(例如,有许多不同的产品类别),因此通常将brand放在索引的前面可能更有效。

sql">CREATE INDEX idx_brand_category_price ON products(brand, category, price);

注意,复合索引(brand, category, price)可能不是所有情况下的最佳选择。例如,如果查询经常只按brandcategory进行筛选,而不是同时按两者进行筛选,那么单独的索引可能更有意义。

建立单独索引

如果你经常单独按brandcategoryprice进行查询或排序,那么单独的索引可能是有用的。但请注意,在存在复合索引的情况下,单独的索引可能不会被使用,除非查询条件或排序方式与复合索引不完全匹配。

sql">CREATE INDEX idx_brand ON products(brand);
CREATE INDEX idx_category ON products(category);
CREATE INDEX idx_price ON products(price);

(2)优化数据表与查询:合理使用非NULL约束与默认值

频繁地在where子句中对字段进行null值判断可能会导致性能下降,并可能使逻辑变得复杂。因此,为了避免这些问题,我们应该审慎地使用null,并考虑在适当的情况下使用not null约束或其他特定的默认值

优化方向

处理数据库中的null值时,我们应该审慎行事,并考虑使用NOT NULL约束、特定的默认值或其他策略来简化查询逻辑并提高性能。

避免NULL值带来的潜在问题

首先,理解NULL数据库中的含义至关重要。NULL表示字段没有值,这与字段值为0或空字符串(‘’)不同。然而,这种不确定性可能导致查询和逻辑变得复杂,特别是在进行条件判断和联接操作时。

案例介绍
sql">INSERT INTO students (id, name, age, graduation_year)  VALUES   
(1, 'Alice', 20, 2023),  
(2, 'Bob', 21, 2022),  
(3, 'Charlie', 20, NULL), -- 尚未毕业  
(4, 'David', 22, 2021);

当我们想要查询所有已毕业的学生时,我们需要排除graduation_year为NULL的记录。

sql">SELECT * FROM students WHERE graduation_year IS NOT NULL;

在这个查询中,我们需要确保我们只包括那些graduation_year非NULL且年龄大于20岁的学生。

sql">SELECT * FROM students WHERE age > 20 AND graduation_year IS NOT NULL;

解决方案

建表的配置问题
  • 使用NOT NULL约束:如果某个字段在逻辑上总是应该有一个值,那么应该为该字段设置NOT NULL约束。这样做可以确保数据的完整性和一致性,并减少在查询时进行NULL值判断的需要。

  • 使用特定的默认值:对于某些字段,如果确实存在没有值的情况,但又不希望使用NULL来表示,可以考虑为该字段设置一个特定的默认值,如0、-1或某个特定的字符串。这样做可以使数据更具可读性,并简化查询逻辑。

查询的操作处理
  • 避免在WHERE子句中进行NULL值判断:尽量避免在WHERE子句中对字段进行NULL值判断。相反,可以考虑使用其他条件或逻辑来过滤数据。

  • 使用索引优化查询:如果必须对包含NULL值的字段进行查询,那么应该确保该字段已经建立了索引。虽然索引可能不会显著提高对NULL值的查询性能,但它仍然可以帮助优化其他类型的查询。

(3)优化SQL查询:避免在WHERE子句中使用非索引操作符

提高查询效率和性能,我们应尽量避免在WHERE子句中使用!=<>操作符。因为MySQL数据库管理系统在大多数情况下仅对使用<<==>>=BETWEENIN等操作符的列使用索引进行快速数据检索,使用LIKE操作符也可以触发索引的使用,但这通常需要LIKE表达式以非通配符字符开头

解决方案

  • 使用合适的操作符:在WHERE子句中,尽量使用上述提到的能够触发索引使用的操作符。

  • 避免使用非索引操作符:尽量避免使用!=<>这样的非索引操作符,尤其是在涉及大量数据的列上。

  • 考虑查询的改写:必须使用!=<>操作符,是否可以改写查询以使用其他操作符或策略。例如,可以使用NOT IN代替<>

  • 注意LIKE操作符的使用:当使用LIKE操作符时,确保表达式以非通配符字符开头,以便能够利用索引。

(4)优化查询性能:使用UNION替代OR条件以减少全表扫描

在编写SQL查询时,我们应当谨慎地在WHERE子句中使用OR来连接条件,因为这种做法可能会导致数据库引擎放弃使用索引,转而进行全表扫描,从而显著降低查询性能

案例介绍

sql">select id from t where num=10 or num=20

优化方向

优化查询性能,一种有效的策略是利用UNION(或UNION ALL,如果确定结果集中不包含重复行)来合并多个基于相同表但具有不同条件的查询。通过使用UNION ALL,我们将这两个查询的结果合并在一起,从而避免了在WHERE子句中使用OR可能导致的性能问题。

案例介绍

以下是一个优化后的示例,它展示了如何使用UNION ALL来替代OR连接条件,从而提高查询效率:

sql">SELECT id FROM t WHERE num = 10
UNION ALL
SELECT id FROM t WHERE num = 20;

在这个例子中,我们分别执行了两个独立的查询,每个查询都针对num字段的不同值进行筛选。由于这两个查询都是基于索引字段(假设num字段已经被索引),因此它们都可以高效地利用索引来检索数据。

注意,当使用UNION(或UNION ALL)时,确保每个查询选择的列具有相同的数量、类型和顺序,以便能够正确合并结果集。此外,如果可能的话,尽量使用UNION ALL而不是UNION,因为UNION ALL不会尝试消除结果集中的重复行。

(5)优化查询性能:合理使用BETWEEN替代IN以避免全表扫描

在编写SQL查询时,INNOT IN 关键字虽然强大且灵活,但如果不谨慎使用,可能会导致性能下降,尤其是在处理大数据集时

INNOT IN列表中的元素过多时,数据库引擎可能会放弃使用索引,转而进行全表扫描,这会显著增加查询的响应时间。

优化方向

优化查询性能,当查询条件涉及连续的数值范围时,我们应优先使用BETWEEN关键字。BETWEEN能够明确地指定一个数值范围,数据库引擎可以高效地利用索引来检索这个范围内的数据,从而避免全表扫描。

案例介绍

优化后的示例,展示了如何使用BETWEEN关键字来替代IN,以提高查询性能:

sql">SELECT id FROM t WHERE num BETWEEN 1 AND 3;

查询将返回表tnum字段值在1到3(包括1和3)之间的所有记录的id。通过使用BETWEEN,我们确保了数据库引擎能够高效地使用索引(如果num字段上有索引的话),从而显著提高了查询的响应速度。

我们应该尽量避免在WHERE子句中使用过多的INNOT IN关键字,尤其是当列表中的元素过多时。对于连续的数值范围,我们应该优先使用BETWEEN关键字,以提高查询性能并减少全表扫描的可能性。

注意:特此声明:本文章首发文章在掘金:https://juejin.cn/post/7363549357410795559,未经允许,请勿进行侵权私自转载。


http://www.ppmy.cn/server/30491.html

相关文章

力扣每日一题-总行驶距离-2024.4.25

力扣题目&#xff1a;总行驶距离 题目链接: 2739.总行驶距离 题目描述 代码思路 直接用数学模拟计算即可 代码纯享版 class Solution {public int distanceTraveled(int mainTank, int additionalTank) {int sum 0;while(additionalTank > 0){if(mainTank > 5){mai…

导出 CDH 中各组件(HDFS、Hive、Impala、Kafka、Kudu、YARN和Zookeeper)指标到 Prometheus

文章目录 前言一、提取准备1. 下载jmx2. 创建规则文件 二、HDFS指标提取1. namenode指标提取2. datanode指标提取 二、Hive指标提取1. Hive Metastore Server 指标提取2. HiveServer2 指标提取 三、Impala 指标提取1. Impala Catalog Server 指标提取2. Impala Daemon 指标提取…

蚁群算法路径规划matlab

蚁群算法&#xff08;Ant Colony Optimization, ACO&#xff09;是一种模拟蚂蚁寻找食物路径的优化算法&#xff0c;它被广泛应用于路径规划问题中。在MATLAB中实现蚁群算法进行路径规划的基本步骤如下&#xff1a; 初始化&#xff1a;设置算法参数&#xff0c;包括蚂蚁数量、信…

win11 Terminal 部分窗口美化

需求及分析&#xff1a;因为在 cmd、anaconda prompt 窗口中输入命令较多&#xff0c;而命令输入行和输出结果都是同一个颜色&#xff0c;不易阅读&#xff0c;故将需求定性为「美化窗口」。 美化结束后&#xff0c;我在想是否能不安装任何软件&#xff0c;简单地通过调整主题颜…

双指针算法

一、Leetcode27.移除元素 1.题目描述 给你一个数组 nums和一个值 val&#xff0c;你需要 [原地] 移除所有数值等于 val的元素&#xff0c;并返回移除后数组的新长度。 不要使用额外的数组空间&#xff0c;你必须仅使用 O(1) 额外空间并 [原地 ]修改输入数组。 元素的顺序可以…

ue引擎游戏开发笔记(28)——实现第三人称越肩视角

1.需求分析 实现一个第三人称越肩视角 2.操作实现 1.思路&#xff1a;建立一个弹簧臂和摄像机&#xff0c;调整两者位置达到越肩效果。 2.直接在蓝图操作&#xff1a;添加摄像机和弹簧臂&#xff1a; 3.对弹簧臂勾选使用pawn控制旋转&#xff0c;并适当调整摄像机和弹簧臂位置…

JAVA面试之MQ

如何保证消息的可靠传输&#xff1f;如果消息丢了怎么办 数据的丢失问题&#xff0c;可能出现在生产者、MQ、消费者中。 &#xff08;1&#xff09;生产者发送消息时丢失&#xff1a; ①生产者发送消息时连接MQ失败 ②生产者发送消息到达MQ后未找到Exchange(交换机) ③生产者发…

Unity开发一个FPS游戏之四

在前面的系列中&#xff0c;我已介绍了如何实现一个基本的FPS游戏&#xff0c;这里将继续进行完善&#xff0c;主要是增加更换武器以及更多动作动画的功能。 之前我是采用了网上一个免费的3D模型来构建角色&#xff0c;这个模型自带了一把AR自动步枪&#xff0c;并且自带了一些…