mysql分表真得能提升查询性能吗

首先将存储引擎限定在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，比理论估算还小一些。

问题

我们有一个 SQL，用于找到没有主键 / 唯一键的表，但是在 MySQL 5.7 上运行特别慢，怎么办？

实验

我们搭建一个 MySQL 5.7 的环境，此处省略搭建步骤。

写个简单的脚本，制造一批带主键和不带主键的表：

执行一下脚本：

现在执行以下 SQL 看看效果：

...

执行了 16.80s，感觉是非常慢了。

现在用一下 DBA 三板斧，看看执行计划：

感觉有点惨，由于 information_schema.columns 是元数据表，没有必要的统计信息。

那我们来 show warnings 看看 MySQL 改写后的 SQL：

我们格式化一下 SQL：

可以看到 MySQL 将

select from A where A.x not in (select x from B) //非关联子查询

转换成了

select from A where not exists (select 1 from B where B.x = a.x) //关联子查询

如果我们自己是 MySQL，在执行非关联子查询时，可以使用很简单的策略：

select from A where A.x not in (select x from B where ...) //非关联子查询:1. 扫描 B 表中的所有记录，找到满足条件的记录，存放在临时表 C 中，建好索引2. 扫描 A 表中的记录，与临时表 C 中的记录进行比对，直接在索引里比对，

而关联子查询就需要循环迭代：

select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA：     扫描 B 表，找到其中的第一条满足 rA 条件的记录。

显然，关联子查询的扫描成本会高于非关联子查询。

我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化，MATERIALIZATION），但MySQL 认为不缓存更快，我们就需要给予 MySQL 一定指导。

...

可以看到执行时间变成了 0.67s。

整理

我们诊断的关键点如下：

\1. 对于 information_schema 中的元数据表，执行计划不能提供有效信息。

\2. 通过查看 MySQL 改写后的 SQL，我们猜测了优化器发生了误判。

\3. 我们增加了 hint，指导 MySQL 正确进行优化判断。

但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。

我们在上面分别进行了分表和不分表的性能测试，只有一个表，和把这个表拆为32个的情况，两种情况都为innoDB,表内有text字段。padding数据为2亿，做了25个进程的每个进程20W的 *** 作。我们发现如果仅有select,update的时候，分表的情况比不分表的情况快10%，insert的情况就差太远了：分表比不分表慢20%多。可以确定的是，select，update后的where子句有索引，都为单条的查询和更新，insert也只是插入一条。如果查看机器负载，发现不分表，CPU12%~20%和磁盘busy不是100%。分表后磁盘busy100%，而CPU15%~25%。我觉得很郁闷，不是一般来说，分表应该效率更高吗？还是说innoDB已经优化的很好了？我的猜想是：innoDB是行级锁的，所以select,update,insert不会锁住其他的进程，所以效率提升不大。如果你业务上只对单一record *** 作， 你也没有必要分表了。但是，这肯定不是实际情况， 实际情况是你有很多批量的update, 很多select。 可能有很多select会和update,insert争抢table锁。把这些情况都考虑进去， 加一些查询。 相信分表的优势立马就有了。 明白了，我的实际情况是根本没有证锁的情况，看来没必要了……问题补充：<div class="quote_title">polymorph 写道</div><div class="quote_div">这种压力测试如果脱离开具体的业务意义就不大了。 <br /><br />就比如一个查询，可能一下查询出1万条记录。这种查询很正常，也很普遍。但是，当系统真正上线以后，有可能出现一下子插入1万条记录的情况吗？所以查询出1万条的查询，速度的提高对系统的性能是很有帮助的，而插入1万条记录速度的降低未必会很大的影响系统的性能。 <br /><br />再比如数据仓库中的高耦合度，要是论插入的速度，那简直惨目人睹。但是这相当于将时间的消耗平坦到每一次的插入里面了。当你要查询时，会非常快的查询出结果。 <br /><br />像你现在这个数据量级的话，说句实话，还用MYSQL真是一种冒险。排除这个，将表分开是正确的。当系统真正运行起来，插入时资源消耗的增加不会对系统增加多大压力，但是会极大的降低查询时对系统的压力，而这部分压力，才是属于能让系统DOWN掉的那部分。 <br /><br />系统资源就是这样，用20%的资源做80%的事情，用剩下的80%的资源做20%的事情。插入就是那80%的事情里面的。就让那20%的消耗增加为25%吧，你会发现那80%的消耗可能一下子就减了一半。

---