Trong những dự án tối ưu cơ sở dữ liệu mà Wecommit trực tiếp triển khai, chúng tôi nhận thấy nhiều anh em lập trình chưa có thói quen và chiến lược đánh giá mức độ phân mảnh dữ liệu của bảng. Những lợi ích mà bạn có được trong bài viết này:

Biết chính có phải tất cả các trường hợp phân mảnh đều ảnh hưởng nghiêm trọng đến hiệu năng hay không?
Các bước triển khai chi tiết, step by step
Phân tích về nguyên lý rất ĐƠN GIẢN, DỄ HIỂU, DỄ NHỚ
Nếu bạn cần giải pháp chi tiết có thể áp dụng vào hệ thống PRODUCTION trong thực tế (áp dụng được cả với những hệ thống có giao dịch 24×7), bạn cũng có thể tìm thấy giải pháp chi tiết, step by step ở cuối bài viết

1. Tạo bảng dữ liệu phục vụ demo

Thực hiện tạo bảng tên là WECOMMIT_FRAGMENT_TABLE có 7 cột và 2 triệu bản ghi thời điểm ban đầu.
Thực hiện xóa phần lớn dữ liệu trong bảng, chỉ để lại 1 bản ghi duy nhất

1.1. Scripts tạo bảng và khởi tạo dữ liệu

SQL> create sequence wecommit_seq_test start with 1 increment by 1;
Sequence created.

SQL> create table wecommit_fragment_table(id number, col1 varchar2(30), col2 varchar2(30), col3 varchar2(30),col4 varchar2(30),col5 varchar2(30),col6 varchar2(30));
Table created.

SQL>  BEGIN
FOR i IN 1 ..2000000 LOOP
insert into WECOMMIT_FRAGMENT_TABLE values(wecommit_seq_test.nextval, 'TRAN QUOC HUY','TRAN QUOC HUY','TRAN QUOC HUY','WECOMMIT','WECOMMIT','WECOMMIT');
commit;
END LOOP;
END;

PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

# Kiểm tra thông tin của bảng sau khi đã tạo

SQL> select count(*) from WECOMMIT_FRAGMENT_TABLE;
  COUNT(*)
----------
   2000000
SQL> select owner, segment_name, bytes/1024/1024 "MB" from dba_segments where segment_name='WECOMMIT_FRAGMENT_TABLE'

Lúc này table đang có 2,000,000 bản ghi và chiếm 176MB dung lượng trên ổ cứng.

1.2. Script xóa dữ liệu

SQL> delete from WECOMMIT_FRAGMENT_TABLE whereid between 1 and 1999999;
1999999 rows deleted.

SQL> commit;
Commit complete.

1.3. Kiểm tra trạng thái của bảng sau khi đã xóa dữ liệu

SQL>  select count(*) from WECOMMIT_FRAGMENT_TABLE;
  COUNT(*)
----------
 1

SQL> select owner, segment_name, bytes/1024/1024 "MB" from dba_segments where segment_name='WECOMMIT_FRAGMENT_TABLE'

Như vậy:

Mặc dù số lượng bản ghi của Table đã giảm từ 2.000.000 xuống còn 1 bản ghi (giảm 99.9999%), tuy nhiên số block dữ liệu của bảng vẫn chiếm 176MB (không thay đổi bất kỳ chút nào).
Trường hợp này bảng đã có hiện tượng phân mảnh cực kỳ nhiều

1.4. Script dưới đây cho ta biết chính xác mức độ phân mảnh của bảng

Bước 1: Thực hiện cập nhật lại thông tin thống kê của bảng

SQL> EXEC DBMS_STATS.gather_table_stats('HUYTQ', 'WECOMMIT_FRAGMENT_TABLE', estimate_percent => 100, cascade => TRUE);
PL/SQL procedure successfully completed.

Bước 2: Câu lệnh kiểm tra chi tiết tình trạng phân mảnh

  SELECT owner,
         table_name,
         ROUND ((blocks * 8) / 1024, 2)
             "Fragmented size (mb)",
         ROUND ((num_rows * avg_row_len / 1024 / 1024), 2)
             "Actual size (mb)",
           ROUND ((blocks * 8) / 1024, 2)
         - ROUND ((num_rows * avg_row_len / 1024 / 1024), 2)
             "Reclaim size (mb)",
             (  (  ROUND ((blocks * 8 / 1024), 2)
                 - ROUND ((num_rows * avg_row_len / 1024 / 1024), 2))
              / ROUND ((blocks * 8 / 1024), 2))
           * 100
         - 10
             "Reclaimable space % "
    FROM dba_tables
   WHERE     table_name = '&TABLE_NAME'
         AND ROUND ((blocks * 8 / 1024), 2) > 0
         AND     (  (  ROUND ((blocks * 8 / 1024), 2)
                     - ROUND ((num_rows * avg_row_len / 1024 / 1024), 2))
                  / ROUND ((blocks * 8 / 1024), 2))
               * 100
             - 10 >
             10
         AND   ROUND ((blocks * 8) / 1024, 2)
             - ROUND ((num_rows * avg_row_len / 1024 / 1024), 2) >
             100
         AND owner NOT IN ('SYS')
ORDER BY 1, 6 DESC;

Enter value for table_name: WECOMMIT_FRAGMENT_TABLE

**Kết quả cho thấy bảng đã bị phân mảnh hơn 90%**

2. Nếu bảng bị phân mảnh thì ảnh hưởng thế nào đến hiệu năng của các câu lệnh? Có phải cứ Table bị phân mảnh thì hệ thống sẽ cực kỳ chậm không?

Các bạn sẽ tìm được câu trả lời thông qua Demo và bài phân tích dưới đây

2.1. Chi tiết câu lệnh thực hiện đánh giá

Chúng ta hãy cùng xem câu lệnh tìm kiếm theo ID (hiện tại cột này không được đánh Index).

select * from WECOMMIT_FRAGMENT_TABLE where id=2000000;

2.2. Trường hợp không có Index trên cột ID thì chiến lược thực thi của câu lệnh thế nào?

Để thực hiện lấy ra 1 bản ghi và trả về cho người dùng, hệ thống cần thực hiện TABLE ACCESS FULL, bản chất của công việc này là QUÉT TOÀN BỘ CÁC BLOCK DỮ LIỆU của bảng.
Chúng ta có thể thấy rằng, công việc này đang tiêu tốn dự kiến 1 phút 13s để thực hiện, và chi phí của công việc này là 6055 (theo đơn vị tính chi phí tài nguyên thực hiện câu lệnh của cơ sở dữ liệu).

2.3. Nếu cột ID được đánh Index thì hiệu năng của câu lệnh sẽ thay đổi thế nào?

Tôi sẽ tạo index trên cột ID cho công việc Test này

create index IDX_ID on WECOMMIT_FRAGMENT_TABLE(ID);

Trong trường hợp này, chiến lược thực thi của câu lệnh như sau:

chiến lược thực thi khi bảng bị phân mảnh

Như vậy, nếu cột ID được đánh Index, hệ thống có thể biết chính xác bản ghi có giá trị ID=2000.000 đang ở vị trí nào (block nào). Và vì xác định được chính xác block cần vào để lấy thông tin, hệ thống chỉ cần ước tính chưa tới 1s để thực hiện công việc này.
Lúc này các bạn có thể thay việc hiệu năng được cải thiện TỤT HUYẾT ÁP:

Về thời gian: từ 1 phút 13s (73s) cho việc tìm kiếm tất cả các block dữ liệu, giảm xuống còn 1s cho việc tìm kiếm chính xác thông tin của block dữ liệu (thông tin nhận được từ Index – giá trị ROWID). Thời gian đã cải thiện hơn 98.64%
Về chi phí thực hiện của câu lệnh: giảm từ 6055 xuống còn 72 – tương đương với giảm hơn 98.8%.
Tổng số bản ghi trả ra của cả 2 trường hợp vẫn là: 01 bản ghi.

2.4 Như vậy việc một bảng bị phân mảnh không phải lúc nào cũng dẫn tới việc hệ thống bị CHẬM, TREO

Nếu hiểu rõ bản chất, bạn sẽ thấy rằng: việc tối ưu không tuân thủ theo một nguyên tắc cứng nhắc nào hết.

Nếu một bảng bị phân mảnh rất nhiều, một số câu lệnh trong hệ thống của bạn sẽ bị giảm hiệu năng thậm tệ, một số câu lệnh thì gần như không ảnh hưởng. Trong ví dụ trên của tôi đã chứng minh được điều này.

Tại phần này tôi sẽ lấy một ví dụ đời thường để bạn hiểu được “bản chất” của vấn đề bên trên.

Các bạn có thể hình dùng Table của bạn giống như một tòa chung cư. Các dữ liệu của bạn giống như những người dân sống trong chung cư đó.

Lúc đầu bạn đưa dữ liệu vào bảng, giống như việc những người dân ồ ạt kéo đến ở trong tòa chung cư.

Lúc ban đầu chung cư rất đông vui nhộn nhịp, rất cả các phòng đều có người ở (dữ liệu được đưa vào các block). Hình ảnh dưới đây chính là chung cư của các bạn: có 14 tầng, mỗi tầng có 18 căn hộ (mỗi căn hộ chính là 1 block chứa dữ liệu đó).

Tầng 14	P1401	P1402	P1403	P1404	P1405	P1406	P1407	P1408
Tầng 13	P1301	P1302	P1303	P1304	P1305	P1306	P1307	P1308
Tầng 12	P1201	P1202	P1203	P1204	P1205	P1206	P1207	P1208
Tầng 11	P1101	P1102	P1103	P1104	P1105	P1106	P1107	P1108
Tầng 10	P1001	P1002	P1003	P1004	P1005	P1006	P1007	P1008
Tầng 9	P901	P902	P903	P904	P905	P906	P907	P908
Tầng 8	P801	P802	P803	P804	P805	P806	P807	P808
Tầng 7	P701	P702	P703	P704	P705	P706	P707	P708
Tầng 6	P601	P602	P603	P604	P605	P606	P607	P608
Tầng 5	P501	P502	P503	P504	P505	P506	P507	P508
Tầng 4	P401	P402	P403	P404	P405	P406	P407	P408
Tầng 3	P301	P302	P303	P304	P305	P306	P307	P308
Tầng 2	P201	P202	P203	P204	P205	P206	P207	P208
Tầng 1	P101	P102	P103	P104	P105	P106	P107	P108

Một ngày nọ, chung cư buộc phải di dời người dân ra địa điểm khác (vì lý do quy hoạch), tất cả mọi người đều rời khỏi căn hộ của mình, trừ ông Trần Quốc Huy vẫn bám trụ ở lại.

Hình ảnh căn hộ của bạn lúc này như sau:

Tầng 14	P1401	P1402	P1403	P1404	P1405	P1406	P1407	P1408
Tầng 13	P1301	P1302	P1303	P1304	P1305	P1306	P1307	P1308
Tầng 12	P1201	P1202	P1203	P1204	P1205	P1206	P1207	P1208
Tầng 11	P1101	P1102	P1103	P1104	P1105	P1106	P1107	P1108
Tầng 10	P1001	P1002	P1003	P1004	P1005	P1006	P1007	P1008
Tầng 9	P901	P902	P903	P904	P905	P906	P907	P908
Tầng 8	P801	P802	P803	P804	P805	P806	P807	P808
Tầng 7	P701	P702	P703	P704	P705	P706	P707	P708
Tầng 6	P601	P602	P603	P604	P605	P606	P607	P608
Tầng 5	P501	P502	P503	P504	P505	P506	P507	P508
Tầng 4	P401	P402	P403	P404	P405	P406	P407	P408
Tầng 3	P301	P302	P303	P304	P305	P306	P307	P308
Tầng 2	P201	P202	P203	P204	P205	P206	P207	P208
Tầng 1	P101	P102	P103	P104	P105	P106	P107	P108

Các căn hộ đánh dấu màu xanh là những căn hộ trống.

Căn hộ được bôi màu đỏ (phòng 1106) là căn hộ có người ở (ông Trần Quốc Huy vẫn ở đó).

Hiện tượng này tương đương với việc rất nhiều block dữ liệu đang không được sử dung, nhưng không được thu hồi (hiện tượng phân mảnh của bảng).

Bây giờ, chúng ta xét 2 trường hợp

Trường hợp 1: Chung cư này không có danh sách thông tin từng người dân đang ở căn hộ số bao nhiêu. Nếu bạn cần đi tìm ông Trần Quốc Huy để yêu cầu ông ấy di dời khỏi tòa chung cư thì phải làm thế nào?

Do bạn không biết chính xác ông ấy đang ở phòng số bao nhiêu, do đó bạn sẽ buộc phải đi từng tầng, đối với mỗi tầng sẽ ghé qua từng phòng để tìm. Việc này chính là giải thuật FULL TABLE SCAN (quét toàn bộ block dữ liệu).

Trường hợp 2: Nếu ban quản lý của chung cư ấy có một danh sách cho biết thông tin của người dân và căn hộ tương ứng thì sao.

Để cần tìm ông Trần Quốc Huy, bạn sẽ phải mở danh sách đó ra và tra xem anh ta đang ở phòng số bao nhiêu. Tại đây bạn biết được anh ấy ở tầng 11, phòng 1106. Bước này tương ứng với giải thuật INDEX RANGE SCAN để quét thông tin ROWID- số phòng)
Bước số 2: Đi tới phòng 1106 để gặp ông Trần Quốc Huy. Bước này chính là bước TABLE ACCES BY INDEX ROWID

3. Nếu bạn muốn hiểu tường tận tất cả các kỹ năng tối ưu SQL tối ưu cơ sở dữ liệu

Khi bạn tham gia chương trình học “Từ điển tối ưu 100x hiệu năng” của Wecommit, bạn sẽ biết được toàn bộ những kỹ thuật tối ưu cơ sở dữ liệu mà chúng tôi đã và đang áp dụng cho rất nhiều dự án tại ngân hàng, chứng khoán, các công ty viễn thông, các hệ thống tại bệnh viện…

Không chỉ vậy, bạn còn được đồng hành và tư vấn HÀNG TUẦN, liên tục trong 1 năm, bạn sẽ cảm thấy vô cùng tự tin và khác biệt so với các đồng nghiệp của mình.

Hãy tìm hiểu chương trình tại đây:https://wecommit.com.vn/tu-dien-toi-uu-100x-hieu-nang/

Chi tiết giải pháp được tôi chia sẻ tại bài viết dành riêng cho nhóm học viên đặc quyền chương trình Từ điển tối ưu 100x hiệu năng.
Bạn có thể xem chi tiết chương trình tại đây.

4. Một số kiến thức khác về tối ưu SQL, tối ưu Database mà bạn cần biết

Đọc về High Water Mark để hiểu rõ hơn vấn đề của phân mảnh dữ liệu: Đọc bài viết tại đây
Tìm hiểu về tầm quan trọng của thứ tự các cột xuất hiện trong Index PostgreSQL: Đọc bài viết tại đây
Hiểu về 6 bước thực thi của câu lệnh SQL và áp dụng tối ưu cải thiện 97% hiệu năng: Đọc bài viết tại đây

5. Nếu ban muốn liên hệ với tôi

Tác giả của bài viết: Trần Quốc Huy – CEO & Founder Wecommit.

Follow tôi tại Facebook cá nhân: https://www.facebook.com/tran.q.huy.71/

Theo dõi các video về tối ưu SQL trên Youtube của tôi: https://www.youtube.com/@tranquochuywecommit

Số điện thoại: 0888549190

ĐĂNG KÝ ĐỂ NHẬN THÔNG TIN MỚI NHẤT

Tất cả mọi người

Làm thế nào tối ưu bảng bị phân mảnh trong các hệ thống giao dịch trực tuyến 24×7? | Defragment Table | Shrink Table | Production OLTP 24×7

1. Tạo bảng dữ liệu phục vụ demo

1.1. Scripts tạo bảng và khởi tạo dữ liệu

1.2. Script xóa dữ liệu

1.3. Kiểm tra trạng thái của bảng sau khi đã xóa dữ liệu

1.4. Script dưới đây cho ta biết chính xác mức độ phân mảnh của bảng

2. Nếu bảng bị phân mảnh thì ảnh hưởng thế nào đến hiệu năng của các câu lệnh? Có phải cứ Table bị phân mảnh thì hệ thống sẽ cực kỳ chậm không?

2.1. Chi tiết câu lệnh thực hiện đánh giá

2.2. Trường hợp không có Index trên cột ID thì chiến lược thực thi của câu lệnh thế nào?

2.3. Nếu cột ID được đánh Index thì hiệu năng của câu lệnh sẽ thay đổi thế nào?

2.4 Như vậy việc một bảng bị phân mảnh không phải lúc nào cũng dẫn tới việc hệ thống bị CHẬM, TREO

3. Nếu bạn muốn hiểu tường tận tất cả các kỹ năng tối ưu SQL tối ưu cơ sở dữ liệu

4. Một số kiến thức khác về tối ưu SQL, tối ưu Database mà bạn cần biết

5. Nếu ban muốn liên hệ với tôi

Trả lời Hủy

ĐĂNG KÝ ĐỂ NHẬN THÔNG TIN MỚI NHẤT

1. Tạo bảng dữ liệu phục vụ demo

1.1. Scripts tạo bảng và khởi tạo dữ liệu

1.2. Script xóa dữ liệu

1.3. Kiểm tra trạng thái của bảng sau khi đã xóa dữ liệu

1.4. Script dưới đây cho ta biết chính xác mức độ phân mảnh của bảng

2. Nếu bảng bị phân mảnh thì ảnh hưởng thế nào đến hiệu năng của các câu lệnh? Có phải cứ Table bị phân mảnh thì hệ thống sẽ cực kỳ chậm không?

2.1. Chi tiết câu lệnh thực hiện đánh giá

2.2. Trường hợp không có Index trên cột ID thì chiến lược thực thi của câu lệnh thế nào?

2.3. Nếu cột ID được đánh Index thì hiệu năng của câu lệnh sẽ thay đổi thế nào?

2.4 Như vậy việc một bảng bị phân mảnh không phải lúc nào cũng dẫn tới việc hệ thống bị CHẬM, TREO

3. Nếu bạn muốn hiểu tường tận tất cả các kỹ năng tối ưu SQL tối ưu cơ sở dữ liệu

4. Một số kiến thức khác về tối ưu SQL, tối ưu Database mà bạn cần biết

5. Nếu ban muốn liên hệ với tôi

Trả lời Hủy

Đăng nhập