mysql 索引

1. 索引数据结构
   1. B+树主键索引‌

      1.                               [根节点]
                                       [10,  50]
                                      /     |     \
                         [内部节点1]        [内部节点2]         [内部节点3]
                         [5, 8]            [20, 30, 40]        [60, 70]
                        /   |   \          /   |   |   \        /   |   \
          [叶子1]       [叶子2]      [叶子3]        [叶子4]         [叶子5]       [叶子6]         [叶子7]
         [1,2,3,4] <-> [5,6,7] <-> [8,9,10] <-> [11,12..20] <-> [21..30] <-> [31..40,50] <-> [51..60] <-> [61..70, ...]
         (数据行)     (数据行)        (数据行)       (数据行)        (数据行)       (数据行)        (数据行)       (数据行)

      2. 结构：平衡多叉树演化而来，非叶子节点 + 叶子节点。
         1. 节点大小：每个节点就是一个页面默认16kb，可以配置。
         2. 非叶子节点：。
            1. 根节点和内部节点都属于非叶子结点
            2. 主要存储键值+指针。
            3. 每个索引项含8字节键值（bigint）+6字节指针 = 14字节‌。
            4. 单页可容纳索引项数量：16384B ÷ 14B ≈ 1170个
         3. 叶子节：
            1. 主要存储数据
            2. 数据之间是有序链表‌。
               1. 当执行WHERE条件包含范围操作符（如BETWEEN、>、<）时，有序链表可直接定位起始点并顺序遍历：
               2. 读取全表数据时（如无WHERE条件的统计查询），优化器优先选择主键索引而非全表扫描

               3. 对索引列排序时，直接复用链表顺序返回结果：

            3. 假设单行数据占1KB：单页可存储数据行数：16KB ÷ 1KB = 16行‌
         4. 三层索引数据大致容纳:
            1. 根节点1170项 → 中间层1170节点 → 叶子层1170×1170=136.89万节点
            2. 总数据行数：136.89万 × 16 ≈ 2190万行‌
            3. 因为层数越多，io加载次数越多，所以一般三层，表数据量2000万就够了，超过了就要考虑分表了。阿里建议表最大500万条
      3. 查询速度

         1. 数据加载机制‌

            1. 查询时需从磁盘加载索引页和数据页到内存（Buffer Pool），若I/O速度慢（如机械硬盘），加载延迟显著增加查询响应时间‌

            2. 例‌：扫描百万行数据分散在1000个磁盘页时，若I/O速度从100MB/s降至10MB/s，查询延迟可能从1秒增至10秒‌2。

         2. 索引检索依赖I/O‌

            • B+树索引查询需逐层加载节点页：根节点→非叶节点→叶节点，每一步均触发磁盘I/O‌23。
            • ‌高频问题‌：即使索引存在，若I/O性能不足（如云实例IOPS超限），仍导致慢查询‌

2. 索引功能分类

   1. ‌主键索引

      1. ‌唯一标识记录，不允许重复和NULL（如PRIMARY KEY(id)）‌

      2. 主键索引又被称为聚簇索引。叶子节点直接存储‌完整数据行‌，数据物理顺序与索引顺序一致‌
      3. 主键索引之外的索引都是非聚簇索引，叶子节点仅存储‌主键值‌（非数据行本身），需通过主键二次查找数据‌，又被称为回表。

   2. ‌唯一索引‌

      1. 确保列值唯一，允许多个，可包含NULL（如UNIQUE(email)）‌

   3. ‌普通索引‌

      1. 基础索引类型，无唯一性约束（如INDEX(idx_name)）‌

   4. ‌组合索引‌

      1. 多列联合索引（如INDEX(idx_user_status, user_id, status)）‌
      2. 最左前缀原则‌：查询条件需包含索引最左列（如WHERE user_id=100 AND status='paid'有效，但WHERE status='paid'无效）‌
         1. 联合索引 (a,b,c) 中，缺失左侧条件只使用b，c会导致索引失效
         2. 范围查询右侧的列无法使用索引‌：a = 1 AND b > 10 AND c = 3。c失效
         3. 对索引列进行运算、函数会导致失效
         4. 对索引列或隐式转换。如 数字类型使用字符串去查
         5. ‌左模糊匹配或者全模糊使索引失效‌：name LIKE '%oh' 或 name LIKE '%oh%'
         6. 使用or 可能会导致索引失效

3. ‌索引的一些优化概念

   1. 回表
      1. 当使用非聚簇索引查询时，若查询字段和索引字段不同，需通过主键回到聚簇索引获取完整数据行
      2. 要减少回表的查询，比如使用索引覆盖。

   2. 覆盖索引‌

      • 查询列完全被索引覆盖，避免回表（如SELECT user_id FROM orders WHERE status='paid'，若(status, user_id)为组合索引则直接返回结果）‌
   3. 索引下推
      1. MySQL 5.6+将WHERE条件‌下推‌到存储引擎层，在索引遍历时提前过滤数据，减少回表次数‌。

      2. select * from user2 where username like 'j%' and age=99;

         1. 不使用索引下推：先通过索引查出所有的username，然后回表查询所有的数据，然后过滤age

         2. 使用索引下推：通过username查询索引的时候，也会使用age进行过滤。然后将查询到的数据进行回表，大大减少了回表次数