Đã đăng vào thg 3 29, 2023 2:44 CH 12 phút đọc

1.6K

12 lý do khiến MySQL truy vấn chậm - Part 2

Mình sẽ tiếp tục bài hôm trước bắt đầu từ mục 2.7

2.7 Trình tối ưu hóa chọn sai chỉ mục

Một bảng trong MySQL có thể hỗ trợ nhiều chỉ mục(index). Khi bạn viết một câu lệnh SQL , nếu bạn không chủ động xác định chỉ mục nào sẽ sử dụng, thì chỉ mục sẽ được xác định bởi MYSQL.

Trong quá trình phát triển hàng ngày, kịch bản liên tục xóa dữ liệu lịch sử và thêm dữ liệu mới có thể khiến MySQL chọn sai chỉ mục. Vì vậy, các giải pháp là gì?

Buộc một chỉ mục được chọn bằng cách sử dụng force index
Sửa đổi SQL của bạn để hướng nó sử dụng chỉ mục mà chúng ta mong đợi
Tối ưu hóa logic business của bạn
Tối ưu hóa chỉ mục, tạo chỉ mục phù hợp hơn hoặc xóa chỉ mục bị lạm dụng.

3. Việc phân trang với offset lớn

Phân trang với offset lớn sẽ dẫn đến các truy vấn chậm, đây là điều phổ biến đối với mọi project.

3.1 Tại sao phân trang với offset lớn nó lại chậm

Giả sử chúng ta có cấu trúc bảng như sau:

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  balance int(11) DEFAULT NULL,
  create_time datetime NOT NULL,
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_name (name),
  KEY idx_create_time (create_time)
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT;

Bạn có biết quá trình thực hiện như thế nào?

select id,name,balance from account where create_time > '2023-03-15' limit 100000,10;

Luồng thực thi của câu lệnh SQL này sẽ như sau:

B1: Thông qua cây chỉ mục phụ idx_create_time, lọc ra các bản ghi có create_time map với điều kiện để tìm id của các trường dữ liệu.

B2: Thông qua id quay lại sử dụng idx_name , tìm hàng thỏa mãn rồi lấy ra cột cần hiển thị( lúc này trình thực thi sẽ quay lại đọc từ bảng ban đầu).

B3: Quét 100010 các hàng , sau đó loại bỏ 100000 các hàng trước đó và trả về.

Phân trang với offset lớn có hai lý do khiến thời gian truy vấn lâu:

Với câu lệnh limit nó sẽ quét offset+n các hàng , sau đó loại bỏ offset các hàng trước đó và trả về n các hàng . Nói cách khác limit 100000,10, sẽ quét qua 100010 các hàng , trong khi limit 0,10 sẽ chỉ quét 10 các hàng, limit 100000,10 quét nhiều hàng hơn cũng có nghĩa là số lần quay lại sẽ nhiều hơn.

3.2 Cách tối ưu hóa trong trường hợp này theo mình thì sẽ như sau:

Chúng ta có thể tối ưu hóa bằng cách giảm số lần quay lại truy vấn bảng. Nói chung sẽ có 2 phương pháp đó là: phương pháp ghi nhãn và phương pháp tương quan trễ.

Phương pháp ghi nhãn: Dó là để đánh dấu mục nào đã được truy vấn lần trước và khi bạn kiểm tra lần sau, hãy bắt đầu quét xuống từ mục này. Nó giống như đọc một cuốn sách, bạn có thể gấp nó lại hoặc đánh dấu trang ở nơi bạn nhìn thấy nó lần trước và khi đọc nó lần sau, bạn chỉ cần lật nó lại. Giả sử nó đã được ghi lại lần trước 100000, SQL có thể được sửa đổi thành:

select  id,name,balance FROM account where id > 100000 limit 10;

Trong trường hợp này, cho dù sau đó có lật bao nhiêu trang đi chăng nữa thì hiệu suất sẽ tốt, bởi vì nó là một điểm nhấn là id. Nhưng phương pháp này có hạn chế là chỉ áp dụng được cho các trường hợp như tự tăng liên tục.

Phương pháp tương quan trễ: Cách thực hiện của phương pháp này là chuyển điều kiện sang cây chỉ mục khóa chính , sau đó rút gọn nó trở lại bảng, như sau:


select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2023-03-15' limit 100000, 10) AS acct2 on acct1.id= acct2.id;

Ý tưởng tối ưu hóa là trước tiên tìm kiếm những id đáp ứng các điều kiện thông qua cây chỉ mục phụ idx_create_time , sau đó kết nối với bảng gốc thông qua id của một kết nối bên trong, để chỉ mục khóa chính được sử dụng trực tiếp sau đó và số lần quay lại bảng cũng giảm.

4. Lượng dữ liệu trong một bảng quá lớn

4.1 Tại sao nó chạy chậm khi lượng dữ liệu trong một bảng quá lớn?

Khi lượng dữ liệu trong một bảng lên tới hàng chục triệu hoặc hàng trăm triệu, tác dụng của việc sử dụng chỉ mục không quá rõ ràng. Lý do khiến hiệu suất giảm sút là mức cấu trúc của B+ Tree trở nên cao hơn, khi truy vấn một phần dữ liệu, cần phải trải qua nhiều quá trình đọc ghi đĩa, do đó hiệu suất truy vấn trở nên chậm hơn.

4.2 Một cấu trúc B+ Tree có thể lưu trữ bao nhiêu dữ liệu?

Đơn vị lưu trữ tối thiểu của InnoDB Storage Engine là trang, kích thước của một trang là 16k.

Các nút lá của B+ Tree lưu trữ dữ liệu, các nút bên trong lưu trữ khóa giá trị và con trỏ. Bảng tổ chức chỉ mục sử dụng phương pháp tìm kiếm nhị phân của các nút không phải lá và con trỏ để xác định dữ liệu nằm trong trang nào, sau đó tìm kiếm dữ liệu cần thiết trong trang dữ liệu. Giả sử rằng chiều cao của cấu trúc B+ Tree là 2 , nghĩa là có một nút gốc và một số nút lá. Tổng số bản ghi được lưu trữ trong cấu trúc B + Tree này là: số lượng con trỏ nút gốc * số lượng hàng được ghi trong một nút lá.

Nếu kích thước dữ liệu của một bản ghi là 1k, thì số lượng bản ghi có thể được lưu trữ trong một nút lá đơn là 16k/1k = 16.

Vậy có bao nhiêu con trỏ được lưu trữ trong các nút không phải là lá:

Mình giả sử rằng ID khóa chính thuộc loại bigint với độ dài 8 byte và kích thước con trỏ là 6 byte trong mã nguồn InnoDB , do đó, nó là 8+6 =14 byte, 16k/14B =16*1024B/14B = 1170

Do đó, một cấu trúc B+ Tree có chiều cao bằng 2 có thể lưu trữ các bản ghi dữ liệu như này 1170 * 16=18720. Tương tự, một cấu trúc B+ Tree có chiều cao là 3 có thể lưu trữ 1170 *1170 *16 =21902400 khoảng 20 triệu bản ghi. Chiều cao của cấu trúc B + Tree thường là 1-3 lớp, đã đáp ứng việc lưu trữ dữ liệu hàng chục triệu bản ghi.

Nếu cấu trúc B+ Tree muốn lưu trữ nhiều dữ liệu hơn, mức cấu trúc cây sẽ cao hơn, khi truy vấn một phần dữ liệu, cần phải trải qua nhiều quá trình đọc ghi đĩa, do đó hiệu suất truy vấn sẽ chậm lại.

4.3 Cách giải quyết vấn đề khối lượng dữ liệu trong một bảng quá lớn và truy vấn chậm lại

Nói chung hơn 10 triệu bản ghi, chúng ta có thể xem xét sử dụng bảng phụ hoặc cơ sở dữ liệu phụ .

Các sự cố có thể xảy ra khi sử dụng cơ sở dữ liệu phụ và bảng phụ như các vấn đề business, thư viện, vấn đề về sort, phân trang, phân phối id ...

Vì vậy, trước khi đánh giá có nên chia cơ sở dữ liệu và bảng hay không, trước tiên hãy xem xét liệu có thể lưu trữ một số dữ liệu lịch sử hay không, nếu có thể, đừng vội chia cơ sở dữ liệu và bảng . Nếu bạn thực sự muốn tách cơ sở dữ liệu và bảng, hãy xem xét và đánh giá kế hoạch một cách toàn diện. Ví dụ: bạn có thể xem xét các bảng con cơ sở dữ liệu con theo chiều dọc và chiều ngang. Thực sự đối với vấn đề này sẽ không có một giải pháp nào là chính xác chúng ta chỉ có thể chọn một giải pháp được cho là phù hợp nhất với tình hình thực tế của mỗi dự án.

5. Quá nhiều join

Nói chung, không nên sử dụng subquery, bạn có thể thay đổi subquery sang join để tối ưu hóa. Nhưng khi sử dụng join cũng cần một số lưu ý. Khi làm việc với các hệ cơ sở dữ liệu nó có một nguyên tắc là: cố gắng không có nhiều hơn 3 kết nối bảng . Tại sao như vậy? Hãy nói về join những khía cạnh có thể gây ra truy vấn chậm.

Trong MySQL, các thuật toán thực thi phép join là: Index Nested-Loop Join và Block Nested-Loop Join.

Index Nested-Loop Join: Thuật toán nối này tương tự như truy vấn lồng nhau khi chúng ta viết chương trình, và có thể sử dụng chỉ mục của bảng điều khiển.
Block Nested-Loop Join: Thuật toán này không sử dụng chỉ mục trên bảng điều khiển, thay vào đó, nó sẽ đọc dữ liệu của bảng này vào bộ đệm liên kết (join buffer), sau đó so khớp từng hàng của bảng điều khiển với dữ liệu trong join buffer để tìm ra các hàng thỏa điều kiện join và trả về kết quả.

Một mặt, quá nhiều join sẽ làm tăng độ phức tạp của SQL lên rất nhiều. Mặt khác, nếu bạn có thể sử dụng chỉ mục của bảng điều khiển thì không sao, và sử dụng một bảng nhỏ làm bảng điều khiển , hiệu quả truy vấn sẽ tốt hơn. Nếu bảng điều khiển không có chỉ mục khả dụng , việc nối được thực hiện trong bộ nhớ join_buffer và tốc độ sẽ không quá chậm nếu lượng dữ liệu khớp tương đối nhỏ hoặc cài đặt join_buffer tương đối lớn. Tuy nhiên, nếu join lượng dữ liệu tương đối lớn, mysql sẽ sử dụng phương pháp tạo bảng tạm thời trên đĩa cứng để thực hiện khớp nối liên kết của nhiều bảng, điều này rõ ràng là cực kỳ kém hiệu quả và đọc ghi của đĩa không nhanh, vì nó cần được liên kết.

Nói chung, tuỳ tình hình mỗi dự án, có thể join 2~3 bảng nhưng các trường liên kết cần được đánh index . Nếu bạn cần join nhiều bảng hơn, nên tách từ cấp mã code, trước tiên hãy truy vấn dữ liệu của một bảng trong lớp nghiệp vụ, sau đó sử dụng trường liên kết làm điều kiện để truy vấn bảng được liên kết để tạo thành, sau đó lắp ráp dữ liệu trong tầng nghiệp vụ.

Database SQL