Shulou(Shulou.com)05/31 Report--

This article is about the reasons why mysql queries are slow. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The reasons for the influence of mysql slow query are: 1, there is no index or no index is used; 2, the small throughput of IO forms a bottleneck; 3, insufficient memory; 4, the network speed is slow; 5, the amount of data in a query is too large; 6, there is a deadlock.

The common reasons for slow queries are as follows:

1. No index or no index is used.

PS: indexes are used to quickly find records with specific values, and all MySQL indexes are saved in the form of a B-tree. If there is no index, MySQL must scan all records of the entire table from the first record when executing the query until a record that meets the requirements is found. The more records there are in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly find the location of the target record without scanning any records. If the table has 1000 records, finding records by index is at least 100 times faster than scanning records sequentially.

Index type:

General index: this is the most basic index type, and there are no restrictions such as uniqueness.

Unique index: basically the same as a normal index, but all index columns can only appear once, maintaining uniqueness.

Primary key: the primary key is a unique index, but must be specified as "PRIMARY KEY".

Full-text indexing: MYSQL supports full-text indexing and full-text retrieval since 3.23.23. In MYSQL, the index type of the full-text index is FULLTEXT. A full-text index can be created on a column of type VARCHAR or TEXT.

2. The small throughput of IO forms a bottleneck.

PS: this is from the system level to analyze MYSQL is more IO-consuming. General database monitoring is also more concerned about IO.

Monitoring command: $iostat-d-k 1 10

The parameter-d indicates that the usage status of the display device (disk) is displayed;-k some columns that use block units force the use of Kilobytes units; and 1 10 means that the data display is refreshed every 1 second for a total of 10 times.

3. Insufficient memory

Monitor memory usage: vmstat [- n] [delay [times]]

Memory

Swpd: switch to memory on swap memory (default is in KB)

If the value of swpd is not 0, or it is relatively large, for example, more than 100m, but the value of si and so is 0 for a long time, we do not have to worry about this situation and will not affect the performance of the system.

Free: free physical memory

Buff: as the memory of buffer cache, it buffers the read and write of block device.

Cache: as the memory of page cache, the cache of the file system if the value of cache is large, it means that cache resides a large number of files, and if all frequently accessed files can be held by cache, then the read IO bi of the disk will be very small.

4. Slow network speed

Ping IP-t to see if there is any packet loss.

5. The amount of data in one query is too large.

For example, there is no paging query, extracting tens of thousands of records at a time. The database could be jammed.

6. Deadlock occurs

The so-called deadlock: refers to two or more processes in the implementation process, caused by the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to move forward.

Show innodb status checks the engine status and can see which statements cause deadlocks.

Execute show processlist to find the deadlock thread number. And then Kill processNo

7. Unnecessary rows or columns returned

The general query SQL statement must specify the field explicitly. Instead of using * to query

Pay attention to the difference between UNion and UNion all. Hello, UNION all.

UNION filters out duplicate records after table linking, so it sorts the resulting result set after table linking, deletes duplicate records and returns the result. So the efficiency of union all must be high!

Thank you for reading! This is the end of this article on "the reason why mysql query is slow". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.