在 mysql 中执行 SELECT ... WHERE id IN ... 的更快方法

发布于 2024-12-21 09:10:18 字数 380 浏览 0 评论 0原文

当我发现我的数据库存在一个巨大问题时，我正在进行一个大学项目。使用wamp和一个庞大的（300Mb）数据库，但只有几个表，我的查询非常慢:(所有表都是用MyISAM引擎创建的。所有设置都是默认的，我没有任何优化经验。我需要考虑一些更好的方法来做到这一点，但现在我的问题是以下查询的最佳替代品是什么：

SELECT * FROM `payments` WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

我不能使用左连接或我在这里找到的任何类似的解决方案，因为这些 ID (1,2,3, 4,5,...)不是来自数据库。用户选择他想要删除的付款，并且在下一个屏幕上显示付款详细信息

（仅供参考），付款表有超过一百万条记录:)。

原文

I am in the middle of a uni project, when I discovered a huge problem with my database. Using wamp, and a massive (300Mb) database but with just a few tables my queries are very slow :( All tables are created with MyISAM engine. All settings are on default, I am not experienced in any optimisation. I will need to think of some better way to do it, but for now my question is what is the best substitute for the following query:

SELECT * FROM `payments` WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

I can't use left join or any similar solution I have found here, because those IDs (1,2,3,4,5, ...) are not coming from the database. User select the payments he wants to delete, and on the next screen payment details are displayed.

FYI, payments table has more than a million records :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

香橙ぽ 2024-12-28 09:10:18

对于连续范围：

SELECT * FROM payments WHERE id BETWEEN 1 AND 10

如果范围不相交：
创建一个索引内存表，其中包含值。

CREATE TABLE mem_table (
  pk unsigned integer primary key
) ENGINE = MEMORY;

INSERT INTO mem_table (pk) VALUES (1),(2),...,(10);

SELECT p.* FROM payments p
INNER JOIN mem_table m ON (m.pk = p.id);

请参阅： http://dev.mysql.com/doc /refman/5.0/en/memory-storage-engine.html

PS
确保您在 id 上有索引（这实际上应该是主键）。

For a continuos range:

SELECT * FROM payments WHERE id BETWEEN 1 AND 10

If the range is disjoint:
Create an indexed memory table with the values in it.

CREATE TABLE mem_table (
  pk unsigned integer primary key
) ENGINE = MEMORY;

INSERT INTO mem_table (pk) VALUES (1),(2),...,(10);

SELECT p.* FROM payments p
INNER JOIN mem_table m ON (m.pk = p.id);

See: http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

PS
Make sure you have an index on id (this should really be the primary key).

回复收藏 0 原文

~没有更多了~