跳至主要內容

MySQL索引

Cutewr大约 17 分钟MySQL索引

MySQL索引

为什么用索引能加快查询？

数据库文件是存储在磁盘上的，磁盘 I/O 是数据库操作中最耗时的部分之一。没有索引时，数据库会进行全表扫描，当表的数据量非常大时，就会导致大量的磁盘 I/O 操作。

有了索引，就可以直接跳到索引指示的数据位置，而不必扫描整张表，从而提升查询的效率。

缺点：

  • 创建索引和维护索引需要耗费一定时间。当对有索引的数据进行增删改的时候，那么索引也需要动态的修改。
  • 索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

介绍一下有索引但是还是全表扫描的情况

MySQL的优化器会选择成本最低的一种执行计划。

索引的区分度不高，加上索引查询之后还需要回表操作找到我们需要的数据。

索引的数据类型

  1. 哈希表

    通过key值可以快速找到value值，可以快速的检索数据。

    缺点：1、可能会发生哈希冲突； 2、 Hash 索引不支持范围查询。

    InnoDB存储引擎支持一种特殊的“自适应哈希”，不需要我们手动设置。结合了B+树和哈希表，主要应用在等值查询中，可以根据索引的key值快速定位到B+树叶子节点中的数据，从而减少B+树查找过程中的IO次数，提升性能。

  2. 树形结构

    1. 二叉搜索树 BST
    2. 红黑树
    3. 二叉平衡树AVL

  3. B树和B+树

拓展点：OLAP和OLTP

  • OLAP：联机分析处理，
  • OLTP：联机事务处理，和业务对接，要求在短时间内给出处理结果

索引数据类型分类

  1. BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样。
  2. 哈希索引：类似键值对的形式，一次即可定位。但是对于范围查询不友好
  3. RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
  4. 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

索引存储方式分类

  1. 聚簇索引：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。

    优点：查询速度非常快，因为索引和数据放在一起，找到索引就相当于找到了数据，少了一次回表操作；

    缺点：1、依赖于有序顺序，因为B+树是多路平衡树，如果索引数据不是有序的，就需要在插入时排序。2、更新代价大，如果索引列数据被修改，索引也需要修改，而且聚簇索引的索引和数据是放在一起的，修改代价比较大。所以对于主键索引来说，主键一般不允许修改。

  2. 非聚簇索引：索引结构和数据分开存放的索引，InnoDB存储引擎中除了主键索引，都是非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

    优点：由于索引和数据分开存放，更新代价相对较小

    缺点：依赖于有序数据；可能需要回表，当查询到索引数据对应的聚簇索引数据后，还需要根据聚簇索引数据再查找一次。

索引按应用分类

主键索引、唯一索引、普通索引、联合索引、覆盖索引

  1. 主键索引

    数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且不能为 null，不能重复。

    在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

  2. 唯一索引：唯一索引的属性列不允许重复，但是允许为 NULL。一张表允许创建多个唯一索引。

  3. 普通索引(Index)：就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

  4. 联合索引：多列值组成一个索引，可实现在满足最左前缀匹配原则下进行联合查询，索引覆盖避免回表。

最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。

为什么遇到（ >、<）会停止匹配？

因为联合索引的存储，只有第一个字段是全局有序的，之后的字段都是在前一个或前多个字段不变的基础上，才有序。

为什么 >=、<=、BETWEEN 以及前缀匹配 LIKE不会停止匹配？

因为虽然在符合 >=条件的索引记录的范围里，b 字段的值是「无序」的，但是对于符合=的索引记录的范围里，b 字段的值是「有序」的。

什么是索引覆盖？

覆盖索引是一种避免回表查询的优化策略，在非聚簇索引上就能获取SQL所需的所有列数据，不需要再去聚簇索引表查询所需数据，减少磁盘IO次数，速度更快。 具体的实现方式：将被查询的字段建立联合索引。

什么是索引下推

索引下推主要是为了减少回表次数，提升性能。实现将过滤条件从server层下推到存储引擎层，是在取出非聚簇索引对应的数据时，先进行where过滤，得到匹配后的数据再回表查询，从而减少回表次数。

索引数据结构

为什么InnoDB存储引擎使用B+树作为索引

  1. 哈希表中的数据存储是无序的，无法进行范围查询
  2. 对于二叉树，这些树的层高相对较高，每一层都可能需要从磁盘中加载新的节点，因此树越高，查找数据的时候意味着需要更多的磁盘IO。
  3. 对于多叉树B树和B+树，内存和磁盘进行IO时，有一个最小的逻辑单元页，页的大小一般是4kB。B树和B+树可以在一个节点中存储更多的数据信息，通常和页的大小对齐，使树变的更加"矮胖"，来减少IO次数。

索引为什么选择b+树不是b树

  1. B树的叶子节点和非叶子节点都存储了数据，B+树的非叶子节点上只存储键值，可以存储更多的键值对，相对而言b+树就更加矮胖，磁盘IO就更少了；
  2. B+树的叶子节点构成了一个有序链表，范围查询的时候可以直接通过叶子节点之间的指针访问范围内的记录，不需要对树进行多次遍历，查询效率会更高。
  3. 对于B+树，任何查找都是根节点到叶子节点的过程，查询效率更加稳定。

B+树查询的时间复杂度是多少?

理论分析来看是 B+树的深度*查询每个节点的时间复杂度。假设一个节点上存m个数据，则深度就是logmN，节点内部用链表组织，则查询每个节点的时间复杂度为m，所以理论分析的时间复杂度是O(mlogmN)。

但是B+树一般用于数据库和文件系统存储，对于这种场景，IO是最消耗时间的，和IO相比，CPU访问节点的时间可以忽略，那对于B+树，IO访问次数就是树的深度O(logmN)

时间复杂度的底数m是什么，怎么计算?

m就是每个中间节点存在的索引数量。

具体计算为：

$$ m=\frac{4KB(页大小)}{索引大小+指针大小} $$

指针的大小取决于系统的寻址能力。比如在32位系统中，指针是4字节，而64位系统则是8字节。现在大部分现代系统都是64位的，所以默认使用8字节作为指针大小可能是常见的情况。

什么时候用hash索引，什么时候用B+树索引?

哈希索引（Hash Index）优点是等值查询的时间复杂度为 O(1)，速度极快。局限性在于不支持范围查询，以及哈希冲突。

使用场景：

  1. 高频等值查询：例如根据主键（如用户ID、订单ID）快速检索单条记录。
  2. 内存数据库：数据完全存储在内存中（如Redis），哈希索引的随机访问优势明显。

案例：

  • MySQL 的 MEMORY 存储引擎默认使用哈希索引。
  • Redis 的键值存储基于哈希表实现。

B+树索引（B+Tree Index）优势是查询、插入、删除的时间复杂度为 O(log N)，性能稳定。并且支持范围查询，磁盘友好，IO次数少

适用场景：

  1. 磁盘存储系统：如传统关系型数据库（MySQL InnoDB、PostgreSQL）的默认索引。
  2. 事务型数据库（OLTP）：需要支持复杂查询和事务的隔离性。

实际选择建议

  1. 优先选择 B+树索引：
    • 大多数业务场景需要范围查询或排序。
    • 数据存储在磁盘，且需要高吞吐量的混合读写操作。
    • 需要支持联合索引的部分匹配（最左前缀原则）。
  2. 选择哈希索引：
    • 纯内存数据库（如Redis），且仅需等值查询。
    • 无需事务支持的高并发点查场景（如缓存键值对）。
  3. 混合使用：
    • 例如 MySQL InnoDB 的自适应哈希索引：在B+树基础上，对热点数据自动创建哈希索引，兼顾范围查询和热点数据检索效率。

MySQL索引为什么使用B+树而不用跳表?

  1. 磁盘I/O效率与数据局部性

    B+树是多路平衡树，每个节点能存储大量键值，树的高度通常只有3-4层。例如，一个3层B+树能轻松索引千万级数据。这种结构使得范围查询或全表扫描时，通过叶子节点的链表顺序访问相邻数据，能大幅减少磁盘I/O次数。而跳表的索引层级是随机分散的，范围查询可能需要多次随机磁盘寻道，这在磁盘存储中效率较低。并且B+树节点和磁盘页适配（如16KB），每次读取一页能加载大量键值，充分利用预读特性。跳表的节点大小不固定，可能导致单次I/O读取的有效数据量降低，增加磁盘访问次数。

  2. 范围查询的天然优势

    B+树的叶子节点构成有序链表，例如执行WHERE id BETWEEN 100 AND 200时，只需定位到100的位置，然后顺序遍历链表即可。跳表虽然也能范围查询，但需要从高层索引逐层下探到具体节点，过程中可能产生更多随机访问，尤其数据在磁盘上时效率差距更明显。

  3. 稳定的查询复杂度

    B+树的所有操作（插入、删除、查询）时间复杂度严格为O(log n)，且通过节点分裂合并保持平衡。跳表依赖概率随机生成索引层数，虽然平均复杂度也是O(log n)，但可能出现极端情况（如大量数据集中在低层链表），导致性能波动，这对数据库的稳定性是潜在风险。

为什么磁盘IO最花时间?

磁盘的物理特性导致IO操作要比内存读写花费更多时间。

因为每次IO操作都需要磁盘寻址，如果是机械磁盘，磁头要移动到目标扇区。

为什么非叶子节点的大小 = 页大小?

B+ 树在搜索过程中，需要从磁盘IO来读取节点的数据。我们知道磁盘IO一次读取的数据大小为一页。

首先，非叶子节点的大小肯定是越大越好，越大，每一个非叶子节点就能够存储更多的索引，B+树相对就更加矮胖。

如果非叶子节点的大小 > 页大小，那么意味着搜索过程中，为了获取一个完整节点来查找数据，我们需要多次IO来获取这个节点，这显然非常消耗时间。所以非叶子节点的大小不能超过一页。

如果非叶子节点小于页大小，这就意味着B+树的分叉就少，深度变大。B+树利用指针构成的树形结构。意味着每个节点在磁盘上是不连续的，B+树的深度变大了，意味着搜索时访问的节点数量变多，IO次数也变多了。

因此，非叶子节点过大、过小，都会导致IO次数增加。所以最合适的非叶子节点大小 = 页大小。

三层B+树可以存储多少数据？

可以存储千万级别的数据。在Innodb存储引擎里面，最小存储单元是页，而一个页的大小默认是16KB。非叶子节点只存储主键ID，

假设主键类型为bigint，占用8Byte，指针可以设置为占用6Byte，总共14Byte。这样就可以算出一个非叶子节点大概可以存放16KByte/14Byte=1170个“主键+指针”的组合。 假设主键类型为int，占用4Byte，指针可以设置为占用6Byte，总共10Byte。这样就可以算出一个非叶子节点大概可以存放16KByte/10Byte≈1600个“主键+指针”的组合。 叶子节点存储主键和数据，这里我们假设我的一行数据大小是1K，那么我们一个叶子节点就可以存16KByte/10Byte=16条（行）数据

创建索引有哪些注意点

  1. 选择合适的列作为索引
    1. 为经常作为查询或者排序条件的列创建索引
    2. 以区分度高的列作为索引【区分度=不同值的个数/总个数；如果匹配数据很多，有的时候查询效率还不如全表扫描】
    3. 频繁更新的字段，不要作为索引【非聚簇索引还好一些，如果是聚簇索引，每次更新还要把索引数据和完整数据一起修改，代价比较大】
    4. 不建议用无序的值作为索引，这样维护索引的成本较大。【因为B+树是多路平衡树，如果用无序的值作为索引，很多情况下，插入数据在B+树已经满了的叶子节点数据范围中，那就肯定导致页分裂，很多数据都要发生移动，严重影响性能；如果数据是有序的，那么每次插入数据通常发生在节点的末尾，】
  2. 避免建立太多的索引
    1. 首先，索引本身也要占用额外的磁盘空间
    2. 第二，索引维护也需要成本
    3. 优化器在选择执行计划的时候，会对每一个可以用到的索引进行评估，索引数量多了，就会增加优化器生成执行计划的时间，降低查询性能
  3. 尽量考虑联合索引，并且根据查询条件将最常作为过滤条件的列放在前面。首先每个索引都对应着B+树，使用联合索引，多个字段在一个索引上，可以节约磁盘空间；并且联合索引有时可以完成索引覆盖，减少回表次数，提高查询效率。
  4. 避免冗余索引。

对名字这一列建立索引，有什么考虑的点。

因为名字的字段类型为字符串varchar、char、text等等，可以考虑使用前缀索引，减少索引占用的占用空间。但是对于中国人的名字区分不是很大，所以用处不是很大。但是对于外国人的名字或者电子邮箱，还是有用处的。

前缀索引建立有什么注意点吗？

建立前缀索引和普通索引的区别就在于是否设置索引长度。

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

索引什么时候会失效？

  1. 使用联合索引的时候，没有遵循最左前缀匹配原则。比如左模糊匹配
  2. 在索引列上进行计算、函数、类型转换等操作（如果索引列是字符串，并且条件语句中输入参数是数字，那么索引列会产生隐式类型转换，导致索引失效，反之索引列是数字，输出参数是字符串，那么不会失效。）
  3. 查询条件中使用OR,如果OR前后条件中有一个列没有索引，涉及到的索引都不会使用到
  4. 索引和查询范围关系也很大，有可能范围过大造成索引没有意义从而失效的情况也不少。IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同)；
    1. 全表扫描比使用索引更快，也不会使用索引。通常是小表查询或者表中大部分数据都满足查询条件，因为索引扫描需要多次查找索引树，而全表扫描只需要遍历一次整个数据表，那么使用索引反而会增加查询时间。

为什么不用性别字段做索引

性别字段的区分度不高，筛选出的数据量还是很多，从性别这个非聚簇索引筛选出的数据，还需要进行一次回表操作查询，IO次数多。访问非聚簇索引和回聚簇索引表查询数据，加起来的开销并不比全表扫描小。

如何知道语句是否走索引查询

可以使用 EXPLAIN命令来分析 SQL 的 执行计划 ，EXPLAIN并不会真的去执行相关的语句，而是通过查询优化器 对语句进行分析。

要知道是否走索引查询，通常需要关注：

  1. possible_keys 可能用到的索引
  2. key 实际用到的索引
  3. key_len所选索引的长度
  4. Extra附加信息

联合索引为什么要遵循最左匹配原则?

To be a better Javaer.
Copyright © 2025 Cutewr