How DBLE Implements MySQL View Functionality

This article explains how DBLE, a PaaS component team’s middleware, implements MySQL view functionality, covering MySQL view concepts, MySQL's own implementation methods, and DBLE's specific implementation.

MySQL introduced views in version 5.0; a view is a virtual table that stores no data and returns results generated from underlying tables. MySQL implements views using two algorithms: the MERGE algorithm and the TEMPTABLE algorithm.

MERGE algorithm : The view's SQL is merged with the outer query SQL and executed as a single statement, effectively rewriting the view back into the original query.

TEMPTABLE algorithm : The view is materialized into a temporary table during query execution, and the outer query operates on that temporary table.

When a view is created without explicitly specifying an algorithm, MySQL prefers the MERGE algorithm, but switches to the TEMPTABLE algorithm if the view contains constructs such as GROUP BY , DISTINCT , aggregate functions, UNION , or subqueries that prevent a one‑to‑one mapping. The chosen algorithm can be verified with EXPLAIN ; if the select_type of the plan is "DERIVED", the TEMPTABLE algorithm is used.

DBLE implements view handling in two scenarios:

Push‑down: DBLE pushes the view‑related SQL directly to the downstream MySQL instance, storing only metadata locally.

Non‑push‑down: DBLE materializes the view logic internally, merges the view SQL with the outer query, and executes it similarly to MySQL's MERGE algorithm.

The decision between push‑down and non‑push‑down is based on whether the logical schema is marked as nosharding ; only schemas without sharding configuration allow push‑down, making push‑down cases relatively rare.

In summary, the article details DBLE's approach to implementing MySQL views, including the underlying MySQL concepts, the two MySQL algorithms, and DBLE's own push‑down versus non‑push‑down mechanisms, helping readers understand DBLE's view handling.