Introduction to MySQL database optimization (pictures and text)

The content of this article is an introduction (pictures and texts) about MySQL database optimization. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

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, and at the same time, as much as possible Save system resources so that the system can provide greater load. (Related recommendations: MySQL Tutorial)

1. Optimization Overview

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`

Display:

In which information such as the index and the number of 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 of the temporary table and Deletion will have a large system overhead, and join queries will not create temporary tables, so the efficiency is higher than nested subqueries.

2.1.3 Using indexes

Indexes improve database query speed One of the most important methods. Regarding indexes, you can refer to the author's article "MySQL Database Index". The introduction is more detailed. Three major precautions for using indexes are recorded here:

1. LIKE keyword matching Strings starting with '%' will not use indexes.
2. Both fields of the OR keyword must be indexed before the query will use indexes.
3. Use multi-column indexes Must satisfy the leftmost matching.

2.1.4 Decompose table

For tables with many fields, if some fields are used less frequently, they should be separated at this time Thus forming a new table,

2.1.5 Intermediate table

For tables that require a large number of connection queries, an intermediate table can be created, thereby reducing the time-consuming connection caused during the query.

2.1.6 Add redundant fields

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

2.1.7 Analyze tables, check tables, optimize tables

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

Analyzing the table: Use ANALYZE Key Words, such as ANALYZE TABLE user;

Op: Indicates the operation performed.
Msg_type: Information type, including status, info, note, warning, error.
Msg_text: Display information.

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:

QUICK: Do not scan rows , does not check wrong connections.

FAST: Only checks tables that have not been closed correctly.

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

MEDIUM: Scan the line to verify that the deleted connection is valid, and also calculate the keyword checksum of each line.

EXTENDED: The most comprehensive check, comprehensive keywords for each line Search.

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

LOCAL|NO_WRITE_TO_BINLOG all means not to write to the log. Optimizing the table is only for VARCHAR, BLOB and TEXT are valid. 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 Hardware three-piece set

1. Configure a 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 O time, thereby improving the response speed.

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

2.2.2 Optimization 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. The parameters that have a greater impact on performance are listed below.

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 you can use SQL_NO_CACHE in the query to mean not to use the buffer, 2 means The buffer must be used only when it is clearly stated in the query, that is, SQL_CACHE.

sort_buffer_size: sorting buffer

Portal:More parameters

2.2.3 Sub-database and sub-table

Because the database pressure is too high, the first problem is that the 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 is getting larger and larger, you can keep adding machines at this time, for example, at the system level. By adding more machines, 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.

Conclusion

A complete and complex high-concurrency system architecture will definitely include: various complex self-developed infrastructure systems. All kinds of exquisite architectural designs. Therefore, a small article can at most have the effect of inspiring others, but that's about it for database optimization ideas.

The above is the detailed content of Introduction to MySQL database optimization (pictures and text). For more information, please follow other related articles on the PHP Chinese website!