Mysql数据库索引

没腿的鸟 · 2025-1-16 16:51:01

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

x

Spring Data JPA创建索引所利用的语法

@Entity
@Table(
name = "user",
indexes = {
@Index(name = "idx_user_username", columnList = "username"),
@Index(name = "idx_user_email_status", columnList = "email, status")
},
uniqueConstraints = {
@UniqueConstraint(name = "uk_user_email_username", columnNames = {"email", "username"})
}
)
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String username;
private String email;
private String status;
// ... 其他字段
}

复制代码

@Table(name = "user")

指定实体类对应的数据库表名为 user。
假如不写 name，默认会根据实体类名推断（比如类名叫 User 就会对应 user 表或 User 表，具体与 JPA 实现有关）。

indexes = { ... }

这里可以声明一个或多个普通索引（或非唯一索引）。

indexes = {
@Index(name = "idx_user_username", columnList = "username"),
@Index(name = "idx_user_email_status", columnList = "email, status")
}

复制代码

@Index(name = "idx_user_username", columnList = "username")
- 代表在 username 字段上创建一个 普通索引，索引名为 idx_user_username。
- 这样在数据库层面会自动执行雷同 CREATE INDEX idx_user_username ON user(username);
  
  的操纵。
@Index(name = "idx_user_email_status", columnList = "email, status")
- 代表在 email 和 status 这两个字段上创建一个团结索引（复合索引），索引名为 idx_user_email_status。
- 在数据库层面大致对应 CREATE INDEX idx_user_email_status ON user(email, status);
  。

注意：这些索引默认不是唯一索引。假如你没有在注解里指定 unique = true，JPA 会把它当作普通索引创建。

uniqueConstraints = { ... }

用来指定唯一性约束，可以是针对一个或多个列。

uniqueConstraints = {
@UniqueConstraint(name = "uk_user_email_username", columnNames = {"email", "username"})
}

复制代码

表现在 email + username 这两个字段上创建一个团结唯一约束，约束名为 uk_user_email_username。
数据库层面上会生成雷同 ALTER TABLE user ADD CONSTRAINT uk_user_email_username UNIQUE(email, username); 的语句（具体实现可能因数据库和 JPA 实现不同而略有差异）。
该约束可以确保同一张表中，不会出现 email 和 username 的组合相同的两行数据。

区别于 indexes：

uniqueConstraints 侧重的是唯一性约束。它不光会在数据库中创建唯一索引，还会在表层面创建“约束”关系，不答应重复。
indexes 只是声明一个非唯一索引（除非加 unique=true），主要用于加快查询，不会强制数据唯一。

@Column(unique = true)

假如你只想创建唯一索引（不肯定是多字段），也可以直接在字段上用 @Column(unique = true)：

@Column(unique = true)
private String email;

复制代码

这样会自动生成一个单字段唯一索引（约束）

一、按「数据结构」分类

1.1 B+Tree 索引

1.1.1 特点与应用场景

特点：
- B+Tree 是 MySQL(InnoDB/MyISAM 等多数引擎) 中最常用的索引结构。
- 支持等值查询和范围查询 (BETWEEN, <, >, ORDER BY, 等)。
- 查询性能稳定，适合多数增删改查需求。
与其他分类的交叉：
- 物理存储：InnoDB 中的主键索引（聚簇索引）和二级索引都是 B+Tree。
- 字段特性：主键、唯一、普通、前缀索引都可由 B+Tree 来实现。
- 字段个数：可做单列或团结索引，底层都是 B+Tree。

1.1.2 Spring Data JPA 中如何利用 B+Tree 索引

在 InnoDB 引擎下，所有主键、唯一索引、普通索引默认就是 B+Tree 实现。只要我们在实体类里用以下方式指定索引，即可默认得到 B+Tree：
主键索引（B+Tree + 聚簇）

@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
// ...
}

复制代码

当表是 InnoDB 时，主键索引就是聚簇索引，底层是 B+Tree。

唯一索引（B+Tree 二级索引）

@Column(unique = true)
private String email;

复制代码

这样会自动为 email 字段创建一个唯一索引，同样是 B+Tree。

普通索引（B+Tree 二级索引）

@Table(
indexes = {
@Index(name = "idx_user_username", columnList = "username")
}
)
@Entity
public class User {
@Id
private Long id;
private String username;
// ...
}

复制代码

利用 @Index 注解在 @Table 中声明普通索引，底层也是 B+Tree。

团结索引（B+Tree）

@Table(
indexes = {
@Index(name = "idx_user_email_status", columnList = "email, status")
}
)

复制代码

声明复合（团结）索引时，依然是 B+Tree。

小结：在 Spring Data JPA 里，正常利用 @Id/@Column(unique=true)/@Table(indexes=...) 就已经默认是 B+Tree，无需额外指定。
1.1.3 MyBatis-Plus 中如何利用 B+Tree 索引

MyBatis-Plus 并没有像 JPA 那样的注解去自动创建索引。常见做法有两种：
在数据库脚本或 Flyway/Liquibase 中显式创建

CREATE TABLE user (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100),
email VARCHAR(100),
status VARCHAR(20)
-- ...
) ENGINE=InnoDB;
-- 普通索引
CREATE INDEX idx_user_username ON user(username);
-- 唯一索引
CREATE UNIQUE INDEX uk_user_email ON user(email);
-- 联合索引
CREATE INDEX idx_user_email_status ON user(email, status);

复制代码

只要引擎是 InnoDB/MyISAM，默认索引结构即 B+Tree。

在 Mapper 接口中执行原生 SQL

@Mapper
public interface UserMapper extends BaseMapper<User> {
@Select("CREATE INDEX idx_user_username ON user(username)")
void createIndexOnUsername();
@Select("CREATE UNIQUE INDEX uk_user_email ON user(email)")
void createUniqueIndexOnEmail();
@Select("CREATE INDEX idx_user_email_status ON user(email, status)")
void createIndexOnEmailStatus();
}

复制代码

这会在运行时执行建索引的 SQL，底层同样是 B+Tree（只要数据引擎是 InnoDB/MyISAM）。

1.2 Hash 索引

1.2.1 特点与应用场景

特点：
- 基于哈希表，只适合等值查询，不支持范围查询、排序等。
- 只在 MEMORY 存储引擎中可用（InnoDB 并不支持手动创建 Hash 索引）。
- 在生产情况中不常见，除非有少少量、频繁等值查询的数据。

1.2.2 Spring Data JPA 中的操纵

Spring Data JPA 没有专门的注解来指定 “Hash 索引”。
假如肯定要在一个 MEMORY 表上创建 Hash 索引，需要先将表改成 MEMORY 引擎，再用原生 SQL 创建：

public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "ALTER TABLE user ENGINE=MEMORY", nativeQuery = true)
@Modifying
void convertToMemory();
@Query(value = "ALTER TABLE user ADD KEY USING HASH (username)", nativeQuery = true)
@Modifying
void createHashIndex();
}

复制代码

这种情况十分少见，一样平常不做推荐。

1.2.3 MyBatis-Plus 中的操纵

同样，需要先把表引擎改成 MEMORY，然后再执行建索引语句：

@Mapper
public interface UserMapper extends BaseMapper<User> {
@Select("ALTER TABLE user ENGINE=MEMORY")
void convertToMemoryEngine();
@Select("ALTER TABLE user ADD KEY USING HASH (username)")
void createHashIndexOnUsername();
}

复制代码

由于场景少少，建议仅在极端情况下利用。

1.3 Full-Text 索引

1.3.1 特点与应用场景

特点：
- 用于对文本字段（CHAR、VARCHAR、TEXT）举行全文检索。
- MyISAM 引擎最早支持，InnoDB 在 5.6+ 版本也支持。
- 需要共同 MATCH ... AGAINST 语法来举行全文搜索。

1.3.2 Spring Data JPA 中的操纵

Spring Data JPA 没有“自动”创建全文索引的注解。
需要利用原生 SQL 来创建：
1. public interface ArticleRepository extends JpaRepository<Article, Long> {
3. @Query(value = "ALTER TABLE article ADD FULLTEXT INDEX idx_content(content)", nativeQuery = true)
4. @Modifying
5. void createFullTextIndexOnContent();
6. }
复制代码
然后查询时：
1. @Query(value = "SELECT * FROM article WHERE MATCH(content) AGAINST(?1 IN NATURAL LANGUAGE MODE)", nativeQuery = true)
2. List<Article> searchByContent(String keywords);
复制代码

1.3.3 MyBatis-Plus 中的操纵

同理，在 MyBatis-Plus 中也需执行原生 SQL：

@Mapper
public interface ArticleMapper extends BaseMapper<Article> {
@Select("ALTER TABLE article ADD FULLTEXT INDEX idx_content(content)")
void createFullTextIndex();
@Select("SELECT * FROM article WHERE MATCH(content) AGAINST(#{keywords} IN NATURAL LANGUAGE MODE)")
List<Article> searchByContent(String keywords);
}

复制代码

二、按「物理存储」分类

2.1 聚簇索引（Clustered Index / 主键索引）

2.1.1 特点与应用场景

InnoDB 中的主键索引即为聚簇索引
数据文件按主键顺序构造存储，主键索引本身就是一棵 B+Tree，叶子节点存的就是整行数据。
与其他分类维度的交叉
- 若显式声明白主键 (PRIMARY KEY)，那么这就是表的聚簇索引。
- 假如主键是单列，则它既是单列索引；假如主键由多列构成，则是团结索引。
- 除了主键索引外，其他都是「二级索引」。

2.1.2 Spring Data JPA 中的操纵

只要你在实体类中通过 @Id 声明主键，并利用 InnoDB 引擎，就会自动成为聚簇索引（无需额外设置）：
1. @Entity
2. @Table(name = "user")
3. public class User {
4. @Id
5. @GeneratedValue(strategy = GenerationType.IDENTITY)
6. private Long id; // InnoDB主键 => 聚簇索引
8. private String username;
9. // ...
10. }
复制代码
假如是团结主键，也依旧是聚簇索引，比如利用 @IdClass 或 @EmbeddedId。

2.1.3 MyBatis-Plus 中的操纵

MyBatis-Plus 并不“帮”你创建表或索引，只要你在数据库里指定：
1. CREATE TABLE user (
2. id BIGINT PRIMARY KEY AUTO_INCREMENT,
3. username VARCHAR(100),
4. ...
5. ) ENGINE=InnoDB;
复制代码
- 这时 id 就是聚簇索引。
在实体类中也会有对应的 @TableId 标识主键，但它并不自动建索引，而是依靠数据库表本身：
1. @TableName("user")
2. public class User {
3. @TableId(type = IdType.AUTO)
4. private Long id;
5. private String username;
6. // ...
7. }
复制代码

2.2 二级索引（Secondary Index / 辅助索引）

2.2.1 特点与应用场景

辅助索引
- 在 InnoDB 中，非主键的索引（唯一索引、普通索引等）都属于二级索引。
- 二级索引是一棵独立的 B+Tree，叶子节点存的是对应行的「主键值」，需要再回表到聚簇索引查数据。
与其他分类维度的交叉
- 可以是唯一索引或普通索引，也可以是单列或团结，底层都是 B+Tree 结构。
- 比如 CREATE UNIQUE INDEX、CREATE INDEX 都属于创建二级索引。
二级索引的叶子节点存储的是主键值，然后通过主键值再去聚簇索引定位行数据（回表）。

2.2.2 Spring Data JPA 中的操纵

唯一索引、普通索引 都是二级索引（只要不是主键）。
示例（普通索引）：
1. @Table(
2. name = "user",
3. indexes = {
4. @Index(name = "idx_user_username", columnList = "username")
5. }
6. )
7. @Entity
8. public class User {
9. @Id
10. private Long id; // 主键(聚簇)
12. private String username; // 二级索引
13. // ...
14. }
复制代码
- 这里 idx_user_username 就是一个二级索引。

2.2.3 MyBatis-Plus 中的操纵

同样需要手动写 SQL 或通过数据库脚本：
1. CREATE INDEX idx_user_username ON user(username);
复制代码
在 MyBatis-Plus 的实体中并没有注解去标明“这是二级索引”，只能说非主键的索引默认就是二级索引（InnoDB）：
1. @Select("CREATE INDEX idx_user_username ON user(username)")
2. void createIndexUsername();
复制代码

三、按「字段特性」分类

3.1 主键索引

3.1.1 特点与应用场景

唯一且不能为空
- 一张表只能有一个主键索引，它在 InnoDB 中就是聚簇索引。
与其他分类维度的交叉
- 底层数据结构：B+Tree；
- 也是聚簇索引；
- 可以是单列或多列（团结主键）。
主键不能为空，通常自增或 UUID 等。

3.1.2 Spring Data JPA 中的操纵

直接 @Id 便是主键索引：
1. @Id
2. @GeneratedValue(strategy = GenerationType.IDENTITY)
3. private Long id;
复制代码
或者在表层面指定：
1. @Entity
2. @Table(name = "user")
3. public class User {
4. @Id
5. private Long id;
6. // ...
7. }
复制代码

3.1.3 MyBatis-Plus 中的操纵

数据库里界说 PRIMARY KEY：
1. CREATE TABLE user (
2. id BIGINT PRIMARY KEY AUTO_INCREMENT,
3. ...
4. ) ENGINE=InnoDB;
复制代码
实体类：
1. @TableId(type = IdType.AUTO)
2. private Long id;
复制代码
就完成了主键索引的声明。

3.2 唯一索引

3.2.1 特点与应用场景

包管字段值不重复
- 答应多列一起唯一，也答应为 NULL（有些场景多个 NULL 可视为不冲突）。
与其他分类维度的交叉
- 底层通常是 B+Tree；
- 若非主键，则它就是一个「二级索引」。
- 可以是单列或团结索引。

3.2.2 Spring Data JPA 中的操纵

单列唯一：

@Column(unique = true)
private String email;

复制代码

这会自动生成一个唯一索引。

团结唯一：

@Table(
uniqueConstraints = {
@UniqueConstraint(name = "uk_user_email_username", columnNames = {"email", "username"})
}
)
@Entity
public class User {
@Id
private Long id;
private String email;
private String username;
}

复制代码

会在 (email, username) 上创建一个唯一索引。

3.2.3 MyBatis-Plus 中的操纵

同样需要手工或脚本：
1. CREATE UNIQUE INDEX uk_user_email ON user(email);
2. CREATE UNIQUE INDEX uk_user_email_username ON user(email, username);
复制代码
或在 Mapper 接口中：
1. @Select("CREATE UNIQUE INDEX uk_user_email ON user(email)")
2. void createUniqueIndexEmail();
4. @Select("CREATE UNIQUE INDEX uk_user_email_username ON user(email, username)")
5. void createUniqueIndexEmailUsername();
复制代码

3.3 普通索引

3.3.1 特点与应用场景

最常见，提升查询速度，没有唯一性约束。
可用于经常在 WHERE 条件或 JOIN 条件中的列。

3.3.2 Spring Data JPA 中的操纵

通过 @Table(indexes = {...})：
1. @Table(
2. name = "user",
3. indexes = {
4. @Index(name = "idx_user_username", columnList = "username")
5. }
6. )
7. @Entity
8. public class User {
9. @Id
10. private Long id;
12. private String username;
13. // ...
14. }
复制代码

3.3.3 MyBatis-Plus 中的操纵

还是手动 SQL：
1. CREATE INDEX idx_user_username ON user(username);
复制代码
或者在 Mapper 接口：
1. @Select("CREATE INDEX idx_user_username ON user(username)")
2. void createIndexUsername();
复制代码

3.4 前缀索引

3.4.1 特点与应用场景

对字符串范例的前 N 个字符创建索引，减少索引体积，适合超长字段。
会降低搜索精度（可能会有更多回表操纵），仅在部分情况下利用。

3.4.2 Spring Data JPA 中的操纵

没有注解可指定 “前缀长度”，需要原生 SQL 或 DDL 脚本：
1. public interface UserRepository extends JpaRepository<User, Long> {
3. @Query(value = "CREATE INDEX idx_prefix_username ON user (username(10))", nativeQuery = true)
4. @Modifying
5. void createPrefixIndex();
6. }
复制代码
或者在建表脚本里提前写：
1. CREATE INDEX idx_prefix_username ON user (username(10));
复制代码

3.4.3 MyBatis-Plus 中的操纵

同理，原生 SQL：
1. @Select("CREATE INDEX idx_prefix_username ON user(username(10))")
2. void createPrefixIndex();
复制代码
或者在数据库层面界说。

四、按「字段个数」分类

4.1 单列索引

4.1.1 特点与应用场景

针对单个字段创建

可以是主键索引、唯一索引或普通索引。
一张表可以有多个不同字段的单列索引。

4.1.2 Spring Data JPA 中的操纵

常见写法：
1. @Table(
2. indexes = @Index(name="idx_user_username", columnList="username")
3. )
4. @Entity
5. public class User {
6. @Id
7. private Long id;
9. // username 单列索引
10. private String username;
11. }
复制代码

4.1.3 MyBatis-Plus 中的操纵

依旧SQL 或 Mapper：
1. @Select("CREATE INDEX idx_user_username ON user(username)")
2. void createIndexUsername();
复制代码

4.2 团结索引（复合索引）

4.2.1 特点与应用场景

针对多个字段的组合创建

可以是团结主键索引、团结唯一索引或普通团结索引。
在团结索引中，需要注意“最左前缀原则”，即只有在按“索引最左字段”举行查询时，索引才会被有用利用。

4.2.2 Spring Data JPA 中的操纵

在 @Index 的 columnList 中写多个字段：
1. @Table(
2. indexes = {
3. @Index(name = "idx_user_email_status", columnList = "email, status")
4. }
5. )
6. @Entity
7. public class User {
8. @Id
9. private Long id;
10. private String email;
11. private String status;
12. // ...
13. }
复制代码
或在 @UniqueConstraint(columnNames = {"col1", "col2"}) 中声明团结唯一。

最左前缀原则：从左到右依次匹配索引列，中间不能跳过；一旦遇到范围查询或被跳过的列，后续列就无法继续利用团结索引举行快速定位。因此，在筹划团结索引时，要结合最常用的查询方式，把过滤频率最高或最常作为等值过滤的列放到最左，才能最大化利用索引加快查询。

CREATE INDEX idx_col1_col2_col3 ON table_name(col1, col2, col3);

复制代码

必须从索引的最左列开始匹配
- 以上例中，最左列是 col1。假如在查询时没有利用 col1 举行过滤或毗连条件，那么即便条件中包含了 col2、col3，也无法有用利用这个团结索引。
- 例如，WHERE col1 = ... 可以用索引；WHERE col1 = ... AND col2 = ... 可以继续利用索引更深条理的匹配；但假如只是 WHERE col2 = ...，MySQL 无法利用 idx_col1_col2_col3 的第一列 col1 举行定位，就不能走该索引的高效检索。
索引可部分匹配，但只能持续到出现“断裂”
- 对团结索引 (col1, col2, col3) 来说，假如查询只利用了 col1，MySQL 仍能利用索引里 (col1) 这部分来加快查询。
- 假如查询中利用了 col1 和 col2，但忽略了 col3，那么 MySQL 可以利用索引的前两列 (col1, col2) 举行匹配。
- 假如跳过了中间一列，比如只用 col1 和 col3（而 col2 不出现在查询条件中），那么对于 col3 的过滤很难直接走这条索引，因为在索引排序里 col2 位于 col1 和 col3 中间，造成“断裂”。
对范围条件的影响
- 若在 col1 上利用了范围查询（如 col1 BETWEEN 10 AND 20），则只能对 col1 举行索引检索，并且无法再对后续 col2、col3 继续利用索引精准匹配。因为范围扫描一旦开始，后续的精准匹配就无法发挥作用。
- 这也是最左前缀原则的延伸：团结索引后续字段的利用，每每需要前一列是等值匹配（=）才能继续深度利用。

4.2.3 MyBatis-Plus 中的操纵

还是原生 SQL：
1. CREATE INDEX idx_user_email_status ON user(email, status);
复制代码
或者：
1. @Select("CREATE INDEX idx_user_email_status ON user(email, status)")
2. void createUnionIndexOnEmailStatus();
复制代码

五、小结

实际开发中最常用的索引：
- B+Tree 实现的主键索引、唯一索引、普通索引和团结索引。这基本覆盖了 90%+ 的应用场景。
- 在 Spring Data JPA 中，利用 @Id、@Column(unique=true)、@Table(indexes=..., uniqueConstraints=...) 已能满足绝大部分需求。
- 在 MyBatis-Plus 中，多利用数据库脚本或 Mapper 接口原生 SQL 来管理索引。
Hash 索引
- 因为只能在 MEMORY 引擎下利用，而且限制许多（不支持范围查询、无法排序等），生产中少少利用。除非需要一些极端高并发且数据量很小、仅仅等值查询的场景。
Full-Text 索引
- 假如需要“搜索功能”，可以思量 MySQL 自带全文索引。但通常更推荐利用专业搜索引擎（如 Elasticsearch、Solr 等），在索引和查询性能上更强大。
- 假如只是简单的搜索，可以用 MySQL Full-Text 索引，但要掌握 MATCH ... AGAINST 的利用和各种搜索模式。
聚簇索引 = InnoDB 主键索引，只有一个；二级索引 = 非主键索引（可唯一可普通可团结）。
主键、唯一、普通、前缀 这四类索引可以恣意组合到单列或多列中，但前缀索引只对字符串列有用。
Spring Data JPA 提供了一些方便的注解：
- @Id / @Column(unique=true) / @Table(indexes=..., uniqueConstraints=...)
- 特殊索引（Hash、Full-Text、前缀）要用原生 SQL。
MyBatis-Plus 不会自动创建索引，需要：
- SQL脚本 / Mapper接口中原生SQL / DB初始化工具(Flyway, Liquibase)
- 只要数据库层面创建了对应索引，即可在 MyBatis-Plus 中正常利用。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

		自动登录	找回密码
密码			立即注册

Mysql数据库索引

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

0 个回复

快速回复

楼主热帖

标签云

浏览过的版块