MySQL query optimization: causes and solutions for slow queries

Friends who are doing development, especially those who have contact with MySQL, will sometimes encounter MySQL queries that are very slow. Of course, I mean large amounts of data in the millions or tens of millions, not just dozens of entries.

Let's take a look at the solution to slow query

Developers are often found checking for statements without indexes or limit n. These statements can have a significant impact on the database. For example, a large table with tens of millions of records needs to be scanned completely, or filesort is performed continuously, which affects the IO of the database and server. This is the situation on the mirror library.

When it comes to the online database, in addition to statements without indexes and statements without limit, there is another problem: too many MySQL connections. Speaking of this, let’s take a look at our previous monitoring practices:

1. Deploy open source distributed monitoring systems such as zabbix to obtain daily database io, cpu, and number of connections
2. Deploy weekly performance statistics, including data increase, iostat, vmstat, and datasize
3. Mysql slowlog collection, list top 10

I used to think that these monitorings were perfect, but now after deploying MySQL node process monitoring, I have discovered many drawbacks.

• Disadvantages of the first approach: Zabbix is ​​too large, and the monitoring is not done inside MySQL. A lot of data is not very prepared, and it is generally used to check historical data.
• Disadvantages of the second method: Because it is only run once a week, many situations cannot be discovered and reported.
• Disadvantages of the third approach: When there are a lot of slowlogs on a node, top10 becomes meaningless, and often you will be given statements for periodic tasks that must be run. . Not much reference value

So how do we solve and query these problems?

When it comes to troubleshooting and finding performance bottlenecks, the easiest problems to find and solve are slow MYSQL queries and queries without indexes.
OK, let’s start finding SQL statements that are not “pleasant” to execute in MySQL.

Method 1 : I am currently using this method. Haha, I prefer the immediacy of this method.

Mysql versions 5.0 and above can support recording SQL statements that execute slowly.

mysql> show variables like 'long%'; Note: This long_query_time is used to define how many seconds slower a query is considered a "slow query".
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> set long_query_time=1; Note: I set it to 1, which means that any query that takes more than 1 second to execute is considered a slow query.
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slow%';
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slow_launch_time | 2 |
| slow_query_log | ON | Note: whether to turn on logging | slow_query_log_file | /tmp/slow.log | Note: where to set it to+---------------------+---------------+
3 rows in set (0.00 sec)
mysql> set global slow_query_log='ON' Note: Turn on logging

Once the slow_query_log variable is set to ON, mysql starts logging immediately.
The initial values ​​of the above MYSQL global variables can be set in /etc/my.cnf .
long_query_time=1
slow_query_log_file=/tmp/slow.log

Method 2 : mysqldumpslow command

/path/mysqldumpslow -sc -t 10 /tmp/slow-log
This will output the top 10 SQL statements with the most records, where:
-s indicates the sorting method. c , t , l , and r are sorting by the number of records, time, query time, and number of records returned, respectively. ac , at , al , and ar indicate the corresponding reverse sorting.
-t means top n, which means how many records are returned.
-g , can be followed by a regular expression matching pattern, case-insensitive;
for example
/path/mysqldumpslow -sr -t 10 /tmp/slow-log
Get the top 10 queries that return the most records.
/path/mysqldumpslow -st -t 10 -g “left join” /tmp/slow-log
Get the first 10 query statements containing left joins sorted by time. Finally, let’s summarize the benefits of node monitoring

1. Lightweight monitoring, real-time, and can be customized and modified according to actual conditions
2. A filter is set up to filter the statements that must be run.
3. Timely discover those unused indexes or illegal queries. Although it takes time to process those slow statements, it is worth it to avoid database crashes.
4. When there are too many connections to the database, the program will automatically save the processlist of the current database, which is a great tool for DBA to find the cause.
5. When using mysqlbinlog for analysis, you can get a clear time period when the database status is abnormal

Some people suggest that we set up the mysql configuration file

When adjusting tmp_table_size , some other parameters were found
Qcache_queries_in_cache The number of queries registered in the cache
Qcache_inserts The number of queries added to the cache
Qcache_hits cache sample count
Qcache_lowmem_prunes The number of queries that were removed from the cache due to lack of memory
Qcache_not_cached The number of queries that were not cached (either because they could not be cached or because of QUERY_CACHE_TYPE)
Qcache_free_memory query cache total free memory
Qcache_free_blocks The number of free memory blocks in the query cache
Qcache_total_blocks queries the total number of blocks in the cache
Qcache_free_memory can cache some commonly used queries. If it is a commonly used SQL, it will be loaded into memory. That will increase the database access speed.

This concludes this article on MySQL query optimization, the causes of slow queries and solutions. For more relevant MySQL query optimization content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!