MySQL 索引原理以及 SQL 优化

索引是什么

• 索引是一种有序的存储结构，按照单个或者多个列的值进行排序。
• 索引用来帮助数据库组织数据。
• 索引的目的：提升搜索效率。
• 索引映射的是磁盘数据。

---

索引的分类

• 数据结构：
  • B+ 树索引（映射的是磁盘数据）。
  • hash 索引（快速锁定内存数据）。
  • 全文索引：
    • 将存储在数据库中的整本书和整篇文章中的任意内容信息查找出来的技术。
    • 在短字符串中用 LIKE %；在全文索引中用 match 和 against。
    • 一般使用 elasticsearch。
• 物理存储：
  • 聚集索引（聚簇索引）：主键索引对应的索引。（B+树包含主键 ID 和表数据）
  • 辅助索引（二级索引）：除了主键索引之外的其他索引。（B+树只包含 key 和主键 ID）
  • 回表查询：辅助索引 B+ 树通过 key 查找到主键 ID，然后通过主键 ID 查找聚簇索引 B+ 树从而得到表记录。

    struct zcoder_tb {
           
    	int id;       // primary key
    	string name;  // key
    	string phone; // key
        short age;
    }; 
    
    map<int, zcoder_tb> // 聚簇索引
    map<string, int>    // 辅助索引
    map<string, int>    // 辅助索引
    

• 列属性：
  • 主键索引：非空唯一索引，一个表有且仅有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息。

    PRIMARY KEY(key1, key2)
    

  • 唯一索引：不可以出现相同的值，可以有 NULL 值。

    UNIQUE(key1, key2)
    

  • 普通索引：允许出现相同的索引内容 。

    INDEX(key1, key2)
    -- OR
    KEY(key1, key2)
    

  • 前缀索引：只比较长字符串的前几个字符。
• 列的个数：
  • 单列索引。
  • 组合索引：对表上的多个列进行索引。

  INDEX(key1, key2)
  UNIQUE(key1, key2)
  PRIMARY KEY(key1, key2)
  

---

索引的代价

• 存储空间占用的代价：
  • 有多个索引，就有多个 B+ 树。
  • 工程上限定：一个表最多只能创建 6 个索引。
• 维护 B+ 树的代价：
  • DML 操作变慢。
  • 如果修改的字段有索引（非聚簇索引），除了要修改聚簇索引 B+ 树，还要修改对应的辅助索引 B+ 树。

---

索引的使用场景

• where（可能使用）
• group by（可能使用）
• order by （可能使用）

---

不需要创建索引的场景

• 不使用 where$/$group by$/$order by。
• 列中的数据区分度不高
• 经常修改的列
• 表数据量少

---

innodb 中 B+ 树（多路平衡搜索树）

• 特征：
  • 非叶子节点只存储索引信息（只存储 key）。
  • 叶子节点还存储数据信息（存储 key 和 value）。
  • 叶子节点之间依次相连。
  • 节点的大小为 16 KB，映射的是连续的磁盘页（通过 mmap 映射磁盘数据）。
  • 一个叶子节点至少存储两行数据，如果某一行数据大于 16 KB，则会截取一部分数据进行存储，并保留一个地址位（记录另一个 B+ 树所对应的地址），然后把剩余的数据存储在另一个 B+ 树中。
• 为什么采用 “多路平衡搜索” 的树结构 ?
  • 一个节点多条链路，相较于平衡二叉搜索树是一个更加矮胖的结构，树的高度较低，较少的磁盘 IO 次数来索引数据。
  • 增删改通过平衡确保搜索时间复杂度稳定。
  • 中序遍历是有序的。
• 为什么非叶子节点只存储索引信息 ?
  • B+ 树节点映射固定大小的磁盘数据，可以包含更多的索引信息，能快速锁定数据所在叶子节点的位置。
• 为什么叶子节点依次相连，构成一个双向链表 ?
  • 便于范围查询，避免中序遍历回溯去查找下一个节点，降低磁盘 IO。
• 索引信息和数据信息的分层管理，便于高效地组织磁盘数据，快速实现单点和范围查询。

---

聚簇索引查找流程

select *