The main reasons for slowing SQL queries include lack of appropriate indexes, inefficient query statements, unreasonable table structure design and improper database configuration. 1. The lack of a suitable index will lead to full table scanning. Indexes for commonly used query fields should be established and useless indexes should be cleaned regularly; 2. The query statement is poorly written, such as SELECT *, field function use, excessive nesting, etc., should be optimized to only check the necessary fields, overwrite the function conditions to range queries, and reduce subquery nesting; 3. Table structure design problems such as improper selection of field types, large tables not split, historical data accumulation should be selected according to query requirements, horizontal/vertical splitting, and cold and hot separation archives; 4. Database configuration problems such as connection pool restrictions, insufficient cache settings, and hardware bottlenecks should be adjusted to connect and cache configuration, monitoring load, and enabling slow query log assisted troubleshooting. During the investigation, the implementation plan and monitoring tools should be gradually analyzed in combination with the execution plan and monitoring tools to avoid blindly modifying the structure or configuration.

Slowing SQL queries is a common problem, especially when the data volume increases or the system load changes. There are many reasons for slow querying, some are code-level problems, while others are related to database structure design or configuration. Here are some of the most common reasons and corresponding solutions.

1. Lack of appropriate indexes

Indexes are like directories of books. Without them, the database has to find data row by line, which is naturally inefficient.
But the more indexes, the better. Creating the wrong position will slow down the writing speed and may also make the optimizer "breathable".

Common phenomena:

• EXPLAIN display uses ALL type scan (full table scan)
• Querying is very fast when there is a small amount of data, and it will get stuck as much data.

Suggested practices:

• Add indexes to fields that often appear in WHERE , JOIN , and ORDER BY
• Pay attention to the order when using composite indexes and put fields with high distinction in front of them
• Regularly check unused indexes and clean them

2. The query statement is not efficient enough

Sometimes slowness is not because the data is large, but because the SQL written in is not smart enough. For example, check too many fields at one time, nest too deeply, use the wrong connection method, etc.

Typical problems include:

• Use SELECT * to pull unwanted data
• Doing function operations on fields in WHERE conditions, resulting in inability to go through indexes
• The subquery is too deep, affecting the execution plan
• Use of inappropriate JOIN types, resulting in a large number of temporary result sets

Optimization direction:

• Check only the required fields
• Avoid using functions on conditional columns, such as WHERE DATE(created_at) = '2024-01-01' should be rewritten into range query
• Change some subqueries JOIN or temporary tables
• Try to avoid executing SQL queries in loops

3. The table structure design is unreasonable

A poorly designed table structure makes it increasingly difficult to optimize queries. For example, the wrong field type selection, excessive redundancy or insufficient standardization, lack of partitioning strategies, etc.

Frequently designed questions:

• Fields are TEXT or JSON types, but are frequently used for searching or sorting.
• There is no reasonable split of large tables, there are too many single table fields
• The archive mechanism was not considered, and the historical data was accumulated severely

Optimization ideas:

• Select the appropriate data type according to the query requirements
• Split the big data scale horizontally or vertically
• Separate log data from hot and cold, and archive it regularly in separate tables

4. Improper database configuration or resource bottleneck

Even if SQL is written well, if the database itself is not configured properly or the server resources are tight, it will cause the query to slow down.

Possible reasons are:

• Connection limit or connection pool configuration is unreasonable
• The cache setting is too small and the hit rate is low
• Disk IO or CPU becomes the bottleneck
• Query cache is not enabled (some databases such as MySQL)

You can try:

• Check the database memory, connection pool, and cache configuration
• Monitor server load to see if there are hardware bottlenecks
• Turn on the slow query log to find SQL that has been dragging back for a long time

Basically these common reasons. SQL slowness may sometimes seem like a minor problem, but behind it may be the result of multiple factors superimposed. When troubleshooting, you can start with the execution plan and gradually position it in combination with monitoring tools. Don’t rush to change the table structure or change the configuration.

The above is the detailed content of Common reasons for slow SQL queries. For more information, please follow other related articles on the PHP Chinese website!