MySQL选择合适的索引，看完你就懂了！

Java水解
58 阅读9分钟

1. 索引是啥？为啥要用它？ (别告诉我你不知道！ 😉)

简单说，索引就像书的目录。你想找某个章节内容，是翻目录快，还是从第一页开始一页页翻到最后一页快？当然是目录！

数据库索引也是一个道理。当你查询数据时，如果没有索引，数据库可能得把整个表的数据都扫一遍（这叫“全表扫描” 🤢），数据量小还好说，数据量一大，那叫一个慢啊！

有了索引，数据库可以直接通过索引找到数据所在的“位置”，大大减少扫描的数据量，查询速度自然就飞起来啦！✨

2. 啥时候该考虑建索引？

不是所有列都值得拥有索引的“光环”😎。主要看你的“查询模式”：

  • WHERE 子句里频繁出现的列：  这是索引的“主战场”！你在 WHERE 里过滤什么，什么就最有可能需要索引。比如 WHERE user_id = 123，user_id 就很可能是个好索引候选人。
  • JOIN 连接的列：  表和表之间通过哪个列“牵手”🤝？这个列也非常重要，索引能让连接操作效率大大提升。
  • ORDER BY 或 GROUP BY 的列：  如果你的查询结果经常需要排序或分组，索引有时可以帮助 MySQL 免去额外的排序或哈希操作。
  • DISTINCT 去重列：  有时候也能用上索引。

3. 啥时候别乱建索引？ (索引也是有“脾气”的！😤)

索引虽然能提升查询速度，但它也是有代价的：

  • 占用磁盘空间：  索引本身也要存储。
  • 降低写操作速度：  INSERT、UPDATE、DELETE 操作时，数据库不仅要更新数据，还要更新索引。索引越多，写操作越慢。
  • 对小表作用不大：  数据量本身就很少，全表扫描可能比走索引还快… 或者差不了多少。

所以，不要给你的每个列都加索引，那是在“自残”！🚫

4. 选哪个“列”当索引的“C位”？ (核心来了！💪)

  • 高“区分度”（Cardinality）的列：  想象一下，你在一个电话簿里找人。如果按姓氏找，姓“王”的有1000个，姓“罕见姓”的只有1个。哪个更容易找到？当然是姓“罕见姓”的！这就是“区分度”的概念。一个列里不重复的值越多，区分度越高，索引的效果越好。比如用户ID、订单号、身份证号等。
  • 低“区分度”的列要慎重：  像性别（男/女/未知）、状态（启用/禁用）这种，区分度很低。单独对这种列建索引意义不大，因为查“男”或“女”可能都要扫描表的一半数据。但它们可以在“联合索引”中发挥作用（后面讲）。
  • 数据类型：  尽量选择占用空间小、固定长度的数据类型。比如用 INT 而不是 VARCHAR(255) 来存储数字ID。

5. 索引类型有哪些？ (主要聊 B-tree 😉)

MySQL 最常用的是 B-tree 索引（InnoDB 存储引擎默认就是它）。它适用于各种场景：

  • = 等值查询
  • >、<、>=、<= 范围查询
  • LIKE 'prefix%' 前缀匹配查询 (但 LIKE '%suffix' 或 LIKE '%substring%' 通常用不上 B-tree 索引，除非借助其他技术或全文本索引)
  • ORDER BY 排序
  • GROUP BY 分组

还有其他的，比如 Hash 索引（主要用于 Memory 引擎，或 InnoDB 引擎的自适应哈希索引），只支持等值查询 =，速度快但不支持范围。Fulltext 索引 用于全文搜索。Spatial 索引 用于地理空间数据。但咱们今天主要focus B-tree，它是“万金油”。

6. 联合索引 (Composite Index) - “王炸”组合！💣

很多时候，你的查询条件不止一个列，比如 WHERE last_name = 'Zhang' AND first_name = 'San'。这时候，建一个联合索引 (last_name, first_name) 可能比单独建两个索引效果更好！

重点：联合索引的“最左前缀原则”！  🚪🔑
想象一下，联合索引就像一把“联合锁”。比如索引是 (A, B, C)。

  • 如果你查 WHERE A = ...，能用上索引的 A 部分。
  • 如果你查 WHERE A = ... AND B = ...，能用上 A 和 B 两部分。
  • 如果你查 WHERE A = ... AND B = ... AND C = ...，三部分都能用上！🚀
  • 但是，如果你查 WHERE B = ... 或者 WHERE C = ...，或者 WHERE B = ... AND C = ...，那就凉凉了… 🥶，索引就用不上了！因为它必须从最左边的列开始匹配。

所以，创建联合索引时，列的顺序很重要！应该把最常用在 WHERE 条件中过滤的、或者区分度最高的列放在最左边。

代码例子来袭！🎬

假设我们有一个用户表 users：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    gender ENUM('Male', 'Female', 'Unknown') NOT NULL,
    city VARCHAR(50),
    registration_date DATE NOT NULL,
    -- ... 其他列
    -- PRIMARY KEY (user_id) -- user_id 已经是主键，主键自带索引
);

AI写代码sql
1234567891011

场景 1: 按用户名查找
经常有查询 SELECT * FROM users WHERE username = 'some_user';
username 是唯一的（或者要求唯一），区分度很高。
索引建议：  在 username 列上建唯一索引。

CREATE UNIQUE INDEX idx_users_username ON users (username);

AI写代码sql
1

（UNIQUE 关键字确保用户名不重复，同时它也是一种特殊的索引）

场景 2: 按姓氏和名字查找
经常有查询 SELECT * FROM users WHERE last_name = 'Zhang' AND first_name = 'San';
索引建议：  考虑在 (last_name, first_name) 上建联合索引。因为通常先知道姓再知道名。

CREATE INDEX idx_users_name ON users (last_name, first_name);

AI写代码sql
1

现在，如果你查 WHERE last_name = 'Zhang'，索引的 last_name 部分能用上。
如果你查 WHERE last_name = 'Zhang' AND first_name = 'San'，索引的 last_name 和 first_name 部分都能用上！
但如果你只查 WHERE first_name = 'San'，这个索引就帮不上忙了。😭

场景 3: 按城市和注册日期范围查找
经常有查询 SELECT * FROM users WHERE city = 'Beijing' AND registration_date BETWEEN '2023-01-01' AND '2023-12-31';
索引建议：  考虑在 (city, registration_date) 上建联合索引。城市通常是等值查询，日期是范围查询。

CREATE INDEX idx_users_city_regdate ON users (city, registration_date);

AI写代码sql
1

为什么 city 在前？因为等值查询（=）的列放在前面，能更有效地缩小搜索范围。范围查询（BETWEEN）的列放在后面，虽然 registration_date 也能利用索引，但它会阻断联合索引后续列的使用（也就是范围查询后面的列无法再使用该联合索引了）。

场景 4: 按城市分组统计用户数
SELECT city, COUNT(*) FROM users GROUP BY city;
如果 city 区分度不高，单独建索引效果有限。但如果这个查询很频繁，且城市数量不是特别多，可以考虑。

CREATE INDEX idx_users_city ON users (city); -- 如果city经常用于group by或少量where

AI写代码sql
1

这个索引可能有助于分组，但效果不如 WHERE 条件过滤那么明显。

场景 5: 只查询某些列，且这些列都在索引里 (索引覆盖！🏖️)

查询： SELECT user_id, username FROM users WHERE username = 'some_user';
我们已经在 username 上建了唯一索引 idx_users_username，而且 user_id 是主键（也在索引里）。
MySQL 可以直接通过 idx_users_username 索引找到对应的 username 和 user_id，根本不需要去查原始数据行！这叫索引覆盖 (Covering Index) 。🚀 速度超级快！就像你去海边度假⛱️，所有必需品都在你包里，不用回酒店拿。

7. 如何知道我的索引有没有生效？ - EXPLAIN 神器！

建了索引不代表一定会被用。MySQL 的优化器会根据查询条件、数据量、索引统计信息等因素，决定是否使用索引以及使用哪个索引。

使用 EXPLAIN 关键字可以查看查询的执行计划：

EXPLAIN SELECT * FROM users WHERE username = 'some_user';
EXPLAIN SELECT * FROM users WHERE last_name = 'Zhang' AND first_name = 'San';
EXPLAIN SELECT * FROM users WHERE city = 'Beijing' AND registration_date BETWEEN '2023-01-01' AND '2023-12-31';

AI写代码sql
123

看 EXPLAIN 的结果，重点关注几个列：

  • type:  连接类型。这是最重要的指标之一。

    • ALL: 全表扫描，通常是最差的，说明索引没用上或者不适合。😱
    • index: 扫描索引，但可能还要回表查询数据。比 ALL 好。
    • range: 索引范围扫描，用在了范围查询上。👍
    • ref: 非唯一索引的等值查找。👍
    • eq_ref: 唯一索引的等值查找（通常用于 JOIN）。🚀
    • const, system: 非常快的查询，数据或表非常小。✨

  • possible_keys:  可能用到的索引。

  • key:  实际用到的索引。这个最关键！如果这里是 NULL，说明没用上索引。😩

  • key_len:  索引使用的字节数，联合索引可以看到用了多少列。

  • rows:  估计需要扫描的行数。越少越好！👍

  • Extra:  额外信息。

    • Using where: 用到了 WHERE 条件过滤。
    • Using index: 索引覆盖！查询的所有列都在索引里，非常高效。🚀
    • Using filesort: 需要额外的文件排序。如果 ORDER BY 的列没有合适索引，或者索引无法用于排序，就会出现这个。尽量避免！😫
    • Using temporary: 使用临时表。如果 GROUP BY 或 DISTINCT 没用上索引，可能出现。尽量避免！😫

通过 EXPLAIN 反复测试你的查询，调整索引，直到看到更好的 type (比如 ref, range, eq_ref)，更少的 rows，或者 Using index (索引覆盖)，并且尽量避免 Using filesort 和 Using temporary。

总结：

  1. 观察你的查询：  你的 WHERE, JOIN, ORDER BY, GROUP BY 里最常用哪些列？
  2. 评估列的特点：  区分度高吗？数据类型大小合适吗？
  3. 考虑联合索引：  如果查询条件涉及多个列，考虑联合索引。记住“最左前缀原则”！
  4. 关注索引覆盖：  设计索引时，如果能让查询的所有列都包含在索引里，那就太棒了！🏖️
  5. 不要过度索引：  索引是双刃剑，权衡读写性能。小表、写操作频繁的列慎重。
  6. 用 EXPLAIN 验证！验证！验证！  (重要的事情说三遍！📢) 这是你最好的朋友！

选择合适的索引就像一门艺术🎨，需要经验和不断的实践。没有放之四海而皆准的规则，但遵循上面的原则，结合 EXPLAIN 的反馈，你一定能成为索引优化的高手！💪

avatar
文章
阅读
粉丝