索引是帮助MySQL高效获取数据的排好序的数据结构
索引的数据结构:二叉树、红黑树、Hash表、B-Tree、B+Tree。
首先说说二叉树:二叉树的数据结构为,叶子节点如果大于根节点那么位于根节点的右边,叶子节点如何小于根节点那么位于根节点的左边。
红黑树:红黑树也可以理解为进阶版的二叉平衡树。
Hash表:通过hash运算存储在hash桶中。
B-Tree:B-Tree的存储形式肯定是K-V的形式啦,K存储索引元素,V存储磁盘地址,B-Tree的根节点以及叶子节点都存在磁盘地址。
B+Tree:B+Tree的存储形式跟B-Tree是一样的,但是B+Tree的磁盘地址只在叶子节点存在,根节点只存在索引元素,B+Tree的叶子节点之间是存在双向指针的。
B+Tree和B-Tree的区别:
1、B+Tree的根节点不存在磁盘地址而B-Tree的根节点存在磁盘地址,这样就会存在一个问题,
假设我们一行数据大小为1K,那么一页就能存16条数据,也就是一个叶子节点能存16条数据;再看非叶子节点,假设主键ID为bigint类型,那么长度为8B,指针大小在Innodb源码中为6B,一共就是14B,那么一页里就可以存储16K/14=1170个(主键+指针)那么一颗高度为2的B+树能存储的数据为:1170*16=18720条,一颗高度为3的B+树能存储的数据为:1170*1170*16=21902400(千万级条)所以B+Tree存储数据肯定是比B-Tree存储的数据要多。
2、B+Tree的叶子节点是存在双向指针的,双向指针是用来做区间查询的,比如Select * from student where age > 18; 假如叶子节点是18后边是20正好18和20是有这个双向指针的那么就直接可以获取到>18的数据,如果是B-Tree的话需要从根节点重新查询然后再获取到数据。
InnoDB引擎:
聚簇索引:就是说白了叶子节点存在所有数据,
InnoDB建表后生成的文件在磁盘中为 表名.frm、表名.Ibd,frm表示表结构,lbd表示索引元素和数据所以说查询的效率是很快的。
InnoDB为什么建议必须建主键:因为通过主键可以将数据串起来生成一个B+Tree,也可以不设置主建mysql也是处理了如果没有主建会自动生成一列rowid来当作主键,所以这么简单的事情需要我们去做因为mysql的时间是很宝贵的哈哈哈~~~~。
推荐使用整型的自增主键:生成B+Tree的前提是数据从左到右是有序的,也就意味着数据需要比较,很简单1和2做对比跟UUID和UUID做对比是有时间上的区别的。还有就是在建立索引的时候是可以选择是hash还是BTREE的这个自行学习啊哈哈哈哈~~~~。
MySAM引擎:
非聚簇索引:就是说白了叶子节点不存在所有数据,
MySAM建表后生成的文件在磁盘中为 表名.frm、表名.MYI、表名.MYD,frm表示表结构,MYI表示索引元素,MYD表示所有数据,所有它的查询需要通过MYI的文件的索引元素回表到MYD文件中查询数据导致它的查询效率是没有聚簇索引快。
联合索引(最左匹配原则):
KEY 'idx_name_age_position'('name','age','position')USIG BTREE设置索引
Select * from student where name='小徐' and age ='10' and position='A'
Select * from student where age ='10' and position='A'
Select * from student where position='A'
这三条SQL语句中肯定第一条是走索引的,其它两条是索引失效的,因为生成B+Tree的前提是数据从左到右是有序的也就是根据name来排序的,第二条和提三条语句是会导致顺序的失效也就意味着需要全局扫描。