MYSQL索引分析
该文章承接上文:建议先读上一篇博客后再来看:https://www.cnblogs.com/liyongliang/p/10622950.html
之前公司一个dba在做分享的时候提到过说mysql单表的数据存储保持在2-3千万最佳,太多就影响查询性能了,当时没有讲具体什么原因,今天做代码优化时刚好想到了这个地方,就做了一个深究,现在互联网公司数据库中存储的数据大部分情况都是是用来读的,所以数据库的优化基本都是以读优先,这里就不多说,今天只讲为什么存储2-3千万最佳
mysql的innodb索引存储结构用的b+树,为什么用这种结构存储呢?上篇文章中说了,就是为了减少磁盘io的次数,尽量把磁盘io次数控制在一个常量级别
如上图是一个b+树,每一个磁盘块默认大小是16kb,每一个磁盘块是包含几个数据项和指针(黄色所示),大家都知道数据项存储在叶子节点中,所以非叶子节点其实存储的是“指引搜索方向的数据项”,一般大小是12byte( 索引一般为long类型也就是8byte,指针是4byte),那么第一层每个磁盘块存储 大小为16kb * 1024 / 12byte = 1365,第二层大约存储就是1365的平方也就是 1365 * 1365 =
1863680 的数据,那么第三层叶子节点存储的数据就是 1863680byte * 16 kb = 1863680byte * 16 * 1024 的数据,假如数据库中每条数据的大小是1kb那么最终存储的数据 1863680byte * 16 * 1024 / 1024 =
29818880 条数据,经过上边的计算,当我们存储2千万数据的时候b+树的高度就是三层
磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表