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

Tối ưu cơ sở dữ liệu cải thiện 97% thời gian thực hiện chỉ bằng một “chấm nhẹ” thế nào?

Đây là những bài viết về các dự án & kinh nghiệm tối ưu cơ sở dữ liệu của Wecommit. Những giá trị mà bạn sẽ nhận được

  • Tư duy về tối ưu cơ sở dữ liệu
  • Kinh nghiệm thực tế
  • Ví dụ đời sống cực dễ hiểu
  • Phân tích chi tiết dưới góc độ của chuyên gia
  • Bài viết sẽ liên tục được cập nhật

Tác giả của nội dung này: Trần Quốc Huy – CEO Wecommit.

1. Thử thách mà rất nhiều lập trình viên gặp phải liên quan đến vấn đề tối ưu cơ sở dữ liệu hiện nay

Cơ sở dữ liệu gặp hiện tượng chậm, treo. Người dùng cuối và sếp liên tục than phiền. Các anh em kỹ thuật “hò” nhau lấy các câu lệnh ra để kiểm thử. Nhưng khi đem ra thử nghiệm thì kết quả nhận được rất oái oăm

  • Cứ chạy từng câu lệnh riêng lẻ thì cực kỳ nhanh (mấy vài mili second)
  • Tuy nhiên thực tế hệ thống PRODUCTION chạy nhiều câu lệnh đó cùng lúc thì lại cực kỳ chậm, thời gian chậm hơn vài chục lần đến hàng trăm lần.

Anh em lập trình lúc này về cơ bản sẽ rơi vào cảm giác “BẾ TẮC”.

Một loạt các phỏng đoán nhảy ra trong đầu:

  • Hạ tầng phần cứng chắc lởm quá, cần phải mở rộng phần cứng chăng?
  • Có nên đổ lỗi cho ông NETWORK không, vì case này bí quá rồi?
  • Cơ sở dữ liệu chắc gặp phải BUG.

Nhưng tất cả các câu hỏi đó đều không đưa đến phương án giải quyết gốc rễ của vấn đề.

2. Nếu bạn cũng đang gặp vấn đề này, tôi chúc mừng bạn vì bài viết này là dành cho bạn.

Bạn hãy đọc thật tập trung và đừng bỏ qua bất kỳ đoạn nào cả, bởi vì tất cả những gì tôi chia sẻ đều được chắt lọc qua kinh nghiệm dự án thực tế.

  • Tối ưu cơ sở dữ liệu Core chứng khoán: HNX, TCBS, VCBS, HDBS, BVSC…
  • Tối ưu cơ sở dữ liệu Core các ngân hàng: Ngân hàng VBSP, Ocean Bank, Ngân hàng Việt Nga, ngân hàng VDB,  ngân hàng CBBank (Myanmar), ngân hàng Lào Việt Bank…  
  • Tối ưu cơ sở dữ liệu các hệ thống quản lý bệnh viện: hàng chục hệ thống HIS do VNPT cung cấp
  • Tối ưu cơ sở dữ liệu các hệ thống viễn thông: Core billing của VNPT, Bitel (Peru)
  • Thông tin về các dự án của chúng tôi có thể xem tại: https://wecommit.com.vn/du-an/

Trong bài viết này các bạn sẽ thấy 1 SAI LẦM NGHIÊM TRỌNG mà các anh em lập trình mắc phải, kèm với đó là GIẢI PHÁP MANG LẠI HIỆU QUẢ NGAY LẬP TỨC. Bạn sẽ thấy cách thức mà tôi đã cải thiện hiệu năng hàng trăm lần, thậm chí nhiều hệ thống cải thiện hàng nghìn lần như thế nào. 

3. Giả lập bài toán cần thực hiện tối ưu cơ sở dữ liệu

3.1. Môi trường & bảng dữ liệu cần xử lý

Môi trường trong bài toán sử dụng Cơ sở dữ liệu Oracle.

Bảng EMPLOYEES sử dụng để lưu thông tin của nhân viên trong một tập đoàn đa quốc gia.

Cấu trúc của bảng như sau:

 

CREATE TABLE HUYTQ.EMPLOYEES

(
  EMP_ID      NUMBER(10)                        NOT NULL,
  FIRST_NAME  VARCHAR2(20 BYTE),
  LAST_NAME   VARCHAR2(20 BYTE),
  SALARY      NUMBER,
  NOTE        VARCHAR2(50 BYTE),
  ADDRESS     VARCHAR2(100 BYTE),
  GENDER      NUMBER,
  DOB         NUMBER,
  START_DATE  DATE                              DEFAULT sysdate-1,
  DEPTNO      NUMBER
)

ALTER TABLE HUYTQ.EMPLOYEES ADD (
  PRIMARY KEY
  (EMP_ID)
  USING INDEX
    TABLESPACE DATA
 ENABLE VALIDATE);

Bảng EMPLOYEES sử dụng khóa chính trên cột EMP_ID.

Tổng số bản ghi của bảng này là 1.100.016. 

3.2. Bài toán về tối ưu cơ sở dữ liệu đang gặp phải

Hệ thống của chúng ta thường xuyên cần thực hiện câu lệnh tìm kiếm thông tin thông qua mã nhân viên (EMP_ID).

Thời điểm cao tải có thể có vài trăm nghìn session cùng thực hiện các câu lệnh tìm kiếm kiểu này.

select * from employees where emp_id=1;
select * from employees where emp_id=2;
....
select * from employees where emp_id=1044;
select * from employees where emp_id=1045;
select * from employees where emp_id=1046;
….
select * from employees where emp_id=9400;

Tôi sẽ giả lập việc chạy 100.000 câu lệnh này bằng cách chạy vòng lặp sau 

SQL> declare
   emp_rec employees%rowtype;
 begin
   for i in 1 .. 100000 loop
     begin
       execute immediate
         'select * from employees where emp_id = ' || i
         into emp_rec;
     exception
       when no_data_found then null;
     end;
   end loop;
 end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:01:33.18

Đoạn lệnh trên có ý nghĩa như sau:

  • Thực thi lần lượt các câu lệnh dạng “select * from employees where emp_id = i” với i tăng dần từ 1 đến 100.000 
  • Vòng lặp for sẽ giúp chúng ta tạo ra 100.000 câu lệnh và thực thi chúng, giả lập đúng với bài toán đưa ra.

Thời gian thực thi của toàn bộ tiến trình này là 93.18 giây.

3.3. Phân tích các câu lệnh chạy trong thủ tục trên.

Chúng ta có thể thấy rằng 100.000 câu lệnh thực thi với ý nghĩa và chiến lược hoàn toàn giống nhau.

Bây giờ ta thử xem xét 1 câu lệnh riêng lẻ

SQL> select * from employees where emp_id=1044;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2455998802
--------------------------------------------------------------------
| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)  | Time    |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    |  1 | 36 |  3   (0)  | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |  1 | 36 |  3   (0)  | 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN     | SYS_C0011152 |  1 |    |  2   (0)  | 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_ID"=1044)

Câu lệnh này chạy chỉ mấy vài ms (thời gian cực kỳ nhanh).

Chiến lược thực thi của câu lệnh đã là tối ưu: 

  • Đầu tiên hệ thống sẽ tìm kiếm thông tin theo UNIQUE INDEX (do cột EMP_ID là PRIMARY KEY, hệ thống sẽ tự tạo 1 UNIQUE INDEX trên cột này). 
  • Do Index chỉ lưu thông tin của cột EMP_ID, nên Oracle sẽ phải thực hiện truy cập vào TABLE để lấy nôt các thông tin của những cột khác (vì người dùng yêu cầu SELECT * ). Đây chính là bước TABLE ACCESS BY INDEX ROWID
  • Nếu bạn muốn tự mình có thể phân tích và hiểu tường tận về chiến lược thực thi của câu lệnh (Execution Plans) thì có thể đăng ký chương trình tối ưu cơ sở dữ liệu cao cấp.

Như vậy, mỗi câu lệnh SQL đã được tối ưu hết mức. 

3.4. Phương án tối ưu trong trường hợp này là gì?

Trước khi đưa phương án, tôi muốn các bạn biết điều này.

Khi làm việc với Cơ sở dữ liệu, bạn có 2 cách thức.

  • Cách 1: Truyền trực tiếp giá trị vào câu lệnh. Đây là cách trong ví dụ bên trên đã thực hiện. Chúng ta truyền luôn giá trị 1044 vào câu lệnh.
SQL> select * from employees where emp_id=1044;
  • Cách 2: Truyền biến. Chúng ta sẽ đưa giá trị cần tìm kiếm vào 1 biến (ví dụ: biến :B1), khi thực hiện câu lệnh thì chúng ta sẽ khai báo cụ thể biến đó nhận giá trị bao nhiêu. Ví dụ như sau
SQL>  select * from employees where emp_id=:B1

Khi thực hiện sẽ cần nhập giá trị của biến :B1 sau.

Hai cách thức trên đều trả ra cùng 1 kết quả.

Bây giờ, thay vì sử dụng cách thức truyền giá trị thực tiếp trong vòng lặp FOR lúc đầu tiên, tôi sẽ chỉnh lại bằng cách sử dụng biến.

SQL> declare
   emp_rec employees%rowtype;
 begin
   for i in 1 .. 100000 loop
     begin
       execute immediate
         'select * from employees where emp_id = :B1'
         into emp_rec using i;
     exception
       when no_data_found then null;
     end;   
   end loop;
 end;
/ 
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.89

Wow, thời gian hiện tại chỉ còn 2.89s (chỉ bằng ~3.1% so với thời gian lúc đầu là 93s).

3.5. Giải thích cơ chế giúp kỹ thuật trên cải thiện hơn 97% trong việc tối ưu cơ sở dữ liệu.

Trước khi tôi giải thích tất cả nguyên lý trên một cách cặn kẽ, các bạn có thể xem một tình huống tương tự nhưng ở trong đời thường.

3.5.1 BÀI TOÁN TÌM ĐƯỜNG ĐẾN TRẠM Y TẾ ĐỂ TIÊM VACCINE COVID-19

Hôm nay phường Tây Mỗ cần thực hiện tiêm chủng vaccine phòng chống COVID-19. Đội y tế phường yêu cầu 100.00 hộ dân  khu đô thị Vinhome Smart City ra đến trạm y tế phường để tiêm chủng. Những người dân tại khu vực này mới chuyển đến và đều không biết chính xác đường đi từ nhà ra đến trạm y tế phường.

Ở dưới tòa chung cư này có một chốt bảo vệ rộng 3m2, trong đó có một người thông thạo mọi con đường trong khu vực phường Tây Mỗ. Mỗi người dân của tòa chung cư này đều phải vào trong chốt bảo vệ để hỏi về con đường nào “ngắn nhất” đi từ nhà mình ra đến nơi tiêm chủng. 

Ngày tiêm chủng đã tới, 100.000 hộ dân đều tụ tập xoay quanh chốt bảo vệ. Và đây là những gì đã diễn ra:

  • Người dân đầu tiên vào chốt và nói: Tôi đang ở phòng 1501 thì nên đi thế nào?
  • Người bảo vệ: Để tôi phân tích xem có bao nhiêu con đường đi… Uhm, từ nhà anh muốn ra phường có thể đi 5 con đường khác nhau. Trong đó muốn đi đường ngắn nhất thì anh đi thẳng, sau đó rẽ phải và bla bla… (Việc phân tích và lựa chọn đường đi này diễn ra mất 1 phút).
  • Người dân thứ hai: Tôi đang ở phòng 1502 thì nên đi thế nào?
  • Người bảo vệ: Thực hiện phân tích lại từ đầu và thấy rằng chiến lược đi cũng giống như người đầu tiên. Và việc phân tích này diễn ra cũng mất 1 phút
  • Người thứ 100.000: Cũng diễn ra như vậy nhưng đã bị muộn giờ tiêm. 

Nếu ở tình huống đời thường này, chúng ta có thể thấy cách thức mà khu dân cư này hoạt động thực sự không hiệu quả, phải không nào?

Vấn đề ở đây là 100.00 hộ dân này tuy ở các tầng khác nhau, ví dụ: người ở phòng 202, người ở phòng 1503, nhưng vẫn trong 1 tòa chung cư. Vì vậy về mặt logic thì những người này sẽ có cùng một con đường đi từ nhà mình ra đến nơi tiêm chủng.

Nếu người bảo vệ kia nhận ra điều này và chỉnh lại như sau:

  • Người dân đầu tiên: Tôi đang ở phòng 1501 thì nên đi thế nào?
  • Người bảo vệ: Để tôi phân tích xem có bao nhiêu con đường đi… Uhm, từ nhà anh muốn ra phường có thể đi 5 con đường khác nhau. Trong đó muốn đi đường ngắn nhất thì anh đi thẳng, sau đó rẽ phải và bla bla… (Việc phân tích và lựa chọn đường đi này diễn ra mất 1 phút). Sau đó người bảo vệ lưu lại thông tin về con đường đi ngắn nhất này ra 1 tấm bản đồ cầm tay.
  • Người thứ hai vào chốt: Tôi đang ở phòng 1502 thì nên đi thế nào?
  • Người bảo vệ: Ah, tôi sẽ không cần phải phân tích lại đường đi mà sẽ dùng ngay thông tin lúc nãy phân tích được. Người bảo vệ đưa luôn thông tin bản đồ cho người thứ hai. Việc này diễn ra chỉ mất 3 giây. 
  • Cứ như vậy mọi việc xảy ra, và toàn bộ cư dân đều đến được địa điểm 1 cách nhanh chóng :).

Ví dụ trên chính là cách thức mà tôi đã xử lý bài toán tối ưu cơ sở dữ liệu đặt ra.

Về mặt tổng quan thì các bạn có thể đã hình dung được.

Bây giờ tôi sẽ đưa tất cả những thứ này vào góc nhìn của Cơ sở dữ liệu Oracle. 

3.5.2. Đây là những thứ “xảy ra bên trong” của Cơ sở dữ liệu Oracle khi chúng ta gửi một câu lệnh SQL tới.

6 bước thực thi 1 câu lệnh - phải biết khi tối ưu cơ sở dữ liệu
6 bước thực thi 1 câu lệnh – phải biết khi tối ưu cơ sở dữ liệu

Bước 1: Thực hiện kiểm tra cú pháp của câu lệnh. Ví dụ khi chúng ta gõ sai cú pháp

select * employees where emp_id=100

Hệ thống kiểm tra và biết được câu lệnh trên bị lỗi cú pháp, thông báo lỗi như sau

ERROR at line 1:
ORA-00923: FROM keyword not found where expected.

Bước 2: Kiểm tra ngữ nghĩa của câu lệnh. 

  • Nếu như câu lệnh đã hoàn toàn đúng cú pháp, hệ thống sẽ kiểm tra xem về mặt “ý nghĩa” thì người dùng đang muốn làm gì.
  • Hệ thống kiểm tra xem table mà người dùng định tương tác có tồn tại không, nếu tồn tại thì người dùng có quyền để làm việc trên đó không, các cột mà người dùng định lấy dữ liệu có trong bảng hay không …
  • Ví dụ: Khi chúng ta thực hiện câu lệnh 
select * from employee where emp_id=100
  • Về mặt ngữ pháp, câu lệnh này không có lỗi gì, nên hệ thống sẽ thực hện bước kiểm tra “ngữ nghĩa”. Sau khi kiểm tra thì thấy người dùng muốn làm việc với một OBJECT tên là EMPLOYEE (có thể là TABLE hoặc VIEW vì object này đứng đằng sau FROM). Hệ thống kiểm tra thông tin các objects mà mình có thì thấy rằng không tồn tại object nào tên là EMPLOYEE cả. Cảnh báo lỗi người dùng nhận được như sau:
ERROR at line 1:
ORA-00942: table or view does not exist.

Bước 3: Kiểm tra thông tin trong Shared Pool (bộ nhớ lưu giữ các chiến lược thực thi của những câu lệnh đã từng thực hiện trong hệ thống.Nếu bạn muốn tìm hiểu thêm về kiến trúc tổng quan của Oracle, bao gồm những vùng bộ nhớ quan trọng khác thì bạn có thể xem tại video mô tả bên dưới). Nếu như đã tìm thấy thông tin về chiến lược thực thi của câu lệnh trong Shared Pool thì thực hiện bước 6 (bỏ qua bước 4 và bước 5). Nếu chưa tìm thấy thông tin thì lần lượt thực hiện 3 bước còn lại (bước 4,5 và 6).

  • Trường hợp nếu Oracle không tìm thấy thông tin trong Shared Pool và buộc phải làm đầy đủ cả 6 bước, chúng ta gọi là HARD PARSE.
  • Trường hợp nếu Oracle tìm thấy thông tin và bỏ qua bước 4,5, chúng ta gọi là SOFT PARSE
  • Trước khi hoàn thành bước số 3 này, Oracle sử dụng một giải thuật HASH để chuyển câu lênh SQL FULL TEXT ban đầu thành một mã SQL_ID. Hàm HASH này có thể thay đổi tùy vào phiên bản của Oracle database mà bạn sử dụng.
  • Ở đây bạn có thể thấy một điều thú vị là:
    • Các câu lệnh SQL có giá trị FULL TEXT giống nhau 100% thì sẽ cùng có SQL_ID, mặc dù các câu lệnh này có thể chạy trên các hệ thống khác nhau (miễn là cùng phiên bản Oracle database).

Bước 4: Thực hiện tính toán và phân tích tất cả những chiến lược thực thi có thể sử dụng để thực hiện câu lệnh mà người dùng yêu cầu. và lựa chọn ra chiến lược thực thi có chi phí tối ưu.

Bước 5: Nhận chiến lược thực thi tối ưu và sinh ra 1 kế hoạch thực thi cụ thể, chi tiết cho câu lệnh. Kế hoạch thực thi của một câu lệnh có dạng như dưới đây:

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)
| Time    |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    |  1 | 36 |  3   (0)
| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |  1 | 36 |  3   (0)
| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN     | SYS_C0011152 |  1 |    |  2   (0)
| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_ID"=100)
  • Dựa vào thông tin này, Cơ sở dữ liệu sẽ biết chính xác mình cần xử lý những gì để lấy được kết quả mong muốn.(xử lý từ các nguồn dữ liệu nào, sử dụng chiến lược quét FULL TABLE hay quét INDEX, nếu quét INDEX thì sử dụng giải thuật gì, các bước thực hiện theo trình tự nào…)

Bước 6: Câu lệnh thực thi theo kế hoạch đã được lựa chọn.

3.5.3. Phân tích giải pháp tối ưu Cơ sở dữ liệu trong bài toán thực tế.

Đến đây bạn đã hiểu được 6 bước cơ sở dữ liệu Oracle cần phải thực hiện khi nhận một câu lệnh SQL. Chúng ta cùng phân tích 6 bước này vào trong bài toán đang thực hiện tối ưu nhé.

a. Đối với trường hợp truyền trực tiếp giá trị cho câu lệnh.
select * from employees where emp_id=1;
select * from employees where emp_id=2;
....
select * from employees where emp_id=1044;
select * from employees where emp_id=1045;
select * from employees where emp_id=1046;
….
select * from employees where emp_id=9400;

Do các câu lệnh có “nội dung” khác nhau (vì những giá trị truyền vào là khác nhau), vì vậy Cơ sở dữ liệu Oracle sẽ thực hiện như sau

  • Tất cả các câu lệnh đều cần thực hiện bước số 1 – phân tích cú pháp. Cú pháp lệnh đúng nên sẽ được đi tiếp bước sau.
  • Các câu lệnh đều thực hiện phân tích ngữ nghĩa. Tại đây Oracle thấy rằng ngữ nghĩa chuẩn xác, hệ thống tiếp tục đi sang bước thứ 3
  • Tại bước kiểm tra thông tin trong Shared Pool, do đây là những câu lệnh khác nhau, Oracle sẽ cần thực hiện 10.000 lần HARD PARSE.
  • Hệ thống phải thực hiện 10.000 lần bước số 4 và bước số 5 (đây là hai công việc cực kỳ tiêu tốn tài nguyên).
  • Sau đó hệ thống thực hiện chạy câu lệnh theo chiến lược đã có ở bước số 5.
b. Đối với trường hợp chúng ta sử dụng phương pháp truyền biến
select * from employees where emp_id=:B1

Trong trường hợp này, Oracle sẽ nhìn thấy 10.000 câu lệnh truyền vào là giống nhau (vì đoạn text của câu lệnh giống nhau hoàn toàn). Do đó các công việc thực tế xảy ra như sau

  • Hệ thống chỉ cần thực hiện phân tích câu lệnh 1 lần duy nhất (đối với câu lệnh đầu tiên).
  • Với 9.999 câu lệnh còn lại, Oralce sẽ lấy luôn chiến lược thực thi (thực hiện SOFT PARSE – bỏ qua 2 bước tiêu tốn tài nguyên là bước số 4 và bước số 5).

Chính việc này dẫn đến kết quả tối ưu đã cải thiện hơn 97% (từ 93s còn ~3s).

4. Trong dự án tối ưu cơ sở dữ liệu thực tế, nếu luôn dùng truyền biến trong các câu lệnh SQL thì có rủi ro gì không?

Để giúp các bạn có thể trải nghiệm nhiều hơn nữa kỹ thuật tối ưu cơ sở dữ liệu trong những dự án thực tế, chúng ta hãy cùng xem xét một tình huống sau đây.

Giả sử bạn cần thực hiện 2 câu lệnh liên tiếp nhau như sau:

select * from employees where salary < 500;
select * from employees where salary < 50000;

Thông tin về bảng employees như sau:

  • Tổng số bản ghi: 1.100.016 bản ghi.
  • Bảng employees đã được đánh index trên cột salary. Index này sử dụng dạng Btree với tên là IDX_SALARYTEST

Bảng employees có 1.100.016 bản ghi. Trong đó có

  • 2 bản ghi thỏa mãn điều kiện salary < 500 (chiếm 0.00018% tổng số lượng bản ghi)
  • 342.344 bản ghi thỏa mãn điều kiện salary < 50000 (hơn 31% tổng số lượng bản ghi).

Chúng ta có thể thấy rõ ràng về tập kết quả cần tìm kiếm của 2 câu lệnh trên là RẤT KHÁC NHAU (về số lượng). Rõ ràng chiến lược thực thi của 2 câu lệnh này cũng hoàn toàn khác nhau.

Dưới đây là chiến lược thực thi mà Oracle sử dụng đối với từng câu lệnh

Đối với câu lệnh thứ nhất:

select * from employees where salary < 500;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 4278 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 4278 | 9 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_SALARYTEST | 6 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALARY"<500)

Với câu lệnh thứ nhất Oracle sẽ thực sử dụng Index để thực hiện tìm kiếm.

Đối với câu lệnh thứ hai

select * from employees where salary < 50000;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1100K| 747M| 39879 (1)| 00:07:59 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1100K| 747M| 39879 (1)| 00:07:59 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
1 - filter("SALARY"<500000)

Chúng ta có thể thấy rằng: Oracle sử dụng giải thuật quét toàn bộ bảng (FULL TABLE SCAN) đối với việc tìm kiếm các bản ghi thỏa mãn điều kiện SALARY < 50000. Tại sao ở câu lệnh thứ hai này Oracle không sử dụng Index?

  • Do bản chất của việc tìm kiếm dựa trên Index và tìm kiếm toàn bộ bảng là khác nhau.
  • Oracle ưu tiên tìm kiếm index nếu số lượng bản thi thỏa mãn là một tập nhỏ (trong trường hợp câu lệnh thứ nhất, số bản ghi thỏa mãn điều kiện chỉ chiếm 0.00018%.
  • Oracle sẽ đánh giá các chiến lược thực thi theo chi phí (Cost) cần phải thực hiện, và sẽ quyết định lựa chọn chiến lược nào có chi phí thấp nhất.

Như vậy ta thấy rằng 2 chiến lược thực thi tối ưu cho 2 câu lệnh trên là hoàn toàn khác nhau.

Bây giờ nếu chúng ta sử dụng truyền biến thì sẽ như thế nào, hãy cùng phân tích nhé.

Câu lệnh của chúng ta sẽ truyền vào như sau:

select * from employees where salary < :B1

Đối với câu lệnh đầu tiên:

  • Người dùng nhập vào giá trị :B1 = 500.
  • Oracle sẽ thực hiện phân tích và thấy rằng đối với giá trị biến :B1 = 500 thì giải pháp tốt nhất là sử dụng Index.
  • Oracle lưu lại thông tin chiến lược thực thi này

Người dùng tiếp tục thực hiện câu lệnh thứ hai

  • Người dùng nhập giá trị :B1=50000
  • Oracle sử dụng luôn chiến lược thực thi đã được lưu trước đó (sử dụng Index).

Ta có thể thấy Oracle đã chọn sai chiến lược “tốt nhất” cho câu lệnh thứ hai !!!.

Như vậy, để có thể thực sự tối ưu những cơ sở dữ liệu phức tạp trong thực tế, chúng ta cần cân nhắc và đo lường rất nhiều yếu tố khác nhau. Tôi sẽ tiếp tục cập nhật sâu hơn nữa các dự án tối ưu thực tế của mình trong thời gian tới.

5. Sai lầm của những anh em lập trình mắc phải là gì?

Các anh em lập trình đã mắc phải một số sai lầm sau trong quá trình xây dựng các câu lệnh SQL.

Thứ nhất: Tin vào một số “LUẬT TRUYỀN MIỆNG” mà không hiểu rõ nguyên lý hoạt động của Cơ sở dữ liệu. Đa phần anh em lập trình chỉ nhìn mặc hiệu năng ở mức “Câu lệnh SQL”, thiết kế bảng, index. Tuy nhiên không thật sự hiểu về kiến trúc của các loại objects này, cách thức mà Cơ sở dữ liệu hành xử khi tương tác với các objects. Ví dụ như:

  • Có một số anh em đọc thấy LUẬT rằng: cứ quét FULL TABLE thì có hiệu năng chậm hơn so với sử dụng Index.
  • Sử dụng với chiến lược INDEX SKIP SCAN thì không hiệu quả.
  • Câu lệnh SELECT mà không có điều kiện WHERE thì không thể sử dụng Index.

Tôi đã trực tiếp lý giải và chứng minh rất nhiều trường hợp ngược lại với các luật trên.

  • Mọi người nói rằng FULL TABLE SCAN luôn có hiệu năng chậm hơn so với việc sử dụng Index. Tôi dẫn chứng các trường hợp nêu sử dụng INDEX chậm hơn nhiều lần so với FULL TABLE SCAN
  • Mọi người nói rằng sử dụng INDEX SKIP SCAN không hiệu quả. Tôi lấy các trường hợp nếu dùn INDEX SKIP SCAN thì cực kỳ nhanh. Tôi đã áp dụng trong những bài toán tối ưu thực tế.
  • Mọi người nói rằng câu lệnh SELECT mà không có điều kiện WHERE thì không có cách nào sử dụng Index. Tôi liền lấy dẫn chứng rằng không có WHERE thì vẫn có thể dùng Index

Muốn thật sự trở thành KHÁC BIỆT và THỰC SỰ có năng lực TỐI ƯU, chúng ta cần hiểu rõ một cách TƯỜNG MINH những gì diễn ra bên trong một Cơ sở dữ liệu, từ lúc chúng ta bắt đầu tương tác, đến khi chúng ta nhận được những kết quả mong muốn.

Thứ hai: Sai lầm khi tìn rằng một bảng khi thêm Index sẽ làm chậm các câu lệnh Insert, Update, Delete.

Thứ ba: Mọi người nói rằng một bảng có ít bản ghi thì truy vấn sẽ nhanh. Tôi lấy dẫn chứng về một bảng có 0 bản ghi nhưng SELECT vẫn cực kỳ lâu, và cũng giải thích gốc rễ của vấn đề hiệu năng này.

6. Một số ví dụ khác về tối ưu Cơ sở dữ liệu

6.1. Tăng tốc câu lệnh Insert sử dụng Insert Append

Giả sử Cơ sở dữ liệu của bạn thường xuyên có những tiến trình Insert dữ liệu lớn.

Ví dụ: mục tiêu của chúng ta cần tối ưu câu lệnh sau

insert into test_insert select * from employees

Chúng ta có thể cải thiện thời gian Insert này lên nhiều lần với HINT APPEND sau

insert /*+ APPEND*/ into test_insert select * from employees

Việc phân tích chi tiết, tại sao APPEND giúp cải thiện hiệu năng, bạn có thể đọc tại đây.

6.1. Tăng tốc câu lệnh Delete từ 11 phút còn 1s

Thực hiện phân tích chiến lược thực thi của câu lênh Delete

delete from wecommit_test where orderqtty=3000;

Đây là câu lệnh mà chúng ta sẽ cần tối ưu.
Chiến lược thực thi của câu lệnh này như sau:

Plan hash value: 284335732
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 785 | 3140 | 55268 (1)| 00:11:04 |
| 1 | DELETE | WECOMMIT_TEST | | | | |
|* 2 | TABLE ACCESS FULL| WECOMMIT_TEST | 785 | 3140 | 55268 (1)| 00:11:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORDERQTTY"=3000)

Thời gian thực hiện dự kiến của câu lệnh là 11 phút 04s.
Dựa vào chiến lược thực thi trên, chúng ta có thể tối ưu câu lệnh này xuống còn 01s.
Chi tiết của phương án tối ưu này các bạn có thể xem tại đây.

7. Các bài viết khác về tối ưu Cơ sở dữ liệu mà bạn nên đọc

Tối ưu cơ sở dữ liệu với Index – sử dụng Btree Index và Bitmap Index khác nhau thế nào: Link bài viết tại đây

Tối ưu cơ sở dữ liệu đối với trường hợp Table bị phân mảnh: Link bài viết tại đây

Tối ưu cơ sở dữ liệu – tăng tốc câu lệnh Delete từ 11 phút còn 1s: Link bài viết tại đây

8. Tôi có một chương trình có phí – Từ điển tối ưu 100x hiệu năng – chương trình này sẽ giúp bạn HOÀN TOÀN TỰ TIN và KHÁC BIỆT so với các đồng nghiệp về năng lực tối ưu cơ sở dữ liệu

Trong chương trình này bạn sẽ nhận được rất nhiều các KIẾN THỨC, KINH NGHIỆM ĐỘC QUYỀN, có nhiều thứ các anh em DEV còn không biết về sự tồn tại.

Tôi sẽ đồng hành 1 năm cùng anh em, để anh em hoàn toàn tự tin khi áp dụng các kiến thức vào dự án thực tế trên công ty.

Đây là một chương trình có phí, anh em có thể tham gia chương trình 01 năm ngay từ mức phí 8.000.000 VNĐ.

Các anh em có thể đăng ký trải nghiệm để hiểu rõ mọi thứ trong chương trình để tự đánh giá được chương trình có phù hợp với mục tiêu của bản thân hay không.

Buổi trải nghiệm này diễn ra qua Zoom Online, cách thức đăng ký tham gia trải nghiệm như sau:

  • Cách 1: Đăng ký trực tiếp trên Website wecommit.com.vn qua Form đăng ký 
  • Cách 2: Inbox Zalo 0888549190 

 

9. Thông tin tác giả

Tác giả: Trần Quốc Huy – Founder & CEO Wecommit

Facebook: https://www.facebook.com/tranquochuy.toiuu/

Email: huy.tranquoc@wecommit.com.vn

Youtube: Trần Quốc Huy

Số điện thoại: 0888549190

Lời nhắn dành cho các bạn có duyên với bài viết:

  • Hãy có tư duy khác biệt và tập trung vào những việc giá trị 100x (các công việc mang lại giá trị cực cao).
  • Nếu như 100.000 người ngoài kia đang đi 1 con đường, bạn cũng đi cùng con đường đó thì tại sao lại mang kết quả khác?
Views: 11786





    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 *