索引
索引的本质:
- 帮助MySQL高效获取数据的排好序的数据结构
演示数据结构网址:https://www.cs.usfca.edu/~galles/visualization/BTree.html
为什么不用以下数据结构?
-
二叉树(二叉搜索树)
- 特殊情况下,树会退化成链表,查询效率是O(n)
-
平衡二叉树(每个节点的左右子树的高度差至多是1)
- 频繁的插入、删除性能会出现问题
-
红黑树(自动平衡)
- 树的高低过高,不便于进行查询
-
b树 – 解决了红黑树高度过高
叶节点具有相同的深度,叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到有递增排列
-
- 叶子节点之间没有双向指针,范围查找慢
- data元素各个索引列存在(树的高度高于B+树)
B+树特点:
- 非叶子节点不存储数据,只存储索引(冗余),可以放更多的索引
- 叶子节点包含所有的索引字段
- 叶子节点用指针连接(双向链表),提高区间访问的性能(范围查询)
相比B树而言,将数据存放到叶子节点,非叶子节点能存放更多的索引(MySql限制每页大小为16kb),因此存储相同的元素,B树的高度要比B+树要高
树的高度:由非叶子节点能存储多少元素决定的。
B+树会把根节点放入内存
聚簇索引和非聚簇索引
- 聚簇索引:将索引和数据放在一起(.ibd)
- 非聚簇索引:索引和数据分开,叶节点包含索引字段值以及指向数据页数据行的逻辑指针(.myi .myd)
Hash索引
- 对索引的Key进行一次hash计算就可以定位出数据存储的位置
- 很多时候hash索引要比B+树索引更高效
- 仅能满足“=” “IN”操作,不支持范围查询
- hash冲突问题
主键不选择UUID
不设置主键,会从表中选择一个列(列中元素不包含重复值),若没有,则新增一个隐藏列
- 使用自增的id结构(自增的主键的值是顺序的,所有InnoDB把每一条记录都存储在一条记录的后面,当达到页面的最大填充因子的时候,InnoDB默认的最大填充因子是页大小的15、16,会留出1、16的空间留作以后的修改)
- 下一条记录就会写入新的页中,一旦数据按照这种方式进行加载,主键页就会近乎顺序的记录填满,提升了页面的最大填充效率,不会有页的浪费
- 新插入的行一定会在原有的最大数据行下一行,mysql定位和寻址很快,不会为了计算新行的位置做出额外的消耗
- 减少了页分类和碎片的产生
- 使用UUID(InnoDB无法做到总是把新行插入到索引的最后,需要为新行寻找合适的位置从而来分配新的空间)
- 写入的目标页可能一家刷新到磁盘上并且从缓存中移除,或者还没有被加载到缓存中,innoDB在插入之前需要先从磁盘中读取目标页到内存,这将导致大量IO
- 因为写入的乱序的,innoDB可能要做页分裂,以便为新的行分配空间,页分裂导致移动大量数据,一次插入最少需要修改三个页以上
- 由于频繁的页分裂,页会变的稀疏并被不规则的填充,最终会导致数据会有碎片
为什么非主键索引结构叶子结构存储的是主键值
- 一致性(修改的时候要修改多分,主键索引数据,和非主键索引数据)
- 节省内存空间(如果索引全部存行记录,多个索引全部存行记录,浪费空间)
联合索引的底层存储结构
尽量建立联合索引
索引最左前缀原理
- 先根据name、再根据age最后根据position进行排序
一颗高度为3的B+树能存多少行数据
InnoDB存储引擎最小的存储单元是页(Page)
一个页的大小默认是16K
-
假设一行的大小为1k,那么一页可以存放16行这样的数据。
-
除了存放数据的页(叶子节点),还有存放键值+指针(索引)的页(非叶子节点)
-
假设B+树高为2,即存在一个根节点和若干个叶子节点,没有页节点,那么这颗B+树的存放总记录数为:根节点指针数 * 单个叶子节点记录行数
-
假设采用bigint作为主键,长度为8个字节,指针大小在InnoDB中设置为6个字节,一共14个字节
-
一个页中能存放的指针数 (16 * 1024) / 14 = 1170,即能存放1170个索引
-
那么存放的数据记录为 1170 * 16 * 1170 = 21902400
高度为2的树存放的记录数为2w左右(18720)
高度为3的树存放的记录数差不多是2千万(21902400)
B+树的高度一般为2~4层