file文件的并行选择查询

Question

我将数据库存储在sqlite中，一些select查询返回数百万行。我想编写一个并行应用程序，其中每个进程将：

1. 单独打开sqlite文件(只读)
2. 运行一个只返回部分行的查询。
3. 处理每个进程中的行

我的问题是关于算法的第二部分。如何更改我的SELECT语句以使其返回部分行？

我想到了OFFSET/LIMIT，但这意味着我知道最后的行数，而我不知道。另外，查询的运行时很重要，我不想为了获得行总数而两次运行相同的查询。

Answer 1

考虑

我考虑了偏移量/限制，但这意味着我知道最后一行的数目，而我不知道。

您可以通过使用一个minuscule表来维护一个计数，该表中每个表都有一行，每个表都有两个列，即表名(如果只有一个表就不需要)和计数。两个触发器可以自动维护计数。

如果使用AUTOINCREMENT，另一种类似但不太密集的方法(以确定计数的概念)是通过访问sqlite_sequence表的seq列获得一个估计量(最大可能不需要任何删除)。

不妨考虑以下两者的例子：

DROP TRIGGER IF EXISTS newmain1row;
DROP TRIGGER IF EXISTS deletedmain1row;
DROP TABLE IF EXISTS rowcount;
DROP TABLE IF EXISTS main1;
DROP TABLE IF EXISTS main2;
CREATE TABLE IF NOT EXISTS main1 (id INTEGER PRIMARY KEY, mycolumn TEXT);
CREATE TABLE IF NOT EXISTS main2 (id INTEGER PRIMARY KEY AUTOINCREMENT, mycolumn TEXT); -- is it slower/faster than 
CREATE TABLE IF NOT EXISTS rowcount (tablename TEXT PRIMARY KEY, rowcount INTEGER) WITHOUT ROWID;
INSERT INTO rowcount VALUES('main1',0); -- initialise rowcount table

-- Trigger for when a new row is added to the table
CREATE TRIGGER newmain1row 
    AFTER INSERT ON main1 BEGIN 
        UPDATE rowcount SET rowcount = rowcount + 1 WHERE tablename = 'main1';
    END
;

-- Trigger for when a row is deleted from the table
CREATE TRIGGER deletedmain1row
    AFTER DELETE ON main1 BEGIN
      UPDATE rowcount SET rowcount = rowcount - 1 WHERE tablename = 'main1';
    END
;

-- populate the two example main tables twice so in reverse order 2nd time
-- with a million rows so 2 million per table
WITH RECURSIVE cte1(counter) AS 
    (
        SELECT 1 
        UNION ALL SELECT counter+1 FROM cte1 WHERE counter < 1000000
    )
INSERT INTO main1 (mycolumn) SELECT counter FROM cte1
;
WITH RECURSIVE cte1(counter) AS 
    (
        SELECT 1 
        UNION ALL SELECT counter+1 FROM cte1 WHERE counter < 1000000
    )
INSERT INTO main2 (mycolumn) SELECT counter FROM cte1
;

WITH RECURSIVE cte1(counter) AS 
    (
        SELECT 1 
        UNION ALL SELECT counter+1 FROM cte1 WHERE counter < 1000000
    )
INSERT INTO main2 (mycolumn) SELECT counter FROM cte1
;
WITH RECURSIVE cte1(counter) AS 
    (
        SELECT 1 
        UNION ALL SELECT counter+1 FROM cte1 WHERE counter < 1000000
    )
INSERT INTO main1 (mycolumn) SELECT counter FROM cte1
;
-- DELETE 40,000 rows
DELETE FROM main1 WHERE CAST(mycolumn AS INTEGER) % 50 = 0;
DELETE FROM main2 WHERE CAST(mycolumn AS INTEGER) % 50 = 0;
-- get the number of rows
SELECT rowcount FROM rowcount WHERE tablename = 'main1';
SELECT seq FROM sqlite_sequence WHERE name = 'main2';

这个:-

1. 删除触发器和表，使其重新运行。
2. 为每个方法创建两个主表(main1和main2)，即除了后者具有AUTOINCREMENT之外，两者基本相同
3. 为第一种方法创建计数器表，并为main1表添加计数为0的行。
4. 为第一个(main1)表插入一行(增加计数)和删除一行(递减计数)创建2个触发器。
5. 将2,000,000行插入到每个表中的4批1,000,000行中，颠倒顺序，以便更好地确定时间。
6. 从每个表中删除40,000行。
7. 从每个方法中检索计数(注意，从第二个方法返回的计数是2,000,000，而不是1,960,000，因为这是最后分配的id)。

日志:-

DROP TRIGGER IF EXISTS newmain1row
> OK
> Time: 0.187s


DROP TRIGGER IF EXISTS deletedmain1row
> OK
> Time: 0.084s


DROP TABLE IF EXISTS rowcount
> OK
> Time: 0.153s


DROP TABLE IF EXISTS main1
> OK
> Time: 2.534s


DROP TABLE IF EXISTS main2
> OK
> Time: 2.547s


CREATE TABLE IF NOT EXISTS main1 (id INTEGER PRIMARY KEY, mycolumn TEXT)
> OK
> Time: 0.158s


CREATE TABLE IF NOT EXISTS main2 (id INTEGER PRIMARY KEY AUTOINCREMENT, mycolumn TEXT)
> OK
> Time: 0.167s


-- is it slower/faster than 
CREATE TABLE IF NOT EXISTS rowcount (tablename TEXT PRIMARY KEY, rowcount INTEGER) WITHOUT ROWID
> OK
> Time: 0.167s


INSERT INTO rowcount VALUES('main1',0)
> Affected rows: 1
> Time: 0.165s


-- initialise rowcount table

-- Trigger for when a new row is added to the table
CREATE TRIGGER newmain1row 
    AFTER INSERT ON main1 BEGIN 
        UPDATE rowcount SET rowcount = rowcount + 1 WHERE tablename = 'main1';
    END
> Affected rows: 1
> Time: 0.086s


-- Trigger for when a row is deleted from the table
CREATE TRIGGER deletedmain1row
    AFTER DELETE ON main1 BEGIN
      UPDATE rowcount SET rowcount = rowcount - 1 WHERE tablename = 'main1';
    END
> Affected rows: 1
> Time: 0.096s


-- populate the two example main tables twice so in reverse order 2nd time
-- with a million rows so 2 million per table
WITH RECURSIVE cte1(counter) AS 
    (
        SELECT 1 
        UNION ALL SELECT counter+1 FROM cte1 WHERE counter < 1000000
    )
INSERT INTO main1 (mycolumn) SELECT counter FROM cte1
> Affected rows: 1000000
> Time: 1.199s


WITH RECURSIVE cte1(counter) AS 
    (
        SELECT 1 
        UNION ALL SELECT counter+1 FROM cte1 WHERE counter < 1000000
    )
INSERT INTO main2 (mycolumn) SELECT counter FROM cte1
> Affected rows: 1000000
> Time: 0.811s


WITH RECURSIVE cte1(counter) AS 
    (
        SELECT 1 
        UNION ALL SELECT counter+1 FROM cte1 WHERE counter < 1000000
    )
INSERT INTO main2 (mycolumn) SELECT counter FROM cte1
> Affected rows: 1000000
> Time: 1.058s


WITH RECURSIVE cte1(counter) AS 
    (
        SELECT 1 
        UNION ALL SELECT counter+1 FROM cte1 WHERE counter < 1000000
    )
INSERT INTO main1 (mycolumn) SELECT counter FROM cte1
> Affected rows: 1000000
> Time: 1.177s


DELETE FROM main1 WHERE CAST(mycolumn AS INTEGER) % 50 = 0
> Affected rows: 40000
> Time: 2.82s


DELETE FROM main2 WHERE CAST(mycolumn AS INTEGER) % 50 = 0
> Affected rows: 40000
> Time: 2.649s


-- get the number of rows
SELECT rowcount FROM rowcount WHERE tablename = 'main1'
> OK
> Time: 0s


SELECT seq FROM sqlite_sequence WHERE name = 'main2'
> OK
> Time: 0s

总时间是16秒多一点。

结果分别为1.96万和200万。