Tối ưu hóa chèn khối lượng lớn

Sử dụng lệnh load để nhập dữ liệu

1.1 Đối với bảng MyISAM

Đối với bảng sử dụng engine MyISAM, có thể thực hiện nhập dữ liệu lớn bằng ba bước sau:

-- Tắt cập nhật chỉ mục không duy nhất
ALTER TABLE table_name DISABLE KEYS;
-- Nhập dữ liệu từ tệp vào bảng
load data infile filepath into table table_name;
-- Bật lại cập nhật chỉ mục không duy nhất
ALTER TABLE table_name ENABLE KEYS;

Khi nhập dữ liệu vào bảng trống MyISAM, có thể bỏ qua hai lệnh trên. Tuy nhiên, khi nhập vào bảng đã có dữ liệu, cần thiết lập thủ công hai tham số này.

1.2 Đối với bảng InnoDB

-- Nhập tệp địa phương, mỗi cột phân tách bằng dấu phẩy, mỗi dòng phân tách bằng ký tự xuống dòng
load data local infile 'F:/Program Files/sql1.log' into table user_table FIELDS TERMINATED by ',' LINES TERMINATED by '\n'

1. Vì InnoDB lưu trữ theo thứ tự khóa chính, nên sắp xếp dữ liệu theo khóa chính sẽ tăng hiệu suất nhập. Nếu không có khóa chính, hệ thống sẽ tạo khóa ẩn, nên nên tạo khóa chính để tối ưu.
2. Trước khi nhập, thực hiện SET UNIQUE_CHECKS = 0 để tắt kiểm tra duy nhất, sau đó bật lại bằng SET UNIQUE_CHECKS = 1.
3. Nếu sử dụng giao dịch tự động, nên tắt bằng SET AUTOCOMMIT=0 trước khi nhập.

Tối ưu hóa lệnh INSERT

• Khi chèn nhiều dòng, nên dùng cú pháp chèn nhiều giá trị trong một lệnh:

-- Cách ban đầu
insert into test_data values(1,'Tom');
insert into test_data values(2,'Cat');
insert into test_data values(3,'Jerry');

-- Cách tối ưu
insert into test_data values(1,'Tom'),(2,'Cat'),(3,'Jerry');

• Thực hiện trong giao dịch:

start transaction;
SET AUTOCOMMIT=0;
insert into test_data values(1,'Tom');
insert into test_data values(2,'Cat');
insert into test_data values(3,'Jerry');
commit;

• Sắp xếp dữ liệu theo thứ tự tăng dần:

-- Trước tối ưu
insert into test_data values(4,'Tim');
insert into test_data values(1,'Tom');
insert into test_data values(3,'Jerry');
insert into test_data values(5,'Rose');
insert into test_data values(2,'Cat');

-- Sau tối ưu
insert into test_data values(1,'Tom');
insert into test_data values(2,'Cat');
insert into test_data values(3,'Jerry');
insert into test_data values(4,'Tim');
insert into test_data values(5,'Rose');

• Dùng lệnh load data infile khi nhập từ tệp văn bản.

Tối ưu hóa ORDER BY

3.1 Chuẩn bị môi trường

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `employee` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
... (các lệnh insert khác)

create index idx_employee_age_salary on employee(age,salary);

3.2 Hai cách sắp xếp trong MySQL

1. Sử dụng chỉ mục có thứ tự để trả về dữ liệu đã sắp xếp (using index).
2. Dùng Filesort để sắp xếp dữ liệu (using filesort).

3.3 Tối ưu ORDER BY

1. Chỉ chọn các trường có trong chỉ mục và khóa chính.
2. Sử dụng hai chỉ mục khác nhau sẽ gây ra filesort.
3. Thứ tự trường trong ORDER BY phải khớp với chỉ mục.
4. Kết hợp cả ORDER BY tăng/giảm sẽ gây filesort.

3.4 Tối ưu WHERE + ORDER BY

• Sử dụng cùng một chỉ mục cho WHERE và ORDER BY.
• Ưu tiên ORDER BY theo cùng hướng tăng/giảm.

3.5 Tối ưu Filesort

MySQL sử dụng hai thuật toán sắp xếp:

1. Thuật toán quét hai lần (dưới MySQL 4.1).
2. Thuật toán quét một lần (từ MySQL 4.1 trở lên).

Có thể tăng hiệu suất bằng cách điều chỉnh các biến hệ thống sort_buffer_size và max_length_for_sort_data.

Tối ưu GROUP BY

Sử dụng ORDER BY NULL để tránh sắp xếp:

-- Với filesort
explain select * from employee group by age;

-- Không dùng filesort
explain select * from employee group by age order by null;

Tối ưu truy vấn lồng nhau

Dùng JOIN thay vì subquery:

-- Truy vấn lồng nhau
explain select * from users where id in (select user_id from user_roles);

-- Truy vấn JOIN
explain select * from users u, user_roles ur where u.id = ur.user_id;

Tối ưu điều kiện OR

Các điều kiện trong OR đều phải có chỉ mục. Sử dụng UNION thay thế OR:

-- Dùng OR
explain select * from employee where name = 'Tom' or age = 25;

-- Dùng UNION
explain select * from employee where name = 'Tom'
union
select * from employee where age = 25;

Tối ưu phân trang

Phương pháp 1: Sử dụng chỉ mục bao phủ:

explain select id from employee order by id limit 2000000,10;

Phương pháp 2: Chuyển đổi limit thành truy vấn vị trí.

Tối ưu JOIN

• Đảm bảo bảng phụ có chỉ mục trên cột JOIN.
• Chọn bảng nhỏ làm bảng lái (drive table) với LEFT JOIN.
• MySQL tự động chọn bảng nhỏ làm bảng lái với INNER JOIN.

Sử dụng SQL Hints

-- Gợi ý sử dụng chỉ mục
explain select count(*) from user_table USE index(PRIMARY);

-- Bỏ qua chỉ mục
explain select count(*) from user_table IGNORE index(PRIMARY);

-- Buộc dùng chỉ mục
explain select count(*) from user_table FORCE index(PRIMARY);