搜尋已建立索引的資料
本頁面提供在 BigQuery 中搜尋資料表資料的範例。建立資料索引時，BigQuery 可針對使用 SEARCH 函式或其他函式和運算子 (例如 =、IN、LIKE 和 STARTS_WITH) 的部分查詢進行最佳化。
SQL 查詢會從所有攝入的資料中傳回正確結果，即使部分資料尚未建立索引也一樣。不過，透過索引可大幅提升查詢效能。當搜尋結果數量占資料表中總列數的比例相對較小時，處理的位元組數和時段毫秒數的節省量會達到最高，因為系統掃描的資料較少。如要判斷查詢是否使用索引，請參閱搜尋索引用法。
建立搜尋索引
下表名為 Logs，用於說明使用 SEARCH 函式的不同方式。這個範例表格相當小，但在實際情況下，使用 SEARCH 所獲得的效能提升會隨著表格大小而改善。
CREATE TABLE my_dataset.Logs (Level STRING, Source STRING, Message STRING) AS ( SELECT 'INFO' as Level, '65.177.8.234' as Source, 'Entry Foo-Bar created' as Message UNION ALL SELECT 'WARNING', '132.249.240.10', 'Entry Foo-Bar already exists, created by 65.177.8.234' UNION ALL SELECT 'INFO', '94.60.64.181', 'Entry Foo-Bar deleted' UNION ALL SELECT 'SEVERE', '4.113.82.10', 'Entry Foo-Bar does not exist, deleted by 94.60.64.181' UNION ALL SELECT 'INFO', '181.94.60.64', 'Entry Foo-Baz created' );
資料表如下所示：
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | INFO | 65.177.8.234 | Entry Foo-Bar created | | WARNING | 132.249.240.10 | Entry Foo-Bar already exists, created by 65.177.8.234 | | INFO | 94.60.64.181 | Entry Foo-Bar deleted | | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | | INFO | 181.94.60.64 | Entry Foo-Baz created | +---------+----------------+-------------------------------------------------------+
使用預設文字分析器，在 Logs 資料表上建立搜尋索引：
CREATE SEARCH INDEX my_index ON my_dataset.Logs(ALL COLUMNS);
如要進一步瞭解搜尋索引，請參閱「管理搜尋索引」。
使用 SEARCH 函式
SEARCH 函式可針對資料提供權杖化搜尋功能。SEARCH 可搭配索引使用，以便最佳化查詢。您可以使用 SEARCH 函式搜尋整個資料表，或將搜尋範圍限制在特定欄。
搜尋整個資料表
以下查詢會在 Logs 資料表的所有資料欄中搜尋值 bar，並傳回包含此值的資料列，不論大小寫為何。由於搜尋索引會使用預設的文字分析器，因此您不需要在 SEARCH 函式中指定該分析器。
SELECT * FROM my_dataset.Logs WHERE SEARCH(Logs, 'bar');
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | INFO | 65.177.8.234 | Entry Foo-Bar created | | WARNING | 132.249.240.10 | Entry Foo-Bar already exists, created by 65.177.8.234 | | INFO | 94.60.64.181 | Entry Foo-Bar deleted | | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | +---------+----------------+-------------------------------------------------------+
以下查詢會在 Logs 資料表的所有欄中搜尋值 `94.60.64.181`，並傳回包含此值的資料列。反引號可進行精確搜尋，因此 Logs 資料表中包含 181.94.60.64 的最後一列會遭到省略。
SELECT * FROM my_dataset.Logs WHERE SEARCH(Logs, '`94.60.64.181`');
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | INFO | 94.60.64.181 | Entry Foo-Bar deleted | | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | +---------+----------------+-------------------------------------------------------+
搜尋部分資料欄
SEARCH 可讓您輕鬆指定要搜尋資料的資料欄子集。以下查詢會搜尋 Logs 資料表的 Message 欄，找出 94.60.64.181 值，並傳回含有此值的資料列。
SELECT * FROM my_dataset.Logs WHERE SEARCH(Message, '`94.60.64.181`');
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | +---------+----------------+-------------------------------------------------------+
下列查詢會搜尋 Logs 資料表的 Source 和 Message 欄。會傳回包含任一欄 94.60.64.181 值的資料列。
SELECT * FROM my_dataset.Logs WHERE SEARCH((Source, Message), '`94.60.64.181`');
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | INFO | 94.60.64.181 | Entry Foo-Bar deleted | | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | +---------+----------------+-------------------------------------------------------+
從搜尋結果中排除資料欄
如果表格有許多欄，而您想搜尋其中大部分的欄，建議只指定要從搜尋中排除的欄，這樣會比較容易。以下查詢會搜尋 Logs 資料表的所有資料欄，但不包含 Message 資料欄。它會傳回 Message 以外任何資料欄的資料列，且該資料欄包含 94.60.64.181 值。
SELECT * FROM my_dataset.Logs WHERE SEARCH( (SELECT AS STRUCT Logs.* EXCEPT (Message)), '`94.60.64.181`');
+---------+----------------+---------------------------------------------------+ | Level | Source | Message | +---------+----------------+---------------------------------------------------+ | INFO | 94.60.64.181 | Entry Foo-Bar deleted | +---------+----------------+---------------------------------------------------+
使用其他文字分析器
以下範例會建立名為 contact_info 的資料表，其中索引會使用 NO_OP_ANALYZER
文字分析器：
CREATE TABLE my_dataset.contact_info (name STRING, email STRING) AS ( SELECT 'Kim Lee' AS name, 'kim.lee@example.com' AS email UNION ALL SELECT 'Kim' AS name, 'kim@example.com' AS email UNION ALL SELECT 'Sasha' AS name, 'sasha@example.com' AS email ); CREATE SEARCH INDEX noop_index ON my_dataset.contact_info(ALL COLUMNS) OPTIONS (analyzer = 'NO_OP_ANALYZER');
+---------+---------------------+ | name | email | +---------+---------------------+ | Kim Lee | kim.lee@example.com | | Kim | kim@example.com | | Sasha | sasha@example.com | +---------+---------------------+
下列查詢會搜尋 name 欄中的 Kim，以及 email 欄中的 kim。由於搜尋索引不會使用預設的文字分析器，因此您必須將分析器的名稱傳遞至 SEARCH 函式。
SELECT name, SEARCH(name, 'Kim', analyzer=>'NO_OP_ANALYZER') AS name_Kim, email, SEARCH(email, 'kim', analyzer=>'NO_OP_ANALYZER') AS email_kim FROM my_dataset.contact_info;
NO_OP_ANALYZER 不會修改文字，因此 SEARCH 函式只會針對完全相符的情況傳回 TRUE：
+---------+----------+---------------------+-----------+ | name | name_Kim | email | email_kim | +---------+----------+---------------------+-----------+ | Kim Lee | FALSE | kim.lee@example.com | FALSE | | Kim | TRUE | kim@example.com | FALSE | | Sasha | FALSE | sasha@example.com | FALSE | +---------+----------+---------------------+-----------+
設定文字分析器選項
您可以將 JSON 格式字串新增至設定選項，自訂 LOG_ANALYZER 和 PATTERN_ANALYZER 文字分析器。您可以在 SEARCH 函式、CREATE
SEARCH INDEX DDL 陳述式和 TEXT_ANALYZE 函式中設定文字分析器。
以下範例會建立名為 complex_table 的資料表，其中的索引會使用 LOG_ANALYZER 文字分析器。它會使用 JSON 格式字串設定分析器選項：
CREATE TABLE dataset.complex_table( a STRING, my_struct STRUCT<string_field STRING, int_field INT64>, b ARRAY<STRING> ); CREATE SEARCH INDEX my_index ON dataset.complex_table(a, my_struct, b) OPTIONS (analyzer = 'LOG_ANALYZER', analyzer_options = '''{ "token_filters": [ { "normalization": {"mode": "NONE"} } ] }''');
下表列出使用不同文字分析器呼叫 SEARCH 函式的範例，以及這些呼叫的結果。第一個表格會使用預設文字分析器 LOG_ANALYZER 呼叫 SEARCH 函式：
| 函式呼叫 | 傳回 | 原因 |
|---|---|---|
| SEARCH("foobarexample", NULL) | 錯誤 | search_terms 為 `NULL`。 |
| SEARCH('foobarexample', '') | 錯誤 | search_terms 不含任何符記。 |
| SEARCH('foobar-example', 'foobar example') | TRUE | '-' 和 ' ' 是分隔符號。 |
| SEARCH('foobar-example', 'foobarexample') | FALSE | 未拆分 search_terms。 |
| SEARCH('foobar-example', 'foobar\\&example') | TRUE | 雙斜線會將連字號視為轉義字元，而連字號是分隔符。 |
| SEARCH('foobar-example', R'foobar\&example') | TRUE | 單斜線會在原始字串中逸出 & 符號。 |
| SEARCH('foobar-example', '`foobar&example`') | FALSE | 反引號必須與 foobar&example 完全相符。 |
| SEARCH('foobar&example', '`foobar&example`') | TRUE | 找到完全相符的結果。 |
| SEARCH('foobar-example', 'example foobar') | TRUE | 條款的順序不拘。 |
| SEARCH('foobar-example', 'foobar example') | TRUE | 符號會轉換為小寫。 |
| SEARCH('foobar-example', '`foobar-example`') | TRUE | 找到完全相符的結果。 |
| SEARCH('foobar-example', '`foobar`') | FALSE | 反引號會保留大小寫。 |
| SEARCH(``foobar-example`', ``foobar-example`') | FALSE | 反引號對 data_to_search 和 |
| SEARCH('foobar@example.com', '`example.com`') | TRUE | 在 data_to_search 中，系統會在分隔符後面尋找完全比對項目。 |
| SEARCH('a foobar-example b', '`foobar-example`') | TRUE | 在空格分隔符之間找到完全比對項目。 |
| SEARCH(['foobar', 'example'], 'foobar example') | FALSE | 沒有任何一個陣列項目符合所有搜尋字詞。 |
| SEARCH('foobar=', '`foobar\\=`') | FALSE | search_terms 等同於 foobar\=。 |
| SEARCH('foobar=', R'`foobar\=`') | FALSE | 這與前一個範例相同。 |
| SEARCH('foobar=', 'foobar\\=') | TRUE | 等號是資料和查詢中的分隔符。 |
| SEARCH('foobar=', R'foobar\=') | TRUE | 這與前一個範例相同。 |
| SEARCH('foobar.example', '`foobar`') | TRUE | 找到完全相符的結果。 |
| SEARCH('foobar.example', '`foobar.`') | FALSE | 系統不會分析 `foobar.`，因為它包含反引號， |
| SEARCH('foobar..example', '`foobar.`') | TRUE | 系統不會分析 `foobar.`，因為它有反尖括號； |
下表列出使用 NO_OP_ANALYZER 文字分析器呼叫 SEARCH 函式的範例，以及各種傳回值的原因：
| 函式呼叫 | 退貨 | 原因 |
|---|---|---|
| SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER') | TRUE | 找到完全相符的結果。 |
| SEARCH('foobar', '`foobar`', analyzer=>'NO_OP_ANALYZER') | FALSE | 反引號並非 NO_OP_ANALYZER 的特殊字元。 |
| SEARCH('Foobar', 'foobar', analyzer=>'NO_OP_ANALYZER') | FALSE | 大小寫不一致。 |
| SEARCH('foobar example', 'foobar', analyzer=>'NO_OP_ANALYZER') | FALSE | NO_OP_ANALYZER 沒有分隔符。 |
| SEARCH('', '', analyzer=>'NO_OP_ANALYZER') | TRUE | NO_OP_ANALYZER 沒有分隔符。 |
其他運算子和函式
您可以使用多個運算子、函式和述詞，執行搜尋索引最佳化。
使用運算子和比較函式進行最佳化
BigQuery 可針對使用等於運算子 (=)、IN 運算子、LIKE 運算子 或 STARTS_WITH 函式 來比較字串文字常值與索引資料的部分查詢進行最佳化。
使用字串述詞進行最佳化
下列述詞可用於搜尋索引最佳化：
column_name = 'string_literal''string_literal' = column_namestruct_column.nested_field = 'string_literal'string_array_column[OFFSET(0)] = 'string_literal'string_array_column[ORDINAL(1)] = 'string_literal'column_name IN ('string_literal1', 'string_literal2', ...)STARTS_WITH(column_name, 'prefix')column_name LIKE 'prefix%'
使用數值述詞進行最佳化
如果搜尋索引是使用數值資料類型建立，BigQuery 就能針對使用等於運算子 (=) 或 IN 運算子搭配索引資料的部分查詢進行最佳化。下列述詞可用於搜尋索引最佳化：
INT64(json_column.int64_field) = 1int64_column = 1int64_array_column[OFFSET(0)] = 1int64_column IN (1, 2)struct_column.nested_int64_field = 1struct_column.nested_timestamp_field = TIMESTAMP "2024-02-15 21:31:40"timestamp_column = "2024-02-15 21:31:40"timestamp_column IN ("2024-02-15 21:31:40", "2024-02-16 21:31:40")
最佳化產生索引資料的函式
當特定函式套用至已編入索引的資料時，BigQuery 會支援搜尋索引最佳化。如果搜尋索引使用預設的 LOG_ANALYZER 文字分析器，您可以將 UPPER 或 LOWER 函式套用至資料欄，例如 UPPER(column_name) = 'STRING_LITERAL'。
如果是從已編入索引的 JSON 資料欄中擷取的 JSON 純量字串資料，您可以套用 STRING 函式或其安全版本 SAFE.STRING。如果擷取的 JSON 值不是字串，STRING 函式會產生錯誤，SAFE.STRING 函式則會傳回 NULL。
對於已編入索引的 JSON 格式 STRING (而非 JSON) 資料，您可以套用下列函式：
舉例來說，假設您有以下名為 dataset.person_data 的索引資料表，其中包含 JSON 和 STRING 欄：
+----------------------------------------------------------------+-----------------------------------------+
| json_column | string_column |
+----------------------------------------------------------------+-----------------------------------------+
| { "name" : "Ariel", "email" : "cloudysanfrancisco@gmail.com" } | { "name" : "Ariel", "job" : "doctor" } |
+----------------------------------------------------------------+-----------------------------------------+
下列查詢符合最佳化資格：
SELECT * FROM dataset.person_data WHERE SAFE.STRING(json_column.email) = 'cloudysanfrancisco@gmail.com';
SELECT * FROM dataset.person_data WHERE JSON_VALUE(string_column, '$.job') IN ('doctor', 'lawyer', 'teacher');
這些函式的組合 (例如 UPPER(JSON_VALUE(json_string_expression)) = 'FOO') 也經過最佳化。
搜尋索引用途
如要判斷查詢是否使用搜尋索引，您可以查看工作詳細資料，或查詢其中一個 INFORMATION_SCHEMA.JOBS* 檢視畫面。
查看工作詳細資料
在查詢結果的工作資訊中，索引使用模式和未使用索引的原因欄位會提供詳細的搜尋索引使用情形。
您也可以透過 Jobs.Get API 方法中的 searchStatistics 欄位取得搜尋索引使用情形的相關資訊。searchStatistics 中的 indexUsageMode 欄位會指出搜尋索引是否與下列值搭配使用：
UNUSED：未使用任何搜尋索引。PARTIALLY_USED：部分查詢使用搜尋索引，部分則未使用。FULLY_USED：查詢中的每個SEARCH函式都使用了搜尋索引。
當 indexUsageMode 為 UNUSED 或 PARTIALLY_USED 時，indexUnusuedReasons 欄位會包含為何在查詢中未使用搜尋索引的相關資訊。
如要查看查詢的 searchStatistics，請執行 bq show 指令。
bq show --format=prettyjson -j JOB_ID
範例
假設您執行的查詢會對資料表中的資料呼叫 SEARCH 函式。您可以查看查詢的工作詳細資料，找出工作 ID，然後執行 bq show 指令，查看更多資訊：
bq show --format=prettyjson --j my_project:US.bquijob_123x456_789y123z456c
輸出內容包含許多欄位，包括 searchStatistics，如下所示。在這個範例中，indexUsageMode 表示未使用索引。原因是資料表沒有搜尋索引。如要解決這個問題，請在資料表上建立搜尋索引。如要瞭解為什麼搜尋索引可能無法用於查詢，請參閱 indexUnusedReason code 欄位。
"searchStatistics": {
"indexUnusedReasons": [
{
"baseTable": {
"datasetId": "my_dataset",
"projectId": "my_project",
"tableId": "my_table"
},
"code": "INDEX_CONFIG_NOT_AVAILABLE",
"message": "There is no search index configuration for the base table `my_project:my_dataset.my_table`."
}
],
"indexUsageMode": "UNUSED"
},
查詢 INFORMATION_SCHEMA 檢視表
您也可以在下列檢視畫面中，查看某個區域中多個職缺的搜尋索引使用情形：
INFORMATION_SCHEMA.JOBSINFORMATION_SCHEMA.JOBS_BY_USERINFORMATION_SCHEMA.JOBS_BY_FOLDERINFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
以下查詢會顯示過去 7 天內所有可改善搜尋索引的查詢，以及這些查詢的索引使用情形：
SELECT job_id, search_statistics.index_usage_mode, index_unused_reason.code, index_unused_reason.base_table.table_id, index_unused_reason.index_name FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS, UNNEST(search_statistics.index_unused_reasons) AS index_unused_reason WHERE end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP();
結果大致如下：
+-----------+----------------------------------------+-----------------------+ | job_id | index_usage_mode | code | table_id | index_name | +-----------+------------------+---------------------+-----------------------+ | bquxjob_1 | UNUSED | BASE_TABLE_TOO_SMALL| my_table | my_index | | bquxjob_2 | FULLY_USED | NULL | my_table | my_index | +-----------+----------------------------------------+-----------------------+
最佳做法
下列各節將說明搜尋的最佳做法。
選擇性搜尋
搜尋結果越少，搜尋功能的效益就越高。請盡量使用具體的搜尋字詞。
最佳化 ORDER BY LIMIT
如果您在非常大型的分區資料表上使用 SEARCH、=、IN、LIKE 或 STARTS_WITH 的查詢，只要在分區欄位上使用 ORDER BY 子句和 LIMIT 子句，即可進行最佳化。如果查詢不含 SEARCH 函式，您可以使用其他運算子和函式來充分利用最佳化功能。無論資料表是否已編入索引，系統都會套用最佳化設定。如果您搜尋的是常見字詞，這項功能就很實用。舉例來說，假設先前建立的 Logs 資料表是在名為 day 的額外 DATE 類型資料欄上進行分區。以下查詢已最佳化：
SELECT Level, Source, Message FROM my_dataset.Logs WHERE SEARCH(Message, "foo") ORDER BY day LIMIT 10;
設定搜尋範圍
使用 SEARCH 函式時，請只搜尋資料表中預期包含搜尋字詞的資料欄。這麼做可提升效能，並減少需要掃描的位元組數量。
使用反引號
當您將 SEARCH 函式與 LOG_ANALYZER 文字分析器搭配使用時，如果將搜尋查詢括在反引號內，系統就會強制進行完全比對。如果搜尋內容有大小寫之分，或包含不應解讀為分隔符的字元，這項功能就很實用。舉例來說，如要搜尋 IP 位址 192.0.2.1，請使用 `192.0.2.1`。如果沒有使用反引號，搜尋結果會傳回任何包含個別符記 192、0、2 和 1 的資料列，不限順序。