This article does not involve the complex underlying data structure, through explain to explain SQL, and according to the possible situation, to do the specific optimization, so that the Tens Table Association query first page results can be completed in 2 seconds (real business alarm system optimization results). Queries that need to be optimized: Using explain appears with a using temporary, a using filesort with pagination means that you cannot use an index, and you need to adjust the statement rows too much, or almost the number of records in the whole table, according to the following technique. Ke Y is (NULL); Possible_keys has an excessive (pending) index. 1. Use the explain syntax to interpret SQL and tune it according to its results: the algorithm associated with the MySQL table is the Nest loop Join, which is the result set of the driver table as the circular base data, and then one by one through the result set of data as the filter criteria to the next table query data , and then merge the results: in the a.explain result, the table that appears in the first row is the drive table B.
The driver table can be sorted directly, a non-driver table (a field sort) needs to be sorted (important!) for the combined result (temporary table) of the circular query, which is the using temporary; c. [Driver table] is defined as: 1) When a join condition is specified, the
table with fewer rows of records is [driver table]; 2) when no join condition is specified,
A table with a small number of rows is[Drive table] (important!). D.
The goal of optimizationis to minimize the number of loops in the join nested loop, which guarantees:
always drive large result sets with small result sets(important!)! : A join b,a is a driver, each row in a and B loops JOIN to see if the condition is met, so when a is a small result set, the faster. E.nestedloopjoin is actually the result set of the driver table as the loop base data, and then one line of data from the result set as the filter criteria to the next table query data, and then merge the results. If there is a third participating join,
The join result set for the first two tables is then used as the circular base dataAgain, querying the data in the third table by looping through the conditions, and so forth 2. Two table join optimizations: A. When there is no order by condition, according to the actual situation, using Left/right/inner join can be, according to explain optimization; b. When there is an order B Y condition, such as SELECT * from a INNER join B where 1=1 and other condition order by A.col; Use explain explanatory statement; 1) If the first row of the driver table is a, then the efficiency will be very high, no need to be optimized; 2) Otherwise, the using temporary will appear because the Driver table field can only be sorted directly, so you need to use
Straight_joinClear A is the driver table, to achieve the use of A.col index on the optimization purposes;
or use a left join and a where condition that does not contain B's filter condition, the result set at this time is the complete collection of a,
while Straight_join is inner join and uses a as the driver table3. Multi-table Join optimization: A. No order by conditions, according to the actual situation, using Left/right/inner join, according to explain optimization; b. When there is an order by A.col condition, all joins must be left joins, and Each join field creates an index, and the Where condition can only have a table condition, the data associated with the other table is formed into a large table in a, then the complete set of a is filtered; If you cannot use the left join entirely, you need to use it flexibly
Straight_join and other techniques, take the time sequence as an example:
1) Data warehousing According to the platform time storage, natural a data are ordered by time; SELECT c.*, r.hypervisor_host_name hostname, r.host_ip from Trust_monitor C
Straight_joinRes_node r on c.res_node_id = R.id
Straight_joinAm_assets A on r.asset_id = a.id and A.status = 58
Straight_joinSe_role s on a.dept_flag = s.role_org and s.role_id in (32,33,36,41) where c.status = and C.changed_type = limit 0; SELECT c.*, r.hypervisor_host_name hostname, r.host_ip from Trust_monitor C
Inner JOINRes_node r on c.res_node_id = R.id
INNER JOINAm_assets A on r.asset_id = a.id and A.status = 58
INNER JOINSe_role s on a.dept_flag = s.role_org and s.role_id in (32,33,36,41) where c.status = and C.changed_type = 79
ORDER BY C.changed_timeLimit 1, 10;
The two results are consistent4. Myth: A. View is just a way to block or efficiently assemble multiple table data, and the view and table joins do not have any effect reference: http://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html htt P://huoding.com/2013/06/04/261http://www.cnblogs.com/uttu/p/6384541.html
MySQL Tens Multi-table associated SQL statement tuning
