Shulou(Shulou.com)06/01 Report--

In this article, I'll show you how to identify queries that cause performance problems, how to find out what's wrong with them, and how to fix them quickly and other ways to speed up queries.

You must know that a quick visit to a website can make users like, can help the site from the Google ranking, can help the site to increase the conversion rate. If you have read articles on site performance optimization, such as setting the best implementation of the server, killing slow code and loading pictures using CDN, you think your WordPress site is fast enough. But is this really the case?

Using dynamic database-driven sites, such as WordPress, your site may still have a problem to solve: database queries slow down site access.

In this article, I'll show you how to identify queries that cause performance problems, how to find out what's wrong with them, and how to fix them quickly and other ways to speed up queries. I will take the situation of the portal deliciousbrains.com that slows down the query as an actual case.

Positioning

The first step in processing a slow SQL query is to find the slow query. Ashley has praised the debugging plug-in Query Monitor in previous blogs, and its database query feature makes it a valuable tool for locating slow SQL queries.

The plug-in reports database requests during all page requests, and can filter these queries by calling these query codes or originals (plug-ins, topics, WordPress cores), highlighting repeated queries and slow queries.

If you don't want to install the debug plug-in in a production security environment (due to performance overhead), you can also open MySQL Slow Query Log so that all queries executed at a specific time are recorded. This method is relatively simple to configure and set the location of the query.

Since this is a service level adjustment, the performance impact will be less than that of using debug plug-ins, but should also be turned off when not in use.

Understand

Once you find a query that will cost you a lot to find, the next step is to try to understand it and find out what slows down the query. Recently, when we were developing our website, we found a query to execute for 8 seconds.

We use WooCommerce and a custom version of the WooCommerce software plug-in to run our plug-in store. The purpose of this query is to get all subscriptions from customers whose customer numbers we know.

WooCommerce is a slightly more complex data model, and even if the order is stored in a custom type, the user's ID (the WordPress created by the store for each user) is not stored in post_author, but as part of the later data. The subscription software plug-in creates a pair of links to the custom table. Let's delve deeper into the query for more information.

Think of MySQL as a friend

MySQL has a convenient statement, DESCRIBE, which outputs information about the table structure, such as field names, data types, and so on. So, when you execute DESCRIBE wp_postmeta;, you will see the following results:

You may already know this sentence. But did you know that DESCRIBE statements can be used before SELECT, INSERT, UPDATE, REPLACE and DELETE statements? Better known is his synonym EXPLAIN and will provide detailed information about how the statement is executed.

This is the result of our inquiry:

At first glance, this is hard to explain. Fortunately, people have summarized a comprehensive guide to understanding sentences through SitePoint.

The most important field is type, which describes how a table is constructed.

If you want to see the whole thing, it means that MySQL reads the entire table from memory, increases the speed of Icano and loads it on CPU. This is called "full table browsing"-this will be described in more detail later.

The rows field is also a good indicator of what MySQL is going to have to do, and it shows how many rows are found in the result.

Explain also gives us a lot of information that can be optimized. For example, the pm2 table ((wp_postmeta) tells us that it is Using filesort because we use the ORDER BY statement to sort the results. If we want to group the query results, this will add overhead to the execution.

Visualization research

MySQL Workbench is another convenient and free tool for this type of research. Open the database with MySQL5.6 or above, and the results of EXPLAIN can be output in JSON format, while MySQL Workbench converts JSON into visual execution statements:

It automatically uses color to remind the user to pay attention to the query. We can immediately see that there is a serious problem with the table that joins wp_woocommerce_software_licences (alias l).

Solve

You should avoid (https://dev.mysql.com/doc/refman/5.7/en/table-scan-avoidance.html) this full table browsing query because it uses the non-index field order_id to join the wp_woocommerce_software_ licenses table and the wp_ posts table. This is a common problem for slow queries, and it is also a relatively easy problem to solve.

Indexes

Order_id is a very important iconic data in the table. If we want to query in this way, we need to build an index on the column. In addition, MySQL will scan every row of the table verbatim until we find the row we want. Let's add an index and see how it works:

Wow, well done! We successfully added the index and reduced the query time by 5 seconds.

Understand your query statement

Check the query statement-- look at every join, every subquery. Have they done what they shouldn't have done? What optimization can be done here?

In this example, we join the licenses table and the posts table through order_id while limiting post type to shop_order. This is to ensure that we only use the correct order records by maintaining the integrity of the data, but in fact this is redundant in the query.

We know that this is a security bet, and the software license row in the posts table is associated with WooCommerce order through order_id, which is mandatory in the PHP plug-in code. Let's remove join to see if there is any improvement:

The improvement is not great, but now the query time is less than 3 seconds.

Cache all data

If your server does not use MySQL query caching by default, then you should turn on caching.

Turning on caching means that MySQL saves all statements and the results of statement execution, and if a statement that is exactly the same as in the cache needs to be executed, MySQL returns the cached results. The cache is not obsolete because MySQL flushes the cache after the table data is updated.

The query monitor found that our query statement was executed four times when loading a page, and although it was good to have MySQL query caching, repeated reading of database data in a request should be completely avoided.

Static caching in your PHP code is simple and efficient in solving this problem. Basically, the query results are obtained from the database on the first request and stored in the static properties of the class, and then subsequent query statement calls return the results from the static properties:

Caches have a lifecycle, specifically instantiated objects have a lifecycle. If you are looking at the results of a query across requests, you need to implement a persistent object cache. However, your code should be responsible for setting up the cache and invalidating the cache when the underlying data changes.

Be considerate of others

Not only do you adjust the query or add indexes, but there are other ways to speed up the execution of the query. The slowest part of our query is from the customer ID to the product ID to adding the form, which we have to do for each customer.

Can we grab customer data when we need it? If that's the case, then we only need to join once.

You can create a data table to store license data, as well as all license user identifiers and product identifiers to de-normalize (de-normalize) the data and query specific customers.

You need to use the MySQL trigger on INSERT / UPDATE / DELETE to rebuild the table (but it depends on the table that the data changes), which can significantly improve the performance of querying data.

Similarly, if some connections slow down the query in MySQL, it may be faster to break the query into two or more statements and execute them separately in PHP, and then you can collect and filter the results in the code. Laravel does something similar in Eloquent by preloading it.

If you have a lot of data and many different custom post types, WordPress may slow down the query on the wp_ posts table. If you find that the post type of the query is slow, consider moving from the storage model of the custom post type to the custom table-more will be covered in a later article.

Conclusion

Through these query optimization methods, we try to reduce the query from 8 seconds to 2 seconds, and reduce the number of queries from 4 to 1. It is important to note that these query times are recorded when our development environment is running, and the production environment will be faster.

This is a useful guide for tracking problems such as slow queries and their fixes. Optimizing queries may seem like a scary task, but as long as you give it a try and get some initial victories, you will start to find errors and want to make further improvements.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.