Understanding MySQL Cost Model for Index Optimization and Conflict Resolution
This article explains MySQL's cost‑based optimizer, demonstrates how to calculate query costs for full‑table, covering, ref and range scans using actual source‑code constants, and applies the model to resolve index‑conflict cases in a store‑goods table, offering practical optimization guidelines and future tool ideas.
The author introduces the background problem of unpredictable index usage in MySQL and motivates the need to understand the MySQL cost model to evaluate different query plans before adding new indexes.
MySQL is described as a four‑layer architecture (connection, service, engine, storage) and the cost‑based optimizer (CBO) is explained as the component that selects the lowest‑cost execution plan using a cost formula that combines CPU cost and I/O cost.
Key cost constants are shown from opt_costconstants.cc and opt_costmodel.cc , for example: const double Server_cost_constants::ROW_EVALUATE_COST = 0.2; const double Server_cost_constants::KEY_COMPARE_COST = 0.1; const double SE_cost_constants::MEMORY_BLOCK_READ_COST = 1.0; const double SE_cost_constants::IO_BLOCK_READ_COST = 1.0; These constants are used by the optimizer to compute CPU and I/O components.
Formulas for different access types are derived and validated with real data. For a full‑table scan the cost is calculated as pages * IO_COST + rows * ROW_EVALUATE_COST , yielding 20446.40 for the example table. Similar calculations are provided for covering‑index scans, ref scans, and range scans, each with their own derived formulas and verification queries.
A concrete case study follows: the store_goods_center table suffers from index conflict because the original unique index (station_no, sku_id) forces a filesort when paginating by id . Adding a new index (station_no, id) resolves most slow queries, but some still use the old index. The analysis shows that MySQL chooses the index with the smallest estimated row count; when the row counts are similar (e.g., id > -1 ) the optimizer may pick either index, leading to inconsistent behavior.
The article concludes with recommendations: merge competing indexes when possible, adjust pagination logic to use the new index, and develop a tool that automatically parses MySQL metadata, applies the cost model, predicts index conflicts, and suggests optimal index designs.
JD Retail Technology
Official platform of JD Retail Technology, delivering insightful R&D news and a deep look into the lives and work of technologists.
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.