3 MySQL索引

3 MySQL索引

• 索引
  • 存储结构
  • SQL语句
• 数据结构及算法
  • B-Tree
  • B+Tree
  • 为什么使用B-Tree(B+Tree)
• MySQL索引实现
  • MyISAM
  • InnoDB
• 索引使用策略及优化
  • 查询优化
  • 索引选择性和前缀索引

索引

存储结构

• 存储结构（InnoDB）（16kB）
  • File Header（38字节）
  • Page Header（56）
  • Infirmum+supermum（行记录）
  • User Records（行记录）：存储插入的记录
  • Free Space
  • Page Directory
  • File Tailer
• 各个数据页组成一个双向链表，每个数据页中的记录组成一个单向链表
• 每个数据页都会为存储在其里面的记录生成一个页记录，在通过主键查找某条记录的时候可以在页目录使用二分法快速定位到对应的槽，然后遍历该槽对应分组中记录即可快速找到指定的记录
• 如果以非主键列作为搜索条件，只能从最小记录开始遍历
• 索引：将无序记录变为有序，每次在B+树中进行查找对应的页，而不是遍历双向链表定位到具体页

SQL语句

• 添加primary key

  alter table 'tableName' add primary key ('column')
  

• 添加unique

  alter table 'tableName' add unique('column')
  

• 添加索引

  alter table 'tableName' add index indexName ('column')
  

• 添加fulltext（全文索引）

  alter table 'tableName' add fulltext ('column')
  

• 添加多列索引

  alter table 'tableName' add index indexName ('column1','column2'...)
  

数据结构及算法

B-Tree

• 树的度d大于1
• 树的高h为一个正整数
• 每个非叶子节点由n-1个key和n个指针组成，d<=n<=2d
• 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶子节点的指针均为null
• 所有叶节点具有相同的深度，等于树高h
• key和指针互相间隔，节点两端是指针
• 一个节点中的key从左到右非递减排列
• 每个指针要么为null，要么指向另一个节点
  
• 度为d的B-Tree，索引N个key，则树高
  $$lo{g}_d((N+1)/2)$$
• 查找节点个数的时间复杂度
  $$O(lo{g}_{d}N)$$

B+Tree

• MySQL普遍使用

• 每个节点的key数目和d相同

• 内节点不存储data，只存储key

• 叶子节点不存储指针

• 带有顺序访问指针的B+Tree
  

为什么使用B-Tree(B+Tree)

• 索引往往以索引文件的形式存储在磁盘上，每次进行索引查找都要产生IO
• 磁盘读取数据时，依据局部性原理，每次会预读，长度一般为页（计算机存储器的逻辑块，大小为4k）的整倍数
• B-Tree检索一次做多要访问h个节点，将每个节点的大小设为等于一个页，这样每次只需要一次IO就可以完全载入
  • 实现技巧：新建节点时直接申请一个页的空间
• 由于根节点常驻内存，索引一次索引最多需要h-1次IO，渐进复杂度为O（logN）
• 而红黑树，h明显要深，逻辑上很近的节点物理上可能很远，无法利用局部性原理，渐进复杂度为O（h），效率明显要差很多
• B+Tree内节点去掉了data域，对于同样大小的页可以存放指针越多，对应的度也越多，而d越大，渐进复杂度也越小，因此查找效率高一些

MySQL索引实现

• 索引术语存储引擎层次的概念，不同的存储引擎的实现方式是不同

MyISAM

• 使用B+Tree，叶节点的data存放数据记录的地址（由这里可知其索引文件和数据文件是分开存放的）
• 主键对应主索引，不可重复
• 一般索引对应辅助索引，其key可重复
• 该索引方式也叫做**“非聚集”**的

InnoDB

• 采用B+Tree作为索引结构
• 数据文件即为索引文件，data直接存放数据记录，故叫**“聚集索引”**
• 因为数据文件本身按主键聚集，所以要求有必须有主键，如果建表时没有显示指定，则会自动选择一个可以唯一标志数据的列作为主键，如果不存在这种列，会自动生成一个隐含字段作为主键
• 辅助索引data存放的是主键的值而不是地址,因此辅助索引需要两次索引，第一次索引辅助索引，第二次依据第一次获得的主键值索引查找
  • 太长的主键会使辅助索引变得过大

索引使用策略及优化

• MySQL有哈主要分为结构优化和查询优化

查询优化

• 联合索引：以一定顺序引用多个列

假设联合索引（A,B,C)

• 全列匹配：顺序无所谓，因为MySQL查询优化器会自动调整
• 最左前缀匹配：匹配索引左边的连续一个或几个列,即（A）或者（A，B）
• 查询条件使用精确匹配但没有提供某个中间条件，如（A，C)，此时使用的索引只有A
  • 在中间索引种类较少的情况下，可以使用In子句来保证select使用全部索引ABC，性能会有所提升
• 查询条件没有指定第一列A的话，此时不会使用索引
• 使用like匹配某列的前缀，此时可以用到索引，但是%不能出现在最前面，如“%三”，此时是无法使用索引的
• 范围查询：范围查询必须满足最左匹配，且范围查询后的语句无法使用索引（两个范围查询只能使用第一个，后面的无法使用索引）
  • 注意between，并不一定等于范围查询，有时会是多值匹配
• 使用函数或者表达式的话也不会使用索引

索引选择性和前缀索引

• 索引的建立消耗存储空间，同时会加重维护负担

• 不建议使用索引的情况

  • 表记录比较少
  • 索引选择性较低

• 索引选择性：指不重复的索引值与表记录的比值

• 前缀索引：用列的前缀替代整列作为索引，一方面可以提高索引选择性，另一方面因为索引长度变短可以减少索引文件的大小和维护开销

  alter table tableName add index indexName (column1,column(num))
  

• InnoDB建议使用自增主键，因为其使用聚集索引，数据记录直接存放于主索引的叶子节点上，在新插入数据的时候，由于使用自增主键可以顺序添加到索引节点的后续位置，不需要移动已有数据；使用非自增主键，每次插入近似于随机插入，可能导致频繁移动数据