Movatterモバイル変換

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |MySQL 8.0 Optimizer GuideMorgan TockerMySQL Product Manager (Server)Copyright © 2017, Oracle and/or its affiliates. All rights reserved.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Safe Harbor StatementThe following is intended to outline our general product direction. It is intendedfor information purposes only, and may not be incorporated into any contract. Itis not a commitment to deliver any material, code, or functionality, and shouldnot be relied upon in making purchasing decisions. The development, release,and timing of any features or functionality described for Oracle’s productsremains at the sole discretion of Oracle.3

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Agenda1. Introduction2. Server Architecture3. B+trees4. EXPLAIN5. Optimizer Trace6. LogicalTransformations7. Cost BasedOptimization8. Hints and Switches9. Comparing Plans10.Composite Indexes11.Covering Indexes12.Visual Explain13.Transient Plans14.Subqueries15.CTEs and Views16.Joins17.Aggregation18.Descending Indexes19.Sorting20.Partitioning21.Query Rewrite22.Invisible Indexes23.Profiling24.JSON25.Character Sets4

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Introduction• SQL is declarative• You state “what you want” not “how you want”• Can’t usually sight check queries to understand executionefficiency• Database management system is like a GPS navigation system. Itfinds the “best”route.5

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Diagnostic Commands• EXPLAIN (all versions)• EXPLAIN FORMAT=JSON (5.6+)– Supported by Workbench in Visual format• Optimizer Trace (5.6+)8

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Examples from “The World Schema”• Contains Cities, Countries, Language statistics• Download from:– https://dev.mysql.com/doc/index-other.html• Very small data set– Good for learning– Not good for explaining performance differences9

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Primary Table we are usingCREATE TABLE `Country` (`Code` char(3) NOT NULL DEFAULT '',`Name` char(52) NOT NULL DEFAULT '',`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT'Asia',`Region` char(26) NOT NULL DEFAULT '',`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',`IndepYear` smallint(6) DEFAULT NULL,`Population` int(11) NOT NULL DEFAULT '0',`LifeExpectancy` float(3,1) DEFAULT NULL,`GNP` float(10,2) DEFAULT NULL,`GNPOld` float(10,2) DEFAULT NULL,`LocalName` char(45) NOT NULL DEFAULT '',`GovernmentForm` char(45) NOT NULL DEFAULT '',`HeadOfState` char(60) DEFAULT NULL,`Capital` int(11) DEFAULT NULL,`Code2` char(2) NOT NULL DEFAULT '',PRIMARY KEY (`Code`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)10

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Companion Website• Content from “The Unofficial MySQL 8.0 Optimizer Guide”• http://www.unofficialmysqlguide.com/• More detailed text for many of the examples here…• Most still applies to 5.6+• EXPLAIN FORMAT=JSON in 5.6 does not show cost• Costs will be different• Output from Optimizer Trace may differ• Some features will be missing11

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Danger: Code on slides!• Some examples may appear small• Please feel free to download this deck from:• https://www.slideshare.net/morgo/mysql-80-optimizer-guide• Follow along on your laptop12

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Just the Important Parts• Comprised of the Server and Storage Engines• Query Optimization happens at the Server Level• Semantically there are four stages of Query Optimization• Followed by Query Execution15

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |B+trees• When we mean “add an index” we usually mean “add a B+treeindex”:– Includes PRIMARY, UNIQUE, INDEX type indexes.• Understanding the basic structure of B+trees helps withoptimization17

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Binary Tree• Not the same as a B+tree• Understand Binary Tree first then compare and contrast18Locate 829813 in a(balanced) binary tree of1MM ~= 20 hops.is this good?

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |B+tree• Amortizes disk accesses by clustering into pages:• Can achieve same outcome in two hops:19CREATE TABLE users ( id INT NOT NULL auto_increment, username VARCHAR(32) NOT NULL, payload TEXT, PRIMARY KEY (id) );

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |B-trees are wide not deep• From the root page: values >= 800788 but < 829908 are on page16386.• From page 16386: values >= 829804 but < 829830 are on leaf page32012.• Large fan out factor; 1000+ keys/page which point to another indexpage with 1000+ keys/page21

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |InnoDB uses a Clustered Index• In InnoDB the data rows are also stored in a B+tree, organized bythe primary key• Secondary key indexes always include the value of the primary key22

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |EXPLAIN• Pre-execution view of how MySQL intends to execute a query• Prints what MySQL considers the best plan after a process ofconsidering potentially thousands of choices24

EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE continent='Asia' and population > 5000000;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "25.40"},"table": {"table_name": "country","access_type": "ALL","rows_examined_per_scan": 239,"rows_produced_per_join": 11,"filtered": "6.46",.."attached_condition": "((`world`.`country`.`Continent` = 'Asia')and (`world`.`country`.`Population` > 5000000))"}}}

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |What indexes will make this query faster?• Some Suggestions:– Index on p (population)– Index on c (continent)– Index on p_c (population, continent)– Index on c_p (continent, population)26

ALTER TABLE Country ADD INDEX p (population);EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE continent='Asia' and population > 5000000;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "25.40"},"table": {"table_name": "Country","access_type": "ALL","possible_keys": ["p"],"rows_examined_per_scan": 239,"rows_produced_per_join": 15,"filtered": "6.46",.."attached_condition": "((`world`.`country`.`Continent` = 'Asia') and(`world`.`country`.`Population` > 5000000))"..

ALTER TABLE Country ADD INDEX p (population);EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE continent='Asia' and population > 5000000;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "25.40"},"table": {"table_name": "Country","access_type": "ALL","possible_keys": ["p"],"rows_examined_per_scan": 239,"rows_produced_per_join": 15,"filtered": "6.46","cost_info": {"read_cost": "23.86","eval_cost": "1.54","prefix_cost": "25.40","data_read_per_join": "3K"},.."attached_condition": "((`world`.`country`.`Continent` = 'Asia') and(`world`.`country`.`Population` > 5000000))"..It’s available but notused. Why?

SET optimizer_trace="enabled=on";SELECT * FROM Country WHERE continent='Asia' and population > 5000000;SELECT * FROM information_schema.optimizer_trace;{"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `country`.`Code` AS `Code`,`country`.`Name` AS`Name`,`country`.`Continent` AS `Continent`,`country`.`Region` AS `Region`,`country`.`SurfaceArea` AS`SurfaceArea`,`country`.ÌndepYear` AS ÌndepYear`,`country`.`Population` AS`Population`,`country`.`LifeExpectancy` AS `LifeExpectancy`,`country`.`GNP` AS`GNP`,`country`.`GNPOld` AS `GNPOld`,`country`.`LocalName` AS `LocalName`,`country`.`GovernmentFormÀS `GovernmentForm`,`country`.`HeadOfState` AS `HeadOfState`,`country`.`Capital` AS`Capital`,`country`.`Code2` AS `Code2` from `country` where ((`country`.`Continent` = 'Asia') and(`country`.`Population` > 5000000))"}]}},{"join_optimization": {"select#": 1,"steps": [{"condition_processing": {"condition": "WHERE",Page 1 of 6

"original_condition": "((`country`.`Continent` = 'Asia') and (`country`.`Population` > 5000000))","steps": [{"transformation": "equality_propagation","resulting_condition": "((`country`.`Population` > 5000000) and multiple equal('Asia',`country`.`Continent`))"},{"transformation": "constant_propagation","resulting_condition": "((`country`.`Population` > 5000000) and multiple equal('Asia',`country`.`Continent`))"},{"transformation": "trivial_condition_removal","resulting_condition": "((`country`.`Population` > 5000000) and multiple equal('Asia',`country`.`Continent`))"}]}},{"substitute_generated_columns": {}},{"table_dependencies": [{"table": "`country`","row_may_be_null": false,"map_bit": 0,Page 2 of 6

"depends_on_map_bits": []}]},{"ref_optimizer_key_uses": []},{"rows_estimation": [{"table": "`country`","range_analysis": {"table_scan": {"rows": 239,"cost": 27.5},"potential_range_indexes": [{"index": "PRIMARY","usable": false,"cause": "not_applicable"},{"index": "p","usable": true,"key_parts": ["Population","Code"]Page 3 of 6

}],"setup_range_conditions": [],"group_index_range": {"chosen": false,"cause": "not_group_by_or_distinct"},"analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "p","ranges": ["5000000 < Population"],"index_dives_for_eq_ranges": true,"rowid_ordered": false,"using_mrr": false,"index_only": false,"rows": 108,"cost": 38.06,"chosen": false,"cause": "cost"}],"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"}}}Aha! It was tooexpensive.Page 4 of 6

}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`country`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 239,"access_type": "scan","resulting_rows": 239,"cost": 25.4,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 239,"cost_for_plan": 25.4,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": "((`country`.`Continent` =  'Asia') and (`country`.`Population` > 5000000))",Prefer to table scaninsteadPage 5 of 6

"attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`country`","attached": "((`country`.`Continent` = 'Asia') and (`country`.`Population` > 5000000))"}]}},{"refine_plan": [{"table": "`country`"}]}]}},{"join_execution": {"select#": 1,"steps": []}}]}Page 6 of 6

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 38Why would an index not be used?"analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "p","ranges": ["5000000 < Population"],"index_dives_for_eq_ranges": true,"rowid_ordered": false,"using_mrr": false,"index_only": false,"rows": 108,"cost": 38.06,"chosen": false,"cause": "cost"}],OPTIMIZER TRACE:.."query_block": {"select_id": 1,"cost_info": {"query_cost": "48.86"},"table": {"table_name": "Country","access_type": "range","possible_keys": ["p"],"key": "p",..FORCE INDEX (p):

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Why eliminate unnecessary work?• Short-cut/reduce number of execution plans that need to beevaluated• Transform parts of queries to take advantage of better executionstrategies• Think of a how a compiler transforms code to be more efficient• MySQL does similar at runtime42

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |SHOW WARNINGS says:EXPLAIN FORMAT=JSON SELECT * FROM Country WHERE population > 5000000 AND 1=1;SHOW WARNINGS;/* select#1 */ select `world`.`Country`.`Code` AS `Code`, `world`.`Country`.`Name` AS `Name`, `world`.`Country`.`Continent` AS `Continent`, `world`.`Country`.`Region` AS `Region`, `world`.`Country`.`SurfaceArea` AS `SurfaceArea`, `world`.`Country`.`IndepYear` AS `IndepYear`, `world`.`Country`.`Population` AS `Population`, `world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`, `world`.`Country`.`GNP` AS `GNP`, `world`.`Country`.`GNPOld` AS `GNPOld`, `world`.`Country`.`LocalName` AS `LocalName`, `world`.`Country`.`GovernmentForm` AS `GovernmentForm`, `world`.`Country`.`HeadOfState` AS `HeadOfState`, `world`.`Country`.`Capital` AS `Capital`, `world`.`Country`.`Code2` AS `Code2` from `world`.`Country` where (`world`.`Country`.`Population` > 5000000)44

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |.."steps": [{"condition_processing": {"condition": "WHERE","original_condition": "((`Country`.`Population` > 5000000) and (1 = 1))","steps": [{"transformation": "equality_propagation","resulting_condition": "((`Country`.`Population` > 5000000) and (1 = 1))"},{"transformation": "constant_propagation","resulting_condition": "((`Country`.`Population` > 5000000) and (1 = 1))"},{"transformation": "trivial_condition_removal","resulting_condition": "(`Country`.`Population` > 5000000)"..OPTIMIZER TRACE says:45

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |What sort of transformations can occur?• Merging views back with definition of base tables• Derived table in FROM clause merged back into base tables• Unique subqueries converted directly to INNER JOIN statements• Primary key lookup converted to constant values.– Shortcut plans that will need to be evaluated.46

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Primary Key LookupSELECT * FROM Country WHERE code='CAN'/* select#1 */ select'CAN' AS `Code`,'Canada' AS `Name`,'North America' AS `Continent`,'North America' AS `Region`,'9970610.00' AS `SurfaceArea`,'1867' AS `IndepYear`,'31147000' AS `Population`,'79.4' AS `LifeExpectancy`,'598862.00' AS `GNP`,'625626.00' AS `GNPOld`,'Canada' AS `LocalName`,'Constitutional Monarchy, Federation' AS `GovernmentForm`,'Elisabeth II' AS `HeadOfState`,'1822' AS `Capital`,'CA' AS `Code2`from `world`.`Country` where 147

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Primary key does not existSELECT * FROM Country WHERE code='XYZ'/* select#1 */ select NULL AS `Code`,NULL AS`Name`,NULL AS `Continent`,NULL AS `Region`, NULL AS`SurfaceArea`,NULL AS `IndepYear`,NULL AS`Population`,NULL AS `LifeExpectancy`,NULL AS `GNP`,NULL AS `GNPOld`,NULL AS `LocalName`,NULL AS`GovernmentForm`,NULL AS `HeadOfState`,NULL AS`Capital`, NULL AS `Code2` from `world`.`Country`where multiple equal('XYZ', NULL)48

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Impossible WHERESELECT * FROM Country WHERE code='CAN' AND 1=0/* select#1 */ select `world`.`Country`.`Code` AS`Code`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`Continent` AS `Continent`,`world`.`Country`.`RegionÀS `Region`, `world`.`Country`.`SurfaceArea` AS`SurfaceArea`,`world`.`Country`.ÌndepYear` AS ÌndepYear`,`world`.`Country`.`Population` AS`Population`,`world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`Country`.`GNP` AS `GNP`,`world`.`Country`.`GNPOld` AS`GNPOld`, `world`.`Country`.`LocalName` AS`LocalName`,`world`.`Country`.`GovernmentForm` AS `GovernmentForm`,`world`.`Country`.`HeadOfState` AS`HeadOfState`,`world`.`Country`.`Capital` AS `Capital`,`world`.`Country`.`Code2` AS `Code2` from `world`.`Country` where 049

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Are transformations always safe?• Yes they should be• New transformations (and execution strategies) may return nondeterministic queries in a different order• Some illegal statements as a result of derived_mergetransformation50

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Query Optimizer Strategy• Model each of the possible execution plans (using support fromstatistics and meta data)• Pick the plan with the lowest cost53

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Model you say?1. Assign a cost to each operation2. Evaluate how many operations each possible plan would take3. Sum up the total4. Choose the plan with the lowest overall cost54

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 56Example Model: Table ScanSELECT * FROM Country WHERE continent='Asia' and population > 5000000;IO Cost:# pages in table * (IO_BLOCK_READ_COST | MEMORY_BLOCK_READ_COST)CPU Cost:# records * ROW_EVALUATE_COSTDefaults: IO_BLOCK_READ_COST = 1 MEMORY_BLOCK_READ_COST = 0.25 ROW_EVALUATE_COST=0.1Values:# pages in table = 6# records = 239100% on Disk:  = (6 * 1) + (0.1 * 239)= 29.9EXPLAIN saidcost was 25.40New! MySQL 8.0 estimates howmany of the pages will be inmemory.SELECT clust_index_size fromINNODB_SYS_TABLESTATS WHEREname='world/country'100% in Memory: = (6 * 0.25) + (0.1 * 239)= 25.4

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 57Example Model: Range ScanSELECT * FROM Country WHERE continent='Asia' and population > 5000000;IO Cost:# records_in_range * (IO_BLOCK_READ_COST | MEMORY_BLOCK_READ_COST) CPU Cost: # records_in_range * ROW_EVALUATE_COST + # records_in_range * ROW_EVALUATE_COST      = (108 * 0.25) + ( (108 * 0.1) + (108 * 0.1) )= 48.6Evaluate range conditionEvaluate WHERE conditionCompares to "query_cost":“48.86" in EXPLAIN.100% in memory.On disk = 129.6

{"query_block": {"select_id": 1,"cost_info": {"query_cost": "25.40"},"table": {"table_name": "country","access_type": "ALL","possible_keys": ["p"],.."cost_info": {"read_cost": "23.86","eval_cost": "1.54","prefix_cost": "25.40","data_read_per_join": "3K"},..CPU CostIO CostTotal Cost

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Cost Constant Refinementselect * from mysql.server_cost;+------------------------------+------------+---------------------+---------+---------------+| cost_name | cost_value | last_update | comment | default_value |+------------------------------+------------+---------------------+---------+---------------+| disk_temptable_create_cost | NULL | 2017-04-14 16:01:42 | NULL | 20 || disk_temptable_row_cost | NULL | 2017-04-14 16:01:42 | NULL | 0.5 || key_compare_cost | NULL | 2017-04-14 16:01:42 | NULL | 0.05 || memory_temptable_create_cost | NULL | 2017-04-14 16:01:42 | NULL | 1 || memory_temptable_row_cost | NULL | 2017-04-14 16:01:42 | NULL | 0.1 || row_evaluate_cost | NULL | 2017-04-14 16:01:42 | NULL | 0.1 |+------------------------------+------------+---------------------+---------+---------------+6 rows in set (0.00 sec)select * from mysql.engine_costG*************************** 1. row ***************************engine_name: defaultdevice_type: 0cost_name: io_block_read_costcost_value: NULLlast_update: 2017-04-14 16:01:42comment: NULLdefault_value: 159

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Cost Constant RefinementUPDATE mysql.server_cost SET cost_value=1 WHERE cost_name=‘row_evaluate_cost';UPDATE mysql.engine_cost set cost_value = 1;FLUSH OPTIMIZER_COSTS;EXPLAIN FORMAT=JSON SELECT * FROM Country WHERE continent='Asia' and population > 5000000;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "245.00"},"table": {"table_name": "Country","access_type": "ALL",..60Increase row evaluate cost from0.1 to 1. Make memory and IOblock read cost the same.New Table Scan Cost:= (6 * 1) + (1 * 239)= 245

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Are plans exhaustively evaluated?• Short cuts are taken to not spend too much time in planning:– Some parts of queries may be transformed to limit plansevaluated– The optimizer will by default limit the search depth of bad plans: optimizer_search_depth=64 optimizer_prune_level=161

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |How often is the query optimizer wrong?• Yes it happens• Similar to GPS; you may not have traffic data available for allstreets• The model may be incomplete or imperfect• There exist method(s) to overwrite it63

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Hints and Switches• Typically a better level of override to modifying cost constants• Come in three varieties:– Old Style Hints– New Comment-Style Hints– Switches64

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Old Style Hints• Have SQL and Hint intermingled• Cause errors when indexes don’t exist65SELECT * FROM Country FORCE INDEX (p) WHERE population >5000000;SELECT * FROM Country IGNORE INDEX (p) WHERE population >5000000;SELECT * FROM Country USE INDEX (p) WHERE population > 5000000;SELECT STRAIGHT_JOIN ..;SELECT * FROM Country STRAIGHT_JOIN ..;

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |New Comment-Style Hints• Can be added by a system that doesn’t understand SQL• Clearer defined semantics as a hint not a directive• Fine granularity66SELECT/*+ NO_RANGE_OPTIMIZATION (Country) */* FROM CountryWHERE Population > 1000000000 AND Continent=‘Asia';

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Switches• As new optimizations are added, some cause regressions• Allow the specific optimization to be disabled (SESSION or GLOBAL)67SELECT @@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, block_nested_loop=on,batched_key_access=off,materialization=on, semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on, subquery_materialization_cost_based=on,use_index_extensions=on, condition_fanout_filter=on,derived_merge=on

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |How to consider hints and switches• They provide immediate pain relief to production problems at thecost of maintenance• They add technical debt to your applications68

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Our simple query with n candidate indexes• Indexes exist on p(population) and c(continent):70SELECT * FROM Country WHERE population > 50000000 AND continent=‘Asia';>50M, how manyare less?How many countries inAsia vs total world?Does order ofpredicates matter? No.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Role of the Optimizer• Given these many choices, which is the best choice?• A good GPS navigator finds the fastest route!• We can expect a good query optimizer to do similar71

ALTER TABLE Country ADD INDEX c (continent);EXPLAIN FORMAT=JSON # 50M SELECT * FROM Country WHERE population > 50000000 AND continent=‘Asia';{"query_block": {"select_id": 1,"cost_info": {"query_cost": "9.60"},"table": {"table_name": "Country","access_type": "ref","possible_keys": ["p","c"],"key": "c","used_key_parts": ["Continent"],"key_length": "1","ref": ["const"],.."attached_condition": "(`world`.`country`.`Population` > 50000000)"..Continent isdetermined to be lowercost.

EXPLAIN FORMAT=JSON # 500MSELECT * FROM Country WHERE continent='Asia' and population > 500000000;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "1.16"},"table": {"table_name": "Country","access_type": "range","possible_keys": ["p","c"],"key": "p","used_key_parts": ["Population"],"key_length": "4",.."attached_condition": "(`world`.`country`.`Continent` = ‘Asia')"..Change the predicate,the query plan changes.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Query Plan Evaluation• Evaluated for each query, and thus each set of predicates• Currently not cached*• For prepared statements, permanent transformations are cached74* Cardinality statistics are cached. Don’t get confused.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |The role of composite indexes• Useful when two or more predicates combined improves filteringeffect. i.e.  Not all countries with a population > 5M are in Asia77

ALTER TABLE Country ADD INDEX p_c (Population, Continent);EXPLAIN FORMAT=JSONSELECT * FROM Country FORCE INDEX (p_c) WHERE continent='Asia' and population >5000000;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "48.86"},"table": {"table_name": "Country","access_type": "range","possible_keys": ["p_c"],"key": "p_c","used_key_parts": ["Population"],"key_length": "4",..Only part of the key isused!

ALTER TABLE Country ADD INDEX c_p (Continent, Population); EXPLAIN FORMAT=JSON SELECT * FROM Country WHERE continent='Asia' and population > 5000000; {"query_block": {"select_id": 1,"cost_info": {"query_cost": "7.91"},"table": {"table_name": "Country","access_type": "range","possible_keys": ["p","c","p_c","c_p"],"key": "c_p","used_key_parts": ["Continent","Population"],"key_length": “5”, ..All of the key isused

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Composite Left-most Rule• An index on (Continent, Population) can also be used as an index on(Continent)• It can not be used as an index on (Population)82

EXPLAIN FORMAT=JSON  SELECT * FROM Country FORCE INDEX (c_p) WHERE population >500000000;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "83.90"},"table": {"table_name": "Country","access_type": "ALL","rows_examined_per_scan": 239,"rows_produced_per_join": 79,"filtered": "33.33",.."attached_condition": "(`world`.`country`.`Population` >500000000)"..

ALTER TABLE Country ADD INDEX c_p_n (Continent,Population,Name);EXPLAIN FORMAT=JSONSELECT Name FROM Country WHERE continent='Asia' and population > 5000000;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "3.72"},"table": {"table_name": "Country","access_type": "range","possible_keys": [.."c_p_n"],"key": "c_p_n","used_key_parts": ["Continent","Population"],"key_length": "5",.."filtered": "100.00","using_index": true,..Cost is reduced by53%Using index means"covering index"

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Use cases• Can be used as in this example• Also beneficial in join conditions (join through covering index onintermediate table)• Useful in aggregate queries87

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Visual Explain• For complex queries, it is useful to see visual representation• Visualizations in this deck are produced by MySQL Workbench.89

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |A quick recap:• So far we’ve talked about 4 candidate indexes:– p (population)– c (continent)– p_c (population, continent)– c_p (continent, population)• We’ve always used c=‘Asia’ and p > 5M91

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 92Cost Estimatesp>5M c=’Asia’ p>5M c=’Antarctica’ p>50M, c=’Asia’p>50Mc=’Antarctica’p>500M, c=’Asia’p>500Mc=’Antarctica’p 48.86 48.86 11.06 11.06 1.16 1.16c 9.60 1.75 9.60 1.75 9.60 1.75c_p 7.91 0.71 5.21 0.71 1.16 0.71p_c 48.86 48.86 11.06 11.06 1.16 1.16ALL 25.40 25.40 25.40 25.40 25.40 25.40

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Subquery (Scalar)• Can optimize away the inner part first and then cache it.• This avoids re-executing the inner part for-each-row96SELECT * FROM Country WHERE Code = (SELECT CountryCode FROM City WHEREname=‘Toronto’);

EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE Code = (SELECT CountryCode FROM City WHERE name=‘Toronto’);{"query_block": {"select_id": 1,"cost_info": {"query_cost": "1.00"},"table": {"table_name": "Country","access_type": "const",.."key": "PRIMARY",..},"optimized_away_subqueries": [{"dependent": false,"cacheable": true,"query_block": {"select_id": 2,"cost_info": {"query_cost": "425.05"},"table": {"table_name": "City","access_type": "ALL",..(misleadingvisualization)First query + itscostSecond query +its cost

ALTER TABLE city ADD INDEX n (name);EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE Code = (SELECT CountryCode FROM City WHERE name=‘Toronto’);{"query_block": {"select_id": 1,"cost_info": {"query_cost": "1.00"},"table": {"table_name": "Country","access_type": "const",.."key": "PRIMARY",..},"optimized_away_subqueries": [{"dependent": false,"cacheable": true,"query_block": {"select_id": 2,"cost_info": {"query_cost": "0.35"},"table": {"table_name": "City","access_type": "ref","possible_keys": ["n"],"key": “n",..First query + itscostSecond query +its cost(misleadingvisualization)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Subquery (IN list)• When the result inner subquery returns unique results it can safelybe transformed to an inner join:99EXPLAIN FORMAT=JSON SELECT * FROM City WHERE CountryCode IN (SELECT Code FROM Country WHERE Continent = 'Asia');show warnings;/* select#1 */ select `world`.`city`.ÌD` AS ÌD`,`world`.`city`.`Name` AS`Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`DistrictÀS `District`,`world`.`city`.`Population` AS `Population` from`world`.`country` join `world`.`city` where ((`world`.`city`.`CountryCode` =`world`.`country`.`Code`) and (`world`.`country`.`Continent` = 'Asia'))1 row in set (0.00 sec)

EXPLAIN FORMAT=JSONSELECT * FROM City WHERE CountryCode IN (SELECT Code FROM Country WHERE Continent = 'Asia');{"query_block": {"select_id": 1,"cost_info": {"query_cost": "327.58"},"nested_loop": [{"table": {"table_name": "Country","access_type": "ref",.."key": "c",.."using_index": true,.."used_columns": ["Code","Continent"..{"table": {"table_name": "City","access_type": "ref","possible_keys": ["CountryCode"],"key": "CountryCode",.."ref": ["world.Country.Code"],..

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |SELECT * FROM Country WHERE Code IN(SELECT CountryCode FROM CountryLanguage  WHERE isOfficial=1);Subquery (cont.)• When non-unique the optimizer needs to pick a semi-join strategy• Multiple options: FirstMatch, MaterializeLookup,DuplicatesWeedout101

ALTER TABLE CountryLanguage ADD INDEX i (isOfficial);EXPLAIN FORMAT=JSON SELECT * FROM Country WHERE Code IN(SELECT CountryCode FROM CountryLanguage WHERE isOfficial=1);{"query_block": {"select_id": 1,"cost_info": {"query_cost": "98.39"},"nested_loop": [{"table": {"table_name": "Country","access_type": "ALL","possible_keys": ["PRIMARY"],.."filtered": "100.00",.."table": {"table_name": "<subquery2>","access_type": "eq_ref","key": "<auto_key>","key_length": "3","ref": ["world.Country.Code"],"rows_examined_per_scan": 1,"materialized_from_subquery": {"using_temporary_table": true,"query_block": {"table": {"table_name": "CountryLanguage","access_type": "ref",.."key": "i",.."using_index": true,..

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Views• A way of saving a SELECT statement as a table• Allows for simplified queries• Processed using one of two methods internally:– Merge - transform the view to be combined with the query.– Materialize - save the contents of the view in a temporary table,then begin querying104

ALTER TABLE country ADD INDEX c_n (continent, name);CREATE VIEW vCountry_Asia AS SELECT * FROM Country WHERE Continent='Asia';EXPLAIN FORMAT=JSONSELECT * FROM vCountry_Asia WHERE Name='China';{"query_block": {"select_id": 1,"cost_info": {"query_cost": "0.35"},"table": {"table_name": "country","access_type": "ref","possible_keys": [.."c_n"],"key": "c_n","used_key_parts": ["Continent","Name"],"key_length": "53","ref": ["const","const"],..This is the base tablePredicates from the viewdeﬁnition and querycombined

SHOW WARNINGS;/* select#1 */ select`world`.`Country`.`Code` AS `Code`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`Continent` AS `Continent`,`world`.`Country`.`Region` AS `Region`,`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`IndepYear` AS `IndepYear`,`world`.`Country`.`Population` AS `Population`,`world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`Country`.`GNP` AS `GNP`,`world`.`Country`.`GNPOld` AS `GNPOld`,`world`.`Country`.`LocalName` AS `LocalName`,`world`.`Country`.`GovernmentForm` AS `GovernmentForm`,`world`.`Country`.`HeadOfState` AS `HeadOfState`,`world`.`Country`.`Capital` AS `Capital`,`world`.`Country`.`Code2` AS `Code2`from `world`.`Country`where((`world`.`Country`.`Continent` = 'Asia')and (`world`.`Country`.`Name` = 'China'))

CREATE VIEW vCountrys_Per_Continent ASSELECT Continent, COUNT(*) as Count FROM CountryGROUP BY Continent;EXPLAIN FORMAT=JSONSELECT * FROM vCountrys_Per_Continent WHERE Continent='Asia';{"query_block": {"select_id": 1,"cost_info": {"query_cost": "3.64"},"table": {"table_name": "vCountrys_Per_Continent","access_type": "ref","possible_keys": ["<auto_key0>"],"key": "<auto_key0>","used_key_parts": ["Continent"],"key_length": "1","ref": ["const"],.."used_columns": ["Continent","Count"],..This is the view name.."materialized_from_subquery": {"using_temporary_table": true,"dependent": false,"cacheable": true,"query_block": {"select_id": 2,"cost_info": {"query_cost": "25.40"},This is only the cost ofaccessing the materialized tableThis step happens ﬁrst.

SHOW WARNINGS;/* select#1 */ select`vCountrys_Per_Continent`.`Continent` AS `Continent`,`vCountrys_Per_Continent`.`Count` AS `Count`from `world`.`vCountrys_Per_Continent`where (`vCountrys_Per_Continent`.`Continent` = 'Asia')

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |WITH (CTE)• A view for query-only duration• Same optimizations available as views:– Merge - transform the CTE to be combined with the query.– Materialize - save the contents of the CTE in a temporary table,then begin querying109

# Identical Queries - CTE and VIEWWITH vCountry_Asia AS (SELECT * FROM Country WHEREContinent='Asia')SELECT * FROM vCountry_Asia WHERE Name='China';CREATE VIEW vCountry_Asia AS SELECT * FROM Country WHEREContinent='Asia';SELECT * FROM vCountry_Asia WHERE Name='China';

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |CTEs are new!• May provide performance enhancements over legacy code using temporarytables - which never merge.• Derived tables may need to materialize more than once. A CTE does not! i.e.111SELECT * FROM my_table, (SELECT ... ) as t1 ...UNION ALLSELECT * FROM my_table, (SELECT ... ) as t1 ...

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |WITH RECURSIVE - new!WITH RECURSIVE my_cte AS (SELECT 1 AS nUNION ALLSELECT 1+n FROM my_cte WHERE n<10)SELECT * FROM my_cte;+------+| n |+------+| 1 || 2 |..| 9 || 10 |+------+10 rows in set (0.01 sec)112

{"query_block": {"select_id": 1,"cost_info": {"query_cost": "2.84"},"table": {"table_name": "my_cte","access_type": "ALL",.."used_columns": ["n"],"materialized_from_subquery": {"using_temporary_table": true,"dependent": false,"cacheable": true,"query_block": {"union_result": {"using_temporary_table": false,....{"dependent": false,"cacheable": true,"query_block": {"select_id": 3,"recursive": true,"cost_info": {"query_cost": "2.72"},"table": {"table_name": "my_cte","access_type": "ALL",.."used_columns": ["n"],"attached_condition":  "(`my_cte`.`n` < 10)"}..Requires atemporary table forintermediate resultsCost per iteration

SELECTCountry.Name as Country, City.Name as Capital,LanguageFROMCityINNER JOIN Country ON Country.Capital=City.idINNER JOIN CountryLanguage ONCountryLanguage.CountryCode=Country.codeWHERECountry.Continent='Asia' andCountryLanguage.IsOfficial='T';

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Join Strategy (Nested Loop Join)1. Pick Driving Table (Country)2. For each row in Country step through to City table3. For each row in City table step through to CountryLanguage table4. Repeat116

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Join efficiency• Important to eliminate work before accessing other tables (WHEREclause should have lots of predicates that filter driving table)• Indexes are required on the columns that connect between drivingtable, and subsequent tables:117ON Country.Capital=City.id

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |INNER JOIN vs LEFT JOIN• LEFT JOIN semantically says “right row is optional”.– Forces JOIN order to be left side first.– Reduces possible ways to join tables118

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Join Order Hints• One of the most frequent types of hints to apply• New join order hints in 8.0:– JOIN_FIXED_ORDER– JOIN_ORDER– JOIN_PREFIX– JOIN_SUFFIX119

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Group By - Loose Index Scan• Scan the index from start to finish without buffering. Results arepipelined to client:121SELECT count(*) as c, continent FROM Country  GROUP BY continent;

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Group By - Index Filtering Rows• Use the index to eliminate as much work as possible• Store rows in intermediate temporary file and then sort122

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Group By - Index Filtering + Guaranteed Order• Use the index to eliminate as much work as possible• The index also maintains order123

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |UNION• Requires an intermediate temporary table to weed out duplicaterows• The optimizer does not really have any optimizations for UNION(such as a merge with views)124

EXPLAIN FORMAT=JSONSELECT * FROM City WHERE CountryCode = 'CAN'UNIONSELECT * FROM City WHERE CountryCode = 'USA'{"union_result": {"using_temporary_table": true,"table_name": "<union1,2>","access_type": "ALL","query_specifications": [{"dependent": false,"cacheable": true,"query_block": {"select_id": 1,"cost_info": {"query_cost": "17.15"},"table": {"table_name": "City","access_type": "ref",.."key": "CountryCode",..Temporary table tode-duplicate{"dependent": false,"cacheable": true,"query_block": {"select_id": 2,"cost_info": {"query_cost": "46.15"},"table": {"table_name": "City","access_type": "ref","possible_keys": ["CountryCode"],"key": "CountryCode","used_key_parts": ["CountryCode"],"key_length": "3","ref": ["const"],..

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |UNION ALL• Results may contain duplicate rows• Does not require an intermediate temporary table in simple usecases. i.e. no result ordering.• Otherwise similar to UNION126

EXPLAIN FORMAT=JSONSELECT * FROM City WHERE CountryCode = 'CAN'UNION ALLSELECT * FROM City WHERE CountryCode = 'USA'{"query_block": {"union_result": {"using_temporary_table": false,"query_specifications": [{"dependent": false,"cacheable": true,"query_block": {"select_id": 1,"cost_info": {"query_cost": "17.15"},"table": {"table_name": "City","access_type": "ref",.."key": "CountryCode",..{"dependent": false,"cacheable": true,"query_block": {"select_id": 2,"cost_info": {"query_cost": "46.15"},"table": {"table_name": "City","access_type": "ref","possible_keys": ["CountryCode"],"key": "CountryCode","used_key_parts": ["CountryCode"],"key_length": "3","ref": ["const"],..No temporary table

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Descending Indexes• B+tree indexes are ordered• In 8.0 you can specify the order• Use cases:– Faster to scan in order– Can’t change direction in a composite index129

EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE continent='Asia' AND population > 5000000ORDER BY population DESC;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "7.91"},"ordering_operation": {"using_filesort": false,"table": {"table_name": "Country","access_type": "range",.."key": "c_p",.."backward_index_scan": true,..Still uses the index, butabout 15% slower

EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE continent IN ('Asia', 'Oceania') AND population > 5000000ORDER BY continent ASC, population DESC{"query_block": {"select_id": 1,"cost_info": {"query_cost": "48.36"},"ordering_operation": {"using_filesort": true,"cost_info": {"sort_cost": "33.00"},"table": {"table_name": "Country","access_type": "range","key": "c_p",.."rows_examined_per_scan": 33,"rows_produced_per_join": 33,"filtered": "100.00",..Must sort values ofpopulation in reverse

ALTER TABLE Country DROP INDEX c_p, DROP INDEX c_p_n,ADD INDEX c_p_desc (continent ASC, population DESC);EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE continent IN ('Asia', 'Oceania') AND population > 5000000ORDER BY continent ASC, population DESC;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "15.36"},"ordering_operation": {"using_filesort": false,"table": {"table_name": "Country","access_type": "range",.."key": "c_p_desc","used_key_parts": ["Continent","Population"],"key_length": "5",..TIP: The optimizer does not consider sort cost inevaluating plans. You may need to FORCE INDEX orDROP similar ascending indexes to use it.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Via an Index• B+tree indexes are ordered• Some ORDER BY queries do not require sorting at all135EXPLAIN FORMAT=JSONSELECT * FROM Country WHERE continent='Asia' ORDER BY population;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "9.60"},"ordering_operation": {"using_filesort": false,.."key": "c_p",The order is provided byc_p

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Via a Priority Queue• Special ORDER BY + small limit optimization• Keeps top N records in an in memory buffer• Usage is NOT shown in EXPLAIN136SELECT * FROM Country IGNORE INDEX (p, p_c)ORDER BY population LIMIT 10;

"select#": 1,"steps": [{"filesort_information": [{"direction": "asc","table": "`country` IGNORE INDEX (`p_c`) IGNORE INDEX (`p`)","field": "Population"}],"filesort_priority_queue_optimization": {"limit": 10,"chosen": true},"filesort_execution": [],"filesort_summary": {"memory_available": 262144,"key_size": 4,"row_size": 272,"max_rows_per_buffer": 11,"num_rows_estimate": 587,"num_rows_found": 11,"num_examined_rows": 239,"num_tmp_files": 0,"sort_buffer_size": 3080,"sort_algorithm": "std::sort","unpacked_addon_fields": "using_priority_queue","sort_mode": "<fixed_sort_key, additional_fields>”..OPTIMIZER TRACEshowing Priority Queue forsort

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Using Temporary Files• Either “Alternative Sort Algorithm” (no blobs present) or “OriginalSort Algorithm”138SELECT * FROM Country IGNORE INDEX (p, p_c)ORDER BY population;

"select#": 1,"steps": [{"filesort_information": [{"direction": "asc","table": "`country` IGNORE INDEX (`p_c`) IGNORE INDEX (`p`)","field": "Population"}],"filesort_priority_queue_optimization": {"usable": false,"cause": "not applicable (no LIMIT)"},"filesort_execution": [],"filesort_summary": {"memory_available": 262144,"key_size": 4,"row_size": 274,"max_rows_per_buffer": 587,"num_rows_estimate": 587,"num_rows_found": 239,"num_examined_rows": 239,"num_tmp_files": 0,"sort_buffer_size": 165536,"sort_algorithm": "std::stable_sort","sort_mode": "<fixed_sort_key, packed_additional_fields>"..Not Using Priority Sort

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Use Cases• Can be a better fit low cardinality columns than indexing• Useful for time series data with retention scheme• i.e. drop data older than 3 months• Data where queries always have some locality• i.e. store_id, region142

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Partition Pruning• Optimizer looks at query and identifies which partitions need to beaccessed143ALTER TABLE CountryLanguage MODIFY IsOfficial CHAR(1) NOT NULL DEFAULT 'F', DROPPRIMARY KEY, ADD PRIMARY KEY(CountryCode, Language, IsOfficial); ALTER TABLE CountryLanguage PARTITION BY LIST COLUMNS (IsOfficial) (PARTITION pUnofficial VALUES IN ('F'),PARTITION pOfficial VALUES IN ('T'));

EXPLAIN FORMAT=JSONSELECT * FROM CountryLanguage WHERE isOfficial='T' ANDCountryCode='CAN';{"query_block": {"select_id": 1,"cost_info": {"query_cost": "2.40"},"table": {"table_name": "CountryLanguage","partitions": ["pOfficial"],"access_type": "ref",.."key": "PRIMARY",..Only accesses onepartition

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Explicit Partition Selection• Also possible to “target” a partition• Consider this similar to query hints145SELECT * FROM CountryLanguage PARTITION (pOfficial)WHERE CountryCode='CAN';

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Query Rewrite• MySQL allows you to change queries before they are executed• Insert a hint, or remove a join that is not required147mysql -u root -p < install_rewriter.sqlINSERT INTO query_rewrite.rewrite_rules(pattern_database, pattern,replacement) VALUES ("world","SELECT * FROM Country WHERE population > ? AND continent=?","SELECT * FROM Country WHERE population > ? AND continent=? LIMIT 1");CALL query_rewrite.flush_rewrite_rules();

SELECT * FROM Country WHERE population > 5000000 ANDcontinent='Asia';SHOW WARNINGS;*********************** 1. row ***********************Level: NoteCode: 1105Message: Query 'SELECT * FROM Country WHERE population >5000000 AND continent='Asia'' rewritten to 'SELECT *FROM Country WHERE population > 5000000 ANDcontinent='Asia' LIMIT 1' by a query rewrite plugin1 row in set (0.00 sec)

SELECT * FROM query_rewrite.rewrite_rulesG********************** 1. row **********************id: 1pattern: SELECT * FROM Country WHEREpopulation > ? AND continent=?pattern_database: worldreplacement: SELECT * FROM Country WHEREpopulation > ? AND continent=? LIMIT 1enabled: YESmessage: NULLpattern_digest: 88876bbb502cef6efddcc661cce77debnormalized_pattern: select `*` from `world`.`country`where ((`population` > ?) and (`continent` = ?))1 row in set (0.00 sec)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Changing Indexes is a Destructive Operation• Removing an index can make some queries much slower• Adding can cause some existing query plans to change• Old-style hints will generate errors if indexes are removed151

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Invisible Indexes, the “Recycle Bin”• Hide the indexes from the optimizer• Will no longer be considered as part of query execution plans• Still kept up to date and are maintained by insert/update/deletestatements152

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Invisible Indexes: Soft DeleteALTER TABLE Country ALTER INDEX c INVISIBLE;SELECT * FROM information_schema.statistics WHERE is_visible='NO';*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: worldTABLE_NAME: CountryNON_UNIQUE: 1INDEX_SCHEMA: worldINDEX_NAME: cSEQ_IN_INDEX: 1COLUMN_NAME: ContinentCOLLATION: ACARDINALITY: 7SUB_PART: NULLPACKED: NULLNULLABLE:INDEX_TYPE: BTREECOMMENT: disabledINDEX_COMMENT:IS_VISIBLE: NO153

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Invisible Indexes: Staged RolloutALTER TABLE Country ADD INDEX c (Continent)INVISIBLE;# after some timeALTER TABLE Country ALTER INDEX c VISIBLE;154

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Finding Unused IndexesSELECT * FROM sys.schema_unused_indexes;+---------------+-------------+------------+| object_schema | object_name | index_name |+---------------+-------------+------------+| world | Country | p || world | Country | p_c |+---------------+-------------+------------+2 rows in set (0.01 sec)155

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Do indexes hurt reads or writes?• They can have some impact on both:– On writes, indexes need to space, and to be maintained– On reads, lets use an example…156

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Indexes Hurting ReadsCREATE TABLE t1 (id INT NOT NULL primary key auto_increment,a VARCHAR(255) NOT NULL,b VARCHAR(255) NOT NULL,c TEXT,d TEXT,INDEX a (a),INDEX ab (a,b));# Sample QuerySELECT * FROM t1 WHERE a = 'abc' AND b = 'bcd';157Both indexes are candidates.Both will be examined.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |A use case for invisible indexes!CREATE TABLE t1 (id INT NOT NULL primary key auto_increment,a VARCHAR(255) NOT NULL,b VARCHAR(255) NOT NULL,c TEXT,d TEXT,INDEX a (a),INDEX ab (a,b));# Consider:SELECT count(*) FROM t1 FORCE INDEX (a) WHERE a='1234' AND id=1234;158Index (a) is made redundant by(a,b). Can we drop it?

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 159No, due to clustered Index!FORCE INDEX (a) WHERE a=‘1234' AND id=1234;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "0.35"},"table": {"table_name": "t1","access_type": "const","possible_keys": ["a"],"key": "a","used_key_parts": ["a","id"],..FORCE INDEX (ab) WHERE a='1234' AND id=1234;{"query_block": {"select_id": 1,"cost_info": {"query_cost": "11.80"},"table": {"table_name": "t1","access_type": "ref","possible_keys": ["ab"],"key": "ab","used_key_parts": ["a"],..

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Profiling• Optimizer only shows estimates from pre-execution view• Can be useful to know actual time spent• Support for profiling is only very basic161wget http://www.tocker.ca/files/ps-show-profiles.sqlmysql -u root -p < ps-show-profiles.sql

CALL sys.enable_profiling();CALL sys.show_profiles;*************************** 1. row ***************************Event_ID: 22Duration: 495.02 usQuery: SELECT * FROM Country WHERE co ... Asia' and population > 50000001 row in set (0.00 sec)CALL sys.show_profile_for_event_id(22);+----------------------+-----------+| Status | Duration |+----------------------+-----------+| starting | 64.82 us || checking permissions | 4.10 us || Opening tables | 11.87 us || init | 29.74 us || System lock | 5.63 us || optimizing | 8.74 us || statistics | 139.38 us || preparing | 11.94 us || executing | 348.00 ns || Sending data | 192.59 us || end | 1.17 us || query end | 4.60 us || closing tables | 4.07 us || freeing items | 13.60 us || cleaning up | 734.00 ns |+----------------------+-----------+15 rows in set (0.00 sec)

SELECT * FROM Country WHERE Continent='Antarctica' and SLEEP(5);CALL sys.show_profiles();CALL sys.show_profile_for_event_id(<event_id>);+----------------------+-----------+| Status | Duration |+----------------------+-----------+| starting | 103.89 us || checking permissions | 4.48 us || Opening tables | 17.78 us || init | 45.75 us || System lock | 8.37 us || optimizing | 11.98 us || statistics | 144.78 us || preparing | 15.78 us || executing | 634.00 ns || Sending data | 116.15 us || User sleep | 5.00 s || User sleep | 5.00 s || User sleep | 5.00 s || User sleep | 5.00 s || User sleep | 5.00 s || end | 2.05 us || query end | 5.63 us || closing tables | 7.30 us || freeing items | 20.19 us || cleaning up | 1.20 us |+----------------------+-----------+20 rows in set (0.01 sec)Sleeps for each row afterindex used on (c)

SELECT region, count(*) as c FROM Country GROUP BY region;CALL sys.show_profiles();CALL sys.show_profile_for_event_id(<event_id>);+----------------------+-----------+| Status | Duration |+----------------------+-----------+| starting | 87.43 us || checking permissions | 4.93 us || Opening tables | 17.35 us || init | 25.81 us || System lock | 9.04 us || optimizing | 3.37 us || statistics | 18.31 us || preparing | 10.94 us || Creating tmp table | 35.57 us || Sorting result | 2.38 us || executing | 741.00 ns || Sending data | 446.03 us || Creating sort index | 49.45 us || end | 1.71 us || query end | 4.85 us || removing tmp table | 4.71 us || closing tables | 6.12 us || freeing items | 17.17 us || cleaning up | 1.00 us |+----------------------+-----------+19 rows in set (0.01 sec)

SELECT * FROM performance_schema.events_statements_history_longWHERE event_id=<event_id>G*********************** 1. row ***********************THREAD_ID: 3062EVENT_ID: 1566END_EVENT_ID: 1585EVENT_NAME: statement/sql/selectSOURCE: init_net_server_extension.cc:80TIMER_START: 588883869566277000TIMER_END: 588883870317683000TIMER_WAIT: 751406000LOCK_TIME: 132000000SQL_TEXT: SELECT region,  count(*) as c FROM Country GROUP BY regionDIGEST: d3a04b346fe48da4f1f5c2e06628a245DIGEST_TEXT: SELECT `region` , COUNT ( * ) AS `c` FROM `Country`  GROUP BY `region`CURRENT_SCHEMA: worldOBJECT_TYPE: NULLOBJECT_SCHEMA: NULLOBJECT_NAME: NULLOBJECT_INSTANCE_BEGIN: NULLMYSQL_ERRNO: 0RETURNED_SQLSTATE: NULLMESSAGE_TEXT: NULLERRORS: 0WARNINGS: 0....ROWS_AFFECTED: 0ROWS_SENT: 25ROWS_EXAMINED: 289CREATED_TMP_DISK_TABLES: 0CREATED_TMP_TABLES: 1SELECT_FULL_JOIN: 0SELECT_FULL_RANGE_JOIN: 0SELECT_RANGE: 0SELECT_RANGE_CHECK: 0SELECT_SCAN: 1SORT_MERGE_PASSES: 0SORT_RANGE: 0SORT_ROWS: 25SORT_SCAN: 1NO_INDEX_USED: 1NO_GOOD_INDEX_USED: 0NESTING_EVENT_ID: NULLNESTING_EVENT_TYPE: NULLNESTING_EVENT_LEVEL: 0For non-aggregate queries rows sentvs. rows examined helps indicate indexeffectiveness.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |JSON• Optimizer has native support for JSON with indexes on generatedcolumns used for matching JSON path expressions167CREATE TABLE CountryJson (Code char(3) not null primary key, doc JSON NOT NULL);INSERT INTO CountryJSON SELECT code,JSON_OBJECT('Name', Name,'Continent', Continent,..'HeadOfState',HeadOfState,'Capital', Capital,'Code2', Code2) FROM Country;

EXPLAIN FORMAT=JSON SELECT * FROM CountryJSON where doc->>"$.Name" = ‘Canada';{"query_block": {"select_id": 1,"cost_info": {"query_cost": "48.80"},"table": {"table_name": "CountryJSON","access_type": "ALL","rows_examined_per_scan": 239,"rows_produced_per_join": 239,"filtered": "100.00","cost_info": {"read_cost": "1.00","eval_cost": "47.80","prefix_cost": "48.80","data_read_per_join": "3K"},..

ALTER TABLE CountryJSON ADD Name char(52) AS (doc->>"$.Name"),ADD INDEX n (Name);EXPLAIN FORMAT=JSONSELECT * FROM CountryJSON where doc->>"$.Name" = ‘Canada';{"query_block": {"select_id": 1,"cost_info": {"query_cost": "1.20"},"table": {"table_name": "CountryJSON","access_type": "ref",.."key": "n",.."key_length": "53","ref": ["const"],..Key from virtual columnMatches expression fromindexed virtual column

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |JSON Comparator• JSON types compare to MySQL types170SELECT CountryJSON.* FROM CountryJSON  INNER JOIN Country ON CountryJSON.doc->>"$.Name" = Country.Name WHERECountry.Name=‘Canada';********************** 1. row **********************Code: CANdoc: {"GNP": 598862, "Name": "Canada", "Code2": "CA", "GNPOld": 625626, "Region":"North America", "Capital": 1822, "Continent": "North America", "IndepYear": 1867,"LocalName": "Canada", "Population": 31147000, "HeadOfState": "Elisabeth II","SurfaceArea": 9970610, "GovernmentForm": "Constitutional Monarchy, Federation","LifeExpectancy": 79.4000015258789}Name: Canada

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Character Sets• The default character set in MySQL 8.0 is utf8mb4• Utf8mb4 is variable length (1-4 bytes)• InnoDB will always store as variable size for both CHAR andVARCHAR• Some buffers inside MySQL may require the fixed length (4 bytes)172

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Character Sets (cont.)• CHAR(n) or VARCHAR(n) refers to n characters - x4 for maximum length• EXPLAIN will always show the maximum length• Mysqldump will preserve character set173 ALTER TABLE City DROP FOREIGN KEY city_ibfk_1;ALTER TABLE CountryLanguage DROP FOREIGN KEYcountryLanguage_ibfk_1;ALTER TABLE Country CONVERT TO CHARACTER SETutf8mb4;

{"query_block": {"select_id": 1,"cost_info": {"query_cost": "0.35"},"table": {"table_name": "Country","access_type": "ref","possible_keys": ["n"],"key": "n","used_key_parts": ["Name"],"key_length": "52",.."rows_examined_per_scan": 1,"rows_produced_per_join": 1,"filtered": "100.00","cost_info": {"read_cost": "0.25","eval_cost": "0.10","prefix_cost": "0.35","data_read_per_join": "264"},..{"query_block": {"select_id": 1,"cost_info": {"query_cost": "0.35"},"table": {"table_name": "Country","access_type": "ref","possible_keys": ["n"],"key": "n","used_key_parts": ["Name"],"key_length": "208",.."rows_examined_per_scan": 1,"rows_produced_per_join": 1,"filtered": "100.00","cost_info": {"read_cost": "0.25","eval_cost": "0.10","prefix_cost": "0.35","data_read_per_join": "968"},..Key length as latin1 Key length as utf8

Movatterモバイル変換

Change Language

MySQL 8.0 Optimizer Guide

Embed presentation

Recommended

More Related Content

What's hot

Similar to MySQL 8.0 Optimizer Guide

More from Morgan Tocker

Recently uploaded

MySQL 8.0 Optimizer Guide