【MySQL 高级索引】索引详解 2020_01_03

最新推荐文章于 2024-10-28 15:18:13 发布

今心木目

最新推荐文章于 2024-10-28 15:18:13 发布

阅读量279

点赞数 1

分类专栏： MySQL 高级

本文链接：https://blog.csdn.net/a1051826842/article/details/103827248

版权

MySQL 高级专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL 索引详解

索引

索引

索引是数据结构。索引（Index）是帮助 MySQL 高效获取数据的数据结构。

**索引的作用是对数据排序、提高查询速度。**类比图书馆藏书类别，跳过全部书籍扫描，精准定位到某小区域的数据。

不同的索引会影响到 OrderBy(排序) 与 Where(条件查询) 运行效率。

数据库除维护数据以外，还维护着拥有特定查找算法的数据结构，这些数据结构存储数据地址来指向真实数据 =》索引

案例图解

在这里插入图片描述

左侧 16进制：是索引【数据结构】存储的真实数据的物理地址【指针】
Col1：为索引【数据结构】的全局唯一 ID【主键】
Col2：为索引【数据结构】的真实数据的别名/代称【关键字】

由此，为了加速 Col2 的查找可以维护一个右边所示的二叉查找树，每个节点分别包含：关键字 Key 与指向真实数据的物理地址 Address，这样就可用二叉查找在一定的时间复杂度中查询到相应真实数据地址，从而获取数据值，而不再是全数据扫描检索。

特性

索引本质是对数据的简化（由地址值代替真实值）排序，索引自身不小，也就不可能全部存储在内存中，因此索引也通常以索引文件的形式存储在磁盘中。
其次，索引为了维持自身指向的有效性，也要随真实数据的增删改发生改变（无效数据，新增数据，越来越臃肿）也就是——索引查询会随数据量增加变的越来越慢。
最后，仍是因为维持索引的有效性，那么就会在真实数据更新操作的同时更新索引指向，故此索引会降低更新效率。

也就是——索引会多次重新构建，去除已删除数据的索引、无效的地址指向等（通常情况下，我们只是逻辑删除，为了保证完整历史操作）

优劣

索引优势

1.提高数据查找速度，减少磁盘IO次数
2.通过索引来对数据排序，降低查询时 OrderBy 子句对 CPU 消耗，提高 OrderBy 子句的查询速度。

索引劣势

1.索引实际上也是个表，存储唯一主键、字段值别值、真实字段值地址，所以索引也会占用内存磁盘资源。
2.索引提升查询速度，同时也会降低更新表速度。（更新真实数据的同时也要更新索引表）
3.索引不可能为每个字段及其他们的所有组合都建立一个索引，故此，就要花时间研究建立 最有效索引 或 优化索引。

类别

单值索引：一个索引只包含单个列，一个表可有多个单列索引【频繁查询的单个列，很少】
唯一索引：索引列的值必须唯一，但允许有空值
主键索引：设定为主键后数据库会自动建立主键索引，innodb为聚簇索引
复合索引：一个索引包含多个列【大多数是多个列作为查询条件】

数据结构

平常未特别说明的 “索引” 结构指代都是 B-Tree（多路搜索树，不一定是二叉树，如三叉）结构的索引。聚集索引，次要索引，前缀索引，唯一索引，默认都是使用 B+树索引，统称索引。除 B+Tree 结构外，还有哈希结构索引(不是重点)等。

B-Tree

在这里插入图片描述

上图为一颗 B-Tree(2叉)，一个节点拥有3个属性，一个为黄色指针（指向下一个节点），一个为蓝色数据项（数值），一个为真实数据的红色地址（磁盘地址）。浅蓝色为 磁盘Block（代指一个真实的物理磁盘区域/扇面/磁道）。

特性：
叶子与非叶子节点均存储真实数据的地址，叶子节点只存储数据项及地址，
二叉对 P1，P2，P3 的定义为：P1 [ -∞，叉1)，P2(叉1，叉2)，P3(叉2，+∞]

查询过程：
如果查询 17 / 35 则只会进行1次磁盘IO，第一层根节点即可找到。如果查询 26 则只会则只会进行2次磁盘IO，第一次磁盘IO 加载磁盘块1，然后在内存中使用二分查找，确定 26 在(17,35)间，进行第二次磁盘IO，在内存中使用二分查找，找到 26。依次类推。

B+Tree

在这里插入图片描述

上图为一颗 B+Tree(3叉)，一个节点拥有2个属性，一个为黄色指针（指向下一个节点），一个为蓝色数据项（数值）
浅蓝色为 磁盘Block（代指一个真实的物理磁盘区域/扇面/磁道）。

特性：
真实数据仅存在于叶子节点，非叶子节点仅存储指向真实数据的指针(黄) 及指引搜索方向的数据项(蓝)，
三叉对 P1，P2，P3 的定义为：P1 [ 叉1，叉2)，P2(叉2，叉3)，P3(叉3，+∞]

查询过程：
查找数据项 28 的真实数据值。第一次磁盘IO 加载磁盘块1，在内存中使用二分查找，确定 29 在(17,35)间，然后进行第二次磁盘IO 加载磁盘块3，再在内存中使用二分查找，确定 29 在(26,30)间，然后进行第三次磁盘IO 加载磁盘块8，再在内存中做二分查找找到29，结束查询，总计三次IO。这对于百万行数据来说，相较全表扫描是巨大提升。

B-Tree/B+Tree 区别

1）B-Tree 关键字、指针、记录 是放在一起的；B+Tree 非叶子节点中只有关键字、指针，叶子节点只有记录。

2）B-Tree，越靠近根节点的记录查找时间越短；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。

B+Tree 为何更适合作为索引结构

因为，B+Tree 非叶子节点只存储关键字与指针，没有B-Tree的记录，也就是，将B-Tree非叶子节点的空间也用于存储更多的关键字与指针，那么 B+Tree 的非叶子节点就比 B-Tree 非叶子节点能存放更多的关键字与指针 ——》也就是，B+Tree 的层级要少于 B-Tree 的层级。这样带来的好处是减少磁盘IO次数。（磁盘IO一次耗时要远远大于内存中做二分查找运算）

故此，尽管B+Tree找到一个记录所需的比较次数要比B-Tree多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际测试中B+树的性能可能还会更好，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历查询全表，这也是很多数据库和文件系统使用B+树的缘故。

总结： B+树的磁盘读写次数更少（代价更低）， B+树的查询效率更加稳定。

补充·聚簇索引与非聚簇索引

聚簇索引，真实的含义是，根据索引相近而聚簇存放的数据行 —— 一种数据存储规则/方式
术语 ‘聚簇’：表示索引列Key的相邻数据行的Value聚成一簇存储在一起。

在这里插入图片描述
优点
按照聚簇索引排列顺序查询范围数据时，由于数据都是紧密相连，不用从多个磁盘块中提取数据，所以减少大量磁盘IO
限制
MySQL目前只有 Innodb 数据引擎支持聚簇索引，而 Myisam 并不支持。
由于数据的物理存储排序方式只能有一种，所以每个表只能有一个聚簇索引。一般情况下就是该表的主键