你写的SQL为什么总是慢？为什么建了索引还是全表扫描？为什么分页查询越往后越慢？90%以上的MySQL性能问题，根源都在于对索引底层原理的理解不到位。本文从数据结构本质出发，彻底讲透MySQL InnoDB索引的底层逻辑、聚簇索引核心机制、索引失效的底层原因，以及生产环境可落地的优化方案，让你不仅知其然，更知其所以然。
一、索引的本质：到底什么是索引？
索引的本质是帮助MySQL高效获取数据的排好序的数据结构，核心作用是减少磁盘IO次数，把随机IO转化为顺序IO。
很多人存在误区：把索引等同于表中的字段。实际上，索引是基于数据表的字段构建的排序数据结构，存储在磁盘的ibd文件中，其核心价值是通过排序结构，避免全表扫描，把O(n)的查询复杂度降低到O(logn)级别。
二、为什么MySQL InnoDB最终选择了B+树？
数据库的核心瓶颈是磁盘IO，所有数据结构的选型，最终都是为了减少IO次数。我们逐一对比主流数据结构的缺陷，就能明白B+树的不可替代性。
2.1 为什么不用二叉搜索树？
二叉搜索树的核心特性：左子树所有节点值小于父节点，右子树所有节点值大于父节点。 致命缺陷：极端场景下（比如顺序插入数据）会退化成单向链表，树高等于数据量。比如100万条数据，树高可达100万，查询需要100万次IO，完全无法满足性能要求。
2.2 为什么不用平衡二叉树/红黑树？
平衡二叉树（AVL）通过旋转保证左右子树高度差不超过1，红黑树通过弱平衡规则保证最长路径不超过最短路径的2倍，解决了二叉搜索树的退化问题。 核心缺陷：树高依然过高。即使是100万条数据，红黑树的树高也接近20，意味着一次查询需要20次磁盘IO，性能无法满足高并发场景。同时二叉树每个节点只能存储一个键值，完全浪费了磁盘预读（每次IO读取一页16KB数据）的特性。
2.3 为什么不用B树（平衡多路查找树）？
B树是多路平衡树，每个节点可以存储多个键值和数据，树高大幅降低。但它依然存在无法解决的缺陷：
- 非叶子节点存储了完整数据，导致每个节点能存储的索引键数量大幅减少，树高依然高于B+树；
- 范围查询需要多次中序遍历，跨节点IO次数多，不适合排序、分页等高频场景；
- 查询性能不稳定，部分查询在非叶子节点就能返回，部分需要遍历到叶子节点，不利于优化器稳定预估成本。
2.4 B+树的核心特性与绝对优势
B+树是专为数据库存储引擎设计的B树变种，也是InnoDB索引的底层数据结构，其核心特性完美适配数据库的查询场景：
- 非叶子节点只存索引键，不存数据：所有完整数据都存储在叶子节点，非叶子节点能存储的索引键数量大幅提升，树高极低。
- 叶子节点是有序双向链表：所有叶子节点按索引键升序排列，通过双向链表串联，范围查询、排序、分页操作只需遍历链表即可，性能极高。
- 所有查询最终都落到叶子节点：查询路径长度固定，性能稳定，便于优化器做成本预估。
B+树树高计算（核心干货）
InnoDB中，每个数据页的大小固定为16KB（操作系统页大小的整数倍，保证原子IO）：
- 非叶子节点：存储bigint类型主键（8字节）+ 子节点指针（6字节），单个键值对仅占14字节；
- 单个非叶子节点可存储的索引数量：16KB / 14B ≈ 1170个；
- 树高2层：可存储1170 * 16 = 18720行数据（按单行数据1KB计算）；
- 树高3层：可存储1170 * 1170 * 16 = 21902400行数据，也就是2千多万行数据，仅需3次IO即可完成查询，性能碾压其他数据结构。
三、InnoDB核心：聚簇索引与二级索引的底层机制
InnoDB是索引组织表，整张表的数据就是按照聚簇索引的B+树组织的，这是它和MyISAM引擎最核心的区别，也是所有索引优化的基础。
3.1 什么是聚簇索引？
聚簇索引是按照每张表的主键构建的B+树，其叶子节点直接存储了整张表的整行数据。聚簇索引既是索引，也是数据的存储方式，数据和主键索引是完全绑定在一起的。
聚簇索引的核心规则：
- 每张表有且只有一个聚簇索引；
- 若显式定义了主键，InnoDB会将主键索引作为聚簇索引；
- 若未定义主键，InnoDB会选择第一个唯一非空索引作为聚簇索引；
- 若既无主键也无唯一非空索引，InnoDB会隐式创建一个6字节的ROWID作为聚簇索引。
3.2 二级索引（辅助索引）的底层结构
二级索引是基于非主键字段构建的B+树，其核心特性和聚簇索引完全不同：
- 非叶子节点仅存储索引键值；
- 叶子节点不存储整行数据，仅存储对应的主键值。
这里有一个核心设计问题：为什么二级索引叶子节点存主键值，而不是数据行的物理地址？ 原因是聚簇索引会发生页分裂，数据行的物理地址会发生变化。如果二级索引存储物理地址，每次页分裂都需要更新所有二级索引的地址，维护成本极高。而存储主键值，页分裂不会影响二级索引，只需通过主键到聚簇索引中查找即可，这是InnoDB索引设计的核心巧思。
3.3 回表查询的完整流程
回表是InnoDB中最核心的概念：当二级索引无法覆盖查询所需的所有列时，需要通过二级索引叶子节点中的主键值，到聚簇索引中查找整行数据的过程，这个额外的IO操作就是回表，也是性能损耗的核心来源。
我们通过具体表结构和SQL示例，完整还原回表过程：
CREATE TABLE `sys_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` varchar(64) NOT NULL COMMENT '用户名',
`age` int NOT NULL COMMENT '年龄',
`gender` tinyint NOT NULL COMMENT '性别 1-男 2-女',
`phone` varchar(11) NOT NULL COMMENT '手机号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_phone` (`phone`),
KEY `idx_name_age` (`user_name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';
执行SQL：
SELECT * FROM sys_user WHERE user_name = '张三' AND age = 20;
完整执行流程：
- 遍历二级索引
idx_name_age的B+树，找到user_name='张三'且age=20的叶子节点，获取对应的主键id=1001； - 遍历聚簇索引的B+树，通过主键id=1001找到对应的整行数据，返回给客户端。
3.4 聚簇索引的主键设计黄金法则
- 必须显式定义主键：避免InnoDB隐式创建无法控制的ROWID，同时主键可用于查询和关联优化；
- 优先使用自增有序主键：推荐使用
bigint auto_increment，有序插入只会在最后一个数据页追加写入，不会触发页分裂，顺序IO性能极高； - 禁止使用UUID/无序字符串作为主键：无序插入会随机写入数据页中间位置，频繁触发页分裂，导致数据页碎片化，写入性能暴跌；同时UUID占用空间大，会导致所有二级索引体积膨胀，增加查询IO次数；
- 主键长度尽量小：二级索引叶子节点都会存储主键值，主键越短，二级索引体积越小，IO效率越高。
四、索引生效的核心规则与失效场景底层解析
很多人背了无数索引失效规则，却依然写不好SQL，核心原因是没有理解规则背后的底层逻辑。所有索引失效的本质，都是无法利用索引的排序结构，只能全表扫描。
4.1 最左前缀原则：索引生效的核心底层逻辑
最左前缀原则是联合索引的核心规则：MySQL联合索引会按照索引定义的字段顺序，从左到右匹配查询条件，遇到范围查询（>、<、between、like前缀匹配）就会停止匹配。
底层原因：联合索引的B+树是按照索引定义的字段顺序排序的——先按第一个字段排序，第一个字段值相同的情况下，再按第二个字段排序，以此类推。只有保证左边的字段是精确匹配，右边的字段才能利用索引的有序性。
我们以联合索引idx_name_age_create_time(user_name,age,create_time)为例，完整说明生效与失效场景：
生效场景
- 全值匹配：
WHERE user_name = '张三' AND age = 20 AND create_time = '2024-01-01'，三个字段全部生效，可通过explain的key_len字段验证； - 匹配最左N个字段：
WHERE user_name = '张三' AND age = 20，前两个字段生效； - 匹配最左前缀：
WHERE user_name = '张三'，第一个字段生效； - 匹配最左字段范围查询：
WHERE user_name BETWEEN '张三' AND '李四'，第一个字段生效。
失效场景
- 不匹配最左前缀：
WHERE age = 20 AND create_time = '2024-01-01'，完全无法使用索引； - 范围查询之后的字段：
WHERE user_name = '张三' AND age > 20 AND create_time = '2024-01-01'，仅user_name和age生效，create_time无法生效，因为范围查询后停止匹配； - 跳过中间字段：
WHERE user_name = '张三' AND create_time = '2024-01-01'，仅user_name生效，create_time无法生效。
4.2 索引失效的10大场景与底层原因
所有场景均基于MySQL 8.0验证，每个场景都附带可执行SQL与底层原理解析。
1. 对索引字段使用函数操作
-- 索引失效
EXPLAIN SELECT * FROM sys_user WHERE DATE(create_time) = '2024-01-01';
-- 索引生效
EXPLAIN SELECT * FROM sys_user WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59';
底层原因：索引存储的是字段的原始值，不是函数计算后的值。对索引字段使用函数后，MySQL无法匹配索引的排序结构，只能全表扫描。
2. 隐式类型转换
-- phone为varchar类型，查询条件为数字，触发隐式类型转换，索引失效
EXPLAIN SELECT * FROM sys_user WHERE phone = 13800138000;
-- 类型匹配，索引生效
EXPLAIN SELECT * FROM sys_user WHERE phone = '13800138000';
底层原因：当查询条件的字段类型和索引字段类型不一致时，MySQL会对索引字段做隐式类型转换，相当于对索引字段使用了函数，导致索引失效。 注意：若索引字段为int类型，查询条件为varchar类型，MySQL会对查询条件做转换，不会对索引字段操作，索引不会失效。
3. 模糊查询以%开头
-- %开头，无法匹配前缀，索引失效
EXPLAIN SELECT * FROM sys_user WHERE user_name LIKE '%张三';
-- %结尾，前缀匹配，索引生效
EXPLAIN SELECT * FROM sys_user WHERE user_name LIKE '张三%';
底层原因：联合索引是按字段前缀有序排列的，%开头的模糊查询无法匹配前缀，只能全表扫描。若需前后模糊匹配，推荐使用MySQL全文索引，而非like语句。
4. 使用OR连接非索引字段
-- gender无索引，OR连接后索引失效
EXPLAIN SELECT * FROM sys_user WHERE user_name = '张三' OR gender = 1;
-- 两个字段均有索引，触发索引合并，索引生效
EXPLAIN SELECT * FROM sys_user WHERE user_name = '张三' OR phone = '13800138000';
底层原因：OR连接的条件中，只要有一个字段没有索引，MySQL就无法通过索引匹配所有条件，只能全表扫描。
5. 索引字段参与算术运算
-- 对索引字段做算术运算，索引失效
EXPLAIN SELECT * FROM sys_user WHERE age + 1 = 21;
-- 索引生效
EXPLAIN SELECT * FROM sys_user WHERE age = 20;
底层原因：和函数操作一致，对索引字段做算术运算后，无法匹配索引的原始值，导致索引失效。
6. join关联字段类型/字符集不一致
两张表join时，若关联字段的类型、字符集不一致，会触发隐式类型转换，导致索引失效，这是生产环境中最常见的隐藏坑。
7. 索引区分度过低
区分度计算公式：COUNT(DISTINCT 字段)/COUNT(*)，值越接近1，区分度越高。 比如gender字段只有1、2两个值，区分度极低，MySQL认为全表扫描比索引查询成本更低，不会使用索引。
8. MySQL优化器选错索引
当表中有多个索引时，MySQL优化器会基于成本预估选择索引，可能出现选错索引的情况。可通过force index强制指定索引，但需谨慎使用。
9. 使用不等于（!=、<>）、NOT IN、IS NOT NULL
这类操作不是绝对失效，但绝大多数场景下，MySQL会认为全表扫描成本更低，放弃使用索引。
10. 冗余与无效的索引设计
比如已有联合索引idx_a_b(a,b)，再单独建索引idx_a(a)，属于冗余索引，不会提升查询性能，只会增加写入负担。
五、生产级索引优化实战指南
所有优化方案均基于底层原理设计，可直接落地到生产环境，解决高频性能问题。
5.1 覆盖索引：彻底消除回表
覆盖索引是性能优化的第一利器：查询的所有列，都包含在索引中，无需回表查询，性能可提升数十倍。
-- 回表查询，需到聚簇索引获取完整数据
EXPLAIN SELECT * FROM sys_user WHERE user_name = '张三' AND age = 20;
-- 覆盖索引，无需回表，Extra字段显示Using index
EXPLAIN SELECT id,user_name,age FROM sys_user WHERE user_name = '张三' AND age = 20;
底层原理：二级索引idx_name_age的叶子节点已经包含了user_name、age、主键id，查询的列全部在索引中，可直接返回，无需回表。Using index是覆盖索引的核心标志。
生产最佳实践：
- 禁止使用
SELECT *，只查询业务需要的字段，更容易实现覆盖索引； - 设计联合索引时，将业务需要返回的字段加入索引，实现覆盖索引。
5.2 联合索引设计黄金法则
- 等值查询优先：将等值查询的字段放在联合索引前面，范围查询的字段放在后面；
- 高区分度优先：区分度高的字段放在前面，可快速过滤掉大部分数据；
- 高频查询优先：将频繁查询的字段放在前面，让更多查询能复用索引；
- 覆盖索引优先：将需要返回的字段加入索引，避免回表。
示例：业务高频查询WHERE user_name = ? AND age > ?，需要返回user_name、age、phone。 设计联合索引：idx_name_age_phone(user_name,age,phone)，符合最左前缀原则，等值查询字段在前，范围查询在后，同时实现覆盖索引，无需回表。
5.3 大偏移量分页查询优化
传统分页SELECT * FROM sys_user LIMIT 100000,20在偏移量极大时性能极差，因为MySQL需要扫描100020行数据，再丢弃前100000行。
优化方案1：主键覆盖优化
SELECT * FROM sys_user
WHERE id >= (SELECT id FROM sys_user ORDER BY id LIMIT 100000,1)
LIMIT 20;
原理：子查询通过覆盖索引快速找到偏移量对应的主键id，再通过主键范围查询，仅需扫描20行数据，性能提升上百倍。
优化方案2：游标分页（推荐用于APP下拉加载）
SELECT * FROM sys_user
WHERE id > 100000
ORDER BY id LIMIT 20;
原理：记录上一页最后一条数据的主键id，下一页通过id > 上一页id查询，完全避免大偏移量，性能稳定。
5.4 Order By/Group By优化
核心原则：利用索引的有序性，避免Using filesort文件排序。
示例：联合索引idx_name_age(user_name,age)
-- 利用索引有序性，无文件排序
EXPLAIN SELECT user_name,age FROM sys_user WHERE user_name = '张三' ORDER BY age;
-- 符合索引顺序，无文件排序
EXPLAIN SELECT user_name,age FROM sys_user ORDER BY user_name,age;
-- 顺序相反，触发文件排序
EXPLAIN SELECT user_name,age FROM sys_user ORDER BY age,user_name;
MySQL 8.0支持降序索引，可解决正反序排序的文件排序问题：
-- 创建降序索引
CREATE INDEX idx_name_asc_age_desc ON sys_user(user_name ASC, age DESC);
-- 不再触发文件排序
EXPLAIN SELECT user_name,age FROM sys_user ORDER BY user_name ASC, age DESC;
5.5 索引条件下推（ICP）优化
ICP是MySQL 5.6引入的优化特性，默认开启，核心作用是在存储引擎层遍历索引时，直接对索引中包含的字段进行过滤，减少回表次数和IO次数。
示例：联合索引idx_name_age(user_name,age)
SELECT * FROM sys_user WHERE user_name LIKE '张%' AND age = 20;
- 无ICP：存储引擎通过
user_name LIKE '张%'找到所有匹配的索引，全部回表，再在server层过滤age=20的记录； - 有ICP：存储引擎遍历索引时，直接过滤
age=20的记录，仅对符合条件的记录回表，大幅减少回表次数。
Using index condition是ICP开启的核心标志。
六、索引设计最佳实践与避坑指南
- 控制索引数量：单表索引数量控制在5个以内，过多的索引会导致插入、更新、删除时需要同步维护多个B+树，写入性能暴跌，同时占用大量磁盘空间。
- 小表无需建索引：数据量小于1000行的表，全表扫描成本低于索引查询，无需建索引。
- 低区分度字段不单独建索引：性别、状态等枚举值少的字段，单独建索引无意义，可和其他字段组成联合索引。
- 避免冗余索引：已有联合索引
idx_a_b(a,b)，无需再建idx_a(a)，联合索引已支持最左前缀匹配。 - 更新频繁的字段少建索引：字段更新时需要同步更新索引，频繁更新会导致索引维护成本极高。
- 定期清理无用索引：通过
sys.schema_unused_indexes查看数据库启动后未使用的索引，定期清理，减少数据库负担。 - 批量导入数据优化：导入大量数据前，可先关闭非唯一索引的更新，导入完成后再重建，大幅提升导入性能。
七、Java业务层索引优化实战代码
核心依赖配置
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.2.4</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-spring-boot3-starter</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.32</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springdoc</groupId>
<artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
<version>2.5.0</version>
</dependency>
<dependency>
<groupId>com.alibaba.fastjson2</groupId>
<artifactId>fastjson2</artifactId>
<version>2.0.49</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>32.1.3-jre</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
</dependencies>
实体类定义
package com.jam.demo.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import java.time.LocalDateTime;
/**
* 用户实体类
*
* @author ken
*/
@Data
@TableName("sys_user")
@Schema(description = "用户信息实体")
public class SysUser {
@TableId(type = IdType.AUTO)
@Schema(description = "主键ID", example = "1")
private Long id;
@Schema(description = "用户名", example = "张三")
private String userName;
@Schema(description = "年龄", example = "20")
private Integer age;
@Schema(description = "性别 1-男 2-女", example = "1")
private Integer gender;
@Schema(description = "手机号", example = "13800138000")
private String phone;
@Schema(description = "创建时间")
private LocalDateTime createTime;
@Schema(description = "更新时间")
private LocalDateTime updateTime;
}
Mapper层定义
package com.jam.demo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jam.demo.entity.SysUser;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* 用户Mapper接口
*
* @author ken
*/
public interface SysUserMapper extends BaseMapper<SysUser> {
/**
* 游标分页查询用户列表
*
* @param startId 起始主键ID
* @param limit 分页条数
* @return 用户列表
*/
List<SysUser> selectPageByCursor(@Param("startId") Long startId, @Param("limit") Integer limit);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jam.demo.mapper.SysUserMapper">
<select id="selectPageByCursor" resultType="com.jam.demo.entity.SysUser">
SELECT id, user_name, age, phone FROM sys_user
WHERE id > #{startId}
ORDER BY id ASC
LIMIT #{limit}
</select>
</mapper>
VO类定义
package com.jam.demo.vo;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
/**
* 用户查询VO
*
* @author ken
*/
@Data
@Schema(description = "用户查询参数")
public class UserQueryVO {
@Schema(description = "上一页最后一条数据的主键ID", example = "100000")
private Long lastId;
@Schema(description = "每页条数", example = "20")
private Integer pageSize;
}
package com.jam.demo.vo;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
/**
* 用户响应VO
*
* @author ken
*/
@Data
@Schema(description = "用户响应信息")
public class UserRespVO {
@Schema(description = "主键ID", example = "1")
private Long id;
@Schema(description = "用户名", example = "张三")
private String userName;
@Schema(description = "年龄", example = "20")
private Integer age;
@Schema(description = "手机号", example = "13800138000")
private String phone;
}
Service层定义
package com.jam.demo.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.jam.demo.entity.SysUser;
import com.jam.demo.vo.UserQueryVO;
import com.jam.demo.vo.UserRespVO;
import java.util.List;
/**
* 用户服务接口
*
* @author ken
*/
public interface SysUserService extends IService<SysUser> {
/**
* 根据用户名和年龄查询用户信息
*
* @param userName 用户名
* @param age 年龄
* @return 用户响应VO
*/
UserRespVO getUserByNameAndAge(String userName, Integer age);
/**
* 游标分页查询用户列表
*
* @param queryVO 查询参数
* @return 用户列表
*/
List<UserRespVO> getUserListByCursor(UserQueryVO queryVO);
/**
* 批量新增用户
*
* @param userList 用户列表
* @return 新增结果
*/
Boolean batchAddUser(List<SysUser> userList);
}
package com.jam.demo.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.jam.demo.entity.SysUser;
import com.jam.demo.mapper.SysUserMapper;
import com.jam.demo.service.SysUserService;
import com.jam.demo.vo.UserQueryVO;
import com.jam.demo.vo.UserRespVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import jakarta.annotation.Resource;
import java.util.List;
/**
* 用户服务实现类
*
* @author ken
*/
@Slf4j
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements SysUserService {
@Resource
private SysUserMapper sysUserMapper;
@Resource
private TransactionTemplate transactionTemplate;
@Override
public UserRespVO getUserByNameAndAge(String userName, Integer age) {
if (!StringUtils.hasText(userName)) {
log.warn("用户名不能为空");
return null;
}
if (ObjectUtils.isEmpty(age)) {
log.warn("年龄不能为空");
return null;
}
LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<SysUser>()
.select(SysUser::getId, SysUser::getUserName, SysUser::getAge, SysUser::getPhone)
.eq(SysUser::getUserName, userName)
.eq(SysUser::getAge, age);
SysUser sysUser = sysUserMapper.selectOne(queryWrapper);
if (ObjectUtils.isEmpty(sysUser)) {
log.info("未查询到用户信息，userName:{}, age:{}", userName, age);
return null;
}
UserRespVO respVO = new UserRespVO();
BeanUtils.copyProperties(sysUser, respVO);
return respVO;
}
@Override
public List<UserRespVO> getUserListByCursor(UserQueryVO queryVO) {
if (ObjectUtils.isEmpty(queryVO)) {
return Lists.newArrayList();
}
Long startId = ObjectUtils.isEmpty(queryVO.getLastId()) ? 0L : queryVO.getLastId();
Integer limit = ObjectUtils.isEmpty(queryVO.getPageSize()) ? 20 : queryVO.getPageSize();
List<SysUser> userList = sysUserMapper.selectPageByCursor(startId, limit);
if (CollectionUtils.isEmpty(userList)) {
return Lists.newArrayList();
}
List<UserRespVO> respList = Lists.newArrayListWithCapacity(userList.size());
for (SysUser user : userList) {
UserRespVO respVO = new UserRespVO();
BeanUtils.copyProperties(user, respVO);
respList.add(respVO);
}
return respList;
}
@Override
public Boolean batchAddUser(List<SysUser> userList) {
if (CollectionUtils.isEmpty(userList)) {
return Boolean.FALSE;
}
return transactionTemplate.execute(new TransactionCallback<Boolean>() {
@Override
public Boolean doInTransaction(TransactionStatus status) {
try {
boolean saveResult = saveBatch(userList, 1000);
if (!saveResult) {
status.setRollbackOnly();
log.error("批量新增用户失败");
return Boolean.FALSE;
}
log.info("批量新增用户成功，数量:{}", userList.size());
return Boolean.TRUE;
} catch (Exception e) {
status.setRollbackOnly();
log.error("批量新增用户异常", e);
return Boolean.FALSE;
}
}
});
}
}
Controller层定义
package com.jam.demo.controller;
import com.jam.demo.entity.SysUser;
import com.jam.demo.service.SysUserService;
import com.jam.demo.vo.UserQueryVO;
import com.jam.demo.vo.UserRespVO;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
import jakarta.annotation.Resource;
import java.util.List;
/**
* 用户控制器
*
* @author ken
*/
@RestController
@RequestMapping("/user")
@Tag(name = "用户管理", description = "用户信息相关接口")
public class SysUserController {
@Resource
private SysUserService sysUserService;
@GetMapping("/getByNameAndAge")
@Operation(summary = "根据用户名和年龄查询用户", description = "使用覆盖索引优化，避免回表")
public UserRespVO getUserByNameAndAge(
@Parameter(description = "用户名", required = true) @RequestParam String userName,
@Parameter(description = "年龄", required = true) @RequestParam Integer age) {
return sysUserService.getUserByNameAndAge(userName, age);
}
@PostMapping("/listByCursor")
@Operation(summary = "游标分页查询用户列表", description = "优化大偏移量分页查询性能")
public List<UserRespVO> getUserListByCursor(@RequestBody UserQueryVO queryVO) {
return sysUserService.getUserListByCursor(queryVO);
}
@PostMapping("/batchAdd")
@Operation(summary = "批量新增用户", description = "批量插入数据，优化索引写入性能")
public Boolean batchAddUser(@RequestBody List<SysUser> userList) {
if (CollectionUtils.isEmpty(userList)) {
return Boolean.FALSE;
}
return sysUserService.batchAddUser(userList);
}
}
写在最后
MySQL索引优化的核心，从来不是死记硬背各种规则，而是彻底理解底层的B+树结构、聚簇索引的组织方式，以及MySQL优化器的执行逻辑。所有的优化方案，都是基于底层原理推导出来的。只有从根上搞懂了这些，才能面对任何复杂的SQL场景，都能写出高性能的查询语句，解决生产环境的各种性能问题。
