Some of the optimizations that Mysql can make in the context of complex _mysql

An optimization of a complex associative SQL was processed yesterday, and this kind of SQL optimization often takes into account the following four points:

First, the result set returned by the query, usually the query returns few result sets, is confident to optimize;

Second, the selection of the driver table is critical, by viewing the execution plan, you can see the driver table selected by the optimizer, from which the rows in the execution plan can roughly reflect the problem;

Third, clarify the relationship between the tables, and pay attention to whether there is an appropriate index on the associated field;

The use of Straight_join keywords to force the association between the table order, we can easily verify some conjecture;

Sql:
Execution Time:

Mysql> Select c.yh_id,
-> c.yh_dm,-> c.yh_mc,-> c.mm,-> c.yh_lx,
-> a.jg_id
,
-> a.jg_dm,
-> a.jg_mc,-> a.jgxz_dm,-> d.js_dm-yh_js-> from
A, B, C
-> Left Join D on d.yh_id = c.yh_id
-> where a.jg_id = b.jg_id-> and
b.yh_id = c.yh_id-> and A.yx_b
j = ' Y '
-> and c.sc_bj = ' n '
-> and c.yx_bj = ' y '
-> and c.sc_bj = ' n '-> and c.yh_dm = '
0 06939748XX ';

1 row in Set (0.75 sec)

The SQL query actually returned only one row of data, but it took 750ms to view the execution plan:

mysql> explain-> Select c.yh_id,-> c.yh_dm,-> c.yh_mc,-> c.mm,-> c.yh_lx,-> a.jg_id,-> A.J G_DM,-> a.jg_mc,-> a.jgxz_dm,-> d.js_dm yh_js-> from A, B, C-> left join D on d.yh_id = c.yh_id-> where a.jg_id = b.jg_id-> and b.yh_id = c.yh_id-> and a.yx_bj = ' y '-> and c.sc_bj = ' N '-> and c.yx_bj = ' Y

'-> and c.sc_bj = ' N '-> and c.yh_dm = ' 006939748XX '; +--+ ————-+ ——-+--–+ —————— + ——— + ——— + ———— –+ ——-+ ————-+ | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +--+ ————-+ ——-+--–+ —————— + ——— + ——— + ———— –+ ——-+ ————-+ | 1 | Simple | A | All | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where | | 1 | Simple | B | Ref | PRIMARY | PRIMARY | 98 | test.a.jg_id | 1 | Using Index | | 1 | Simple | C | Eq_ref | PRIMARY | PRIMARY | 98 | test.b.yh_id | 1 | Using where | | 1 | Simple | D | Index | NULL | PRIMARY | 196 | NULL | 54584 |
Using Index | +--+ ————-+ ——-+--–+ —————— + ——— + ——— + ———— –+ ——-+ ————-+

 

You can see two of the more visible performance bottlenecks in the execution plan:

| 1 | Simple | A | All | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |

| 1 | Simple | D | Index | NULL | PRIMARY | 196 | NULL | 54584 | Using Index |

Since d is the table of the left JOIN, the driver table does not select the D table, and we look at the size of the A,b,c three tables:

Mysql> Select COUNT (*) from C;
+ ———-+
| count (*) |
+ ———-+
| 53731 |
+ ———-+

mysql> Select COUNT (*) from A;
+ ———-+
| count (*) |
+ ———-+
| 53335 |
+ ———-+

mysql> Select COUNT (*) from B;
+ ———-+
| count (*) |
+ ———-+
| 105809 |
+ ———-+

Because the data of B table is larger than the other two tables, and there is basically no query filtering condition on B table, the possible exclusion of the driver table choice B;

The optimizer actually chooses a table as the driving table, and why not the C table as the driver table? Let's analyze:

The first stage: a table as a driving table
A–>b–>c–>d:
(1):a.jg_id=b.jg_id-> (b Index: PRIMARY KEY (' jg_id ', ' yh_id '))

(2):b.yh_id=c.yh_id-> (c index: PRIMARY KEY (' yh_id '))

(3):c.yh_id=d.yh_id-> (d index: PRIMARY KEY (' js_dm ', ' yh_id '))
Because there are no yh_id indexes on the D table, indexes are added to the D table:

ALTER TABLE D add index ind_yh_id (yh_id);

Execution Plan:

+--+ ————-+ ——-+--–+ —————— + ——— –+ ——— + ———— –+ ——-+ ————-+
| id | select_type | table | possible_keys | Ref | Rows | Extra |
+--+ ————-+ ——-+--–+ —————— + ——— –+ ——— + ———— –+ ——-+ ————-+
| 1 | Simple | A | All | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | Simple | B | Ref | PRIMARY | PRIMARY | 98 | test.a.jg_id | 1 | Using Index |
| 1 | Simple | C | Eq_ref | PRIMARY | PRIMARY | 98 | test.b.yh_id | 1 | Using where |
| 1 | Simple | D | Ref | ind_yh_id | ind_yh_id | 98 | test.b.yh_id | 272 | Using Index |
+--+ ————-+ ——-+--–+ —————— + ——— –+ ——— + ———— –+ ——-+ ————-+

Execution Time:

1 row in Set (0.77 sec)

After adding an index to the D table, the number of scanned rows in the D table drops to 272 lines (starting with: 54584)

| 1 | Simple | D | Ref | ind_yh_id | ind_yh_id | 98 | test.b.yh_id | 272 | Using Index |

Phase II: Table C as a driving table

D
^
|
C–>b–>a
Because there are YH_DM filtering conditions on the C table, we create an index on the YH_DM:

Mysql> Select COUNT (*) from C where yh_dm = ' 006939748XX ';
+ ———-+
| count (*) |
+ ———-+
| 2 |
+ ———-+

To add an index:

ALTER TABLE C Add index IND_YH_DM (YH_DM)

To view the execution plan:

+--+ ————-+ ——-+--–+ ——————-+ ——— –+ ——— + ———— –+ ——-+ ————-+
| id | select_type | table | type | possible_keys | key | key_len | Ref | Rows | Extra |
+--+ ————-+ ——-+--–+ ——————-+ ——— –+ ——— + ———— –+ ——-+ ————-+
| 1 | Simple | A | All | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | Simple | B | Ref | PRIMARY | PRIMARY | 98 | test.a.jg_id | 1 | Using Index |
| 1 | Simple | C | Eq_ref | PRIMARY,IND_YH_DM | PRIMARY | 98 | test.b.yh_id | 1 | Using where |
| 1 | Simple | D | Ref | ind_yh_id | ind_yh_id | 98 | test.b.yh_id | 272 | Using Index |
+--+ ————-+ ——-+--–+ ——————-+ ——— –+ ——— + ———— –+ ——-+ ————-+

Execution Time:

1 row in Set (0.74 sec)

After adding the index to the C table, the index is still not on, the execution plan still takes the table A as the driver, so let's analyze why we still use a table as the driver table?

1):c.yh_id=b.yh_id-> (PRIMARY KEY (' jg_id ', ' yh_id '))

A. If you use Table C as a driver, C table and b table in the association, because there is no yh_id field in B table index, because B table data volume is very large, so the optimizer thinks that if the C table as a driver table, it will have a large association with B table (here you can use Straight_ Join enforces the use of C tables as a driver table);
B. If a table is the driving table, then the table A and B are associated, because there is an index of the jg_id field on table B, so the optimizer considers that the cost of using a as the driving table is less than the cost of C as the driving plate;
So if we want to use Table C as the driver, just add the yh_id index on B:

ALTER TABLE B Add index ind_yh_id (yh_id);

2):b.jg_id=a.jg_id-> (PRIMARY KEY (' jg_id '))

3):c.yh_id=d.yh_id-> (KEY ' ind_yh_id ' (' yh_id '))
Execution Plan:

+--+ ————-+ ——-+--–+ ——————-+ ——— –+ ——— + ———— –+--+ ————-+
| id | select_type | table | type | possible_keys | key | key_len | Ref | Rows | Extra |
+--+ ————-+ ——-+--–+ ——————-+ ——— –+ ——— + ———— –+--+ ————-+
| 1 | Simple | C | Ref | PRIMARY,IND_YH_DM | IND_YH_DM | 57 | Const | 2 | Using where |
| 1 | Simple | D | Ref | ind_yh_id | ind_yh_id | 98 | test.c.yh_id | 272 | Using Index |
| 1 | Simple | B | Ref | primary,ind_yh_id | ind_yh_id | 98 | test.c.yh_id | 531 | Using Index |
| 1 | Simple | A | Eq_ref | PRIMARY,INDEX_JG | PRIMARY | 98 | test.b.jg_id | 1 | Using where |
+--+ ————-+ ——-+--–+ ——————-+ ——— –+ ——— + ———— + –+--+ ————-+

Execution Time:

1 row in Set (0.00 sec)

You can see that the rows in the execution plan have been significantly reduced, and the execution time has been reduced from 750ms to 0 MS levels;