SQL optimization case (2): or condition optimization

Time:2021-5-12

Next, the last article “SQL optimization case (1): implicit transformation” is introduced. The content here focuses on the optimization of or.

In mysql, the same query conditions, if you change the position of or in SQL statements, the query results will be different. In multiple complex cases, it may bring poor index selection performance hidden trouble. In order to avoid the problem of greatly reducing the execution efficiency, we can properly consider using unified all SQL to separate the complex query logic.

For common or scenarios, please read the following cases.

Case 1: different columns using or condition query

1. Scenarios to be optimized

SELECT
..
..
 FROM`t1` a
 WHERE a.token= '16149684'
 AND a.store_id= '242950'
 AND(a.registrationId IS NOT NULL
 AND a.registrationId<> '')
 OR a.uid= 308475
 AND a.registrationId IS NOT NULL
 AND a.registrationId<> ''

Implementation plan

+--------------+-----------------------+-----------------+----------------+-------------------+-------------------+---------------+----------------+---------------------------------------------+
| id           | select_type           | table           | type           | key               | key_len           | ref           | rows           | Extra                                       |
+--------------+-----------------------+-----------------+----------------+-------------------+-------------------+---------------+----------------+---------------------------------------------+
| 1            | SIMPLE                | a               | range          |idx_registrationid | 99                |               | 100445         | Using index condition; Using where          |
+--------------+-----------------------+-----------------+----------------+-------------------+-------------------+---------------+----------------+---------------------------------------------+

It takes 5 ms to return one row of records.

2. Scene analysis

From the query conditions, we can see that the token and uid filtering are very good, but because of the use of, or need to use the index merge method to get better performance. However, in the actual implementation process, MySQL optimizer chooses the index using registrationid instead, resulting in poor performance of SQL.

3. Scene optimization

We rewrite SQL to union all.

SELECT
...
...
FROM`t1` a
WHERE a.token = '16054473'
AND a.store_id = '138343'
AND b.is_refund = 1
AND (a.registrationId IS NOT NULL
AND a.registrationId <> '')
union all
SELECT
...
...
FROM`t1` a
where a.uid = 181579
AND a.registrationId IS NOT NULL
AND a.registrationId <> ''
+--------------+-----------------------+-----------------+----------------+------------------------------+---------------+-------------------+------------------------------+----------------+------------------------------------+
| id           | select_type           | table           | type           | possible_keys                | key           | key_len           | ref                          | rows           | Extra                              |
+--------------+-----------------------+-----------------+----------------+------------------------------+---------------+-------------------+------------------------------+----------------+------------------------------------+
| 1            | PRIMARY               | a               | ref            | IDX_TOKEN,IDX_STORE_ID_TOKEN | IDX_TOKEN     | 63                | const                        | 1              | Using index condition; Using where |
| 1            | PRIMARY               | b               | eq_ref         | PRIMARY                      | PRIMARY       | 4                 | youdian_life_sewsq.a.role_id | 1              | Using where                        |
| 2            | UNION                 | a               | const          | PRIMARY                      | PRIMARY       | 4                 | const                        | 1              |                                    |
| 2            | UNION                 | b               | const          | PRIMARY                      | PRIMARY       | 4                 | const                        | 0              | unique row not found               |
|              | UNION RESULT          | <union1,2>      | ALL            |                              |               |                   |                              |                | Using temporary                    |
+--------------+-----------------------+-----------------+----------------+------------------------------+---------------+-------------------+------------------------------+----------------+------------------------------------+

A total of 5 rows of records are returned, which takes 5 ms.

By comparing the execution plan before and after optimization, we can prune it obviously, merge SQL into two sub queries, and then use union to merge the results, which has better stability, security and performance.

Case 2: using or query condition in the same column

1. Scenarios to be optimized

select
....
....
from
t1 as mci
left join t1 as ccv2_1 on ccv2_1.unique_no = mci=category_no1
left join t1 as ccv2_2 on ccv2_2.unique_no = mci=category_no2
left join t1 as ccv2_3 on ccv2_3.unique_no = mci=category_no3
left join(
 select product_id,
 count(0) count
 from t2 pprod
 inner join t3 pinfo on pinfo.promotion_id = pprod.promotion_id
 and pprod.is_enable =1
 and ppinfo.is_enable=1
 and pinfo.belong_t0 =1
 and pinfo.end_time >=now()
 and not (
 pinfo.onshelv_time>'2019-06-30 00:00:00'
 or pinfo.end_time>'2018-12-05 00:00:00'
 )group by pprod.product_id
)as pc on pc.product_id = mci.product_id
where mci.is_enable =0
and mci.comodifty_type in ('1', '5', '6')
and (pc.count =0 or pc.count isnull ) limit 0,5;

Implementation plan

SQL optimization case (2): or condition optimization

2. Scene analysis

In this example, there is a sub query in the SQL query. The sub query is regarded as a driver table, resulting in auto_ Key, which is tested by SQL. The main reason for verification is that (pc.count = 0 or pc.count is null) will affect the performance of the whole SQL, so comparison and rewriting are needed.

3. Scene optimization

First of all, we can think independently (PC. count = 0 or PC. count is null) how to optimize? Write a similar SQL first

Select col from test where col =100 or col is null;
+--------+
| col    |
+--------+
|    100 |
|   NULL |
+--------+
2 rows in set (0.00 sec)

At this time, we see the same column, but corresponding to different values. In this case, we can use case when to convert.

Select col From test where case when col is null then 100 else col =100 end;
+--------+
| col    |
+--------+
|    100 |
|   NULL |
+--------+
2 rows in set (0.00 sec)

Go back to the original SQL and rewrite it.

select
....
....
from
t1 as mci
left join t1 as ccv2_1 on ccv2_1.unique_no = mci=category_no1
left join t1 as ccv2_2 on ccv2_2.unique_no = mci=category_no2
left join t1 as ccv2_3 on ccv2_3.unique_no = mci=category_no3
left join(
 select product_id,
 count(0) count
 from t2 pprod
 inner join t3 pinfo on pinfo.promotion_id = pprod.promotion_id
 and pprod.is_enable =1
 and ppinfo.is_enable=1
 and pinfo.belong_t0 =1
 and pinfo.end_time >=now()
 and not (
 pinfo.onshelv_time>'2019-06-30 00:00:00'
 or pinfo.end_time>'2018-12-05 00:00:00'
 )group by pprod.product_id
)as pc on pc.product_id = mci.product_id
where mci.is_enable =0
and mci.comodifty_type in ('1', '5', '6')
and case when pc.count is null then 0 else pc.count end=0 limit 0,5;

SQL optimization case (2): or condition optimization

The optimized SQL is 30 seconds faster than the original SQL, and the execution efficiency is improved about 50 times.

Case 3: optimizing or conditions of associated SQL

1. Scenarios to be optimized

SELECT user_msg.msg_id AS ‘msg_id’, user_msg.content AS ‘msg_content’, …
FROM user_msg
LEFT JOIN user ON user_msg.user_id = user.user_id
LEFT JOIN group ON user_msg.group_id = group.group_id
WHERE user_msg.gmt_modified >= date_sub('2018-03-29 09:31:44', INTERVAL30SECOND)
OR user.gmt_modified >= date_sub('2018-03-29 09:31:44', INTERVAL 30 SECOND)
OR group.gmt_modified >= date_sub('2018-03-29 09:31:44', INTERVAL 30 SECOND)

2. Scene analysis

We carefully analyze the above query statements and find that although the business logic only needs to query the data modified in half a minute, the execution process must associate all the data, resulting in performance loss.

SQL optimization case (2): or condition optimization

3. Scene optimization

The first part of sql-01 is as follows:

SELECT user_msg.msg_id AS ‘msg_id’, user_msg.content AS ‘msg_content’, …
FROM user_msg
LEFT JOIN user ON user_msg.user_id = user.user_id
LEFT JOIN group ON user_msg.group_id = group.group_id
WHERE user_msg.gmt_modified >= date_sub('2018-03-29 09:31:44', INTERVAL 30 SECOND)

SQL optimization case (2): or condition optimization

Sql-01 as user_ MSG table as the driver, using GMT_ The modified index filters the latest data.

The second part of sql-02 is as follows:

SELECT user_msg.msg_id AS ‘msg_id’, user_msg.content AS ‘msg_content’, …
FROM user_msg
LEFT JOIN user ON user_msg.user_id = user.user_id
LEFT JOIN group ON user_msg.group_id = group.group_id
WHERE user.gmt_modified >= date_sub('2018-03-29 09:31:44', INTERVAL 30 SECOND)

SQL optimization case (2): or condition optimization

Sql-02 user driven table, MSG user_ The index of ID filters the rows very well.

The third part sql-03 is as follows:

SELECT user_msg.msg_id AS ‘msg_id’, user_msg.content AS ‘msg_content’, …
FROM user_msg
LEFT JOIN user ON user_msg.user_id = user.user_id
LEFT JOIN group ON user_msg.group_id = group.group_id
WHERE group.gmt_modified >= date_sub('2018-03-29 09:31:44', INTERVAL 30 SECOND)

SQL optimization case (2): or condition optimization

Sql-03 takes group as the driving table and uses GMT_ The modified index filters the latest data.

summary

The common scenarios of MySQL or condition optimization are as follows:

1, the same column can be replaced by in

2. Union all can be used to separate different columns and complex situations

3. Associate SQL or conditions

We need to combine the actual scene, analysis and optimization.

More technologies can be found on the official website[https://www.dtstack.com/dtsmart/
](https://www.dtstack.com/dtsma…