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

This article shows you what MySQL optimization ideas are, the content is concise and easy to understand, absolutely can make your eyes bright, through the detailed introduction of this article, I hope you can get something.

Optimized diagram

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 server hardware and parameter settings.

1. Soft optimization

1) query statement optimization

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

Example:

DESC SELECT * FROM `user`

Display:

Information such as the index and the number of entries read from query data are displayed.

2) optimize subquery

In MySQL, try to use JOIN instead of subqueries. Because the subquery requires 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 it is more efficient than the nested subquery.

3) use index

The index is one of the most important ways to improve the query speed of the database. The three major considerations for using the index are:

The LIKE keyword matches a string that begins with'% 'and does not use an index

Both fields of the OR keyword must be indexed before the query uses an index

Using a multi-column index must satisfy the leftmost match.

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.

5) Intermediate table

For tables that will have a large number of join queries, you can create intermediate tables, thus reducing the join time caused by the query.

6) add redundant fields

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

7) Analysis table, checklist, optimization table

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

Analysis tables: use ANALYZE keywords, such as ANALYZE TABLE user

Op: indicates the action performed

Msg_type: information type, such as status, info, note, warning, error

Msg_text: displays information.

Checklist: use the CHECK keyword, such as CHECK TABLE user [option]. Option is only valid for MyISAM. There are 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.

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. The optimized table is 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. Hard optimization

1) hardware three-piece set

Configure multi-core and high-frequency cpu, multi-core can execute multiple threads

By configuring large memory and increasing memory, the cache capacity can be increased, so the disk Imax O time can be reduced, thus the response speed can be improved.

Configuration of high-speed disks or reasonable distribution of disks: high-speed disks can improve the ability of parallel operation, while distributed disks can improve the ability of parallel operation.

2) optimize 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 are some parameters that have a significant performance impact:

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.

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.

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, 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's a big problem here:

In fact, the database itself is not 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 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. The concurrency of the single machine is tens of thousands per second, 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.

According to the business characteristics of the system, you can introduce cache clusters for requests that write less and read more.

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 and a variety of exquisite architecture design, so a small article has the effect of throwing a brick to attract jade at most. But all in all, that's all for database optimization.

What are the above contents of MySQL optimization ideas? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, 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.