preface

There are many ways to optimize a Web project, such as properly caching the returned data at the back end, making full use of the browser’s front-end cache, CDN, and even deploying multiple machines to do clustering and load balancing. This blog will focus on optimization of slow queries in databases, a topic of more general interest for Web projects.

While it is not limited to any one technology stack, it also optimizes the performance of your project directly at the business logic level from the nature of the code, which is often a performance bottleneck under single-instance services.

One reason it’s not easy to get started is how we can target the slow queries that are really creating bottlenecks. A typical project may have dozens or even hundreds of SQL, and the “murderers” are hiding in them.

A naive idea would be to timestamp each SQL execution in a project to estimate the execution time, despite the fact that such estimates may be inaccurate due to various factors, and even more unacceptable because of the significant intrusion into the original code.

Check whether slow log is enabled

SHOW VARIABLES LIKE '%slow%';
Copy the code

Enable slow log query

Method one:

Modify the configuration file to add a few lines to my.ini: slow_query_log (slow_query_log) and slow_query_log (slow_query_log)

// Other configuration items slow_query_log=1 slow_query_log_file=/var/log/mysql/log-slow-queries.log long_query_time=2Copy the code

Method 2:

MySQL > enable slow query

Mysql > set global slow_query_log=on; Query OK, 0 rows affected (0.08 SEC) mysql> set global long_query_time=3600; Query OK, 0 rows affected (0.08 SEC) mysql> set global long_query_time=3600; Query OK, 0 rows affected (0.08 SEC) mysql> set global log_queries_not_using_indexes=on; Query OK, 0 rows affected (0.00 SEC)Copy the code

Test the SQL

You can refer to MySQL to generate millions of test data

When the SQL exceeds the threshold, a slow log is generated

Check the corresponding slow log

Analyze slow SQL using the Explain tool

For details about Explain parameters, see Explain parameters

Parameter analysis after execution

explain select * from users;
Copy the code

SQL optimization analysis

For details about SQL optimization, see MySQL SQL statement optimization

  1. select * form users;

The more data is read from the database, the slower the query becomes. Also, if your database server and WEB server are two separate servers, this can increase the load of network traffic.

Therefore, you should form a good habit of taking what you need.

The above SQL can be optimized into:

Select id,username form users; / / after optimizationCopy the code

  1. select id,username from users where username like "%test%";

Indexes do not have to be assigned to primary keys or unique fields. If you have a field in your table that you want to search frequently, index it.

For example, when you need to search for a word in a large article, such as: “WHERE post_content LIKE ‘%apple%'”, the index may be meaningless. You may need to use the MySQL full-text index or create your own index (e.g., search keywords or tags).

The above SQL is indexed:

ALTER TABLE `users` ADD INDEX index_name ( `username` );
Copy the code

After the above SQL optimization:

Like "%test%" => like "test%"; select username from users where username like "test%";; / / after optimizationCopy the code