我要评论核心目标：让数据库能够通过扫描索引（小范围、有序的数据结构）快速定位到所需数据行，而不是扫描整个表（大数据量下非常慢）。
1. 理解索引的作用和场景
- 加速查找: 主要优化
where子句和join on条件的查找速度。 - 加速排序: 优化
order by子句，避免文件排序 (filesort)。 - 加速分组: 优化
group by子句，帮助快速找到相同分组。 - 唯一性约束:
unique索引还能保证数据的唯一性。
2. 识别需要索引的查询
在开始添加索引之前，先找出哪些查询是性能瓶颈或者执行频率高。前面我们也介绍过，有兴趣的小伙伴可以去看一下之前的文章，我们在回顾一下：
- mysql 慢查询日志 (slow query log): 定位执行时间长的 sql。
- apm 工具 (如 skywalking): 查看请求中耗时长的调用。
explain分析: 对怀疑有问题的 sql 执行explain，检查执行计划。- 业务分析: 思考核心业务流程和高频查询场景。
重点关注涉及以下操作的查询：
- 过滤 (where):
findbyemail(string email),findallbystatus(orderstatus status) - 连接 (join): 加载关联实体，如查询订单及其用户信息。
- 排序 (order by):
findallbyorderbycreatedatdesc() - 分组 (group by): 统计类查询。
3. 掌握关键的索引类型
- 单列索引 (single-column index): 对单个列创建索引。适用于简单的、基于该列的精确匹配或范围查询。
create index idx_users_email on users (email);
- 联合索引 / 复合索引 (composite / multi-column index): 对多个列组合创建索引。极其重要，适用于涉及多个条件的
where子句或同时需要满足where和order by/group by的查询。
-- 适用于 where status = ? and created_at > ? create index idx_orders_status_created on orders (status, created_at);
- 最左前缀原则 (leftmost prefix rule): 联合索引
(a, b, c)可以支持where a=?、where a=? and b=?、where a=? and b=? and c=?的查询，但通常不支持where b=?或where a=? and c=?。列的顺序至关重要。
- 最左前缀原则 (leftmost prefix rule): 联合索引
- 覆盖索引 (covering index): 如果一个索引包含了查询所需的所有列（
select,where,order by等），数据库可以直接从索引返回结果，无需访问数据表（回表），性能极高。
-- 查询: select user_id, status from orders where order_date > ? -- 覆盖索引: create index idx_orders_date_user_status on orders (order_date, user_id, status);
- 唯一索引 (unique index): 保证索引列的值唯一，通常用于业务上的唯一标识（如用户邮箱、手机号），同时也具备普通索引的查询加速功能。
create unique index uk_users_email on users (email);
- 全文索引 (full-text index): 用于对
text类型数据进行关键词搜索。
4. 在 spring boot 项目中创建和管理索引
下面我们将理论应用到项目中实践：
错误的方式（严禁用于生产环境！）:
- 依赖 jpa/hibernate 的
spring.jpa.hibernate.ddl-auto=update或create。 - 原因:
update行为不可预测，可能丢失数据或产生意想不到的变更。create会删除整个数据库！- 无法进行版本控制和团队协作。
- 绕过了必要的 code review 和数据库变更管理流程。
@table(indexes = ...)或@index注解：这些注解主要是给ddl-auto用的，或者用于生成 ddl 脚本供其他工具使用，不应该直接依赖它们在生产环境自动创建/更新索引。
- 依赖 jpa/hibernate 的
正确的方式（生产环境标准）:
- 使用数据库迁移工具 (database migration tools): flyway 或 liquibase 是 spring boot 项目的最佳实践和必备工具。
- 工作流程:
- 添加依赖: 在
pom.xml或build.gradle中添加 flyway 或 liquibase 的 spring boot starter 依赖。
- 添加依赖: 在
<!-- flyway example -->
<dependency>
<groupid>org.flywaydb</groupid>
<artifactid>flyway-core</artifactid>
</dependency>
<dependency> <!-- if using mysql -->
<groupid>org.flywaydb</groupid>
<artifactid>flyway-mysql</artifactid>
</dependency>
<!-- liquibase example -->
<dependency>
<groupid>org.liquibase</groupid>
<artifactid>liquibase-core</artifactid>
</dependency>
- 创建迁移脚本: 在
src/main/resources/db/migration(flyway 默认) 或指定的路径 (liquibase) 下创建 sql 脚本。脚本命名需符合工具的版本规范（例如 flyway:v1__initial_schema.sql,v2__add_index_on_users_email.sql）。 - 编写 ddl: 在 sql 脚本中使用标准的
create index语句来定义索引。
-- v2__add_index_on_users_email.sql create index idx_users_email on users (email); -- v3__add_composite_index_on_orders.sql create index idx_orders_user_status on orders (user_id, status); -- v4__add_unique_index_on_products.sql create unique index uk_products_sku on products (sku);
- 运行应用: spring boot 应用启动时，flyway/liquibase 会自动检测并按版本顺序执行新的迁移脚本，将索引变更应用到数据库。
- 优点:
- 版本控制: 索引的变更可以像代码一样纳入 我们git 管理仓库中。
- 可重复: 在任何环境都能应用相同的变更。
- 自动化: 方便集成到 ci/cd 流程中。
- 团队协作: 清晰的记录了 schema 的变更历史。
- 安全: 变更经过了脚本和版本控制，减少了手动操作的失误。
5. 针对常见 spring boot 查询场景的索引策略示例
场景：通过唯一业务标识查找实体 (如 user findbyemail(string email);)
- sql :
select * from users where email = ? - 索引策略: 在
email列上创建唯一索引 (unique index)。
create unique index uk_users_email on users (email);
场景：根据状态过滤并按时间排序的分页列表 (如 page<order> findbystatusorderbycreatedatdesc(orderstatus status, pageable pageable);)
- sql :
select * from orders where status = ? order by created_at desc limit ?, ? - 索引策略: 创建联合索引，包含
status和created_at。status是等值过滤，放前面；created_at是排序，放后面。
create index idx_orders_status_created on orders (status, created_at);
- 进阶 (覆盖索引): 如果只需要少数几列（如
id,order_no,status,created_at），可以创建覆盖索引以避免回表：
create index idx_orders_status_created_cover on orders (status, created_at, id, order_no);
场景：加载关联实体 (如获取订单及其用户信息 order order = orderrepository.findbyid(id); user user = order.getuser();)
- jpa 可能生成 (取决于 fetchtype):
- 一次性 join:
select ... from orders o left join users u on o.user_id = u.id where o.id = ? - n+1 (如果 lazy fetching 且后续访问 user): 先查 order，再根据
order.user_id查 user。
- 一次性 join:
- 索引策略: 必须在外键列 (
orders.user_id) 上创建索引。
create index idx_orders_user_id on orders (user_id);
- 这样无论是 join 查询还是 n+1 中的第二次查询，都能快速通过
user_id找到对应的订单或用户。 场景：多条件过滤查询 (如
list<product> findbynamecontainingandcategoryandpricebetween(string name, string category, bigdecimal minprice, bigdecimal maxprice);)- sql :
select * from products where category = ? and price between ? and ? and name like ?(注意like的用法会影响索引效率) - 索引策略: 创建联合索引。通常将等值查询、选择性高的列放在前面。范围查询 (
between) 和like放后面。- 索引：
(category, price, name)。这样可以先用category过滤，再用price进行范围扫描。name上的like如果是'%keyword%'则此索引无效；如果是'prefix%'则可能有部分效果。 - 如果
name的查询更频繁或选择性更高，也可以考虑(name, category, price)并使用前缀索引。需要根据实际情况分析。
- 索引：
- sql :
create index idx_products_category_price_name on products (category, price, name); -- 或者，如果 name 需要前缀索引 -- create index idx_products_category_price_name on products (category, price, name(20));
6. 验证索引效果
添加索引后，必须验证它是否被正确使用且有效：
- 使用
explain:- 获取 spring boot 应用生成的 sql。
- 用实际参数替换占位符。
- 在 mysql 客户端执行
explain [your sql query];。 - 检查输出:
key列是否显示了你期望使用的索引名？type列是否是较优的类型（如ref,range,eq_ref），避免all？rows列估计扫描的行数是否显著减少？extra列是否有using filesort或using temporary？是否出现了using index（覆盖索引）？
- 性能测试:
- 在测试环境模拟负载，对比添加索引前后的查询响应时间。
- 监控:
- 观察 apm 工具中对应数据库调用的耗时变化。
- 观察慢查询日志中，之前的慢 sql 是否消失或频率降低。
总结:
在 spring boot 项目中优化 sql 查询性能，使用索引是关键。核心步骤包括：识别慢查询 -> 理解查询模式 -> 选择合适的索引类型（单列、联合、覆盖等） -> 使用数据库迁移工具 (flyway/liquibase) 在版本化的 sql 脚本中创建索引 -> 使用 explain 和监控验证效果。
以上就是springboot使用索引来优化sql查询的详细步骤的详细内容，更多关于springboot索引优化sql查询的资料请关注代码网其它相关文章！
发表评论