Tối ưu câu lệnh Insert trong MySql và Laravel

Tối ưu câu lệnh Insert trong Production – cải thiện hơn 17 lần nếu áp dụng đúng kỹ thuật

Giá trị mà bạn nhận được trong bài viết này:

1. Hiểu được những vấn đề có thể ảnh hưởng đến hiệu năng của câu lệnh Insert

2. Biết được một trong số các kỹ thuật tối ưu câu lênh Insert đang được thực hiện trong các dự án tại ngân hàng, chứng khoán, bệnh viện....

1. Cậu lệnh Insert thực hiện lâu nhưng nguyên nhân gốc có thể nằm ở chỗ khác?

Không phải bất kỳ câu lệnh Insert nào thực hiện lâu cũng do nội tại của câu lệnh, anh em có thể thực hiện kiểm tra ngay 2 vấn đề sau

• Thứ nhất: Có Trigger nào được gọi khi câu lệnh Insert của chúng ta thực thi hay không
• Thứ hai: Kiểm tra câu lệnh có đang bị LOCK không

1.1. Kiểm tra trigger trong Cơ sở dữ liệu thế nào?

Kiểm tra toàn bộ danh sách Trigger trong Cơ sở dữ liệu

select * from dba_triggers;

Kiểm tra các trigger có liên quan đến TABLE mà chúng ta đang thực hiện lệnh INSERT

select * from dba_triggers where table_name='<TABLE_NAME>’;

1.2. Kiểm tra LOCK trong cơ sở dữ liệu

Tất cả các cơ sở dữ liệu đều có cơ chế LOCK (để bảo vệ tính toàn vẹn dữ liệu) khi các transaction có thực hiên lệnh DML (Delete, Insert, Update).

Hãy xem ví dụ sau:

Thực hiện tạo table tên là WECOMMIT_TABLE phục vụ việc Demo

create table wecommit_table(id number primary key, name varchar2(100));

Chúng ta tạo 2 transaction phục vụ việc Demo này

• Tiến trình thứ nhất (bên tay trái trong ảnh) thực hiện lệnh vào thời điểm 10h. Câu lệnh này thực hiện được ngay lập tức.

SQL> insert into wecommit_table values(‘1′,’Wecommit1’);

1 row created.

• Tiến trình thứ hai (bên tay phải trong ảnh) thực hiện sau tiến trình thứ nhất 1 phút. Câu lệnh cũng vô cùng đơn giản

SQL> insert into wecommit_table values (‘1′,’Wecommit_Pending’);

• Kết quả: Tiến trình thứ hai bị TREO (do hệ thống đang LOCK vì cùng Insert một giá trị Primary Key của bảng). Nếu trường hợp này người dùng chúng ta đang đứng ở phiên làm việc thứ 2 sẽ có cảm giác “KHÔNG THỂ HIỂU NỔI, TẠI SAO INSERT CÓ 1 BẢN GHI MÀ LẠI CHẬM VÃI CHƯỞNG!!!!”

2. Kỹ thuât sử dụng HINT để tối ưu câu lệnh Insert

2.1. Phương án 1: Thực hiên INSERT dữ liệu từ kết quả của câu lệnh SELECT

Tại đây tôi thực hiện tạo 1 bảng có cấu trúc giống với bảng WECOMMIT_TABLE

CREATE TABLE HUYTQ.WECOMMIT_TABLE_NEW
(
ID NUMBER,
NAME VARCHAR2(100 BYTE)
);

SQL> insert into WECOMMIT_TABLE_NEW select * from WECOMMIT_TABLE;

1000000 rows created.

Elapsed: 00:00:01.13

SQL> commit;

Commit complete.

2.2. Phương án 2: Thực hiện INSERT nhưng thêm HINT APPEND

SQL> insert /*+ APPEND*/ into WECOMMIT_TABLE_NEW select * from WECOMMIT_TABLE;

1000000 rows created.

Elapsed: 00:00:00.60

Commit complete.

Elapsed: 00:00:00.00

2.3. Kết luận

Sử dụng HINT APPEND đã giúp câu lệnh INSERT cải thiện hơn 2 lần.

Bản chất HINT APPEND này là gì và tại sao có thể cải thiện hiệu năng sẽ được tôi phân tích riêng trên kênh trao đổi của nhóm đặc quyền.

3. Kỹ thuật tối ưu câu lệnh Insert buộc phải biết

Thực hiện 1 triệu lần, mỗi lần Insert 1 bản ghi hay thực hiện 1 lần những Insert 1 triệu bản ghi?

Hãy giảm thiểu số lần gọi câu lệnh SQL đến Database.

Các ví dụ dưới đây tôi sẽ áp dụng kỹ thuật sử dụng 1 bảng TEMP với câu lệnh WITH

WITH
temp
AS
( SELECT LEVEL x
FROM DUAL
CONNECT BY LEVEL <= 1000000)
SELECT ROWNUM id, ‘Wecommit ‘ || ROWNUM AS name
FROM temp

Câu lệnh này bản chất sẽ tạo ra 1 bảng TEMP có dữ liệu như sau:

• Bảng này có 1.000.000 bản ghi
• Bảng có 2 cột: Cột ID và Cột Name
• Cột ID nhận giá trị từ 1 đến 1.000.000
• Cột Name nhận giá trị kết hợp của chuỗi Wecommit + ID

3.1 Phương án 1Thực hiện Insert 1 triệu lần, mỗi lần 1 bản ghi

Thực hiện Insert vào bảng WECOMMIT_TABLE như sau

BEGIN
FOR i IN (WITH
temp
AS
( SELECT LEVEL x
FROM DUAL
CONNECT BY LEVEL <= 1000000)
SELECT ROWNUM id, ‘Wecommit ‘ || ROWNUM AS name
FROM temp)
LOOP
INSERT INTO WECOMMIT_TABLE
VALUES (i.id, i.name);
END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.56

5.2. Thực hiện 1 lần Insert nhưng Insert 1 triệu bản ghi

Thực hiện Truncate Table WECOMMIT_TABLE để đảm bảo tính khách quan trước khi Demo

SQL> truncate table WECOMMIT_TABLE;

Table truncated.

INSERT INTO WECOMMIT_TABLE
WITH
temp
AS
( SELECT LEVEL x
FROM DUAL
CONNECT BY LEVEL <= 1000000)
SELECT ROWNUM id, ‘Wecommit ‘ || ROWNUM AS name
FROM temp;

1000000 rows created.

Elapsed: 00:00:04.76

3.3. Nếu áp dụng thêm chiêu thức HINT APPEND bên trên thì sao?

SQL> truncate table WECOMMIT_TABLE;

Table truncated.

SQL> INSERT /*+ APPEND*/
INTO WECOMMIT_TABLE
WITH
temp
AS
( SELECT LEVEL x
FROM DUAL
CONNECT BY LEVEL <= 1000000)
SELECT ROWNUM id, ‘Wecommit ‘ || ROWNUM AS name
FROM temp 2 3 4 5 6 7 8 9 10 ;

1000000 rows created.

Elapsed: 00:00:02.68

Tại đây chúng ta thấy: Cùng 1 mục đích insert 1 triệu giá trị vào bảng WECOMMIT_TABLE, nhưng ta đã tối ưu từ hơn 44 giây xuống chỉ còn 2 giây 68 (cải thiện 17 lần)

4. Các câu lệnh trong thực tế đều cần phải COMMIT, tần suất COMMIT có ảnh hưởng đến hiệu năng hay không?

Câu trả lời là có.

Chúng ta hãy cùng xem 2 vòng lặp Insert nhưng tần suất COMMIT khác nhau bên dưới đây

4.1. Phương án 1: Thực hiện COMMIT sau khi đã hoàn thành việc INSERT 1.000.000 bản ghi

SQL> set timing on
SQL> truncate table wecommit_table;

Table truncated.

Thực hiện Insert 1 triệu bản ghi vào bảng WECOMMIT_TABLES

SQL> begin
for i in 1 .. 1000000 loop
insert into WECOMMIT_TABLE values ( i, ‘WECOMMIT’||i);
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:43.98

4.2. Phương án 2: Thực hiện COMMIT sau mỗi lệnh INSERT

Thực hiện truncate table trước khi Insert dữ liệu mới

SQL> truncate table WECOMMIT_TABLE;

Table truncated.

SQL> begin
for i in 1 .. 1000000 loop
insert into WECOMMIT_TABLE values ( i, ‘WECOMMIT’||i);
commit;
end loop;
end;
 /

PL/SQL procedure successfully completed.

Elapsed: 00:02:39.00

5. Tối ưu câu lệnh Insert dựa trên tối ưu Subquery

Trong nhiều trường hợp chúng ta sẽ cần thực hiện câu lệnh INSERT với kết quả trả ra của 1 câu lệnh SELECT.

Ví dụ xem xét câu lệnh sau

INSERT INTO emp_bk(EMPNO,ENAME)
SELECT id, last_name FROM emp where salary < 500

Chiến lược thực thi của câu lệnh này:

Câu lênh Insert ước lượng mất 7 phút 58s để thực hiện xong, trong đó thời gian lâu nhất chủ yếu là ở câu lệnh Subquery.

Chúng ta sẽ tối ưu câu lệnh Insert này bằng cách thực hiện tối ưu câu lệnh Subquery

SELECT id, last_name FROM emp where salary < 500

Trong trường hợp này chúng ta có thể áp dụng kỹ thuật tối ưu sử dụng Index:

create index idx_wecommit_salary on emp(salary)

Chiến lược thực thi sau khi tạo Index này như sau

Kết quả câu lệnh đã giảm từ 7ph 58s xuống còn 01s.