重新设计基本Mysql存储体系结构

Question

我们是一个小型互联网提供商，我们将所有客户端流量数据存储在一个MySQL数据库(MyISAM)中。我们每隔几分钟在三个表格中插入数据，如下所示：

capture_table
----------
client_id
date
time
data1
...
datan
---------

数据总是按日期查询。

在表变得太大之后，我们决定使用几个表来划分数据。我们创建了三个PHP脚本，每隔几分钟运行一次，每月将原始数据分成3个表(MyISAM)，并创建一个索引表。

index_table
data_table_2014-01-1
data_table_2014-01-2
data_table_2014-01-3
...
...

目前，对于我们当前的客户端数量，我们有这种结构，每个表大约有250万行，而且还在增长(DB重量约为15 DB)。

查询是通过使用UNION连接来自两个日期之间涉及的所有表的数据来完成的，始终搜索日期和客户端。例如，2014-01-01和2014-01-15年间的搜索：

(SELECT * FROM data_table_2014-01_1 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_2 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_3 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')

这是更好的方法吗？能否以更好的方式实现这一目标？我在考虑表分区，例如.

非常感谢。

Answer 1

在我看来，如果您面临性能问题，应该使用SSD。这里是一个大约120 GB的驱动器。64欧元(< 100美元)。这将保持您的应用程序滴答数年，而不需要任何重新设计和性能应大大改善，以最低的费用。

接下来，如果您最终决定转移到DW场景，我可以推荐PostgreSQL作为您的DW服务器而不是MySQL吗？MySQL适用于面向阅读的网页应用程序，但不适合DW类型的应用程序。PostgreSQL具有CHECK约束、SET操作符(INTERSECT )、CTE(公共表表达式)和窗口函数(所有这些MySQL (难以置信)都没有)。

传统上，PostgreSQL在复制、集群和连接池方面并不强大，但是有变化。

Answer 2

我的观点是，您将有多个VM，并且您希望坚持使用Mysql，使用Fabric来分割您的数据库。http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric-quick-start-sharding-scenario.html。分片是水平分区，您可以将数据拆分到多个服务器上，并获得更快的写入和读取速度。

Answer 3

如果选择不使用内置分区，则可以通过具有视图来滚动自己的分区。

create view capture_table as
select * from data_table_2014-01-1
UNION ALL
select * from data_table_2014-01-2
UNION ALL
select * from data_table_2014-01-3
etc.

隔夜维护创建一个新数据表，删除旧数据表，并重新生成视图的定义。读取过程永远不需要改变。编写过程可能更复杂，取决于MySQL的能力(我是以Server为中心的)。选项1-动态SQL始终写入当前表。2-当前表的常量名称，并在维护期间重命名。3-写入视图并让RDBMS对其进行排序。