mysql - 拆表是否能够提高性能

Question

比如我有一张 post，它本应该包含的字段（text 类型的 body 已被移出到附表）：

id、user_id、title、description、num_views、num_likes、num_comments、create_time

这时候我要取 posts 列表：

SELECT `id` FROM `post` ORDER BY `id` DESC LIMIT 10000, 20
SELECT * FROM `post` WHERE `id` IN (上一步foreach出的ids)

我的问题：

1、上面的读取方式是不是比单纯的 SELECT * FROM post ORDER BY id DESC LIMIT 10000, 20 效率要更高？如果是这样，那随着表记录越来越庞大，是不是把 id 独立出去能够获得更快的 LIMIT 速度？

2、抛开主从/Cache等等其它因素，单纯的读库，因为三个 num_* 字段相对来讲 update 是比较频繁的（特别是 num_views 每次打开文章页都会触发更新），这个 update 和我的 LIMIT 读取是不是在锁机制上存在着排队和竞争？那如果这样，我把这个三个字段独立出去是不是能够增加并发程度（LIMIT 不受 update 的困扰，毕竟 title 和 description 的更新几率要小的多）？

Answer 1

1、覆盖索引查询的确会比普通的limit start,N 方式要好一些。具体的可以实测下，根据我的经验可以增加不少

2、大量update的确会对表造成一些影响。不过不建议大量拆表，这样虽然可以降低写的压力。但是代码结构可能维护起来就很灾难了。毕竟不可能无限进行分表。。建议前面加一层cache。redis or memcache。 数据id_key 进行incr操作，达到某个值进行同步到数据库。比如rand(5,10)。这样数据库压力可以降低很多，效率也可以进行保证。代码维护也没有很复杂。

Answer 2

1. 提升数据库查询的速度首先应考虑建议索引和优化索引方面的努力，在数据量级较小的情况下，通过索引查询的差别其实是非常低的，如果这时候就拆表，加上程序上重新合并多表查询所消耗的时间，未必就真的比单表少。而拆表主要还是针对百万、千万级以上的表，在有索引的情况下都会比较吃力的时候进行的。

2. 更新和读取的锁问题，如果较大且更新频繁的表，建议采用InnoDB引擎进行行锁。

Answer 3

看到你的题目，以为是要把一个表拆成多个表，实际上就是个分页问题。MYSQL 里面LIMIT分页的原理你清楚吧，一句话说就是把limit+offset 记录全部取出来，然后丢掉limit记录，留下的返回。当limit很大时，被查出来的记录数就很大，而且都是被丢弃的.

1：select * 这种写法不符合SQL编写规范，任何时候都不要用*来代替具体的列名称，需要什么列就取什么列。如果表里有个text/blob等大字段，影响就更加明显。
2：pre_forum_thread 表在tid字段做了分区，但是查询里面没有用到分区字段，这样就需要扫描全部分区，性能比不分区更差。
3：在这个分页SQL里，偏移量高到193万。
LIMIT语法：
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
MYSQL是处理LIMIT语句的方式是：取出全部offset+rowcount,然后丢弃掉前面所有行，只返回row_count行。
在这个案例里，在mysql server端需要查询的行数为1933100+50，217S还未得出结果。
优化方案： 最终需要的只是50行记录，如果先取出这50行记录的主键ID，这样是不是会很快？ 执行计划和执行时间：

分析一下为什么只取出PK值会很快。 在INNODB索引树里面，每个二级索引的叶子节点都会保存一份PK值，通过二级索引查找数据的过程是：从索引树的根节点开始比较索引值是否和查询值匹配，如果不匹配，根据情况进入左或右分支，再比较，直到在找到符合要求的节点，然后从叶节点里取出PK值，再回表根据主键得到全部数据。如果只是查找主键，那么就少了”然后从叶节点里取出PK值，再回表根据主键得到全部数据“这一部分，而这一部分正是最耗时的。在执行计划里可以看到”Using index“，这就说明优化器使用了”覆盖索引“，只需要扫描索引数据就可以得到最终数据，索引一般情况下比数据要小，往往常驻内存，所以虽然偏移量193万，也能给在1.02秒内返回结果。
得到这50个主键ID值之后，用这50条记录再关联原表查询：
mysql> explain select sql_no_cache * from pre_forum_thread A inner join (SELECT tid FROM pre_forum_thread  WHERE fid=22 AND displayorder>=0 ORDER BY lastpost DESC  LIMIT 1933100, 50) B on A.tid=B.tid;

#执行时间
50 rows in set (1.06 sec)
处理分页的方法有很多种，在业务层面可以限制翻页的起始位置，不允许直接定位到10000页。在数据库查询方面也有别的方法来处理，根据相应的业务需要寻找最佳的处理方法。 本案例里的 LIMIT 1933100, 50 需要规避。

Answer 4

设计数据库尽量符合三大范式，考虑一下