What is the knowledge about MySQL database optimization?

On the one hand, database optimization is to identify the bottlenecks of the system and improve the overall performance of the MySQL database. On the other hand, it requires reasonable structural design and parameter adjustment to improve the user's response speed. At the same time, it is also necessary to save system resources as much as possible so that the user can The system provides greater load.

1. Optimization overview chart

2. Optimization

The author divides optimization into two categories, soft optimization and hard optimization. Soft optimization generally involves operating the database, while hard optimization involves operating the server hardware and parameter settings.

2.1 Soft optimization

2.1.1 Query statement optimization

1. First, we can use the EXPLAIN or DESCRIBE (abbreviation: DESC) command to analyze the execution information of a query statement.

2.Example:

DESC SELECT * FROM `user`

show:

Information such as the number of indexes and query data read data will be displayed.

2.1.2 Optimizing subqueries

In MySQL, try to use JOIN instead of subqueries. Because subqueries require nested queries, a temporary table will be created when nesting queries. The creation and deletion of the temporary table will have a large system overhead, while the join query will not Create a temporary table, so it is more efficient than nested subqueries.

2.1.3 Using index

Indexing is one of the most important ways to improve database query speed. Regarding indexing, you can refer to the author's article for a more detailed introduction. Here are three major precautions for using indexes: 数据库索引>

1. The LIKE keyword matches a string starting with '%' and does not use the index.

2. Both fields of the OR keyword must be indexed before this query will use the index.

3. The use of multi-column indexes must satisfy the leftmost matching.

2.1.4 Decomposition table

For tables with many fields, if some fields are used less frequently, they should be separated to form a new table.

2.1.5 Intermediate table

For tables that require a large number of connections to be queried, intermediate tables can be created to reduce the connection time consumed during queries.

2.1.6 Add redundant fields

Similar to creating intermediate tables, adding redundancy is also to reduce connection queries.

2.1.7 Analysis table, check table, optimization table

Analyzing the table mainly analyzes the distribution of keywords in the table, checking the table mainly checks whether there are errors in the table, and optimizing the table mainly eliminates the waste of table space caused by deletion or update.

1. Analyze table: Use ANALYZE keyword, such as ANALYZE TABLE user;

1. Op: Indicates the operation to be performed.

2. Msg_type: Information type, including status, info, note, warning, error.

3. Msg_text: Display information.

2. Check table: Use the CHECK keyword, such as CHECK TABLE user [option]

option is only valid for MyISAM, with a total of five parameter values:

1. QUICK: Do not scan lines, do not check for bad connections.

2. FAST: Only check tables that were not closed correctly.

3. CHANGED: Only check tables that have been changed since the last check and tables that have not been closed correctly.

4. MEDIUM: Scan lines to verify that the deleted connection is valid, and also calculate keyword checksums for each line.

5. EXTENDED: The most comprehensive inspection, comprehensive search of keywords in each line.

3. Optimize the table: use the OPTIMIZE keyword, such as OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG means not to write to the log. Optimized tables are only valid for VARCHAR, BLOB and TEXT. File fragmentation can be eliminated through the OPTIMIZE TABLE statement, and read-only locks will be added during execution.

2.2 Hard optimization

2.2.1 Three-piece hardware set

1. Configure multi-core and high-frequency CPU. Multi-core can execute multiple threads.

2. Configure large memory and increase the memory to increase the cache capacity, thus reducing disk I/O time and improving response speed.

3. Configure high-speed disks or reasonably distribute disks: high-speed disks improve I/O, and distributed disks can improve the ability of parallel operations.

2.2.2 Optimize database parameters

Optimizing database parameters can improve resource utilization and thereby improve MySQL server performance. The configuration parameters of the MySQL service are all in my.cnf or my.ini. Here are several parameters that have a greater impact on performance.

• key_buffer_size: index buffer size

• table_cache: The number of tables that can be opened simultaneously

• query_cache_size and query_cache_type: The former is the query buffer size, the latter is the switch of the previous parameter, 0 means not to use the buffer, 1 means to use the buffer, but can be used in the query SQL_NO_CACHE means not to use the buffer, 2 means in the query It is clearly pointed out that the buffer should be used only when using the buffer, that is, SQL_CACHE.

• sort_buffer_size: Sorting buffer

2.2.3 Sub-database and sub-table

Because the database is under too much pressure, the first problem is that system performance may be reduced during peak periods, because excessive database load will have an impact on performance. Another one, what should you do if your database crashes due to excessive pressure? So at this time, you must divide the system into databases and tables, and separate reading and writing, that is, splitting one database into multiple databases and deploying them on multiple database services. At this time, the database will serve as the main database to handle write requests. Then each master library mounts at least one slave library, and the slave library handles read requests.

2.2.4 Cache cluster

If the number of users increases, you can continue to add machines. For example, if you continue to add machines at the system level, you can handle higher concurrent requests. Then if the write concurrency at the database level becomes higher and higher, the database server will be expanded and the machine will be expanded through sub-database and table sharding. If the read concurrency at the database level becomes higher and higher, the capacity will be expanded and more slave databases will be added. But there is a big problem here: the database itself is not actually used to carry high concurrent requests, so generally speaking, the concurrency carried by a single database machine per second is in the order of thousands, and the machines used by the database are relatively high-configuration , relatively expensive machines, the cost is very high. If you simply keep adding machines, it is actually wrong. Therefore, cache is usually included in high-concurrency architectures. The cache system is designed to carry high concurrency. Therefore, the amount of concurrency carried by a single machine is tens of thousands, or even hundreds of thousands per second, and the carrying capacity of high concurrency is one to two orders of magnitude higher than that of a database system. Therefore, you can completely introduce a cache cluster according to the business characteristics of the system for requests that require less writing and more reading. Specifically, when writing to the database, a copy of the data is written to the cache cluster at the same time, and then the cache cluster is used to carry most of the read requests. In this case, through cache clustering, fewer machine resources can be used to host higher concurrency.

The above is the detailed content of What is the knowledge about MySQL database optimization?. For more information, please follow other related articles on the PHP Chinese website!