【学习日志】MySQL分表与索引的关系

什么情况下需要分表呢？分表又能解决什么问题呢？

一般情况下分表的直接原因是数据量太大了，比如一张表一共只有1w条数据，确实没必要分表。为什么数据量大了就需要分表呢？首先得看看数量量过大后会带来什么问题。

以InnoDB为例：

使用的是B+树，聚簇索引。如果数据量过大，可能会导致多一次甚至多次磁盘IO，查询性能大幅下降。

因此，我们需要尽量将B+树层数保持在3层以内。那么如何计算3层的B+树可以保存多少数据呢？

页是InnoDB存储引擎磁盘管理的最小单位，每个页默认16KB。那么一个叶子节点能存储的数据量为16k，每一页留给用户存储数据的空间为15232byte。

每一条索引记录大小=8byte(bigint主键)+6byte(指针信息)+5byte(行标头)=19byte

那么15232÷19≈800条数据索引，如果主键为int，那可存储的数据索引会更多。

由于InnoDB使用的是B+树

叶子节点数量=800(第一层单个节点索引数量)*800(第二层单个节点索引数量)=640000个

假设表如下：

CREATE TABLE `t1` (
  `id` bigint NOT NULL PRIMARY KEY,
  `type` char(5) NOT NULL,
  `data` char(5) NOT NULL,
  (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

那么每条数据大小为

8+5+5+6（事务ID）+7（指针字段）+5（行记录头信息）=36byte

15232 / 36 = 423条数据

每个叶子节点可以存放423条数据，那么三层B+树可以存放 423*640000 <span class="katex"><span class="katex-mathml">=&nbsp;270,720,000，约2亿7千万条数据。