在业务数据量快速增长的情况下，原本运行正常的SQL查询可能会出现响应缓慢、占用大量数据库资源的问题，严重时甚至会导致数据库服务不可用。针对这类大数据量查询的场景，需要从多个维度进行优化，才能有效提升查询性能。
索引层面优化
索引是提升查询性能最直接的方式，但不合理的索引设计反而会降低性能，需要遵循以下原则：
- 优先为查询条件、连接条件、排序字段创建索引，避免全表扫描
- 控制索引数量，单表索引建议不超过5个，避免写入时维护索引的开销过大
- 避免对索引字段做函数运算或类型转换，否则索引会失效
- 对于区分度低的字段，不建议单独创建索引，可考虑组合索引
以下是创建组合索引的示例，假设经常需要根据用户ID和创建时间查询订单数据：
-- 创建组合索引，注意字段顺序，区分度高的字段放前面 CREATE INDEX idx_user_create_time ON orders(user_id, create_time);
SQL语句编写优化
不合理的SQL语句写法会导致即使有索引也无法生效，需要避免以下常见问题：
- 避免使用
SELECT *，只查询需要的字段，减少数据传输和解析开销 - 避免使用
LIKE '%关键词%'这种前置模糊查询，会导致索引失效，可改为后置模糊LIKE '关键词%' - 减少子查询的使用，尽量用连接查询替代，子查询可能会产生临时表
- 合理使用分页，大数据量分页时避免
LIMIT 100000, 10这种写法，可改为基于主键的分页方式
以下是优化分页查询的示例，原查询在大偏移量时性能很差：
-- 原慢查询 SELECT id, order_no, amount FROM orders LIMIT 100000, 10; -- 优化后查询，基于上一页的最大ID进行查询 SELECT id, order_no, amount FROM orders WHERE id > 100000 LIMIT 10;
数据库配置与架构优化
当单表数据量超过千万级时，仅靠SQL和索引优化可能无法满足需求，需要结合配置和架构调整：
- 调整数据库参数，比如增大
innodb_buffer_pool_size，让更多数据和索引缓存在内存中 - 对大表进行分库分表，可按时间、业务维度拆分，降低单表数据量
- 对于读多写少的场景，搭建读写分离架构，将查询请求分流到从库
- 对高频查询的结果做缓存，比如使用Redis缓存查询结果，减少数据库访问次数
优化效果验证
每次优化后都需要验证效果，可使用数据库自带的执行计划工具分析查询是否走索引、扫描行数是否合理。以MySQL为例，使用EXPLAIN关键字查看执行计划：
-- 查看查询的执行计划 EXPLAIN SELECT id, order_no, amount FROM orders WHERE user_id = 123 AND create_time > '2024-01-01';
执行计划中的type字段如果是ref或range说明索引使用合理，如果是ALL则说明是全表扫描，需要进一步优化。
注意：优化方案需要结合实际业务场景选择，不要盲目套用，比如分库分表会增加系统复杂度，只有在单表数据量确实过大时才考虑使用。