SQL optimization for multiple table union queries eliminates “distinct” de-duplication keywords

When I submitted the code, the architect pointed out to me that I would have problems writing this SQL. Subqueries are not supported when the database is divided into tables.Copy the code

Therefore, the SQL structure of multi-table subqueries needs to be optimized.

Isn’t that scary; (In order to desensitize here, I have coded out the relevant SQL keywords)

SQL > select * from table D where id = 1; SQL > select * from table D where ID = 1; As you can see in this step, I generated a subtable named SSS with the result of multiple values.

2, the next step is the need to sort (in reverse order by time, because in the foreground by time display);

3, the third step is to merge these results with a table, query the sorted information of each ID; Then it paginates;

Others can not care about, the final is to duplicate the keyword (DISTINCT), take a small book of this mark, will test oh.Copy the code
Practice is the sole criterion for verifying truthCopy the code

For example, the following table:

You can see that the values of both the name and product_unit columns can be duplicated.

mysql> SELECT t1.id,t1.name,t1.product_unit FROM dd_product_category t1; + + | | id name | product_unit | | | | beverage bottle 55 | | 56 | | drinks box | | | 57 snack packages | | | | | | puffing food bag 59 | | | 60 convenient food box | | 61 | since hot pot bowls | | | 62 | | boxes of instant noodles | | 63 | | mineral water box | | 64 | | candy | | | | 65 | wine box | 66 | | hard liquor box | | 67 | | beer box | | 68 | The mixer | | box + 13 rows in the set (0.13 SEC) mysql > mysql >Copy the code

How do we want to get onlynameorProduct_unit columnWhat if you don’t want duplicate values?

1. It is easy to get a single value, but there are duplicate data, we only keep one of these duplicate data, so what should we do?

mysql> SELECT t1.product_unit FROM dd_product_category t1; Bottle + + | product_unit | | | | box | | | package bag | | | box | | bowl | | box | | box | | | | box | | box | | box | | | box + 13 rows in the set (19.31)  sec) mysql>Copy the code

2. Remove duplicate columns

mysql> mysql> SELECT DISTINCT t1.product_unit FROM dd_product_category t1; Bottle + + | product_unit | | | | box | | | package bag | | | bowl | | | + 6 rows in the set (0.11 SEC) mysql >Copy the code

Is it very simple, although it looks simple, but if multiple table subquery, there will be a problem, for example, you want to query the data of three tables A, B, C, these three tables must be related.

A and B are 1-n. But you only have the ID in table B, you need to query the data in table B, then use the data in table B to query the data in table A, and then query the data in table C.

It must be very convoluted.

You definitely need to lose weight during the whole processCopy the code

When the entire SQL is written, it is basically the same as the SQL I wrote before optimization. (multiple table nesting, multiple SQL nested SQL, la la la a lot of).

There are a lot of optimization ideas, at that time can think of is to break this complex SQL into a number of simple SQL execution, and then use Java backend code for processing. (For those of me who are not content with the status quo and want to find a more amicable solution than this, I will not give in to this problem.)

At this point, I will give you a link:

  • 1, (Mysql5.7 official mentioned in the manual about the optimization of distinct methods) dev.mysql.com/doc/refman/…
  • 2, and an optimized group by: dev.mysql.com/doc/refman/…

I recommend it.

The Mysql5.7 official manual mentions a method for optimizing distinct, which reads as follows:

MySQL 5.7 Reference Manual /… / DISTINCT Optimization

8.2.1.16 DISTINCT Optimization

DISTINCT combined with ORDER BY needs a temporary table in many cases.

Many cases where DISTINCT is combined with order BY require a temporary table;

Because DISTINCT may use GROUP BY, learn how MySQL works with columns in ORDER BY or HAVING clauses that are not part of the selected columns. See Section 12.20.3, “MySQL Handling of GROUP BY”.

Because DISTINCT may use Group BY, see how MySQL handles columns by Order or clauses that are not part of the selected column. See section 12.20.3, "MySQL Handling of GROUP BY".Copy the code

In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. For example, the following two queries are equivalent:

In most cases, a different clause can be considered a special case of group by. For example, the following two queries are equivalent:Copy the code
SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;
Copy the code
SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;
Copy the code

Due to this equivalence, the optimizations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause. Thus, for more details on the optimization possibilities for DISTINCT queries, see Section 8.2.1.15, “GROUP BY Optimization”.

Because of this equivalence, optimization of group by queries can also be applied to queries with different clauses. Therefore, more details on distinct query Optimization can be found in Section 8.2.1.15, "GROUP BY Optimization."Copy the code

When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

When row_count is used with DISTINCT, MySQL stops as soon as it discovers that row_count is a unique row.

If you do not use columns from all tables named in a query, MySQL stops scanning any unused tables as soon as it finds the first match. In the following case, assuming that t1 is used before t2 (which you can check with EXPLAIN), MySQL stops reading from t2 (for any particular row in t1) when it finds the first row in t2:

If columns from all tables are not applicable in the query, MySQL will stop scanning any unused tables as soon as it finds the first match.

In the following example, assuming T1 is used before T2 (which you can look through with explanin), MySQL stops reading from T2 when it finds the first row of T2 (for any particular row in T1).

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
Copy the code

The official manual wrote, is really every button heart ah!!

The summary has the following important points:

  • 1, distinct is almost equivalent to group by;
  • 2. Distinct correlation optimization is equivalent to group by query optimization;

Let’s try it and do an experiment.

Let’s take the following effect as our ultimate goal:

mysql> mysql> SELECT DISTINCT t1.product_unit FROM dd_product_category t1; Bottle + + | product_unit | | | | box | | | package bag | | | bowl | | | + 6 rows in the set (0.11 SEC) mysql >Copy the code

Use group by to remove weight:

mysql> select t1.product_unit from dd_product_category t1 group by t1.product_unit; + + | product_unit | | | | | package bottle | | | bowl | | box | | | + 6 rows in the set (19.46 SEC) mysql >Copy the code
As you can see, the final numbers are exactly the same. Then the experiment was successful, and the distinct effect was the same as that of group BY. So when we optimize distinct, we will change to optimize Group BY. (The SQL I optimized before did not use Group BY, so we could not optimize group BY, we could only transform the SQL with distinct complex SQL into group BY.)Copy the code

Open I mentioned before this optimization group by the official handbook: dev.mysql.com/doc/refman/…

Because the text is quite long, I will not repeat it here.

All you need to do now is change distinct into a group by SQL syntax.

How, the SQL after transformation, is not quite refreshing.

1. We threw away multiple nested SQL;

There is no need to generate a temporary SSS tableCopy the code

For myself, I have learned:

  • 1, distinct is almost equivalent to group by;
  • 2. Distinct correlation optimization is equivalent to group by query optimization;
  • 3. If distinct doesn’t optimize your SQL, try using group by.

I uploaded all these to Baidu Cloud.

In order to prevent lost links can follow the public account, reply :"mysql". Get all the interesting content about MySQL.Copy the code

Welcome to study together, communicate together and make progress together.

** follow my wechat public number for the first time to push you wonderful content oh: **TrueDi