Anh em dev khi làm việc với database tưởng chừng như chỉ gặp khó khăn với những Database có dữ liệu lớn như hàng triệu, tỷ bản ghi. Hoặc là với ứng dụng có lượng truy cập lớn và liên tục như hàng chục, trăm nghìn user online và gửi các request.
Nhưng ở giai đoạn hậu covid, kinh tế khó khăn, chúng ta phải tối ưu về mọi mặt. Việc team gặp phải vấn đề ngay cả khi lượng user ít, database không quá nặng cũng dễ bị dẹo. Việc tối ưu database càng trở nên quan trọng và cấp thiết hơn.
Sau đây mình có thể trình bày vài bước cơ bản tối ưu database mà tưởng chừng như rất cơ bản nhưng có đôi khi chúng ta quên, hoặc “cố tình” quên. Ở bài này mình sẽ nói về DB mySQL
Và làm thế nào để biết được đã tối ưu câu lệnh hay chưa, khi chúng ta sử dụng câu lệnh thì database sẽ xử lý như thế nào, hay chiến lược thực thi của database ra sao ? Chúng ta có cách kinh điển đó là sử dụng EXPLAIN
1. Explain
Sử dụng Explain sẽ giúp chúng ta thấy được hệ thống sẽ ước lượng được sẽ phải làm những gì, kế hoạch thực thi ra sao, số bản ghi sẽ tìm kiếm…..
Việc sử dụng nó rất đơn giản, chỉ cần dùng nó vào trước SELECT trong câu query.
EXPLAIN SELECT * FROM tiktok_orders WHERE creator_username='huyenhena' and order_status = 2Kết quả
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tiktok_orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 42411
filtered: 1.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Kết quả hiển thị ra nhiều thông số khác nhau. Ở đây mình sẽ tập trung mô tả 1 số field quan trọng trong kết quả đó.
- table: Tên bảng nằm trong câu truy vấn
- type: Cách thức truy vấn của câu lệnh. Đây là 1 trong những trường quan trọng nhất mà chúng ta cần để ý tới. Với kết quả ALL ở trên nó tương đương với thuật ngữ Full table scan. Tức là nó sẽ quét toàn bộ dữ liệu của bảng tiktok_orders ở trên
- possible_keys: Hiển thị những key có thể được dùng
- key: khóa thực sự được sử dụng
- rows: Số bản ghi dự kiến hệ thống sẽ quét
- Extra: thông tin bổ sung trong quá trình thực hiện truy vấn
SELECT count(*) FROM tiktok_orders;
+----------+
| count(*) |
+----------+
| 45445 |
+----------+Số lượng bản ghi bảng titkok_orders mà ta đang có 45445. Với câu truy vấn với điều kiện creator_username=’khanhvanofficial’ hệ thống đã phải quét rất nhiều bản ghi gây ra tiêu tốn hiệu năng.
Với những database chỉ có vài nghìn hay vài chục nghìn bản ghi có thể không xảy ra vấn đề gì, nhưng khi dữ liệu lên tới hàng triệu, chục triệu hoặc lớn hơn sẽ gây ra các vấn đề cực kì nghiêm trọng.
Và kỹ thuật kinh điển mà có lẽ anh em Dev thường hay sử dụng đó chính là đánh Index
2. Index
Index là cấu trúc dữ liệu giúp hệ thống xác định, định vị và truy cập nhanh chóng vào dữ liệu DB. Hiểu đơn giản sẽ giúp SQL không phải scan toàn bộ DB nữa mà sẽ scan những dữ liệu liên quan
Ở đây mình sẽ tạo index cho cột creator_username để sau đó so sánh câu lệnh có index và không có index
create index ix_tiktok_orders_creator_username on tiktok_orders(creator_username);Mình chạy lại câu lệnh Explain để xem những gì đã thay
EXPLAIN SELECT * FROM tiktok_orders WHERE creator_username='huyenhena' and order_status = 2;Kết quả:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tiktok_orders
partitions: NULL
type: ref
possible_keys: ix_tiktok_orders_creator_username
key: ix_tiktok_orders_creator_username
key_len: 1023
ref: const
rows: 323
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Sau khi có index kết quả trả về đã thay đổi khá nhiều.
- Cột type đã thay đổi từ ALL sang ref. Chứng tỏ SQL đã không quét toàn bộ nữa mà đã quét qua index mà mình vừa tạo
- possible_keys: hệ thống đã sử dụng những index nào để thực hiện truy vấn
- key: là cột hệ thống quyết định sử dụng index. Ở trường hợp này possible_keys và key có giá trị giống nhau vì chúng ta chỉ có 1 index áp dụng trong câu truy vấn
- rows: Nhìn vào cột này ta có thể thấy sự thay đổi cực lớn. Từ 42411 xuống còn 323. Rõ ràng số lượng rows hệ thống ước lượng quét đã giảm xuống rất nhiều. ~ 100 lần
- Extra: vẫn giống bên trên bởi vì ngoài điều kiện creator_username thì còn điều kiện theo order_status. Hệ thống tiếp tục tìm các bản ghi vừa tìm được có order_status = 2
Ở đây chúng ta có thể tiếp tục tối ưu thêm bằng cách đánh index thêm cột order_status. Chúng ta hay gọi với thuật ngữ Composite index hay Multicolumn indexes (không phải đánh riêng lẻ từng cột)
Mình thực hiện cú pháp tạo index cho 2 cột trên:
create index ix_tiktok_orders_creator_username_order_status on tiktok_orders(creator_username, order_status);Lúc này thực hiện lại câu lệnh trên
EXPLAIN SELECT * FROM tiktok_orders WHERE creator_username='huyenhena' and order_status = 2;*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tiktok_orders
partitions: NULL
type: ref
possible_keys: ix_tiktok_orders_creator_username,ix_tiktok_orders_creator_username_order_status
key: ix_tiktok_orders_creator_username_order_status
key_len: 1026
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)- possible_keys: hệ thống đã có 2 index để có thể sử dụng
- key: Sau khi so sánh, hệ thống đã chọn ix_tiktok_orders_creator_username_order_status để sử dụng index này
- rows: số lượng chỉ còn 1. Rõ ràng khối lượng đã giảm hàng chục nghìn lần so với chưa có index, giảm hàng trăm lần khi có 1 index.
Ở 1 trường hợp khác, nếu tôi chỉ truy vấn điều kiện có order_status = 2 thì để xem điều gì đã xảy ra.
EXPLAIN SELECT * FROM tiktok_orders WHERE order_status=2;*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tiktok_orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 42411
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)Ồ, kết quả chẳng khác gì khi chưa đánh index. Tại sao điều đó xảy ra ?
Ở index đầu tiên ix_tiktok_orders_creator_username chỉ chứa thông tin của creator_username, không có thông tin order_status.
Ở index thứ 2 ix_tiktok_orders_creator_username_order_status có thông tin của order_status nhưng tại sao SQL sử dụng được? Chúng ta cần lưu ý các index dạng Composite index hay Multicolumn indexes cần để ý thứ tự index. Cột đâu tiên xuất hiện là cực kì quan trọng.
Chúng ta cùng thử nhé. Mình sẽ tạo 1 index mới và sẽ để order_status đứng trước:
create index ix_tiktok_orders_order_status_creator_username on tiktok_orders(order_status, creator_username);Thực hiện lại truy vấn:
EXPLAIN SELECT * FROM tiktok_orders WHERE order_status=2;Kết quả
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tiktok_orders
partitions: NULL
type: ref
possible_keys: ix_tiktok_orders_order_status_creator_username
key: ix_tiktok_orders_order_status_creator_username
key_len: 3
ref: const
rows: 21205
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)Từ kết quả cho thấy hệ thống đã sử dụng index ix_tiktok_orders_order_status_creator_username và câu lệnh đã được tối ưu hơn.
Từ những ví dụ trên ta có thể thấy ích lợi của việc đánh index. Tuy nhiên chúng ta đánh index cần phải có chiến lược, cần hiểu bản chất của index để sử dụng nó dễ dàng và chuẩn xác hơn. Nếu chúng ta đánh index 1 cách bừa bãi sẽ gây ra tác dụng ngược và ảnh hưởng tới hệ thống.
Bên cạnh EXPLAIN mà còn 1 việc nữa mình hay áp dụng cho chính dự án mình đang làm đó chính là Explain analyze. Vì bản chất Explain không chạy câu lệnh thực tế, mà đó chỉ là ước lượng hệ thống sẽ thực thi. Mình dựa vào đó để tối ưu. Tuy nhiên Explain analyze sẽ đi vào thực tế câu lệnh sẽ chạy và trả ra dữ liệu thực tế cho chúng ta xem.
Cú pháp cũng rất đơn giản:
EXPLAIN analyze SELECT * FROM tiktok_orders;+——————————————————————————————————-+
| EXPLAIN |
+——————————————————————————————————-+
| -> Table scan on tiktok_orders (cost=4941 rows=42411) (actual time=0.0929..77.7 rows=45445 loops=1)
|
+——————————————————————————————————-+
Với câu lệnh trên, Hệ thống scan bảng tiktok_orders, số row là 42411 và có cost 4941. Cost càng lớn thông thường time thực tế sẽ càng lớn
Với câu lệnh truy vấn với cột có index
EXPLAIN analyze SELECT * FROM tiktok_orders WHERE creator_username='huyenhena';+—————————————————————————————————————————————————————————+
| EXPLAIN |
+—————————————————————————————————————————————————————————+
| -> Index lookup on tiktok_orders using ix_tiktok_orders_creator_username (creator_username=’huyenhena’) (cost=113 rows=323) (actual time=0.0228..1.05 rows=323 loops=1)
|
+—————————————————————————————————————————————————————————+
Chúng ta có thể thấy cost và time đã giảm rất nhiều.
Vì chủ đề này khá dài nên mình sẻ chia sẻ tiếp ở các bài chia sẻ tiếp theo.
