|
Умолчательное направление при построении индекса по PK
#39164141
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
Что-то не очень сильно бросается в глаза разница между:
1) скоростью заливки данных (50 млн строк, тип id = bigint) при юзании asc vs desc индексов: DDL + add initial data 1.
2.
3.
4.
5.
6.
7.
8.
9.
recreate sequence g_asc;
recreate sequence g_desc;
recreate table test_asc(id bigint primary key using ascending index test_asc_id);
recreate table test_dec(id bigint primary key using descending index test_dec_id);
set term ^; execute block as declare n int = 50000000; begin while(n>0) do insert into test_asc values(gen_id(g_asc,1)) returning :n-1 into n; end^ set term ;^
commit;
set term ^; execute block as declare n int = 50000000; begin while(n>0) do insert into test_dec values(gen_id(g_desc, -1)) returning :n-1 into n; end^ set term ;^
commit;
trace 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
2016-02-05T15:15:39.4020 (2608:0x7fae326ef040) EXECUTE_STATEMENT_FINISH
e30 (ATT_7, SYSDBA:NONE, NONE, TCPv4:192.168.0.220/36061)
/opt/fb30ss/bin/isql:2631
(TRA_11, CONCURRENCY | WAIT | READ_WRITE)
Statement 27:
-------------------------------------------------------------------------------
execute block as declare n int = 50000000; begin while(n>0) do insert into test_asc values(gen_id(g_asc,1)) returning :n-1 into n; end
0 records fetched
795093 ms, 568 read(s), 530075 write(s), 401730629 fetch(es), 151488906 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$PAGES 187
TEST_ASC 50000000
2016-02-05T15:32:38.0340 (2608:0x7fae326ef040) EXECUTE_STATEMENT_FINISH
e30 (ATT_7, SYSDBA:NONE, NONE, TCPv4:192.168.0.220/36061)
/opt/fb30ss/bin/isql:2631
(TRA_20, CONCURRENCY | WAIT | READ_WRITE)
Statement 96:
-------------------------------------------------------------------------------
execute block as declare n int = 50000000; begin while(n>0) do insert into test_dec values(gen_id(g_desc, -1)) returning :n-1 into n; end
0 records fetched
796282 ms, 563 read(s), 530098 write(s), 401750452 fetch(es), 151488927 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$PAGES 187
TEST_DEC 50000000
2) выборкой по asc vs desc индексу (делал по три запуска в каждом случае; дифферент порогов = 1 млн, значения из середины листового уровня):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SQL> select * from test_asc where id between 25000000 and 26000000 order by id;
Select Expression
-> Filter
-> Table "TEST_ASC" Access By ID
-> Index "TEST_ASC_ID" Range Scan (lower bound: 1/1, upper bound: 1/1)
vs
SQL> select * from test_dec where id between -26000000 and -25000000 order by id desc;
Select Expression
-> Filter
-> Table "TEST_DEC" Access By ID
-> Index "TEST_DEC_ID" Range Scan (lower bound: 1/1, upper bound: 1/1)
trace 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
2016-02-05T15:35:11.6000 (2608:0x7fae326ef040) EXECUTE_STATEMENT_FINISH
e30 (ATT_7, SYSDBA:NONE, NONE, TCPv4:192.168.0.220/36061)
/opt/fb30ss/bin/isql:2631
(TRA_27, CONCURRENCY | WAIT | READ_WRITE)
Statement 97:
-------------------------------------------------------------------------------
select * from test_asc where id between 25000000 and 26000000 order by id
1000001 records fetched
3050 ms, 7640 read(s), 3001503 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TEST_ASC 1000001
== vs ==
Statement 403:
-------------------------------------------------------------------------------
select * from test_dec where id between -26000000 and -25000000 order by id desc
1000001 records fetched
3088 ms, 7640 read(s), 3001503 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TEST_DEC 1000001
3) статистикой индексов (практически близнецы): 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
Database "/var/db/fb30/e30.fdb"
Database header page information:
Flags 0
Generation 46
System Change Number 0
Page size 8192
ODS version 12.0
Oldest transaction 32
Oldest active 33
Oldest snapshot 33
Next transaction 34
Sequence number 0
Next attachment ID 11
Implementation HW=AMD/Intel/x64 little-endian OS=Linux CC=gcc
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Feb 2, 2016 18:57:15
Attributes
Variable header data:
*END*
Database file sequence:
File /var/db/fb30/e30.fdb is the only file
Analyzing database pages ...
TEST_ASC (128)
Primary pointer page: 181, Index root page: 182
Total formats: 1, used formats: 1
Average record length: 10.66, total records: 50000000
Average version length: 0.00, total versions: 0, max versions: 0
Average fragment length: 0.00, total fragments: 0, max fragments: 0
Average unpacked length: 16.00, compression ratio: 1.50
Pointer pages: 188, data page slots: 306752
Data pages: 306752, average fill: 55%
Primary pages: 306752, secondary pages: 0, swept pages: 0
Empty pages: 3, full pages: 306748
Fill distribution:
0 - 19% = 3
20 - 39% = 1
40 - 59% = 306748
60 - 79% = 0
80 - 99% = 0
Index TEST_ASC_ID (0)
Root page: 3026, depth: 3, leaf buckets: 73987, nodes: 50000000
Average node length: 11.68, total dup: 0, max dup: 0
Average key length: 8.14, compression ratio: 1.11
Average prefix length: 3.86, average data length: 5.14
Clustering factor: 306749, ratio: 0.01
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 3120
60 - 79% = 0
80 - 99% = 70867
TEST_DEC (129)
Primary pointer page: 381495, Index root page: 381520
Total formats: 1, used formats: 1
Average record length: 10.66, total records: 50000000
Average version length: 0.00, total versions: 0, max versions: 0
Average fragment length: 0.00, total fragments: 0, max fragments: 0
Average unpacked length: 16.00, compression ratio: 1.50
Pointer pages: 188, data page slots: 306752
Data pages: 306752, average fill: 55%
Primary pages: 306752, secondary pages: 0, swept pages: 0
Empty pages: 3, full pages: 306748
Fill distribution:
0 - 19% = 3
20 - 39% = 1
40 - 59% = 306748
60 - 79% = 0
80 - 99% = 0
Index TEST_DEC_ID (0)
Root page: 384209, depth: 3, leaf buckets: 73987, nodes: 50000000
Average node length: 11.68, total dup: 0, max dup: 0
Average key length: 8.14, compression ratio: 1.11
Average prefix length: 3.86, average data length: 5.14
Clustering factor: 306749, ratio: 0.01
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 3120
60 - 79% = 0
80 - 99% = 70867
PS. LI-V3.0.0.32323 |
|
|
