SQLite select 语句优化建议 [英] SQLite select statement optimisation advice
问题描述
我有一个带有结构的 SQLite 表详细信息":
I have a SQLite table 'Details' with structure:
ID Name Category
---------------------
1 Matt 0
2 Shervin 0
3 Bob 0
4 Lee 0
5 Rick 0
6 Suraya 0
7 Susan 0
8 Adam 0
9 Jon 1
10 Lorna 1
... and so on .......
我想随机选择一行，然后从三个不同的行中选择三个名称(最好再次随机选择).我希望所有这些都从一个 SQLite 语句中返回.例如
I want to select a row at random, and then three names from three different rows (again preferably at random). I would like this to all be returned from one SQLite statement. E.g.
ID Name Category Name1 Name2 Name 3
----------------------------------------
3 Bob 0 Matt Lee Susan
我在这方面的尝试如下所示，但有两个问题:
My attempt at this can be seen below, but it has two problems:
- 这三个额外的名称不一定总是不同的 - 我似乎无法排除之前选择的名称，因为变量 b/c/d 不在它们自己的 COALESCE 函数之外的范围内.
- 由于每个嵌套选择都使用 Random() 函数，因此效率不高.
谁能建议另一种选择我需要的数据的方法(使用 SQLite 数据库)?欢迎提供任何帮助/建议 - 希望清楚我要实现的目标，请随时提出任何说明.
Can anyone suggest another way to select the data I need (using SQLite database)? Any help/advice is welcome - hope it is clear what I am trying to achieve, feel free to ask for any clarifications.
我目前的尝试:
SELECT a.Id,
a.Name,
a.Category,
COALESCE((SELECT b.Name
FROM Details b
WHERE b.Id NOT IN (a.Id)
AND b.Category IN (0)
ORDER BY Random()
LIMIT 1),'') as "Name1",
COALESCE((SELECT c.Name
FROM Details c
WHERE c.Id NOT IN (a.Id)
AND c.Category IN (0)
ORDER BY Random()
LIMIT 1),'') as "Name2",
COALESCE((SELECT d.Name
FROM Details d
WHERE d.Id NOT IN (a.Id)
AND d.Category IN (0)
ORDER BY Random()
LIMIT 1),'') as "Name3"
FROM Details a
AND a.Category IN (0)
ORDER BY Random()
LIMIT 1
推荐答案
我和 neurino 在一起.你还没有说为什么要把随机选择的四个名字放在一行，为什么要在后端做.
I'm with neurino here. You have not said why you need to put the four names chosen at random into a single row, and why this has to be done in the back-end.
如果您担心性能，请在您的客户端中生成随机整数(范围 >= min(pkcol) 和 <= max(pkcol) )，直到您找到四个不同的行(即实体/名称).有可能不存在具有生成的 ID 之一的行，但这只需几毫秒即可找出.采取这种随机密钥方法，您可以避免订购.即使对于具有数十亿行的表，该方法也能快速奏效.
If you're concerned about performance, generate random integers in your client (range >= min(pkcol) and <= max(pkcol) ) until you've found four distinct rows (i.e. entities/names). There's a chance that no row exists with one of the generated ids, but that takes mere milliseconds to find out. Taking that random-key approach you could avoid an order by. The approach would work quickly even for tables with billions of rows.
附言(发现它是一个iPhone应用程序后)您需要一次调用来获取最小和最大 ID 值(它是 PK，因此使用索引).然后，您至少需要再次调用数据库(再次，索引辅助)以使用随机生成的 PK 值获取四个不同的行 [其中 ID in (a, b, c , d) ] 最大调用次数未知;多少将取决于您的主键序列的密度.我不认为这会是过多的 I/O，而且它比 Random() 的订单占用的资源要少得多——尤其是在表有很多行的情况下.您始终可以随机生成一个包含 8、12、16 个 ID 的 ID 列表，并且如果返回的 ID 超过 4 个，则让您的客户端仅剔除所需的 4 行.
P.S. (After finding out it was an iPhone app) You need one call to get the min and max ID values (it's the PK so that uses an index). Then you need at least another call to the DB (again, index-assisted) to get the four distinct rows using your randomly generated PK values [where ID in (a, b, c , d) ] The maximum number of calls is unknown; how many will depend on the density of your primary key sequence. I do not believe this would be an inordinate amount of I/O and it would be considerably less resource-intensive than an order by Random()--especially if the table has many rows. You could always generate an ID list of 8, 12, 16 ids at random and have your client cull only the 4 rows required if more than 4 are returned.
P.P.S.通常，数据库连接的实例化开销很大，您不希望在循环中执行此操作，或者不希望这样做的频率超出您的需要.但是您可以打开一个连接，运行两个或三个有效的选择，每个选择返回几行，然后在完成手头的任务后关闭.
P.P.S. Typically it is the instantiation of the database connection that is expensive, and you don't want to do that in a loop or any more often than you need to. But you can open a connection, run two or three efficient selects that return a few rows each, and then close if you're done with the task at hand.
这篇关于SQLite select 语句优化建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
