MySQL Optimizer:  Design Principles

•  Best out of the box performance

•  Easy to use, minimum tuning needed

•  When you need to understand: explain and trace

•  Flexibility through optimizer hints, switches, and plugins

•  Fast evolving

MySQL 5.7 Optimizer Improvements

•  Generated columns and functional indexes 

•  New JSON datatype and functions 

•  Improved cost model 

•  New hint syntax and improved hint support 

•  Query rewrite plugin 

•  UNION ALL queries do not always use temporary tables 

•  Improved optimizations for queries with IN expressions 

•  Explain on a running query 

Indexing Generated column: STORED vs VIRTUAL (InnoDB)

•  STORED                                  •  VIRTUAL

–Primary & secondary index                 –Secondary index only 

–B-TREE, Full text search, R-TREE          –B-TREE only  

–Duplication of data in base table         –Less storage  

and index                                  –Independent of SE 

–Online operation                          –Requires SE support 

–Online operation 

JSON Support

•  Seemless integration of relational and schema-less data 

•  Leverage existing database infrastructure for new applications 

•  Provide a native JSON datatype 

•  Provide a set of built-in JSON functions 

Inlining JSON PATH Expressions in SQL

[[database.]table.]column->”$<path spec>” (GA version)

SELECT * FROM employees WHERE data->”$.name” = “Jane";

Is a short hand for

SELECT * FROM employees WHERE JSON_EXTRACT(data, “$.name” ) = “Jane”;

Motivation for Changing the Cost Model

•  Adopt to new hardware architectures 

– SSD, larger memories, caches

•  Allows storage engines to provide accurate and dynamic cost estimate 

– Whether the data is in RAM, SSD, HDD?

•  More maintainable cost model implementation 

– Avoid hard coded constants

– Refactoring of existing cost model code

•  Tunable/configurable 

•  Replace heuristics with cost based decisions 

Cost Model: Main Focus in 5.7

Address the following pain points in current cost model:

•  Inaccurate record estimation for JOIN ØSolution: condition filtering

•  Hard-coded cost constants ØSolution: adjustable cost parameters

•  Imprecise cardinality/records per key estimates from SE ØSolution: Integer value replaced by floating point

•  Hard to obtain detailed cost numbers ØSolution: Added to JSON explain and MySQL WorkBench MySQL 5.7: Improved HINTs •  Introduced new hint syntax /*+ ...*/  – Flexibility over optimizer switch, effect individual statement only – Hints within one statement take preceduence over optimizer switch – Hints apply at different scope levels: global, query block, table, index •  Extended hint support  – BKA, BNL, MRR, ICP, SEMIJOIN, SUBQUERY,MAX_EXECUTION_TIME++ – Disabling prevents optimizer to use it – Enabling means optimizer is free to use it, but is not forced to use it •  Will gradually replace the old hint syntax in upcoming releases  MySQL 5.7: Query Rewrite Plugin •  New pre and post parse query rewrite APIs   – Users can write their own plug-ins •  Provides a post-parse query plugin  – Rewrite problematic queries without the need to make application changes •  Add hints •  Modify join order •  Rewrite rules are defined in a table •  Improve problematic queries from ORMs, third party apps, etc  MySQL 5.7 Query Rewrite Plugin: Performance Impact What is the Cost of Rewriting queries?  •  Designed for rewriting problematic queries only!  • ~ Zero cost for queries not to be rewritten  – Statement digest computed for performance schema anyway •  Cost of queries to be rewritten is insignificant compared to  performance gain  – Cost of generating query + reparsing max ~5% performance overhead – Performance gain potentially x times MySQL 5.7: Avoid Creating Temporary Table for UNION ALL SELECT * FROM table_a UNION ALL SELECT * FROM table_b; •  5.6: Always materialize results of UNION ALL in temporary tables  •  5.7: Do not materialize in temporary tables unless used for sorting,  rows are sent directly to client  •  5.7: Client will receive the first row faster, no need to wait until the  last query block is finished  •  5.7: Less memory and disk consumption  MySQL 5.7: Optimizations for IN Expressions CREATE TABLE t1 (a INT, b INT, c INT, KEY x(a, b)); SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); •  5.6: Certain queries with IN predicates can’t use index scans or range scans even though all the columns in the query are indexed. •  5.6:  Range optimizer ignores lists of rows •  5.6: Needs to rewrite to de-normalized form SELECT a, b FROM t1 WHERE ( a = 0 AND b = 0 ) OR ( a = 1 AND b = 1 ) •  5.7: IN queries with row value expressions executed using range scans. •  5.7: Explain output: Index/table scans change to range scans

MySQL 5.7 Explain on a Running Query

EXPLAIN [FORMAT=(JSON|TRADITIONAL)] FOR CONNECTION <id>;

•  Shows query plan on connection <id> 

•  Useful for diagnostic on long running queries 

•  Plan isn’t available when query plan is under creation

•  Applicable to SELECT/INSERT/DELETE/UPDATE