Mysql如何计算索引高度
2020, Jun 01
InnoDB
- 非叶子节点上没有实际的数据
- 叶子节点上才有实际的数据
- 叶子节点之间有指针串联指向下一个叶子节点,这样能够提升范围查询的效率
聚簇索引(Clustered)
- 在InnoDB使用了聚簇索引(Clustered),即所有二级索引聚集在主键索引上,对InnoDB存储引擎表的任何访问,最终一定要搜索主键索引树.
- 在InnoDB中,二级索引(所有不是主键索引的索引)上没有实际的数据,取而代之的是主键索引的值。
- 如果是基于二级索引的查询,会先在二级索引上搜索得到主键索引的值,然后再去主键索引树上搜索,得到最终的行数据.
- 例如:一个表有两个字段 ‘id’,’name’,id是主键,name建立了索引,查询name得到id,再通过id获取到name。
- 意味至少有一次索引查找,可能会有两次索引查找,其中一定有一次主键索引查找。
` 在InnoDB中,主键要设计的尽量小,主键越小,二级索引也会越小。 满足需求的情况下,SMALLINT优先于INT,INT优先于BITINT,INTEGER类型优先于VARCHAR类型。 ` ` 如果主键用更大的数据类型,由于二级索引上有主键索引的值,那么不只是主键索引树变的更大更高, 其他的二级索引树也会更大更高。 `
MyISAM
- 没有使用聚簇索引,所以主键就是一个普通的唯一索引,
- 基于索引查询只会搜索当前索引,不会和其他索引有任何关系,任意两个索引之间互不影响
- 叶子节点上只有表中行数据的地址
B+TREE 的高度
- 表的记录条数是 N;
- 每个节点平均有M个索引;
高度 = log2~N / log2~M
假设,有1亿条数据,每个节点有64个索引. 26.575425 / 6 近似 4.42
每个节点能存储多少个索引KEY ?
每个节点保存的KEY的数量 = pagesize/(keysize+pointsize)
- 每个索引节点一般都是操作系统页的整数倍
- 操作系统页可通过命令得到该值得大小,且一般是4094,即4k。
- 而InnoDB的pageSize可以通过命令得到,默认值是16k。
- keysize:索引值大小,索引值类型决定。例如:BIGINT,存储大小为8个字节。INT存储大小为4个字节(32位)。
- pointsize:指针大小
` 如果是B-TREE索引结构,索引节点上不仅保存了索引和指针,还保存了具体的行数据. 公式则是pagesize/(keysize+datasize+pointsize)) `
来算一算: pointsize = 6个字节 keysize(BIGINT) = 8个字节 每个节点保存的KEY的数量 = 161024/((8+6)8) = 146.285714285714286
` 大致 单表千万级数据的索引高度 在3~5之间。 `
结论:
- 索引树越高,性能就会越差。
- 不考虑内存的情况是 索引树有多高,就表示查询至少需要多少次IO操作。
- 如果是二级索引,要翻倍。(现在二级索引中找到一级索引)