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

关于性能测试：数据库的 SQL 性能优化实战

article 2024/11/18 20:49:18

在性能测试中，SQL性能优化往往是最具挑战性的任务之一。数据库作为系统的核心数据处理单元，其性能直接影响整体系统的响应速度。当面对复杂的业务需求和庞大的数据量时，如何高效执行SQL语句，减少查询耗时？今天，让我们深入探索数据库的SQL优化实战技巧！

什么样的SQL优化策略能显著提升数据库的查询速度？在真实项目中有哪些有效的SQL优化案例？如何从实际测试数据中提取关键优化点？

做过性能测试的同学都应该知道，性能测试核心就是结果分析和性能瓶颈调优，然后性能的瓶颈70%-80%的问题都是来自于数据库。所以，掌握数据库的瓶颈分析对其性能测试工程师来说尤为重要。

数据库的性能优化

数据库的性能优化可以从硬件和软件两个层面来考虑：

1）硬件方面：主要就是磁盘选择，考虑磁盘的读写速度、消耗IO【读入写出】性能，所以尽量选择固态硬盘【速度比机械硬盘高但是价格贵】，并使用独立服务器，跟业务服务器分开。

数据库服务器的存储操作大于计算操作，所以关注磁盘会更多一些；
但是CPU的影响也会有，比如sql复杂一些的时候也会消耗CPU；所以同步关注CPU；
硬件的优化因为主要涉及设备配件的选型和购买，所以性能测试能做的性能优化有限；

2）软件方面：我们做数据库的性能优化主要是这个层面的优化，数据库层面优化 + 表层面优化

库层面：MYSQL数据库的配置参数会影响数据库的一些性能，比如最大连接数；
表层面：主要是索引字段的设置优化和SQL的优化。

我们这篇文章主要讲一下表层面的SQL性能优化。

SQL语句性能优化

数据库SQL性能优化是提升数据库运行效率的关键环节，以下是一些具体的注意点，并结合案例进行演示和说明，帮助大家理解。

1、避免使用SELECT *

使用SELECT *会检索表中的所有列，会导致不必要的数据传输和内存消耗；如果数据库和项目是两台独立的服务器的话，还会增加网络传输的负载。所以尽量查询具体的列：

比如SQL样例：

* --优化前：select * from user where id=1；

* --优化后：select name,age from user where id =1;

2. 使用JOIN代替子查询

子查询通常会导致数据库执行多次表扫描，会增加I/O开销和查询执行时间；使用JOIN操作将多个查询合并为一个查询，可以更好地利用索引和减少表扫描次数；

比如SQL样例：

-- 优化前（子查询）

SELECT * FROM orders WHERE user_id = (SELECT user_id FROM users WHERE username = 'john_doe');


-- 优化后（JOIN）

SELECT o.* FROM orders o JOIN users u ON o.user_id = u.user_id WHERE u.username = 'john_doe';

3. 避免使用OR，使用UNION或UNION ALL代替

在WHERE子句中使用OR条件可能会导致数据库无法使用索引，从而增加全表扫描的机会。使用UNION ALL将多个查询结果合并，每个查询都可以单独使用索引进行优化，从而减少全表扫描的机会。

比如SQL样例：

* -- 优化前（使用OR）：
SELECT * FROM users WHERE age > 30 OR city = 'New York';


* -- 优化后（使用UNION ALL） 
SELECT * FROM users WHERE age > 30 UNION ALL SELECT * FROM users WHERE city = 'New York';

注意：使用UNION ALL时，需要确保结果集中不包含重复数据，或者重复数据对业务逻辑没有影响。

4. 避免在WHERE子句中使用函数

在WHERE子句中使用函数会导致数据库无法使用索引来加速查询。将函数计算移到列外，直接使用列值进行判断，这样数据库可以利用索引来加速查询，避免全表扫描。

比如SQL样例：

 -- 优化前（使用函数） 
SELECT * FROM users WHERE SUBSTRING(username, 1, 3) = 'joh';

 -- 优化后（不使用函数） 
SELECT * FROM users WHERE username LIKE 'joh%';

5、使用LIMIT限制结果集大小

如果查询返回的结果集过大，会消耗大量的内存和I/O资源。使用LIMIT语句限制结果集的大小可以减少数据库的负担，并提高查询性能。

比如SQL样例：

-- 优化前（无LIMIT） 
SELECT id,name FROM products;


-- 优化后（使用LIMIT） 
SELECT id,name FROM products LIMIT 100;

6、选择合理的字段类型

字段类型选择不当会导致数据存储效率低下和查询性能下降。例如，使用VARCHAR类型存储数字数据会浪费存储空间，并降低查询速度。

所以，根据业务需求选择最合适的字段类型。例如，对于数字数据，应使用INT、FLOAT或DECIMAL等数字类型；对于文本数据，应使用VARCHAR或TEXT等字符串类型。这样可以提高存储效率和查询性能。

SQL案例：使用数字类型代替字符串类型（如果适用）

-- 优化前（使用VARCHAR）：
CREATE TABLE example (id VARCHAR(10), value VARCHAR(100));



-- 优化后（使用INT）：
CREATE TABLE example (id INT, value VARCHAR(100));

7、使用索引

索引是提高SQL查询性能的关键。没有索引的表在查询时需要执行全表扫描来查找数据，这会导致查询性能下降。在常用的查询条件和连接条件的列上建立索引。索引可以加快查询速度，并减少全表扫描的机会。同时，要注意索引的维护成本，避免创建过多的索引导致插入、更新和删除操作的性能下降。

例如SQL语句：

-- 在user_id列上创建索引 
CREATE INDEX idx_user_id ON users(user_id);



-- 使用索引进行查询 
SELECT * FROM users WHERE user_id = 123;

8、避免全表扫描

优化方式：通过合理的索引设计和查询条件，避免全表扫描。

-- 避免在没有索引的列上进行范围查询


-- 优化前（可能导致全表扫描）：
SELECT * FROM products WHERE price > 100;



-- 优化后（在price列上创建索引）：
CREATE INDEX idx_price ON products(price);

SELECT * FROM products WHERE price > 100;