In-depth understanding of MySQL advanced drifting (6)

Performance Optimization

Index

The index points to a location in the database where the specific data is located. Colleagues create an index on the column to arrange information on this column. When the server needs to access this information for a query, it will know where to look because the index points to the relevant location.

If a column involves query, grouping, and sorting, the index will be able to achieve the effect of improving performance.
Indices with many duplicate values ​​will not produce good results.
You can use tables to join multiple non-unique indexes to improve performance.
The more indexes, the better the performance is not necessarily. Adding an index does not necessarily improve performance.

Query cache

When running a select query, MySQL4.x will record the query and return results. This is achieved by saving the result set in a special cache each time a select query is made. Then, when the server is asked to do the same query again, MySQL will retrieve the results from the cache rather than running the query again. This feature is enabled by default.

Note that once the table changes, cached queries using this table become invalid and will be deleted from the cache. This prevents queries from returning inaccurate data from the old table. Tables that change frequently will not benefit from caching. In this case, you can consider not using the cache, and you can add an option sql_no_cache to achieve this.

Query Analysis

Adding an explain keyword at the beginning of the select query will tell MySQL to return a chart indicating that if this query is processed, this chart involves which query will access Information about the table and the number of rows the query expects to return. This information can be used to see which tables can be indexed to speed up execution and analyze where bottlenecks are.

Through the displayed results of the query, you can know where to add indexes and make quick corrections.

Optimizing multi-table queries

A subquery is a select statement nested in another select statement. Subqueries are often used to break a complex query into a series of logical steps, or to use the results of other queries to answer a query. The result is that instead of executing two or more separate queries, a simple query containing one or more subqueries can be executed.

MySQL can optimize joins better than subqueries, so if you find that load averages on your MySQL server have reached unacceptably high levels, you should examine your application code and try to rewrite it as Subqueries for joins and join sequences.

You can transform inefficient subqueries into more efficient joins by effectively using MySQL's collection capabilities and modifying procedures.
If you want to avoid using nested queries in the middle, you can also use session-based server variables.

Using temporary tables

MySQL also allows the creation of temporary tables using the create temporary table command. This kind of table is so called because it only exists for a single MySQL session. When the client using these tables closes the connection to the MySQL server, it will be automatically deleted.

Because temporary tables are stored in memory, they are significantly faster than disk-based tables. Results can be effectively used as an intermediate storage area to increase the speed of query execution, help split complex ones into simpler components, or as a substitute for subquery and join support.

Optimizing table design

In order to make the query more refined, some factors in table design need to be considered. First of all, if the table that is frequently queried will undergo many changes, the way to improve performance is to use fixed-length fields instead of variable-length fields. Although using fixed-length fields will waste more disk space, from a query perspective, MySQL processes fixed-length fields faster than variable-length fields.

Another technique to improve performance is to use the optimize table command to handle tables that frequently need to be modified. Frequently modifying the table can cause disk fragmentation, causing extra time to be read from unused blocks of space in order to obtain the desired data.

When considering improving performance, also check whether you need to target all tables that have been created. Extra tables mean reduced performance. For tables that do not need to be merged, an attempt should be made to match the joined columns.

Adjust server settings

If you want the server to run more efficiently, the best solution is to increase the memory space and use a larger and faster disk. But more often than not, conditions don’t allow it. At this point, we need some general techniques for improving the server.

Adjust server variables. The key_buffer_size variable controls the amount of memory that can be used by the MySQL index buffer. The higher the value, the more memory the index can use and the better the performance. Generally, this value is kept at 25% to 30% of the total available memory. The table_cache variable controls the amount of memory that the table cache can use, and the total number of table opens that MySQL can handle at one time. For very busy servers with many databases and tables, this value should be increased and modified using set.

Once a global server variable is modified, these variables will exist until the server is shut down, but after the server is restarted, the variables will return to their default state. So it's better to make permanent changes.

The above is the detailed content of In-depth understanding of MySQL advanced drifting (6). For more information, please follow other related articles on the PHP Chinese website!