BigQuery未分区表为何能规避全列扫描？是否可依赖该特性？

现象描述 #

我有一张未分区、未聚类的BigQuery表，包含约42.5万行数据，时间跨度1-2年。原本计划按timestamp字段分区，却发现一个特殊现象：查询表现和分区表一致——timestamp取值越晚，查询处理的数据量越少、速度越快：

SELECT meta FROM `project.dataset.table` where timestamp > '2024-07-09'
-- 处理数据量：3.25Mb

SELECT meta FROM `project.dataset.table` where timestamp > '2024-06-30'
-- 处理数据量：13Mb

SELECT meta FROM `project.dataset.table` where timestamp > '2024-06-15'
-- 处理数据量：26.81 MB

该表压缩效率极高：

逻辑总字节数：2.06 GB
物理总字节数：86.6 MB

核心问题 #

1. 这张表既未分区也未聚类，为什么能跳过全列扫描，只处理符合timestamp条件的数据？
2. 这种行为是否可靠？能不能完全依赖这个特性，跳过表的分区设置？

之前我一直认为，分区（分区剪枝）和聚类是避免全列扫描的唯一方法。

表规格信息 #

bq show --format=prettyjson project:dataset.table
{
  "creationTime": "1660725581097",
  "etag": "PNomRWba1MQFgVDSGcrgiQ==",
  "id": "project:dataset.table",
  "kind": "bigquery#table",
  "lastModifiedTime": "1720490034640",
  "location": "region",
  "numActiveLogicalBytes": "2213392702",
  "numActivePhysicalBytes": "90803671",
  "numBytes": "2213392702",
  "numCurrentPhysicalBytes": "90803671",
  "numLongTermBytes": "0",
  "numLongTermLogicalBytes": "0",
  "numLongTermPhysicalBytes": "0",
  "numRows": "425332",
  "numTimeTravelPhysicalBytes": "0",
  "numTotalLogicalBytes": "2213392702",
  "numTotalPhysicalBytes": "90803671",
  "schema": {
    "fields": [
      {
        "description": "特征提取ID",
        "mode": "REQUIRED",
        "name": "id",
        "type": "STRING"
      },
      {
        "description": "实体ID（哈希系统标识）",
        "mode": "REQUIRED",
        "name": "entity_id",
        "type": "STRING"
      },
      {
        "description": "采集器（检查）运行ID",
        "mode": "REQUIRED",
        "name": "collector_run_id",
        "type": "STRING"
      },
      {
        "description": "特征提取运行时间戳",
        "mode": "REQUIRED",
        "name": "timestamp",
        "type": "TIMESTAMP"
      },
      {
        "description": "特征提取运行元数据",
        "mode": "REQUIRED",
        "name": "meta",
        "type": "JSON"
      },
      {
        "description": "提取的特征",
        "mode": "REQUIRED",
        "name": "features",
        "type": "JSON"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/project/datasets/dataset/tables/table",
  "tableReference": {
    "datasetId": "dataset",
    "projectId": "project",
    "tableId": "table"
  },
  "type": "TABLE"
}

查询作业详情 #

bq show -j --format=prettyjson project:region.bquxjob_562108fe_190966d854d
{
  "configuration": {
    "jobType": "QUERY",
    "query": {
      "destinationTable": {
        "datasetId": "_c7a721d4b70d2392c6643caa52c5ecf0fa067025",
        "projectId": "project",
        "tableId": "anoncb8970105fa30adbf37868493309113c88adcb284ef581320a4553c0eb2d1a66"
      },
      "priority": "INTERACTIVE",
      "query": "SELECT meta FROM `project.dataset.table` where timestamp > '2024-06-30'",
      "useLegacySql": false,
      "writeDisposition": "WRITE_TRUNCATE"
    }
  },
  "etag": "g7fZ9ss84zKxUWA8PXpjZg==",
  "id": "project:region.bquxjob_562108fe_190966d854d",
  "jobCreationReason": {
    "code": "REQUESTED"
  },
  "jobReference": {
    "jobId": "bquxjob_562108fe_190966d854d",
    "location": "region",
    "projectId": "project"
  },
  "kind": "bigquery#job",
  "principal_subject": "user:user1",
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/project/jobs/bquxjob_562108fe_190966d854d?location=region",
  "statistics": {
    "creationTime": "1720510678481",
    "endTime": "1720510679673",
    "finalExecutionDurationMs": "681",
    "query": {
      "billingTier": 1,
      "cacheHit": false,
      "estimatedBytesProcessed": "206178878",
      "metadataCacheStatistics": {
        "tableMetadataCacheUsage": [
          {
            "explanation": "表必须分区并聚类才能使用CMETA",
            "tableReference": {
              "datasetId": "dataset",
              "projectId": "project",
              "tableId": "table"
            },
            "unusedReason": "OTHER_REASON"
          }
        ]
      },
      "performanceInsights": {
        "avgPreviousExecutionMs": "1358"
      },
      "queryPlan": [
        {
          "completedParallelInputs": "127",
          "computeMode": "BIGQUERY",
          "computeMsAvg": "19",
          "computeMsMax": "125",
          "computeRatioAvg": 0.05,
          "computeRatioMax": 0.32894736842105265,
          "endMs": "1720510678747",
          "id": "0",
          "name": "S00: Input",
          "parallelInputs": "127",
          "readMsAvg": "4",
          "readMsMax": "10",
          "readRatioAvg": 0.010526315789473684,
          "readRatioMax": 0.02631578947368421,
          "recordsRead": "21038",
          "recordsWritten": "21038",
          "shuffleOutputBytes": "17148416",
          "shuffleOutputBytesSpilled": "0",
          "slotMs": "287",
          "startMs": "1720510678666",
          "status": "COMPLETE",
          "steps": [
            {
              "kind": "READ",
              "substeps": [
                "$2:meta, $1:timestamp",
                "FROM project.dataset.table",
                "WHERE greater($1, 1719705600.000000000)"
              ]
            },
            {
              "kind": "WRITE",
              "substeps": [
                "$2",
                "TO __stage00_output"
              ]
            }
          ],
          "waitMsAvg": "36",
          "waitMsMax": "67",
          "waitRatioAvg": 0.09473684210526316,
          "waitRatioMax": 0.1763157894736842,
          "writeMsAvg": "2",
          "writeMsMax": "5",
          "writeRatioAvg": 0.005263157894736842,
          "writeRatioMax": 0.013157894736842105
        },
        {
          "completedParallelInputs": "1",
          "computeMode": "BIGQUERY",
          "computeMsAvg": "380",
          "computeMsMax": "380",
          "computeRatioAvg": 1,
          "computeRatioMax": 1,
          "endMs": "1720510679148",
          "id": "1",
          "inputStages": [
            "0"
          ],
          "name": "S01: Output",
          "parallelInputs": "1",
          "readMsAvg": "0",
          "readMsMax": "0",
          "readRatioAvg": 0,
          "readRatioMax": 0,
          "recordsRead": "21038",
          "recordsWritten": "21038",
          "shuffleOutputBytes": "9601578",
          "shuffleOutputBytesSpilled": "0",
          "slotMs": "526",
          "startMs": "1720510678843",
          "status": "COMPLETE",
          "steps": [
            {
              "kind": "READ",
              "substeps": [
                "$2",
                "FROM __stage00_output"
              ]
            },
            {
              "kind": "WRITE",
              "substeps": [
                "$2",
                "TO __stage01_output"
              ]
            }
          ],
          "waitMsAvg": "93",
          "waitMsMax": "93",
          "waitRatioAvg": 0.24473684210526317,
          "waitRatioMax": 0.24473684210526317,
          "writeMsAvg": "76",
          "writeMsMax": "76",
          "writeRatioAvg": 0.2,
          "writeRatioMax": 0.2
        }
      ],
      "referencedTables": [
        {
          "datasetId": "dataset",
          "projectId": "project",
          "tableId": "table"
        }
      ],
      "statementType": "SELECT",
      "timeline": [
        {
          "activeUnits": "0",
          "completedUnits": "128",
          "elapsedMs": "595",
          "estimatedRunnableUnits": "0",
          "pendingUnits": "0",
          "totalSlotMs": "814"
        },
        {
          "completedUnits": "128",
          "elapsedMs": "657",
          "estimatedRunnableUnits": "0",
          "pendingUnits": "0",
          "totalSlotMs": "814"
        }
      ],
      "totalBytesBilled": "13631488",
      "totalBytesProcessed": "13004234",
      "totalPartitionsProcessed": "0",
      "totalSlotMs": "814",
      "transferredBytes": "0"
    },
    "startTime": "1720510678570",
    "totalBytesProcessed": "13004234",
    "totalSlotMs": "814"
  },
  "status": {
    "state": "DONE"
  },
  "user_email": "user1"
}

问题解答 #

1. 为什么非分区表能实现类分区剪枝？ #

这是BigQuery的列存储结构和数据布局优化共同作用的结果：

• BigQuery采用列存储，每一列的数据被分割成多个"列分片"存储。如果你的数据是按timestamp递增顺序写入的，晚时间戳的数据会集中在较新的列分片里。
• 执行带timestamp过滤的查询时，BigQuery的查询优化器可以通过统计信息（比如每列分片的最小/最大timestamp值）快速判断哪些分片不包含符合条件的数据，直接跳过这些分片的读取，只加载包含目标数据的分片。
• 高压缩率也有辅助作用：压缩后的列分片体积更小，即使读取也更快，进一步强化了"速度随时间范围缩小变快"的感知。

从作业详情的查询计划也能佐证这一点：recordsRead只有21038行，远小于总表的42.5万行，说明确实只读取了符合条件的数据分片。

2. 这种行为是否可靠？能否替代分区？ #

不可靠，不能替代分区：

• 这种优化完全依赖数据的写入顺序。如果后续数据是乱序写入的（比如插入旧时间戳的数据），列分片的时间戳范围会变得混乱，优化器无法再精准跳过分片，查询会退化为全列扫描。
• 分区是显式的、有保障的优化机制：分区表会强制按指定字段（比如timestamp）组织数据，无论写入顺序如何，分区剪枝都会稳定生效。
• 非分区表的这种优化没有官方承诺的稳定性，BigQuery的优化器逻辑可能在版本更新中调整，无法保证长期一致的表现。
• 分区表还能提供其他优势：比如按分区管理数据（删除旧分区、设置不同存储层级），这些是非分区表做不到的。

综上，这种现象是列存储特性带来的"意外"优化，但不能作为长期依赖，建议还是按原计划将表改为按timestamp分区。

内容的提问来源于stack exchange，提问作者Anton Golubev