mysql单表为什么不要超过2千万条时最优

mysql单表为什么不要超过2千万条时最优

理解本文章，本身要对索引原理和B+tree有一定了解

mysql数据存储

• 大部分场景我们都是使用InnoDB存储引擎，InnoDB最小存储单元是页，每页固定是16KB

假设：一行数据是1KB（大部分互联网公司的数据都为1KB，表的字段属性多），那每页就只能存储16条数据

B+tree结构

• 在B+tree中，叶子节点存放数据，非叶子节点存放键值+指针

假设主键 ID 为 bigint 类型，长度为 8 字节，下一层页的指针地址大小在 InnoDB 源码中设置为 6 字节，一共 14 字节，我们一个页中能存放多少这样的单元，即 16384/14=1170单元。
那么可以算出一棵高度为 2 的 B+ 树，能存放 1170*16=18720 条这样的数据记录

• 根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放： 1170X1170X16=21902400 条这样的记录,即2千万多些，
• 通过主键查询一条数据，只需要3次磁盘IO访问，

如果是辅助索引，辅助索引高度也为3时，需要经过6次磁盘IO访问得到数据，

辅助索引的叶子节点都是存放主键索引值，通过辅助索引查询需要经过回表，
查询优化点：当我们通过辅助索引查询时，如果查询结果只要主键值时，select （这里主键一个字段）from table_XXX，这样就查询辅助索引就能拿到值，不需要再去回表，提高查询效率

• 当超出2千万条时，索引树高度为4，

mysql都有缓存，树高度为3时，第一层和第二层的数据都在缓存，高度为3查询效率很快，但是超过高度4时，查询效率就急速下降了，这就回答了文章的主题了，为什么mysql单表不要超过2千万条

其他

• 为什么 MySQL 的索引要使用 B+ 树而不是其它树形结构？

比如 B 树？
因为 B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低

• B+Tree(B-Tree变种)
  非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
  叶子节点包含所有索引字段
  叶子节点用指针连接，提高区间访问的性能
  

总结

• 每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是3次