What is MySQL slow query? In fact, the query SQL statement takes a long time.
How long does it take to calculate a slow query? This actually varies from person to person. Some companies have a slow query threshold of 100ms, and some may have a threshold of 500ms. That is, if the query time exceeds this threshold, it is considered a slow query. Under normal circumstances, MySQL will not automatically enable slow query, and if it is enabled, the default threshold is 10 seconds# slow_query_log 表示是否开启 mysql> show global variables like '%slow_query_log%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/0bd9099fc77f-slow.log | +---------------------+--------------------------------------+ # long_query_time 表示慢查询的阈值,默认10秒 show global variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+
1. Poor user experience.
We have to wait for a long time to access something or save something, so why don’t we give up every minute? Wait, I know the experience will be poor, but setting the slow query threshold to 100ms seems too low. It should be acceptable for me to access something for 1-2 seconds. In fact, this threshold is not too low, because it is the threshold of a SQL, and you may have to check the SQL several times for one interface, and it is very common to even adjust the external interface.2. Occupying MySQL memory and affecting performance
MySQL memory is inherently limited (large memory costs extra!). Why is SQL query slow? Sometimes it is because you scan the entire table and query a large amount of data, coupled with various filters, it becomes slow. Therefore, slow queries often mean an increase in memory usage. When the memory is high, the SQL queries that can be carried become smaller. Less, and performance deteriorates.3. Causes DDL operation blocking
As we all know, the InnoDB engine adds row locks by default, but the locks are actually added to the index. If the filter conditions are not Creating an index will downgrade to table lock. Most of the reasons for slow queries are due to the lack of indexes. Therefore, if the slow query time is too long, the table lock time will also be very long. If DDL is executed at this time, it will cause blocking.1. No index added/failed to make good use of the index
In the case ofnot adding an index, it will cause a full table scan; or The index is not reached (or the index is not the optimal index). These two situations will cause the number of scanned rows to increase, thereby slowing down the query time.
The following is an example of my test:# 这是我的表结构,算是一种比较常规的表 create table t_user_article ( id bigint unsigned auto_increment primary key, cid tinyint(2) default 0 not null comment 'id', title varchar(100) not null, author varchar(15) not null, content text not null, keywords varchar(255) not null, description varchar(255) not null, is_show tinyint(1) default 1 not null comment ' 1 0', is_delete tinyint(1) default 0 not null comment ' 1 0', is_top tinyint(1) default 0 not null comment ' 1 0', is_original tinyint(1) default 1 not null, click int(10) default 0 not null, created_at timestamp default CURRENT_TIMESTAMP not null, updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP ) collate = utf8mb4_unicode_ci;Under the above table structure, I passed
[Fill Database](https://filldb.info/) this The website randomly generated a batch of data for testing. It can be seen that without indexing, slow queries will begin after 50,000 pieces of data (assuming the threshold is 100ms)
Number of fields | Query type | Query time | |
---|---|---|---|
* | Full table (ALL) | About 80ms | |
* | Full table (ALL) | About 120ms | |
* | Full table (ALL) | About 180ms |
The above is the detailed content of This article will give you a quick understanding of slow queries in MySQL. For more information, please follow other related articles on the PHP Chinese website!