Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how MySQL database optimization is. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Preface

On the one hand, database optimization is to find out the bottleneck of the system and improve the overall performance of MySQL database, on the other hand, it needs reasonable structure design and parameter adjustment to improve the corresponding speed of users, and at the same time, it also needs to save system resources as much as possible, so that the system can provide a larger load.

1. Optimized diagram

two。 Optimize

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

2.1 soft optimization

2.1.1 query statement optimization

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

(2) example:

DESC SELECT * FROM `user`

Display:

It will display the index and query data read the number of data and other information.

2.1.2 optimize subquery

In MySQL, try to use JOIN instead of subqueries. Because the subquery needs a nested query, a temporary table will be created when the nested query is used, and the establishment and deletion of the temporary table will have greater system overhead, while the join query will not create a temporary table, so the efficiency is higher than that of the nested subquery.

2.1.3 using indexes

The index is one of the most important ways to improve the query speed of the database. The author introduces the article that the index can be improved. Here we record three points for attention when using the index:

(1) the LIKE keyword matches the string at the beginning of'% 'and does not use the index.

(2) both fields of the OR keyword must be indexed before the query uses an index.

(3) the use of multi-column indexes must satisfy the leftmost match.

2.1.4 decompose the table

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

2.1.5 Intermediate Table

For tables with a large number of join queries, intermediate tables can be created to reduce the join time caused by the query.

2.1.6 add redundant fields

Similar to creating intermediate tables, redundancy is also added to reduce join queries.

2.1.7 Analytical table, checklist, optimization table

The main purpose of the analysis table is to analyze the distribution of keywords in the table, to check whether there are errors in the table, and to optimize the table to eliminate the waste of table space caused by deletion or update.

(1) Analysis table: use ANALYZE keywords, such as ANALYZE TABLE user

Op: indicates the operation performed.

Msg_type: information type, there is status,info,note,warning,error.

Msg_text: displays information.

(2) Checklist: use CHECK keywords, such as CHECK TABLE user [option]

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

QUICK: do not scan lines, do not check for incorrect connections.

FAST: check only tables that are not properly closed.

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

MEDIUM: scan lines to verify that the deleted connection is valid, or you can calculate the keyword checksum of each line.

EXTENDED: the most comprehensive check, a comprehensive search for each line of keywords.

(3) Optimization table: use OPTIMIZE keywords, such as OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE user

LOCAL | NO_WRITE_TO_BINLOG means no log is written. Optimized tables are only valid for VARCHAR,BLOB and TEXT. File fragments can be eliminated through OPTIMIZE TABLE statements, and read-only locks will be added 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) by allocating large memory and increasing memory, the cache capacity can be increased, so the disk I / O time can be reduced and the response speed can be improved.

(3) configuration of high-speed disk or reasonable distribution of disk: high-speed disk can improve the ability of parallel operation, and distributed disk can improve the ability of parallel operation.

2.2.2 optimizing database parameters

Optimizing the database parameters can improve the resource utilization, thus improving the performance of the MySQL server. The configuration parameters of the MySQL service are all in my.cnf or my.ini. Several parameters that have a great impact on performance are listed below.

Key_buffer_size: index buffer size

Table_cache: 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, and the latter is the switch for the previous parameters. 0 means not to use the buffer, 1 means to use the buffer, but you can use SQL_NO_CACHE to indicate not to use the buffer in the query, and 2 to indicate clearly that the buffer is used only in the query, that is, SQL_CACHE.

Sort_buffer_size: sort buffer

2.2.3 Sub-database and sub-table

Because the database is under too much pressure, the first problem is that the system performance may be degraded during peak hours, because the high database load will affect the performance. On the other hand, what if you fail your database under too much pressure? So at this time, you have to do sub-library sub-table + read-write separation of the system, 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. Each master library then mounts at least one slave library, and the slave library carries the read request.

2.2.4 caching 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, you can carry higher concurrent requests. Then, if the write concurrency at the database level is getting higher and higher, the capacity of the database server will be expanded, and the machine can be expanded by dividing the database and tables. If the read concurrency at the database level is getting 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 used to carry high concurrency requests, so generally speaking, the concurrency carried by a single database machine per second is in the order of thousands of orders of magnitude, and the machines used in the database are relatively high configuration, more expensive machines, the cost is very high. If you simply add the machine all the time, it's not right. Therefore, there is usually a cache in the high concurrency architecture, and the cache system is designed to carry the high concurrency. Therefore, the concurrency of the single machine is tens of thousands per second, or even hundreds of thousands per second, and the carrying capacity for high concurrency is one or two orders of magnitude higher than that of the database system. So you can introduce a cache cluster for requests that write less and read more according to the business characteristics of the system. Specifically, when writing to the database, a piece of 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 way, by caching the cluster, you can host higher concurrency with fewer machine resources.

Conclusion

A complete and complex high concurrency system architecture must include: a variety of complex self-developed infrastructure systems. A variety of exquisite architectural designs. So a short article has the effect of throwing a brick to attract jade at most, but the idea of database optimization is almost there.

The above is the editor for you to share the MySQL database optimization is how, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.