Database query optimization methods

Database Query Optimization

1. Using indexes

should try to avoid full table scans, first consider where and order by, Create an index on the columns involved in group
by.

2. Optimize the sql statement

• Use explain (query optimization artifact) to check the execution effect of the SQL statement,
  can help Choose better indexes and optimized query statements, and write better optimized statements.
  

• Do not use select * from table anywhere, replace "*" with a specific field list, and do not return any unused fields.
  

• Do not perform operations or use functions on index columns.

• Queries use limit as much as possible to reduce the number of returned rows, reduce data transmission time and

  bandwidth waste.
  

3. Optimize database objects

• Optimize the data type of the table

  
  Use procedure analyze( ) function analyzes the table, and this function can make optimization suggestions for the data types of the columns in the table. Use small if you can. The first principle of table data types is: use the shortest type that can correctly represent and store data. This reduces the use of disk space, memory, and cpu cache.
  

• Split the table

  * Vertical split
  Put the primary key and some columns in one table, and then put the primary key and other columns in another table middle. If some columns in a table are commonly used and others are not, you can use vertical splitting.
  * Horizontal split
  Put data rows into two independent tables based on the value of one or more columns of data.
  

• Use intermediate tables to improve query speed

  Create an intermediate table, the table structure is exactly the same as the source table structure, transfer the data to be counted to the intermediate table, and then perform operations on the intermediate table Statistics to get the desired results
  

4. Hardware optimization

• cpu optimization

  Select multi-core and a CPU with a high clock speed.
  

• Memory optimization

  Use larger memory. Allocate as much memory as possible to MYSQL for caching.
  

• Optimization of disk I/O

  * Using disk array
  * Adjusting the disk scheduling algorithm
  Choosing the appropriate disk scheduling algorithm can reduce the disk seek time
  

5. Mysql’s own optimization

The optimization of MySQL itself mainly focuses on the parameters in its configuration file my.cnf

Perform optimization adjustments. For example, specify the size of the MySQL query buffer, specify the maximum number of connection processes allowed by MySQL, etc.

6. Application optimization

Use database connection pool
Use query cache
• Its role is to store the text of the select query and its corresponding results. If the same query is received subsequently, the server will get the query results directly from the query cache. The query cache is suitable for tables that are not updated frequently. When the data in the table changes, the relevant entries in the query cache will be cleared

  
  

#

The above is the detailed content of Database query optimization methods. For more information, please follow other related articles on the PHP Chinese website!