评论里聊了这么多, 我也重新编辑一下答案吧. 关于你提出的两个点:
第 1 点的问题是不存在的, 因为 information_schema 下面的表都是内存表, 所以实际上并不存在什么查询效率上的问题. 1000条和10000条其实是没有什么显著的差异的.
第 2 点的问题有一定的合理性, 文件描述符越多意味着越多的 I/O 和内存占用, 当然这就并不仅仅是文件描述符本身的占用, 还包括 MySQL 的 InnoDB 的 Buffer Pool 这类的, 但是这个你得在动态的层面来看, 假设加服务器加内存和迁移到 SSD 就能解决的问题何必要堆工程师非在软件层面去解决, 立竿见影, 成本还低.
当然没错, 的确 10000 个分表或者后面可能有 50000 个分表听上去第一感觉的确可能有问题. 但就问题的本身的信息而言做不出这个判断(因为这里不知道你们的业务细节), 但可以考虑是否有可能向这几种方案改造有助于数据更加均匀分布的同时并且对你们现有的系统改造代价最小:
- 按照某个 ID 进行取模进行分表, 分析一下当下的数据, 看看除哪个数字可以让数据尽可能均匀, 所有的表的数据量趋于相对一致而不是都分散在一个表下面. 大多数 SaaS 系统中由于大多数用户都只能访问他自身的数据, 所以这种分法是很常见且合理的.
- 按照冷热数据进行分表是否可行. 几百万的数据肯定不会是实时都要访问的, 那是否可以把一年前的一些数据放到冷表里, 热表仅保留近期半年或者一年的数据 ---- 具体时间多少取决于你们的业务 (你可以看到京东的订单也是类似于这么设计的, 你在 PC 上打开京东的订单页, 可以选择订单的时间范围, 实际上就是在选择冷数据)
关于数据量, 冲着单表 20000 条左右数据的这个目标的确是不合理的, 在合理的硬件配置, 合理的软件设置, 合理的表结构设计, 合理的索引设计, 合理的 SQL 语句的作用下, MySQL 是可以做到千万级别的查询并没有什么明显的延迟的. 如果你们团队中没有能在这个层面进行相关把关的人, 并且业务也能够支撑(换句话说, 在赚钱), 那么是时候招聘一个 DBA 了.
以下为原答案
和你想象的不同, 实际上分库分表是切实可行的性能优化方案, 用空间换时间, 在单表确实碰到了性能问题后, 分表是可行的.
分表方案本身也很多, 除了你上面说的按照实际业务情况来分的以外
非常常见的一种, 用上一个MySQL的中间件, 根据 ID 取模切割, 除10取余数那就是分成10个, 除1000那就是分成1000个, 中间件配置好规则后会自动帮你把数据插入对应的服务器中对应的表. 当然这些需要提前规划好数据量.
当然确实分表会带来一些问题, 只不过你所说的多一步查找database和table压根算不上什么问题, 至于文件描述符的也不算什么问题(影响十分有限), 特别考虑到如果引入中间件之后还可以实现跨多服务器, 性能层面由于中间件都有连接池的所以其实并没有什么太大降低, 我随便列出几个问题(当然并不全面):
- 这些表的主键将失去唯一性, 如果这个表的 ID 对你们来说至关重要 (例如: 用户ID或者店铺ID这类在业务中非常重要的ID), 解决方案是要引入一个三方的发号器
- 跨表跨服务器的事务会很麻烦或者干脆不可用, 数据一致性需要付出较大精力来保证.
- 连表很容易遭遇问题, 建议不再用连表 (不过即使是单表, 也不建议采用连表, 不成熟的连表语句很容易踩坑)
- 运维成本急剧增加, 例如修改表结构会非常麻烦, 需要批量依次执行, 并且还要确保都执行正确.
虽然多少有一些问题, 但由于互联网公司内大量还是使用社区版本的 MySQL(也就是免费的), 所以分表几乎是数据量大了之后的并不多的可行的选项之一.
