Summary of MySQL decomposition connections (from high-performance MySQL and its own site performance optimizations)

Many high-performance sites use the "explode-connect" technique, which is to change a single multi-table connection query to multiple but table queries, and then merge the data into the program, such as:

Select A.*,b.* from a a joins b b on a.id = b.ID

Can be replaced by:

Select A.* from A;

Select b.* from B;

Then merge the data through the program.

Some people may think that this is too wasteful, to turn a query into two query statements or more query statements, and if any of the apes think so, then you should continue to look down.

Refactoring a connection query to a multi-table query has the following performance benefits:

One, the cache is more efficient. Many applications cache tables directly.

Second, for the MyISAM table, each table query can make more efficient use of the table lock, because the query locks a single table for a shorter time, rather than locking all the tables for a long time.

Third, in the terminal application connection, can be more convenient to expand the database, the different tables on different servers.

Four, the query itself will be more efficient

Five, you can reduce the extra row access. Connecting on the application side means that each row of data is accessed only once, and the connection is inherently non-regularization, and it repeatedly accesses the same row of data. For the same reason, this refactoring method can reduce network traffic and memory consumption.

When is it more efficient to connect on the application side?

One, you can cache large amounts of data from earlier queries

Second, multiple MyISAM tables are used

Third, the data is distributed on unused servers

Four, for large tables, replace the connection with in ()

Five, a connection references the same table many times