数据库索引介绍

丧心病狂汤姆猫
105 阅读8分钟

一、先聊聊索引的概念

首先，咱们得明确什么是索引。简单说，索引就是一种排好顺序的数据结构，它的核心作用只有一个 ——提升数据库的查询性能。有了它，数据库不用全表扫描找数据，能直接通过索引快速定位，效率会高很多。

二、索引的两大分类

索引主要分两类，核心区别在于 “叶子节点存什么”。

  1. 聚簇索引（也叫主键索引）

    • 它是和数据 “绑在一起” 的索引。
    • 它的叶子节点里，既存索引值，也存整行数据，不用再去别的地方找数据。

  2. 非聚簇索引（也叫非主键索引）

    • 它只负责定位，不存完整数据。
    • 它的叶子节点里，只存索引值和对应的主键值。如果要查整行数据，还得用主键值再去聚簇索引里找，这个过程叫 “回表”。

三、索引底层为啥用 B + 树？

咱们先明确结论：MySQL 索引的底层数据结构，用的是B + 树。那为啥不选别的？咱们一步步说。

1. 先排除 Hash：只能等值查，没法做范围查

Hash 结构的特点很明显，它通过哈希算法快速定位，只支持 “等于” 这种等值查询。但实际业务里，咱们经常要查 “年龄大于 30”“时间在 2024 年之后” 这种范围条件，Hash 根本处理不了，所以直接排除。

2. 再看 B 树：节点存数据，树会变高，查询慢

B 树的结构比较 “实在”，不管是非叶子节点（比如父节点）还是叶子节点（没有子节点的节点），每个节点里都又存索引值、又存整行数据。咱们算笔账就知道问题在哪：

  • 假设索引值占 8 字节，一行数据占 1024 字节（约 1K），那一个节点总共要存 1032 字节。
  • MySQL 里数据是按 “页” 存的，一页固定 16K（161024 字节）。这么算下来，一页只能存 15 个节点（161024 / 1032 ≈15）。
  • 那树的高度就会很高：第一层 15 个，第二层 1515=225 个，第三层 1515*15=3375 个…… 要存大量数据，树会越来越高，查询时要走的路径就越长，效率自然就低了。

3. 最后看 B + 树：非叶子只存索引，叶子连链表，完美解决问题

B + 树做了两个关键优化，正好弥补了 B 树的缺点：

  • 非叶子节点：只存索引和指针，能存更多数据

    • 非叶子节点里，不存整行数据，只存 “索引值 + 指向下个节点的指针”。
    • 假设索引值 8 字节、指针 6 字节，一个节点总共 14 字节。按 16K 一页算，一页能存 1170 个节点（16*1024 /14 ≈1170）。
    • 这样一来，树的高度能压得很低：第一层 1170 个，第二层 11701170≈137 万，第三层如果是叶子节点，能存 11701170*15≈2000 万条数据。2000 万数据只需要 3 层树，查询时最多查 3 次，效率极高。

  • 叶子节点：存数据 + 双向链表，支持范围查

    • 叶子节点里，既存索引值也存整行数据，和聚簇索引类似。
    • 同时，叶子节点之间会用双向链表连起来，这样查范围数据时，不用再回溯上层节点，直接顺着链表找就行，大大提升了区间访问的效率。

总结：为啥选 B + 树？

就两点核心原因：

  1. 树的深度低，查询次数少，性能高；
  2. 叶子节点是双向链表，支持范围查询，适配更多业务场景。

四、主键索引的那些细节

关于主键索引，有几个重点必须记住：

  1. InnoDB 引擎必须有主键

    • 就算你没手动创建主键，InnoDB 也会自动创建一个隐藏的主键索引，自己维护。
    • 建议用 “没有业务意义的列” 当主键，比如自增 ID，别用手机号、身份证号这种业务字段，避免后续业务变动影响索引。

  2. 主键索引的查询效率极高

    • 它的非叶子节点存主键值，叶子节点直接存整行数据。
    • 所以通过主键查数据时，直接在索引树上就能找到，不用 “回表”，速度非常快。

  3. 强烈推荐 “主键自增”

    • 自增主键插入数据时，新数据永远会放在索引树的最后面，能快速找到插入位置，不用做 “移动数据”“旋转树” 这些额外操作，开销很小。
    • 如果主键不是自增的（比如用随机 ID），数据库没法判断新数据该插在树的哪个位置，可能要调整树的结构，会带来很多不必要的开销。

五、非主键索引的 4 种常见类型

除了主键索引，咱们平时用的更多是非主键索引，主要有 4 种：

1. 普通索引

  • 最基础的索引，直接在普通字段（比如姓名、年龄）上创建。

  • 它的非叶子节点存索引列的值，叶子节点只存对应的主键值。

  • 这里有个关键问题：为啥非主键索引的叶子节点只存主键值？

    • 要是存整行数据，一旦数据修改，不仅要更主键索引，还得改所有非主键索引，会产生大量额外开销。存主键值的话，只需要维护主键索引，其他索引跟着主键走就行，开销小很多。

2. 唯一索引

  • 在 “唯一列” 上创建的索引，比如手机号、邮箱（确保字段值不重复），创建时要加unique关键字。
  • 它的作用除了加速查询，还能保证字段的唯一性，相当于多了一层数据校验。

3. 联合索引（也叫组合索引）

  • 给多个非主键字段一起创建的索引，比如给 “姓名、年龄、手机号” 这三个字段建联合索引。
  • 它的核心是 “最左前缀原则”，查询时要从左到右匹配字段，不然可能用不上索引，这点后面实际用的时候要注意。

4. 全文索引

  • 这个用得相对少，因为它主要是针对 “大文本” 做模糊查询的（比如文章内容）。
  • 现在更多是用 Elasticsearch 来做全文检索，MySQL 的全文索引一般只在简单场景下用。

六、创建索引的 6 个关键要求

不是所有字段都适合建索引，建之前一定要注意这 6 点：

  1. 单张表的索引数量，别超过 5 个。索引多了会让插入、修改、删除操作变慢，因为要同步维护多个索引。

  2. 联合索引的字段数量，别超过 5 个。字段越多，索引体积越大，查询时匹配效率也会下降。

  3. 经常增删改的字段，不适合建索引。比如 “订单状态”，频繁修改会频繁维护索引，反而影响性能。

  4. 枚举值字段（比如 “性别”“支付方式”，值只有几种），不适合建索引。因为值太少，索引过滤效果差，可能还不如全表扫描快。

  5. 不经常修改、但经常查询的字段，适合建索引。比如 “用户注册时间”，查询多、修改少，索引能最大程度发挥作用。

  6. 大长度字段（比如 VARCHAR (255) 的用户名、邮箱），可以建前缀索引。只取字段的前几个字符建索引（比如用户名的前 10 个字符），既能减小索引体积，又能保证一定的查询效率。

    • 举个例子，创建用户名的前缀索引：

    sql

    CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255)
);
-- 只对username的前10个字符建索引
CREATE INDEX idx_username_prefix ON users (username(10));

七、创建索引的语法和命名规范

最后，咱们把常用的索引创建语法和命名规范理一理，方便实际操作：

  1. 主键索引

    • 语法：建表时加PRIMARY KEY，比如user_id INT AUTO_INCREMENT PRIMARY KEY。
    • 命名规范：pk_列名（pk 是 primary key 的缩写），比如pk_user_id。

  2. 普通索引

    • 语法：CREATE INDEX 索引名称 ON 表名(列名);，比如CREATE INDEX idx_username ON users(username);。
    • 命名规范：idx_列名（idx 是 index 的缩写），比如idx_username。

  3. 唯一索引

    • 语法：CREATE UNIQUE INDEX 索引名称 ON 表名(列名);，比如CREATE UNIQUE INDEX uk_email ON users(email);。
    • 命名规范：uk_列名（uk 是 unique key 的缩写），比如uk_email。

  4. 联合索引

    • 语法：CREATE INDEX 索引名称 ON 表名(列1,列2,...);，比如CREATE INDEX idx_name_age ON users(name,age);。
    • 命名规范：idx_列1_列2，把所有字段名按顺序拼上，比如idx_name_age_phone。
avatar
文章
阅读
粉丝