本文整理自：袋鼠云技術(shù)薈 | SQL優(yōu)化案例(2)：OR條件優(yōu)化

數(shù)棧是云原生—站式數(shù)據(jù)中臺(tái)PaaS，我們?cè)趃ithub上有一個(gè)有趣的開源項(xiàng)目：https://github.com/DTStack/flinkx

FlinkX是一個(gè)基于Flink的批流統(tǒng)一的數(shù)據(jù)同步工具，既可以采集靜態(tài)的數(shù)據(jù)，比如MySQL，HDFS等，也可以采集實(shí)時(shí)變化的數(shù)據(jù)，比如MySQL binlog，Kafka等，是全域、異構(gòu)、批流一體的數(shù)據(jù)同步引擎，大家如果有興趣，歡迎來(lái)github社區(qū)找我們玩~

在MySQL中，同樣的查詢條件，如果變換OR在SQL語(yǔ)句中的位置，那么查詢的結(jié)果也會(huì)有差異，在較為復(fù)雜的情況下，可能會(huì)帶來(lái)索引選擇不佳的性能隱患，為了避免執(zhí)行效率大幅度下降的問(wèn)題，我們可以適當(dāng)考慮使用Union all 對(duì)查詢邏輯較為復(fù)雜的SQL進(jìn)行分離。

常見(jiàn)OR使用場(chǎng)景，請(qǐng)閱讀以下案例：

案例一：不同列使用OR條件查詢

1. 待優(yōu)化場(chǎng)景

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<> ''

執(zhí)行計(jì)劃

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

共返回1 行記錄,花費(fèi) 5 ms。

2. 場(chǎng)景解析

從查詢條件中可以看出 token 和 uid 過(guò)濾性都非常好，但是由于使用了 or， 需要采用 index merge 的方法才能獲得比較好的性能。但在實(shí)際執(zhí)行過(guò)程中MySQL優(yōu)化器默認(rèn)選擇了使用registrationId 上的索引，導(dǎo)致 SQL 的性能很差。

3. 場(chǎng)景優(yōu)化

我們將SQL改寫成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 ? ? ? ? ? ? ? ? ? ?| +--------------+-----------------------+-----------------+----------------+------------------------------+---------------+-------------------+------------------------------+----------------+------------------------------------+

共返回5 行記錄,花費(fèi) 5 ms。

通過(guò)對(duì)比優(yōu)化前后的執(zhí)行計(jì)劃，可以明顯看出，將SQL拆分成兩個(gè)子查詢，再使用union對(duì)結(jié)果進(jìn)行合并，穩(wěn)定性和安全性更好，性能更高。

案例二：同一列使用OR查詢條件

1. 待優(yōu)化場(chǎng)景

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;

執(zhí)行計(jì)劃

2. 場(chǎng)景解析

本例的SQL查詢中有一個(gè)子查詢，子查詢被當(dāng)成驅(qū)動(dòng)表，產(chǎn)生了auto_key，通過(guò)SQL拆分進(jìn)行測(cè)試，驗(yàn)證主要是(pc.count =0 , or pc.count is null )會(huì)影響到整個(gè)SQL的性能，需要進(jìn)行比較改寫。

3. 場(chǎng)景優(yōu)化

首先我們可以單獨(dú)思考(pc.count =0 , or pc.count is null ) 如何進(jìn)行優(yōu)化？先寫一個(gè)類似的SQL

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

這個(gè)時(shí)候我們看到的其實(shí)是同一個(gè)列，但對(duì)應(yīng)不同的值，這種情況可以利用case when進(jìn)行轉(zhuǎn)換。

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)

再回到原始SQL進(jìn)行改寫。

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;

可以看出優(yōu)化后的SQL比原始SQL快了30秒，執(zhí)行效率提升約50倍。

案例三：優(yōu)化關(guān)聯(lián)SQL OR條件

1. 待優(yōu)化場(chǎng)景

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.場(chǎng)景解析

我們仔細(xì)分析上述查詢語(yǔ)句，發(fā)現(xiàn)雖然業(yè)務(wù)邏輯只需要查詢半分鐘內(nèi)修改的數(shù)據(jù)，但執(zhí)行過(guò)程卻必須對(duì)所有的數(shù)據(jù)進(jìn)行關(guān)聯(lián)操作，帶來(lái)不必要的性能損耗。

3.場(chǎng)景優(yōu)化

我們對(duì)原始SQL進(jìn)行拆分操作，第一部分sql-01如下：

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-01以u(píng)ser_msg 表為驅(qū)動(dòng)，使用gmt_modified 索引過(guò)濾最新數(shù)據(jù)。

第二部分sql-02如下：

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)

ql-02以u(píng)ser為驅(qū)動(dòng)表，msg user_id 的索引過(guò)濾行很好。

第三部分sql-03如下：

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-03以group為驅(qū)動(dòng)表，使用gmt_modified 索引過(guò)濾最新數(shù)據(jù)。

總結(jié)

MySQL OR條件優(yōu)化的常見(jiàn)場(chǎng)景主要有以下情況：

1、相同列可以使用IN進(jìn)行代替

2、不同列及復(fù)雜的情況下，可以使用union all 進(jìn)行分離

3、關(guān)聯(lián)SQL OR條件

我們需要結(jié)合實(shí)際場(chǎng)景，分析優(yōu)化。