在我的 SQLite 表中資料將是這樣的
UNIQUE_DATA_ID, DET_ID_1, DET_ID_2, DET_ID_3, DET_ID_4, DET_ID_5, DET_ID_6, DET_ID_7, DET_ID_8, DET_ID_9, DET_ID_10, DET_ID_11, DET_ID_12
[1, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[2, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[3, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[4, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[5, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[6, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[7, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
無論 UNIQUE_DATA_ID 是什么，我都需要獲得與條件匹配的單行。在上表中，每行只有 UNIQUE_DATA_ID 不同。
這是我的查詢
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
如果我運行它，我將從上表中獲取所有值，并且與有限制的查詢相比，它有點快（1ms）。
我只需要得到一行，所以我使用了 1 的限制
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011 LIMIT 1
但這需要 5ms 的時間。
在這個表中，只有 UNIQUE_DATA_ID 被索引。這個 UNIQUE_DATA_ID 對于每一行都是不同的，因為它來自另一個表。
我可以通過哪種方式優化此選擇查詢并獲得準確的一行。
還有像這樣索引 DET_ID_1 之后的內部查詢呢
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
uj5u.com熱心網友回復：
你確定你的觀察是正確的嗎？
以下內容根據您的問題中提供的資訊，回傳的結果始終顯示，對于 999999 行，查詢LIMIT 1顯著減少了經過的時間。顯然，這樣的時間取決于設備。
LIMIT 1查詢最多需要 2 毫秒，而沒有 LIMIT 則需要超過 4 秒。
但是，運行（此運行以 UNIQUE_DATA_ID 作為主鍵，而不是索引（時間大致相同））。訊息日志（來自 Navicat for SQlite）：-
DROP TABLE IF EXISTS `354567000013_6744043_DET_TABLE`
> OK
> Time: 4.592s
CREATE TABLE IF NOT EXISTS `354567000013_6744043_DET_TABLE` (UNIQUE_DATA_ID INTEGER PRIMARY KEY,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12)
> OK
> Time: 0.094s
-- CREATE UNIQUE INDEX idx_UDI ON `354567000013_6744043_DET_TABLE`(UNIQUE_DATA_ID);
WITH cte(counter) AS (SELECT 1 UNION ALL SELECT counter 1 FROM cte LIMIT 999999)
INSERT INTO `354567000013_6744043_DET_TABLE`
(UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12)
SELECT counter,178,-6003041,-1,606245,6006919,6007,600113,-1,600011,-1,6013,-1
FROM cte
> Affected rows: 999999
> Time: 2.661s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
LIMIT 1
> OK
> Time: 0.002s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
> OK
> Time: 4.507s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
> OK
> Time: 4.107s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
LIMIT 1
> OK
> Time: 0s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
> OK
> Time: 4.241s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
LIMIT 1
> OK
> Time: 0s
如果根據您使用的已編輯問題：-
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
然后結果顯示略微改進到略低于 4 秒（再次 LIMIT 顯著減少了時間）：-
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
LIMIT 1
> OK
> Time: 0s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
> OK
> Time: 3.919s
EXPLAIN QUERY PLAN
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
> OK
> Time: 0s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
LIMIT 1
> OK
> Time: 0s
請注意EXPLAIN QUERY PLAN這會導致：-
id parent notused detail
2 0 0 SCAN TABLE 354567000013_6744043_DET_TABLE
7 0 0 SCALAR SUBQUERY 1
12 7 0 SCAN TABLE 354567000013_6744043_DET_TABLE
36 7 0 USE TEMP B-TREE FOR DISTINCT
與原始查詢相反： -
id parent notused detail
2 0 0 SCAN TABLE 354567000013_6744043_DET_TABLE
你也可以使用解釋
對于帶有子查詢/不同的查詢，結果是：-
addr opcode p1 p2 p3 p4 p5 comment
0 Init 0 51 0 00 Start at 51
1 OpenRead 0 2 0 13 00 root=2 iDb=0; 354567000013_6744043_DET_TABLE
2 Rewind 0 50 0 00
3 Column 0 1 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_1
4 Integer 33 3 0 00 r[3]=33; return address
5 Once 0 33 0 00
6 Null 0 4 4 00 r[4..4]=NULL; Init subquery result
7 Integer 1 5 0 00 r[5]=1; LIMIT counter
8 OpenEphemeral 2 0 0 k(1,B) 08 nColumn=0
9 OpenRead 1 2 0 10 00 root=2 iDb=0; 354567000013_6744043_DET_TABLE
10 Rewind 1 33 0 00
11 Column 1 2 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_2
12 Ne 7 32 6 (BINARY) 51 if r[6]!=r[7] goto 32
13 Column 1 3 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_3
14 Ne 8 32 6 (BINARY) 51 if r[6]!=r[8] goto 32
15 Column 1 4 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_4
16 Ne 9 32 6 (BINARY) 51 if r[6]!=r[9] goto 32
17 Column 1 5 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_5
18 Ne 10 32 6 (BINARY) 51 if r[6]!=r[10] goto 32
19 Column 1 6 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_6
20 Ne 11 32 6 (BINARY) 51 if r[6]!=r[11] goto 32
21 Column 1 7 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_7
22 Ne 12 32 6 (BINARY) 51 if r[6]!=r[12] goto 32
23 Column 1 8 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_8
24 Ne 8 32 6 (BINARY) 51 if r[6]!=r[8] goto 32
25 Column 1 9 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_9
26 Ne 13 32 6 (BINARY) 51 if r[6]!=r[13] goto 32
27 Column 1 1 4 00 r[4]=354567000013_6744043_DET_TABLE.DET_ID_1
28 Found 2 32 4 1 00 key=r[4]
29 MakeRecord 4 1 6 00 r[6]=mkrec(r[4])
30 IdxInsert 2 6 4 1 10 key=r[6]
31 DecrJumpZero 5 33 0 00 if (--r[5])==0 goto 33
32 Next 1 11 0 01
33 Return 3 0 0 00
34 Ne 4 49 1 (BINARY) 51 if r[1]!=r[4] goto 49
35 Rowid 0 14 0 00 r[14]=rowid
36 Column 0 1 15 00 r[15]=354567000013_6744043_DET_TABLE.DET_ID_1
37 Column 0 2 16 00 r[16]=354567000013_6744043_DET_TABLE.DET_ID_2
38 Column 0 3 17 00 r[17]=354567000013_6744043_DET_TABLE.DET_ID_3
39 Column 0 4 18 00 r[18]=354567000013_6744043_DET_TABLE.DET_ID_4
40 Column 0 5 19 00 r[19]=354567000013_6744043_DET_TABLE.DET_ID_5
41 Column 0 6 20 00 r[20]=354567000013_6744043_DET_TABLE.DET_ID_6
42 Column 0 7 21 00 r[21]=354567000013_6744043_DET_TABLE.DET_ID_7
43 Column 0 8 22 00 r[22]=354567000013_6744043_DET_TABLE.DET_ID_8
44 Column 0 9 23 00 r[23]=354567000013_6744043_DET_TABLE.DET_ID_9
45 Column 0 10 24 00 r[24]=354567000013_6744043_DET_TABLE.DET_ID_10
46 Column 0 11 25 00 r[25]=354567000013_6744043_DET_TABLE.DET_ID_11
47 Column 0 12 26 00 r[26]=354567000013_6744043_DET_TABLE.DET_ID_12
48 ResultRow 14 13 0 00 output=r[14..26]
49 Next 0 3 0 01
50 Halt 0 0 0 00
51 Transaction 0 0 13241 0 01 usesStmtJournal=0
52 Integer -6003041 7 0 00 r[7]=-6003041
53 Integer -1 8 0 00 r[8]=-1
54 Integer 606245 9 0 00 r[9]=606245
55 Integer 6006919 10 0 00 r[10]=6006919
56 Integer 6007 11 0 00 r[11]=6007
57 Integer 600113 12 0 00 r[12]=600113
58 Integer 600011 13 0 00 r[13]=600011
59 Goto 0 1 0 00
相對于：-
addr opcode p1 p2 p3 p4 p5 comment
0 Init 0 35 0 00 Start at 35
1 OpenRead 0 2 0 13 00 root=2 iDb=0; 354567000013_6744043_DET_TABLE
2 Rewind 0 34 0 00
3 Column 0 2 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_2
4 Ne 2 33 1 (BINARY) 51 if r[1]!=r[2] goto 33
5 Column 0 3 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_3
6 Ne 3 33 1 (BINARY) 51 if r[1]!=r[3] goto 33
7 Column 0 4 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_4
8 Ne 4 33 1 (BINARY) 51 if r[1]!=r[4] goto 33
9 Column 0 5 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_5
10 Ne 5 33 1 (BINARY) 51 if r[1]!=r[5] goto 33
11 Column 0 6 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_6
12 Ne 6 33 1 (BINARY) 51 if r[1]!=r[6] goto 33
13 Column 0 7 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_7
14 Ne 7 33 1 (BINARY) 51 if r[1]!=r[7] goto 33
15 Column 0 8 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_8
16 Ne 3 33 1 (BINARY) 51 if r[1]!=r[3] goto 33
17 Column 0 9 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_9
18 Ne 8 33 1 (BINARY) 51 if r[1]!=r[8] goto 33
19 Rowid 0 9 0 00 r[9]=rowid
20 Column 0 1 10 00 r[10]=354567000013_6744043_DET_TABLE.DET_ID_1
21 Column 0 2 11 00 r[11]=354567000013_6744043_DET_TABLE.DET_ID_2
22 Column 0 3 12 00 r[12]=354567000013_6744043_DET_TABLE.DET_ID_3
23 Column 0 4 13 00 r[13]=354567000013_6744043_DET_TABLE.DET_ID_4
24 Column 0 5 14 00 r[14]=354567000013_6744043_DET_TABLE.DET_ID_5
25 Column 0 6 15 00 r[15]=354567000013_6744043_DET_TABLE.DET_ID_6
26 Column 0 7 16 00 r[16]=354567000013_6744043_DET_TABLE.DET_ID_7
27 Column 0 8 17 00 r[17]=354567000013_6744043_DET_TABLE.DET_ID_8
28 Column 0 9 18 00 r[18]=354567000013_6744043_DET_TABLE.DET_ID_9
29 Column 0 10 19 00 r[19]=354567000013_6744043_DET_TABLE.DET_ID_10
30 Column 0 11 20 00 r[20]=354567000013_6744043_DET_TABLE.DET_ID_11
31 Column 0 12 21 00 r[21]=354567000013_6744043_DET_TABLE.DET_ID_12
32 ResultRow 9 13 0 00 output=r[9..21]
33 Next 0 3 0 01
34 Halt 0 0 0 00
35 Transaction 0 0 13241 0 01 usesStmtJournal=0
36 Integer -6003041 2 0 00 r[2]=-6003041
37 Integer -1 3 0 00 r[3]=-1
38 Integer 606245 4 0 00 r[4]=606245
39 Integer 6006919 5 0 00 r[5]=6006919
40 Integer 6007 6 0 00 r[6]=6007
41 Integer 600113 7 0 00 r[7]=600113
42 Integer 600011 8 0 00 r[8]=600011
43 Goto 0 1 0 00
以上內容在 The SQLite Bytecode Engine中進行了介紹， EXPLAIN QUERY PLAN的鏈接還包括有用的鏈接，例如指向The Next Generation Query Planner的鏈接，它解釋了很多關于查詢優化的內容。這還有一些鏈接，您可能希望考慮第 4 段中的鏈接。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/410072.html
標籤：
上一篇：Chrome97-未從Office365OAuth回呼設定Cookie
下一篇：MySQLSQL性能需要一些改進