Three methods to optimize MySQL database queries

In the optimization query, database applications (such as MySQL) mean the operation and use of the tool. Using indexes, using explain to analyze and query, and adjusting the internal configuration of MySQL can optimize the query.

Any database programmer has the following experience: a bad SQL query statement in a high-traffic database driver can seriously affect the running of the entire application, it not only consumes more database time, but also affects other application components.

Like other disciplines, optimizing query performance is largely dependent on developers' intuition. Fortunately, databases like MySQL come with some assistance tools. This article briefly discusses the three tools: Using indexes, analyzing queries using explain, and adjusting the internal configuration of MySQL.

#1:Use Index

MySQL allows you to index database tables so that you can quickly search for records without scanning the entire table at the beginning, which significantly speeds up query. Each table can have up to 16 indexes. In addition, MySQL also supports multiple column indexes and full-text searches.

It is very easy to add an index to a table. You only need to call oneCreate IndexCommand and specify its domain for the index. List A provides an example:

ListA

Mysql> Create index idx_username on users (username );
Query OK, 1 row affected (0.15 Sec)
Records: 1 duplicates: 0 Warnings: 0

HereUsersTableUsernameTo make sure thatWhereOrHavingClause that referencesSelectQuery statements run faster than when no index is added. PassShow IndexCommand to check that the index has been created (List B ).

ListB

Mysql> show index from users;
-------------- + ------------- + ----------- + ------------- + ---------- + -------- + -------------- + --------- +
| Table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment |
-------------- + ------------- + ----------- + ------------- + ---------- + -------- + -------------- + --------- +
| Users | 1 | idx_username | 1 | username | A | null | Yes | btree |
-------------- + ------------- + ----------- + ------------- + ---------- + -------- + -------------- + --------- +
1 row in SET (0.00 Sec)

It is worth noting that indexes are like a double-edged sword. Indexing each field of a table is usually unnecessary and may slow down the operation because MySQL has to re-create indexes for these additional tasks every time it inserts or modifies data in the table. On the other hand, it is not a good idea to avoid indexing each field of a table, because the query operation slows down when the record insertion speed is increased. This requires a balance. For example, when designing an index system, it is wise to consider the table's main functions (data repair and editing.

#2:Optimize Query Performance

When analyzing query performance, considerExplainThe keyword is also very useful.ExplainKeyword is generally placed inSelectBefore the query statement, used to describeMySQLHow to perform query operations andMySQLNumber of rows to be executed in the returned result set. The following simple example illustrates the process (List C:

ListC

Mysql> explain select city. Name, city. District from city, country where city. countrycode = Country. Code and country. Code = 'ind ';
+ ---- + ------------- + --------- + ------- + --------------- + --------- + ------- + ------ + ------------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+ ---- + ------------- + --------- + ------- + --------------- + --------- + ------- + ------ + ------------- +
| 1 | simple | country | const | primary | 3 | const | 1 | using index |
| 1 | simple | city | all | null | 4079 | using where |
+ ---- + ------------- + --------- + ------- + --------------- + --------- + ------- + ------ + ------------- +
2 rows in SET (0.00 Sec) the query here is based on two table connections.ExplainThe keyword describes how MySQL processes the connection between the two tables. It must be clear that the current design requires MySQL to processCountryOne record in the table andCityThe entire 4019 records in the table. This means that other optimization techniques can be used to improve the query method. For example, add the following index (List D) to the city table ):

ListD

Mysql>Create index idx_ccode on city (countrycode );
Query OK, 4079 rows affected (0.15 Sec)
Records: 4079 duplicates: 0 Warnings: 0

Now, when we re-useExplainWhen querying the keyword, we can see a significant improvement (List E ):

ListE

Mysql> explain select city. Name, city. District from city, country where city. countrycode = Country. Code and country. Code = 'ind ';
+ ---- + ------------- + --------- + ------- + --------------- + ----------- + --------- + ------- + ------ + ------------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+ ---- + ------------- + --------- + ------- + --------------- + ----------- + --------- + ------- + ------ + ------------- +
| 1 | simple | country | const | primary | 3 | const | 1 | using index |
| 1 | simple | city | ref | idx_ccode | 3 | const | 333 | using where |
+ ---- + ------------- + --------- + ------- + --------------- + ----------- + --------- + ------- + ------ + ------------- +
2 rows in SET (0.01 Sec)

In this example, MySQL only needs to scan 333 records in the city table to generate a result set, and the number of scan records is almost reduced by 90%! Naturally, database resources are faster to query and more efficient.

#3:Adjust internal variables

MySQL is so open that you can easily adjust its default settings to achieve better performance and stability. Key variables to be optimized are as follows:

• Change the index buffer Length(Key_buffer)

Generally, this variable controls the buffer length to be used when processing index tables (read/write operations. MySQL user manual points out that this variable can be continuously increased to ensure the optimal performance of the index table, and it is recommended to use 25% of the size of the system memory as the value of this variable. This is one of the most important configuration variables of MySQL. If you are interested in Optimizing and improving the system, you can changeKey_buffer_sizeVariable value.

• Change table length(Read_buffer_size)

When a query continuously scans a table, MySQL allocates a memory buffer for it.Read_buffer_sizeThe variable controls the size of the buffer. If you think continuous scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.

• Sets the maximum number of opened tables.(Table_cache)

This variable controls the maximum number of tables opened by MySQL at any time, thereby controlling the server's ability to respond to input requests. It followsMax_connectionsVariables are closely related and increaseTable_cacheValue can beMySQLOpen more tables, just like addingMax_connectionsThe value can increase the number of connections. When receiving a large number of requests from different databases and tables, consider changing the size of this value.

• Set a time limit for slow Query(Long_query_time)

MySQL has a "Slow query log", which automatically records all queries that have not been completed within a specific time range. This log is useful for tracking inefficient or misperforming queries and searching for optimization objects.Long_query_timeThe maximum time limit for Variable Control, in seconds.

The above discussion provides the usage methods of the three tools used to analyze and optimize SQL queries to improve the performance of your applications. Use them to optimize them happily!