我可以降低MySQL速度以突出显示哪个查询导致缓慢吗？

I'm working on an API written in NodeJS and connecting to a MySQL 5.5 database. On our live system, we have one query that's running at over 7 seconds. We've traced this down to a couple of tables having over 5 millions records in and incorrectly indexed.

我正在研究用NodeJS编写的API并连接到MySQL 5.5数据库。在我们的实时系统上,我们有一个查询运行超过7秒。我们已将其追溯到几个表中,其中包含超过5百万条记录并错误地编入索引。

On our dev areas, we don't have that number of records. I think I've identified the query that's causing the problem but, short of putting 5 millions records on my laptop (which will take ages to generate), I cannot prove it.

在我们的开发区域,我们没有那么多的记录。我想我已经确定了导致问题的查询,但是,在我的笔记本电脑上放置了5百万条记录(生成需要很长时间),我无法证明这一点。

Is there a setting/technique that I can use to emulate having millions of database records without actually having the records in?

是否有一个设置/技术可以用来模拟拥有数百万个数据库记录而不实际拥有记录?

3 个解决方案

#1

You can write Perl or Python script to populate your big test tables. If you do it as single transaction, it should not take very long time.

您可以编写Perl或Python脚本来填充大型测试表。如果您将其作为单个事务执行,则不应该花费很长时间。

If MySQL had generate_series() supported by PostgreSQL, it would have been much easier to do without scripting, but unfortunately, it doesn't :(.

如果MySQL拥有PostgreSQL支持的generate_series(),那么没有脚本就可以轻松实现,但不幸的是,它没有:(。

However, you still can easily create big tables in MySQL (or any other SQL database) without scripting. Main idea is to use INSERT INTO ... SELECT like this:

但是,您仍然可以轻松地在MySQL(或任何其他SQL数据库)中创建大表而无需编写脚本。主要思想是使用INSERT INTO ... SELECT这样:

CREATE TABLE mytable (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(16)
);
INSERT INTO mytable(name) VALUES ('Blah');
INSERT INTO mytable(name) SELECT name FROM mytable;
INSERT INTO mytable(name) SELECT name FROM mytable;
...

Note that each next INSERT doubles table size.

请注意,每个下一个INSERT都会使表大小加倍。

Using this approach, this SQLFiddle creates 1 million test rows using about 20 lines of SQL in less than 5 seconds.

使用这种方法,这个SQLFiddle在不到5秒的时间内使用大约20行SQL创建了100万个测试行。

#2

In Oracle, you can load in table statistics to from other databases - ie production to dev - in effect, mock the data volumes used when doing things like explain plans. Might be worth seeing if something equivalent exists under MySQL. It's not perfect, but would give you a better idea of what's going on under the hood.

在Oracle中,您可以将表统计信息加载到其他数据库中 - 即生成到dev - 实际上,模拟在执行解释计划等操作时使用的数据量。如果在MySQL下存在等效的东西,可能值得一看。它并不完美,但可以让你更好地了解幕后发生的事情。

Sorry I can't be more specific, I've just woken up and I haven't had tea yet.

对不起,我不能更具体,我刚刚醒来,我还没喝茶。

#3

My original thought was of get a lot of data into the databases to highlight the issue. I managed to get a million records in, just by leaving a script hammering the (dev) API overnight on.

我最初的想法是将大量数据导入数据库以突出问题。我设法获得了一百万条记录,只需在一夜之间留下一个锤击(dev)API的脚本。

The EXPLAIN function was pretty helpful. I also found the EXPLAIN EXTENDED one pretty useful too. However, neither of these highlighted the problem as it wasn't an indexing issue.

EXPLAIN函数非常有用。我还发现EXPLAIN EXTENDED也很有用。但是,这些都没有突出问题,因为它不是索引问题。

I also found the "RESET QUERY CACHE" function useful, as this cleared down the cached data for debugging purposes.

我还发现“RESET QUERY CACHE”函数很有用,因为这样可以清除缓存的数据以进行调试。

Some numpty had put a "WHERE DATE_FORMAT() = ''" in there. Although quite annoyed at how long it took me to notice it (it was a Friday afternoon - be kind), I found and fixed the issue. Thanks all.

一些numpty在那里放了一个“WHERE DATE_FORMAT()=''”。虽然我花了多长时间才注意到它(这是星期五下午 - 善良),但我发现并解决了这个问题。谢谢大家。

The moral of the story is: NEVER STICK FUNCTIONS IN WHERE CLAUSES!!!

这个故事的寓意是:从来没有粘贴功能!

#1