对于有3亿多万条记录的MySQL表，有哪些优化技术?

I am looking at storing some JMX data from JVMs on many servers for about 90 days. This data would be statistics like heap size and thread count. This will mean that one of the tables will have around 388 million records.

我正在研究将一些来自jvm的JMX数据在许多服务器上存储大约90天。这些数据将是统计数据，如堆大小和线程计数。这将意味着其中一个表将有大约3.88亿的记录。

From this data I am building some graphs so you can compare the stats retrieved from the Mbeans. This means I will be grabbing some data at an interval using timestamps.

从这些数据中，我构建了一些图表，以便您可以比较从mbean中检索到的统计数据。这意味着我将使用时间戳以间隔获取一些数据。

So the real question is, Is there anyway to optimize the table or query so you can perform these queries in a reasonable amount of time?

所以真正的问题是，是否存在优化表或查询的方法，以便在合理的时间内执行这些查询?

Thanks,

谢谢,

Josh

杰克

6 个解决方案

#1

There are several things you can do:

你可以做以下几件事:

Build your indexes to match the queries you are running. Run EXPLAIN to see the types of queries that are run and make sure that they all use an index where possible.
构建索引以匹配正在运行的查询。运行EXPLAIN查看正在运行的查询类型，并确保它们在可能的情况下都使用索引。
Partition your table. Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key. MySQL supports this internally from ver. 5.1.
分区表。pari是一种将大的表分解成几个较小的表的技术，通过一个特定的(聚合)键。MySQL内部支持这一点。5.1。
If necessary, build summary tables that cache the costlier parts of your queries. Then run your queries against the summary tables. Similarly, temporary in-memory tables can be used to store a simplified view of your table as a pre-processing stage.
如果需要，构建用于缓存查询中更昂贵部分的汇总表。然后对汇总表运行查询。类似地，可以使用内存中的临时表将表的简化视图存储为预处理阶段。

6 个解决方案

#1

更多相关文章

随机推荐