Mysql slow query has an impact, the reasons are: 1. There is no index or the index is not used; 2. The low IO throughput forms a bottleneck; 3. Insufficient memory; 4. The network speed is slow; 5. One query The amount of data is too large; 6. Deadlock occurs.
The operating environment of this tutorial: Windows 7 system, mysql version 8.0.22, DELL G3 computer. This method is suitable for all brands of computers.
Related free learning recommendations: mysql video tutorial
mysql slow query is influential.
Common reasons for slow queries include the following:
1. There is no index or no index is used.
PS: Indexes are used to quickly find records with specific values. All MySQL indexes are saved in the form of B-trees. If there is no index, when executing a query, MySQL must scan all records in the entire table starting from the first record until it finds a record that meets the requirements. The greater the number of records in the table, the higher the cost of this operation. If an index has been created on the column used as the search condition, MySQL can quickly get the location of the target record without scanning any records. If the table has 1000 records, finding the records through the index is at least 100 times faster than scanning the records sequentially.
Index type:
Ordinary index: This is the most basic index type, without restrictions such as uniqueness.
Unique index: basically the same as a normal index, but all index columns can only appear once to maintain uniqueness.
Primary key: The primary key is a unique index, but it must be specified as "PRIMARY KEY".
Full-text index: MYSQL supports full-text index and full-text search starting from 3.23.23. In MYSQL, the index type of full-text index is FULLTEXT. Full-text indexes can be created on VARCHAR or TEXT type columns.
#2. The low IO throughput forms a bottleneck.
PS: This is an analysis from the system layer that MYSQL consumes more IO. General database monitoring also pays more attention to IO.
Monitoring command: $iostat -d -k 1 10
The parameter -d indicates that the device (disk) usage status is displayed; -k some columns that use block units are forced to use Kilobytes. Unit; 1 10 means that the data display is refreshed every 1 second and is displayed 10 times in total.
3. Insufficient memory
Monitor memory usage: vmstat [-n] [Delay [number of times]]
Memory
swpd: Switch to the memory on the swap memory (default in KB)
• If the value of swpd is not 0, or is relatively large, such as more than 100M, but the long-term values of si and so are 0, we don’t need to worry about this situation, it will not affect system performance.
free: free physical memory
buff: memory used as buffer cache, buffering the reading and writing of block devices
cache: memory used as page cache, file system cache• If the cache value is large, it means that there are many files in the cache. If frequently accessed files can be cached, the read IO bi of the disk will be very small.
4. The network speed is slow
ping IP -t to check whether there is packet loss.
5. The amount of data in one query is too large.
For example, if there is no paging query, tens of thousands of records can be extracted at one time. The database may be stuck.
6. Deadlock occurs
The so-called deadlock: refers to a situation caused by two or more processes competing for resources during execution. The phenomenon of waiting for each other, without external force, they will not be able to advance.
Show innodb status checks the engine status, and you can see which statements cause deadlock.
Execute show processlist to find the deadlock thread number. Then Kill processNo
7. Unnecessary rows or columns are returned
General query SQL statements Be sure to specify the fields explicitly. Do not use * to query
8. Pay attention to the difference between UNion and UNion all. UNION all is good
UNION will filter out duplicate records after table linking, so after table linking, it will sort the result set generated, delete duplicate records, and then return the results. Therefore, the efficiency of union all must be high!
The above is the detailed content of Does mysql slow query have any impact?. For more information, please follow other related articles on the PHP Chinese website!