blocks|key|1988003|text|MySQL是非常可伸缩的，这一点是肯定的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1988004|关键不是将数据库从Mysql更改为other，但您应该：|1988005|1988006|Optimize您的查询(对其他人来说可能听起来很傻，但我记得，例如，我不久前做了一个巨大的改进，就是将SELECT+*更改为只选择我需要的列。这是我在others+too)|ordered-list-item|offset|length|style|BOLD|CODE|1988007|Optimize+your+table(s)+design中经常遇到的问题(在查询中经常使用的列上的normalization+etc).|1988008|。|1988009|1988010|类似的建议here|1988011|entityMap|0|LINK|mutability|MUTABLE|url|http://databases.about.com/od/specificproducts/a/normalization.htm|1|https://stackoverflow.com/questions/1004672/optimizing-a-simple-query-on-two-large-tables^0|0|0|0|0|C|1G|8|0|0|T|1E|D|0|0|0|0|5|4|1|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|15|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|16|8|@$H|17|I|18|J|K]|$H|19|I|1A|J|L]]|9|@]|A|$]]|$1|M|3|N|5|G|7|1B|8|@$H|1C|I|1D|J|K]]|9|@$H|1E|I|1F|1|1G]]|A|$]]|$1|O|3|P|5|G|7|1H|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1J|8|@]|9|@$H|1K|I|1L|1|1M]]|A|$]]|$1|T|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]]|U|$V|$5|W|X|Y|A|$Z|10]]|11|$5|W|X|Y|A|$Z|12]]]]

MySQL is very scalable, that's for sure.

The key is not changing the db from Mysql to other but you should:

<ol>
<li>Optimize your queries (can sound silly for others but I remember for instance that a huge improvment I've done sometime ago is to change <code>SELECT *</code> into selecting only the column(s) I need. It's a frequent issue I meet in others code too)</li>
<li>Optimize your table(s) design (<a href="http://databases.about.com/od/specificproducts/a/normalization.htm" rel="nofollow noreferrer">normalization</a> etc).</li>
<li><a href="http://www.tizag.com/mysqlTutorial/mysql-index.php" rel="nofollow noreferrer">Add indexes</a> on the column(s) you are using frequently in the queries.</li>
</ol>

Similar advices <a href="https://stackoverflow.com/questions/1004672/optimizing-a-simple-query-on-two-large-tables">here</a>

blocks|key|4061492|text|对于生成包含大块数据的报告或文件下载，您应该考虑使用flush并增加time_limit和内存限制。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4061493|我怀疑问题出在行数上，因为MySQL可以支持很多行。但是，您当然可以一次获取x行，并对它们进行分块处理。|4061494|我确实假设您的MySQL已经针对性能进行了适当的调整。|4061495|entityMap|0|LINK|mutability|MUTABLE|url|http://nl3.php.net/flush^0|Q|5|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

For generating reports or file downloads with large chunks of data you should concider using <a href="http://nl3.php.net/flush" rel="nofollow">flush</a> and increasing time_limit and memory limit.

I doubt the problem lies in the amount of rows, since MySQL can support ALOT of rows. But you can of course fetch x rows a time and process them in chunks.

I do assume your MySQL is properly tweaked for performance.

blocks|key|4061535|text|首先分析查询速度慢的原因(或者:是否)：http://dev.mysql.com/doc/refman/5.1/en/using-explain.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4061536|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/using-explain.html^0|K|1L|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

First analyse why (or: whether) your queries are slow: <a href="http://dev.mysql.com/doc/refman/5.1/en/using-explain.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/using-explain.html</a>

blocks|key|1988097|text|您应该阅读以下内容，并了解一些设计良好的innodb表的优点，以及如何最好地使用聚集索引--只有innodb才能使用！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1988098|该示例包含一个包含5亿行的表，查询时间为0.02秒。|1988099|MySQL+and+NoSQL:+Help+me+to+choose+the+right+one|offset|length|1988100|希望你能对此感兴趣。|1988101|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601^0|0|0|0|1C|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$F|U|G|V|1|W]]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

You should read the following and learn a little bit about the advantages of a well designed innodb table and how best to use clustered indexes - only available with innodb !

The example includes a table with 500 million rows with query times of 0.02 seconds.

<a href="https://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601">MySQL and NoSQL: Help me to choose the right one</a>

Hope you find this of interest.

blocks|key|4061719|text|另一种想法是将超过一定年龄的记录移动到历史数据库中，以便存档、报告等。如果您不需要大量的事务处理，则从事务数据存储中提取它们可能是有意义的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4061720|分离事务数据库和报告数据库是很常见的。|4061721|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Another thought is to move records beyond a certain age to a historical database for archiving, reporting, etc. If you don't need that large volume for transactional processing it might make sense to extract them from the transactional data store.

It's common to separate transactional and reporting databases.

blocks|key|2351459|text|我将做一些假设|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2351460|2351461|您每天添加的100k行具有时间戳，这些时间戳要么是实时的，要么被相对较短的时间量(最多几个小时)所偏移；您的100k行是全天添加的，或者是以几个大的批次添加的。|unordered-list-item|2351462|2351463|+data+in+|2351464|您正在使用InnoDB+engine+(坦率地说，对于大型表使用MyISAM将是疯狂的，因为如果发生崩溃，索引重建将占用令人望而却步的时间)|2351465|2351466|您还没有解释您想要生成什么样的报告，但是我假设您的表是这样的：|2351467|+CREATE+TABLE+logdata+(
+++dateandtime+some_timestamp_type+NOT+NULL,
+++property1+some_type_1+NOT+NULL,
+++property2+some_type_2+NOT+NULL,
+++some_quantity+some_numerical_type+NOT+NULL,
+++...+some+other+columns+not+required+for+reports+...
+++...+some+indexes+...

+);|code-block|syntax|javascript|2351468|你的报告看起来像|2351469|SELECT+count(*),+SUM(some_quantity),+property1+FROM+logdata+WHERE+dateandtime+BETWEEEN+some_time_range+GROUP+BY+property1;
SELECT+count(*),+SUM(some_quantity),+property2+FROM+logdata+WHERE+dateandtime+BETWEEEN+some_time_range+GROUP+BY+property2;|2351470|现在，正如我们所看到的，这两个报告都在扫描大量的表，因为您正在报告许多行。|2351471|时间范围越大，报告的速度就越慢。此外，如果有很多其他列(比如一些varchars或blob)对报告不感兴趣，那么它们也会减慢您的报告速度(因为服务器仍然需要检查这些行)。|2351472|您可以使用几种可能的技术来加速此过程：|2351473|2351474|为每种类型的报表添加覆盖索引，以支持所需的列并省略不需要的列。这可能会有很大帮助，但会根据要报告的维缓慢地插入down.|2351475|Summarise数据。在这个虚构的案例中，您的所有报表要么计算行数，要么对具有适当主键/索引/列的some_quantity.|2351476|Build镜像表(包含相同数据)进行求和(SUM)，以提高报表速度。|2351477|使用列引擎(例如Infobright)|2351478|2351479|摘要通常是一个有吸引力的选择，如果你的用例支持它；|2351480|你可能想问一个更详细的问题，解释一下你的用例。|2351481|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1K|8|@]|9|@]|A|$]]|$1|F|3|-4|5|E|7|1L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1M|8|@]|9|@]|A|$]]|$1|I|3|J|5|E|7|1N|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1P|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1Q|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|1R|8|@]|9|@]|A|$]]|$1|U|3|V|5|P|7|1S|8|@]|9|@]|A|$Q|R]]|$1|W|3|X|5|6|7|1T|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1U|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1V|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]|$1|13|3|14|5|E|7|1X|8|@]|9|@]|A|$]]|$1|15|3|16|5|E|7|1Y|8|@]|9|@]|A|$]]|$1|17|3|18|5|E|7|1Z|8|@]|9|@]|A|$]]|$1|19|3|1A|5|E|7|20|8|@]|9|@]|A|$]]|$1|1B|3|-4|5|6|7|21|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|6|7|22|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|23|8|@]|9|@]|A|$]]|$1|1G|3|-4|5|6|7|24|8|@]|9|@]|A|$]]]|1H|$]]

I am going to make some assumptions

<ul>
<li>Your 100k rows added every day have timestamps which are either real-time, or are offset by a relatively short amount of time (hours at most); your 100k rows are added either throughout the day or in a few big batches.</li>
<li>The data are never updated</li>
<li>You are using InnoDB engine (Frankly you would be insane to use MyISAM for large tables because in the event of a crash, index rebuild takes a prohibitive time)</li>
</ul>

You haven't explained what kind of reports you're trying to generate, but I'm assuming that your table looks like this:

<pre><code> CREATE TABLE logdata (
 dateandtime some_timestamp_type NOT NULL,
 property1 some_type_1 NOT NULL,
 property2 some_type_2 NOT NULL,
 some_quantity some_numerical_type NOT NULL,
 ... some other columns not required for reports ...
 ... some indexes ...

 );
</code></pre>

And that your reports look like

<pre><code>SELECT count(*), SUM(some_quantity), property1 FROM logdata WHERE dateandtime BETWEEEN some_time_range GROUP BY property1;
SELECT count(*), SUM(some_quantity), property2 FROM logdata WHERE dateandtime BETWEEEN some_time_range GROUP BY property2;
</code></pre>

Now, as we can see, both of these reports are doing a scan of a large amount of the table, because you are reporting on a lot of rows.

The bigger the time range becomes the slower the reports will be. Moreover, if you have a lot of OTHER columns (say some varchars or blobs) which you aren't interested in reporting on, then they slow your report down too (because the server still needs to inspect the rows).

You can use several possible techniques for speeding this up:

<ul>
<li>Add covering index for each type of report, to support the columns you need and omit columns you don't. This may help a lot but slow inserts down.</li>
<li>Summarise data according to the dimension(s) that you want to report on. In this ficticious case, all your reports are either counting rows, or SUM()ing some_quantity.</li>
<li>Build mirror tables (containing the same data) which have appropriate primary keys / indexes/ columns to make the reports faster.</li>
<li>Use a column engine (e.g. Infobright)</li>
</ul>

Summarisation is usually an attractive option if your use-case supports it;

You may wish to ask a more detailed question with an explanation of your use-case.

blocks|key|2351398|text|如果您知道某个特定文件可能会通过在脚本开头调用set_time_limit+(0);来超出时间限制，则可以暂时关闭该文件的时间限制。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2351399|还应考虑其他注意事项，如将非常旧的数据编入索引或将非常旧的数据归档到不同的表。|2351400|entityMap^0|N|J|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

The time limit can be temporarily turned off for a particular file if you know that it is going to potentially run over the time limit by calling <code>set_time_limit (0);</code> at the start of your script.

Other considerations such as indexing or archiving very old data to a different table should also be looked at.

blocks|key|4061565|text|最好的选择是像MongoDB或CouchDB这样的数据库，它们都是面向存储大量数据的非关系数据库。这是假设您已经调整了MySQL安装以提高性能，并且您的情况不会从并行化中受益。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4061566|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Your best bet is something like MongoDB or CouchDB, both of which are non-relational databases oriented toward storing massive amounts of data. This is assuming that you've already tweaked your MySQL installation for performance and that your situation wouldn't benefit from parallelization.

I have a table in which approx 100,000 rows are added every day. I am supposed to generate reports from this table. I am using PHP to generate these reports. Recently the script which used to do this is taking too long to complete. How can I improve the performance by shifting to something else than MYSQL which is scalable in the long run.

MYSQL table becoming large

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我有一个表，其中大约每天添加100,000行。我应该从这个表中生成报告。我正在使用PHP来生成这些报告。最近用来做这件事的脚本花了太长时间才完成。我如何才能通过转移到MYSQL之外的东西来提高性能，从长远来看，MYSQL是可扩展的。

问MYSQL表变大
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MYSQL表变大EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MYSQL表变大
EN