MySQL索引及其底层原理

summer哥
126 阅读9分钟

什么是索引

索引是帮助MySQL高效查询数据的数据结构，相当于书的目录，指向对应记录所在位置，帮助我们快速找到对应数据。同时索引是一个文件，需要占用物理空间，更新数据时需要维护索引。所以索引一方面提高了查询效率，另一方面降低了更新效率。

创建索引

// 索引创建方式1：在已存在的表上创建索引
CREATE INDEX idx_username ON users (username);
// 索引创建方式2：修改表结构的同时创建索引
ALTER TABLE users ADD INDEX idx_username (username);
// 索引创建方式3：创建表时定义索引
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    [UNIQUE|FULLTEXT] INDEX index_name (column1, column2, ...)
);

索引类型

  • 单列索引与联合索引。使用一个字段建立索引即为单列索引，使用多个字段共同构建索引即为联合索引。
  • 聚簇索引与非聚簇索引。
    • 聚簇索引存储索引字段和其他数据（即索引与数据存储在一起），聚簇索引记录了数据的物理顺序，所以每个表只能有一个聚簇索引，例如InnoDB中主键就是聚簇索引。
    • 非聚簇索引只存储索引字段和指向数据的指针（即索引与数据分开存储），非聚簇索引记录了数据的逻辑顺序，所以每个表可以存在多个非聚簇索引，我们手动创建的都是非聚簇索引。

注意：在InnoDB中，若定义了主键，主键就是聚簇索引; 若没有明确指定主键则自动选择一个唯一索引作为聚簇索引; 若没有唯一性索引，则使用行隐藏字段row_id作为聚簇索引。

注意：聚簇索引决定数据物理存储位置，所以虽然聚簇索引不用回表，查找速度快，但更新数据时可能要移动数据。而非聚簇索引只影响数据的逻辑顺序，所以非聚簇索引更新时不需要移动数据。

回表与索引覆盖

  • 非聚簇索引可能需要回表操作，即找到索引后再根据指针去找实际数据。回表操作是比较耗时的，因为索引查找是顺序IO，回表操作是随机IO。对于MySQL优化器来说，随机IO次数越多，越倾向于全表扫描。
  • 非聚簇索引也不一定会回表，若索引字段全部覆盖了查询字段，则不回表，即索引覆盖。

联合索引与最左前缀原则

  • 最左前缀原则：使用联合索引时要遵循从左到右的顺序匹配，即筛选数据时先用联合索引靠左的字段，若先使用到了靠右字段，则联合索引失效。
  • 原理：建立联合索引时，数据会按照联合索引的字段依次排序（非聚簇索引，即逻辑顺序），故查询时使用字段的顺序要与排序时使用字段的顺序相同。
  • 举例：联合索引(A,B)，数据先按照A字段排序，A字段相同再按B字段排序，在查询时如果只用B字段作为条件去查询而不用A字段查询是无法使用到这个联合索引的，因为单从B字段看数据是无序的。
  • MySQL优化器的影响：如果A，B字段都作为条件进行查询，它们的顺序可以随意，因为MySQL有优化器可以帮我们自动调整它们的顺序。

索引底层结构

索引底层结构是B+树，B+树是一棵N叉平衡树，结构如下图所示

image.png

B+树的特点

  • B+树中每个节点中n个关键字对应n棵子树，这使得它成为一棵"矮胖"的树，从而减少读写时的磁盘IO次数。
  • B+树只有叶节点存储数据，非叶节点存储的是其子树的最大（最小）关键字，即索引。
  • B+树所有叶节点按照主键递增的顺序使用双向链表连接，方便范围查询和全表扫描。

为什么用B+树

  • B+树的数据均存储在叶节点中，方便扫库和范围查询（不用回到上层），且查询效率稳定。
  • B+树的非叶子节点只存储索引，占用空间较小，而一个节点就是一页，容量固定，所以包含的关键字多，那么一次IO读取到的关键字更多，IO读写次数就降低了。
  • B+树的高度不会随着数据量的增加而增加，一般高度为2-4层，3层即可存储4000w条数据。

为什么不用其他数据结构

  • B树：数据分布在整棵树中，查询时间不稳定；非叶节点存储数据，没有B+树非叶子节点的IO优势；范围查询要在整棵树上进行，没有B+树叶子节点的链表强大。
  • Hash结构：hash结构只适合等值查询，不适合范围查询，不适合字段排序。
  • 二叉树和红黑树：二叉树和红黑树都是二叉树，高度会随着数据量增加而增加，而B+树和B树是n叉树，高度一般较小。且平衡二叉树和红黑树插入数据需要旋转操作，比较复杂。

3层B+树可以存多少数据

  • 若主键是int类型，可以存储4000W条数据。
  • 在InnoDB中，B+树一个节点就是一页，大小为16KB。非叶节点存储主键和指针，即4B+6B共10B，一页可以存1600条数据。叶节点存储的数据行假设1KB，可以存储16行，故三层B+树能存1600 * 1600 * 16=4096w行数据。
  • 注意：这里的指针大小为6B是InnoDB设定的，与常见的4B（32位系统）或8B（64位系统）不同。
  • 需要几次IO：一页的查找需要一次IO，B+树高度为2-4层，故一般需要2-4次IO。

索引的其他实现

  • Hash：Hash只适合等值查询并不适合范围查询，MySQL的Memory引擎默认使用Hash结构。另外在InnoDB中有一种自适应哈希索引技术，即若某个数据被频繁访问时会为其创建一个哈希索引以加快对此数据的访问。
  • B树：MaongoDB使用B树作为索引结构，因为MongoDB属于非关系型数据库，大多数情况下只需要查询单条数据，故使用B树（每个节点都存数据，查询效率可能会更高）。
  • 倒排索引：全文索引使用倒排索引实现，适用于全文搜索，是一种常见的搜索引擎数据结构。倒排索引先把一篇文章切分为多个短语，以这些短语为key，去映射找到对应的文章。

为什么使用自增长主键

  • 使用自增主键在插入新数据时会插入到B+树最后一个叶子节点，可以尽量避免页分裂，减少数据的移动。
  • 页分裂指B+树的一个页（叶子节点）满时如果再插入数据，需要创建新页，然后将原页的一部分数据移动到新页中，开销较大。

索引原则

  • 最左前缀原则：创建联合索引时，使用最频繁的字段放最左边，查询条件应该从左到右并且不跳过中间字段，否则索引失效。
  • 索引覆盖：非聚簇索引中使用联合索引覆盖所有需要查询的字段，这样不需要回表。
  • 前缀索引：索引字段非常长时，只把字段的前缀部分作为索引。
  • 索引下推：
    • 不使用索引下推时先通过索引找到数据，然后回表，最后再进行where条件过滤。
    • 使用索引下推时先通过索引找到数据，若where条件中存在未用到的索引字段，则用其做过滤，然后再回表。
    • 总结：索引下推避免回表读取不满足条件的行。
    • 例如联合索引AB，查询条件是A > x and B > y（注意A是范围查询，所以无法用到B字段索引）。如果没有索引下推，索引找到A > x的数据后直接回表然后再对B字段过滤。如果使用索引下推，索引找到A大于x的数据后再对B字段索引筛选，然后再回表。

索引失效

首先如何知道索引是否生效？我们可以使用"explain SQL语句"来查看索引使用情况。explain结果的一些比较重要的字段为：

  • id：每个select对应一个id。
  • table：查询的表名。
  • possible_keys：可能用到的索引。
  • key：实际使用的索引。
  • key_len：实际使用的索引长度，可以看联合索引中使用到的索引长度。
  • rows：预计需要读取的记录条数。
  • type：判断本次查询是全表扫描or索引扫描，结果值从好到坏依次是：system，const，eq_ref， ref，range，index（扫描所有索引），ALL(全表扫描)。

若发现SQL语句没有如预期那样使用索引，即索引失效，可能是因为：

  • SQL语句使用了不等于运算符：例如!=,<>,is not null, not like, not in。
  • SQL语句使用了运算符，函数，类型转换。
  • SQL语句使用like时，通配符%在开头。
  • or前后存在非索引列，索引失效。
  • 没有遵守最左前缀原则。
  • 范围查询之后的字段也无法使用索引，因为范围查询会破坏数据的有序性，故创建联合索引必须把范围查询字段放到最后。
  • 如果MySQL估计使用全表扫描比使用索引更快时，也不会使用索引，例如小表的索引。
  • 子查询会生成临时表，临时表不会使用到索引。

创建索引的注意事项

  • 在创建索引时要选择合适的列作为索引。
    • 经常作为查询、排序、分组、连接的列。
    • 区分度高的列（count(distinct 字段) / count(*)） 。
    • 不频繁更新的，读多写少的列。
    • 数据量小的表不要设置索引。
    • 无序字段（如身份证，UUID）不要设为索引，会造成页分裂。
  • 避免过多的索引，因为索引需要占用额外空间，维护索引需要消耗资源。
  • 注意索引的各种原则，如最左前缀原则，索引覆盖，索引下推，前缀索引。
  • 避免索引失效。
avatar
文章
阅读
粉丝