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

数据库查询优化

ops 2025/9/6 5:36:43

这篇文章适合刚刚入手项目的同学，为大家如何提高数据库查询效率提供一些建议。

1.添加索引

1.1 索引是什么

对于索引基础薄弱的同学，我们可以从 “索引是什么” 简单类比：索引就像书籍的目录，能帮数据库快速定位到需要的数据，而不用逐行扫描全表。下面结合项目具体说明索引的创建逻辑和使用方式。

1.2 索引的作用

索引的核心功能是帮助数据库快速定位目标数据，避免全表扫描，从而大幅提升查询效率（类似书籍目录的作用）。

这里以复合索引举一个例子：

定义：由多个字段组合创建的索引，遵循 “最左前缀原则”（查询条件包含索引的前 N 个字段时才能生效）。
项目示例：

-- 订单表：按“状态+订单时间”创建复合索引
CREATE INDEX idx_orders_status_time ON orders(status, order_time DESC);

作用：优化包含 status 和 order_time 的查询，例如：

-- 能用到上述索引的查询
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY order_time DESC;
SELECT * FROM orders WHERE status = 'PENDING' AND order_time > '2024-01-01';

1.3 索引的使用方式

1.3.1 索引提示（强制使用指定索引）

当数据库优化器未选择最优索引时，通过 /*+ INDEX(表名索引名) */ 强制指定：

<!-- 订单查询中强制使用 idx_orders_status_time 索引 -->
<select id="pageQuery" resultType="com.sky.entity.Orders">SELECT /*+ INDEX(orders idx_orders_status_time) */ id, number, status, user_id, order_time, amountFROM ordersWHERE status = #{status}ORDER BY order_time DESC
</select>

1.3.2 配合查询优化使用索引

避免 SELECT *：只查询需要的字段，让覆盖索引生效（否则会触发回表）：

<!-- 优化后：只查索引包含的字段 -->
<select id="pageQuery" resultType="com.sky.entity.Orders">SELECT id, number, status, order_time  -- 这些字段都在 idx_orders_cover_list 中FROM orders WHERE status = #{status}
</select>

1.3.3 优化分页查询

使用游标分页时，通过索引字段（如 order_time）定位，避免大偏移量导致的全表扫描：

<!-- 基于 order_time 索引的游标分页 -->
<select id="pageQueryByCursor" resultType="com.sky.entity.Orders">SELECT id, order_time FROM ordersWHERE order_time < #{lastOrderTime}  -- 利用索引快速定位ORDER BY order_time DESCLIMIT #{limit}
</select>

1.4 索引的注意事项

1.4.1 索引不是越多越好

索引会增加写入（插入 / 更新 / 删除）的开销（因为要维护索引结构），项目中通过定期删除无用索引（如长期未被使用的索引）避免性能损耗。

1.4.2 索引失效场景

不满足最左前缀原则（如用 idx_orders_status_time 时，查询条件不含 status）；
在索引字段上使用函数（如 DATE(order_time) = '2024-01-01'，需用函数索引解决）；
模糊查询以 % 开头（如 name LIKE '%鱼香肉丝'，索引失效，项目中通过 idx_dish_name_status 优化前缀匹配）。

1.4.3 定期维护索引

项目建议通过 ANALYZE TABLE 更新表统计信息，帮助数据库优化器选择最优索引：

ANALYZE TABLE orders;  -- 更新订单表统计信息

ANALYZE TABLE 是数据库（如 MySQL）的内置命令，作用是重新计算并更新表的统计信息（如行数、数据分布、索引基数等），这些信息会被数据库优化器直接使用，只需在 SQL 客户端中直接执行 ANALYZE TABLE 表名；即可完成统计信息更新无需在应用层或数据库配置文件中做额外设置。

2.SQL查询优化

通过优化 SQL 语句逻辑，减少不必要的数据处理，提升执行效率：

避免SELECT *：只查询必要字段，减少数据传输量和回表操作。例如OrderMapper.xml中，优化后的查询明确指定所需字段（如id, number, status等），而非查询所有字段。
优化 JOIN 查询：用INNER JOIN替代LEFT JOIN（适用场景），避免返回无效空数据，减少关联扫描范围。例如菜品查询中，通过INNER JOIN关联菜品表和分类表，确保关联数据有效性。
索引提示：使用/*+ INDEX(table_name index_name) */强制优化器使用指定索引，避免索引选择偏差。例如订单统计查询中，通过/*+ INDEX(orders idx_orders_status_time) */确保使用复合索引。

3.分页查询优化

针对传统分页（LIMIT offset, size）在大偏移量时性能差的问题，采用游标分页：

原理：以上一次查询的最后一条记录的排序字段（如order_time）作为条件，避免扫描偏移量前的所有数据。
示例：OrderMapper.xml中pageQueryByCursor方法通过WHERE order_time < #{lastOrderTime}定位分页起点，配合LIMIT获取数据，性能不受偏移量影响。

<!-- 游标分页查询，避免OFFSET性能问题 --><select id="pageQueryByCursor" resultType="com.sky.entity.Orders">SELECT /*+ INDEX(orders idx_orders_status_time) */id, number, status, user_id, address_book_id, order_time, checkout_time, pay_method, pay_status, amount, remark, phone, address, consignee, estimated_delivery_time, delivery_status, pack_amount, tableware_number, tableware_statusFROM orders<where><if test="lastOrderTime != null">and order_time &lt; #{lastOrderTime}</if><if test="status != null">and status = #{status}</if><if test="userId != null">and user_id = #{userId}</if></where>ORDER BY order_time DESCLIMIT #{limit}</select>

4. 批量操作优化

减少与数据库的交互次数，降低网络开销：

批量插入 / 更新 / 删除：通过foreach标签在 XML 中实现批量操作。例如菜品批量插入时，一次性插入多条数据，而非单条循环执行：

<insert id="batchInsert">INSERT INTO dish (...) VALUES<foreach collection="list" item="dish" separator=",">(#{dish.name}, #{dish.categoryId}, ...)</foreach>
</insert>

5.数据库配置优化（spring.datasource.druid）

在配置文件application.yml中配置。

5.1 Druid数据库连接池优化配置

Druid 是高性能连接池，该部分配置直接决定数据库连接的利用率、稳定性和查询效率，是数据库优化的 “基石”：

spring:    druid:driver-class-name: ${sky.datasource.driver-class-name}#rewriteBatchedStatements: true 优化 MySQL 批量插入 / 更新（将多条 SQL 合并为 1 条，减少网络交互）url: jdbc:mysql://${sky.datasource.host}:${sky.datasource.port}/${sky.datasource.database}?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=trueusername: ${sky.datasource.username}password: ${sky.datasource.password}# 连接池优化配置#系统启动时创建 10 个连接，避免首次请求时 “临时创建连接” 的延迟initial-size: 10#保留 20 个空闲连接，避免 “频繁创建 / 销毁连接” 的资源损耗min-idle: 20#限制并发连接上限，避免连接过多压垮数据库max-active: 100#避免线程无限阻塞在 “获取连接” 上，快速失败并释放资源max-wait: 30000time-between-eviction-runs-millis: 30000min-evictable-idle-time-millis: 900000#定期检查空闲连接是否可用，避免使用 “失效连接”（如网络波动导致的死连接）test-while-idle: truevalidation-query: SELECT 1test-on-borrow: falsetest-on-return: false#缓存 SQL 预处理语句，避免重复解析 SQL（尤其高频查询，如 “查询在售菜品”）pool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 20filters: stat,wall,slf4j#记录执行时间超过 5 秒的 SQL，便于定位性能瓶颈connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000#避免 “连接泄漏”（如代码未关闭连接）导致连接池耗尽remove-abandoned: true                    # 自动回收超时连接remove-abandoned-timeout: 1800            # 超时时间30分钟log-abandoned: true                       # 记录超时连接日志

5.2 MyBatis 执行优化配置(mybatis.configuration)

MyBatis 作为 ORM 框架，其配置直接影响 SQL 执行效率和内存使用，属于 “数据库操作层” 的优化：

mybatis:#mapper配置文件mapper-locations: classpath:mapper/*.xmltype-aliases-package: com.sky.entityconfiguration:#开启驼峰命名，自动将数据库字段（如 order_time）映射为 Java 驼峰属性（orderTime），避免手动配置 resultMapmap-underscore-to-camel-case: true# 开启二级缓存，缓存 MyBatis 执行结果（跨 SqlSession 共享），减少重复查询数据库cache-enabled: true#开启 “按需延迟加载”，关联查询时（如 “查询订单 + 关联订单详情”），仅在使用关联数据时才查库，避免冗余数据加载# 开启延迟加载lazy-loading-enabled: true# 设置积极的延迟加载aggressive-lazy-loading: false# 设置延迟加载的触发方法lazy-load-trigger-methods: equals,clone,hashCode,toString# 设置默认执行器为复用，复用 SQL 执行器（避免频繁创建执行器实例），减少对象创建销毁的资源损耗default-executor-type: REUSE# 设置默认语句超时时间，SQL执行超时=30秒，限制单条 SQL 的执行时间，避免长耗时查询阻塞数据库连接default-statement-timeout: 30# 结果集每次获取 100 行，分批读取查询结果，避免一次性加载大量数据导致内存溢出default-fetch-size: 100

5.3 隐藏参数：数据库连接URL参数（spring.datasource.druid.url）

spring：  datasource:druid:url: jdbc:mysql://${sky.datasource.host}:${sky.datasource.port}/${sky.datasource.database}?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=true

URL 参数	作用	优化效果（外卖场景）
`rewriteBatchedStatements=true`	优化 MySQL 批量操作（将 `INSERT INTO ...; INSERT INTO ...` 合并为 `INSERT INTO ... VALUES (...),(...)`）	外卖系统 “批量添加购物车商品”“批量更新菜品库存” 时，网络交互次数从 N 次减少到 1 次，效率提升 10-100 倍
`cachePrepStmts=true` + `prepStmtCacheSize=250` + `prepStmtCacheSqlLimit=2048`	开启客户端 PreparedStatement 缓存，缓存 SQL 模板和参数	高频查询（如 “查询菜品详情（dish_id=?）”）可复用缓存的 SQL 模板，避免重复解析 SQL 语法，提升查询速度
`useServerPrepStmts=true`	使用 MySQL 服务器端预处理语句，而非客户端模拟	进一步减少 SQL 解析开销，尤其对复杂 SQL（如 “多条件筛选订单”），提升执行效率
`zeroDateTimeBehavior=convertToNull`	将数据库中的 `0000-00-00` 时间转为 `null`	避免因时间格式不兼容导致的 SQL 执行异常，保障订单时间查询的稳定性

5.4 总结：这些配置解决了数据库的哪些核心问题？

连接管理问题：通过 Druid 连接池参数（max-active/min-idle/ 超时回收），避免 “连接耗尽” 或 “无效连接”，支撑外卖高峰的高并发；
SQL 效率问题：通过 MyBatis 二级缓存、延迟加载、执行器复用，减少数据库访问次数，降低高频查询的压力；
批量操作问题：rewriteBatchedStatements 等 URL 参数，优化外卖系统的批量场景（如批量导入菜品、批量更新订单）；
问题定位问题：slowSqlMillis 记录慢 SQL，便于后续优化（如给慢查询加索引、调整 SQL 逻辑）。

这些配置与之前提到的 “索引优化”“ANALYZE TABLE 统计信息” 形成互补，共同保障数据库在高并发下的稳定和高效。

6.数据库监控实现

数据库监控是干嘛的？简单说，数据库监控就是「给数据库操作装个 “记录仪”」，帮你实时掌握：

数据库连接够不够用？有没有连接超时、浪费的情况？
执行的 SQL 快不快？有没有 “拖慢系统” 的慢 SQL？
有没有危险的 SQL（比如注入攻击）？
每个 SQL 执行了多少次、查了多少数据、耗时多久？

这里我们通过Druid连接池（阿里开源的数据库连接池工具）和日志配置来内置这套“记录仪”。

6.1 核心实现工具：Druid连接池

项目中的数据库连接使用spring.datasource.druid 相关配置,而Druid本身自带强大的监控功能，不需要额外集成第三方工具（如Prometheus），只需要通过配置 “开启” 即可。

在项目application.yml中配置：

spring:datasource:druid:# 1. 开启监控相关的过滤器（核心！监控的“入口”）filters: stat,wall,slf4j# 2. 监控参数配置（比如“慢SQL”的定义、是否合并重复SQL）connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000# 3. 超时连接监控（记录“没人要”的连接）remove-abandoned: true                    # 自动回收超时连接remove-abandoned-timeout: 1800            # 超时时间：30分钟（1800秒）log-abandoned: true                       # 记录超时连接的日志

先拆解这几个核心配置的作用 —— 它们是监控功能的 “开关” 和 “规则”：

配置项	通俗解释	监控作用
`filters: stat,wall,slf4j`	给 Druid 装 3 个 “插件”	- `stat`：SQL 执行监控插件（核心），记录 SQL 执行耗时、次数、行数 - `wall`：防 SQL 注入插件（附带监控），拦截危险 SQL 并记录 - `slf4j`：日志输出插件，把监控数据输出到日志文件
`druid.stat.slowSqlMillis=5000`	定义 “慢 SQL”：执行时间超过 5000 毫秒（5 秒）的 SQL	自动标记慢 SQL，方便排查 “拖慢系统” 的语句
`druid.stat.mergeSql=true`	把相同的 SQL 合并统计（比如 “SELECT * FROM user WHERE id=1” 和 “id=2” 算一类）	避免重复 SQL 刷屏，更清晰看 “哪类 SQL 执行最多 / 最慢”
`remove-abandoned + log-abandoned`	连接 “没人要”（比如代码没关连接）超过 30 分钟，就回收并记录日志	监控 “连接泄露” 问题（连接泄露会导致数据库连接不够用）

6.2 具体监控内容

基于上面的配置，项目会监控 3 大类核心数据，每类都能直接帮你排查问题：

1.数据库连接池监控（看 “连接够不够用”）

连接池是 “管理数据库连接的容器”（比如你配置的 initial-size=10 是初始 10 个连接，max-active=100 是最多 100 个连接）。监控会实时统计：

当前活跃的连接数（正在用的连接）
空闲连接数（没被用的连接）
等待连接的请求数（连接不够时，排队等连接的请求）
连接超时 / 泄露的次数（比如超过 30 分钟没关的连接，会被记录）

这些数据的来源，就是你配置的 initial-size「初始连接数」、max-active「最大连接数」、remove-abandoned-timeout「超时时间」等 ——Druid 会自动统计这些配置对应的实际使用情况。

2.SQL 执行监控（看 “SQL 快不快、多不多”）

这是最常用的监控，stat 过滤器会拦截每一次 SQL 执行，记录：

基础信息：执行的 SQL 语句（合并后的，比如 “SELECT * FROM user WHERE id=?”）
性能信息：
- 执行总次数（这个 SQL 跑了多少次）
- 平均耗时（每次执行平均花多久）
- 最大耗时（单次执行最久的时间）
- 慢 SQL 次数（超过 5 秒的次数）
数据量信息：每次 SQL 返回多少行数据、影响多少行数据（比如 INSERT/UPDATE 的行数）

举个例子：如果监控到 “SELECT * FROM order WHERE create_time < '2024-01-01'” 执行了 100 次，平均耗时 6 秒，那这个就是 “慢 SQL”，需要优化（比如加索引）。

3.SQL 注入防护监控（看 “有没有危险 SQL”）

wall 过滤器（防注入插件）会监控并拦截危险 SQL，比如：

用户输入的恶意语句（比如 “SELECT * FROM user WHERE name='admin' OR 1=1”—— 这会查所有用户数据）
禁止的操作（比如 DROP TABLE 删表、ALTER TABLE 改表结构）

如果有危险 SQL 被拦截，slf4j 插件会把这个行为记录到日志里，方便你知道 “有人在尝试攻击数据库”。

6.3 查看监控数据

两种方式，新手优先看日志。

1. 看日志文件

我们的logging配置已经指定日志输出路径和级别，监控数据会自动写到我们在application.yml中指定的文件中。

logging:level:com.alibaba.druid: info  # Druid的监控日志输出级别（info级别的日志会记录）org.mybatis: debug       # MyBatis的SQL日志（会打印执行的SQL语句）file:name: logs/szj.log  # 日志文件路径

你只需要打开指定的文件就能看到：

慢 SQL 日志：比如 [DruidDataSourceStatLogger] slow sql 5001ms: SELECT * FROM order WHERE ...（明确标出耗时 5.001 秒的慢 SQL）
连接泄露日志：比如 [DruidDataSource] abandon connection, url:jdbc:mysql://..., timeout:1800秒（标出超时 30 分钟的连接）
SQL 执行日志：MyBatis 的 debug 级别会打印 “执行的 SQL 语句 + 参数”，比如 ==> Preparing: SELECT * FROM user WHERE id=? ==> Parameters: 1(Integer)

2.Druid 可视化控制台(更直观)

如果觉得看日志不够直观，还可以开启 Druid 自带的 Web 控制台（类似一个网页，能看到图表化的监控数据）。只需要在你的项目中加一段配置（Spring Boot 项目），就能通过浏览器访问：

spring:datasource:druid:# 开启Druid控制台stat-view-servlet:enabled: true        # 开启控制台url-pattern: /druid/* # 访问路径：http://你的项目地址:8080/druidlogin-username: admin # 控制台登录账号（自己设）login-password: 123456 # 控制台登录密码（自己设）

配置后，打开浏览器访问 http://localhost:8080/druid（如果你项目的端口是 8080），登录后就能看到：