1. Optimization Overview
optimize
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 hardware and parameter settings of the server.
2. 1 soft optimization
2. 1. 1 query sentence optimization
1. First, we can use the Explain or DESCribe (Desc) command to analyze the execution information of the query statement.
2. Example:
Show:
It will display information such as the index and the number of data read by the query data.
2. 1.2 Optimize the subquery
In MySQL, try to use JOIN instead of subquery. Because the subquery needs to be nested, a temporary table will be created when the subquery is nested, and the establishment 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 the nested subquery.
2. 1.3 Use index
Index is one of the important methods to improve the query speed of database. For index, please refer to the author; The introduction of this article is more detailed, and three points for attention in using indexes are recorded here:
2. 1.4 decomposition table
For a table with many fields, if some fields are used less frequently, they should be separated to form a new table.
2. 1.5 intermediate table
You can create intermediate tables for tables that will query a large number of joins, thus reducing the time-consuming joins during the query.
2. 1.6 Add redundant fields
Similar to creating intermediate tables, redundancy is added to reduce join queries.
2. 1.7 analysis table, checklist and optimization table
The analysis table mainly analyzes the distribution of keywords in the table, and the check table mainly checks whether there are errors in the table. The optimization table mainly eliminates the waste of table space caused by deletion or update.
1. Analysis table: use analysis keywords, such as analysis table users;
2. Checklist: Use check keywords, such as Checklist User [Option].
Option is only valid for MyISAM, * * * five parameter values:
3. Optimize the table: use optimization keywords, such as optimizing [local | no _ write _ to _ binlog] tableuser;
LOCAL|NO_WRITE_TO_BINLOG means not to write to the log. Optimized tables are only valid for VARCHAR, BLOB and TEXT. You can eliminate file fragmentation through the OPTIMIZE TABLE statement and add a read-only lock during execution.
2.2 hard optimization
2.2. 1 hardware three-piece set
1. Configure multi-core and high-frequency cpu. Multi-core can execute multiple threads.
2. Configuring large memory and improving memory can increase the capacity of the cache area, thus reducing the disk I/O time and improving the 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 operation.
2.2.2 Optimizing database parameters
Optimizing database parameters can improve the utilization of resources, thus improving the performance of MySQL server. The configuration parameters of MySQL service are all in my.cnf or my.ini The following parameters have great influence on performance.
2.2.3 Sublibrary and Subtable
Because the database pressure is too high, the first problem is that the peak period may reduce the system performance, because the high database load will affect the performance. The other one, what if the pressure hangs up your database? So at this time, you have to divide the system into libraries and tables+read-write separation, that is, split a library into multiple libraries and deploy them on multiple database services, and then serve as the main library to carry write requests. Then each master library mounts at least one slave library, which carries a read request.
Cache cluster
If the number of users is increasing, you can continue to add machines at this time. For example, if you keep adding machines at the system level, you can host higher concurrent requests. Then, if the write concurrency at the database level is getting higher and higher, it is necessary to extend the database server and support the expander by dividing the database into tables. If the read concurrency at the database level becomes higher and higher, the extension will add more slave databases. However, there is a big problem here: the database itself is not used to carry high concurrent requests, so generally speaking, the amount of concurrency carried by a single database per second is in the order of thousands, and the machines used in the database are all relatively high-configured, expensive machines, and the cost is very high. It is actually wrong to simply add machines. Therefore, there is usually a cache link in high concurrency architecture, and the cache system is designed to carry high concurrency. Therefore, the concurrency of a single machine is tens of thousands or even hundreds of thousands per second, and the carrying capacity of high concurrency is one or two orders of magnitude higher than that of a database system. Therefore, according to the business characteristics of the system, cache clusters can be introduced for requests that write less and read more. Specifically, when writing a database, a copy of the data is written into the cache cluster at the same time, and then most of the read requests are carried by the cache cluster. In this way, by caching clusters, higher concurrency can be carried with less machine resources.
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 short article has played a role of attracting jade at most, but the idea of database optimization is coming to an end.