Using Explain to Analyze and Optimize Distributed SQL Queries in DBLE
This article demonstrates how to use the Explain feature in DBLE to visualize distributed query plans, compare join orders across multiple SQL statements, identify Cartesian products and ordered joins, and apply optimization insights for better performance in multi‑table queries.
Hello everyone, in previous sessions we introduced the usage of Explain; in this session we provide a detailed overview of the Explain functionality.
Explain can be used to analyze distributed query plans and view the results of the dispatched SQL, and it also helps with SQL tuning.
We present an example with three tables and three SQL statements that differ only in the order of tables (a‑b‑c, c‑a‑b, b‑c‑a). While MySQL treats these as equivalent, DBLE may generate different execution plans, which we examine using Explain.
The Explain result for the first SQL is lengthy; we visualize it as a tree where the original statement is the root.
In this plan, table A first joins with B (left‑deep join), then the intermediate result joins with C. The dispatch order (X_A) to the four nodes, subsequent rename operations, and handling of sub‑queries are also shown.
Comparing the three trees, A and B have similar structures, while C differs. A’s first join requires a Cartesian product because there is no direct ON condition between A and B; it must convert the product into ordered sequences before joining with C. In contrast, B joins with C first, avoiding the Cartesian product.
The third graph is simpler, involving only one join because B and C include a global table that can be partially optimized; the join is performed on the MySQL node rather than the middleware.
Key takeaways: (1) Avoid placing tables without join relationships together early in the SQL, as DBLE’s equivalence optimization may select a driver table based on statement order, leading to costly Cartesian products. (2) Use ordered joins to let MySQL handle sorting and prevent middleware Cartesian products. (3) Consider table affinity; the join order in the SQL influences how DBLE builds the execution set.
That concludes today’s introduction.
DBLE and related project code repositories:
https://github.com/actiontech/dble
https://github.com/actiontech/dble-docs-cn
https://github.com/actiontech/dble-test-suite
Course consultation: WeChat public account “ActiontechOSS”, official technical group (669663113).
Aikesheng Open Source Community
The Aikesheng Open Source Community provides stable, enterprise‑grade MySQL open‑source tools and services, releases a premium open‑source component each year (1024), and continuously operates and maintains them.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.