Optimization map

To optimize the

The author divides optimization into two categories: soft optimization and hard optimization. Soft optimization is generally the operation of the database; Hard tuning involves manipulating server hardware and setting parameters.

1. Soft optimization

1) Query statement optimization

First we can analyze the execution information of a query using EXPLAIN or DESCRIBE(abbreviated :DESC).

Ex. :

	DESC SELECT * FROM `user`
Copy the code

Display:

Information such as index and number of data read from query data is displayed.

2) Optimize subqueries

In MySQL, try to use joins instead of subqueries. Because subqueries need nested queries, nested queries will create a temporary table, the establishment and deletion of temporary tables will have a large system overhead, and join queries will not create temporary tables, so the efficiency is higher than nested subqueries.

3) Use indexes

Indexes are one of the most important ways to speed up database queries. There are three important considerations for using indexes:

  • The LIKE keyword matches strings starting with ‘%’ and does not use indexes;
  • Both fields of the OR keyword must be indexed for the query to use the index;
  • To use a multi-column index, the leftmost match must be met.

4) Resolution table

For a table with many fields, if some fields are used infrequently, they should be separated to form a new table.

5) Intermediate table

Intermediate tables can be created for tables that will be queried in large numbers to reduce join time during queries.

6) Add redundant fields

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

7) Analysis table, check table and optimization table

Analysis table mainly analyzes the distribution of keywords in the table; Check table is mainly to check whether there are errors in the table; Tuning a table is about eliminating tablespace waste caused by deletes or updates.

ANALYZE tables: Use the ANALYZE keyword, such as ANALYZE TABLE user

  • Op: indicates the operation performed.
  • Msg_type: information type, including status, info, note, warning, and error.
  • Msg_text: displays information.

CHECK TABLE: Use the CHECK keyword, such as CHECK TABLE user [option]. Option is valid only for MyISAM. There are five parameter values:

  • QUICK: do not scan lines and check for incorrect connections.
  • FAST: checks only tables that are not closed properly;
  • CHANGED: checks only tables that have been CHANGED since the last check and tables that have not been closed properly;
  • MEDIUM: scans lines to verify that the deleted connection is valid, or calculates a one-line keyword checksum.
  • EXTENDED: The most comprehensive check, a comprehensive search for each line of keywords.

TABLE: OPTIMIZE using keywords, such as OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE the user;

LOCAL | NO_WRITE_TO_BINLOG is said not to a log, OPTIMIZE TABLE applies only to a VARCHAR, BLOB and TEXT, through OPTIMIZE TABLE statement can eliminate file fragments, in the process of execution will add a read-only lock.

2. Hard optimization

1) Three pieces of hardware

  • Configure multi-core and high frequency CPU, multi-core can execute multiple threads;
  • By configuring a large memory and increasing the memory, you can increase the cache capacity, reduce disk I/O time, and improve the response speed.
  • Configure high-speed disks or properly distributed disks: High-speed disks improve I/O, and distributed disks improve parallel operations.

2) Optimize database parameters

Optimizing database parameters can improve resource utilization and thus improve MySQL server performance. MySQL service configuration parameters are listed in my.cnf or my.ini.

  • Key_buffer_size: index buffer size;
  • Table_cache: number of open tables;
  • Query_cache_size and query_cache_type: The former is the size of the query buffer, the latter is the switch of the preceding parameters, 0 means no buffer, 1 means use buffer, but can be used in the query SQL_NO_CACHE means do not use buffer, 2 means use buffer in the query, namely SQL_CACHE;
  • Sort_buffer_size: sort buffer.

3) Separate databases and tables

The first problem is that system performance may deteriorate at peak times because of high database load.

On the other hand, what if stress crashes your database?

In this case, you have to split the system into multiple libraries, deployed on multiple database services, and then serve as the master library to handle the write requests. Each master library then mounts at least one slave library, which hosts the read requests.

4) Cache cluster

If you have more and more users, then you can keep adding machines, say at the system level, and you can handle more concurrent requests.

Then, if the write concurrency at the database level becomes higher and higher, expand the database server, which can support the expansion of the machine through the partition table. If the read concurrency at the database level becomes higher and higher, expand and add more slave libraries.

But there’s a big problem:

In fact, the database itself is not used to bear high concurrency requests, so generally speaking, database single machine per second bearing concurrency is in thousands of orders of magnitude, and the database using machines are relatively high configuration, relatively expensive machines, high cost.

If you simply keep adding machines, that’s not true.

Caching is a common part of high concurrency architectures, and caching systems are designed to accommodate high concurrency. The concurrency of a single machine is in tens of thousands per second, or even hundreds of thousands per second. The carrying capacity of high concurrency is one or two orders of magnitude higher than that of database system.

Depending on the business nature of the system, you can introduce a cache cluster for those requests that are less written and more read.

Specifically, you write a copy of data to the cache cluster while writing to the database, and then use the cache cluster to handle most of the read requests. This way, by caching the cluster, higher concurrency can be carried with fewer machine resources.

conclusion

A complete and complex highly concurrent system architecture will certainly contain a variety of complex self-developed infrastructure systems and a variety of exquisite architectural designs, so a short article can at most have the effect of introducing others to the world. But in general, the idea of database optimization is pretty much there. I hope I can help you.