高性能查询优化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字段。由于这些字段上缺少索引,查询性能受到了严重影响。
解决方案
建立复合索引
由于查询条件同时涉及brand
和category
字段,并且它们是通过AND
连接的,因此一个复合索引可能是一个好选择。但请注意,复合索引的顺序很重要。由于brand
的值可能相对较少(例如,只有几个不同的品牌),而category
的值可能更多(例如,有许多不同的产品类别),因此通常将brand
放在索引的前面可能更有效。
sql">CREATE INDEX idx_brand_category_price ON products(brand, category, price);
注意,复合索引(brand, category, price)
可能不是所有情况下的最佳选择。例如,如果查询经常只按brand
或category
进行筛选,而不是同时按两者进行筛选,那么单独的索引可能更有意义。
建立单独索引
如果你经常单独按brand
、category
或price
进行查询或排序,那么单独的索引可能是有用的。但请注意,在存在复合索引的情况下,单独的索引可能不会被使用,除非查询条件或排序方式与复合索引不完全匹配。
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数据库管理系统在大多数情况下仅对使用<
、<=
、=
、>
、>=
、BETWEEN
、IN
等操作符的列使用索引进行快速数据检索,使用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查询时,
IN
和NOT IN
关键字虽然强大且灵活,但如果不谨慎使用,可能会导致性能下降,尤其是在处理大数据集时。
当IN
或NOT IN
列表中的元素过多时,数据库引擎可能会放弃使用索引,转而进行全表扫描,这会显著增加查询的响应时间。
优化方向
优化查询性能,当查询条件涉及连续的数值范围时,我们应优先使用BETWEEN
关键字。BETWEEN
能够明确地指定一个数值范围,数据库引擎可以高效地利用索引来检索这个范围内的数据,从而避免全表扫描。
案例介绍
优化后的示例,展示了如何使用BETWEEN
关键字来替代IN
,以提高查询性能:
sql">SELECT id FROM t WHERE num BETWEEN 1 AND 3;
查询将返回表t
中num
字段值在1到3(包括1和3)之间的所有记录的id
。通过使用BETWEEN
,我们确保了数据库引擎能够高效地使用索引(如果num
字段上有索引的话),从而显著提高了查询的响应速度。
我们应该尽量避免在WHERE
子句中使用过多的IN
或NOT IN
关键字,尤其是当列表中的元素过多时。对于连续的数值范围,我们应该优先使用BETWEEN
关键字,以提高查询性能并减少全表扫描的可能性。
注意:特此声明:本文章首发文章在掘金:https://juejin.cn/post/7363549357410795559,未经允许,请勿进行侵权私自转载。