mysql分表真得能提升查询性能吗
发布网友
发布时间：2022-10-18 09:46
共1个回答
热心网友
时间：2023-11-11 18:21
首先将存储引擎限定在innodb
如果将不同的表分到多个库多个机器上，那一定是能提升性能的，毕竟你花钱买机器了，总的可用CPU和内存高了。这种情况不必考虑。
如果所有分表都在同库同机器上，每个表的查询都共用CPU和内存，性能是怎么提升的？
例如这条查询 select * from user_feeds where uid = ? ，且uid字段上有二级索引，查询步骤为：
通过以上步骤可以看出，查询的主要成本为：确定索引扫描范围的IO次数（最大为4）、遍历扫描范围内索引数据的CPU占用（与扫描范围内记录数成正比）、回表IO次数（与扫描范围内记录数成正比，且受聚簇索引B+树层数影响）。所以查询的成本主要取决于uid=X对应的记录数量和索引B+树的层数。
来看一下分表后的查询成本：
可见分表后主要的差别在于确定索引扫描范围的IO次数和回表IO次数，即分表后B+树的层数变化，变化范围为0到3。对于不需要回表的查询，差别在于用到的索引B+树层数变化导致的确定扫描范围的IO次数变化；对于需要回表的查询，除了确定扫描范围的IO次数变化，还有回表IO次数变化（回表IO次数变化=记录数X聚簇索引B+树层数变化）。
以上是有二级索引的情况，如果没有索引，需要全表扫描时，扫描整个聚簇索引的成本：加载数据页到内存的IO次数（聚簇索引的页面数）、扫描记录的CPU占用（整个聚簇索引的记录数量）。分表以后所有表总的记录数量不变，页面数量也可以认为不变。所以全表扫描的查询性能分表与不分表也不会有什么差别。
垂直分表，表记录数不会改变，每条记录数据占用空间会变小，从而导致单表聚簇索引每个数据页可以存储的记录变多，聚簇索引的数据页数量变少，原来uid=X的记录可能分布在N个数据页上，分表后uid=X的记录分布在的数据页数量<=N。也就是减少回表IO的次数。
但是，这种情况只限于合理的垂直分表，也就是select的列可以落在同一张表中。
索引列：uid、created_at
单条记录大小 uid4字节、created_at4字节、主键post_id8字节，页号6字节 共22字节
单页可以存放的记录数 16kb（16384）/22 = 744，去除掉一些其他信息（header、槽信息），再去除一些碎片（删除造成的、没填满造成的），姑且认为每页存放500条记录
如果总的记录条数为5000w，那么需要的叶子节点数量为 5000w/500 = 10w，B+树需要两层（500X500 = 25w）
索引叶子节点占用空间大小为 10wX16kb = 1.6GB
索引目录节点占用空间大小为 500X16kb = 8M
单条记录大小 24
单页可以存放的记录数 682
与理论估算相近
索引空间大小19.38GB，生产环境二级索引数量16个，平均每个索引空间大小约1.1GB，比理论估算还小一些。
