1、索引概念
索引 ( index)是帮助MySQL高效获取数据的排好序的数据结构。
2、常见的索引数据结构
树的高度和查询速度紧密相关,树越高,查询效率越低。
2.1、二叉树(mysql未使用)
特点:
1、树的高度不可控,会严重影响查询效率
2、对于单边增长的数据列(比如自增主键),对查找效率没有帮助
2.2、红黑树(平衡二叉树,mysql未使用)
特点:
1、当数据量大时,树的高度不可控制,树的高度会非常大,如果要查找的数正好在叶子节点上,红黑树是从根结点开始查找,比大小的原则往下去找,如果树高度是20,那么也要查找20次才能找到,效率很低
2.3、Hash表(mysql中可以选择使用)
假设0x56 为 zhao 对应的磁盘文件地址,例如查询 name 为 zhao 时,会先通过 hash 运算得到结果桶 1,再去遍历桶 1 的链表,最终查询到 zhao,包括所对应的磁盘文件地址。
特点:
1、对索引的key进行一次hash计算就可以定位出数据存储的位置
2、很多时候Hash索引要比B+树索引更高效
3、仅能满足“=”,“IN”,不支持范围查询
4、hash冲突问题
2.4、B-Tree(mysql未使用)
特点
1、叶节点具有相同的深度,叶节点的指针为空
2、所有索引元素不重复,非叶子节点也会存储data
3、节点中的数据索引从左到右递增排列
2.5、B+Tree(B-Tree变种,mysql采用该结果进行存储数据的)
特点:
1、非叶子节点不存储data,只存储索引(冗余,比如8、51、88这些就是冗余索引),可以放更多的索引
2、叶子节点包含所有索引字段
3、叶子节点用指针连接,提高区间访问的性能
3、存储引擎
3.1、MyISAM存储引擎
MyISAM索引文件和数据文件是分离的(非聚集索引,数据和索引是分开存储的)
MYI文件:以Col1建立索引,MYI文件用B+Tree的的结构组织存储好索引信息,根据上图可知,叶子节点中data存储的是磁盘文件地址,根据磁盘地址可以去MYD文件里定位到具体的这一行数据。比如查询条件:where Col1=36,通过MYI可以找到磁盘地址是0x88,再通过0x88去MYD文件中找到这一行数据。
3.2、InnoDB存储引擎
InnoDB索引实现(聚集索引,数据和索引是在一起的)
1、表数据文件本身就是按B+Tree组织的一个索引结构文件
2、聚集索引—叶节点包含了完整的数据记录
3、为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
问题①建议InnoDB表必须建主键:若不建主键,则会在创建表时,遍历每一列,选择其中数据没有重复出现的一列作为主键;若不存在符合条件的列,则会自动创建一个隐藏列(rowId)作为主键来进行维护。因此需要在创建表时主动建主键,减少数据库不必要的性能消耗。
问题②推荐使用整型的自增主键:当我们找一个数据时,都是从根节点开始查找,在索引定位的过程中,中间经过了很多次数据比大小,而整型在进行比较时效率最高(像uuid这种比大小肯定没有整型的快)。索引这些文件是存储在磁盘上的,整型的占用的磁盘空间更小(更节约空间),所以用整型做索引的最好。
问题③为什么用自增主键:因为B+Tree是递增的结构。若不使用自增主键,则在加入新的数据时,可能会多次将数据插入到前面已经放满索引元素的节点,使其进行分裂调整,甚至会导致整棵树重新进行平衡,极大降低效率。因此选择使用自增主键,在每次加入新的数据时,都从末端插入数据,效率最高。
4、为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)
a、主键索引(即聚集索引)由主键来组织整张表的数据,因此叶子节点存放即为具体数据;
b、非主键索引(即二级主键/二级索引/非聚集索引/稀疏索引)的叶子节点存放的是主键索引,以此来保证数据的一致性,即在操作过程中,完成主键索引的操作即可,二级主键的增删操作不会影响数据的一致性;以及不需要重复存储相同的数据,节省存储空间。
c、非主键索引进行查找时,先根据二级主键定位到对应的主键(即二级主键的叶子节点),再根据主键索引查找到对应的数据(即回表操作)。
聚集索引查询到获取数据的效率高于非聚集索引
聚集索引在单文件中即可完成查询与获取,而非聚集索引需要跨文件获取数据(包括二级索引的回表操作)。
一般的B+Tree是单向箭头,而mysql的B+做了改变,叶子节点是双向箭头。
4、索引最左前缀(左列)原理
联合索引遵循索引最左前缀原理(左列,按照索引建的先后顺序,创建索引
根据最左前缀原则,若比较大小会看比较 name,若相等,则比较 age,若再相等,则比较 position,再根据叶子节点存储的主键进行回表查找对应的主键数据。