How to Optimize SQL Queries for Faster Sites

Bạn biết rằng trang web nhanh đồng nghĩa với người dùng sẽ vui vẻ hơn khi sử dụng, cải thiện được google ranking. Thậm chí bạn có thể nghĩ rằng trang WordPress của bạn có thể nhanh hơn nữa - vậy là bạn cần phải xem xét đến performance của nó, từ việc thiết lập máy chủ, khắc phục sự cố code chậm và tải hình ảnh của bạn sang CDN, nhưng đó có phải tất cả ?

Với các trang web dựa trên dynamic database-driven như WordPress, bạn vẫn có thể gặp một vấn đề: truy vấn database làm chậm trang web của bạn.

Trong bài đăng này, tôi sẽ hướng dẫn bạn cách xác định các truy vấn gây tắc nghẽn, cách hiểu các vấn đề với chúng, cùng với các bản fix lỗi nhanh và các cách tiếp cận khác để tăng tốc mọi thứ. Tôi sẽ sử dụng một truy vấn thực tế mà gần đây chúng tôi đã giải quyết, nó đã làm chậm mọi thứ trên cổng thông tin khách hàng của deliciousbrains.com.

Identification

Bước đầu tiên trong việc sửa các truy vấn chậm là tìm ra chúng. Ashley có một bài viết về plugin debug Query Monitor, và tính năng truy vấn database của plugin đã thực sự biến nó trở thành một công cụ vô cùng giá trị để 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 việc thêm một số chi phí về performance), bạn có thể chọn 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. Điều này là tương đối đơ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, performance đạt được sẽ ít hơn một debug plugin trên site, nhưng nó nên được tắt khi không được sử dụng.

Understanding

Một khi đã tìm thấy một truy vấn mà bạn muốn cải thiện, thì bước tiếp theo là bạn phải cố gắng hiểu những gì đang làm cho truy vấn đó bị chậm. Gần đây trong quá trình phát triển trang web của chúng tôi, chúng tôi đã tìm thấy một truy vấn mất khoảng 8 giây để thực thi!

SELECT 
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

Chúng tôi sử dụng WooCommerce và phiên bản customize của plugin WooCommerce Software Subscriptions để chạy plugin store của chúng tôi. Mục đích của truy vấn này là get tất cả subscriptions cho một khách hàng, từ đó chúng tôi biết số khách hàng của họ. WooCommerce có một mô hình dữ liệu hơi 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ố join vào custom table được tạo bởi software subscriptions plugin.

MySQL is your Friend

MySQL có một câu lệnh tiện lợi đó 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 execute DESCRIBE wp_postmeta bạn sẽ thấy kết quả như sau:

Có thể bạn đã biết về nó. 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? Điều này thường được biết đến bởi từ đồ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ì cái này không dễ hiểu lắm. May mắn là những người ở SitePoint đã cùng nhau đưa ra một hướng dẫn toàn diện để hiểu rõ điều đó.

Column quan trọng nhất là type, mô tả cách các bảng được join với nhau. Nếu bạn thấy ALL thì điều đó 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 cũng là một dấu hiệu tốt 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 cũng cung cấp cho chúng ta thêm thông tin để có thể sử dụng trong việc tối ưu hóa. Ví dụ, bảng pm2 (wp_postmeta), nó cho chúng ta 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 chúng ta 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.

Visual Investigation

MySQL Workbench là một công cụ miễn phí, tiện dụng 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ó tự động vẽ 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.

Solving

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 đó, 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.

Indexes

order_id là một phần khá quan trọng trong việc xác định dữ liệu trong bảng và 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 5 giây truy vấn bằng cách thêm index đó.

Know your Query

Kiểm tra truy vấn - join by join, subquery by subquery. Nó 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 ?

Trong trường hợp này, chúng ta join bảng licenses và bảng posts sử dụng order_id tất cả trong khi hạn chế tuyên bố để đăng các loại shop_order. Điều này là để thực thi toàn vẹn dữ liệu, hơn nữa là đả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. Chúng ta biết rằng đó là một cược an toàn rằng một hàng giấy phép phần mềm trong bảng có một order_id liên quan đến thứ tự WooCommerce trong bảng bài viết, vì điều này được thực thi trong mã trình cắm PHP. Hãy xóa tham gia và xem điều đó có cải thiện mọi thứ không: Tuy đây không cải thiện được nhiều nhưng cũng giúp query thực hiện dưới 3s.

Cache All The Things!

Nếu server của bạn không có MySQL query caching theo mặc định thì nó sẽ có giá trị bật. Điều này có nghĩa là 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 cũng sẽ xóa bộ nhớ cache khi các bảng được thay đổi.

Query Monitor đã tìm thấy truy vấn của chúng tôi để chạy 4 lần trên một lần tải trang và mặc dù rất tốt để có bộ nhớ cache truy vấn MySQL, nhưng lần đọc trùng lặp tới cơ sở dữ liệu trong một yêu cầu thực sự nên tránh dừng hoàn toàn. Bộ nhớ đệm tĩnh trong mã PHP của bạn 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, và sau đó các cuộc gọi tiếp theo sẽ trả về kết quả từ thuộc tính tĩnh:

class WC_Software_Subscription {

    protected static $subscriptions = array();

    public static function get_user_subscriptions( $user_id ) {
        if ( isset( static::$subscriptions[ $user_id ] ) ) {
            return static::$subscriptions[ $user_id ];
        }

        global $wpdb;

        $sql = '...';

        $results = $wpdb->get_results( $sql, ARRAY_A );

        static::$subscriptions[ $user_id ] = $results;

        return $results;
    }
}

Nếu bạn đang xem xét các kết quả của truy vấn bền bỉ trên các yêu cầu, thì bạn sẽ cần implement 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.

Thinking Outside the Box

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 join chỉ 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 làm chậm truy vấn của bạn trong MySQL, 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 có tùy chỉnh 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.

Results

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ừ 8 giây xuống còn hơn 2 giây 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.

Tôi hy vọng đây là một hướng dẫn hữu ích để 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à có một số 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.

References

https://deliciousbrains.com/sql-query-optimization/