Let's talk in detail about the direction of slow SQL optimization in MySQL

Preface

There are many factors that affect the running speed of a system. They are multifaceted and may even be accidental, whether it is the front-end, the back-end, the database, the middleware, the server, the network, etc. To truly locate a problem, you need to have a certain understanding of the system and be able to narrow the scope of the problem based on your own judgment.

Today, I will not talk about other optimizations, but will focus on database optimization and discuss several optimization directions.

Like the optimization direction of the system, the optimization of the database is also multifaceted, covering the execution of SQL statements, the situation of the database itself, etc. Next, we will talk about the optimization direction of slow SQL statements in the MySQL database, hoping to give you some optimization ideas.

SQL statement optimization

There are many articles on SQL statement optimization, and there are also many guides on SQL writing; however, those can only support basic development. If you want to troubleshoot problems, you cannot just stay at SQL writing, but have an overall process for discovering problems.

The optimization direction of this time is roughly divided into several aspects, such as discovering slow query SQL, viewing and parsing SQL execution plan, optimizing SQL writing, and index optimization.

Record slow query SQL

Logging slow query SQL in MySQL can be achieved by using MySQL internal configuration, which is the slow_query_log configuration.

You can use show variables like '%query%'; to query the following three related results.

long_query_time | 1.00000
slow_query_log | off
slow_query_log_file | /data/mysql/mysql_slow.log

Explain these three parameters.

• long_query_time: To distinguish SQL queries from slow queries, you need to specify a query time. Queries exceeding this time are classified as slow queries. This parameter is used to set the time range; the unit is seconds, and decimals can be set.
• slow_query_log: This parameter is the switch to enable or disable the logging of slow query SQL. There are two options: on or off. The default value is off. So here we know that if we want to enable the logging of slow query SQL, we need to manually set it to enable.
• slow_query_log_file: The file path of the slow query SQL log, which can be specified by yourself.
  

How to modify the configuration

There are two methods.

First: Modify the my.ini or my.cnf file and configure these three configurations as one.

Second: Use the set syntax to modify the parameters directly in sqlplus, but it will become invalid after restarting the mysql database. The sql is as follows:

set global long_query_time = 10;

set global slow_query_log = on;

set global slow_query_log_file = /data/mysql/mysql_slow.log;

Because this method will fail after restart, it is recommended to use the first method.

View slow query logs

How to query the slow query log? If the amount is small, there is no need to use any tools and you can just open it directly.

If the amount is large, it will be more convenient to query with the mysqldumpslow tool.

mysqldumpslow is an execution script of the same type as mysqld and can be executed directly in the command line. The specific usage is as follows:

mysqldumpslow parameters:

-s, is the order
-----al Average lock time
-----ar Average return recording time
-----at average query time (default)
-----c count
-----l Lock time
-----r Return record
-----t query time

-t, top, that is, how many records are returned in front
-g, custom regular expression

For example, as follows:

mysqldumpslow -sr -t 5 /data/mysql/mysql_slow.log

Query the five slow query SQLs that return the most records.

I will build a test library and write a separate article to explain more usage later.

View SQL execution plan

View the execution plan Keywords: EXPLAIN

How to use

Just execute EXPLAIN SELECT * FROM TABLE_NAME directly;

I was planning to talk about this briefly at first, but then I found that it was too long, so I will leave it for the next article. Thank you for your understanding.

SQL Writing Optimization

There are many ways to optimize SQL writing. I have sorted out some of them here. Please check and fill in the gaps yourself.

• Regardless of the judgment condition used in the query statement, whether it is equal to, less than, or greater than, do not use functions or expressions in the condition query field on the left side of where.
• Do not use select * directly, but use the specific table fields that need to be queried; select * uses a full table scan and does not use indexes.
• Avoid NULL checks on columns in the WHERE clause.
• Avoid using the != or <> operators in WHERE.
• Use BETWEEN AND instead of IN.
• Create indexes for common search criteria
• Choose the correct storage engine, such as InnoDB, MyISAM, MEMORY, etc. Using different storage engines in different scenarios will have better results.
• Using like %123% will not go through the index, but using like 123% will go through the index. Very important! ! !
• Select the appropriate field type.
• When designing fields, try to use NOT NULL.

Why should we manage slow SQL?

From the database perspective: Each SQL execution consumes a certain amount of I/O resources. The speed of SQL execution determines the length of time the resources are occupied. Assume that the total resources are 100, and a slow SQL statement occupies 30 resources for a total of 1 minute. Then, during this one minute, the total amount of resources that can be allocated to other SQL statements is 70, and the cycle continues. When all resources are allocated, all new SQL statements will be queued.

From the application perspective: Long SQL execution time means waiting, which results in poor user experience in OLTP applications.

Governance priorities

• master database -> slave database
  • Currently, databases basically have a read-write separation architecture, where reading is performed on the slave database and writing is performed on the master database.
  • Since the data of the slave database is copied from the master database, if the master database waits for a long time, the replication delay with the slave database will increase.
• SQL statements with high execution times are managed first
• If there is a type of SQL with high concurrency and concentrated access to a certain table, it should be managed first.

Summarize

This is far from complete. There are still many writing rules, and the establishment of indexes has not been discussed yet. I leave some time for you to read the book on your own. I hope you can make progress.

This is the end of this article about the optimization direction of slow SQL in MySQL. For more relevant content on the optimization direction of MySQL slow SQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!