数据库索引相关 ｜ 青训营笔记

这是我参与「第五届青训营 」伴学笔记创作活动的第 23 天

今天学习了数据库索引相关知识

聚簇索引和非聚簇索引的根本区别：

• 聚簇索引中，表数据和索引数据是按照相同顺序存储的，非聚簇索引则不是。
• 聚簇索引在一张表中是唯一的，只能有一个，非聚簇索引则可以存在多个。
• 聚簇索引在逻辑+物理上都是连续的，非聚簇索引则仅是逻辑上的连续。
• 聚簇索引中找到了索引键就找到了行数据，但非聚簇索引还需要做一次回表查询。

InnoDB-非聚簇索引与MyISAM-非聚簇索引的区别：

• InnoDB中的非聚簇索引是以聚簇索引的索引键，与具体的行数据建立关联关系的。
• MyISAM中的非聚簇索引是以行数据的地址指针，与具体的行数据建立关联关系的。

一般来说，由于MyISAM引擎中的索引可以根据指针直接获取数据，不需要做二次回表查询，因此从整体查询效率来看，会比InnoDB要快上不少。

• 读取数据会根据局部性原理读取一整块数据：其实就是指MySQL一次磁盘IO不仅仅只会读取一条表数据，而是会读取多条数据，那到底读多少条数据呢？在InnoDB引擎中，一次默认会读取16KB数据到内存。
• 实际上具有唯一性的最好给定唯一约束，因为唯一的值在查找到其中一条后就会停止

使用B+树作为数据结构的辨析

• 二叉树

  • 运气不好的时候会退化成链表
  • 由于树结构在磁盘中，各节点的数据并不连续，因此无法利用局部性原理。

• 红黑树

  • 虽然对比二叉树来说，树高有所降低，但数据量一大时，依旧会有很大的高度。
  • 每个节点中只存储一个数据，节点之间还是不连续的，依旧无法利用局部性原理从而减少磁盘IO

• B-树

  • 虽然对比之前的红黑树更矮，检索数据更快，也能够充分利用局部性原理减少IO次数，但对于大范围查询的需求，依旧需要通过多次磁盘IO来检索数据（每个结点虽然有几个数据，但是架不住大量查询）

• B+树

  • 数据只存放在叶子结点上，非叶子结点相当于路标，并且在B+树基础上又添加了反向指针便于逆序查找结果

B+树能够存储的数据量计算相关细节

• 原本64bit的指针过多，MySQL将其指针进行压缩为6Byte
• 前缀索引之所以查找效率高就是因为其索引所占的数据量小，单个结点能够存储的索引多，进而磁盘IO少

MyISAM存储的表实际结构

• zz_myisam_index.frm：该文件中存储表的结构信息。
• zz_myisam_index.MYD：该文件中存储表的行数据。
• zz_myisam_index.MYI：该文件中存储表的索引数据。

也就是说，MyISAM引擎的表数据和索引数据，是分别放在两个不同的磁盘文件中存储的，这也意味着MyISAM引擎并不支持聚簇索引，因为聚簇索引要求表数据和索引数据一起存储在同一块空间，而MyISAM的.MYI索引文件中，存储的是表数据所在的地址指针

INNODB的存储的表的实际结构

• zz_innodb_index.frm：该文件中存储表的结构信息。
• zz_innodb_index.ibd：该文件中存储表的行数据和索引数据。

因为InnoDB引擎中，表数据和索引数据都一起放在.ibd文件中，也就代表着索引数据和表数据是处于同一块空间存储的，这符合聚簇索引的定义，因此InnoDB支持聚簇索引

同时也正由于这个原因，所以如果使用InnoDB引擎的表未主动创建聚簇索引，它会自动选择表中的主键字段，作为聚簇索引的字段。但如果表中未声明主键字段，那则会选择一个非空唯一索引来作为聚簇索引。但如果表中依旧没有非空的唯一索引，InnoDB则会隐式定义一个主键来作为聚簇索引（这个列在上层是不可见的，是一个按序自增的值）

插入删除数据相关

• 插入优先插入聚簇索引，删除最后删聚簇索引（否则找不到对应位置）