1. On the one hand,
database optimization is to find the bottleneck 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 corresponding speed of users, while saving system resources as much as possible. In order to allow the system to provide greater load.
2.
Optimization
The author divides optimization into two categories, soft optimization and hard optimization.Soft optimization is generally to operate the database, while hard optimization is to operate the server hardware and parameter settings.
2.1 Soft optimization
2.1.1 Query statement optimization
1. First, we can use EXPLAIN or DESCRIBE (abbreviation: DESC) command to analyze the execution information of a query statement. 2. Example:
DESC SELECT * FROM ʻuser`
Information such as the index and the number of data read for query data will be displayed.
2.1.2 Optimize subqueries
In MySQL, try to use JOIN instead of subqueries. Because subqueries require nested queries, a temporary table will be created during nested queries. The creation and deletion of temporary tables will have a large system overhead, while join queries will not Create temporary tables, so the efficiency is higher than nested subqueries.
2.1.3 Use Index
Indexes are one of the most important ways to improve database query speed. About indexes, you can refer to the article <MySQL Database Index>. The introduction is more detailed. Here are three major considerations for using indexes:
The LIKE keyword matches the string starting with'%' and will not use the index.
The two fields of the OR keyword must both use the index, and the query will use the index.
The use of a multi-column index must satisfy the leftmost match.
2.1.4 Breakdown table
For tables with many fields, if some fields are used less frequently, you should separate them to form a new table at this time.
2.1.5 Intermediate table
Intermediate tables can be created for tables that will be queried by a large number of connections, thereby reducing the time-consuming connection during query.
2.1.6 Add redundant fields
Similar to creating intermediate tables, increasing redundancy is also to reduce join queries.
2.1.7 Analysis table, check table, optimization table
The analysis table mainly analyzes the distribution of keywords in the table, the check table mainly checks whether there are errors in the table, and the optimization table mainly eliminates the waste of table space caused by deletion or update.
1. Analyze the table: use the ANALYZE keyword, such as ANALYZE TABLE user;
Op: Represents the operation performed.
Msg_type: Information type, including status, info, note, warning, error.
Msg_text: Display information.
2. Check table: Use CHECK keyword, such as CHECK TABLE user [option]
option is only valid for MyISAM, there are five parameter values:
QUICK: Do not scan rows, do not check wrong connections.
FAST: Only check tables that are not closed properly.
CHANGED: Only check tables that have been changed since the last check and tables that have not been closed properly.
MEDIUM: Scan the rows to verify that the deleted connection is valid, and you can also calculate the key checksum of each row.
EXTENDED: The most comprehensive inspection, a comprehensive search for each line of keywords.
3. Optimize the table: use the OPTIMIZE keyword, such as OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;
LOCAL|NO_WRITE_TO_BINLOG means that the log is not written., 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
1. Configure multi-core and high-frequency cpu, multi-core can execute multiple threads. 2. Configure large memory, increase the memory, you can increase the capacity of the cache area, so it can reduce the disk I/O time, thereby improving the response speed. 3 Configure high-speed disks or reasonably distribute disks: high-speed disks can 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, thereby improving MySQL server performance. The configuration parameters of the MySQL service are all in my.cnf or my.ini. Several parameters with greater performance impact are listed below.
key_buffer_size: index buffer size
table_cache: The number of tables that can be opened at the same time
query_cache_size and query_cache_type: the former is the size of the query buffer, the latter is the switch of the previous parameters, 0 means not to use the buffer, 1 means to use the buffer, but you can use SQL_NO_CACHE in the query to indicate not to use the buffer, and 2 to indicate in the query It is clearly pointed out that the buffer is only used when the buffer is used, that is, SQL_CACHE.
sort_buffer_size: sort buffer
Portal: more parameters
2.2.3 Sub-library 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 affect performance. Another one, what should you do if your database is broken due to excessive pressure? So at this time, you must split the system into database and table + read-write separation, that is, split a database into multiple databases, deploy them on multiple database services, and then serve as the main database to carry write requests. Then each master library is mounted with at least one slave library, and the slave library carries the read request.
2.2.4 Cache cluster
If the number of users is getting larger, you can keep adding machines at this time. For example, if you keep adding machines at the system level, you can carry higher concurrent requests. Then, if the write concurrency at the database level becomes higher and higher, the database server will be expanded. Through sub-database and table partitioning, the machine can be expanded. If the read concurrency at the database level becomes higher and higher, the capacity will be expanded to add more slave libraries. But there is a big problem here: the database itself is not used to carry high-concurrency requests, so generally speaking, the concurrency carried by a single database is on the order of several thousand per second, and the machines used by the database are all relatively high-configuration , More expensive machines, the cost is very high. If you simply keep adding machines, it is actually wrong. Therefore, caching is usually included in high-concurrency architectures, and the cache system is designed to carry high concurrency. Therefore, the concurrency carried by a single machine is in the tens of thousands per second, or even hundreds of thousands per second. The carrying capacity for high concurrency is one to two orders of magnitude higher than that of the database system. Therefore, you can introduce a cache cluster for requests that write less and read more based on the business characteristics of the system. Specifically, it is to write a copy of data to the cache cluster when writing the database, and then use the cache cluster to carry most of the read requests. In this way, through the cache cluster, fewer machine resources can be used to carry higher concurrency.
Conclusion
A complete and complex high-concurrency system architecture must include: various complex self-developed infrastructure systems. Various exquisite architecture designs. Therefore, a small article has the effect of at best, but the idea of database optimization is almost that.
