Tìm hiểu về index trong MySQL thông qua ví dụ thực tế

Giới thiệu

Bài viết giới thiệu một số kiến thức cơ bản về SQL. Các ví dụ minh họa sẽ được thực hiện ở phiên bản MySQL 5.7.18, với cấu hình máy ở mức tầm trung. Thời gian thực hiện truy vấn dựa trên nhiều lần thực hiện để index caching. Use-case đến từ một ứng dụng thực tế và giải pháp được sử dụng trong product. Bình thường khi làm một ứng dụng trên local với lượng nhỏ dữ liệu, bạn luôn cảm thấy ứng dụng của bạn mượt mà, thời gian truy vấn, xử lý và phản hồi lại rất nhanh bởi dữ liệu của bạn mới là một lượng nhỏ dữ liệu test do bạn tự tạo. Nhưng bạn đã bao giờ nghĩ ứng dụng của mình được dùng bởi hàng chục hằng trăm nghìn thậm chí cả triệu người dùng chưa? Và khi đó liệu ứng dụng của mình sẽ nhanh hay chậm? Việc nhanh hay chậm của một ứng dụng web phụ thuộc vào nhiều yếu tố, tuy nhiên ở các ứng dụng sử dụng MySQL là nơi lưu trữ dữ liệu thì một phần rất lớn là ở cách lưu và cách truy vấn dữ liệu. Trường hợp số lượng bản ghi lớn có tới hàng triệu record thì có những cách nào để tăng tốc độ truy vấn? Bài viết này sẽ giới thiệu cho các bạn một cách khá hiệu quả mà nhiều người sử dụng, ở cả những hệ thống nhỏ đến những hệ thống lớn. Đó là sử dụng Index. Bài viết sẽ trình bày qua một số vấn đề sau:

  • Làm thế nào để xác định index nào nên tạo dựa trên các truy vấn bạn sử dụng?
  • Làm cách nào để kiểm tra xem truy vấn của bạn có / không sử dụng các index bạn đã đánh hay không (sử dụng EXPLAIN)
  • Làm thế nào để giúp SQL chọn các index chính xác? Điều này thực sự giúp ích cho bạn như thế nào?

Ý tưởng chung của bài viết là giúp bạn tìm và giải quyết các loại vấn đề này với các loại truy vấn khác nhau xung quanh ứng dụng của bạn. Mặc dù tôi sẽ sử dụng một trường hợp để ví dụ, nhưng giải pháp không bị giới hạn chỉ với tập dữ liệu hoặc lược đồ đã dùng mà có thể được sử dụng trên một loạt các truy vấn khác nhau.

Bài toán đặt ra

Đầu tiên hãy xem trường hợp ví dụ của tôi. Tôi có một số các service và chúng sử dụng đến các API của tôi, và mỗi lần gọi API nó sẽ ghi log vào bảng services_events. Nó là một bảng đơn giản, với service_id là khóa ngoại nối với bảng service, uuid, dueration của event, event status và thời điểm lưu vào bảng ghi dạng datetime. Bảng này sử có rất nhiều record, nó vào khoảng 781k record. Và mục tiêu là có một trang theo dõi với số lượng lỗi và số lượng event chậm. Event được coi là chậm khi thời lượng của chúng vượt quá> 1 giây. Lược đồ sẽ trông như thế này:

Bạn cũng có thể tải xuống schema + dữ liệu từ đây (bạn cần tự tạo DB, dump table và data, đã bao gồm các index được tạo trong bài viết này): Download tại đây

Như bạn thấy, tôi có 3 index, Cả 2 bảng có cột ID là khóa chính, được đánh index là mặc định và bảng service_event có cột khóa ngoại service_id cũng được đánh index. (Lưu ý ở đây: Thêm foreign key constrain không tạo ra một chỉ mục. Hầu hết các DB clients đều tự động làm như vậy nhưng nếu bạn không có các index cho các cột foreign key của mình, bạn nên tạo chúng).

Đây là dữ liệu thử nghiệm của tôi (lưu ý rằng với mục đích thử nghiệm tôi đang sử dụng một tập dữ liệu nhỏ hơn hơn thực tế khá nhiều, thời gian để chạy các truy vấn ví dụ không index sẽ tăng nhanh khi bảng service_events trở nên lớn), tôi có tổng cộng 42 services với ~ 781k events:

SELECT COUNT(*) FROM index_testing.service;
-- 42

SELECT COUNT(*) FROM index_testing.service_event;
-- 781479

Bây giờ, hãy đặt ra các truy vấn cơ bản mà tôi muốn chạy cho trang theo dõi:

-- We want to know how many error events services had during the last week
SELECT service_id, COUNT(*)
  FROM service_event
  WHERE status = 'error' AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 17 rows retrieved starting from 1 in 1s 679ms (execution: 1s 675ms, fetching: 4ms)

-- We also want to know how many "slow" events services had during the last week (duration column is in ms)
SELECT service_id, COUNT(*)
  FROM service_event
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 1s 634ms (execution: 1s 630ms, fetching: 4ms)                                                                                                                                        

Như bạn có thể thấy cả hai truy vấn này mất khoảng 1.6 giây để hoàn thành mặc dù kết quả của chúng khác nhau về điều kiện truy vấn một chút. Điều này là do MySQL đi qua cùng một lượng dữ liệu cho cả hai truy vấn này. Chúng ta có thể điều tra điều này bằng cách sử dụng EXPLAIN:

-- Let's run EXPLAIN for our queries
EXPLAIN SELECT service_id, COUNT(*)
          FROM service_event
          WHERE status = 'error' AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
EXPLAIN SELECT service_id, COUNT(*)
          FROM service_event
          WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
--
-- select_type  key                           rows    filtered  type            partitions  type    possible_keys                 key_len     ref  Extra
-- SIMPLE       service_events_service_id_fk  777896  11.11     service_event   null        index   service_events_service_id_fk  4           null Using where
-- SIMPLE       service_events_service_id_fk  777896  11.11     service_event   null        index   service_events_service_id_fk  4           null Using where

Giải quyết vấn đề

MySQL báo cáo rằng đối với cả hai truy vấn này, nó sẽ đi qua 777896 hàng và sử dụng index service_events_service_id_fk. Bây giờ, hãy nói về việc bạn sẽ bắt đầu từ đâu nếu bạn muốn đánh index (các) bảng của mình. Một số người chỉ muốn tạo index cho các cột mà họ biết thường được sử dụng trong các tìm kiếm (tên, email, v.v.). Nhưng good indexes serve the queries you run. Tất nhiên, khi tạo các schema, bạn có thể chưa biết những truy vấn nào mà bạn sẽ chạy, do đó, nó vẫn là một ý tưởng tốt để tạo các index cơ bản. Nhưng phải lưu ý rằng không đánh index tất cả các trường trong mỗi bảng, vì các index sẽ sử dụng hết bộ nhớ và tiêu tốn hiệu năng vào các truy vấn chèn / cập nhật / xóa của bạn. Vì vậy, việc tạo ra một loạt các chỉ mục mà bạn thực sự không sử dụng sẽ có hại cho hiệu suất của cơ sở dữ liệu của bạn. Thỉnh thoảng hãy ghé thăm danh sách các index trong luồng phát triển ứng dụng để bạn có thể xóa các index bạn không sử dụng hoặc thêm các index mà tốc độ truy vấn của bạn tăng tốc.

Trong ví dụ của tôi, tôi đã biết những truy vấn nào chậm, vì vậy tôi sẽ sử dụng những truy vấn này làm thông tin cơ bản để thêm index. Mệnh đề WHERE chỉ sử dụng 2 trong 3 cột tổng cộng. Đó là status, duration, và created_at. Vì vậy, với mỗi trường tôi đã sẽ thêm index cho nó và sau đó chạy lại các truy vấn của để xem đầu ra EXPLAIN và thời gian thực hiện thay đổi như thế nào:

-- Add index for each column used in the queries
CREATE INDEX service_event_status_index ON service_event (status);
CREATE INDEX service_event_duration_index ON service_event (duration);
CREATE INDEX service_event_created_at_index ON service_event (created_at);

SELECT service_id, COUNT(*)
  FROM service_event
  WHERE status = 'error' AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 17 rows retrieved starting from 1 in 163ms (execution: 111ms, fetching: 52ms)

SELECT service_id, COUNT(*)
  FROM service_event
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 1s 699ms (execution: 1s 695ms, fetching: 4ms)

-- And EXPLAIN output for our queries
-- select_type key                              rows     filtered table            partitions type   possible_keys                                                                             key_len   ref   Extra
-- SIMPLE      service_event_status_index       83474    14.6     service_event    null       ref    service_events_service_id_fk,service_event_status_index,service_event_created_at_index    2         const Using index condition; Using where; Using temporary; Using filesort
-- SIMPLE      service_event_created_at_index   113550   20.94    service_event    null       range  service_events_service_id_fk,service_event_duration_index,service_event_created_at_index  6         null  Using index condition; Using where; Using temporary; Using filesort

Có thể thấy truy vấn status tăng tốc đáng kể, khoảng 10 lần, nhưng truy vấn duration vẫn còn chậm. Điều đó cho thấy rằng thực thế nếu bạn đánh chỉ mục cho tất cả các các cột trong bảng không có nghĩa là các truy vấn của bạn với bảng đó trở nên nhanh chóng. Bạn cũng có thể thấy cho cả hai kết quả EXPLAIN mà danh sách possible_keys hiện có các khóa mới được thêm vào, điều đó thật tốt, MySQL biết các khóa ở đó và nó sử dụng chúng, nhưng liệu có phải không? Với việc query status, nó báo cáo rằng nó đã sử dụng status_index. Về cơ bản, điều đó có nghĩa là nó đã sử dụng index để lấy danh sách các hàng có status mà chúng ta yêu cầu và sau đó nó sử dụng thường xuyên để tìm ra record nào trong số các record đó đã được thêm vào trong tuần trước. Điều này cũng có nghĩa là vào thời điểm nó khớp với created_at, nó sẽ chỉ có các record có status 'error', do đó, tập dữ liệu cần trải qua nhỏ hơn đáng kể so với truy vấn ban đầu.

Truy vấn với duration không hoạt động tốt như vậy, chúng ta có thể thấy MySQL đã chọn index created_at mà chúng ta đã thêm, nhưng bản thân truy vấn vẫn mất khoảng 1.6 giây. Vì vậy, tôi đã thêm chỉ mục cho tất cả các cột được sử dụng trong mệnh đề WHERE nhưng truy vấn vẫn còn chậm, sẽ làm gì tiếp theo?

Bây giờ tôi sẽ thực hiện đánh index cho nhiều cột và tạo ra các index mà nó sẽ phục vụ trực tiếp cho các câu truy vấn của tôi. Tôi sẽ thêm hai chỉ mục mới, một cho mỗi truy vấn. Cái đầu tiên là status + created_at, cái thứ hai sẽ là duration + created_at.

CREATE INDEX service_event_status_created_at_index ON service_event (status, created_at);
CREATE INDEX service_event_duration_created_at_index ON service_event (duration, created_at);

SELECT service_id, COUNT(*)
  FROM service_event
  WHERE status = 'error' AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 17 rows retrieved starting from 1 in 28ms (execution: 24ms, fetching: 4ms)

SELECT service_id, COUNT(*)
  FROM service_event
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 1s 714ms (execution: 1s 700ms, fetching: 14ms)
   
-- EXPLAIN output                                                                                        
-- select_type  key                                    rows    filtered  table            partitions  type   possible_keys                                                                                                                     key_len  ref   Extra
-- SIMPLE       service_event_status_created_at_index  3517    100       service_event  null          range  service_events_service_id_fk,service_event_status_index,service_event_created_at_index,service_event_status_created_at_index      8        null  Using index condition; Using temporary; Using filesort
-- SIMPLE       service_event_created_at_index         113550  20.94     service_event  null          range  service_events_service_id_fk,service_event_duration_index,service_event_created_at_index,service_event_duration_created_at_index  6        null  Using index condition; Using where; Using temporary; Using filesort

Truy vấn status bây giờ là 24ms, nhanh hơn ~ 65 lần so với lúc bắt đầu. 24ms là thời gian thực hiện tôi có thể hài lòng khi có ~ 780k record. Nhưng truy vấn duration vẫn không hoạt động tốt, chúng ta có thể thấy danh sách possible_keys có tất cả các index mà chúng ta đã thêm, nhưng vì một số lý do, nó không sử dụng nó.

Đây là khi một (USE INDEX, FORCE INDEX) được sử dụng. Với chúng, chúng ta có thể giúp trình tối ưu hóa MySQL đưa ra quyết định đúng đắn trong khi chọn chỉ mục. Tuy nhiên, lưu ý rằng việc sử dụng chúng phải là giải pháp cuối cùng và chỉ được thực hiện trên các truy vấn tĩnh. Nghĩa là nếu bạn thêm chúng cho các truy vấn mà mệnh đề WHERE có thể thay đổi (về các cột được sử dụng), bạn có thể làm giảm đáng kể hiệu năng của các truy vấn vì chúng không để trình tối ưu hóa thực hiện công việc của mình. Trong trường hợp của tôi, tôi biết rằng các cột được sử dụng trong mệnh đề WHERE sẽ không thay đổi, vì vậy, hãy thử dùng index hints:

-- Regular
SELECT service_id, COUNT(*)
  FROM service_event
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 1s 714ms (execution: 1s 700ms, fetching: 14ms)

-- With USE INDEX
SELECT service_id, COUNT(*)
  FROM service_event USE INDEX (service_event_duration_created_at_index) 
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 203ms (execution: 198ms, fetching: 5ms)

-- With FORCE INDEX
SELECT service_id, COUNT(*)
  FROM service_event FORCE INDEX (service_event_duration_created_at_index) 
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 114ms (execution: 39ms, fetching: 75ms)

Như bạn có thể thấy, sử dụng index hints có thể tăng thêm tốc độ truy vấn. Sự khác biệt trong USE INDEX và FORCE INDEX đến từ (Trích dẫn từ tài liệu MySQL):

The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.

Như vậy, tôi đã điều chỉnh thời gian thực hiện xuống mức chấp nhận được cho cả hai truy vấn này chỉ bằng cách sử dụng các index.

Bonous

Bonus 1: Tôi đã nhân đôi kích thước tập dữ liệu và chạy lại tất cả các truy vấn để xem cách chúng sẽ thực hiện:

SELECT COUNT(*) FROM index_testing.service_event;
-- 1562958

-- Without any added indexes
SELECT service_id, COUNT(*)
  FROM service_event
  WHERE status = 'error' AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 17 rows retrieved starting from 1 in 52s 318ms (execution: 52s 312ms, fetching: 6ms)

SELECT service_id, COUNT(*)
  FROM service_event
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 40s 274ms (execution: 40s 269ms, fetching: 5ms)


-- With one column indexes
SELECT service_id, COUNT(*)
  FROM service_event
  WHERE status = 'error' AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
--  17 rows retrieved starting from 1 in 3s 131ms (execution: 3s 128ms, fetching: 3ms)

SELECT service_id, COUNT(*)
  FROM service_event
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 41s 571ms (execution: 41s 565ms, fetching: 6ms)

-- With compose indexes
SELECT service_id, COUNT(*)
  FROM service_event
  WHERE status = 'error' AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
--  17 rows retrieved starting from 1 in 32ms (execution: 25ms, fetching: 7ms)

SELECT service_id, COUNT(*)
  FROM service_event
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
--  31 rows retrieved starting from 1 in 39s 604ms (execution: 39s 600ms, fetching: 4ms)

-- With USE INDEX
SELECT service_id, COUNT(*)
  FROM service_event USE INDEX (service_event_duration_created_at_index) 
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 2s 220ms (execution: 2s 215ms, fetching: 5ms)

-- With FORCE INDEX
SELECT service_id, COUNT(*)
  FROM service_event FORCE INDEX (service_event_duration_created_at_index) 
  WHERE duration > 1000 AND created_at >= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service_id;
-- 31 rows retrieved starting from 1 in 107ms (execution: 102ms, fetching: 5ms)

Như bạn có thể thấy, ngay cả với 1,5 triệu hàng, tốc độ truy vấn cuối cùng vẫn rất ok. Bonus 2: Bạn rất có thể muốn xem tên service và tìm kiếm theo tên đó, điều này có thể được thực hiện dễ dàng bằng cách thêm một câu lệnh JOIN với nhiều mệnh đề ON.

SELECT
  service_id,
  service.name,
  COUNT(*)
FROM service_event
  JOIN service ON service.id = service_event.service_id AND service.name LIKE '%ID: 56%'
WHERE status = 'error' AND service_event.created_at >= DATE(NOW()) - INTERVAL 1 WEEK
GROUP BY service_id;
-- 1 row retrieved starting from 1 in 78ms (execution: 32ms, fetching: 46ms)

Mặc dù hiện tại tôi cũng lấy dữ liệu từ một bảng khác, thời gian thực hiện vẫn có thể chấp nhận được. Ví dụ này được chạy với index tổng hợp được đặt trong bảng service_event, nhưng bảng service không có index cho cột tên, do đó, việc thêm đó sẽ tăng tốc truy vấn này lên (mặc dù bảng service hiện rất nhỏ và việc thêm index vào nó sẽ có lợi ích rất nhỏ với dữ liệu này).

Kết luận

Bài viết đã đi qua:

  • Sử dụng EXPLAIN để tìm hiểu xem và những index nào mà truy vấn của bạn hiện đang sử dụng.
  • Tạo các composite index dựa trên các mệnh đề WHERE thực tế của các truy vấn của bạn.
  • Sử dụng index hints (USE INDEX, FORCE INDEX) nếu cần để giúp trình tối ưu hóa MySQL chọn index chính xác.

Về index còn vô vàn thứ cần phải tìm hiểu và thực hiện tối ưu câu truy vấn SQL còn rất nhiều vấn đề phải giải quyết, bài viết chỉ nêu những các giải quyết cơ bản nhất. Nếu có hứng thú bạn hãy dành thời gian để tìm hiểu thêm nhé. Cảm ơn các bạn đã theo dõi.

Bài viết được tham khảo bởi: Making slow queries fast using composite indexes in MySQL