01深入MySQL索引

索引

索引的本质：

• 帮助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层