ClickHouse-语法优化规则

ClickHouse 的 SQL 优化规则是基于 RBO(Rule Based Optimization)，下面是一些优化规则

以下规则中使用了ClickHouse官方测试数据集，数据集使用步骤移步这里。

1.COUNT优化

在调用 count 函数时，如果使用的是 count() 或者 count(*)，且没有 where 条件，则会直接使用 system.tables 的 total_rows，例如:

date1002 :) EXPLAIN SELECT count() FROM datasets.hits_v1;
EXPLAIN
SELECT count()
FROM datasets.hits_v1
Query id: 5bc13813-9749-47c2-876a-71816f306358
┌─explain─────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                                         │
│   SettingQuotaAndLimits (Set limits and quota after reading from storage)           │
│     ReadFromStorage (MergeTree(with Aggregate projection _minmax_count_projection)) │
└─────────────────────────────────────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.003 sec. 

 

ReadFromStorage，直接从持久化文件中读取count，这是对count的优化

如果count某一具体列字段，则不会使用此项优化

 

date1002 :) EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;
EXPLAIN
SELECT count(CounterID)
FROM datasets.hits_v1
Query id: 4a8a69d4-a8b1-478a-a0fb-fbad12a84fa5
┌─explain───────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                                   │
│   Aggregating                                                                 │
│     Expression (Before GROUP BY)                                              │
│       SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│         ReadFromMergeTree                                                     │
└───────────────────────────────────────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.002 sec. 

 

执行SQL看区别

 

执行SELECT count() FROM datasets.hits_v1;

date1002 :) SELECT count() FROM datasets.hits_v1;
SELECT count()
FROM datasets.hits_v1
Query id: 96ee9fc8-29ee-4e45-9917-33b6c9b99574
┌─count()─┐
│ 8873898 │
└─────────
1 rows in set. Elapsed: 0.002 sec. 

 

执行指定某一列的SQL可看到扫描了所有数据

date1002 :) SELECT count(CounterID) FROM datasets.hits_v1;
SELECT count(CounterID)
FROM datasets.hits_v1
Query id: 1a8bb3fa-9864-4e33-a92e-55b89bd8e9e5
┌─count(CounterID)─┐
│          8873898 │
└──────────────────┘
1 rows in set. Elapsed: 0.008 sec. Processed 8.87 million rows, 35.50 MB (1.09 billion rows/s., 4.34 GB/s.)

 

2.消除子查询重复字段

下面语句子查询中有两个重复的 id 字段，会被去重:

date1002 :) EXPLAIN SYNTAX
            SELECT
                a.UserID,
                b.VisitID,
                a.URL,
                b.UserID
            FROM datasets.hits_v1 AS a
            LEFT JOIN
            (
                SELECT
                    UserID,
                    UserID AS HaHa,
                    VisitID
                FROM datasets.visits_v1
            ) AS b USING (UserID)
            LIMIT 3;
EXPLAIN SYNTAX
SELECT
    a.UserID,
    b.VisitID,
    a.URL,
    b.UserID
FROM datasets.hits_v1 AS a
LEFT JOIN
(
    SELECT
        UserID,
        UserID AS HaHa,
        VisitID
    FROM datasets.visits_v1
) AS b USING (UserID)
LIMIT 3
Query id: e6907eb2-abe9-4a39-95bd-cfdcc4b87939
┌─explain─────────────────────┐
│ SELECT                      │
│     UserID,                 │
│     VisitID,                │
│     URL,                    │
│     b.UserID                │
│ FROM datasets.hits_v1 AS a  │
│ ALL LEFT JOIN               │
│ (                           │
│     SELECT                  │
│         UserID,             │
│         VisitID             │
│     FROM datasets.visits_v1 │
│ ) AS b USING (UserID)       │
│ LIMIT 3                     │
└─────────────────────────────┘
14 rows in set. Elapsed: 0.002 sec. 

 

 

3.谓词下推

当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤。例如下面的查询，HAVING name 变成了 WHERE name，在 group by 之前过滤：

date1002 :) EXPLAIN SYNTAX SELECT UserID FROM datasets.hits_v1 GROUP BY UserID HAVING UserID ='8585742290196126178';
EXPLAIN SYNTAX
SELECT UserID
FROM datasets.hits_v1
GROUP BY UserID
HAVING UserID = '8585742290196126178'
Query id: 87cd76aa-947a-4cf3-bd00-b21d2a5951cf
┌─explain──────────────────────────────┐
│ SELECT UserID                        │
│ FROM datasets.hits_v1                │
│ WHERE UserID = '8585742290196126178' │
│ GROUP BY UserID                      │
└──────────────────────────────────────┘
4 rows in set. Elapsed: 0.002 sec.

 

 

子查询也支持谓词下推：

EXPLAIN SYNTAX
SELECT *
FROM
(
    SELECT UserID
    FROM datasets.visits_v1
)
WHERE UserID = '8585742290196126178'
Query id: 4db29eee-5dc5-4bf5-84de-564e2f58a4f6
┌─explain──────────────────────────────────┐
│ SELECT UserID                            │
│ FROM                                     │
│ (                                        │
│     SELECT UserID                        │
│     FROM datasets.visits_v1              │
│     WHERE UserID = '8585742290196126178' │
│ )                                        │
│ WHERE UserID = '8585742290196126178'     │
└──────────────────────────────────────────┘
8 rows in set. Elapsed: 0.002 sec.  

 

另外一个例子

EXPLAIN SYNTAX
SELECT *
FROM
(
    SELECT *
    FROM
    (
        SELECT UserID
        FROM datasets.visits_v1
    )
    UNION ALL
    SELECT *
    FROM
    (
        SELECT UserID
        FROM datasets.visits_v1
    )
)
WHERE UserID = '8585742290196126178'
Query id: b317cb59-9594-4d4f-8e5e-6ddf2f3bd52f
┌─explain──────────────────────────────────────┐
│ SELECT UserID                                │
│ FROM                                         │
│ (                                            │
│     SELECT UserID                            │
│     FROM                                     │
│     (                                        │
│         SELECT UserID                        │
│         FROM datasets.visits_v1              │
│         WHERE UserID = '8585742290196126178' │
│     )                                        │
│     WHERE UserID = '8585742290196126178'     │
│     UNION ALL                                │
│     SELECT UserID                            │
│     FROM                                     │
│     (                                        │
│         SELECT UserID                        │
│         FROM datasets.visits_v1              │
│         WHERE UserID = '8585742290196126178' │
│     )                                        │
│     WHERE UserID = '8585742290196126178'     │
│ )                                            │
│ WHERE UserID = '8585742290196126178'         │
└──────────────────────────────────────────────┘
22 rows in set. Elapsed: 0.005 sec. 

 

4.聚合计算外推

聚合函数内的计算，会外推，例如：

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM datasets.visits_v1
Query id: a1fa9181-51b0-427f-96cc-dc106b8eade6
┌─explain─────────────────┐
│ SELECT sum(UserID) * 2  │
│ FROM datasets.visits_v1 │
└─────────────────────────┘
2 rows in set. Elapsed: 0.001 sec.

 

5.聚合函数消除

如果对聚合键，也就是 group by key 使用 min、max、any 聚合函数，则将函数消除，例如：

EXPLAIN SYNTAX
SELECT
    sum(UserID * 2),
    max(VisitID),
    max(UserID)
FROM datasets.visits_v1
GROUP BY UserID
Query id: df74bce8-e31b-4ff5-8b7e-83bcbb3b8849
┌─explain─────────────────┐
│ SELECT                  │
│     sum(UserID) * 2,    │
│     max(VisitID),       │
│     UserID              │
│ FROM datasets.visits_v1 │
│ GROUP BY UserID         │
└─────────────────────────┘
6 rows in set. Elapsed: 0.002 sec.  

 

6.删除重复的order by key

例如下面的语句，重复的聚合键 id 字段会被去重:

EXPLAIN SYNTAX
SELECT *
FROM datasets.visits_v1
ORDER BY
    UserID ASC,
    UserID ASC,
    VisitID ASC,
    VisitID ASC
Query id: 5c368e05-f17e-4600-8543-fb87c1035f7a
┌─explain───────────────────────────────────┐
│ SELECT                                    │
│     CounterID,                            │
│     StartDate,                            │
│     Sign,                                 │
│     IsNew,                                │
│     VisitID,                              │
│     UserID,                               │
│     StartTime,                            │
│     Duration,                             │
│     UTCStartTime,                         │
│     PageViews,                            │
│     Hits,                                 │
│     IsBounce,                             │
│     Referer,                              │
│     StartURL,                             │
│     RefererDomain,                        │
│     StartURLDomain,                       │
│     EndURL,                               │
│     LinkURL,                              │
│     IsDownload,                           │
│     TraficSourceID,                       │
│     SearchEngineID,                       │
│     SearchPhrase,                         │
│     AdvEngineID,                          │
│     PlaceID,                              │
│     RefererCategories,                    │
│     URLCategories,                        │
│     URLRegions,                           │
│     RefererRegions,                       │
│     IsYandex,                             │
│     GoalReachesDepth,                     │
│     GoalReachesURL,                       │
│     GoalReachesAny,                       │
│     SocialSourceNetworkID,                │
│     SocialSourcePage,                     │
│     MobilePhoneModel,                     │
│     ClientEventTime,                      │
│     RegionID,                             │
│     ClientIP,                             │
│     ClientIP6,                            │
│     RemoteIP,                             │
│     RemoteIP6,                            │
│     IPNetworkID,                          │
│     SilverlightVersion3,                  │
│     CodeVersion,                          │
│     ResolutionWidth,                      │
│     ResolutionHeight,                     │
│     UserAgentMajor,                       │
│     UserAgentMinor,                       │
│     WindowClientWidth,                    │
│     WindowClientHeight,                   │
│     SilverlightVersion2,                  │
│     SilverlightVersion4,                  │
│     FlashVersion3,                        │
│     FlashVersion4,                        │
│     ClientTimeZone,                       │
│     OS,                                   │
│     UserAgent,                            │
│     ResolutionDepth,                      │
│     FlashMajor,                           │
│     FlashMinor,                           │
│     NetMajor,                             │
│     NetMinor,                             │
│     MobilePhone,                          │
│     SilverlightVersion1,                  │
│     Age,                                  │
│     Sex,                                  │
│     Income,                               │
│     JavaEnable,                           │
│     CookieEnable,                         │
│     JavascriptEnable,                     │
│     IsMobile,                             │
│     BrowserLanguage,                      │
│     BrowserCountry,                       │
│     Interests,                            │
│     Robotness,                            │
│     GeneralInterests,                     │
│     Params,                               │
│     `Goals.ID`,                           │
│     `Goals.Serial`,                       │
│     `Goals.EventTime`,                    │
│     `Goals.Price`,                        │
│     `Goals.OrderID`,                      │
│     `Goals.CurrencyID`,                   │
│     WatchIDs,                             │
│     ParamSumPrice,                        │
│     ParamCurrency,                        │
│     ParamCurrencyID,                      │
│     ClickLogID,                           │
│     ClickEventID,                         │
│     ClickGoodEvent,                       │
│     ClickEventTime,                       │
│     ClickPriorityID,                      │
│     ClickPhraseID,                        │
│     ClickPageID,                          │
│     ClickPlaceID,                         │
│     ClickTypeID,                          │
│     ClickResourceID,                      │
│     ClickCost,                            │
│     ClickClientIP,                        │
│     ClickDomainID,                        │
│     ClickURL,                             │
│     ClickAttempt,                         │
│     ClickOrderID,                         │
│     ClickBannerID,                        │
│     ClickMarketCategoryID,                │
│     ClickMarketPP,                        │
│     ClickMarketCategoryName,              │
│     ClickMarketPPName,                    │
│     ClickAWAPSCampaignName,               │
│     ClickPageName,                        │
│     ClickTargetType,                      │
│     ClickTargetPhraseID,                  │
│     ClickContextType,                     │
│     ClickSelectType,                      │
│     ClickOptions,                         │
│     ClickGroupBannerID,                   │
│     OpenstatServiceName,                  │
│     OpenstatCampaignID,                   │
│     OpenstatAdID,                         │
│     OpenstatSourceID,                     │
│     UTMSource,                            │
│     UTMMedium,                            │
│     UTMCampaign,                          │
│     UTMContent,                           │
│     UTMTerm,                              │
│     FromTag,                              │
│     HasGCLID,                             │
│     FirstVisit,                           │
│     PredLastVisit,                        │
│     LastVisit,                            │
│     TotalVisits,                          │
│     `TraficSource.ID`,                    │
│     `TraficSource.SearchEngineID`,        │
│     `TraficSource.AdvEngineID`,           │
│     `TraficSource.PlaceID`,               │
│     `TraficSource.SocialSourceNetworkID`, │
│     `TraficSource.Domain`,                │
│     `TraficSource.SearchPhrase`,          │
│     `TraficSource.SocialSourcePage`,      │
│     Attendance,                           │
│     CLID,                                 │
│     YCLID,                                │
│     NormalizedRefererHash,                │
│     SearchPhraseHash,                     │
│     RefererDomainHash,                    │
│     NormalizedStartURLHash,               │
│     StartURLDomainHash,                   │
│     NormalizedEndURLHash,                 │
│     TopLevelDomain,                       │
│     URLScheme,                            │
│     OpenstatServiceNameHash,              │
│     OpenstatCampaignIDHash,               │
│     OpenstatAdIDHash,                     │
│     OpenstatSourceIDHash,                 │
│     UTMSourceHash,                        │
│     UTMMediumHash,                        │
│     UTMCampaignHash,                      │
│     UTMContentHash,                       │
│     UTMTermHash,                          │
│     FromHash,                             │
│     WebVisorEnabled,                      │
│     WebVisorActivity,                     │
│     `ParsedParams.Key1`,                  │
│     `ParsedParams.Key2`,                  │
│     `ParsedParams.Key3`,                  │
│     `ParsedParams.Key4`,                  │
│     `ParsedParams.Key5`,                  │
│     `ParsedParams.ValueDouble`,           │
│     `Market.Type`,                        │
│     `Market.GoalID`,                      │
│     `Market.OrderID`,                     │
│     `Market.OrderPrice`,                  │
│     `Market.PP`,                          │
│     `Market.DirectPlaceID`,               │
│     `Market.DirectOrderID`,               │
│     `Market.DirectBannerID`,              │
│     `Market.GoodID`,                      │
│     `Market.GoodName`,                    │
│     `Market.GoodQuantity`,                │
│     `Market.GoodPrice`,                   │
│     IslandID                              │
│ FROM datasets.visits_v1                   │
│ ORDER BY                                  │
│     UserID ASC,                           │
│     VisitID ASC                           │
└───────────────────────────────────────────┘
186 rows in set. Elapsed: 0.004 sec. 

 

7.删除重复的limit by key

例如下面的语句，重复声明的 name 字段会被去重：

EXPLAIN SYNTAX
SELECT *
FROM datasets.visits_v1
LIMIT 3 BY
    VisitID,
    VisitID
LIMIT 10
Query id: cfb47dca-ebf5-48c2-a913-032d2f054cb9
┌─explain───────────────────────────────────┐
│ SELECT                                    │
│     CounterID,                            │
│     StartDate,                            │
│     Sign,                                 │
│     IsNew,                                │
│     VisitID,                              │
│     UserID,                               │
│     StartTime,                            │
│     Duration,                             │
│     UTCStartTime,                         │
│     PageViews,                            │
│     Hits,                                 │
│     IsBounce,                             │
│     Referer,                              │
│     StartURL,                             │
│     RefererDomain,                        │
│     StartURLDomain,                       │
│     EndURL,                               │
│     LinkURL,                              │
│     IsDownload,                           │
│     TraficSourceID,                       │
│     SearchEngineID,                       │
│     SearchPhrase,                         │
│     AdvEngineID,                          │
│     PlaceID,                              │
│     RefererCategories,                    │
│     URLCategories,                        │
│     URLRegions,                           │
│     RefererRegions,                       │
│     IsYandex,                             │
│     GoalReachesDepth,                     │
│     GoalReachesURL,                       │
│     GoalReachesAny,                       │
│     SocialSourceNetworkID,                │
│     SocialSourcePage,                     │
│     MobilePhoneModel,                     │
│     ClientEventTime,                      │
│     RegionID,                             │
│     ClientIP,                             │
│     ClientIP6,                            │
│     RemoteIP,                             │
│     RemoteIP6,                            │
│     IPNetworkID,                          │
│     SilverlightVersion3,                  │
│     CodeVersion,                          │
│     ResolutionWidth,                      │
│     ResolutionHeight,                     │
│     UserAgentMajor,                       │
│     UserAgentMinor,                       │
│     WindowClientWidth,                    │
│     WindowClientHeight,                   │
│     SilverlightVersion2,                  │
│     SilverlightVersion4,                  │
│     FlashVersion3,                        │
│     FlashVersion4,                        │
│     ClientTimeZone,                       │
│     OS,                                   │
│     UserAgent,                            │
│     ResolutionDepth,                      │
│     FlashMajor,                           │
│     FlashMinor,                           │
│     NetMajor,                             │
│     NetMinor,                             │
│     MobilePhone,                          │
│     SilverlightVersion1,                  │
│     Age,                                  │
│     Sex,                                  │
│     Income,                               │
│     JavaEnable,                           │
│     CookieEnable,                         │
│     JavascriptEnable,                     │
│     IsMobile,                             │
│     BrowserLanguage,                      │
│     BrowserCountry,                       │
│     Interests,                            │
│     Robotness,                            │
│     GeneralInterests,                     │
│     Params,                               │
│     `Goals.ID`,                           │
│     `Goals.Serial`,                       │
│     `Goals.EventTime`,                    │
│     `Goals.Price`,                        │
│     `Goals.OrderID`,                      │
│     `Goals.CurrencyID`,                   │
│     WatchIDs,                             │
│     ParamSumPrice,                        │
│     ParamCurrency,                        │
│     ParamCurrencyID,                      │
│     ClickLogID,                           │
│     ClickEventID,                         │
│     ClickGoodEvent,                       │
│     ClickEventTime,                       │
│     ClickPriorityID,                      │
│     ClickPhraseID,                        │
│     ClickPageID,                          │
│     ClickPlaceID,                         │
│     ClickTypeID,                          │
│     ClickResourceID,                      │
│     ClickCost,                            │
│     ClickClientIP,                        │
│     ClickDomainID,                        │
│     ClickURL,                             │
│     ClickAttempt,                         │
│     ClickOrderID,                         │
│     ClickBannerID,                        │
│     ClickMarketCategoryID,                │
│     ClickMarketPP,                        │
│     ClickMarketCategoryName,              │
│     ClickMarketPPName,                    │
│     ClickAWAPSCampaignName,               │
│     ClickPageName,                        │
│     ClickTargetType,                      │
│     ClickTargetPhraseID,                  │
│     ClickContextType,                     │
│     ClickSelectType,                      │
│     ClickOptions,                         │
│     ClickGroupBannerID,                   │
│     OpenstatServiceName,                  │
│     OpenstatCampaignID,                   │
│     OpenstatAdID,                         │
│     OpenstatSourceID,                     │
│     UTMSource,                            │
│     UTMMedium,                            │
│     UTMCampaign,                          │
│     UTMContent,                           │
│     UTMTerm,                              │
│     FromTag,                              │
│     HasGCLID,                             │
│     FirstVisit,                           │
│     PredLastVisit,                        │
│     LastVisit,                            │
│     TotalVisits,                          │
│     `TraficSource.ID`,                    │
│     `TraficSource.SearchEngineID`,        │
│     `TraficSource.AdvEngineID`,           │
│     `TraficSource.PlaceID`,               │
│     `TraficSource.SocialSourceNetworkID`, │
│     `TraficSource.Domain`,                │
│     `TraficSource.SearchPhrase`,          │
│     `TraficSource.SocialSourcePage`,      │
│     Attendance,                           │
│     CLID,                                 │
│     YCLID,                                │
│     NormalizedRefererHash,                │
│     SearchPhraseHash,                     │
│     RefererDomainHash,                    │
│     NormalizedStartURLHash,               │
│     StartURLDomainHash,                   │
│     NormalizedEndURLHash,                 │
│     TopLevelDomain,                       │
│     URLScheme,                            │
│     OpenstatServiceNameHash,              │
│     OpenstatCampaignIDHash,               │
│     OpenstatAdIDHash,                     │
│     OpenstatSourceIDHash,                 │
│     UTMSourceHash,                        │
│     UTMMediumHash,                        │
│     UTMCampaignHash,                      │
│     UTMContentHash,                       │
│     UTMTermHash,                          │
│     FromHash,                             │
│     WebVisorEnabled,                      │
│     WebVisorActivity,                     │
│     `ParsedParams.Key1`,                  │
│     `ParsedParams.Key2`,                  │
│     `ParsedParams.Key3`,                  │
│     `ParsedParams.Key4`,                  │
│     `ParsedParams.Key5`,                  │
│     `ParsedParams.ValueDouble`,           │
│     `Market.Type`,                        │
│     `Market.GoalID`,                      │
│     `Market.OrderID`,                     │
│     `Market.OrderPrice`,                  │
│     `Market.PP`,                          │
│     `Market.DirectPlaceID`,               │
│     `Market.DirectOrderID`,               │
│     `Market.DirectBannerID`,              │
│     `Market.GoodID`,                      │
│     `Market.GoodName`,                    │
│     `Market.GoodQuantity`,                │
│     `Market.GoodPrice`,                   │
│     IslandID                              │
│ FROM datasets.visits_v1                   │
│ LIMIT 3 BY VisitID                        │
│ LIMIT 10                                  │
└───────────────────────────────────────────┘
185 rows in set. Elapsed: 0.004 sec. 

 

8.删除重复的USING Key

例如下面的语句，重复的关联键 id 字段会被去重：

EXPLAIN SYNTAX
SELECT
    a.UserID,
    a.UserID,
    b.VisitID,
    a.URL,
    b.UserID
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v1 AS b USING (UserID, UserID)
Query id: 83d2ea38-4d51-48fd-8674-618eb1379d33
┌─explain──────────────────────────────────────────────┐
│ SELECT                                               │
│     UserID,                                          │
│     UserID,                                          │
│     VisitID,                                         │
│     URL,                                             │
│     b.UserID                                         │
│ FROM datasets.hits_v1 AS a                           │
│ ALL LEFT JOIN datasets.visits_v1 AS b USING (UserID) │
└──────────────────────────────────────────────────────┘
8 rows in set. Elapsed: 0.003 sec.

 

9.标量替换

如果子查询只返回一行数据，在被引用的时候用标量替换，例如下面语句中的total_disk_usage 字段：

EXPLAIN SYNTAX
WITH (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10
Query id: 499e49b3-62e8-4a26-afd0-e3936d8c43f7
┌─explain─────────────────────────────────────────────────────────────────────────┐
│ WITH identity(_CAST(0, 'Nullable(UInt64)')) AS total_disk_usage                 │
│ SELECT                                                                          │
│     (sum(bytes_on_disk AS bytes) / total_disk_usage) * 100 AS table_disk_usage, │
│     table                                                                       │
│ FROM system.parts                                                               │
│ GROUP BY table                                                                  │
│ ORDER BY table_disk_usage DESC                                                  │
│ LIMIT 10                                                                        │
└─────────────────────────────────────────────────────────────────────────────────┘
8 rows in set. Elapsed: 0.003 sec. 

 

10.三元运算优化

如果开启了 optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multiIf 函数，例如：

EXPLAIN SYNTAX
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'fengzhen'))
FROM numbers(10)
Query id: 9601b406-349b-4af8-bccf-8ccfd62d8638
┌─explain─────────────────────────────────────────────────────────────┐
│ SELECT if(number = 1, 'hello', if(number = 2, 'world', 'fengzhen')) │
│ FROM numbers(10)                                                    │
└─────────────────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.001 sec. 

 

开启multiif：

set optimize_if_chain_to_multiif = 1;

 

EXPLAIN SYNTAX
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'fengzhen'))
FROM numbers(10)
Query id: 36d98f1c-d0b1-4ecb-952f-1a7a8393d187
┌─explain──────────────────────────────────────────────────────────────┐
│ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'fengzhen') │
│ FROM numbers(10)                                                     │
└──────────────────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.001 sec.