MySQL 索引相关

• 索引是帮助MySQL高效获取数据的排好序的数据结构。

• 索引存储在文件里

• 索引结构

  • × 二叉树 ：顺序情况(1,2,3,4,5,6,7…)会造成"畸形"树效率非常低

  • × 红黑树（二叉平衡树）：是一种不会出现“畸形”树的二叉树，但是假如有M组数据，那么设二叉树的高度为N即2的N次方 等于 M，如果要查询的数据恰好在树的最低端，索引就会进行N次磁盘的IO操作，每次磁盘的IO操作效率很低会导致查询效率低下。

  • √ HASH：MySql的默认两种索引方式之一，但是很少用，更多的是用B-TREE，原因是当进行范围查找时效率低下，例如：

    select .... where age > 10
    

    以HASH的特性是很难快速得进行范围查找

    当然如果是单点查询的话HASH的查询效率还是挺高的，

  • √ B-TREE：

    • 度（Degree）-节点的数据存储个数

  • 也节点具有相同的深度

    • 叶节点的指针为空

  • 节点的数据key从左至右递增排列

    • data 磁盘文件指针，通过磁盘文件指针获取磁盘文件数据

  

  以上图为例寻找，key为49的磁盘文件指针

  15 < 49 ········ 56 > 49 ········ 20 < 49 ········ 49 = 40

  • B+TREE

    • 非叶子节点不存储data，只存储key，可以增大每个‘’大‘’节点的度(Innodb_page_size默认大小是16k mysql中key的大小约为8B，指针约为6B所以一个节点(默认大小)能存储的key的个数为 16k/14b≈1170)

  • 叶子节点不存储指针，叶子节点存储16个key

    • 顺序访问指针，提高区间访问的性能

    

    默认16k，三层B+TREE就可以存储2千多万（1170x1170x16）个索引,一般来说B+TREE的高度应该在2-4

  • MyISAM索引（非聚集索引） - 数据与索引分开存储

    MyISAM表的存储文件
    
    .frm：存储表结构

    .MYD：存储数据

    .MYI：存储索引

  • InnoDB索引（聚集索引）- 数据和索引存在一起

    InnoDB的存储文件
    
    .ibd：存储索引和数据

    • 表数据文件本身就是B+TREE组织得一个索引结构文件

    • 聚集索引 - 叶节点包含了完整得数据记录

    • 为什么InnoDB表必须有主键，并且推荐使用整形的自增主键？

      **InnoDB 要求表必须有主键(MyISAM 可以没有),**如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

      同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 **InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择（具体问题就需要手撕一下B+树了）。**如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。

    • 为什么非主键索引结构叶节点存储得是主键值？（一致性和节省存储空间）

    
    叶节点之间得指针是为了更好得进行范围查询，假如查询的是索引列，例如
    select .... > 20
    那么如上图在子节点之间插入了指针的话就只需要查找 = 20的节点即可，然后往后遍历，如果子节点之间没有指针的话，范围查找的效果可想而知。
    - MyISAM索引分为主索引和辅助索引

  MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引，Col1为主键。
  

  **在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。**下图在Col2上建立一个辅助索引

  

• InnoDB索引分为主索引和辅助索引

  主索引的叶子节点data域记录着完整的数据记录，这种索引方式称为聚簇索引。因为无法把数据行存放到两个不同的地方，所以一个表智能有一个聚簇索引。

辅助索引的叶子节点的data域记录着主键的值，因此使用辅助索引进行查找时，需要先查找主键值，然后再到主索引中进行查找。

• InnoDB索引和MyISAM索引的区别

  • 存储结构（主索引／辅助索引）

    InnoDB的数据文件本身就是主索引文件。而MyISAM的主索引和数据是分开的。

    InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

    InnoDB是聚簇索引，数据I挂在逐渐索引之下。

  • 锁

    MyISAM使用的是表锁

    InnoDB使用行锁

  • 事务

    MyISAM没有事务支持和MVCC(多版本控制)

    InnoDB支持事务和MVCC

  • 全文索引

    MyISAM支持FULLTEXT类型的全文索引

    InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好

  • 主键

    MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址

    InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值

  • 外键

    MyISAM不支持

    InnoDB支持

• 索引优化

  • 独立的列

    在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

    例如下面不能使用actor_id列的索引

    SELECT actor_id FROM actor WHERE actor_id + 1 = 5;
    

  • 多列索引

    ​ 在需要使用多个列作为条件进行查询时，使用多列索比使用单列索引性能更好。

    例如下面语句中，最好把actor_id和film_id设置为多列索引

    SELECT film,actor_id FROM film_actor
    WHERE actor_id = 1 AND film_id = 1
    

  • 索引的顺序

    让选择性最强的索引放在前面。

    索引的选择性是指：不重复的索引值和记录总数的比值。最大值是1，此时每个记录都有唯一索引与其对应。选择性越高，每个记录的区分度越高，查询效率就越高。这样主键的选择性就最高，而年龄等属性的选择性就不高。

  • 前缀索引

    对于BLOB、TEXT、VARCHAR类型的列，必须使用前缀索引，只要索引开始的部分字符。

    前缀索引的长度的选取需要根据索引选择性来确定。

  • 覆盖索引

    索引包含所有需要查询的字段的值。

    具有以下优点：

    • 索引通常远小于数据行的大小，智能读取所有能大大减小数据访问量。
    • 一些存储引擎（例如 MyISAM）在内存中智能缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）
    • 对于InnoDB引擎，若辅助索引能够覆盖查询，则无需访问主索引。

• 索引的优点

  • 大大减少了服务器需要扫描的数据行数。
  • 帮助服务器避免进行排序和分组，以及避免创建临时表（B+TREE索引是有序的，可以用于ORDER BY和GROUP BY操作。临时表主要是在排序和分组过程中创建，也就不需要创建临时表）。
  • 奖随机IO变为顺序IO（B+TREE索引是有序的，会将相邻的数据都存储在一起）

• 索引的使用条件

  • 对于非常小的表，大部分情况下简单的全表扫描比建立索引更有效
  • 对中大型的表，索引就非常有效
  • 但是对于特大型的表，建立和维护索引的代缴将会随之增长。在这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条地匹配，例如可以使用分区技术。