MySQL tuning and mysql Performance Tuning

MySQL tuning and mysql Performance Tuning

Prerequisites: a normal two-Table connection is required to query the detailed information. Table A contains A large table with tens of thousands of test data records. Table B contains about 1000 million data records in A small table.

1. In order to rush to write the unoptimized SQL statement (For the highlighted topic, the returned field is replaced by * instead of the detailed field, and the WHERE condition is also removed ):

SELECT       *    FROM        A a        LEFT JOIN    B b       ON       a.JOB_ID = b.ID

Time consumption: 3.712 s

The rows field indicates the number of rows to be scanned in the previous step. The number of rows to be scanned in this SQL statement is 22711*1230, this is almost the overhead of two tables for Cartesian Product (select * from a, B ).

MySQL uses a BLOCK Nested-Loop algorithm for JOIN processing. The Block Nested-Loop algorithm uses the result set of the driving table (which can be simply understood as the previous table) as the basic data of the Loop, the data in the result set is used as the filter condition to query data in the next table one by one, and then the results are merged. If there is a third JOIN clause, the JOIN result set of the first two tables is used as the basic data for JOIN, and the data in the third table is queried again through the cyclic query condition. Conclusion: The data volume of the driver table (the preceding table) determines the total number of scans.

2. From the business perspective

All of them are full table scans. Then I found that the Left table data in the business would be meaningless if the right table is NULL, and then changed to INNER JOIN.

Time consumption: 2.745 s

3. We can see that the above types are ALL

System/const/eq_ref/ref/range/index/ALL ---- decrease efficiency from left to right

ALL types take into account whether the connection conditions can be primary keys. If a primary key exists, MySQL can use index query, which improves the efficiency.

Because Table A is A link field of A history table and is not A primary key, you can only add the primary key to this field in table B.

Time consumption: 2.672 s

It is still very slow, because no index has been created, and now the index is added to the ID field of Table B. The result is as follows:

Time consumption: 0.109 s