MySql 索引

1. 索引分类

  • 普通索引
  • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 单个索引：一个列构成的索引。
  • 联合索引（复合索引）：对表的多个列建立索引。联合索引就是一棵 B+ 树，只是对多个列进行排序，比如 a，b 两个列建立联合索引，会先按 a 排序，再按 b 排序。联合索引的使用必须满足最左匹配原则，即查询从索引的最左列开始且不跳过索引中的列，**如果跳过，索引就会失效。**这是 B+ 树索引的一个特征。
  • 聚集索引（主键索引）：聚集索引是根据表的主键构建的 B+树，叶子节点存放行数据，也称为数据页，数据页之间用双向链表连接。
  • 辅助索引：辅助索引，其叶子节点不包含行记录的全部数据，只包含键值和书签（书签就是相应行数据的聚集索引键） ，可以通过聚集索引键找到相应行数据。

聚簇索引：Innodb 的索引结构，索引和数据一起存放。

mysql 组合索引匹配规则 mysql组合索引原则_主键


非聚簇索引：MyISAM 的索引结构，索引和数据分开存放，在索引中通过主键找到数据文件中行数据对应的物理地址，再获得行数据。

mysql 组合索引匹配规则 mysql组合索引原则_辅助索引_02

2. 覆盖索引

覆盖索引是指查询的列和索引列相同，可以直接从辅助索引获得查询的结果，而不用访问聚集索引，辅助索引中不包括整行的所有信息，大小远小于聚集索引，减少了大量 io 操作。

3. 索引实现

聚集索引是根据表的主键构建的 B+ 树，叶节点存放行数据，也称为数据页，数据页之间用双向链表连接。
而辅助索引的叶节点存放的是主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

  • B+ 树索引：使用 B+ 树来索引，是大多数 MySql 存储引擎的默认索引类型。
  • Innodb 存储引擎将 B+ 树索引分为聚集索引和 辅助索引，本质都是 B+ 树，聚集索引是根据表的主键构建的索引，叶子节点存放行数据，也称数据页，数据页之间通过双向链表连接。而辅助索引的叶节点存放的是主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
  • MyISAM 也使用 B+ 树作为索引存储结构，但他们叶子节点的存储方式有所不同，MyISAM 索引文件和数据文件是分离的，索引文件仅保存了记录所在页的指针。
    而 Innodb 直接存储数据或主键值。
  • 哈希索引：哈希索引能以 O(1) 时间进行查找，但失去了有序性：
  • 无法用于排序和分组
  • 只能精确查找，无法进行范围查找和部分查找

Innodb 的哈希索引是自适应的，它会根据表的使用情况自动为表生成哈希索引。

  • 全文索引：MyISAM 引擎支持全文索引，用于查找文本中的关键词。全文索引是使用倒排索引实现，它记录关键词到其所在文档的映射。Innodb 在 MySQL 5.6 版本中也开始支持全文索引。
    倒排索引：主要由单词词典和倒排文件组成，单词词典出现在内存中，是组成所有文档的单词的集合，单词文档的索引项记录了单词本身的一些信息和指向倒排列表的指针，通过这个指针可以找到倒排列表，倒排列表记录了出现了某个单词的所有文档的文档列表和单词在这些文档中出现的位置信息，每条记录称为倒排向项，而倒排文件是倒排列表在磁盘上的物理存储。

4. 索引的好坏

好处

  1. 加快查询的速度
  2. 保证数据的唯一性
  3. 加快多表连接的速度
  4. 减少分组和排序的时间

坏处

  1. 建立索引耗费时间
  2. 索引占内存
  3. 在对表中数据进行增加，修改，删除时，索引也要维护

5. 索引失效

  1. 使用 or 时，or 的两边都必须为索引，否则失效
  2. like 查询以通配符开头
  3. 联合查询必须满足最左匹配原则，否则失效
  4. 对索引做运算，使用函数，不等于，索引失效
  5. 数据类型出现隐式转换，如字符串不加引号，索引失效
  6. 如果使用全表查询比使用索引快，索引失效

6. 与红黑树比较

  1. 红黑树的出度为2，而 B+ 树的出度一般都非常大，导致红黑树的树高比 B+ 树大很多，相应红黑树的查询结果所需的次数也比 B+ 树多。
  2. 利用磁盘的预读特性：磁盘往往不是严格按需读取，而是每次都会预读，预读长度一般是页（4 K）的整数倍。而索引一个节点的大小和页的大小相同，使得一次 I/O 就能完全载入一个节点。（操作系统知识）

7. B 树与 B+树

B 树：它是一种多路平衡查找树，每个结点包含了 key 和 key 对应的指针，搜索一个对象可以不用到达叶节点。

B+ 树：是 B 树的变形，它的非叶节点只是索引部分，所有叶节点都在同一层上，包含全部数据，且叶节点按数据数据升序连接。

B+ 树更适合作为数据库索引：

  1. I/O 次数更少：B + 树的非叶节点不存放数据，因此节点相对 B树 就更小，相同内存可以放入更多的节点，所以相对 B树，I/O 次数更少。
  2. 范围查询：B 树进行范围查询，需要进行中序遍历，而 B+ 树只需要遍历叶节点就可以实现整个树的遍历。
  3. **查询效率稳定：**B+树查找必须从根节点到叶子节点，查询的路径相同，导致每一个数据的查询效率相当。

，而 B+ 树只需要遍历叶节点就可以实现整个树的遍历。
3. **查询效率稳定：**B+树查找必须从根节点到叶子节点，查询的路径相同，导致每一个数据的查询效率相当。