MySQL Query Optimization
•
4 likes•1,850 views
This document discusses query optimization in MySQL. It provides an introduction to how the MySQL query optimizer works to determine the most efficient execution plan for a SQL query. Several examples are shown using the EXPLAIN statement to analyze queries against sample data in the World Schema. Indexes are added and analyzed to demonstrate how they can improve query performance in different scenarios. The document also discusses some general strategies and rules of thumb used by the query optimizer.
Report
Share
More Related Content
What's hot
What's hot (20)
Viewers also liked
Viewers also liked (20)
Similar to MySQL Query Optimization
Similar to MySQL Query Optimization (20)
More from Morgan Tocker
More from Morgan Tocker (20)
Recently uploaded
Recently uploaded (20)
MySQL Query Optimization
- 2. Query Optimization Morgan Tocker MySQL Community Manager October, 2014 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
- 3. Safe Harbor Statement The following 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 © 2014 Oracle and/or its affiliates. All rights reserved. | 3
- 4. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 4 Today’s Agenda Introduction World Schema IMDB Schema Advanced Topics 1 2 3 4
- 5. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Introduction • SQL is declarative. • You say what you want, not how to process. • Can’t sight check a query to understand how it executes. • Database management chooses the best possible way -‐ think like a GPS navigator. 5
- 6. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | GPS Software 6
- 7. MySQL Optimizer Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 7
- 8. Diagnostic Commands Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • EXPLAIN (all versions) • EXPLAIN FORMAT=JSON (MySQL 5.6+) • Workbench supports visual format. • OPTIMIZER TRACE (MySQL 5.6+) 8
- 9. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 9 Today’s Agenda Introduction World Schema IMDB Schema Advanced Topics 1 2 3 4
- 10. The World Schema Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Contains cities, countries and language statistics. • Download From: • http://dev.mysql.com/doc/index-‐other.html • Very small data set -‐ good for learning. • Not perfect for explaining performance differences 10
- 11. Find Countries in Asia with Population >5M Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 11 mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND population > 5000000G ********************** 1. row ********************** id: 1 select_type: SIMPLE table: Country partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 239 filtered: 4.76 Extra: Using where 1 row in set, 1 warning (0.00 sec)
- 12. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 12 EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "53.80" }, "table": { "table_name": "Country", "access_type": "ALL", "rows_examined_per_scan": 239, "rows_produced_per_join": 11, "filtered": 4.7614, "cost_info": { "read_cost": "51.52", "eval_cost": "2.28", "prefix_cost": "53.80", "data_read_per_join": "2K" }, "used_columns": [ "Code", .. "Capital", "Code2" ], "attached_condition": "((`world`.`country`.`Continent` = 'Asia') and (`world`.`country`.`Population` > 5000000))" } } } FORMAT=JSON Available since MySQL 5.6. Expanded in 5.7 to add cost information.
- 13. What indexes will make this query faster? Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Some suggestions: • Index on (population) • Index on (continent) • Index on (population, continent) • Index on (continent, population) 13 SELECT * FROM Country WHERE Continent = 'Asia' AND population > 5000000;
- 14. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Next Question • Given so many choices, which is the best choice? • A good GPS navigator understands traffic and finds the fastest route. • A good query optimizer does similar. 14
- 15. Query Optimizer Strategy Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Pick the plan that consumes the least amount of resources (CPU, IO). 15
- 16. Query Optimizer Strategy (cont.) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Use statistics available to identify good and bad plans. • Decisions may include: • Which indexes are good/bad. • Which order to join tables in. • If indexes should be read in a particular order to avoid sorting, or if sorting is not expensive. 16
- 17. Using single column indexes first…. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • p (population) • c (continent) 17
- 18. mysql> ALTER TABLE Country ADD INDEX p (population); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND population > 5000000G ************************ 1. row ************************ Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 18 id: 1 select_type: SIMPLE table: Country partitions: NULL type: ALL possible_keys: p key: NULL key_len: NULL ref: NULL rows: 239 filtered: 6.46 Extra: Using where 1 row in set, 1 warning (0.00 sec)
- 19. Why would an index not be used? Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 19 mysql> EXPLAIN FORMAT=JSON SELECT * FROM Country FORCE INDEX (p) WHERE Continent = 'Asia' AND population > 5000000G ******* 1. row ******* EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "152.21" }, "table": { "table_name": "Country", "access_type": "range", "possible_keys": [ "p" ], Using MySQL 5.7+ mysql> EXPLAIN FORMAT=JSON SELECT * FROM Country WHERE Continent = 'Asia' AND population > 5000000G ******* 1. row ******* EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "53.80" }, "table": { "table_name": "Country", "access_type": "ALL", "possible_keys": [ "p" ],
- 20. Modifying the query range: >50M Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 20 mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND population > 50000000G ********************** 1. row ********************** id: 1 select_type: SIMPLE table: Country partitions: NULL type: range possible_keys: p key: p key_len: 4 ref: NULL rows: 24 filtered: 14.29 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec)
- 21. mysql> ALTER TABLE Country ADD INDEX c (continent); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND population > 5000000G *********************** 1. row *********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 21 id: 1 select_type: SIMPLE table: Country partitions: NULL type: ref possible_keys: p,c key: c key_len: 1 ref: const rows: 51 filtered: 45.19 Extra: Using where 1 row in set, 1 warning (0.00 sec)
- 22. Modifying the query range: >500M mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND population > 500000000G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 22 id: 1 select_type: SIMPLE table: Country partitions: NULL type: range possible_keys: p,c key: p key_len: 4 ref: NULL rows: 2 filtered: 21.34 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec)
- 23. Composite Indexes Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • On >50M which is better? • (population, continent) • (continent, population) 23
- 24. mysql> ALTER TABLE Country ADD INDEX pc (pop..on, co..nt); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND population > 50000000G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 24 id: 1 select_type: SIMPLE table: Country partitions: NULL type: ref possible_keys: p,c,pc key: c key_len: 1 ref: const rows: 51 filtered: 10.04 Extra: Using where 1 row in set, 1 warning (0.00 sec)
- 25. FORCE INDEX mysql> EXPLAIN SELECT * FROM Country FORCE INDEX (pc) WHERE Continent = 'Asia' AND population > 50000000G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 25 id: 1 select_type: SIMPLE table: Country partitions: NULL type: range possible_keys: pc key: pc key_len: 4 ref: NULL rows: 24 filtered: 14.29 Extra: Using index condition 1 row in set, 1 warning (0.00 sec)
- 26. mysql> EXPLAIN FORMAT=JSON SELECT * FROM Country FORCE INDEX (pc) WHERE Continent = 'Asia' AND population > 50000000G ********************** 1. row ********************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "34.61" }, "table": { Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 26 .. ], "key": "pc", "used_key_parts": [ "Population" ], .. Easier to see with JSON EXPLAIN
- 27. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Rule of Thumb • Index on (const, range) instead of (range, const). • Applies to all databases. 27
- 28. mysql> ALTER TABLE Country ADD INDEX cp (co..nt, pop..on); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND population > 50000000G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 28 id: 1 select_type: SIMPLE table: Country partitions: NULL type: range possible_keys: p,c,pc,cp key: cp key_len: 5 ref: NULL rows: 11 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec)
- 29. Where Population >N. Lower is better. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Cost Estimates 29 5M 50M 500M Table Scan 53.80 53.80 53.80 (Population) 152.21 34.61 3.81 (Continent) 28.20 28.20 28.20 (Population, 152.21 34.61 3.81 Continent) (Continent, Population) 24.83 16.41 3.81
- 30. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 30 Today’s Agenda Introduction World Schema IMDB Schema Advanced Topics 1 2 3 4
- 31. Examples Using IMDB Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Larger than world database schema. • Better to understand performance differences. • License requires data to be regenerated: • http://imdbpy.sourceforge.net/ 31
- 32. mysql> EXPLAIN SELECT * FROM title WHERE title = 'The Lion King'G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 32 id: 1 select_type: SIMPLE table: title partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2387523 filtered: 0.50 Extra: Using where 1 row in set, 1 warning (0.00 sec) Ignore EXPLAIN time Actual run time is 2.9 seconds (Not shown)
- 33. mysql> ALTER TABLE title ADD INDEX (title(100)); Query OK, 0 rows affected (4.73 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM title WHERE title = 'The Lion King'G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 33 id: 1 select_type: SIMPLE table: title partitions: NULL type: ref possible_keys: title key: title key_len: 102 ref: const rows: 4 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.01 sec) From 2.9 seconds to 0.0 seconds (!!!)
- 34. How does LIKE work? Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 34 mysql> EXPLAIN SELECT * FROM title WHERE title LIKE 'The Lion %'G ********************** 1. row ********************** id: 1 select_type: SIMPLE table: title partitions: NULL type: range possible_keys: title key: title key_len: 102 ref: NULL rows: 109 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.01 sec)
- 35. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | LIKE (cont.) 35 mysql> EXPLAIN SELECT * FROM title WHERE title LIKE 'The%'G ********************** 1. row ********************** id: 1 select_type: SIMPLE table: title partitions: NULL type: range possible_keys: title key: title key_len: 102 ref: NULL rows: 325548 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) VS Very Close. Estimate is that it is almost as fast to table scan. Run Time is 1.9 sec
- 36. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Sorting 36 mysql> EXPLAIN SELECT * FROM title WHERE title = 'The Lion King' ORDER BY production_yearG ********************** 1. row ********************** id: 1 select_type: SIMPLE table: title partitions: NULL type: ref possible_keys: title key: title key_len: 102 ref: const rows: 4 filtered: 100.00 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec)
- 37. SELECT movie_info.* FROM title INNER JOIN movie_info ON title.id=movie_info.movie_id WHERE title=‘The Lion King’G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 37 id: 1 select_type: SIMPLE table: movie_info type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1212187 Extra: NULL ********************** 2. row ********************** id: 1 select_type: SIMPLE table: title type: eq_ref possible_keys: PRIMARY,title key: PRIMARY key_len: 4 ref: imdb.movie_info.movie_id rows: 1 Extra: Using where
- 38. mysql> ALTER TABLE movie_info ADD INDEX mi (movie_id); Query OK, 0 rows affected (1.95 sec) Records: 0 Duplicates: 0 Warnings: 0 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 38
- 39. EXPLAIN SELECT movie_info.* FROM title INNER .. WHERE title='The Lion King'G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 39 id: 1 select_type: SIMPLE table: title type: ref possible_keys: PRIMARY,title key: title key_len: 102 ref: const rows: 4 Extra: Using where ********************** 2. row ********************** id: 1 select_type: SIMPLE table: movie_info type: ref possible_keys: mi key: mi key_len: 4 ref: imdb.title.id rows: 7 Extra: NULL
- 40. mysql> EXPLAIN SELECT * FROM movie_info WHERE movie_id IN (SELECT id FROM title WHERE title = 'The Lion King')G ********************** 1. row ********************** Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 40 id: 1 select_type: SIMPLE table: title type: ref possible_keys: PRIMARY,title key: title key_len: 102 ref: const rows: 4 Extra: Using where ********************** 2. row ********************** id: 1 select_type: SIMPLE table: movie_info type: ref possible_keys: mi key: mi key_len: 4 ref: imdb.title.id rows: 7 Extra: NULL
- 41. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 41 Today’s Agenda Introduction World Schema IMDB Schema Advanced Topics 1 2 3 4
- 42. Optimizer Hints Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • USE INDEX • FORCE INDEX • IGNORE INDEX • STRAIGHT JOIN 42
- 43. Optimizer Hints (cont.) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 43 Expanded in MySQL 5.6 mysql> select @@optimizer_switchG ********************** 1. row ********************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union =on,index_merge_intersection=on,engine_condition_pushdown= on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,bl ock_nested_loop=on,batched_key_access=off,materialization= on,semijoin=on,loosescan=on,firstmatch=on,subquery_materia lization_cost_based=on,use_index_extensions=on,condition_f anout_filter=on 1 row in set (0.00 sec)
- 44. Statistics Sampling Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Some decisions based on pre-‐computed statistics. • For example, which order to join tables. • Accuracy of statistics greatly improved in MySQL 5.6. • Feature is called “InnoDB persistent statistics”. 44
- 45. Optimizer Decision Visibility Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Intended for MySQL developer usage, and reporting optimizer bugs. • Yet totally practical for advanced usage :) 45 New to MySQL 5.6 SET optimizer_trace="enabled=on"; SELECT /* query here */; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
- 46. Optimizer Trace Output • Includes why decisions were not made: Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 46 .. "index": "pc", "ranges": [ "50000000 < Population" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 24, "cost": 29.81, "chosen": false, "cause": "cost" },
- 47. Too many indexes Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Slows down modifications due to maintenance cost. • i.e. Slower INSERT/UPDATE/DELETE • May also lead to worse decisions being made by optimizer. • Too many similar choices. • Only limited time window to evaluate suitability. • Space requirements in memory and storage. 47
- 48. MySQL 5.7 and beyond Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • We are working on a new cost model. • Better adapt to new hardware with different costs (i.e. SSDs have cheap random IO). • Memory buffer aware cost estimates. 48
- 49. Cost constants configurable per engine in 5.7 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | • Previously hard-‐coded constants. • Better adapts to differences in hardware. 49
- 50. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 50