session: add a variable to limit rows examined per SELECT query #66925

Feature Request

Is your feature request related to a problem? Please describe:

In production OLTP clusters, poorly-optimized or ad-hoc SELECT queries can accidentally perform full table scans, examining millions of rows and saturating coprocessor threads across multiple TiKV nodes. Today there are two mechanisms to guard against this:

• max_execution_time — a time-based limit that is imprecise: the same query may finish within budget on fast NVMe storage but time out on contended storage. It says
  nothing about how much data was touched.
• Runaway queries (PROCESSED_KEYS threshold in ALTER RESOURCE GROUP) — a cluster-level DBA tool that requires resource groups to be configured and assigned. It
  cannot be overridden per query, and its enforcement happens after TiKV has already scanned and transmitted the rows.

There is no lightweight, self-service, application-level control that gives developers a deterministic bound on storage-level work — independent of hardware speed, without DBA involvement, and with per-query granularity.

Describe the feature you'd like:

Add a tidb_max_rows_read session variable (and SET_VAR hint support) that limits the number of rows examined by the storage engine during a SELECT query. When the limit is exceeded the query is terminated with an error, similar to max_execution_time.

-- Session default: protect all queries in this connection
SET SESSION tidb_max_rows_read = 100000;

-- Per-query override via hint (no DBA required)
SELECT /*+ SET_VAR(tidb_max_rows_read=500000) */ *
FROM orders WHERE status = 'pending';

-- Disable for one specific expensive query
SELECT /*+ SET_VAR(tidb_max_rows_read=0) */ *
FROM analytics WHERE date > '2024-01-01';

When the limit is reached:
ERROR 8270 (HY000): Query execution was interrupted due to reaching tidb_max_rows_read limit

The transaction remains active; only the statement is aborted, consistent with max_execution_time behavior.

A companion read-only status variable tidb_rows_examined would expose the cumulative row count for the session, reset by FLUSH STATUS.

SHOW STATUS LIKE 'tidb_rows_examined';

The limit should be pushed down to TiKV's coprocessor layer so TiKV can stop scanning early, saving CPU, disk I/O, and network bandwidth. This is the key efficiency advantage over the existing PROCESSED_KEYS runaway check, which enforces only at the TiDB layer after TiKV has already scanned.

Describe alternatives you've considered:

1. max_execution_time — already exists but is time-based and hardware-dependent. Does not bound storage work.
2. Runaway queries (PROCESSED_KEYS) — cluster-level DBA tool. Cannot be set per-session or per-query by the application developer. Enforcement is TiDB-side only (no TiKV pushdown). Both features address different governance layers and can coexist.
3. LIMIT N clause — limits result rows, not scanned rows. A SELECT count(*) ... LIMIT 1 still scans the full table.

Teachability, Documentation, Adoption, Migration Strategy:

This feature is directly inspired by MariaDB's LIMIT ROWS EXAMINED clause, which lets users write:
https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/limit-rows-examined

-- MariaDB syntax
SELECT * FROM t1, t2 LIMIT 10 ROWS EXAMINED 10000;

Adoption path:

• Zero-config: set SET GLOBAL tidb_max_rows_read = N to protect all new sessions.
• Application-level: SET SESSION tidb_max_rows_read = N at connection open time.
• Query-level: annotate individual high-risk queries with /*+ SET_VAR(tidb_max_rows_read=N) */ in application code.
• Observability: use SHOW STATUS LIKE 'tidb_rows_examined' to measure how many rows each session has scanned, enabling data-driven query optimization.

Tasks:

• spec: design: Max Rows Read Limit for SELECT Queries #66926
• kvproto: copr: add max_keys_read coprocessor Request kvproto#1447
• tidb: sessionctx/variable: add tidb_max_keys_read #68047
• tikv: implement tidb_max_keys_read tikv/tikv#19518