Tối ưu SQL Queries giúp website nhanh hơn
Website tải nhanh hơn đồng nghĩa với việc người dùng của bạn sẽ cảm thấy hài lòng và vui vẻ hơn, đồng thời cải thiện được google ranking cho website. Thực tế trang WordPress của bạn vẫn còn có thể nhanh hơn nữa: tối ưu performance của nó, bằng cách thiết lập server, khắc phục sự cố code chậm và sử dụng dịch vụ CDN, nhưng chắc chắn bạn sẽ vẫn quên làm một việc:
Với các website dựa trên dynamic database-driven như WordPress: truy vấn database có khả năng làm chậm trang web của bạn.
Trong bài viết dưới đây, Bizfly Cloud sẽ cùng nhau xác định các truy vấn gây nên vấn đề này, cùng với các giải pháp để khắc phục, đồng thêm tìm hiểu thêm nhiều cách giúp tăng tốc mọi thứ. Bài viết cũng sẽ sử dụng một số ví dụ cụ thể để minh họa chi tiết, giúp bạn hiểu tường tận hơn.
Xác định nguyên nhân
Bước đầu tiên ta cần làm là tìm ra vấn đề. Plugin debug Query Monitor sở hữu tính năng truy vấn database, biến nó trở thành một công cụ vô cùng hữu ích trong việc xác định các truy vấn SQL chậm. Plugin này báo cáo về tất cả các truy vấn database được thực hiện khi load trang. Nó cho phép bạn filter chúng bằng các đoạn code hoặc component (plugin, theme hoặc WordPress core) đang gọi chúng, và highlight các truy vấn bị trùng lặp và bị chậm:
Nếu bạn không muốn cài đặt debug plugin trên production (có thể bạn đang lo lắng về việc mất thêm chi phí cho performance), bạn có thể bật MySQL Slow Query Log, để log tất cả các truy vấn phải mất một khoảng thời gian nhất định để thực hiện. Khá đơn giản để cấu hình và thiết lập nơi để log các truy vấn. Vì đây là một tinh chỉnh ở server, và nó nên được tắt khi không được sử dụng.
Hiểu hơn về nguyên nhân
Một khi đã tìm thấy truy vấn cần khắc phục, bước tiếp theo bạn phải cố gắng tìm ra những yếu tố gì khiến cho truy vấn đó bị chậm. Gần đây trong quá trình phát triển website, chúng tôi đã tìm thấy một truy vấn mất khoảng 8s để thực thi!
Trong phần trình bày trên sử dụng WooCommerce và phiên bản customize của plugin WooCommerce Software Subscriptions để chạy plugin store. Mục đích của truy vấn này là lấy tất cả subscriptions cho một khách hàng, từ đó biết số khách hàng của họ. WooCommerce sở hữu mô hình dữ liệu khá phức tạp, mặc dù đơn đặt hàng được lưu trữ dưới dạng một loại custom post, id của khách hàng (đối với các cửa hàng mà mỗi khách hàng được WordPress tạo ra) không được lưu trữ như post_author, nhưng dưới dạng một phần của dữ liệu bài post. Ngoài ra còn có một số thành phần tham gia vào custom table được tạo bởi software subscriptions plugin.
MySQL chính là giải pháp
MySQL có một câu lệnh khá tiên dụng đó là DESCRIBE, có thể được sử dụng để đưa ra thông tin về cấu trúc của một bảng như các cột, kiểu dữ liệu, các giá trị mặc định. Vì vậy, nếu bạn thực thi DESCRIBE wp_postmeta, kết quả sẽ như sau:
Có thể bạn đã biết về lệnh này. Nhưng bạn có biết rằng DESCRIBE thực sự có thể được sử dụng trên các câu lệnh SELECT, INSERT, UPDATE, REPLACE và DELETE không? Bởi đồng nghĩa của nó là EXPLAIN và nó sẽ cung cấp cho chúng ta thông tin chi tiết về cách statement sẽ được execute.
Đây là kết quả cho truy vấn chậm:
Thoạt nhìn thì bảng này có thể không dễ hiểu lắm cho lắm.
Cột (column) quan trọng nhất là type, mô tả cách các bảng được kết hợp với nhau. Nếu All, có nghĩa là MySQL đang đọc toàn bộ bảng từ disk, tăng tỷ lệ I/O và đặt tải trên CPU. Điều này được biết đến như là "full table scan".
Rows (dòng): cũng là một dấu hiệu về những gì MySQL đang phải làm, nó được thể hiện bằng việc có bao nhiêu rows được tìm thấy trong kết quả của truy vấn.
Explain cung cấp thông tin để có thể sử dụng trong việc tối ưu hóa. Ví dụ, bảng pm2 (wp_postmeta) cho biết chúng ta đang sử dụng filesort, vì chúng ta đang yêu cầu các kết quả được sắp xếp với ORDER BY trên câu query. Nếu nhóm các truy vấn thì chúng ta sẽ cần bổ sung thêm chi phí cho việc thực hiện.
Điều tra trực quan
MySQL Workbench là một công cụ miễn phí, khá hữu ích cho kiểu điều tra này. Đối với các database chạy trên MySQL 5.6 trở lên, kết quả của EXPLAIN có thể được xuất ra dưới dạng JSON và MySQL Workbench, biến JSON thành một kế hoạch thực hiện trực quan của statement:
Nó sẽ tự động chỉ ra vấn đề của bạn bằng cách tô màu các phần của query. Chúng ta có thể thấy ngay rằng việc join vào bảng wp_woocommerce_software_licences (alias l) có một vấn đề nghiêm trọng.
Giải quyết
Một phần của truy vấn đang thực hiện việc full table scan, bạn nên cố gắng tránh điều này vì nó sử dụng một non-indexed column là order_id trong việc join giữa bảng wp_woocommerce_software_licences với bảng wp_posts. Đây là vấn đề thường gặp đối với các truy vấn chậm và có thể được giải quyết một cách dễ dàng.
Index trong MySQL
order_id là một phần khá quan trọng trong việc xác định dữ liệu trong bảng, nếu chúng ta đang truy vấn như thế này, chúng ta thực sự cần có index trên column, nếu không thì MySQL sẽ quét từng row của bảng cho đến khi nó tìm thấy các row cần thiết. Hãy thêm index và xem điều gì sẽ xảy ra:
Vậy là chúng ta đã giảm hơn 5s truy vấn bằng cách thêm index đó.
Tìm hiểu về Query
Kiểm tra truy vấn - join by join, subquery by subquery có làm những việc không cần thiết không? Việc tối ưu hóa có thể được thực hiện không?
Trong trường hợp này, chúng ta join bảng licenses và bảng posts sử dụng order_id, hạn chế đăng các loại shop_order. Điều này giúp toàn vẹn dữ liệu, hơn nữa giúp đảm bảo rằng chúng ta chỉ đang sử dụng các bản ghi với thứ tự chính xác, tuy nhiên, nó thực sự là một phần thừa của truy vấn. Hãy xóa và xem điều đó có cải thiện mọi thứ không:
Tuy không cải thiện được nhiều nhưng cũng giúp query thực hiện dưới 3s.
Cache
Nếu server của bạn không có MySQL query caching theo mặc định, MySQL sẽ lưu giữ một record lưu lại tất cả các câu lệnh được thực hiện cùng với kết quả, và nếu một câu lệnh giống hệt nhau được thực hiện thì các kết quả được lưu trong cache sẽ được trả về. Bộ nhớ cache sẽ không bị lỗi thời, vì MySQL xóa cache khi các bảng được thay đổi.
Query Monitor đã tìm thấy một truy vấn chạy 4 lần trên một lần tải trang của chúng tôi, mặc dù có bộ nhớ cache truy vấn MySQL, nhưng đọc trùng lặp cơ sở dữ liệu trong một yêu cầu thực sự nên tránh. Bộ nhớ đệm tĩnh trong mã PHP là một cách đơn giản và hiệu quả để giải quyết vấn đề này. Về cơ bản bạn đang tìm nạp các kết quả của một truy vấn từ cơ sở dữ liệu trong lần đầu tiên nó được yêu cầu, và lưu chúng trong thuộc tính tĩnh của một lớp, sau đó các cuộc gọi tiếp theo sẽ được trả về kết quả từ thuộc tính tĩnh:
Nếu bạn đang xem xét các kết quả của truy vấn trên các yêu cầu, thì bạn sẽ cần thực hiện một persistent Cache Object. Tuy nhiên, code của bạn sẽ cần phải có trách nhiệm setting cache và loại bỏ khả năng cache entry khi dữ liệu cơ bản thay đổi.
Mạnh dạn thử nghiệm những phương pháp khác
Có những cách tiếp cận khác mà chúng ta có thể thực hiện để thử và tăng tốc độ độ thực thi truy vấn nhiều hơn một chút so với việc chỉ chỉnh sửa truy vấn hoặc thêm chỉ mục. Một trong những phần chậm nhất của truy vấn là việc join các bảng từ customer id đến product id, và chúng ta phải thực hiện việc này cho mọi customer. Điều gì sẽ xảy ra nếu chúng ta chỉ join một lần, từ đó chúng ta có thể lấy dữ liệu của customer khi cần?
Bạn có thể chuẩn hóa dữ liệu bằng cách tạo bảng để lưu trữ "license data", cùng với user id và product id cho tất cả các license, và chỉ truy vấn đối với một customer cụ thể. Bạn sẽ cần phải xây dựng lại bảng bằng cách sử dụng các MySQL trigger trên INSERT / UPDATE / DELETE vào các license table (hoặc những bảng khác tùy thuộc vào cách dữ liệu có thể thay đổi), điều này sẽ cải thiện hiệu suất truy vấn dữ liệu đó.
Tương tự, nếu một số join trong MySQL làm chậm truy vấn của bạn, có thể ngắt truy vấn thành hai hoặc nhiều câu lệnh và thực thi chúng một cách riêng biệt trong PHP, sau đó thu thập và filter kết quả trong code. Laravel cũng làm một điều tương tự là eager loading các relation trong Eloquent.
WordPress có thể dễ bị truy vấn chậm hơn trên wp_posts table, nếu như bạn có một lượng lớn dữ liệu và nhiều loại bài đăng dạng tùy biến khác nhau. Nếu bạn đang tìm kiếm các truy vấn bị chậm đó, thì hãy xem xét di chuyển ra khỏi mô hình lưu trữ loại custom post và custom table.
Kết luận
Với các phương pháp tối ưu hóa truy vấn này, chúng ta đã làm các truy vấn giảm từ 8s xuống còn hơn 2s và giảm số lần được gọi từ 4 xuống còn 1.
Lưu ý, thời gian truy vấn được ghi lại khi chạy trên môi trường develop và sẽ nhanh hơn trên production.
Hy vọng đây là một hướng dẫn hữu ích giúp bạn theo dõi các truy vấn chậm và sửa chúng. Tối ưu hóa truy vấn có vẻ giống như một nhiệm vụ đáng sợ, nhưng ngay sau khi bạn thử và thành công, bạn sẽ bắt đầu nhận được bug và muốn cải thiện mọi thứ hơn nữa.
Theo viblo.vn
>> Có thể bạn quan tâm: Kỹ thuật tăng tốc cho website (Lazy Load, Adaptive Image, Gzip, DNS Prefetch)
