Đã qua rồi cái thời bạn không phải lo lắng về việc tối ưu hóa hiệu suất cơ sở dữ liệu. Thời gian không đứng yên. Mọi doanh nhân công nghệ mới đều muốn tạo ra Facebook tiếp theo, đồng thời cố gắng thu thập tất cả dữ liệu mà họ có thể có được. Các doanh nghiệp cần dữ liệu này để đào tạo các mô hình giúp họ kiếm tiền tốt hơn. Trong điều kiện như vậy, các lập trình viên cần tạo các API cho phép họ làm việc nhanh chóng và đáng tin cậy với lượng thông tin khổng lồ.
Nếu bạn đã thiết kế phần phụ trợ ứng dụng hoặc cơ sở dữ liệu trong một khoảng thời gian dài, có thể bạn đã viết mã để chạy các truy vấn phân trang. Ví dụ như thế này:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Nó là như vậy?
Nhưng nếu đây là cách bạn thực hiện phân trang thì tôi rất tiếc phải nói rằng bạn đã không thực hiện nó theo cách hiệu quả nhất.
Bạn có muốn phản đối tôi không? . , и Họ đã sử dụng những kỹ thuật mà tôi muốn nói đến hôm nay.
Kể tên ít nhất một nhà phát triển phụ trợ chưa từng sử dụng OFFSET и LIMIT để thực hiện các truy vấn được phân trang. Trong MVP (Sản phẩm khả thi tối thiểu) và trong các dự án sử dụng lượng nhỏ dữ liệu, phương pháp này khá phù hợp. Có thể nói, nó “chỉ hoạt động”.
Nhưng nếu bạn cần tạo các hệ thống đáng tin cậy và hiệu quả ngay từ đầu, bạn nên quan tâm trước đến hiệu quả của việc truy vấn cơ sở dữ liệu được sử dụng trong các hệ thống đó.
Hôm nay chúng ta sẽ nói về các vấn đề với việc triển khai các công cụ truy vấn được phân trang thường được sử dụng (quá tệ) và cách đạt được hiệu suất cao khi thực hiện các truy vấn đó.
Có vấn đề gì với OFFSET và LIMIT?
Như đã nói, OFFSET и LIMIT Họ hoạt động tốt trong các dự án không cần phải làm việc với lượng lớn dữ liệu.
Vấn đề phát sinh khi cơ sở dữ liệu phát triển đến kích thước không còn vừa với bộ nhớ của máy chủ. Tuy nhiên, khi làm việc với cơ sở dữ liệu này, bạn cần sử dụng các truy vấn được phân trang.
Để vấn đề này tự bộc lộ, phải có một tình huống trong đó DBMS sử dụng thao tác Quét toàn bộ bảng không hiệu quả trên mỗi truy vấn được phân trang (trong khi các thao tác chèn và xóa có thể xảy ra và chúng tôi không cần dữ liệu lỗi thời!).
“Quét toàn bộ bảng” (hoặc “quét bảng tuần tự”, Quét tuần tự) là gì? Đây là một hoạt động trong đó DBMS đọc tuần tự từng hàng của bảng, nghĩa là dữ liệu chứa trong đó và kiểm tra xem chúng có tuân thủ một điều kiện nhất định hay không. Kiểu quét bảng này được biết là chậm nhất. Thực tế là khi nó được thực thi, nhiều thao tác đầu vào/đầu ra được thực hiện có liên quan đến hệ thống con đĩa của máy chủ. Tình hình trở nên tồi tệ hơn do độ trễ liên quan đến việc xử lý dữ liệu được lưu trữ trên đĩa và thực tế là việc truyền dữ liệu từ đĩa sang bộ nhớ là một hoạt động tiêu tốn nhiều tài nguyên.
Ví dụ: bạn có bản ghi của 100000000 người dùng và bạn chạy truy vấn với cấu trúc OFFSET 50000000. Điều này có nghĩa là DBMS sẽ phải tải tất cả các bản ghi này (và chúng tôi thậm chí không cần chúng!), đặt chúng vào bộ nhớ và sau đó lấy 20 kết quả được báo cáo trong LIMIT.
Giả sử nó có thể trông như thế này: "chọn các hàng từ 50000 đến 50020 từ 100000". Nghĩa là, trước tiên hệ thống sẽ cần tải 50000 hàng để hoàn thành truy vấn. Bạn có thấy cô ấy sẽ phải làm bao nhiêu việc không cần thiết không?
Nếu bạn không tin tôi, hãy xem ví dụ tôi đã tạo bằng các tính năng .
Ví dụ tại db-fiddle.com
Ở đó, bên trái, trên cánh đồng Schema SQL, có mã chèn 100000 hàng vào cơ sở dữ liệu và ở bên phải, trong trường Query SQL, hai truy vấn được hiển thị. Cái đầu tiên, chậm, trông như thế này:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Và cách thứ hai, là giải pháp hiệu quả cho cùng một vấn đề, là như thế này:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Để thực hiện những yêu cầu này, chỉ cần nhấp vào nút Run trên đầu của trang. Sau khi thực hiện việc này, chúng tôi so sánh thông tin về thời gian thực hiện truy vấn. Hóa ra việc thực hiện một truy vấn không hiệu quả mất nhiều thời gian hơn ít nhất 30 lần so với việc thực hiện truy vấn thứ hai (thời gian này thay đổi tùy theo lần chạy; ví dụ: hệ thống có thể báo cáo rằng truy vấn đầu tiên mất 37 mili giây để hoàn thành, nhưng việc thực thi truy vấn giây - 1 mili giây).
Và nếu có nhiều dữ liệu hơn thì mọi thứ sẽ còn tệ hơn nữa (để tin chắc vào điều này, hãy nhìn vào với 10 triệu hàng).
Những gì chúng ta vừa thảo luận sẽ cung cấp cho bạn một số thông tin chi tiết về cách các truy vấn cơ sở dữ liệu thực sự được xử lý.
Xin lưu ý rằng giá trị càng cao OFFSET — yêu cầu sẽ mất nhiều thời gian hơn để hoàn thành.
Tôi nên sử dụng cái gì thay vì kết hợp OFFSET và LIMIT?
Thay vì sự kết hợp OFFSET и LIMIT Nên sử dụng cấu trúc được xây dựng theo sơ đồ sau:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Đây là thực hiện truy vấn với phân trang dựa trên con trỏ.
Thay vì lưu trữ những cái hiện tại cục bộ OFFSET и LIMIT và truyền chúng theo từng yêu cầu, bạn cần lưu trữ khóa chính nhận được cuối cùng (thông thường đây là ID) Và LIMIT, kết quả là sẽ nhận được các truy vấn tương tự như trên.
Tại sao? Vấn đề là bằng cách chỉ định rõ ràng mã định danh của hàng cuối cùng được đọc, bạn sẽ cho DBMS biết nơi nó cần bắt đầu tìm kiếm dữ liệu cần thiết. Hơn nữa, việc tìm kiếm nhờ sử dụng khóa sẽ được thực hiện một cách hiệu quả; hệ thống sẽ không bị phân tâm bởi các dòng nằm ngoài phạm vi đã chỉ định.
Chúng ta hãy xem so sánh hiệu suất sau đây của các truy vấn khác nhau. Đây là một truy vấn không hiệu quả.
Yêu cầu chậm
Và đây là phiên bản tối ưu hóa của yêu cầu này.
Yêu cầu nhanh
Cả hai truy vấn đều trả về chính xác cùng một lượng dữ liệu. Nhưng lần đầu tiên mất 12,80 giây để hoàn thành và lần thứ hai mất 0,01 giây. Bạn có cảm thấy sự khác biệt?
Các vấn đề có thể xảy ra
Để phương thức truy vấn được đề xuất hoạt động hiệu quả, bảng phải có một cột (hoặc các cột) chứa các chỉ mục tuần tự, duy nhất, chẳng hạn như mã định danh số nguyên. Trong một số trường hợp cụ thể, điều này có thể quyết định sự thành công của việc sử dụng các truy vấn đó để tăng tốc độ làm việc với cơ sở dữ liệu.
Đương nhiên, khi xây dựng truy vấn, bạn cần tính đến kiến trúc cụ thể của các bảng và chọn những cơ chế hoạt động tốt nhất trên các bảng hiện có. Ví dụ: nếu bạn cần làm việc với các truy vấn có khối lượng lớn dữ liệu liên quan, bạn có thể thấy nó thú vị bài báo.
Nếu chúng ta gặp phải vấn đề thiếu khóa chính, chẳng hạn như nếu chúng ta có một bảng có mối quan hệ nhiều-nhiều, thì cách tiếp cận truyền thống là sử dụng OFFSET и LIMIT, được đảm bảo phù hợp với chúng tôi. Tuy nhiên, việc sử dụng nó có thể dẫn đến các truy vấn có khả năng bị chậm. Trong những trường hợp như vậy, tôi khuyên bạn nên sử dụng khóa chính tăng tự động, ngay cả khi nó chỉ cần thiết để xử lý các truy vấn được phân trang.
Nếu bạn quan tâm đến chủ đề này - , и - một số tài liệu hữu ích.
Kết quả
Kết luận chính mà chúng ta có thể rút ra là, cho dù chúng ta đang nói đến kích thước cơ sở dữ liệu nào, thì việc phân tích tốc độ thực hiện truy vấn luôn là điều cần thiết. Ngày nay, khả năng mở rộng của các giải pháp là vô cùng quan trọng và nếu mọi thứ được thiết kế chính xác ngay từ khi bắt đầu làm việc trên một hệ thống nhất định, thì điều này trong tương lai có thể cứu nhà phát triển khỏi nhiều vấn đề.
Làm thế nào để bạn phân tích và tối ưu hóa các truy vấn cơ sở dữ liệu?
Nguồn: www.habr.com