慢查询问题及优化策略，如添加索引、优化SQL语句等

连接查询性能问题及优化，如确保连接字段有索引等

子查询性能问题及解决办法，如转换为连接查询等

过度使用通配符%的LIKE查询的优化，如避免%开头等

大批量插入或更新的优化，如使用批量操作等

威哥爱编程 2024-12-04 07:15 广东

一些常见的业务需求场景、可能的性能问题原因、优化策略以及详细的示例解释，都是我验证过的经验……

作为 SQL 专家，一定深知 SQL 查询优化对于数据库性能的重要性。在不同的业务需求场景中，SQL 查询可能因各种原因导致性能下降。以下是一些常见的业务需求场景、可能的性能问题原因、优化策略以及详细的示例解释，这是笔者验证过的经验，今天拿出来分享给大家。

当咱们遇到查询问题，通常会使用 EXPLAIN 分析查询计划，查看原因，再根据原因分析去优化查询语句。下面笔者根据不同的业务场景做具体问题分析和优化策略，共计 30 个业务场景优化，让你一次学到爽，爽歪歪。

30 个业务场景目录如下，对号入座吧：

1.慢查询

2.连接查询性能问题

3.子查询性能问题

4.过度使用通配符%的 LIKE 查询

5.大批量插入或更新

6.频繁的重复查询

7.过度使用分组和聚合函数

8.大量重复数据的查询

9.过度使用 OR 条件的查询

10.大型数据分页查询

11.使用不必要的列

12.频繁更新的表

13.未使用索引的外键约束

14.大型查询的分批处理

15.未使用存储过程的重复逻辑

16.未使用合适的数据类型

17.大量写操作导致的锁竞争

18.频繁使用数据库函数和表达式

19.未使用合适的索引策略

20.大量数据的联合操作

21.数据分布不均匀的索引

22.过度使用子查询

23.未使用批量操作

24.过度使用内存表

25.缺乏定期统计和优化

26.未使用合适的数据库引擎

27.使用强制类型转换

28.未优化的长事务

29.未优化的存储过程

30.未考虑 DB 服务器配置和硬件资源

1.慢查询

问题原因：通常是由于查询中涉及大量数据、缺乏索引或者SQL语句本身写得不够有效率所导致。

优化策略：

添加索引以加速检索。

优化SQL查询语句，使用更有效率的方法。

限制返回的行数。

示例：假设有一个表orders包含订单信息，现在要查询特定日期范围内的订单数量：

-- 原始查询SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-01';
-- 优化后的查询CREATE INDEX idx_order_date ON orders (order_date);SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-01';

2.连接查询性能问题

问题原因：连接查询（特别是跨多个表的连接）可能会导致性能下降，尤其是在没有合适索引的情况下。

优化策略：

确保连接字段上存在索引。

尽可能使用INNER JOIN替代其他类型的连接，因为INNER JOIN的性能通常更好。

示例：假设有两个表orders和customers，需要检索订单及其关联的客户信息：

-- 原始查询SELECT o.*, c.nameFROM orders oJOIN customers c ON o.customer_id = c.id;
-- 优化后的查询CREATE INDEX idx_customer_id ON orders (customer_id);SELECT o.*, c.nameFROM orders oJOIN customers c ON o.customer_id = c.id;

3.子查询性能问题

问题原因：子查询可能会执行多次，导致性能下降。

优化策略：

将子查询转换为连接查询或者联合查询。

优化子查询本身，确保其尽可能高效。

示例：假设需要查询销售额超过平均销售额的产品列表：

-- 原始查询SELECT product_id, product_nameFROM productsWHERE price > (SELECT AVG(price) FROM products);
-- 优化后的查询SELECT p.product_id, p.product_nameFROM products pJOIN (SELECT AVG(price) AS avg_price FROM products) AS avg_tableWHERE p.price > avg_table.avg_price;

4.过度使用通配符%的 LIKE 查询

问题原因：以%开头的 LIKE 查询会导致索引失效，因此性能较差。

优化策略：

尽可能避免在LIKE查询中使用%开头的通配符。

如果不可避免，考虑全文搜索或者其他更适合的索引方法。

示例：假设需要查询以"abc"开头的产品名称：

-- 原始查询SELECT * FROM products WHERE product_name LIKE 'abc%';
-- 优化后的查询SELECT * FROM products WHERE product_name >= 'abc' AND product_name < 'abd';

5.大批量插入或更新

问题原因：大量数据的插入或更新可能会导致性能下降，特别是在事务中进行时。

优化策略：

使用批量插入或更新操作，减少事务的开销。

禁用或延迟索引更新，以减少插入或更新操作的负担。

示例：假设需要批量插入大量订单信息：

-- 原始插入操作INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');INSERT INTO orders (order_id, customer_id, order_date) VALUES (2, 1002, '2024-03-15');...-- 优化后的批量插入操作INSERT INTO orders (order_id, customer_id, order_date) VALUES(1, 1001, '2024-03-15'), (2, 1002, '2024-03-15'), ...;

6.频繁的重复查询

问题原因：重复执行相同的查询会造成资源浪费，尤其是在查询结果较大或复杂时。

优化策略：

使用缓存技术，如数据库查询缓存或应用程序级别的缓存，避免重复查询相同的数据。

考虑使用预编译语句或存储过程，以减少解析和编译的开销。

示例：假设需要频繁查询某产品的库存数量：

-- 原始查询SELECT stock_quantity FROM products WHERE product_id = 123;
-- 优化后的查询（使用缓存）SELECT stock_quantity FROM cached_stock WHERE product_id = 123;

7.过度使用分组和聚合函数

问题原因：对大数据集进行分组和聚合操作可能会导致性能下降，尤其是在没有适当索引或者内存不足的情况下。

优化策略：

确保使用合适的索引以加速分组和聚合操作。

考虑使用汇总表（Summary Tables）或者其他预计算技术，避免在运行时进行大量聚合计算。

示例：假设需要计算每个产品的平均销售额：

-- 原始查询SELECT product_id, AVG(amount) AS avg_amountFROM salesGROUP BY product_id;
-- 优化后的查询（使用汇总表）SELECT product_id, avg_amountFROM product_sales_summary;

8.大量重复数据的查询

问题原因：当查询中存在大量重复的数据时，可能会导致不必要的资源消耗，降低查询效率。

优化策略：

使用 DISTINCT 关键字去除重复数据，但要注意使用得当，避免过多的内存消耗。

考虑是否有更好的数据模型或者查询方式来避免重复数据的产生。

示例：假设需要查询订单表中不同客户的数量：

-- 原始查询SELECT COUNT(customer_id) FROM orders;
-- 优化后的查询（使用DISTINCT）SELECT COUNT(DISTINCT customer_id) FROM orders;

9.过度使用 OR 条件的查询

问题原因：当查询中存在多个OR条件时，数据库可能无法有效使用索引，导致全表扫描，降低性能。

优化策略：

将OR条件转换为使用IN或者UNION操作，以提高查询效率。

确保在OR条件中的每个字段都有索引，以帮助数据库优化查询计划。

示例：假设需要查询销售订单中某个客户或者某个产品的订单数量：

-- 原始查询SELECT COUNT(*) FROM orders WHERE customer_id = 1001 OR product_id = 123;
-- 优化后的查询（使用UNION）SELECT COUNT(*) FROM (    SELECT * FROM orders WHERE customer_id = 1001    UNION    SELECT * FROM orders WHERE product_id = 123) AS combined_orders;

10.大型数据分页查询

问题原因：当需要获取大型数据集的分页结果时，传统的LIMIT OFFSET方法可能导致性能下降，特别是在偏移量较大时。

优化策略：

使用基于游标的分页（cursor-based pagination）来避免OFFSET的性能问题。

考虑在应用层面或者缓存层面进行分页处理，减轻数据库的负担。

示例：假设需要获取订单表中的第 1001 到第 1050 行的数据：

-- 原始分页查询SELECT * FROM orders LIMIT 50 OFFSET 1000;
-- 优化后的查询（使用游标）SELECT * FROM orders WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 1 OFFSET 1000) LIMIT 50;

11.使用不必要的列

问题原因：当查询中包含不必要的列时，数据库引擎可能会浪费时间和资源来获取这些不需要的数据。

优化策略：

只选择查询中需要的列，避免选择不必要的列。

如果存在大对象（如BLOB或CLOB），尽量避免在查询中选择这些对象，除非确实需要。

示例：假设需要查询订单表中订单号和订单日期：

-- 原始查询SELECT * FROM orders;
-- 优化后的查询（只选择需要的列）SELECT order_id, order_date FROM orders;

12.频繁更新的表

问题原因：当表上有大量更新操作时，可能会导致表锁定和性能下降。

优化策略：

尽可能减少更新操作，考虑是否可以将数据操作转移到非热点表上。

将更新操作批量处理，以减少事务开销。

示例：假设有一个用户登录日志表，需要更新用户最后一次登录时间：

-- 原始更新操作UPDATE login_logs SET last_login = NOW() WHERE user_id = 123;
-- 优化后的更新操作（批量处理）UPDATE login_logsSET last_login = NOW()WHERE user_id IN (123, 124, 125);

13.未使用索引的外键约束

问题原因：虽然外键约束可以确保数据完整性，但如果没有为外键字段创建索引，可能会导致性能下降。

优化策略：

确保为外键字段创建索引，以加速相关的数据操作。

在数据库设计阶段考虑外键索引的需要性。

示例：假设有一个订单表，包含客户 ID 作为外键，需要为外键字段创建索引：

-- 创建外键约束ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);
-- 为外键字段创建索引CREATE INDEX idx_customer_id ON orders (customer_id);

14.大型查询的分批处理

问题原因：当查询涉及大量数据时，一次性处理可能会导致内存和 CPU 资源过度消耗。

优化策略：

将大型查询分成多个较小的批次进行处理，以减少每个批次的负载。

使用游标或者分页技术来处理大型数据集。

示例：假设需要对大型用户表进行逐行处理：

-- 原始查询SELECT * FROM users;
-- 优化后的查询（使用游标）DECLARE cursor_name CURSOR FOR SELECT * FROM users;OPEN cursor_name;FETCH NEXT FROM cursor_name;-- 逐行处理数据...CLOSE cursor_name;

15.未使用存储过程的重复逻辑

问题原因：当有重复的业务逻辑需要在多个地方执行时，未使用存储过程可能导致代码重复和维护困难。

优化策略：

将重复的逻辑封装到存储过程中，以便在需要时可以重复使用。

使用存储过程可以减少网络通信开销，并且可以通过权限控制提高安全性。

示例：假设需要在多个地方计算订单总额：

-- 原始逻辑SELECT SUM(total_amount) FROM orders WHERE customer_id = 123;
-- 优化后的存储过程CREATE PROCEDURE CalculateOrderTotal(IN customer_id INT)BEGIN  SELECT SUM(total_amount) FROM orders WHERE customer_id = customer_id;END;

16.未使用合适的数据类型

问题原因：选择不恰当的数据类型可能会导致存储空间浪费和性能下降。

优化策略：

使用最合适的数据类型来存储数据，避免过大或过小的数据类型。

尽量使用整型来存储整数数据，使用定点数或浮点数来存储小数数据。

示例：假设需要存储订单数量信息：

-- 原始表定义CREATE TABLE orders (order_id INT, quantity VARCHAR(10));
-- 优化后的表定义CREATE TABLE orders (order_id INT, quantity INT);

17.大量写操作导致的锁竞争

问题原因：当有大量写操作时，可能会导致锁竞争，降低数据库的并发性能。

优化策略：

尽量减少长时间持有锁的操作，例如尽快提交或回滚事务。

考虑分批处理写操作，以减少锁竞争的影响。

示例：假设有大量用户同时进行下单操作：

-- 原始下单操作BEGIN TRANSACTION;INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');-- 更多写操作...COMMIT;
-- 优化后的下单操作（分批处理）BEGIN TRANSACTION;INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');COMMIT;-- 分批处理更多下单操作...

18.频繁使用数据库函数和表达式

问题原因：频繁使用复杂的数据库函数和表达式可能会导致计算开销增加，影响查询性能。

优化策略：

尽量减少使用数据库函数和表达式，尽可能在应用层或缓存层进行计算。

对于经常使用的表达式，考虑将其结果存储在列中，以减少重复计算。

示例：假设需要计算订单总额并加上税率：

-- 原始查询SELECT SUM(total_amount * (1 + tax_rate)) FROM orders;-- 优化后的查询（使用预计算结果）ALTER TABLE orders ADD COLUMN total_with_tax DECIMAL(10, 2);UPDATE orders SET total_with_tax = total_amount * (1 + tax_rate);SELECT SUM(total_with_tax) FROM orders;

19.未使用合适的索引策略

问题原因：选择不当的索引策略可能会导致索引失效或不必要的索引开销。

优化策略：

定期检查和优化索引，确保其适应实际查询需求。

根据查询模式选择合适的索引类型，例如B-Tree索引、哈希索引等。

示例：假设有一个订单表，经常需要根据订单日期范围进行查询：

-- 原始索引策略CREATE INDEX idx_order_date ON orders (order_date);
-- 优化后的索引策略（创建覆盖索引）CREATE INDEX idx_order_date_amount ON orders (order_date, total_amount);

20.大量数据的联合操作

问题原因：在执行大量数据的联合操作（UNION、UNION ALL）时，数据库可能需要对结果集进行排序和去重，导致性能下降。

优化策略：

尽量避免使用联合操作，如果必须使用，确保各个子查询的结果集尽量小且已经过滤，以减少排序和去重的开销。

考虑使用其他方法代替联合操作，如JOIN、子查询等。

示例：假设需要获取两个表的不重复记录：

-- 原始查询SELECT * FROM table1UNIONSELECT * FROM table2;
-- 优化后的查询（使用JOIN）SELECT DISTINCT t1.* FROM table1 t1JOIN table2 t2 ON t1.id = t2.id;

21.数据分布不均匀的索引

问题原因：如果索引数据分布不均匀，可能会导致部分索引页过度填充，而另一部分过度稀疏，影响查询性能。

优化策略：

定期重新组织索引或重新构建索引，以平衡数据分布。

根据具体情况，考虑使用分区表或者其他分布均匀的数据结构。

示例：假设有一个用户表，根据用户名进行查询，但是数据分布不均匀：

-- 原始索引CREATE INDEX idx_username ON users (username);
-- 优化后的索引（重新组织）ALTER INDEX idx_username REORGANIZE;

22.过度使用子查询

问题原因：过度使用子查询可能会导致查询执行效率低下，尤其是嵌套子查询的情况下。

优化策略：

将子查询转换为JOIN操作，以减少查询的嵌套层次。

尽量避免在SELECT子句中使用子查询。

示例：假设需要查询每个用户的最新订单：

-- 原始查询SELECT user_id, (SELECT MAX(order_date) FROM orders WHERE orders.user_id = users.user_id) AS latest_order_date FROM users;
-- 优化后的查询（使用JOIN）SELECT users.user_id, MAX(orders.order_date) AS latest_order_dateFROM usersLEFT JOIN orders ON users.user_id = orders.user_idGROUP BY users.user_id;

23.未使用批量操作

问题原因：频繁执行单条数据操作可能会导致数据库连接开销增加，从而降低性能。

优化策略：

尽量使用批量操作来替代单条数据操作，以减少数据库连接开销。

对于大批量数据的操作，可以考虑使用批量导入或批量更新的方式，减少交互次数。

示例：假设需要更新大量订单的状态：

-- 原始更新操作UPDATE orders SET status = 'shipped' WHERE order_id = 1;UPDATE orders SET status = 'shipped' WHERE order_id = 2;-- 更多单条更新操作...
-- 优化后的批量更新操作UPDATE orders SET status = 'shipped' WHERE order_id IN (1, 2, ...);

24.过度使用内存表

问题原因：虽然内存表在某些情况下可以提高查询速度，但过度使用内存表可能会导致内存消耗过大，甚至影响系统的稳定性。

优化策略：

评估内存表的使用情况，确保仅在适当的情况下使用内存表。

对于大数据量或需要持久化存储的数据，避免使用内存表，而是选择合适的磁盘存储方式。

示例：假设需要对查询结果进行排序：

-- 原始查询（使用内存表进行排序）SELECT * FROM orders ORDER BY order_date;
-- 优化后的查询（使用磁盘排序）SELECT * FROM orders ORDER BY order_date;

25.缺乏定期统计和优化

问题原因：未定期统计表的数据分布和索引使用情况，未进行数据库性能优化可能导致查询性能逐渐下降。

优化策略：

定期执行统计分析，评估表的数据分布和索引使用情况。

根据统计结果，定期优化数据库的索引、表结构或者查询语句。

示例：假设需要定期分析数据库性能：

-- 定期执行统计分析ANALYZE TABLE orders;
-- 定期优化数据库索引OPTIMIZE TABLE orders;

26.未使用合适的数据库引擎

问题原因：选择不合适的数据库引擎可能导致性能下降，无法充分发挥数据库的优势。

优化策略：

根据具体的业务需求和性能要求选择合适的数据库引擎，如InnoDB、MyISAM等。

评估和比较不同数据库引擎的特性，选择最适合当前业务场景的引擎。

示例：假设需要存储事务性数据和执行频繁的读写操作：

-- 使用InnoDB引擎存储事务性数据CREATE TABLE orders (order_id INT, customer_id INT, order_date DATE) ENGINE=InnoDB;
-- 使用MyISAM引擎存储非事务性数据CREATE TABLE logs (log_id INT, log_message TEXT) ENGINE=MyISAM;

27.使用强制类型转换

问题原因：频繁使用强制类型转换可能会导致查询执行效率低下，尤其是在大数据量的情况下。

优化策略：

尽量避免在查询中使用强制类型转换，而是尽量保持数据类型一致性。

如果必须进行类型转换，尽可能在应用程序层面完成，而不是在数据库层面。

示例：假设需要将字符串类型的日期转换为日期类型：

-- 原始查询（使用强制类型转换）SELECT * FROM orders WHERE DATE(order_date) = '2024-03-15';
-- 优化后的查询（避免强制类型转换）SELECT * FROM orders WHERE order_date = '2024-03-15';

28.未优化的长事务

问题原因：长时间运行的事务可能会阻塞其他事务，影响数据库的并发性能。

优化策略：

尽量减少事务的持续时间，尽早提交或者回滚事务。

如果事务必须长时间运行，考虑是否可以使用行级锁而不是表级锁。

示例：假设需要处理大量订单数据：

-- 原始事务BEGIN TRANSACTION;-- 处理订单数据...COMMIT;
-- 优化后的事务（分批提交）BEGIN TRANSACTION;-- 处理部分订单数据...COMMIT;-- 继续处理剩余的订单数据...

29.未优化的存储过程

问题原因：存储过程中可能存在未优化的SQL语句，导致存储过程执行效率低下。

优化策略：

定期评估和优化存储过程中的SQL语句，确保其执行效率。

使用合适的索引、避免不必要的循环或递归等方式优化存储过程。

示例：假设存在一个存储过程用于计算订单总额：

-- 原始存储过程CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)BEGIN  DECLARE total DECIMAL(10, 2);  SELECT SUM(amount) INTO total FROM order_details WHERE order_id = order_id;  SELECT total;END;
-- 优化后的存储过程（使用索引）CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)BEGIN  DECLARE total DECIMAL(10, 2);  SELECT SUM(amount) INTO total FROM order_details WHERE order_id = order_id;  SELECT total;END;

30.未考虑 DB 服务器配置和硬件资源

问题原因：数据库服务器配置不合理或硬件资源不足可能会导致数据库性能低下。

优化策略：

根据数据库负载情况和性能需求合理配置数据库服务器的硬件资源，包括CPU、内存、磁盘等。

定期监控数据库服务器的性能指标，进行性能调优和升级。

示例：假设数据库服务器负载过高，需要升级硬件资源：

-- 原始查询数据库服务器性能指标SHOW STATUS;
-- 优化后的升级硬件资源增加CPU核心数、内存容量或者使用更快的磁盘。

结语

这些优化策略和示例可以帮助改善 SQL 查询的性能和效率。在实践中，需要综合考虑数据库设计、SQL 编写、服务器配置等多方面因素，选择合适的优化方法，并进行充分的测试和验证。以上 30 个经验是在实际经验中总结的内容，当然，业务场景不同，具体的优化策略也会不同，按实际情况处理，这不就是程序员要做的事情么？

作者丨 威哥爱编程

来源丨公众号：Java架构栈（ID：）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn

跳转微信打开