What do you know about MySQL database optimization?

Time:2021-9-23

What do you know about MySQL database optimization?

preface

On the one hand, database optimization is to find out the bottleneck of the system and improve the efficiencyMysql databaseOn the other hand, reasonable structural design and parameter adjustment are needed to improve the corresponding speed of users, and save system resources as much as possible so that the system can provide greater load

1. Optimization diagram

What do you know about MySQL database optimization?

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 analyze the execution information of a query statement with explain or describe (DESC)
  • 2. Example:

`DESC SELECT * FROM user`

Display:It will display information such as index, query data, number of read data, etc

2.1.2 optimize sub query

stayMySQLIn, try to use join instead of subquery. Because subquery needs nested query, a temporary table will be established during nested query. The establishment and deletion of temporary table will have large system overhead, while join query will not create temporary table, so the efficiency is higher than nested subquery

2.1.3 use index

Index is one of the most important methods to improve the speed of database query<Mysql database index>The introduction is more detailed. Here, three precautions for using the index are recorded:

  • Like keyword matches the string beginning with ‘%’, and the index will not be used
  • Both fields of the or keyword must be indexed before the query can use the index
  • The leftmost match must be satisfied when using multi column index

2.1.4 breakdown table

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

2.1.5 intermediate table

Intermediate tables can be created for tables that join a large number of queries, so as to reduce the join time

2.1.6 adding redundant fields is similar to creating intermediate tables. Adding redundancy is also to reduce connection queries

2.1.7 analysis table, checklist and 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. Analysis table: use the analyze keyword, such as analyze table user;What do you know about MySQL database optimization?

  • OP: indicates the operation to be performed
  • Msg_ Type: information type, including status, info, note, warning, error
  • Msg_ Text: display information
  • 2. Check table: 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 and check for incorrect connections
  • Fast: check only tables that are not closed correctly
  • Changed: only tables that have been changed since the last check and tables that have not been closed correctly are checked
  • Medium: scan lines to verify that the deleted connection is valid. You can also calculate the keyword checksum of each line
  • Extended: the most comprehensive check, a comprehensive search for each line of keywords
  • 3. Optimize 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. The optimized table is only valid for varchar, blob and text. The file fragments can be eliminated through the optimize TABLE statement, and a read-only lock will be added during execution

    2.2 hard optimization

    2.2.1 three piece hardware set

    • 1. Configure multi-core and high frequency CPU, and multi-core can execute multiple threads
    • 2. Configure large memory and increase the memory to increase the buffer capacity, so it can reduce the disk I / O time and improve the response speed
    • 3. Configure high-speed disks or reasonably distributed disks: high-speed disks can improve I / O, and distributed disks can improve the ability of parallel operation

    2.2.2 optimizing database parameters

    Optimizing database parameters can improve resource utilization and improve efficiencyMySQL server performanceThe configuration parameters of. 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: 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, and the latter is the switch of the previous parameters. 0 means not to use the buffer, and 1 means to use the buffer, but SQL can be used in the query_ NO_ Cache means not to use the buffer. 2 means to use the buffer only when it is explicitly indicated 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 reduced during peak hours, because too high database load will affect the performance. Another, what if you hang up your database under too much pressure? Therefore, at this time, you must separate the system into databases and tables + read-write separation, that is, split a database into multiple databases and deploy it on multiple database services. At this time, it is used as the main database to carry write requests. Then, each master library mounts at least one slave library, and the slave library carries the read request.What do you know about MySQL database optimization?

    2.2.4 cache cluster

    If the number of users is increasing, you can add machines continuously. For example, if you add machines continuously 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. The capacity expansion machine can be supported through database and table splitting. If the read concurrency at the database level becomes higher and higher, the capacity will be expanded and more slave databases will be added. However, 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 in the order of thousands per second, and the machines used in the database are highly configured, expensive and costly. If you simply keep adding machines, it’s actually wrong. Therefore, cache is usually used in high concurrency architecture. The design of cache system is to carry high concurrency. Therefore, the concurrency of a single machine is tens of thousands or even hundreds of thousands per second. The bearing capacity of high concurrency is one or two orders of magnitude higher than that of the database system. Therefore, according to the business characteristics of the system, you can introduce cache clusters for requests that write less and read more. Specifically, when writing the database, write a copy of data to the cache cluster at the same time, and then use the cache cluster to carry most of the read requests. In this way, through the cache cluster, you can carry higher concurrency with fewer machine resources.It is recommended to collect for future reference! MySQL common error code description

    What do you know about MySQL database optimization?

    epilogue

    A complete and complex high concurrency system architecture must include: various complex self-developed infrastructure systems. All kinds of exquisite architecture design. Therefore, a small article can attract jade at most, but the idea of database optimization is almost that

    Source:https://segmentfault.com/a/11…

    What do you know about MySQL database optimization?

    Recommended Today

    Supervisor

    Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]