Simple principles of MySQL Optimization

In the optimization query, database applications (suchMySQL) Means the operation and use of the tool. Use index and usageExplainAnalysis query and adjustmentMySQLTo 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:

List A

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 ).

List B

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:

List C

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 ):

List D

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 ):

List E

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!

 

 

Explain shows how MySQL uses indexes to process select statements and connect tables. It can help you select better indexes and write more optimized query statements. You can use the explain statement before the SELECT statement, for example, explain select surname, first_name Form A, B where. id = B. the ID analysis result is as follows: descriwhereusing temporaryusing filesort brefidfirst_nameid4id2using where explain column explanation: the table displays the data of this row, which table type is an important column, shows the connection type. The best to worst connection types are const, eq_reg, ref, range, indexhe, and allpossible_keys. The indexes that may be applied to this table are displayed. If it is null, there is no possible index. You can select an index suitable for the statement key from the where statement for the related domain. If it is null, no index is used. In rare cases, MySQL selects an optimized index. In this case, you can use index (indexname) in the SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the index length used by index key_len. Without compromising accuracy, the shorter the length, the better. Ref indicates which column of the index is used. If possible, it is a constant that rows MySQL considers to be required to check the number of rows used to return request data extra information about how MySQL parses the query. We will discuss it in table 4.3, but here we can see that the bad examples are using temporary and using filesort, which means MySQL cannot use indexes at all, the result is that the search will be slow. The description returned by the extra column indicates the meaning of distinct. Once MySQL finds the row that matches with the row, it no longer searches for not exists MySQL to optimize left join, once it finds a row that matches the left join standard, it no longer searches for range checked for eachrecord (index map: #) and does not find the ideal index, therefore, for each row combination in the preceding table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using indexes. When you see this in using filesort, the query needs to be optimized. MySQL requires additional steps to find out how to sort the returned rows. It sorts data in the using index column of all rows based on the connection type and the row pointer of all rows that store the sort key value and match the condition, instead of actually reading the information in the index., this occurs when all the request columns of the table are the same index, and using temporary sees this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results. This usually happens when order by is performed on different column sets, instead of using the WHERE clause on group by, where used uses the WHERE clause to restrict which rows match the next table or which rows are returned to the user. If you do not want to return all rows in the table and the connection type is all or index, this will happen, or if there is a problem with the query interpretation of different connection types (sort by efficiency order) the system table has only one row: system table. This is a special case of the const connection type. The maximum value of a record in the const table can match this query (the index can be a primary key or a unique index ). Because there is only one row, this value is actually a constant, because MySQL first reads this value and treats it as a constant to treat eq_ref in the connection. when MySQL queries, from the previous table, the Union of each record reads a record from the table, it uses ref when the query uses the index as the primary key or unique key. This connection type is only used when the query uses a key that is not the only or primary key or a part of these types (for example, occurs with the leftmost prefix. For each row union in the previous table, all records are read from the table. This type depends heavily on the number of records matched by the index-the smaller the better the range. This connection type uses the index to return rows in a range, for example, if you use> or <when something is found, the index connection type performs a full scan of each record in the preceding table (better than all, because the index is generally smaller than the table data) the all connection type performs a full scan for each of the preceding records. This is generally poor and should be avoided as much as possible.