文章目录

  • 参考文章
  • 1.索引概述
  • 2.索引数据结构
  • 2.1.为什么不用其他数据结构
  • Hash索引
  • 二叉树 / 红黑树
  • **B树（BalanceTree）-  多路平衡查找树**
  • **2.2.MySQL采用的索引结构：B+树**
  • 3.五种索引类型
  • 4.聚簇索引和非聚簇索引
  • 4.1.聚簇索引（聚集索引）
  • 4.2.非聚簇索引（非聚集索引、辅助索引、二级索引）
  • 5.两种引擎的索引特性

1.索引概述

  • 索引是一种可以加快随机查找和范围查找效率的特殊数据结构，有效降低数据库的I/O成本；
  • MySQL的索引结构是B+树，特点是非叶子结点不存储数据。
  • 索引本身存储在磁盘上的文件中：
    – MyISAM：xxx.myi，只存储索引；
    – InnoDB：xxx.myd，存储索引和数据；
  • 索引并非全是优点：
    – 优点：提升随机查找和范围查找效率；
    – 缺点：占用磁盘空间，且降低表更新的效率，因为每次增删改，索引也要随之更新；
  • 索引分为五种类型：主键索引、唯一索引、组合索引、普通索引和全文索引；
  • 聚簇索引只存在于InnoDB，key的选择顺序：主键 > 唯一键 > row_id。
  • 非聚簇索引在查询完整数据时必定会回表（二次查找）
    – MyISAM：根据地址查找完整数据
    – InnoDB：根据key在聚簇索引中查找其他字段值

2.索引数据结构

2.1.为什么不用其他数据结构

Hash索引

  • 所有数据存在内存中，浪费内存空间；
  • Hash是无序的，只能等值查询，无法实现范围查询

二叉树 / 红黑树

随着结点不断增加，树的深度也会不断增加，最终会导致I/O次数过多。

B树（BalanceTree）-  多路平衡查找树

mysql索引一定快吗 mysql索引有什么_mysql索引一定快吗

mysql索引一定快吗 mysql索引有什么_mysql索引一定快吗_02

特点：

  • 所有键、值分布在整棵树当中；
  • 数据可以分布在非叶子结点中，性能逼近二分查找。

缺点：

由于非叶子结点也有Key和Data，当Data过大将会导致结点存储的Key数量变得很小（每个结点只存储16k），这同样会导致树的深度加大，从而增加I/O次数。

2.2.MySQL采用的索引结构：B+树

mysql索引一定快吗 mysql索引有什么_mysql_03

mysql索引一定快吗 mysql索引有什么_结点_04

特点与优点：

  • 非叶子结点只存储key和指针，叶子结点中存储key和data - 避免data过大时导致深度过高，提升随机查找的效率；
  • 叶子结点之间两两相连 - 提升范围查找的效率。

3.五种索引类型

  • 主键索引：建表时自动对主键创建；
  • 唯一索引：建表时若没有主键，就会对唯一键创建唯一索引；
  • 组合索引：为多个列建立索引，涉及的知识点是（针对组合索引的）最左匹配原则；
  • 普通索引：为普通字段建立的索引；
  • 全文索引

最左匹配原则：假设创建了组合索引（name, age），有以下四条SQL语句：

√ SELECT * FROM student WHERE name = 'zs'; × SELECT * FROM student WHERE age = 14; √ SELECT * FROM student WHERE name = 'zs' AND age = 14; √ SELECT * FROM student WHERE age = 14 AND name = 'zs';

只有1、3、4会使用索引，而4会使用索引是因为SQL优化器优化了SQL的处理顺序，使之顺序调整成了3。

4.聚簇索引和非聚簇索引

4.1.聚簇索引（聚集索引）

使用InnoDB引擎时，只要表被创建，就必定会建立一个聚簇索引；

聚簇索引中，叶子结点的data存储的是非key的其他字段值；

而聚簇索引中key的选择顺序为：主键 > 唯一键 > MySQL生成的row_id，即

  • 有主键时，对主键创建聚簇索引；
  • 没有主键时，对唯一键创建聚簇索引；
  • 既没有主键也没有唯一键时，在MySQL底层生成一个6位的row_id来创建聚簇索引。

据此便有以下结论：

  • 主键索引一定是聚簇索引；
  • 唯一索引不一定是聚簇索引（若有主键索引，那么这个唯一索引是后续建立的非聚簇索引）；
  • 聚簇索引不一定是主键索引（有可能是唯一索引）。

4.2.非聚簇索引（非聚集索引、辅助索引、二级索引）

使用MyISAM引擎时，只要表被创建，就必定会建立一个非聚簇索引；

mysql索引一定快吗 mysql索引有什么_结点_05

使用InnoDB引擎时，在表创建完毕后手动再建立的索引都是非聚簇索引；

mysql索引一定快吗 mysql索引有什么_mysql_06

针对不同的引擎，非聚簇索引的叶子结点data存储的数据有所不同：

  • InnoDB：存储聚簇索引中该结点的key；
  • MyISAM：存储这条记录的地址。

使用非聚簇索引查找完整的数据时，总是要回表（二次查找）

回表：说白了就是二次查找。常见的两种回表场景：

  • MyISAM：在非聚簇索引中根据key查出地址后，再根据地址获取完整数据；
  • InnoDB：在非聚簇索引中查出聚簇索引的key后，再根据key在聚簇索引中获取其他字段值。

顺带一提，避免回表的过程，被称作索引覆盖

索引覆盖 / 覆盖索引

假设对name字段建立了索引，此时看以下两条SQL语句：

SELECT * FROM student WHERE name = 'zs'; SELECT id FROM student WHERE name = 'zs';

第一条SQL语句在name对应的非聚簇索引树中找到id值后，需要通过回表获取剩余字段值；

第二条SQL语句在找到id后不需要再回表了，即此次查询触发了索引覆盖。

5.两种引擎的索引特性

  • InnoDB在建表时，一定会创建一个聚簇索引（主键 > 唯一键 > row_id）；
  • MyISAM在建表时，一定会创建一个非聚簇索引，叶子结点存储完整数据的地址。