目录
1、索引本质
索引是帮助Mysql高效获取数据的排好序的数据结构
2、索引数据结构
二叉树、红黑树、Hash表、B-Tree
Mysql从第一行将数据从磁盘load到内存与sql对比,每一次load叫做一次IO。
此时给Col2添加索引,Col2作为一个二叉树,二叉树也存储在磁盘上。每个节点保存为一个map<字段值,该字段所在磁盘位置0x77>。
1.2、二叉树问题:当存在索引字段存在单边增长时,二叉树不适合作为索引。
如果将Col1作为索引字段,该索引列的结构是一个链表。
此时使用语句select * from t where t.col1 = 6,此时索引的存在没有意义。
1.3、红黑树Red-Black-Tree:是一种平衡二叉树,自动实现自旋,解决单边增长的问题。
红黑树问题:不支持大数据量存储,存储大数据量是高度不能控制。如果有100万行数据,根据2^n=100万,n=20即树高20,假如待查找的数据的索引在叶子结点,则需要从根节点查找进行20次磁盘IO。如果1000万行更严重。
1.4、hash表:通过对索引求hash得到数据所在磁盘所在地址,直接定位数据,只需要一次磁盘IO即可。
hash表问题:不能进行范围查询。例如当select * from t where t.col1 > 6,只能进行全表扫描,因为实际业务中很多范围查找,故很少使用hash表。
1.5、B-Tree: 对接点横向扩展,使每个节点存储多个索引元素,保证整颗索引树的高度可控,同时满足二叉树特性且同一个节点从左到右增加。
特性:所有索引分布在整颗树上,比红黑树的高度低很多,其中data是索引所在行的磁盘位置即文件指针或者所在行的其他字段数据。
疑问:是否可以将所有索引节点都存储在一行。
解答:对于百万-千万的索引数据量达到500M-G,此时将如此大的索引数据从磁盘load到内存属于浪费空间。实际一次IO仅仅4M。
mysql官方:每个索引最大16K。
问题:B-Tree是否可以进行范围查询。例如select * from t where t.col>20
解答:不能使用范围查找。
3、索引是怎么支撑千万级表的快速查找?
1.6、B+Tree(B-Tree变种):树的叶子节点存储一份完整的索引数据
特性:非叶子节点仅仅存储索引值不存储实际data。
问题:为什么B+Tree设置一个节点16K
解答:为了保证每个节点存储更多的索引值。以bigint为8B为例,非叶子节点中索引+指针为8B+6B=14B,16K/14B=1170个索引值。叶子节点中8B+data假设1K,16K/1K=16个索引值。
此时B+Tree时存储满时,共存储多少索引:1170*1170*16=2千多万。
此时解答了:索引是怎么支撑千万级表的快速查找?
实际上,mysql会将索引树的根节点存储到内存中。
问题:B+Tree如何实现范围查找
解答:叶子节点有链接指针,按照顺序排序,可以支撑范围查找。
4、如何基于索引b+树精准简历高性能索引?
1、每一个数据库实际存储在一个文件夹下。
2、其中,一张表对应单个文件。
frm:数据定义相关信息【例如表的定义create table...】
myd: 存储表的所有数据行,即数据值
myi:存储数据索引。
对下图表test_myisam来说使用b+tree建立主键id的索引。
3、MyISAM索引文件和数据文件是分离的(非聚集【索引和数据存储在不同文件中】)
当执行select * from t where t.col=49时步骤【myisam引擎data处存储的是数据在磁盘中的指针】
先load文件MYI中的两行,定位到49 ---> 0x90
再load文件MYD中具体数据行
4、InnoDB索引属于聚集索引【索引和数据聚集存储在一颗B+Tree上】
问题:为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?
解答:如果使用innodb情况下没有显示建主键,数据库会自动帮你选一个可以代表本表的列,如果找不到它会自动生成唯一列,类似rowid,依据这个列生成一个建立B+Tree树。
因为UUID【arg2342】占用更多的存储空间,因为查找数据时需要比较。整型比较比UUID快得多,性能高且节省空间。
问题:为什么自增?
解答:因为索引树B+Tree是有顺序的,自增的话新插入的数都会在索引树后面添加。如果不是自增的话,如果某个节点满了,而新增元素是在该节点中间插入,需要节点进行分裂。
5、联合索引底层数据结构是怎么样的?
例如:(a,b,c)
联合索引B+Tree树图: