MySQL分表后,怎么查询所有表中的记录?

抛开性能等问题,一张表分成N张表后,如何查询所有分表 type = 1 的数据?
(一张用户表,分成10张表后,我想取所有用户 type = 1 的数据)

MySQL有一个MERGE存储引擎是专门做这个事情的,借这篇博文了解:Mysql MERGE引擎分表

SELECT xx FROM table1 WHERE type=1 union
SELECT xx FROM table1 WHERE type=1 union
SELECT xx FROM table1 WHERE type=1 union
SELECT xx FROM table1 WHERE type=1 

如果你需要二次提取

  • 要么自己写程序从上面的union结果集里提取

  • 要么子查询

SELECT xxx FROM SELECT xx FROM table1 WHERE type=1 union SELECT xx FROM table1 WHERE type=1 union SELECT xx FROM table1 WHERE type=1 union SELECT xx FROM table1 WHERE type=1 t WHERE t.xxx....

不过, 我上面写的那些你可以不用看, 现在一些通用程序使用分表是因为MySQL 5.1之前不支持分区, 至少你这个场景合适的是分区, 而非分表.

分表的数量会影响采用哪种方案。

从数据库层面来解决:

  • Mysql MERGE 引擎,@samoay 回答了
  • Mysql 表分区,5.1 版本后才支持,@Yj.Lee 回答了

从 SQL 层面来解决:

标准的用法就是 UNION了,@Yj.Lee 也回答了。

题主的分表数量可能比较多,那么 SQL 方式可能不太适用,可以考虑数据库层面的解决方法。或者从应用层来解决,多个查询、拼合结果;或者按 type 建简单的冗余表/缓存进行查询;或者采用任何方法但缓存查询结果等等。

以上都是分表的情况,如果是分库,数据库层面的方法基本上也不可行了,只能从应用上来解决。

如果数据量就是不拆不行,那么就直接拆干净。MySQL拆了以后就把它看作key value的持久层,不要想跨表跨库。(除非是统计/数据仓库之类的需求,一个查询可以不计成本做很久堵死整台服务器也无妨)

慎重选择拆分依据,比如type很均衡,应用场景又基本总是已知type,不妨直接按type拆分。
其次如果就是大量数据,且就有和拆分纬度不同的检索需求,那么你需要的是搭搜索服务,或者说索引服务。这方面我没有实际经验,只能给一串关键词了 lucene solr sphinx elasticsearch

这中应该是按照路由去存储表格的吧,但是如果想查询一个不是按路由键的时候,就需要全库查询了吧

目测你需要的是分区而不是分表。

分表最好的办法就是merge引擎,适用于大部分场景,当然要看具体的数据量。

mysql部署中依次参考:

分区
分表
sharding

不过mysql千万级表索引设计得当,查询速度很快的。不知道为啥要分表。

建个视图吧

分表架构时最好是有中间件做支持,用来屏蔽这些细节。

merge引擎 抛开性能不说, 一个不好的地方是, 子表存储引擎必须是MyISAM, 而MyISAM 不支持事务!!!,如果你的表事先设置的存储引擎是innodb, 改为MyISAM后会出大问题的。

个人觉得,分表还是你原来方案,只是在做搜索时遇到了问题, 可以考虑将搜索字段存在单独表里,或者nosqlmongodb里,或者分布式缓存(redis)里, 通过搜索条件首先检索出分表字段属性(即是确定分表ID),然后查询详细数据。

如果是分库分表,有成熟的方案不?

该答案已被忽略,原因:宜作评论而非答案

发表评论

电子邮件地址不会被公开。 必填项已用*标注