mysql建立索引示例

示例表

CREATE TABLE user (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '唯一标识每个用户的ID',
username VARCHAR(255) NOT NULL UNIQUE COMMENT '用户名，用于登录系统',
password VARCHAR(255) NOT NULL COMMENT '用户密码的哈希值',
email VARCHAR(255) NOT NULL UNIQUE COMMENT '用户邮箱',
first_name VARCHAR(255) COMMENT '用户的名字',
last_name VARCHAR(255) COMMENT '用户的姓氏',
phone_number VARCHAR(20) COMMENT '用户的电话号码',
address VARCHAR(255) COMMENT '用户的地址',
city VARCHAR(100) COMMENT '用户所在的城市',
state VARCHAR(100) COMMENT '用户所在的州或省份',
postal_code VARCHAR(20) COMMENT '用户的邮政编码',
country VARCHAR(100) COMMENT '用户所在的国家',
date_of_birth DATE COMMENT '用户的出生日期',
gender ENUM('Male', 'Female', 'Other') COMMENT '用户的性别',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '用户记录创建的时间',
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户记录最后更新的时间',
status ENUM('Active', 'Inactive', 'Pending') DEFAULT 'Active' COMMENT '用户的状态',
role_id INT COMMENT '用户的角色ID，通常与角色表关联',
profile_picture VARCHAR(255) COMMENT '用户的头像图片路径',
verification_code VARCHAR(255) COMMENT '用户的验证代码，用于账户验证或密码重置',
is_verified BOOLEAN DEFAULT FALSE COMMENT '用户是否已经验证了他们的电子邮件地址',
last_login DATETIME COMMENT '用户上次登录的时间'
);

思路

我现在要优化这个MySQL表的索引。首先，我需要理解这个表的结构和用途。这个表是用户表，存储用户的基本信息，包括用户名、密码、邮箱、个人资料等等。主键是id，自增的，这没问题。还有username和email字段都是UNIQUE的，所以它们应该已经有唯一索引了。

接下来，我要考虑哪些字段会被频繁用于查询、排序或者连接。比如，用户登录时可能会用username或email来查找，所以这两个字段已经有唯一索引，应该足够。但是可能需要考虑其他查询的情况，比如按状态（status）、角色（role_id）、最后登录时间（last_login）来筛选用户。管理后台可能会有这样的查询需求。

另外，像created_at和updated_at这类时间字段，如果经常需要按时间范围查询或者排序，可能也需要索引。例如，查找最近注册的用户，或者统计某段时间内的活跃用户。

还有组合查询的情况。比如，同时根据status和role_id来过滤用户，这时候可能需要一个联合索引。联合索引的顺序很重要，应该把区分度高的字段放在前面，或者根据查询条件的顺序来定。

比如，如果有查询条件WHERE status = ‘Active’ AND role_id = 2，那么建立一个(status, role_id)的联合索引可能会更好，这样可以利用索引的最左前缀原则。但是如果查询中单独使用role_id的情况较多，可能就需要单独的索引，或者调整联合索引的顺序。不过单独为role_id建索引可能更好，因为联合索引只有在使用左边的字段时才有效。

另外，phone_number字段如果经常用来查找用户，可能也需要索引。比如用户通过电话号码登录或者找回密码，这时候如果没有索引，查询会全表扫描，效率低。

还有，像verification_code字段可能在用户验证的时候会被查询，但这种查询可能频率不高，而且code通常是唯一的，可以考虑加索引。不过如果该字段使用次数很少，可能没必要，需要根据实际业务情况判断。

date_of_birth字段如果经常需要按年龄筛选用户，可能需要索引。或者像根据城市、国家等地理位置进行查询，如果这类查询频繁，可能需要为city、state、country等字段加索引。

还有，last_login字段可能用于查询最近活跃的用户，这时候如果按last_login排序或筛选，索引可能有帮助。

不过，索引也不是越多越好，因为每个索引都会增加写操作的开销。插入、更新、删除数据时，索引需要维护，所以需要权衡。

现在，原表中已经有的索引是主键id的聚簇索引，以及username和email的唯一索引。接下来要考虑其他可能的索引：

1. role_id字段：如果经常根据角色查询用户，比如查找所有管理员用户（role_id=1），应该为role_id建立索引。

2. status字段：可能有很多查询根据状态筛选用户，如状态为Active的用户，索引会加快这类查询。

3. created_at：按注册时间排序或筛选，比如最近一周注册的用户，索引可以加速。

4. 联合索引：比如status和role_id的组合，或者country和city的组合，视查询需求而定。

5. phone_number：如果有根据电话号码查询的需求，应该加索引。

6. last_login：可能需要按最后登录时间排序，或者筛选长时间未登录的用户。

另外，组合索引的顺序需要根据查询的条件和顺序来定。例如，如果有查询同时使用status和role_id，并且status的选择性比role_id高，那么把status放在前面会更好。

还需要考虑是否有ORDER BY或者JOIN的情况。比如，在分页查询用户时，按created_at倒序排列，这时候如果有WHERE条件加上ORDER BY，合适的索引可以避免filesort。

比如，查询活跃用户并按注册时间排序：

SELECT * FROM user WHERE status=’Active’ ORDER BY created_at DESC;

这时候，一个(status, created_at)的联合索引会有效，因为WHERE条件使用status，ORDER BY使用created_at，索引可以同时满足这两个需求。

再比如，查找某个角色下未验证的用户：

SELECT * FROM user WHERE role_id=2 AND is_verified=0;

这时候可能需要一个(role_id, is_verified)的联合索引，或者单独的role_id索引，如果is_verified的选择性不高（比如大部分用户已经验证），单独role_id可能足够。

不过，is_verified的布尔字段可能只有两个值，单独索引可能意义不大，但如果和别的字段组合，可能会有用。

对于性别gender字段，可能选择性较低，单独索引可能不太有用，但如果和其他字段组合查询，可以考虑加入联合索引。

现在总结可能的索引：

– role_id 单列索引

– status 单列索引

– created_at 单列索引

– (status, created_at) 联合索引

– (role_id, status) 联合索引

– phone_number 单列索引

– last_login 单列索引

– verification_code 单列索引（如果查询频繁）

– country, state, city 的组合索引（如果经常按这些地理位置筛选）

但需要根据实际查询模式来调整。比如，如果有一个高频查询是根据status和created_at来获取用户，那么联合索引更好。如果单独查询created_at的情况多，可能需要单独的索引，否则联合索引可能足够。

另外，如果存在范围查询，比如created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’，那么索引的顺序可能需要调整。但如果是联合索引，范围查询后面的列可能无法使用索引。

还需要注意，当有多个查询条件时，联合索引可能覆盖更多的查询场景。例如，如果有多个查询组合不同的字段，可能需要不同的联合索引，但索引太多会影响性能。

例如，假设常见查询有：

1. WHERE status = ‘Active’ ORDER BY created_at DESC

2. WHERE role_id = 5 AND status = ‘Pending’

3. WHERE country = ‘USA’ AND city = ‘New York’

4. WHERE phone_number = ‘123456789’

5. WHERE last_login < ‘2023-01-01’ 查找不活跃用户

针对这些情况：

– 查询1需要(status, created_at)的联合索引。

– 查询2需要(role_id, status)的联合索引，或者单独索引，但联合索引可能更高效。

– 查询3可能需要(country, state, city)的联合索引，但具体要看查询条件是否常一起使用。

– 查询4需要phone_number的索引。

– 查询5需要last_login的索引。

此外，如果verification_code用于密码重置等操作，虽然频率可能不高，但每次操作需要快速查询，所以可以加索引，因为字段是VARCHAR(255)，可能比较长，需要考虑索引大小，但因为是唯一查询，所以索引是必要的。

另外，对于ENUM类型的字段，比如status，gender等，它们的索引效果和普通字符串字段类似，但可能因为枚举值少，选择性低，单独索引可能效果不明显，但如果与其他字段组合，可能会有帮助。

接下来，可能需要为以下字段或组合创建索引：

– role_id：单独索引

– status：单独索引，或者与其他字段组合

– created_at：单独或组合

– phone_number：单独唯一索引？原表中phone_number没有唯一约束，但可能有业务需求需要唯一，但原表里没有，所以这里只是普通索引。

– verification_code：单独索引

– last_login：单独索引

– (status, created_at)：联合索引

– (role_id, status)：联合索引

但需要避免冗余索引。比如，如果已经有了(status, created_at)，单独的status索引可能冗余，因为联合索引的最左前缀可以用于只使用status的查询。这时候，如果大部分查询只用status，可以只建联合索引，而不需要单独的status索引。

但需要考虑，如果有些查询只使用created_at，而联合索引是(status, created_at)，这时候这个联合索引对只按created_at查询的情况没有帮助，所以可能需要单独的created_at索引。这需要根据具体情况权衡。

举个例子，假设有两个查询：

A. WHERE status = ‘Active’ ORDER BY created_at

B. WHERE created_at BETWEEN …

如果查询A很频繁，那么(status, created_at)的联合索引对A有帮助，而查询B需要单独的created_at索引。

但如果查询B也很频繁，可能需要同时存在这两个索引，但会增加存储和维护成本。

综上，可能的索引建议：

1. 主键id（已有）

2. username的唯一索引（已有）

3. email的唯一索引（已有）

4. phone_number的索引（新）

5. role_id的索引（新）

6. status的索引（新），或者联合索引

7. created_at的索引（新）

8. (status, created_at)的联合索引（新）

9. verification_code的索引（新）

10. last_login的索引（新）

但需要检查是否联合索引可以替代多个单独索引，以减少索引数量。

比如，如果联合索引(status, created_at)存在，那么单独的status索引可能可以去掉，因为联合索引的最左前缀可以用于仅status的查询。但是，如果存在其他联合索引的情况，比如(status, role_id)，那么可能需要不同的处理。

此外，对于verification_code，如果该字段是唯一的，比如每个用户的验证码唯一，那么可以设置为唯一索引，但原表中并没有唯一约束，所以可能只是普通索引。

综上，最终的索引建议可能如下：

ALTER TABLE user ADD INDEX idx_role_id (role_id);

ALTER TABLE user ADD INDEX idx_status (status);

ALTER TABLE user ADD INDEX idx_created_at (created_at);

ALTER TABLE user ADD INDEX idx_phone_number (phone_number);

ALTER TABLE user ADD INDEX idx_verification_code (verification_code);

ALTER TABLE user ADD INDEX idx_last_login (last_login);

ALTER TABLE user ADD INDEX idx_status_created_at (status, created_at);

ALTER TABLE user ADD INDEX idx_role_status (role_id, status);

不过，这样的索引数量较多，可能需要根据实际查询频率和模式进行调整。例如，如果status和role_id经常一起查询，那么idx_role_status可能更好。而如果单独查询status或role_id的情况较多，可能需要单独索引。

另外，对于联合索引idx_status_created_at，可以优化同时使用status和created_at的查询，以及仅使用status的查询。如果created_at的单独查询也很多，可能需要单独索引。

此外，对于phone_number，如果业务上需要唯一，应该添加唯一索引，但原表结构中没有，所以这里只是普通索引。

还要注意，长字符串的索引，比如verification_code是VARCHAR(255)，可以考虑使用前缀索引来减少索引大小，但需要权衡选择性。例如：

ALTER TABLE user ADD INDEX idx_verification_code (verification_code(20));

但可能影响查询性能，因为前缀索引可能导致更多的扫描。如果verification_code是哈希值，前20个字符可能足够唯一，这样前缀索引是可行的。

另外，对于last_login的索引，如果查询需要按时间范围查找，比如超过6个月未登录的用户，索引会很有帮助。

总结，最终的索引建议需要根据具体的查询需求，但基于常见的用户表查询模式，上述的索引可能是一个合理的起点。同时需要注意，每个额外的索引都会影响写入性能，因此需要根据实际业务情况权衡。

最后建议

为了优化提供的 user 表的查询性能，以下是索引建议及详细讲解：

1. 已存在的索引

主键索引：id（自动创建，聚簇索引）
唯一索引：username 和 email（确保唯一性）

2. 建议新增的索引

(1) 单列索引

ALTER TABLE user
  ADD INDEX idx_phone_number (phone_number),
  ADD INDEX idx_role_id (role_id),
  ADD INDEX idx_status (status),
  ADD INDEX idx_created_at (created_at),
  ADD INDEX idx_last_login (last_login),
  ADD INDEX idx_verification_code (verification_code(64));  -- 前缀索引优化长字符串

优化场景：

phone_number：用于电话号码查询（如登录/找回密码）。
role_id：按角色筛选用户（如管理后台查询）。
status：按用户状态过滤（如筛选活跃用户）。
created_at：按注册时间排序或范围查询（如最近注册用户）。
last_login：查找不活跃用户（如最后登录时间超过阈值）。
verification_code：账户验证时快速查询（前缀索引平衡长度与选择性）。

(2) 联合索引

ALTER TABLE user
  ADD INDEX idx_status_created_at (status, created_at),
  ADD INDEX idx_role_status (role_id, status);

优化场景：

idx_status_created_at：
高频查询：WHERE status = 'Active' ORDER BY created_at DESC（如分页展示活跃用户）。
联合索引直接覆盖查询条件和排序，避免 filesort。
idx_role_status：
高频查询：WHERE role_id = 2 AND status = 'Pending'（如筛选特定角色的待审用户）。
联合索引高效匹配多条件查询。

3. 索引优化原则

(1) 覆盖高频查询

WHERE 条件：为高频筛选字段（如 role_id、status）添加索引。
ORDER BY/GROUP BY：为排序字段（如 created_at）添加索引。
联合索引顺序：将区分度高的字段放在左侧（如 status 区分度高于 created_at）。

(2) 避免过度索引

写性能损耗：索引会降低 INSERT/UPDATE/DELETE 速度，需权衡读写比例。
长字段优化：对 VARCHAR(255) 使用前缀索引（如 verification_code(64)）。

(3) 使用 EXPLAIN 分析

通过 EXPLAIN 验证索引是否生效：

EXPLAIN SELECT * FROM user WHERE status = 'Active' AND role_id = 1;

4. 示例查询与索引匹配

场景 1：管理后台分页查询活跃用户

SELECT * 
FROM user 
WHERE status = 'Active' 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;

使用索引：idx_status_created_at（直接覆盖 WHERE 和 ORDER BY）。

场景 2：根据角色和状态筛选用户

SELECT * 
FROM user 
WHERE role_id = 2 AND status = 'Pending';

使用索引：idx_role_status（联合索引匹配多条件）。

场景 3：根据电话号码快速查询用户

SELECT * 
FROM user 
WHERE phone_number = '+123456789';

使用索引：idx_phone_number（避免全表扫描）。

5. 最终索引列表

CREATE TABLE user (
    -- 表结构保持不变，仅添加索引
    -- ...
    INDEX idx_phone_number (phone_number),
    INDEX idx_role_id (role_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_last_login (last_login),
    INDEX idx_verification_code (verification_code(64)),
    INDEX idx_status_created_at (status, created_at),
    INDEX idx_role_status (role_id, status)
);

总结

核心思路：根据查询频率、条件组合和排序需求添加索引。
权衡点：索引的查询收益与维护成本（写性能）。
动态调整：通过 EXPLAIN 和慢查询日志持续优化。

近期文章

近期评论

后端 · 2025年 2月 11日 0

1. 已存在的索引

2. 建议新增的索引

(1) 单列索引

(2) 联合索引

3. 索引优化原则

(1) 覆盖高频查询

(2) 避免过度索引

(3) 使用 EXPLAIN 分析

4. 示例查询与索引匹配

场景 1：管理后台分页查询活跃用户

场景 2：根据角色和状态筛选用户

场景 3：根据电话号码快速查询用户

5. 最终索引列表

总结

您可能还喜欢...

发表回复取消回复

后端 · 2025年 2月 11日 0

1. 已存在的索引

2. 建议新增的索引

(1) 单列索引

(2) 联合索引

3. 索引优化原则

(1) 覆盖高频查询

(2) 避免过度索引

(3) 使用 EXPLAIN 分析

4. 示例查询与索引匹配

场景 1：管理后台分页查询活跃用户

场景 2：根据角色和状态筛选用户

场景 3：根据电话号码快速查询用户

5. 最终索引列表

总结

您可能还喜欢...

优利德ut61e+数字万用表使用说明使用指南

dell r620 使用说明书

使用docker自建BT Tracker 服务器

发表回复 取消回复

发表回复取消回复