Mysql性能优化


Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | How to Analyze and Tune MySQL Queries for Better Performance Øystein Grøvlen Senior Principal Software Engineer MySQL Optimizer Team, Oracle Copyright © 2015, Oracle and/or its affiliates. All rights reserved. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to cost-based query optimization in MySQL Selecting data access methods (index selection) Join optimizer Subquery optimizations Ordering and aggregation (sorting and grouping) Tools for monitoring, analyzing, and tuning queries Influencing the Optimizer 1 2 3 4 5 6 7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to cost-based query optimization in MySQL Selecting data access methods (index selection) Join optimizer Subquery optimizations Ordering and aggregation (sorting and grouping) Tools for monitoring, analyzing, and tuning queries Influencing the Optimizer 1 2 3 4 5 6 7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Optimizer SELECT a, b FROM t1, t2, t3 WHERE t1.a = t2.b AND t2.b = t3.c AND t2.d > 20 AND t2.d < 30; MySQL Server Cost based optimizations Heuristics Cost Model Optimizer Table/index info (data dictionary) Statistics (storage engines) t2 t3 t1 Table scan Range scan Ref access JOIN JOIN Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Architecture Optimizer Logical transformations Cost-based optimizer: Join order and access methods Plan refinement Query execution plan Query execution Parser Resolver: Semantic check,name resolution Storage Engine InnoDB MyISAM SQL query Query result Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Optimizer Characteristics • Produce the query plan that uses least resources – IO and CPU • Optimizes a single query – No inter-query optimizations • Produces left-deep linear query execution plan JOIN JOIN Table scan Ref access t1 t2 t3 Range scan JOIN Table scan t4 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Optimizer Overview Main phases Optimizer Logical transformations Cost-based optimizer: Join order and access methods Plan refinement Query execution plan Query execution Parser Resolver: Semantic check,name resolution Storage engine InnoDB MyISAM Prepare for cost-based optimization Negation elimination Equality and constant propagation Evaluation of constant expressions Conversions of outer to inner join Subquery transformation Ref access analysis Range access analysis Estimation of condition fan out Constant table detection Table condition pushdown Access method adjustments Sort avoidance Index condition pushdown Access method selection Join order Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Cost-based Query Optimization • Assign cost to operations • Assign cost to partial or alternative plans • Search for plan with lowest cost • Cost-based optimizations: General idea Access method Subquery strategy Join order t2 t3 t1 Table scan Range scan Ref access JOIN JOIN Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Optimizer Cost Model t1 Cost estimate Row estimate Cost Model Cost formulas Access methods Join Subquery Cost constants CPU IO Metadata: - Record and index size - Index information - Uniqueness Statistics: - Table size - Cardinality - Range estimates Cost model configuration Range scan JOIN New in MySQL 5.7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | • The cost for executing a query • Cost unit: –“read a random data page from disk” • Main cost factors: – IO cost: •#pages read from table •#pages read from index – CPU cost: •Evaluating query conditions • Comparing keys/records • Sorting keys • Main cost constants: Cost Estimates Cost Default value Reading a random disk page 1.0 Reading a data page from memory buffer 1.0 Evaluating query condition 0.2 Comparing key/record 0.1 New in MySQL 5.7: Configurable Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | • IO-cost: – Estimates from storage engine based on number of pages to read – Both index and data pages • Schema: – Length of records and keys – Uniqueness for indexes – Nullability • Statistics: – Number of records in table – Key distribution/Cardinality: • Average number of records per key value • Only for indexed columns • Maintained by storage engine – Number of records in an index range Input to Cost Model Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | InnoDB Persistent Statistics (MySQL 5.6) • More accurate statistics – New algorithm for sampling – Less variance between servers • More stable statistics (Will not be changed by restart) • Turned on by default • Automatically recalculates statistics after significant changes – May turn off automatic recalculations • ANALYZE TABLE forces recalculation of statistics • May increase precision by changing number of samples – innodb_stats_persistent_sample_pages Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Cost Model Example Table scan: • IO-cost: #pages in table * IO_BLOCK_READ_COST • CPU cost: #records * ROW_EVALUATE_COST Range scan (on secondary index): • IO-cost: #records_in_range * IO_BLOCK_READ_COST • CPU cost: #records_in_range * ROW_EVALUATE_COST SELECT SUM(o_totalprice) FROM orders WHERE o_orderdate BETWEEN '1994-01-01' AND '1994-12-31'; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Cost Model EXPLAIN SELECT SUM(o_totalprice) FROM orders WHERE o_orderdate BETWEEN '1994-01-01' AND '1994-12-31'; Example EXPLAIN SELECT SUM(o_totalprice) FROM orders WHERE o_orderdate BETWEEN '1994-01-01' AND '1994-06-30'; id select type table type possible keys key key len ref rows extra 1 SIMPLE orders ALL i_o_orderdate NULL NULL NULL 15000000 Using where Id select type table type possible keys key key len ref rows extra 1 SIMPLE orders range i_o_orderdate i_o_orderdate 4 NULL 2235118 Using index condition Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Cost Model Example: Optimizer Trace join_optimization / row_estimation / table : orders / range_analysis "table_scan": { "rows": 15000000, "cost": 3.12e6 } /* table_scan */, "potential_range_indices": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable“ }, { "index": "i_o_orderdate", "usable": true, "key_parts": [ "o_orderDATE", "o_orderkey" ] } ] /* potential_range_indices */, … "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i_o_orderdate", "ranges": [ "1994-01-01 <= o_orderDATE <= 1994-12-31" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 4489990, "cost": 5.39e6, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, … } /* analyzing_range_alternatives */ Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Cost Model vs Real World Data in Memory Data on Disk Data on SSD Table scan 6.8 seconds 36 seconds 15 seconds Index scan 5.2 seconds 2.5 hours 30 minutes Measured Execution Times Force Index Scan: SELECT SUM(o_totalprice) FROM orders FORCE INDEX (i_o_orderdate) WHERE o_orderdate BETWEEN '1994-01-01' AND '1994-12-31‘; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Performance Schema SELECT event_name, count_read, avg_timer_read/1000000000.0 “Avg Read Time (ms)”, sum_number_of_bytes_read “Bytes Read” FROM performance_schema.file_summary_by_event_name WHERE event_name='wait/io/file/innodb/innodb_data_file'; Disk I/O event_name count_read Avg Read Time (ms) Bytes Read wait/io/file/innodb/innodb_data_file 2188853 4.2094 35862167552 event_name count_read Avg Read Time (ms) Bytes Read wait/io/file/innodb/innodb_data_file 115769 0.0342 1896759296 Index Scan Table Scan Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to cost-based query optimization in MySQL Selecting data access methods (index selection) Join optimizer Subquery optimizations Ordering and aggregation (sorting and grouping) Tools for monitoring, analyzing, and tuning queries Influencing the Optimizer 1 2 3 4 5 6 7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Selecting Access Method • For each table, find the best access method: – Check if the access method is useful – Estimate cost of using access method – Select the cheapest to be used • Choice of access method is cost based Finding the optimal method to read data from storage engine Main access methods: . Table scan . Index scan . Index look-up (ref access) . Range scan . Index merge . Loose index scan Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Ref Access • Read all records with a given key value using an index: • Examples: SELECT * FROM t1 WHERE t1.key = 7; SELECT * FROM t1, t2 WHERE t1.key = t2.key; •“eq_ref”: – Reading from a unique index, max one record returned •“ref”: – Reading from a non-unique index or a prefix of an index, multiple records returned Index look-up Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Ref Access EXPLAIN SELECT * FROM customer WHERE c_custkey = 570887; Single Table Queries id select type table type possible keys key key len ref rows extra 1 SIMPLE customer const PRIMARY PRIMARY 4 const 1 NULL EXPLAIN SELECT * FROM orders WHERE o_orderdate = ‘1992-09-12’; id select type table type possible keys key key len ref rows extra 1 SIMPLE orders ref i_o_orderdate i_o_orderdate 4 const 6271 NULL Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Ref Access Analysis Determine which indexes that can be used for ref access in a join SELECT City.Name AS Capital, Language FROM CountryLanguage, Country, City WHERE City.CountryCode = Country.Code AND City.ID = Country.Capital AND CountryLanguage.Country = Country.Code CountryLanguage Country Country Code Capital City CountryCode ID Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Ref Access Join Queries EXPLAIN SELECT * FROM orders JOIN customer ON c_custkey = o_custkey WHERE o_orderdate = ‘1992-09-12’; Id select type table type possible keys key key len ref rows extra 1 SIMPLE orders ref i_o_orderdate, i_o_custkey i_o_orderdate 4 const 6271 Using where 1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders. o_custkey 1 NULL Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Ref Access Join Queries, continued EXPLAIN SELECT * FROM orders JOIN customer ON c_custkey = o_custkey WHERE c_acctbal < -1000; Id select type table type possible keys key key len ref rows extra 1 SIMPLE customer ALL PRIMARY NULL NULL NULL 1500000 Using where 1 SIMPLE orders ref i_o_custkey i_o_custkey 5 dbt3.customer. c_custkey 7 NULL Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Optimizer • Goal: find the “minimal” ranges for each index that needs to be read • Example: SELECT * FROM t1 WHERE (key1 > 10 AND key1 < 20) AND key2 > 30 • Range scan using INDEX(key1): • Range scan using INDEX(key2): 10 20 30 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Optimizer, cont. • Range optimizer selects the “useful” parts of the WHERE condition: – Conditions comparing a column value with a constant: – Nested AND/OR conditions are supported • Result: list of disjoint ranges that need to be read from index: • Cost estimate based on number of records in each range: – Record estimate is found by asking the Storage Engine (“index dives”) key > 3 key = 4 key IS NULL key BETWEEN 4 AND 6 key LIKE ”abc%” key IN (10,12,..) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Optimizer "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i_a", "ranges": [ "10 < a < 11", "11 < a < 19", "19 < a < 25" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 6.61, "chosen": true }, { "index": "i_b", "ranges": [ "NULL < b < 5", "10 < b" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, … Optimizer Trace show ranges SELECT a, b FROM t1 WHERE a > 10 AND a < 25 AND a NOT IN (11, 19)) AND (b < 5 OR b > 10); Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Optimizer: Case Study SELECT * FROM orders WHERE YEAR(o_orderdate) = 1997 AND MONTH(o_orderdate) = 5 AND o_clerk LIKE '%01866'; Why table scan? id select type table type possible keys key key len ref rows extra 1 SIMPLE orders ALL NULL NULL NULL NULL 15000000 Using where Index not considered mysql> SELECT * FROM orders WHERE year(o_orderdate) = 1997 AND MONTH(… ... 15 rows in set (8.91 sec) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Some Reasons Why Index can not be Used • Indexed column is used as argument to function YEAR(o_orderdate) = 1997 • Looking for a suffix: name LIKE ’%son’ • First column(s) of compound index NOT used b = 10 when index defined over (a, b) • Type mismatch my_string = 10 • Character set / collation mismatch t1 LEFT JOIN t2 ON t1.utf8_string = t2. latin1_string Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Optimizer: Case Study SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' AND '1997-05-31' AND o_clerk LIKE '%01866'; Rewrite query to avoid functions on indexed columns id select type table type possible keys key key len ref rows extra 1 SIMPLE orders range i_o_orderdate i_o_orderdate 4 NULL 376352 Using index condition; Using where mysql> SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' AND … ... 15 rows in set (0.91 sec) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Optimizer: Case Study CREATE INDEX i_o_clerk ON orders(o_clerk); SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' AND '1997-05-31' AND o_clerk LIKE '%01866'; Adding another index id select type table type possible keys key key len ref rows extra 1 SIMPLE orders range i_o_orderdate i_o_orderdate 4 NULL 376352 Using index condition; Using where New index not considered Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Optimizer: Case Study SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' AND '1997-05-31' AND o_clerk = 'Clerk#000001866'; Rewrite query, again id select type table type possible keys key key len ref rows extra 1 SIMPLE orders range i_o_orderdate, i_o_clerk i_o_clerk 16 NULL 1504 Using index condition; Using where mysql> SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' AND … ... 15 rows in set (0.01 sec) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Access for Multi-part Index • Table: • INDEX idx(a, b, c); • Logical storage layout of index: Example table with multi-part index 10 1 2 3 4 5 10 11 1 2 3 4 5 12 1 2 3 4 5 13 1 2 3 4 5 a b c 11 12 pk a b c Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Access for Multi-part Index, cont • Equality on 1st index part? – Can add condition on 2nd index part in range condition • Example: SELECT * from t1 WHERE a IN (10,11,13) AND (b=2 OR b=4) • Resulting range scan: 10 1 2 3 4 5 11 1 2 3 4 5 12 1 2 3 4 5 13 1 2 3 4 5 a b c 2 4 2 4 2 4 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Access for Multi-part Index, cont • Non-Equality on 1st index part: – Can NOT add condition on 2nd index part in range condition • Example: SELECT * from t1 WHERE a > 10 AND a < 13 AND (b=2 OR b=4) • Resulting range scan: 10 1 2 3 4 5 11 1 2 3 4 5 12 1 2 3 4 5 13 1 2 3 4 5 a b c a >10 AND a < 13 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Range Optimizer: Case Study CREATE INDEX i_o_clerk_date ON orders(o_clerk, o_orderdate); SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' AND '1997-05-31' AND o_clerk = 'Clerk#000001866'; Create multi-column index id select type table type possible keys key key len ref rows extra 1 SIMPLE orders range i_o_orderdate, i_o_clerk, i_o_clerk_date i_o_clerk_date 20 NULL 14 Using index condition mysql> SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' AND … ... 15 rows in set (0.00 sec) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Performance Schema: Query History UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_statements_history'; mysql> SELECT sql_text, (timer_wait)/1000000000.0 “Time (ms)”, rows_examined FROM performance_schema.events_statements_history ORDER BY timer_start; +---------------------------------------------------------------+--------+------+ | sql_text | Time (ms) | rows_examined | +---------------------------------------------------------------+--------+------+ | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 8.1690 | 1505 | | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 7.2120 | 1505 | | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 8.1613 | 1505 | | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 7.0535 | 1505 | | CREATE INDEX i_o_clerk_date ON orders(o_clerk,o_orderdate) |82036.4190 | 0 | | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 0.7259 | 15 | | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 0.5791 | 15 | | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 0.5423 | 15 | | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 0.6031 | 15 | | SELECT * FROM orders WHERE o_orderdate BETWEEN '1997-05-01' … | 0.2710 | 15 | +---------------------------------------------------------------+--------+------+ MySQL 5.7: Enabled by default Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Index Merge • Uses multiple indexes on the same table • Implemented index merge strategies: – Index Merge Union • OR-ed conditions between different indexes – Index Merge Intersection • AND conditions between different indexes – Index Merge Sort-Union • OR-ed conditions where condition is a range Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Index Merge Union • Single index cannot handle ORed conditions on different columns • Example: SELECT * FROM t1 WHERE a=10 OR b=10 • Index Merge Union: 10 INDEX(a) 10 INDEX(b) a=10 OR b=10 Result: Union Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Index Merge Intersection • Combine several indexes to reduce number of (or avoid) accesses to base table for ANDed conditions • Example: SELECT * FROM t1 WHERE a=10 AND b=10 • Index Merge Intersection: 10 INDEX(a) 10 INDEX(b) a=10 AND b=10 Result: Intersection Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Index Merge Intersection: Example 1 SELECT COUNT(*) FROM lineitem WHERE l_shipdate = '1997-05-01' AND l_commitdate = '1997-05-01'; id select type table type possible keys key key len ref rows extra 1 SIMPLE lineitem index_ merge i_l_shipdate, i_l_commitdate i_l_shipdate, i_l_commitdate 4,4 NULL 43 Using intersect (i_l_shipdate, i_l_commitdate); Using where; Using index mysql> SELECT COUNT(*) FROM lineitem WHERE l_shipdate = '1997-05-01 … ... 1 row in set (0.02 sec) mysql> SET optimizer_switch='index_merge_intersection=off'; mysql> SELECT COUNT(*) FROM lineitem WHERE l_shipdate = '1997-05-01 … ... 1 row in set (0.11 sec) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Index Merge Intersection: Example 2 SELECT count(*) FROM user WHERE user_type=2 AND status=1 AND parent_id=0; Beware of low-selectivity indexes! id select type table type possible keys key key len ref rows extra 1 SIMPLE user index_ merge parent_id, status, user_type user_type, status, parent_id 1,1,4 NULL 3696 Using intersect (user_type, status, parent_id); Using where; Using index mysql> SELECT count(*) FROM user WHERE user_type=2 AND status=1 … ... 1 row in set (5.33 sec) mysql> SELECT count(*) FROM user USE INDEX (user_type) WHERE user_type=2 … ... 1 row in set (0.09 sec) Low selectivity Source: http://www.mysqlperformanceblog.com/2012/12/14/ the-optimization-that-often-isnt-index-merge-intersection/ Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Index Merge Intersection: Example 2 mysql> FLUSH STATUS; mysql> SELECT count(*) FROM user WHERE user_type=2 AND status=1 AND parent_id=0; ... 1 row in set (5.28 sec) mysql> SHOW STATUS LIKE 'Handler_read%'; +-----------------------+----------+ | Variable_name | Value | +-----------------------+----------+ | Handler_read_first | 0 | | Handler_read_key | 3 | | Handler_read_last | 0 | | Handler_read_next | 15825904 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+----------+ Handler status variables mysql> FLUSH STATUS; mysql> SELECT count(*) FROM user USE INDEX (user_id) WHERE user_type=2 AND status=1 AND parent_id=0; ... 1 row in set (0.09 sec) mysql> SHOW STATUS LIKE 'Handler_read%‘; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 23312 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to cost-based query optimization in MySQL Selecting data access methods (index selection) Join optimizer Subquery optimizations Ordering and aggregation (sorting and grouping) Tools for monitoring, analyzing, and tuning queries Influencing the Optimizer 1 2 3 4 5 6 7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Join Optimizer • Goal: Given a JOIN of N tables, find the best JOIN ordering • Strategy: – Start with all 1-table plans (Sorted based on size and key dependency) – Expand each plan with remaining tables • Depth-first – If “cost of partial plan” > “cost of best plan”: •“prune” plan – Heuristic pruning: • Prune less promising partial plans • May in rare cases miss most optimal plan (turn off with set optimizer_prune_level = 0) ”Greedy search strategy” t1 t2 t2 t2 t2 t3 t3 t3 t4 t4 t4 t4 t4 t3 t3 t2 t4 t2 t3 N! possible plans Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | JOIN Optimizer Illustrated SELECT City.Name, Language FROM Language, Country, City WHERE City.CountryCode = Country.Code AND City.ID = Country.Capital AND City.Population >= 1000000 AND Language.Country = Country.Code; Language Country City Language Country Country City City City City cost=26568 cost=32568 cost=627 cost=1245 cost=862 start Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Join Optimizer: Case study SELECT o_year, SUM(CASE WHEN nation = 'FRANCE' THEN volume ELSE 0 END) / SUM(volume) AS mkt_share FROM ( SELECT EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n2.n_name AS nation FROM part JOIN lineitem ON p_partkey = l_partkey JOIN supplier ON s_suppkey = l_suppkey JOIN orders ON l_orderkey = o_orderkey JOIN customer ON o_custkey = c_custkey JOIN nation n1 ON c_nationkey = n1.n_nationkey JOIN region ON n1.n_regionkey = r_regionkey JOIN nation n2 ON s_nationkey = n2.n_nationkey WHERE r_name = 'EUROPE’ AND o_orderdate BETWEEN '1995-01-01' AND '1996-12-31’ AND p_type = 'PROMO BRUSHED STEEL' ) AS all_nations GROUP BY o_year ORDER BY o_year; DBT-3 Query 8: National Market Share Query Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Join Optimizer: Case Study MySQL Workbench: Visual EXPLAIN Execution time: 21 seconds Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Join Optimizer: Case study SELECT o_year, SUM(CASE WHEN nation = 'FRANCE' THEN volume ELSE 0 END) / SUM(volume) AS mkt_share FROM ( SELECT EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n2.n_name AS nation FROM part STRAIGHT_JOIN lineitem ON p_partkey = l_partkey JOIN supplier ON s_suppkey = l_suppkey JOIN orders ON l_orderkey = o_orderkey JOIN customer ON o_custkey = c_custkey JOIN nation n1 ON c_nationkey = n1.n_nationkey JOIN region ON n1.n_regionkey = r_regionkey JOIN nation n2 ON s_nationkey = n2.n_nationkey WHERE r_name = 'EUROPE’ AND o_orderdate BETWEEN '1995-01-01' AND '1996-12-31’ AND p_type = 'PROMO BRUSHED STEEL' ) AS all_nations GROUP BY o_year ORDER BY o_year; Force early processing of high selectivity predicates Highest selectivity part before lineitem Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Join Optimizer: Case study Improved join order Execution time: 3 seconds Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | • tx JOIN tx+1 • records(tx+1) = records(tx) * condition_filter_effect * records_per_key New in MySQL 5.7 Record and Cost Estimates for JOIN Condition filter effect tx tx+1 Ref access Number of records read from tx Condition filter effect Records passing the table conditions on tx Cardinality statistics for index Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | How to Calculate Condition Filter Effect, step 1 A condition contributes to the condition filter effect for a table only if: – It references a field in the table – It is not used by the access method – It depends on an available value: • employee.name = “John” will always contribute to filter on employee • employee.first_office_id <> office.id; depends on JOIN order SELECT office_name FROM office JOIN employee WHERE office.id = employee.office_id AND employee.name = “John” AND employee.first_office_id <> office.id; New in MySQL 5.7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Filter estimate based on what is available: 1. Range estimate 2. Index statistics 3. Guesstimate = 0.1 <=,<,>,>= 1/3 BETWEEN 1/9 NOT 1 – SEL() AND P(A and B) = P(A) * P(B) OR P(A or B) = P(A) + P(B) – P(A and B) … … How to Calculate Condition Filter Effect, step 2 SELECT * FROM office JOIN employee ON office.id = employee.office_id WHERE office_name = “San Francisco” AND employee.name = “John” AND age > 21 AND hire_date BETWEEN “2014-01-01” AND “2014-06-01”; Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | SELECT * FROM office JOIN employee ON office.id = employee.office_id WHERE office_name = “San Francisco” AND employee.name = “John” AND age > 21 AND hire_date BETWEEN “2014-01-01” AND “2014-06-01”; Calculating Condition Filter Effect for Tables Condition filter effect for tables: – office: 0.03 – employee: 0.1 * 0.11 * 0.89 ≈ 0.01 Example 0.1 (guesstimate) 0.89 (range) 0.11 (guesstimate) 0.03 (index) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.7: Cost Information in Structured EXPLAIN Accumulated cost Total query cost Cost per table Improvements to Query 8 in MySQL 5.7: • Filtering on non-indexed columns are taken into account – No need for hint to force part table to be processed early • Merge derived tables into outer query – No temporary table Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to cost-based query optimization in MySQL Selecting data access methods (index selection) Join optimizer Subquery optimizations Ordering and aggregation (sorting and grouping) Tools for monitoring, analyzing, and tuning queries Influencing the Optimizer 1 2 3 4 5 6 7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Subquery category: • IN (SELECT …) • NOT IN (SELECT …) • FROM (SELECT …) • ALL/ANY (SELECT ..) • EXISTS/other Strategy: Overview of Subquery Optimizations • Semi-join • Materialization • IN ➜ EXISTS • Merged • Materialized • MAX/MIN re-write • Execute subquery New in MySQL 5.7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Traditional Optimization of IN Subqueries • Convert IN subquery to EXISTS subquery by “push-down” IN-equality to subquery: • Benefit: subquery will evaluate fewer records • Note: Special handling if pushed down expressions can be NULL IN  EXISTS transformation SELECT title FROM film WHERE film_id IN (SELECT film_id FROM actor WHERE name=“Bullock”) SELECT title FROM film WHERE EXISTS (SELECT 1 FROM actor WHERE name=“Bullock” AND film.film_id = actor.film_id) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | SELECT l_orderkey FROM lineitem GROUP BY l_orderkey HAVING SUM(l_quantity) > 313 SELECT o_orderdate, o_totalprice FROM orders WHERE o_orderkey IN ( ); Subquery Materialization 1. Execute subquery once and store result in a temporary table – Table has unique index for quick look-up and duplicate removal. 2. Execute outer query and check for matches in temporary table. Materialize Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 87% 0,000006 % 48% 0% 20% 40% 60% 80% 100% Q16 Q18 Q20 Query Execution Time Relative to MySQL 5.5 MySQL 5.5 MySQL 5.6 DBT-3, Scale 10 (23 GB) innodb_buffer_pool_size= 24 GB (CPU-bound) For Q20: optimizer_switch = ’semijoin=off; subquery_materialization_cost_based =off’ MySQL 5.5 vs MySQL 5.6: Subquery Materialization Q18: MySQL 5.5: ~37 years? MySQL 5.6: 69 seconds Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Subquery Materialization SELECT o_orderdate, o_totalprice FROM orders WHERE o_orderkey IN ( SELECT l_orderkey FROM lineitem GROUP BY l_orderkey HAVING SUM(l_quantity) > 313); Traditional IN-to-EXISTS evaluation SET optimizer_switch='materialization=off'; id select type table type possible keys key key len ref rows extra 1 PRIMARY orders ALL NULL NULL NULL NULL 1500000 Using where 2 SUBQUERY lineitem index PRIMARY, ... PRIMARY 8 NULL 6001215 NULL id select type table type possible keys key key len ref rows extra 1 PRIMARY orders ALL NULL NULL NULL NULL 1500000 Using where 2 DEPENDENT SUBQUERY lineitem index PRIMARY, ... PRIMARY 8 NULL 6001215 NULL Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.6: Semi-join • Convert subquery to inner join, BUT – Need some way to remove duplicates • Different strategies for duplicate removal: – FirstMatch (equivalent to IN→EXISTS execution) – LooseScan (index scan, skip duplicates) – Materialization: MatLookup (like subquery materialization), MatScan (materialized table is first in join order) – Duplicate WeedOut (insert result rows of semi-join query into temporary table with unique index; duplicate rows will be rejected. Any join order.) • If duplicate removal is not necessary: – Table pull-out Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Semi-join, cont. • Main advantage: – Opens up for more optimal ”join orders”. – Example: SELECT o_orderdate, o_totalprice FROM orders WHERE o_orderkey IN (SELECT l_orderkey FROM lineitem WHERE l_shipDate=’1996-09-30’); Will process less rows if starting with lineitem instead of orders • Restriction: – Cannot use semi-join if subquery contains union or aggregation Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 57,83 10,84 0,06 0,07 0 10 20 30 40 50 60 70 Query Time (seconds) Trad. Subquery Mat. LooseScan DupsWeedout SELECT o_totalprice FROM orders WHERE o_orderkey IN (SELECT l_orderkey FROM lineitem WHERE l_shipdate = '1996-09-30'); DBT-3, Scale 10 (23 GB) innodb_buffer_pool_size= 24 GB (CPU-bound) MySQL 5.6: Semi-join: Example 1 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 67,02 27,61 0,03 0 10 20 30 40 50 60 70 80 Query Time (seconds) Trad. Subquery Mat. Table Pullout SELECT SUM(l_quantity* l_extendedprice) FROM lineitem WHERE l_orderkey IN (SELECT o_orderkey FROM orders WHERE o_orderdate = '1996-09-30'); DBT-3, Scale 10 (23 GB) innodb_buffer_pool_size= 24 GB (CPU-bound) MySQL 5.6: Semi-join: Example 2 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 12,74 0,89 0,89 12,53 12,49 0 2 4 6 8 10 12 14 Query Time (seconds) Trad. Subquery Mat. MatLookup FirstMatch DupsWeedout SELECT s_name, s_address FROM supplier WHERE s_suppkey IN (SELECT ps_suppkey FROM partsupp, part WHERE ps_partkey=p_partkey AND p_name LIKE 'grey%‘ AND ps_availqty > 9990); DBT-3, Scale 10 (23 GB) innodb_buffer_pool_size= 24 GB (CPU-bound) MySQL 5.6: Semi-join: Example 3 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Semi-join SELECT o_totalprice FROM orders WHERE o_orderkey IN (SELECT l_orderkey FROM lineitem WHERE l_discount > 0.10); FirstMatch id select type table type possible keys key key len ref rows extra 1 SIMPLE orders ALL NULL NULL NULL NULL 1500000 NULL 1 SIMPLE lineitem ref PRIMARY, ... PRIMARY 4 dbt3.orders. o_orderkey 2 Using where; FirstMatch (orders) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Semi-join SELECT s_name, s_address FROM supplier WHERE s_suppkey IN (SELECT ps_suppkey FROM partsupp, part WHERE ps_partkey=p_partkey AND p_name LIKE 'grey%' AND ps_availqty > 9990); MatLookup id select type table type possible keys key key len ref rows extra 1 SIMPLE supplier ALL PRIMARY NULL NULL NULL 1000 Using where 1 SIMPLE eq_ref 4 ... 1 NULL 2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 20000 Using where 2 MATERIALIZED partsupp ref PRIMARY PRIMARY 4 … 2 Using where Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Semi-join SELECT o_totalprice FROM orders WHERE o_orderkey IN (SELECT l_orderkey FROM lineitem WHERE l_shipdate = '1996-09-30'); LooseScan id select type table type possible keys key key len ref rows extra 1 SIMPLE lineitem ref PRIMARY, i_l_shipdate i_l_shipdate 4 const 2532 Using index; Loosescan 1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 ... 1 NULL Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Semi-join SELECT o_totalprice FROM orders WHERE o_orderkey IN (SELECT l_orderkey FROM lineitem WHERE l_shipdate BETWEEN '1996-09-24' AND '1996-09-30'); Duplicate WeedOut id select type table type possible keys key key len ref rows extra 1 SIMPLE lineitem range PRIMARY, i_l_shipdate i_l_shipdate 4 NULL 37124 Using where; Using index; Start temporary 1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 ... 1 End temporary Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Derived Tables • Subquery in FROM clause SELECT AVG(o_totalprice) FROM ( SELECT * FROM orders ORDER BY o_totalprice DESC LIMIT 100000 ) td; • MySQL 5.6 and earlier: Executed separately and result stored in a temporary table (materialization) • MySQL 5.7: Treat derived tables like views: May be merged with outer query block Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.6: Index on Materialized Derived Table SELECT o_clerk, price - o_totalprice FROM (SELECT l_orderkey, SUM( l_extendedprice * (1 - l_discount)) price FROM lineitem GROUP by l_orderkey) t1 JOIN (SELECT o_clerk, o_orderkey, o_totalprice FROM orders WHERE o_orderdate BETWEEN '1995-01-01' AND '1995-12-31') t2 ON t1.l_orderkey = t2.o_orderkey WHERE t1.price > t2.o_totalprice; DBT-3 Scale 10: • MySQL 5.5: ? months; MySQL 5.6: 2 minutes Create index for join Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Materialization of Derived Tables mysql> explain select o_clerk, price - o_totalprice from (select l_orderkey, sum( l_extendedprice * (1 - l_discount)) price from lineitem group by l_orderkey) t1 join (select o_clerk, o_orderkey, o_totalprice from orders where o_orderdate between '1995-01-01' and '1995-12-31') t2 on t1.l_orderkey = t2.o_orderkey where t1.price > t2.o_totalprice; +----+-------------+------------+-------+--------------+-------------+ ... | id | select_type | table | type | possible_keys| key | ... +----+-------------+------------+-------+--------------+-------------+ ... | 1 | PRIMARY | | ALL | NULL | NULL | ... | 1 | PRIMARY | | ref | | | ... | 3 | DERIVED | orders | ALL | i_o_orderdate| NULL | ... | 2 | DERIVED | lineitem | index | PRIMARY, … | PRIMARY | ... +----+-------------+------------+-------+--------------+-------------+ ... EXPLAIN Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.7: Merge Derived Table with Outer Query mysql> explain select o_clerk, price - o_totalprice from (select l_orderkey, sum( l_extendedprice * (1 - l_discount)) price from lineitem group by l_orderkey) t1 join (select o_clerk, o_orderkey, o_totalprice from orders where o_orderdate between '1995-01-01' and '1995-12-31') t2 on t1.l_orderkey = t2.o_orderkey where t1.price > t2.o_totalprice; +----+-------------+------------+--------+--------------+-------------+ ... | id | select_type | table | type | possible_keys| key | ... +----+-------------+------------+--------+--------------+-------------+ ... | 1 | PRIMARY | | ALL | NULL | NULL | ... | 1 | PRIMARY | orders | eq_ref | PRIMARY, … | PRIMARY | ... | 2 | DERIVED | lineitem | index | PRIMARY, … | PRIMARY | ... +----+-------------+------------+--------+--------------+-------------+ ... • Derived tables based on GROUP BY, DISTINCT, LIMIT, or aggregate functions will not be merged. • MySQL 5.7: 1.5 minutes (DBT-3 SF10) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to cost-based query optimization in MySQL Selecting data access methods (index selection) Join optimizer Subquery optimizations Ordering and aggregation (sorting and grouping) Tools for monitoring, analyzing, and tuning queries Influencing the Optimizer 1 2 3 4 5 6 7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | ORDER BY Optimizations • General solution; “Filesort”: – Store query result in temporary table before sorting – If data volume is large, may need to sort in several passes with intermediate storage on disk. • Optimizations: – Take advantage of index to generate query result in sorted order – For ”LIMIT n” queries, maintain priority queue of n top items in memory instead of filesort. (MySQL 5.6) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Filesort SELECT * FROM orders ORDER BY o_totalprice ; SELECT c_name, o_orderkey, o_totalprice FROM orders JOIN customer ON c_custkey = o_custkey WHERE c_acctbal < -1000 ORDER BY o_totalprice ; id select type table type possible keys key key len ref rows extra 1 SIMPLE customer ALL PRIMARY NULL NULL NULL 1500000 Using where; Using temporary; Using filesort 1 SIMPLE orders ref i_o_custkey i_o_custkey 5 ... 7 NULL id select type table type possible keys key key len ref rows extra 1 SIMPLE orders ALL NULL NULL NULL NULL 15000000 Using filesort Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Filesort Status variables related to sorting: mysql> show status like 'Sort%'; +-------------------+--------+ | Variable_name | Value | +-------------------+--------+ | Sort_merge_passes | 1 | | Sort_range | 0 | | Sort_rows | 136170 | | Sort_scan | 1 | +-------------------+--------+ Status variables >0: Intermediate storage on disk. Consider increasing sort_buffer_size Number of sort operations (range scan or table/index scans) Number of rows sorted Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Filesort Sorting status per statement available from Performance Schema mysql> SELECT sql_text,sort_merge_passes,sort_range,sort_rows,sort_scan FROM performance_schema.events_statements_history ORDER BY timer_start DESC LIMIT 1; +--------------+-------------------+------------+-----------+-----------+ | sql_text | sort_merge_passes | sort_range | sort_rows | sort_scan | +--------------+-------------------+------------+-----------+-----------+ | SELECT ... | 1 | 0 | 136170 | 1 | +--------------+-------------------+------------+-----------+-----------+ Performance Schema Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql> SELECT AVG(o_totalprice) FROM ( SELECT * FROM orders ORDER BY o_totalprice DESC LIMIT 100000) td; +-------------------+ | AVG(o_totalprice) | +-------------------+ | 398185.986158 | +-------------------+ 1 row in set (24.65 sec) mysql> SHOW STATUS LIKE 'sort%'; +-------------------+--------+ | Variable_name | Value | +-------------------+--------+ | Sort_merge_passes | 1432 | | Sort_range | 0 | | Sort_rows | 100000 | | Sort_scan | 1 | +-------------------+--------+ 4 rows in set (0.00 sec) Filesort: Case Study Unnecessary large data volume! Many intermediate sorting steps! Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Filesort: Case Study mysql> SELECT AVG(o_totalprice) FROM (SELECT o_totalprice FROM orders ORDER BY o_totalprice DESC LIMIT 100000) td; +-------------------+ | AVG(o_totalprice) | +-------------------+ | 398185.986158 | +-------------------+ 1 row in set (8.18 sec) mysql> SELECT sql_text, sort_merge_passes FROM performance_schema. events_statements_history ORDER BY timer_start DESC LIMIT 1; +----------------------------------------------------+-------------------+ | sql_text | sort_merge_passes | +----------------------------------------------------+-------------------+ | SELECT AVG(o_totalprice) FROM (SELECT o_totalprice | 229 | +----------------------------------------------------+-------------------+ Reduce amount of data to be sorted Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Filesort: Case Study mysql> SET sort_buffer_size = 1024*1024; mysql> SELECT AVG(o_totalprice) FROM (SELECT o_totalprice FROM orders ORDER BY o_totalprice DESC LIMIT 100000) td; +-------------------+ | AVG(o_totalprice) | +-------------------+ | 398185.986158 | +-------------------+ 1 row in set (7.24 sec) mysql> SELECT sql_text, sort_merge_passes FROM performance_schema. events_statements_history ORDER BY timer_start DESC LIMIT 1; +----------------------------------------------------+-------------------+ | sql_text | sort_merge_passes | +----------------------------------------------------+-------------------+ | SELECT AVG(o_totalprice) FROM (SELECT o_totalprice | 57 | +----------------------------------------------------+-------------------+ Increase sort buffer (1 MB) Default is 256 kB Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Filesort: Case Study mysql> SET sort_buffer_size = 8*1024*1024; mysql> SELECT AVG(o_totalprice) FROM (SELECT o_totalprice FROM orders ORDER BY o_totalprice DESC LIMIT 100000) td; +-------------------+ | AVG(o_totalprice) | +-------------------+ | 398185.986158 | +-------------------+ 1 row in set (6.30 sec) mysql> SELECT sql_text, sort_merge_passes FROM performance_schema. events_statements_history ORDER BY timer_start DESC LIMIT 1; +----------------------------------------------------+-------------------+ | sql_text | sort_merge_passes | +----------------------------------------------------+-------------------+ | SELECT AVG(o_totalprice) FROM (SELECT o_totalprice | 0 | +----------------------------------------------------+-------------------+ Increase sort buffer even more (8 MB) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Using Index to Avoid Sorting CREATE INDEX i_o_totalprice ON orders(o_totalprice); SELECT o_orderkey, o_totalprice FROM orders ORDER BY o_totalprice ; id select type table type possible keys key key len ref rows extra 1 SIMPLE orders index NULL i_o_totalprice 6 NULL 15000000 Using index SELECT * FROM orders ORDER BY o_totalprice ; However, still (due to total cost): id select type table type possible keys key key len ref rows extra 1 SIMPLE orders ALL NULL NULL NULL NULL 15000000 Using filesort Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Using Index to Avoid Sorting SELECT AVG(o_totalprice) FROM (SELECT o_totalprice FROM orders ORDER BY o_totalprice DESC LIMIT 100000) td; Case study revisited id select type table Type possible keys key key len ref rows extra 1 PRIMARY ALL NULL NULL NULL NULL 100000 NULL 2 DERIVED orders index NULL i_o_totalprice 6 NULL 15000000 Using index mysql> SELECT AVG(o_totalprice) FROM ( SELECT o_totalprice FROM orders ORDER BY o_totalprice DESC LIMIT 100000) td; ... 1 row in set (0.06 sec) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Aggregate Queries • General solution: 1. Sort the rows and insert into a new temporary table so that all rows from each group are consecutive 2. Scan temporary table and compute aggregated result • Optimizations: – Use an index which is ordered on grouping column • Requires that all GROUP BY columns are from same index – Loose index scan GROUP BY Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | id select type table type possible keys key key len ref rows extra 1 SIMPLE orders ALL NULL NULL NULL NULL 15000000 Using temporary; Using filesort Aggregate Queries SELECT SUM(o_totalprice) FROM orders GROUP BY o_clerk; Examples SELECT p_name, AVG(l_quantity) FROM lineitem JOIN part ON l_partkey=p_partkey GROUP BY p_partkey; id select type table type possible keys key key len ref rows extra 1 SIMPLE part index PRIMARY PRIMARY 4 NULL 2000000 NULL 1 SIMPLE lineitem ref i_l_partkey i_l_partkey 5 ... 29 NULL Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Loose Index Scan • Optimization for GROUP BY and DISTINCT: SELECT a, b FROM t1 GROUP BY a, b; SELECT DISTINCT a, b FROM t1; SELECT a, MIN(b) FROM t1 GROUP BY a; • GROUP BY/DISTINCT must be on the prefix of the index 10 1 2 3 4 5 11 1 2 3 4 5 12 1 2 3 4 5 13 1 2 3 4 5 a b c Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Loose Index Scan SELECT a, MIN(b) FROM t1 GROUP BY a; Example id select type table type possible keys key key len ref rows extra 1 SIMPLE t1 range abc abc 10 NULL 11 Using index for group-by Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to cost-based query optimization in MySQL Selecting data access methods (index selection) Join optimizer Subquery optimizations Ordering and aggregation (sorting and grouping) Tools for monitoring, analyzing, and tuning queries Influencing the Optimizer 1 2 3 4 5 6 7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Useful tools • MySQL Enterprise Monitor (MEM), Query Analyzer – Commercial product • Performance schema, MySQL sys schema • EXPLAIN • Structured EXPLAIN (FORMAT=JSON) • Visual EXPLAIN (MySQL Workbench) • Optimizer trace • Slow log • Status variables (SHOW STATUS LIKE ’Sort%’) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Enterprise Monitor, Query Analyzer Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Query Analyzer Query Details Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Performance Schema • events_statements_history events_statements_history_long – Most recent statements executed • events_statements_summary_by_digest – Summary for similar statements (same statement digest) • file_summary_by_event_name – Interesting event: wait/io/file/innodb/innodb_data_file • table_io_waits_summary_by_table table_io_waits_summary_by_index_usage – Statistics on storage engine access per table and index Some useful tables Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Performance Schema • Normalization of queries to group statements that are similar to be grouped and summarized: SELECT * FROM orders WHERE o_custkey=10 AND o_totalprice>20 SELECT * FROM orders WHERE o_custkey = 20 AND o_totalprice > 100 SELECT * FROM orders WHERE o_custkey = ? AND o_totalprice > ? • events_statements_summary_by_digest DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT, SUM_LOCK_TIME, SUM_ERRORS, SUM_WARNINGS, SUM_ROWS_AFFECTED, SUM_ROWS_SENT, SUM_ROWS_EXAMINED, SUM_CREATED_TMP_DISK_TABLES, SUM_CREATED_TMP_TABLES, SUM_SELECT_FULL_JOIN, SUM_SELECT_FULL_RANGE_JOIN, SUM_SELECT_RANGE, SUM_SELECT_RANGE_CHECK, SUM_SELECT_SCAN, SUM_SORT_MERGE_PASSES, SUM_SORT_RANGE, SUM_SORT_ROWS, SUM_SORT_SCAN, SUM_NO_INDEX_USED, SUM_NO_GOOD_INDEX_USED, FIRST_SEEN, LAST_SEEN Statement digest Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Performance Schema • Tables: events_statements_current (Current statement for each thread) events_statements_history (10 most recent statements per thread) events_statements_history_long (10000 most recent statements) • Columns: THREAD_ID, EVENT_ID, END_EVENT_ID, EVENT_NAME, SOURCE, TIMER_START, TIMER_END, TIMER_WAIT, LOCK_TIME, SQL_TEXT, DIGEST, DIGEST_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_INSTANCE_BEGIN, MYSQL_ERRNO, RETURNED_SQLSTATE, MESSAGE_TEXT, ERRORS, WARNINGS, ROWS_AFFECTED, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_DISK_TABLES, CREATED_TMP_TABLES, SELECT_FULL_JOIN, SELECT_FULL_RANGE_JOIN, SELECT_RANGE, SELECT_RANGE_CHECK, SELECT_SCAN, SORT_MERGE_PASSES, SORT_RANGE, SORT_ROWS, SORT_SCAN, NO_INDEX_USED, NO_GOOD_INDEX_USED, NESTING_EVENT_ID, NESTING_EVENT_TYPE Statement events Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL sys Schema •A collection of views, procedures and functions, designed to make reading raw Performance Schema data easier • Implements many common DBA and Developer use cases • Included with MySQL 5.7 •Bundled with MySQL Workbench • Available on GitHub – https://github.com/MarkLeith/mysql-sys • Examples of very useful functions: – format_time() , format_bytes(), format_statement() Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL sys Schema statement_analysis: Lists a normalized statement view with aggregated statistics, mimics the MySQL Enterprise Monitor Query Analysis view, ordered by the total execution time per normalized statement mysql> select * from statement_analysis limit 1\G *************************** 1. row *************************** query: INSERT INTO `mem__quan` . `nor ... nDuration` = IF ( VALUES ( ... db: mem full_scan: exec_count: 1110067 err_count: 0 warn_count: 0 total_latency: 1.93h max_latency: 5.03 s avg_latency: 6.27 ms Example lock_latency: 00:18:29.18 rows_sent: 0 rows_sent_avg: 0 rows_examined: 0 rows_examined_avg: 0 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 0 sort_merge_passes: 0 digest: d48316a218e95b1b8b72db5e6b177788! first_seen: 2014-05-20 10:42:17 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | EXPLAIN • Use EXPLAIN to print the final query plan: • Explain for a running query (New in MySQL 5.7): EXPLAIN FOR CONNECTION connection_id; Understand the query plan EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.a WHERE b > 10 AND c > 10; +----+--------+-------+------------+------+---------------+-----+---------+-----+------+----------+------------+ | id | type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------+-------+------------+------+---------------+-----+---------+-----+------+----------+------------+ | 1 | SIMPLE | t1 | NULL | range| idx1 | idx1| 4 | NULL| 12 | 33.33 | Using where| | 2 | SIMPLE | t2 | NULL | ref | idx2 | idx2| 4 | t1.a| 1 | 100.00 | NULL | +----+--------+-------+------------+------+---------------+-----+---------+-----+------+----------+------------+ Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Added in MySQL 5.7 Structured EXPLAIN • JSON format: • Contains more information: – Used index parts – Pushed index conditions – Cost estimates – Data estimates EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE b > 10 AND c > 10; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "17.81" }, "table": { "table_name": "t1", "access_type": "range", "possible_keys": [ "idx1" ], "key": "idx1", "used_key_parts": [ "b" ], "key_length": "4", "rows_examined_per_scan": 12, "rows_produced_per_join": 3, "filtered": "33.33", "index_condition": "(`test`.`t1`.`b` > 10)", "cost_info": { "read_cost": "17.01", "eval_cost": "0.80", "prefix_cost": "17.81", "data_read_per_join": "63" }, ……… "attached_condition": "(`test`.`t1`.`c` > 10)" } } } EXPLAIN FORMAT=JSON SELECT … Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 10, "filtered": 100, "attached_condition": "(t1.a = 9)" } /* table */ }, { "table": { "table_name": "t2", "access_type": "ALL", "rows": 10, "filtered": 100, "using_join_buffer": "Block Nested Loop", "attached_condition": "((t2.a = 9) and ((t2.b <= 3) or ((t2.b = 5) and (t1.b = 12))))" } /* table */ } ] /* nested_loop */ } /* query_block */ } Structured EXPLAIN Assigning Conditions to Tables EXPLAIN FORMAT=JSON 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)); Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Optimizer Trace: Query Plan Debugging • EXPLAIN shows the selected plan • TRACE shows WHY the plan was selected: – Alternative plans – Estimated costs – Decisions made • JSON format Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Optimizer Trace: Example SET optimizer_trace= “enabled=on“, end_markers_in_json=on; SELECT * FROM t1,t2 WHERE f1=1 AND f1=f2 AND f2>0; SELECT trace INTO DUMPFILE FROM information_schema.optimizer_trace; SET optimizer_trace="enabled=off"; QUERY SELECT * FROM t1,t2 WHERE f1=1 AND f1=f2 AND f2>0; TRACE “steps”: [ { "join_preparation": { "select#": 1,… } … } …] MISSING_BYTES_BEYOND_MAX_MEM_SIZE 0 INSUFFICIENT_PRIVILEGES 0 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to cost-based query optimization in MySQL Selecting data access methods (index selection) Join optimizer Subquery optimizations Ordering and aggregation (sorting and grouping) Tools for monitoring, analyzing, and tuning queries Influencing the Optimizer 1 2 3 4 5 6 7 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Influencing the Optimizer • Add indexes • Force use of specific indexes: – USE INDEX, FORCE INDEX, IGNORE INDEX • Force specific join order: – STRAIGHT_JOIN • Adjust session variables – optimizer_switch flags: set optimizer_switch=“index_merge=off” – Buffer sizes: set sort_buffer=8*1024*1024; – Other variables: set optimizer_search_depth = 10; When the optimizer does not do what you want Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.7: New Optimizer Hints • Ny hint syntax: – SELECT /*+ HINT1(args) HINT2(args) */ … FROM … • New hints: – BKA(tables)/NO_BKA(tables), BNL(tables)/NO_BNL(tables) – MRR(table indexes)/NO_MRR(table indexes) – SEMIJOIN/NO_SEMIJOIN(strategies), SUBQUERY(strategy) – NO_ICP(table indexes) – NO_RANGE_OPTIMIZATION(table indexes) – QB_NAME(name) • Finer granularilty than optimizer_switch session variable Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | • Disable semi-join with hint: EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t3); • No hint, optimizer chooses semi-join algorithm LooseScan: EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t3); MySQL 5.7: Hint Example: SEMIJOIN id select type table type possible keys key key len ref rows extra 1 SIMPLE t3 index a a 4 NULL 3 Using where; LooseScan 1 SIMPLE t2 ref a a 4 test.t3.a 1 Using index id select type table type possible keys key key len ref rows extra 1 PRIMARY t2 index null a 4 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t3 Index_ subquery a a 4 func 1 Using index Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.7: Hint Example: SEMIJOIN • Force Semi-join Materialization to be used EXPLAIN SELECT /*+ SEMIJOIN(@subq MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t3); 3 rows in set, 1 warning (0.01 sec) id select type table type possible keys key key len ref rows extra 1 SIMPLE t2 index a a 4 NULL 4 Using where; Using index 1 SIMPLE eq_ref 4 test.t2.a 1 NULL 2 MATERIALIZED t3 index a a 4 NULL 3 Using index Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Optimizer Hints • Hints we consider to add – Enable/disable merge of views and derived tables – Force/ignore index_merge alternatives – Join order: LEADING(t1 t2 ...) • Plan to reimplement existing hints in new syntax Future Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.7: Query Rewrite Plugin • Rewrite problematic queries without the need to make application changes – Add hints – Modify join order – Much more … • Add rewrite rules to table: INSERT INTO query_rewrite.rewrite_rules (pattern, replacement ) VALUES ("SELECT * FROM t1 WHERE a > ? AND b = ?", "SELECT * FROM t1 FORCE INDEX (a_idx) WHERE a > ? AND b = ?"); • New pre and post parse query rewrite APIs – Users can write their own plug-ins Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.7: Adjustable Cost Constants EXPLAIN SELECT SUM(o_totalprice) FROM orders WHERE o_orderdate BETWEEN '1994-01-01' AND '1994-12-31'; Experimental! Use with caution! No guarantees! UPDATE mysql.engine_cost SET cost_value=0.2 WHERE cost_name='memory_block_read_cost'; FLUSH COST_CONSTANTS; id select type table type possible keys key key len rows filtered extra 1 SIMPLE orders ALL i_o_orderdate NULL NULL 15000000 29.93 Using where Default: 1.0 Make server read new cost constants Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 5.7: Adjustable Cost Constants EXPLAIN SELECT SUM(o_totalprice) FROM orders WHERE o_orderdate BETWEEN '1994-01-01' AND '1994-12-31'; Continued Id select type table type possible keys key key len rows filtered extra 1 SIMPLE orders range i_o_orderdate i_o_orderdate 4 4489990 100.00 Using index condition Note: – Heuristic used: If table is smaller than 20% of database buffer, all pages are in memory – Only new connections will see updated cost constants Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | More information • My blog: – http://oysteing.blogspot.com/ • Optimizer team blog: – http://mysqloptimizerteam.blogspot.com/ • MySQL Server Team blog – http://mysqlserverteam.com/ • MySQL forums: – Optimizer & Parser: http://forums.mysql.com/list.php?115 – Performance: http://forums.mysql.com/list.php?24 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Some Relevant Presentations at OpenWorld 2015 • MySQL 5.7: What Is New in the Optimizer? [CON3379] – Wednesday, Oct 28, 11:00 a.m. | Moscone South—262 • JSON Support in MySQL 5.7 [CON2131] – Wednesday, Oct 28, 1:45 p.m. | Moscone South—262 • A MySQL Sys Schema Deep Dive [CON2257] – Tuesday, Oct 27, 4:00 p.m. | Moscone South—262 • Performance Monitoring with MySQL Enterprise Monitor [HOL8320] – Wednesday, Oct 28, 2:45 p.m. | Hotel Nikko—Peninsula (25th Floor) • Meet the MySQL Server Development Team [BOF6442] – Tuesday, Oct 27, 6:15 p.m. | Moscone South—250 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Keynote: Monday, 4.00-6.00 pm, YBCA Theater • State of the Dolphin • Customer Experiences Hari Tatrakal, Director of Database Services, Live Nation Olaniyi Oshinowo, MySQL & Open Source Technologies Leader, GE Ernie Souhrada & Rob Wultsch, Database Engineers, Pinterest • Rich Mason, SVP & General Manager MySQL GBU, Oracle • Tomas Ulin, VP MySQL Engineering, Oracle Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | MySQL Community Reception @ Oracle OpenWorld • Tuesday, October 27: 7 pm • Jillian’s at Metreon: 175 Fourth Street, San Francisco At the corner of Howard and 4th st.; only 2-min walk from Moscone Center (same place as last year) Join us! Celebrate, Have Fun and Mingle with Oracle’s MySQL Engineers & Your Peers Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Oracle University MySQL Training Services Enable Reliable and High-Performance Web-Based Database Applications in Your Organization Benefits . Expert-led training to support your MySQL learning needs . Flexibility to train in the classroom or online . Hands-on experience to gain real world experience . Key skills needed for database administrators and developers . MySQL for Beginners . MySQL for Database Administrators . MySQL Performance Tuning . MySQL Cluster . MySQL and PHP - Developing Dynamic Web Applications . MySQL for Developers . MySQL Developer Techniques . MySQL 5.6 Database Administrator . MySQL 5.6 Developer To find out more about available MySQL Training & Certification offerings, go to: education.oracle.com/mysql Top Courses for Administrators and Developers Top Certifications Learn how to install and configure an open source, ACID-compliant transactional database designed to deliver real-time in-memory performance and 99.999% availability. Find out more at: education.oracle.com/MySQL MySQL Student Quotes: • “Slides are excellent. I was so happy to get so much detail. The information was relevant and written in a way that was easy to understand and absorb.” • “The prepared scripts to run for executing testing was very nice to have.” • “The course content was amazing and the instructor's real-world solutions really helped.” Premier Support customers eligible to save 20% on learning credits. MySQL Cluster Training – Available NOW! Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Thank You! Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
还剩118页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

xaxa3kp

贡献于2016-04-30

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf