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

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

B-tree Index và Bitmap Index: Tối ưu câu lệnh SQL khác biệt thế nào – Wecommit

Trần Quốc Huy

Bài viết này sẽ thực hiện so sánh hiệu năng câu lệnh SQL khi sử dụng B-tree Index và Bitmap Index.

Đây là 2 loại Index kinh điển khi chúng ta thực hiện thiết kế và tối ưu câu lệnh SQL.

Tài bài viết này tôi sẽ thực hiện các Demo sau để các anh em thấy được sự khác biệt và những điều cần chú ý khi làm việc với B-tree Index và Bitmap Index

  • Tôi sẽ tạo 2 bảng có cùng 1 triệu bản ghi
    • Một bảng có dữ liệu được Insert theo đúng tuần tự của cột ID
    • Một bảng dữ liệu được insert vào một cách Random
  • Tiến hành đánh giá hiệu năng của B-tree Index và Bitmap Index trên 2 bảng này, để xem khi làm việc với các bảng có dữ liệu “lộn xộn” thì hiệu năng có khác gì không? – Phần này tôi thường thấy hơn 80% anh em DEV không hiểu bản chất, cứ đinh ninh là việc sắp xếp dữ liệu chẳng ảnh hưởng gì cả – NHẦM TO !!!!
  • Thực hiện đánh giá hiệu năng giữa B-tree Index và Bitmap Index khi chúng được tạo trên các cột có đặc đính khác nhau:
    • TH1: Thực hiện tạo trên cột ID (cột key)
    • TH2: Thực hiện tạo trên 1 cột có số lượng giá trị khác nhau trung bình (normal cardinality)
    • TH3: Thực hiện tạo trên 1 cố chỉ có 2 giá trị khác nhau (low cardinality)
  • Thực hiện phân tích hiệu năng khi thực hiện câu lệnh SELECT với các điều kiện khác nhau
    • Điều kiện so sánh bằng
    • Điều kiện so sánh không bằng
    • Điền kiện tìm kiếm giá trị NULL

1. Thực hiện tạo bảng phục vụ cho việc kiểm thử so sánh B-tree Index và Bitmap Index

Chiến lược tạo bảng của tôi như sau

  • Tôi sẽ tạo 2 bảng có cùng 1 triệu bản ghi.
  • Bảng WECOMMIT_EMP_NORMAL được sắp xếp dữ liệu có thứ tự
  • Bảng WECOMMIT_EMP_RANDOM được sắp xếp dữ liệu “hỗn độn”, không có bất kỳ quy tắc nào
  • Cột ID trong cả 2 bảng trên đều chứa những giá trị duy nhất.

1.1. Tạo bảng wecommit_emp_normal có 1 triệu bản ghi, dữ liệu được sắp xếp có thứ tự.

CREATE TABLE wecommit_emp_normal
(
id NUMBER (10),
name VARCHAR2 (50),
salary NUMBER
);

BEGIN
    FOR i IN 1 .. 1000000
    LOOP
        INSERT INTO wecommit_emp_normal
                 VALUES (i,
                         DBMS_RANDOM.string ('H', 50),
                         trunc(DBMS_RANDOM.VALUE (1000, 7000)));
        IF MOD (i, 1000) = 0
        THEN
            COMMIT;
        END IF;
    END LOOP;
END;
/

PL/SQL procedure successfully completed.

1.2. Tạo bảng wecommit_emp_random 

CREATE TABLE wecommit_emp_random
AS
SELECT
*
FROM wecommit_emp_normal
ORDER BY DBMS_RANDOM.random;

Table created.

1.3. Kiểm tra thông tin của 2 bảng mới tạo

SQL> select count(*) as "Total rows", count(distinct id) as "Total Distinct Values" from wecommit_emp_normal;
Total rows Total Distinct Values
---------- ---------------------
1000000 1000000

Elapsed: 00:00:00.31

SQL> select count(*) as "Total rows", count(distinct id) as "Total Distinct Values" from wecommit_emp_random;
Total rows Total Distinct Values
---------- ---------------------
1000000 1000000
Elapsed: 00:00:00.39

Để thực hiện kiểm thử hiệu năng của B-tree Index và Bitmap Index, tôi sẽ thực hiện tạo 2 kiểu Index này trên cùng cột của 2 bảng trên, và thực hiện các bài toán phân tích khác nhau

2. Case 1: So sánh hiệu năng B-tree Index và Bitmap Index khi tạo trên cột ID (coi như là cột Key) và điều kiện WHERE là so sánh bằng (“=”)

2.1. Thực hiện so sánh hiệu năng giữa 2 loại Index trên bảng WECOMMIT_EMP_NORMAL

2.1.1. Tạo B-tree Index và kiểm thử hiệu năng

SQL> create index idx_btree_id_normal on wecommit_emp_normal(id);
Index created.
Elapsed: 00:00:00.81

SQL> exec dbms_stats.gather_table_stats (ownname => 'HUYTQ' , tabname => 'WECOMMIT_EMP_NORMAL',cascade => true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.80

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
from dba_segments
where segment_name in ('WECOMMIT_EMP_NORMAL', 'IDX_BTREE_ID_NORMAL');

SEGMENT_NAME           Size in MB
------------------- ----------
WECOMMIT_EMP_NORMAL      96
IDX_BTREE_ID_NORMAL      18
Bây giờ tôi sẽ thực hiện một số câu lệnh tìm kiếm với điều kiện so sánh bằng, chúng ta sẽ cùng đánh giá hiệu năng của câu lệnh khi có B-tree Index.
SQL> set autotrace traceonly
SQL> select * from wecommit_emp_normal where id=500;
Elapsed: 00:00:00.02


Execution Plan
----------------------------------------------------------
Plan hash value: 1845664746

 

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=500)

Statistics
----------------------------------------------------------
  1  recursive calls
  0  db block gets
  5  consistent gets
  0  physical reads
  0  redo size
771  bytes sent via SQL*Net to client
408  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed
Với câu lệnh trên, để thực hiện câu lệnh, hệ thống cần phải tiêu tốn tài nguyên như sau:
  •  Số block phải đọc từ bộ nhớ: 5 block (consistent gets)
  •  Số block phải xử lý từ ổ cứng: 0 (0 physical reads)
Để đảm bảo tính khách quan, tôi sẽ thực hiện tìm kiếm trên một số giá trị nữa
SQL> select * from wecommit_emp_normal where id=1228;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
772 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> select * from wecommit_emp_normal where id=8050
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
772 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> select * from wecommit_emp_normal where id=99984;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
988 bytes sent via SQL*Net to client
410 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Tổng hợp kết quả như sau:
B-TREE INDEX
ID  consistent gets physical reads
500 5 0
1228 5 0
8050 5 0
99984 5 0

2.1.2. Tạo Bitmap Index và kiểm thử hiệu năng

Tôi sẽ thử xem nếu tạo Bitmap Index trên cột ID của bảng WECOMMIT_EMP_NORMAL thì mọi thứ có tốt hơn so với sử dụng B-tree Index hay không

Do tôi muốn cùng thử nghiệm trên 1 cột ID, tôi cần phải drop index B-tree đã tạo trên cột này

SQL> drop index IDX_BTREE_ID_NORMAL;
Index dropped.

Elapsed: 00:00:00.17

SQL> create bitmap index IDX_BITMAP_ID_NORMAL on WECOMMIT_EMP_NORMAL(ID);
Index created.
Elapsed: 00:00:03.67

SQL> exec dbms_stats.gather_table_stats (ownname => 'HUYTQ' , tabname => 'WECOMMIT_EMP_NORMAL',cascade => true);
PL/SQL procedure successfully completed.

SQL>  select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
from dba_segments
where segment_name in ('WECOMMIT_EMP_NORMAL', 'IDX_BITMAP_ID_NORMAL');

SEGMENT_NAME                  Size in MB
-----------------------------   ----------
WECOMMIT_EMP_NORMAL            96
IDX_BITMAP_ID_NORMAL           28
Tiếp theoi, tôi sẽ thực hiện các câu lệnh SELECT với điều kiện so sánh bằng, chúng ta cùng xem hiệu năng khi sử dụng BITMAP Index của các câu lệnh này như thế nào nhé.
SQL> set autotrace traceonly
SQL> select * from wecommit_emp_normal where id=500;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
771 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Thực hiện với các giá trị khác, tương tự lúc kiểm thử với B-tree Index ta được kết quả như sau
BITMAP INDEX
ID  consistent gets physical reads
500 5 0
1228 5 0
8050 5 0
99984 5 0

2.1.3. Đánh giá B-tree Index và Bitmap Index khi sử dụng trên 1 cột có 1.000.000 giá trị khác nhau của bảng WECOMMIT_EMP_NORMAL

  • Về hiệu năng chúng ta thấy hai loại B-tree Index và Bitmap Index có kết quả tương tự nhau
B-TREE INDEX BITMAP INDEX
ID  consistent gets physical reads  consistent gets physical reads
500 5 0 5 0
1228 5 0 5 0
8050 5 0 5 0
99984 5 0 5 0
  • Dung lượng của Bitmap Index lớn hơn so với B-tree Index: Dung lượng của B-tree Index là 18 MB, trong khi dung lượng của Bitmap Index là 28 MB.

2.2. Thực hiện so sánh hiệu năng giữa 2 loại Index trên bảng có dữ liệu “lộn xộn”  WECOMMIT_EMP_RANDOM

2.2.1. Tạo B-tree Index và kiểm thử hiệu năng

SQL> create index idx_btree_id_random on wecommit_emp_random(id);
Index created.

Elapsed: 00:00:01.22

SQL> exec dbms_stats.gather_table_stats (ownname => 'HUYTQ' , tabname => 'WECOMMIT_EMP_RANDOM',cascade => true);
PL/SQL procedure successfully completed.

Elapsed: 00:00:01.52

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
from dba_segments
where segment_name in ('WECOMMIT_EMP_RANDOM', 'IDX_BTREE_ID_RANDOM');

SEGMENT_NAME Size in MB
------------------------------ ----------
WECOMMIT_EMP_RANDOM 96
IDX_BTREE_ID_RANDOM 18

SQL> select index_name, clustering_factor from dba_indexes where index_name='IDX_BTREE_ID_RANDOM';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_BTREE_ID_RANDOM 999901

Bây giờ tôi sẽ thực hiện một số câu lệnh SQL để kiểm thửu hiệu năng của B-tree Index vừa tạo

SQL> select * from WECOMMIT_EMP_RANDOM where id=500;
Statistics
----------------------------------------------------------
80 recursive calls
12 db block gets
5 consistent gets
0 physical reads
2464 redo size
771 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
Với điều kiện tìm kiếm ID=50, câu lệnh trên đã thực hiện sử dụng Index IDX_BTREE_ID_RANDOM trong chiến lược thực thi, giải pháp thực hiện là INDEX RANGE SCAN.
Chi tiết về tài nguyên cần sử dụng của câu lệnh là:
  • Hệ thống cần đọc 73 block từ bộ nhớ (consitent gets)
  • Hệ thống cần đọc 0 block từ đĩa (physical gets)

Chúng ta thực hiện chạy lần lượt câu lệnh tìm kiếm trên với các giá trị ID trong danh sách sau: 50, 1228, 8050, 99984. Kết quả ghi nhận được như sau

B-TREE INDEX
ID  consistent gets physical reads
500 5 0
1228 5 0
8050 5 0
99984 5 0

2.2.2. Tạo Bitmap Index và kiểm thử hiệu năng

Thực hiện Drop index dạng B-tree vừa tạo và sử dụng bằng Bitmap Index, câu lệnh thực hiện như sau:

SQL> drop index IDX_BTREE_ID_RANDOM;
Index dropped.

SQL> create bitmap index IDX_BITMAP_ID_RANDOM on WECOMMIT_EMP_RANDOM(ID);
Index created.

Kiểm tra một số thông tin của Index trên hệ thống sau khi tạo thành công

SQL> exec dbms_stats.gather_table_stats (ownname => 'HUYTQ' , tabname => 'WECOMMIT_EMP_RANDOM',cascade => true);

PL/SQL procedure successfully completed.

SQL> select segment_name, bytes/1024/1024 "Size in MB"
from dba_segments
where segment_name in ('WECOMMIT_EMP_RANDOM','IDX_BITMAP_ID_RANDOM');

SEGMENT_NAME Size in MB
------------------------------ ----------
WECOMMIT_EMP_RANDOM 96
IDX_BITMAP_ID_RANDOM 28

SQL> select index_name, clustering_factor from dba_indexes where index_name='IDX_BITMAP_ID_RANDOM';

INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_BITMAP_ID_RANDOM 1000000

Thực hiện đánh giá hiệu năng với các câu lệnh SELECT, điều kiện so sánh bằng.

SQL> select * from WECOMMIT_EMP_RANDOM where id=500;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
771 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Câu lệnh trên khi thực hiện đã sử dụng Bitmap Index IDX_BITMAP_IDX_RANDOM, tài nguyên sử dụng như sau:
  • Hệ thống cần quét 5 block dữ liệu tại bộ nhớ (consistent gets)
  • Hệ thống cần quét 0 block dữ liệu từ ổ cứng (physical reads)

Thực hiện tiếp tục câu lệnh này  với giá trị ID là 1228, 8050, 99984, ta được các kết quả như sau

BITMAP INDEX
ID  consistent gets physical reads
500 5 0
1228 5 0
8050 5 0
99984 5 0

2.2.3. Đánh giá hiệu năng giữa B-tree Index và Bitmap index

Ta có thể thấy rằng, hiệu năng khi sử dụng B-tree Index và Bitmap Index với câu lệnh so sánh bằng trên 1 cột có 1 triệu giá trị khác nhau (có thể nói là trên cột KEY), hiệu năng của 2 loại Index này không có sự khác biệt.
Hiệu năng này cũng không bị ảnh hưởng bởi mức động “lộn xộn” của dữ liệu. Cụ thể là hiệu năng khi làm việc với bảng WECOMMIT_EMP_NORMAL và bảng WECOMMIT_EMP_RANDOM là giống nhau.
B-TREE INDEX BITMAP INDEX
ID  consistent gets physical reads  consistent gets physical reads
500 5 0 5 0
1228 5 0 5 0
8050 5 0 5 0
99984 5 0 5 0

 

3. Case 2: So sánh hiệu năng B-tree Index và Bitmap Index khi tạo trên cột ID với câu lệnh so sánh khoảng giá trị

Chiến lược kiểm thử case study này như sau so sánh lần lượt hiệu năng của B-tree Index và Bitmap Index đối với các câu lệnh sau

Đối với bảng WECOMMIT_EMP_NORMAL

SELECT * FROM WECOMMIT_EMP_NORMAL  WHERE id BETWEEN 20 AND 1500;
SELECT * FROM WECOMMIT_EMP_NORMAL  WHERE id BETWEEN 10 AND 2300;
SELECT * FROM WECOMMIT_EMP_NORMAL  WHERE id BETWEEN 1035 AND 5320;
SELECT * FROM WECOMMIT_EMP_NORMAL  WHERE id BETWEEN 30135 AND 35230;
SELECT * FROM WECOMMIT_EMP_NORMAL  WHERE id BETWEEN 982135 AND 993115;

Đối với bảng WECOMMIT_EMP_RANDOM

SELECT * FROM WECOMMIT_EMP_RANDOM  WHERE id BETWEEN 20 AND 1500;
SELECT * FROM WECOMMIT_EMP_RANDOM  WHERE id BETWEEN 10 AND 2300;
SELECT * FROM WECOMMIT_EMP_RANDOM  WHERE id BETWEEN 1035 AND 5320;
SELECT * FROM WECOMMIT_EMP_RANDOM  WHERE id BETWEEN 30135 AND 35230;
SELECT * FROM WECOMMIT_EMP_RANDOM  WHERE id BETWEEN 982135 AND 993115;

3.1. Thực hiện đánh giá hiệu năng B-tree Index và Bitmap Index đối với bảng WECOMMIT_EMP_NORMAL

3.1.1. Hiệu năng của Bitmap Index

SQL> SELECT * FROM WECOMMIT_EMP_NORMAL WHERE ID BETWEEN 20 AND 1500;

------------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    |  1482 |   112K|   312   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| WECOMMIT_EMP_NORMAL  |  1482 |   112K|   312   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS     |    |    |    | |    |
|*  3 |    BITMAP INDEX RANGE SCAN     | IDX_BITMAP_ID_NORMAL |    |    | |    |
------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
223 consistent gets
0 physical reads
0 redo size
140789 bytes sent via SQL*Net to client
1502 bytes received via SQL*Net from client
100 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1481 rows processed
Thực hiện lần lượt với các trường hợp còn lại, ta được kết quả tổng hợp như sau:
– Tất cả các trường hợp hệ thống đề sử dụng chiến lược thực thi dùng Bitmap Index
– Tổng hợp số block cần thực hiện của các câu lệnh như sau
WECOMMIT_EMP_NORMAL
BITMAP INDEX
Range ID consistent gets physical reads
20 AND 1500 223 0
10 AND 2300 341 0
1035 AND 5320 635 0
30135 AND 35230 758 0
982135 AND 993115 1605 0

3.1.1. Hiệu năng của B-tree Index

SQL> drop index IDX_BITMAP_ID_NORMAL;
Index dropped.
SQL> create index IDX_BTREE_ID_NORMAL on WECOMMIT_EMP_NORMAL(ID);
Index created.
SQL> SELECT * FROM WECOMMIT_EMP_NORMAL WHERE ID BETWEEN 20 AND 1500;
1481 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1845664746
-----------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |   |  1482 |   112K|    24   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| WECOMMIT_EMP_NORMAL |  1482 |   112K|    24   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | IDX_BTREE_ID_NORMAL |  1482 |   | 6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
    220  consistent gets
      5  physical reads
      0  redo size
 140789  bytes sent via SQL*Net to client
   1502  bytes received via SQL*Net from client
    100  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1481  rows processed
Thực hiện lần lượt với các trường hợp còn lại, ta được kết quả tổng hợp như sau
WECOMMIT_EMP_NORMAL
B-TREE INDEX
Range ID consistent gets physical reads
20 AND 1500 220 5
10 AND 2300 338 1
1035 AND 5320 631 7
30135 AND 35230 753 13
982135 AND 993115 1593 27

Tại đây, ta có bức tranh tổng quát hiệu năng trong trường hợp sử dụng B-tree Index và Bitmap Index như sau:

WECOMMIT_EMP_NORMAL
BITMAP INDEX B-TREE INDEX
Range ID consistent gets physical reads consistent gets physical reads
20 AND 1500 223 0 220 5
10 AND 2300 341 0 338 1
1035 AND 5320 635 0 631 7
30135 AND 35230 758 0 753 13
982135 AND 993115 1605 0 1593 27
  • Trong tất cả các trường hợp trên, hệ thống đề sử dụng Index để thực hiện câu lệnh, không có bất kỳ trường hợp nào Index không hiệu quả mà phải chuyển qua FULL TABLE SCAN.
  • Về lượng block cần thực hiện, không có sự chênh lệch quá nhiều giữa B-tree Index và Bitmap Index

3.2  Thực hiện đánh giá hiệu năng B-tree Index và Bitmap Index đối với bảng WECOMMIT_EMP_RANDOM

3.2.1. Hiệu năng của Bitmap Index

Hiện tại bảng đang được tạo sẵn Bitmap Index

Kiểm thử hiệu năng như sau

SQL> set autotrace traceonly

SQL> select * from WECOMMIT_EMP_RANDOM where id between 10 and 2300;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2454 consistent gets
0 physical reads
0 redo size
217508 bytes sent via SQL*Net to client
2096 bytes received via SQL*Net from client
154 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2291 rows processed

Làm tương tự cho các câu lệnh còn lại, kết quả như sau:

WECOMMIT_EMP_RANDOM
BITMAP INDEX
Range ID consistent gets physical reads
20 AND 1500 1588 0
10 AND 2300 2454 0
1035 AND 5320 4588 0
30135 AND 35230 5454 0
982135 AND 993115 11751 0

3.2.2. Hiệu năng của B-tree Index

Bây giờ chúng ta sẽ đánh giá hiệu năng của B-tree Index
Thực hiện Drop Bitmap Index và tạo lại B-tree Index như sau
SQL> create index idx_btree_id_random on wecommit_emp_random(id);
Index created

SQL> SELECT * FROM WECOMMIT_EMP_RANDOM WHERE ID BETWEEN 20 AND 1500;
Chiến lược thực thi đã sử dụng Index B-tree

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1585 consistent gets
5 physical reads
0 redo size
140789 bytes sent via SQL*Net to client
1502 bytes received via SQL*Net from client
100 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1481 rows processed
Thực hiện tương tự, ta có kết quả như sau
WECOMMIT_EMP_RANDOM
BITMAP INDEX B-TREE INDEX
Range ID consistent gets physical reads consistent gets physical reads
20 AND 1500 1588 0 1585 0
10 AND 2300 2454 0 2451 1
1035 AND 5320 4588 0 11787 0
30135 AND 35230 5454 0 11840 0
982135 AND 993115 11751 0 12227 0

Nhận xét:

  • Trong 2 trường hợp đầu tiên (điều kiện tìm kiếm ID trong khoảng 20 – 1500 và 10 – 2300) thông số hiệu năng của B-tree Index và Bitmap gần như nhau (chỉ khác biệt 2-3 block, không đáng kể).
  • Đối với B-tree Index, khi câu lệnh thực hiện tìm kiếm các khoảng giá trị RANGE ID 1035 AND 5320, 30135 AND 35230 và 982135 AND 993115, B-tree Index có chi phí thực hiện quá lớn, nên hệ thống buộc phải chuyển sang quét toàn bộ bảng (FULL TABLE SCAN). Chính vì thế tài nguyên block cần thực hiện của các câu lệnh này tăng lên rất nhiều so với Bitmap Index
  • Trong tất cả các trường hợp trên, Bitmap Index đều có thể sử dụng (mà không phải chuyển qua FULL TABLE SCAN)
SQL> SELECT * FROM WECOMMIT_EMP_RANDOM WHERE ID BETWEEN 982135 AND 993115;

Execution Plan
----------------------------------------------------------
Plan hash value: 4224781487


-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                     | 10982 |   836K|  3164   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| WECOMMIT_EMP_RANDOM | 10982 |   836K|  3164   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

3.3. Đánh giá kết quả

Tại đây chúng ta có thể thấy một điều:
– Việc dữ liệu được đưa vào có trật tự sẽ ảnh hưởng rất nhiều đến hiệu quả của việc sử dụng Index (đối với cả B-tree Index và Bitmap Index).
– Trong Demo trên chúng ta có thể thấy rằng, việc tìm kiếm trên bảng có dữ liệu “lộn xộn”, sẽ tốn tài nguyên hơn rất nhiều so với các dữ liệu đã được sắp xếp có thứ tự).
– Ví dụ: cùng là tìm kiếm B-tree Index, nhưng thực hiện trên bảng WECOMMIT_EMP_RANDOM tốn gấp hơn 7 lần so với tìm kiếm trên bảng WECOMMIT_EMP_NORMAL)
WECOMMIT_EMP_RANDOM WECOMMIT_EMP_NORMAL
BITMAP INDEX B-TREE INDEX BITMAP INDEX B-TREE INDEX
Range ID consistent gets physical reads consistent gets physical reads consistent gets physical reads consistent gets physical reads
20 AND 1500 1588 0 1585 0 223 0 220 5
10 AND 2300 2454 0 2451 1 341 0 338 1
1035 AND 5320 4588 0 11787 0 635 0 631 7
30135 AND 35230 5454 0 11840 0 758 0 753 13
982135 AND 993115 11751 0 12227 0 1605 0 1593 27

4. Case Study 3: So sánh hiệu năng nếu tạo B-tree Index và Bitmap Index trên các cột có số giá trị khác nhau không quá nhiều.

SQL> select count(*) as "Total rows", count(distinct salary) as "Total Distinct Salary" from wecommit_emp_normal;

Total rows Total Distinct Salary
---------- ---------------------
   1000000     6000

Cột Salary chỉ có 6000 giá trị khác nhau trong 1.000.000. Chúng ta cùng thực hiện phân tích hiệu năng nếu tạo Index trên cột này đối với 2 bảng dữ liệu ban đầu nhé.

03 Case Study bên dưới tôi sẽ trao đổi trên nhóm các học viên đặc quyền.

Nội dung khóa
Bạn cần nhập mật khẩu để mở khóa nội dung này

7. Một số bài viết liên quan đến hiệu năng của Index bạn có thể đọc

  • Sai lầm khi thiết kế FK và Index
  • Case Study tối ưu sử dụng Partition kết hợp với Index:

8. Nếu bạn muốn liên hệ với tôi

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: 2885





    Câu hỏi bảo mật

    Trả lời

    Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

    Ask ChatGPT
    Set ChatGPT API key
    Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.