Tại bài này, tôi sẽ giúp các bạn giải quyết “một lần và mãi mãi” những hiểu lầm kinh điển sau
- Hiệu năng của câu lệnh phụ thuộc vào số lượng bản ghi của bảng – đúng hay sai
- Cùng số lượng bản ghi thì thiết kế kiểu dữ liệu ảnh hưởng thế nào đến hiệu năng
- Thay đổi thứ tự các bảng trong câu lệnh JOIN khi chúng ta viết lệnh SQL thì có ảnh hưởng đến hiệu năng hay không
Các case study chia sẻ đều xuất phát từ những dự án thực tế mà Wecommit đã thực hiện. Đây là danh sách những dự án, kinh nghiệm mà chúng tôi đúc kết để chia sẻ: https://wecommit.com.vn/du-an/
1. Cùng 1.000.000 bản ghi thì VARCHAR2(400) và VARCHAR(2) có hiệu năng khác nhau thế nào?
1.1. So sánh hiệu năng của 3 bảng cùng số lượng bản ghi nhưng thiết kế kiểu dữ liệu khác nhau – VARCHAR2(400) và VARCHAR2(400)
Bước 1: Tạo bảng
Tạo 2 bảng có 20 cột varchar2(400) và 1 bảng có 20 cột varchar2(2)
- Bảng TEST_BIG_VARCHAR_2 sử dụng để chứa toàn những dữ liệu thật sự có độ dài 400 bytes.
- Bảng TEST_BIG_VARCHAR chỉ sử dụng để chứa những dữ liệu có độ dài tối đa là 2
- Tạo một bảng tên là test_small_varchar có cùng số lượng cột, nhưng kiểu giá trị là VARCHAR2(2). Bảng này sẽ có dữ liệu giống hệt với bảng test_big_varchar
CREATE TABLE TEST_BIG_VARCHAR_2 ( col1 VARCHAR2 (400), col2 VARCHAR2 (400), col3 VARCHAR2 (400), col4 VARCHAR2 (400), col5 VARCHAR2 (400), col6 VARCHAR2 (400), col7 VARCHAR2 (400), col8 VARCHAR2 (400), col9 VARCHAR2 (400), col10 VARCHAR2 (400), col11 VARCHAR2 (400), col12 VARCHAR2 (400), col13 VARCHAR2 (400), col14 VARCHAR2 (400), col15 VARCHAR2 (400), col16 VARCHAR2 (400), col17 VARCHAR2 (400), col18 VARCHAR2 (400), col19 VARCHAR2 (400), col20 VARCHAR2 (400) ); CREATE TABLE test_big_varchar ( col1 VARCHAR2 (400), col2 VARCHAR2 (400), col3 VARCHAR2 (400), col4 VARCHAR2 (400), col5 VARCHAR2 (400), col6 VARCHAR2 (400), col7 VARCHAR2 (400), col8 VARCHAR2 (400), col9 VARCHAR2 (400), col10 VARCHAR2 (400), col11 VARCHAR2 (400), col12 VARCHAR2 (400), col13 VARCHAR2 (400), col14 VARCHAR2 (400), col15 VARCHAR2 (400), col16 VARCHAR2 (400), col17 VARCHAR2 (400), col18 VARCHAR2 (400), col19 VARCHAR2 (400), col20 VARCHAR2 (400) ); CREATE TABLE TEST_SMALL_VARCHAR ( col1 VARCHAR2 (2), col2 VARCHAR2 (2), col3 VARCHAR2 (2), col4 VARCHAR2 (2), col5 VARCHAR2 (2), col6 VARCHAR2 (2), col7 VARCHAR2 (2), col8 VARCHAR2 (2), col9 VARCHAR2 (2), col10 VARCHAR2 (2), col11 VARCHAR2 (2), col12 VARCHAR2 (2), col13 VARCHAR2 (2), col14 VARCHAR2 (2), col15 VARCHAR2 (2), col16 VARCHAR2 (2), col17 VARCHAR2 (2), col18 VARCHAR2 (2), col19 VARCHAR2 (2), col20 VARCHAR2 (2) )
Bước 2: Thực hiện Insert 1.000.000 bản ghi vào cả 3 bảng trên
- Thực hiện đối với bảng: TEST_BIG_VARCHAR_2
SQL>
BEGIN
FOR i IN 1 .. 1000000
LOOP
INSERT INTO TEST_BIG_VARCHAR_2
VALUES (LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400),
LPAD ('A', 400));
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
- Thực hiện với bảng TEST_BIG_VARCHAR
SQL> BEGIN
FOR i IN 1 .. 1000000
LOOP
INSERT INTO TEST_BIG_VARCHAR
VALUES ('1',
'2',
'3',
'4',
'5',
'6',
'7',
'8',
'9',
'10',
'11',
'12',
'13',
'14',
'15',
'16',
'17',
'18',
'19',
'20');
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
- Thực hiện với bảng TEST_SMALL_VARCHAR
SQL> BEGIN FOR i IN 1 .. 1000000 LOOP INSERT INTO TEST_SMALL_VARCHAR VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20'); COMMIT; END LOOP; END; / PL/SQL procedure successfully completed.
Bước 3: Đánh giá hiệu năng với các câu lệnh TABLE ACCESS FULL
Như vậy lúc này 3 bảng đều có số lượng bản ghi giống nhau.
Để đảm bảo tính chính xác của tất cả việc demo bên dưới, tôi sẽ tiến hành gather statistics cho toàn bộ 3 bảng trên.
SQL> EXEC dbms_stats.gather_table_stats('HUYTQ','TEST_BIG_VARCHAR_2',cascade=>TRUE); PL/SQL procedure successfully completed. Elapsed: 00:02:34.90 SQL> EXEC dbms_stats.gather_table_stats('HUYTQ','TEST_BIG_VARCHAR',cascade=>TRUE); PL/SQL procedure successfully completed. Elapsed: 00:00:01.86 SQL> EXEC dbms_stats.gather_table_stats('HUYTQ','TEST_SMALL_VARCHAR',cascade=>TRUE); PL/SQL procedure successfully completed. Elapsed: 00:00:00.78Chúng ta kiểm tra dung lượng của 3 bảng này xem có khác gì nhau không nhé.
select owner, segment_name, bytes/1024/1024 "SIZE_MB" from dba_segments where segment_name in ('TEST_BIG_VARCHAR','TEST_SMALL_VARCHAR','TEST_BIG_VARCHAR_2'); OWNER SEGMENT_NAME SIZE_MB ------ -------------------- ---------- HUYTQ TEST_BIG_VARCHAR_2 17270 HUYTQ TEST_SMALL_VARCHAR 62 HUYTQ TEST_BIG_VARCHAR 62
Như vậy với cùng số lượng bản ghi:
- Hai bảng TEST_SMALL_VARCHAR và TEST_BIG_VARCHAR có cùng dung lượng vì bản chất dữ liệu đưa vào là giống nhau (mặc dù định nghĩa chiều dài kiểu ký tự một bảng là VARCHAR2, một bảng là VARCHAR2(400)).
- Bảng TEST_BIG_VARCHAR_2 có dung lượng lớn hơn nhiều lần: 17270 MB (so với 62MB của 2 bảng bên trên).
Do dung lượng bảng khác nhau, nên sẽ ảnh hưởng rất nhiều đến hiệu năng của các câu lệnh cần phải thực hiện TABLE ACCESS FULL.
Ví dụ như sau: Chúng ta cùng đánh giá chiến lược và thông số khi thực thi của 3 câu lệnh SELECT * FROM <TABLE_NAME>
select * from TEST_BIG_VARCHAR_2 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1101K| 8420M| 598K (1)| 01:59:42 | | 1 | TABLE ACCESS FULL| TEST_BIG_VARCHAR_2 | 1101K| 8420M| 598K (1)| 01:59:42 | ----------------------------------------------------------------------------------------
select * from TEST_BIG_VARCHAR -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000K| 48M| 2131 (1)| 00:00:26 | | 1 | TABLE ACCESS FULL| TEST_BIG_VARCHAR | 1000K| 48M| 2131 (1)| 00:00:26 | --------------------------------------------------------------------------------------
select * from TEST_SMALL_VARCHAR ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000K| 48M| 2131 (1)| 00:00:26 | | 1 | TABLE ACCESS FULL| TEST_SMALL_VARCHAR | 1000K| 48M| 2131 (1)| 00:00:26 | ----------------------------------------------------------------------------------------
Cả 3 câu lệnh trên đều có mục đích: lấy ra toàn bộ 1.000.000 bản ghi.
Tuy nhiên thời gian và chi phí thực hiện các câu lệnh cho chúng ta thấy sự chênh lệch rất lớn:
-
Thời gian của câu lệnh làm việc trên bảng TEST_BIG_VARCHAR_2 ước tính là 1 giờ, 59 phút và 42 giây). Chi phí để thực hiện câu lệnh là 598K
-
Thời gian của 2 câu lệnh còn lại chỉ mất ước tính 26s, chi phí thực hiện là 2131 (nhỏ hơn 280 lần!!!)
Bước 4: Đánh giá hiệu năng khi làm việc với Index
Bây giờ chúng ta sẽ xem nếu tạo Index trên 3 bảng này thì có sự khác biệt nào không nhé.
Tôi sẽ tạo Index trên cả 3 cột Col1 của 3 bảng
SQL> create index idx_small_col1 on test_small_varchar(col1); Index created. Elapsed: 00:00:01.16 SQL> create index idx_big_col1 on test_big_varchar(col1); Index created. Elapsed: 00:00:01.57 SQL> create index idx_bigdata_col1 on test_big_varchar_2(col1); Index created. Elapsed: 00:01:05.48
Thời gian tạo Index cũng có sự chênh lệch lớn:
- Tạo index trên 2 bảng đầu tiên chỉ mất thời gian gần như nhau (1.57s)
- Thời gian tạo index trên bảng TEST_BIG_VARCHAR_2 là hơn 1 phút.
Dung lượng các Index tạo ra cũng có sự chênh lệch lớn
SQL> select owner, segment_name, bytes/1024/1024 from dba_segments where segment_name in ('IDX_BIGDATA_COL1','IDX_SMALL_COL1','IDX_BIG_COL1') order by 3 ; OWNER SEGMENT_NAME BYTES/1024/1024 ------ -------------------- --------------- HUYTQ IDX_BIG_COL1 15 HUYTQ IDX_SMALL_COL1 15 HUYTQ IDX_BIGDATA_COL1 536 SQL> select * from test_big_varchar_2 where col1='0'; Execution Plan ---------------------------------------------------------- Plan hash value: 3693100239 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8020 | 6 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIG_VARCHAR_2 | 1 | 8020 | 6 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_BIGDATA_COL1 | 1 | | 5 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 1597 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL> select * from test_big_varchar where col1='0'; ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 51 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIG_VARCHAR | 1 | 51 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_BIG_COL1 | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1597 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL> select * from test_small_varchar where col1='0' -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 51 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_SMALL_VARCHAR | 1 | 51 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_SMALL_COL1 | 1 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1597 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
Khi tìm kiếm trên Index, số lượng block cần phải đọc của câu lệnh làm việc trên bảng TEST_SMALL_VARCHAR và bảng TEST_BIG_VARCHAR chỉ là 3 block, trong khi làm việc với bảng TEST_BIG_VARCHAR_2 là 5 block.
2. Thay đổi thứ tự viết câu lệnh có ảnh hưởng đến hiệu năng không?
2.1. Thay đổi thứ tự trong mệnh đề WHERE khi làm việc với 1 bảng
Trường hợp 1: Nếu bảng không có Index
Chúng ta sẽ đánh giá hiệu năng của 2 câu lệnh sau
- Câu lệnh thứ nhất:
select * from emp where first_name='TRAN' and last_name='HUY'
- Câu lệnh thứ hai thực hiện đổi chỗ các cột tìm kiếm trong mệnh đề WHERE
select * from emp where last_name='HUY' and first_name='TRAN'
Chiến lược thực thi của 2 câu lệnh như sau
select * from emp where first_name='TRAN' and last_name='HUY'-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 723 | 39828 (1) | 00:07:58 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 723 | 39828 (1) | 00:07:58 | -------------------------------------------------------------------------- select * from emp where last_name='HUY' and first_name='TRAN' -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 723 | 39828 (1) | 00:07:58 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 723 | 39828 (1) | 00:07:58 | --------------------------------------------------------------------------
Hai câu lệnh này cùng có 1 chiến lược thực thi: quét toàn bộ các block dữ liệu trong bảng (TABLE ACCESS FULL).
Do cùng chiến lược thực thi nên thời gian và hiệu năng của hai cách viết này là như nhau.
Bây giờ ta sẽ xem xét 2 trường hợp khi bảng có Index.
Mật khẩu để đọc nội dung 2 trường hợp này tôi gửi trong nhóm Zalo Tư Duy – Tối Ưu – Khác Biệt. Bạn có thể tham gia nhóm (miễn phí). Link tham giá nhóm: https://wecommit.com.vn/zalo
Nội dung khóa
Bạn cần nhập mật khẩu để mở khóa nội dung này
2.2. Thay đổi thứ tự trong câu lệnh JOIN nhiều bảng
Chúng ta sẽ xem xét các câu lệnh có cùng ý nghĩa nghiệp vụ sau
Câu lệnh thứ nhất:
select * from emp e, dept d where e.deptno=d.deptno and e.salary=1000 and d.DNAME like '%K%'
Câu lệnh thứ hai: thực hiện đổi chỗ hai bảng DEPT và EMP trong thứ tự JOIN
select * from dept d, emp e where e.deptno=d.deptno and e.salary=1000 and d.DNAME like '%K%'
Câu lệnh thứ ba: thực hiện đổi chỗ hai bảng DEPT và EMP trong thứ tự JOIN, đồng thời đổi chỗ cả vị trí trong mệnh đề WHERE
select * from dept d, emp e where e.salary=1000 and d.deptno=e.deptno and d.DNAME like '%K%'
Chiến lược thực thi của các câu lệnh như sau
Câu lệnh thứ nhất:
select * from emp e, dept d where e.deptno=d.deptno and e.salary=1000 and d.DNAME like '%K%' ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 740 | 39880 (1) | 00:07:59 | | 1 | NESTED LOOPS | | 1 | 740 | 39880 (1) | 00:07:59 | |* 2 | TABLE ACCESS FULL | EMP | 1 | 723 | 39879 (1) | 00:07:59 | |* 3 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 17 | 1 (0) | 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 | ----------------------------------------------------------------------------------------
Câu lệnh thứ hai:
select * from dept d, emp e where e.deptno=d.deptno and e.salary=1000 and d.DNAME like '%K%' ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 740 | 39880 (1) | 00:07:59 | | 1 | NESTED LOOPS | | 1 | 740 | 39880 (1 ) | 00:07:59 | |* 2 | TABLE ACCESS FULL | EMP | 1 | 723 | 39879 (1) | 00:07:59 | |* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 17 | 1 (0) | 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 | ----------------------------------------------------------------------------------------
Câu lệnh thứ ba:
select * from dept d, emp e where e.salary=1000 and d.deptno=e.deptno and d.DNAME like '%K%' ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 740 | 39880 (1) | 00:07:59 | | 1 | NESTED LOOPS | | 1 | 740 | 39880 (1) | 00:07:59 | |* 2 | TABLE ACCESS FULL | EMP | 1 | 723 | 39879 (1) | 00:07:59 | |* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 17 | 1 (0) | 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 | ---------------------------------------------------------------------------------------
3. Bảng có 0 bản ghi thì có thể bị chậm hay không?
Câu trả lời là CÓ.
Tôi đã chia sẻ cụ thể ví dụ này tại đây
4. Nếu bạn muốn được tôi trực tiếp huấn luyện toàn bộ về Tư duy tối ưu, Kiến thức tối ưu và Kinh nghiệm tối ưu.
Chương trình huấn luyện tối ưu cơ sở dữ liệu thực chiến: https://wecommit.com.vn/key-person-system-coaching-huan-luyen-toi-uu-csdl-thuc-chien/
Tham gia nhóm Zalo (miễn phí) để cập nhật những kiến thức tối ưu về tư duy cũng như tối ưu trong công việc: Zalo Tư Duy – Tối Ưu – Đặc Biệt.
5. Thông tin tác giả
Tác giả: Trần Quốc Huy – Founder & CEO Wecommit
Facebook: https://www.facebook.com/tran.q.huy.71
Email: huy.tranquoc@wecommit.com.vn
Youtube: Trần Quốc Huy
Số điện thoại: 0888549190
Views: 1955