30
4
Phân tích SQL chậm, tổng số hàng 800.000+, thông qua giám sát và phân tích SQL chậm, một truy vấn mất hơn 1 giây.
Điều đặc biệt là có một trường info có kiểu là jsonb và được viết như sau: info::json->'length' là length.
Sự khác biệt giữa việc tìm kiếm trường này và không tìm kiếm trường này trong cùng một điều kiện truy vấn là 3,3 lần.
Có vẻ như giá trị json làm giảm hiệu suất truy vấn.
Có nhiều cách để lấy các trường từ jsonb (như được hiển thị bên dưới). Sự khác biệt giữa chúng là gì và chúng ảnh hưởng đến hiệu suất như thế nào?
Truy vấn các kiểu viết khác nhau:
Trung tâm cá nhân
Bài viết phát hành
từ bỏ
lựa chọn
thông tin::json
->
'
chiều dài
'
BẰNG
"thông tin::json
->
", pg_typeof(thông tin::json
->
'
chiều dài
'
), thông tin::jsonb
->
'
chiều dài
'
BẰNG
"thông tin::jsonb
->
" , pg_typeof(thông tin::jsonb
->
'
chiều dài
'
), thông tin::json
->>
'
chiều dài
'
BẰNG
"thông tin::json
->>
" , pg_typeof(thông tin::json
->>
'
chiều dài
'
), thông tin::jsonb
->>
'
chiều dài
'
BẰNG
"thông tin::jsonb
->>
" , pg_typeof(thông tin::jsonb
->>
'
chiều dài
'
), thông tin
->
'
chiều dài
'
BẰNG
"thông tin
->
" , pg_typeof(thông tin
->
'
chiều dài
'
), thông tin
->
'
chiều dài
'
BẰNG
"thông tin
->
" , pg_typeof(thông tin
->
'
chiều dài
'
), thông tin
->>
'
chiều dài
'
BẰNG
"thông tin
->>
" , pg_typeof(thông tin
->>
'
chiều dài
'
), thông tin
->>
'
chiều dài
'
BẰNG
"thông tin
->>
" , pg_typeof(thông tin
->>
'
chiều dài
'
)
từ
giới hạn t_test_json
1
;
kết quả.
info::json-> | pg_typeof | info::jsonb-> | pg_typeof | info::json->> | pg_typeof | info::jsonb->> | pg_typeof | info-> | pg_typeof | info-> | pg_typeof | info->> | pg_typeof | info->> | pg_typeof --------------+------------+--------------+------------+-----------+-----------+----------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------- 123,9 | json | 123,9 | jsonb | 123,9 | văn bản | 123,9 | văn bản | 123,9 | jsonb | 123,9 | jsonb | 123,9 | văn bản | 123.9 | văn bản
Tóm tắt phân tích.
0,027..0.027 hàng = 1 vòng = 1) Thời gian lập kế hoạch: 0,056 ms Thời gian thực hiện: 0,047 ms (4 hàng) ------------------------------------------------------------------------------------------ Giới hạn (chi phí = 0,00..0.04 hàng = 1 chiều rộng = 36) Thời gian: 0,056 ms Thời gian thực hiện: 0,047 ms (4 hàng) jihite => Giải thích Phân tích thánh chiến-> Chọn ..015 hàng = 1 vòng == 0; 15 hàng = 1 vòng lặp = 1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0,031 ms (4 hàng) jihite => 0,010 hàng = 1 vòng = 1)-> seq scan trên t_test_json (chi phí = 0,00..23,12 hàng = 750 chiều rộng = 36) "Thông tin :: jsonb->", pg_typeOf (thông tin :: jsonb-> 'length') jihite-> từ t_test_json giới hạn 1; Quét trên t_test_json (chi phí = 0,00..23,12 hàng = 750 chiều rộng = 36) (thời gian thực tế = 0,009..0.009 hàng = 1 vòng = 1) 0,025 hàng = 1 vòng = 1) Thời gian lập kế hoạch: 0,056 ms Thời gian thực hiện: 0,046 ms (4 hàng) jihite => jihite => Giải thích phân tích jihite-> chọn jihite-> thông tin :: .04 hàng = 1 chiều rộng = 36) (thời gian thực tế = 0,026..0.027 hàng = 1 vòng Phân tích jihite-> chọn jihite-> thông tin :: jsonb->> 'length' là "info :: jsonb->>"; , pg_typeof(info::jsonb->>'length') jihite-> from t_test_json limit 1; KẾ HOẠCH TRUY VẤN ------------------------------------------------------------------------------------------------------------------ Giới hạn (chi phí=0,00..0,03 hàng=1 chiều rộng=36) (thời gian thực tế=0,012..0,012 hàng=1 vòng lặp=1) -> Quét chuỗi trên t_test_json (chi phí=0,00..23,12 hàng=750 chiều rộng=36) (thời gian thực tế=0,011..0,011 hàng=1 vòng lặp=1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0.0,029 ms (4 hàng) jihite=> jihite=> GIẢI THÍCH PHÂN TÍCH jihite-> select jihite-> info->'length' AS "info->" , pg_typeof(info->'length' ) jihite-> from t_test_json giới hạn 1; KẾ HOẠCH TRUY VẤN ------------------------------------------------------------------------------------------------------------------ Giới hạn (chi phí=0,00..0,03 hàng=1 chiều rộng=36) (thời gian thực tế=0,014..0,014 hàng=1 vòng lặp=1) -> Quét chuỗi trên t_test_json (chi phí=0,00..23,12 hàng=750 chiều rộng=36) (thời gian thực tế=0,013..0,013 hàng=1 vòng lặp=1) Thời gian lập kế hoạch: 0,052 ms Thời gian thực hiện: 0,030 ms (4 hàng) jihite=> jihite=> 0,012..0.012 hàng = 1 vòng -------------------------------------------------------------- Giới hạn (chi phí = 0,00..0.03 hàng = 1 chiều rộng = 36) (Thời gian thực tế = 0,013..0.013 hàng = 1 vòng lặp = 1) Thời gian thực hiện: 0,029 ms (4 hàng) jihite => jihite => Giải thích Phân tích jihite-> chọn jihite-> info-> 'length' là "info->", pg_typeOf (thông tin-> 'length') jihite-> từ t_test_json giới hạn 1; 0,014..0.014 hàng = 1 vòng ---------------------------------------------------------------------- Giới hạn (chi phí = 0,00..0.03 hàng = 1 chiều rộng = 36) Thời gian thực hiện: 0,030 ms (4 hàng) jihite => jihite => Giải thích Phân tích jihite-> chọn jihite-> thông tin->> 'length' là "thông tin->>", pg_typeof (thông tin->> 'length') 0,011..0,011 hàng=1 vòng lặp=1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0,029 ms (4 hàng)0,012 hàng = 1 vòng -------------------------------------------------- Giới hạn (chi phí = 0,00..0,03 hàng = 1 chiều rộng = 36) (thời gian thực tế = 0,013..0.013 hàng = 1 vòng lặp = 1) : 0,029 ms (4 hàng) jihite => jihite => t_test_json giới hạn 1; KẾ HOẠCH TRUY VẤN ------------------------------------------------------------------------------------------------------------------ Giới hạn (chi phí=0,00..0,03 hàng=1 chiều rộng=36) (thời gian thực tế=0,012..0,013 hàng=1 vòng lặp=1) -> Quét Seq trên t_test_json (chi phí=0,00..23,12 hàng=750 chiều rộng=36) (thời gian thực tế=0,011..0,011 hàng=1 vòng lặp=1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0,030 ms (4 hàng) jihite=> jihite=> GIẢI THÍCH PHÂN TÍCH jihite-> chọn jihite-> info->>'length' NHƯ "info->>" , pg_typeof(info->>'length' ) jihite-> từ t_test_json giới hạn 1; KẾ HOẠCH TRUY VẤN -------------------------------------------------------------------------------------------------------------- Giới hạn (chi phí=0,00..0,03 hàng=1 chiều rộng=36) (thời gian thực tế=0,012..0,013 hàng=1 vòng lặp=1) -> Quét Seq trên t_test_json (chi phí=0,00..23,12 hàng=750 chiều rộng=36) (thời gian thực tế=0,011..0,011 hàng=1 vòng lặp=1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0,029 ms (4 hàng)0,012 hàng = 1 vòng -------------------------------------------------- Giới hạn (chi phí = 0,00..0,03 hàng = 1 chiều rộng = 36) (thời gian thực tế = 0,013..0.013 hàng = 1 vòng lặp = 1) : 0,029 ms (4 hàng) jihite => jihite => t_test_json giới hạn 1; KẾ HOẠCH TRUY VẤN ------------------------------------------------------------------------------------------------------------------ Giới hạn (chi phí=0,00..0,03 hàng=1 chiều rộng=36) (thời gian thực tế=0,012..0,013 hàng=1 vòng lặp=1) -> Quét Seq trên t_test_json (chi phí=0,00..23,12 hàng=750 chiều rộng=36) (thời gian thực tế=0,011..0,011 hàng=1 vòng lặp=1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0,030 ms (4 hàng) jihite=> jihite=> GIẢI THÍCH PHÂN TÍCH jihite-> chọn jihite-> info->>'length' NHƯ "info->>" , pg_typeof(info->>'length' ) jihite-> từ t_test_json giới hạn 1; KẾ HOẠCH TRUY VẤN -------------------------------------------------------------------------------------------------------------- Giới hạn (chi phí=0,00..0,03 hàng=1 chiều rộng=36) (thời gian thực tế=0,012..0,013 hàng=1 vòng lặp=1) -> Quét Seq trên t_test_json (chi phí=0,00..23,12 hàng=750 chiều rộng=36) (thời gian thực tế=0,011..0,011 hàng=1 vòng lặp=1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0,029 ms (4 hàng)03 hàng = 1 chiều rộng = 36) (thời gian thực tế = 0,012..0,013 hàng = 1 vòng lặp = 1) -> Quét chuỗi trên t_test_json (chi phí = 0,00..23,12 hàng = 750 chiều rộng = 36) (thời gian thực tế = 0,011..0,011 hàng = 1 vòng lặp = 1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0,029 ms (4 hàng)03 hàng = 1 chiều rộng = 36) (thời gian thực tế = 0,012..0,013 hàng = 1 vòng lặp = 1) -> Quét chuỗi trên t_test_json (chi phí = 0,00..23,12 hàng = 750 chiều rộng = 36) (thời gian thực tế = 0,011..0,011 hàng = 1 vòng lặp = 1) Thời gian lập kế hoạch: 0,053 ms Thời gian thực hiện: 0,029 ms (4 hàng)
Tóm tắt phân tích thời gian thực hiện.
Chuyển đổi kiểu jsonb->json được thực hiện và hiệu suất chuyển đổi (0,46ms) rõ ràng thấp hơn so với khi không chuyển đổi (0,3ms).
Thay đổi trường truy vấn: info::json->'length' thành info->>'length' để giảm tình trạng mất hiệu suất do chuyển đổi kiểu.
.
Bản thân trường là jsonb. Việc chuyển đổi bắt buộc sang ::jsonb có ảnh hưởng đến hiệu suất hay đã được tối ưu hóa trong quá trình biên dịch trước không?
Từ bài kiểm tra ứng suất của một lượng dữ liệu lớn, việc chuyển đổi sẽ có tác động đến hiệu suất, nhưng tác động này không đáng kể.
Khi giải thích và phân tích, chúng ta chủ yếu phân tích tác động của các chỉ số đến hiệu suất. Làm thế nào chúng ta có thể xem tác động cụ thể của các chức năng?
.
Nên lưu trữ dữ liệu JSON dưới dạng jsonb.
.
pg_typeof() .
.
Nếu bạn có một câu lệnh SQL thực thi chậm, bạn muốn biết điều gì đang xảy ra và cách tối ưu hóa nó. EXPLAIN ANALYSE có thể thu thập thông tin về quá trình thực thi các câu lệnh SQL của cơ sở dữ liệu, quy trình thực hiện và thời gian thực hiện, điều này có thể giúp tối ưu hóa hiệu suất.
Các thông số chính:
Thời gian thực hiện: *** ms biểu thị thời gian thực hiện SQL thực tế, không bao gồm thời gian tạo kế hoạch truy vấn.
.
# Tạo câu lệnh bảng.
tạo nên
bàn
t_test_json ( id bigserial
không
vô giá trị
SƠ ĐẲNG
CHÌA KHÓA
, nhiệm vụ
tính cách
thay đổi
không
vô giá trị
, thông tin jsonb
không
vô giá trị
, tạo_thời_gian
dấu thời gian
không
vô giá trị
mặc định
dấu thời gian hiện tại
);
# Dữ liệu thử nghiệm ứng suất.
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
1
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
2
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
3
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
4
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
5
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
6
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
7
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
8
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
9
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
10
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
11
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
12
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
13
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
14
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
15
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
16
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
17
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
18
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
19
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
chèn
vào trong
t_test_json(nhiệm vụ, thông tin)
giá trị
(
'
20
'
,
'
{"chiều dài": 123.9, "avatar": "avatar_url", "thẻ": ["python", "golang", "db"]}
'
);
.
nhập thời gian nhập psycopg dbname,
người sử dụng
, pwd, ip, cổng
=
''
,
''
,
''
,
''
,
'
5432
'
sự liên quan
=
"tên cơ sở dữ liệu
=%
S
người sử dụng
=%
mật khẩu
=%
chủ nhà
=%
thể thao
=%
S"
%
(tên cơ sở dữ liệu,
người sử dụng
, pwd, ip, cổng) db
=
psycopg.connect(kết nối) cur
=
cơ sở dữ liệu
con trỏ
() số
=
0
ống kính
=
20
vì
Tôi
TRONG
phạm vi (ống kính): s
=
time.time() sql
=
'''
chọn id, info::json->
'
chiều dài
'
theo chiều dài từ t_test_json sắp xếp theo id offset %s giới hạn 1000
'''
%
(Tôi
*
1000
) #
in
("sql:", sql) hiện tại.
thực hiện
(sql) sửa đổi
=
cur.fetchall() e
=
thời gian.thời gian()
in
("quét:", i, e
-
các số
+=
(và
-
S)
in
(
'
trung bình
'
, ss
/
ống kính)
.
Cuối cùng, bài viết này sẽ nói về lý do tại sao postgresqljson lại chậm trong việc truy xuất giá trị như vậy? Đây là phần cuối của bài viết. Nếu bạn muốn biết thêm về lý do tại sao postgresqljson lại chậm trong việc truy xuất giá trị? Vui lòng tìm kiếm các bài viết trên CFSDN hoặc tiếp tục duyệt các bài viết liên quan. Tôi hy vọng bạn sẽ ủng hộ blog của tôi trong tương lai! .
30
4
0
Tôi là một lập trình viên xuất sắc, rất giỏi!