Video học lập trình mỗi ngày

Bài viết này được đưa vào: Series: Tuần sau tôi đi phỏng vấn Backend

Series bao gồm nhiều câu hỏi phỏng vấn về cấp độ ngôn ngữ cũng như thiết kế hệ thống dành cho vị trí BackEnd. Nó phù hợp cho tất cả các level...

...

Khuyến nghị: Stop use LIKE, cải thiện tìm kiếm từ 6s xuống còn 1s trong MYSQL như thế nào?

Đầu tiền hãy chọn xem MyISAM và InnoDB xử lý

Tôi có hai table mỗi table có 13 triệu record. Nhưng hãy xem khi tôi chạy vẫn là câu lệnh SELECT COUNT(*) nhưng một table mất 4.2s và một table mất 0.002s.

SELECT COUNT(*) FROM products; -- 13000372
SELECT COUNT(*) FROM productsB; -- 13000372

Thì hiệu suất lại đối lập nhau, một bên là 0,002s bên là tốc độ khủng khiếp so với table còn lại. Và đây chúng ta bắt đầu xem xét nó vì sao có sự khác biệt về hiệu suất như thế này?

Đầu tiên thì sự khác biệt nằm ở cách ăn ở của kiểu dữ liệu MyISAM và InnoDB. Tôi nói sơ qua và đi cụ thể từng bước.

MyISAM: Lưu tổng số dòng của toàn bộ bảng vào một biến metadata. Khi bạn chạy COUNT(*) (không có WHERE), nó chỉ cần đọc giá trị biến này ra, cực kỳ nhanh.

InnoDB: Không lưu tổng số dòng này. Do phải xử lý transaction và MVCC (đảm bảo mỗi transaction thấy dữ liệu đúng thời điểm của nó), InnoDB phải quét index hoặc bảng để đếm số dòng thực tế khi bạn chạy COUNT(*), nên chậm hơn nhiều, đặc biệt với bảng lớn.

Tôi sẽ cho anh chị nhìn thấy dữ liệu metadata lấy như thế nào mà nhanh dữ vậy chính là câu lệnh này:

SHOW TABLE STATUS LIKE 'productsB';

Xem thêm phần thực hành: Cải thiện SELECT COUNT(*) từ 4,2s xuống 0.002s với 13 TRIỆU records, DATA ĐÁNH ĐỔI gì? 3 Kịch bản

Và hãy chú ý đến cột row phải nó không? Chính nó rồi... À vậy thì trong InnoDB tôi cũng làm vậy được không? Được chứ sao không?

SHOW TABLE STATUS LIKE 'products';

Rồi nhìn vào row ủa sao nó lại khác giá trị? Phân tích ở đây lý do là cách hoạt động của InnoBD Vì InnoDB ưu tiên xử lý giao dịch (transaction), nó không lưu sẵn số dòng chính xác như MyISAM.

Nên ở đây có thể dùng số ước lượng từ thống kê, nhanh nhưng không chính xác.

Chốt lại count nhanh thì nênMyISAM, đừng xài InnoDB. Lúc này nhiều anh chị sẽ phản đối đúng không?

Ông đùa à, tự dưng vì cái count này mà đổi lại dùng MyISAM rồi mất đi transaction và MVCC vậy thì DB còn ý nghĩa gì nữa?

Đúng vậy cho nên chúng ta đi tiếp xem cải thiện hiệu suất của count() này tập trung vào InnoDB thôi đúng không? OK, tiếp xem chúng ta cải thiện được gì?

Xem thêm: Cải thiện SELECT COUNT(*) từ 4,2s xuống 0.002s với 13 TRIỆU records, DATA ĐÁNH ĐỔI gì? 3 Kịch bản

Đầu tiên phân biệt count(*), count(1), count(feild)

Tôi sẽ đi nhanh thôi để còn thời gian tối ưu hiệu suất count của thằng InnOB nữa..

CREATE TABLE users_count (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),  -- Cho phép NULL
    last_login_ip VARCHAR(45) -- Cho phép NULL
);

Và tôi sẽ insert 5 dữ liệu như sau:

INSERT INTO users_count (username, email, last_login_ip) VALUES
('Jun', 'June@example.com', '192.168.1.10'),
('J', 'j@example.com', NULL),            -- last_login_ip là NULL
('JM', NULL, '192.168.1.12'),          -- email là NULL
('tony', 'tony@example.com', '192.168.1.10'),
('goto', NULL, NULL);                        -- Cả email và last_login_ip đều NULL

Sau khi insert dữ liệu thành công thì đến lúc chúng ta sẽ đi tìm hiểu sự khác biệt cũng như hiệu suất giữa count(*), count(1)và count(email) nó như thế nào?

Đầu tiên là count(*)

SELECT COUNT(*) FROM users_count;

Anh em có thể xem tiếp tại đây: Cải thiện SELECT COUNT(*) từ 4,2s xuống 0.002s với 13 TRIỆU records, DATA ĐÁNH ĐỔI gì? 3 Kịch bản

Ngoài ra còn rất nhiều video về cải thiện hiệu suất với MYSQL anh em đón đọc...