The MySQL Query Optimizer Explained Through Optimizer Trace ystein - PowerPoint PPT Presentation

The MySQL Query Optimizer Explained Through Optimizer Trace Øystein Grøvlen Senior Staff Engineer Alibaba Cloud 1

MySQL Query Optimizer JOIN SELECT a, b FROM t1, t2, t3 Query Table JOIN WHERE t1.a = t2.b scan Optimizer AND t2.b = t3.c Range Ref AND t2.d > 20 t1 scan access AND t2.d < 30; t2 t3 Statistics Table/index info (storage engines) (data dictionary) 2

MySQL Architecture Parser SQL query Resolver (Prepare): Semantic check,name resolution Optimizer Logical transformations Cost-based optimizer: Join order and access methods Plan refinement Query execution plan Query result Query execution Storage Engine InnoDB MyISAM 3

Optimizer Trace How to generate it • EXPLAIN shows the selected plan • Optimizer trace shows WHY the plan was selected SET optimizer_trace= " enabled=on " ; SELECT * FROM t1, t2 WHERE f1=1 AND f1=f2 AND f2>0; SELECT trace FROM information_schema.optimizer_trace INTO OUTFILE filename LINES TERMINATED BY ’’; SET optimizer_trace="enabled=off"; QUERY SELECT * FROM t1, t2 WHERE f1=1 AND f1=f2 AND f2>0; { "steps": [ { " join_preparation": { "select#": 1,… } … } …] } TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE 0 INSUFFICIENT_PRIVILEGES 0 4

Optimizer Trace Example { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f1` AS `f1`,`t2`.`f2` AS `f2` from `t1` join `t2` where ((`t1`.`f1` = 1) and (`t1`.`f1` = `t2`.`f2`) and (`t2`.`f2` > 0))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`f1` = 1) and (`t1`.`f1` = `t2`.`f2`) and (`t2`.`f2` > 0))", "steps": [ { … 5

Optimizer Trace JSON browser plugin 6

Browser Plugin Collapse to see main trace objects/phases 7

Browser Plugin Expand JSON objects 8

Prepare Phase • Name resolving • Map names to database objects (tables, columns, …) • Semantic checks • Permanent transformations: Simpler query to • Conversion of outer join to inner join optimize and • Merging of views and derived tables execute • Subquery transformations • IN to EXISTS Prepare for later • IN to Semijoin (5.6) optimizations • EXISTS to IN (8.0.16) • Etc. 9

Conversion of outer join to inner join SELECT o_orderkey FROM orders LEFT JOIN lineitem ON o_orderkey = l_orderkey WHERE l_discount > 0.10; "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `orders`.`o_orderkey` AS `o_orderkey` from (`orders` left join `lineitem` on ((`orders`.`o_orderkey` = `lineitem`.`l_orderkey`))) where (`lineitem`.`l_discount` > 0.10)" }, { "transformations_to_nested_joins": { "transformations": [ "outer_join_to_inner_join", "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `orders`.`o_orderkey` AS `o_orderkey` from `orders` join `lineitem` where ((`lineitem`.`l_discount` > 0.10) and(`orders`.`o_orderkey` = `lineitem`.`l_orderkey`))" } … 10

Conversion from EXISTS-subquery to IN-subquery New in 8.0.16 SELECT o_orderpriority, COUNT(*) AS order_count FROM orders WHERE EXISTS (SELECT * FROM lineitem WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate) GROUP BY o_orderpriority ORDER BY o_orderpriority; SELECT o_orderpriority, COUNT(*) AS order_count FROM orders WHERE o_orderkey IN (SELECT l_orderkey FROM lineitem WHERE l_commitdate < l_receiptdate GROUP BY o_orderpriority ORDER BY o_orderpriority; SELECT o_orderpriority, COUNT(*) AS order_count FROM orders SEMIJOIN lineitem ON l_orderkey = o_orderkey WHERE l_commitdate < l_receiptdate GROUP BY o_orderpriority ORDER BY o_orderpriority; 11

Conversion from EXISTS-subquery to IN-subquery Optimizer trace "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 from `lineitem` where ((`lineitem`.`l_orderkey` = `orders`.`o_orderkey`) and (`lineitem`.`l_commitDATE` < `lineitem`.`l_receiptDATE`))" }, { "transformation": { "select#": 2, "from": "EXISTS (SELECT)", "to": "semijoin", "chosen": true } } ] 12 …

Conversion from EXISTS-subquery to IN-subquery Optimizer trace, cont. 13

Query Optimization Main phases Parser Resolver: Semantic check,name resolution Negation elimination Optimizer Ref access analysis Equality and constant propagation Range access analysis Evaluation of constant expressions Logical transformations Estimation of condition fan out Substitution of generated columns Prepare for cost-based Constant table detection optimization Cost-based optimizer: Join order and access methods Table condition pushdown Access method selection Plan refinement Access method adjustments Join order Sort avoidance Index condition pushdown Query execution Prepare temporary tables plan Query execution Storage engine InnoDB MyISAM 14

Query Optimization Main phases Parser Resolver: Semantic check,name resolution Negation elimination Optimizer Ref access analysis Equality and constant propagation Range access analysis Evaluation of constant expressions Logical transformations Estimation of condition fan out Substitution of generated columns Prepare for cost-based Constant table detection optimization Cost-based optimizer: Join order and access methods Table condition pushdown Access method selection Plan refinement Access method adjustments Join order Sort avoidance Index condition pushdown Query execution Prepare temporary tables plan Query execution Storage engine InnoDB MyISAM 15

Logical Transformations Condition processing SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a = 9 AND (NOT (t1.a > 10 OR t2.b > 3) OR (t1.b = t2.b + 7 AND t2.b = 5)); Negation elimination t1.a = t2.a AND t2.a = 9 AND (t1.a <= 10 AND t2.b <= 3 OR (t1.b = t2.b + 7 AND t2.b = 5)); Equality/const propagation t1.a = 9 AND t2.a = 9 AND (9 <= 10 AND t2.b <= 3 OR (t1.b = 5 + 7 AND t2.b = 5)); Evaluate const expressions t1.a = 9 AND t2.a = 9 AND (9 <= 10 AND t2.b <= 3 OR (t1.b = 12 AND t2.b = 5)); Trivial condition removal =TRUE t1.a = 9 AND t2.a = 9 AND (t2.b <= 3 OR (t1.b = 12 AND t2.b = 5)); 16

Logical Transformations Optimizer Trace "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`a` = `t2`.`a`) and (`t2`.`a` = 9) and (((`t1`.`a` <= 10) and (`t2`.`b` <= 3)) or ((`t1`.`b` =(`t2`.`b` + 7)) and (`t2`.`b` = 5))))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((((9 <= 10) and (`t2`.`b` <= 3)) or ((`t1`.`b` = (5 + 7)) and multiple equal(5, `t2`.`b`))) and multiple equal(9, `t1`.`a`, `t2`.`a`))" }, { "transformation": "constant_propagation", "resulting_condition": "((((9 <= 10) and (`t2`.`b` <= 3)) or ((`t1`.`b` = 12) and multiple equal(5, `t2`.`b`))) and multiple equal(9, `t1`.`a` , `t2`.`a`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(((`t2`.`b` <= 3) or ((`t1`.`b` = 12) and multiple equal(5, `t2`.`b`))) and multiple equal(9, `t1`.`a`, `t2`.`a`))" }

Query Optimization Main phases Parser Resolver: Semantic check,name resolution Negation elimination Optimizer Ref access analysis Equality and constant propagation Range access analysis Evaluation of constant expressions Logical transformations Estimation of condition fan out Substitution of generated columns Prepare for cost-based Constant table detection optimization Cost-based optimizer: Join order and access methods Table condition pushdown Access method selection Plan refinement Access method adjustments Join order Sort avoidance Index condition pushdown Query execution Prepare temporary tables plan Query execution Storage engine InnoDB MyISAM 18