MySQL Optimizer: What's New in 8.0

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Manyi Lu
Director
MySQL Optimizer Team, Oracle
April 2017
MySQL Optimizer: What’s New in 8.0

Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
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.
2

Program Agenda
3
 New syntax: SKIP LOCKED, NOWAIT
 Common table expressions
 Invisible index
 Descending index
 Cost model
 Better IPv6 and UUID support
 JSON aggregation
 Hints
 UTF8 support
 Roadmap

Program Agenda
4
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

New Syntax: SELECT ... FOR UPDATE SKIP LOCKED
Common problem:
• Hot row contention, multiple worker threads accesing the same rows
Solution 1:
• Only read rows that are not already locked
• InnoDB skips a locked row, and the next one goes to the result set
• Example:
– Booking system: skip orders that are pending
START TRANSACTION;
SELECT * FROM seats WHERE seat_no BETWEEN 2 AND 3 AND booked = 'NO'
FOR UPDATE SKIP LOCKED;
5

New Syntax: SELECT… FOR UPDATE NOWAIT
Common problem:
• Hot row contention, multiple work threads accesing the same rows
Solution 2:
• If any of the rows are already locked, the statement should fail immediately
• Without NOWAIT, have to wait for innodb_lock_wait_timeout (default: 50 sec) while
trying to acquire lock
Usage:
START TRANSACTION;
SELECT * FROM seats WHERE seat_no BETWEEN 2 AND 3 AND booked = 'NO'
FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock
6

Program Agenda
7
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

Common Table Expression
• A derived table is a subquery in the FROM clause
SELECT … FROM (subquery) AS derived, t1 ...
• Common Table Expression (CTE) is just like a derived table, but its
declaration is put before the query block instead of in FROM clause
WITH derived AS (subquery)
SELECT … FROM derived, t1 ...
• A CTE may precede SELECT/UPDATE/DELETE including sub-queries
WITH derived AS (subquery)
DELETE FROM t1 WHERE t1.a IN (SELECT b FROM derived);
8
Alternative to derived table

Common Table Expression versus Derived Table
Better readability
Can be referenced multiple times
Can refer to other CTEs
Improved performance
9

Better readability
• Derived table:
SELECT …
FROM t1 LEFT JOIN ((SELECT … FROM …) AS dt JOIN t2 ON …) ON …
• CTE:
WITH dt AS (SELECT ... FROM ...)
SELECT ...
FROM t1 LEFT JOIN (dt JOIN t2 ON ...) ON ...
10

Can Be Referenced multiple times
• Derived table can not be referenced twice:
SELECT ...
FROM (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d1
JOIN (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d2 ON d1.b = d2.a;
• CTE can:
WITH d AS (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b)
SELECT ... FROM d AS d1 JOIN d AS d2 ON d1.b = d2.a;
11

Can refer to other CTEs
• Derived tables can not refer to other derived tables:
SELECT …
FROM (SELECT … FROM …) AS d1, (SELECT … FROM d1 …) AS d2 …
ERROR: 1146 (42S02): Table ‘db.d1’ doesn’t exist
• CTEs can refer other CTEs:
WITH d1 AS (SELECT … FROM …),
d2 AS (SELECT … FROM d1 …)
SELECT
FROM d1, d2 …
12

Recursive CTE
• A recursive CTE refers to itself in a subquery
• The “seed” SELECT is executed once to create the initial data subset, the recursive
SELECT is repeatedly executed to return subsets of data.
• Recursion stops when an interaction does not generate any new rows
• Set MAX_EXECUTION_TIME (default 0) !
• Useful to dig in hierarchies (parent/child, part/subpart)
• Similar to Oracle's CONNECT BY
13
WITH RECURSIVE cte AS
( SELECT ... FROM table_name /* "seed" SELECT */
UNION ALL
SELECT ... FROM cte, table_name) /* "recursive" SELECT */
SELECT ... FROM cte;/

Recursive CTE – Simple Example
14
Print 1 to 10 :
WITH RECURSIVE qn AS
( SELECT 1 AS a
UNION ALL
SELECT 1+a FROM qn WHERE a<10
)
SELECT * FROM qn;
a
1
2
3
4
5
6
7
8
9
10

Recursive CTE – Example Fibonacci Numbers
15
F1 = 1, F2 = 1
Fn = Fn-1+ Fn-2
WITH RECURSIVE qn AS
( SELECT 1 AS n, 1 AS un, 1 AS unp1
UNION ALL
SELECT 1+n, unp1, un+unp1
FROM qn WHERE n<10)
SELECT * FROM qn;
n un unp1
1 1 1
2 1 2
3 2 3
4 3 5
5 5 8
6 8 13
7 13 21
8 21 34
9 34 55
10 55 89

Hierarchy Traversal
16
Employee database
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT,
FOREIGN KEY (manager_id)
REFERENCES employees(id) );
INSERT INTO employees VALUES
(333, "Yasmina", NULL), # CEO
(198, "John", 333), # John reports to 333
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

Hierarchy Traversal
17
List reporting chain
WITH RECURSIVE
emp_ext (id, name, path) AS (
SELECT id, name, CAST(id AS CHAR(200))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT s.id, s.name,
CONCAT(m.path, ",", s.id)
FROM emp_ext m JOIN employees s
ON m.id=s.manager_id )
SELECT * FROM emp_ext ORDER BY path;
id name path
333 Yasmina 333
198 John 333,198
692 Tarek 333,692
29 Pedro 333,198,29
123 Adil 333,692,123
4610 Sarah 333,198,29,4610
72 Pierre 333,198,29,72

Program Agenda
18
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

Invisible Index
• Maintained by the SE, ignored by the Optimizer
• Primary key cannot be INVISIBLE
• Use case: Check for performance drop BEFORE dropping an index
ALTER TABLE t1 ALTER INDEX idx INVISIBLE;
mysql> SHOW INDEXES FROM t1;
+---------+------------------+---------------------------------------+
| Table | Key_name | Column_name | Visible |
+---------+------------------+----------------------+----------------+
| t1 | idx | a | NO |
+---------+------------------ +----------------------+---------------+
19

Program Agenda
20
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

Descending Index
• In 5.7 and earlier: Index in ascending order is created, server scans it backwards
• In 8.0: Index in descending order is created, server scans it forwards
• Works on btree index only
Benefits:
• Use indexes instead of filesort for ORDER BY clause with ASC/DESC sort key
• Forward index scan is slightly faster than backward index scan
CREATE TABLE t1 (
a INT,
b INT,
INDEX a_b (a DESC, b ASC)
);
21

Program Agenda
22
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

Motivation for Improving the MySQL Cost Model
• Produce more correct cost estimates
– Better decisions by the optimizer should improve performance
• Adapt to new hardware architectures
– SSD, larger memories, caches
• More maintainable cost model implementation
– Avoid hard coded “cost constants”
– Refactoring of existing cost model code
• Configurable and tunable
• Make more of the optimizer cost-based
Faster
queries

• Storage engines:
– Estimate for how much of data and
indexes are in a memory buffer
– Estimate for hit rate for memory buffer
• Optimizer cost model:
– Take into account whether data is
already in memory or need to be read
from disk
Memory Buffer Aware Cost Estimates
Server
Storage
engine
Disk data
Query
executor
Database
buffer

Table Scan or Index Range Scan?
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
SELECT SUM(o_totalprice)
FROM orders
WHERE o_orderdate BETWEEN '1994-01-01' AND '1994-12-31';

Table Scan or Index Range Scan?
5.7: Data in memory
EXPLAIN SELECT SUM(o_totalprice) FROM orders
8.0 Data in memory
EXPLAIN SELECT SUM(o_totalprice) FROM orders
id
select
type
table type possible keys key
key
len
rows extra
1 SIMPLE orders ALL i_o_orderdate NULL NULL 15000000 Using where
Id
select
type
key
len
rows Extra
1 SIMPLE orders range i_o_orderdate i_o_orderdate 4 4489990
Using index
condition

Program Agenda
27
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

Improved Support for UUID
• Five “-” separated hexadecimal numbers
• MySQL uses version 1, the first three numbers are generated from the
low, middle, and high parts of a timestamp.
• 36 characters, inefficient for storage
Convert to BINARY(16) datatype, only 16 bytes
mysql> select uuid();
+---------------------------------------------------------+
| uuid() |
+---------------------------------------------------------+
| aab5d5fd-70c1-11e5-a4fb-b026b977eb28 |
+---------------------------------------------------------+
28

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 29
Improved Support for UUID
CREATE TABLE t (id binary(16)
PRIMARY KEY);
INSERT INTO t
VALUES(UUID_TO_BIN(UUID()));
# few inserts later
SELECT BIN_TO_UUID(id) FROM t;
BIN_TO_UUID(id);
586bcc2d-9a96-11e6-852c-4439c456d444
5942e49a-9a96-11e6-852c-4439c456d444
841ae775-9a96-11e6-852c-4439c456d444
84393c8e-9a96-11e6-852c-4439c456d444
Functions to convert UUID to and
from binary:
– UUID_TO_BIN()
– UUID_TO_BIN (arg1, arg2)
– BIN_TO_UUID()
– BIN_TO_UUID (arg1, arg2)
– IS_UUID()

UUID_TO_BIN Optimization
25 26 27 28 29
Insert Performance Optimized
Original
• Binary format is now smaller
• Shuffling low part with the high part improves index performance
11e678fe53303f87a4778c89a52c4f3b
53303f87-78fe-11e6-a477-8c89a52c4f3bFrom BINARY(36)
To BINARY(16)

IPv6
• New! Bit-wise operations on binary data types
– Designed with IPv6 in mind:
– INET6_ATON(address) & INET6_ATON(network)
– No longer truncation beyond 64 bits
Feature Request
from Developers

IPv4 vs IPv6
• IPv4 address commonly stored as INT
• IPv6 address 128 bit, commonly stored as BINARY(16)
• The secondary query would give wrong results in 5.7, because & operator converts
both operands from VARBINARY to BIGINT
SELECT inet_ntoa(network) AS network, inet_ntoa(netmask) AS netmask
FROM network_table
WHERE (inet_aton('192.168.0.30') & netmask) = network;
SELECT inet6_ntoa(network) AS network, inet6_ntoa(netmask) AS netmask
FROM network_table
WHERE (inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334') & netmask) = network;
32
Getting all the networks that contain the given IP address

Program Agenda
33
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

JSON Aggregation
34
CREATE TABLE t1(id INT, grp INT, jsoncol
JSON);
INSERT INTO t1 VALUES(1, 1,
'{"key1":"value1","key2":"value2"}');
'{"keyA":"valueA","keyB":"valueB"}');
'{"keyX":"valueX","keyY":"valueY"}');
SELECT JSON_ARRAYAGG(jsoncol) AS
json FROM t1;
[{"key1":"value1","key2":"value2"},
{"keyA":"valueA","keyB":"valueB"},
{"keyX":"valueX","keyY":"valueY"}]
Combine JSON documents in multiple rows into a JSON array

JSON Aggregation
35
CREATE TABLE t1(id INT, grp INT,
jsoncol JSON);
'{"key1":"value1","key2":"value2"}');
'{"keyA":"valueA","keyB":"valueB"}');
'{"keyX":"valueX","keyY":"valueY"}');
SELECT JSON_OBJECTAGG(id, jsoncol) AS json
FROM t1 GROUP BY grp;
1 | {"1":{"key1":"value1","key2":"value2"},
"2":{"keyA":"valueA","keyB":"valueB"}}
2 | {"3":{"keyX":"valueX","keyY":"valueY"}}
Combine JSON documents in multiple rows into a JSON object

Program Agenda
36
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

Hint: Join Order
• Hints to control table order for join execution
• 5.7: STRAIGHT_JOIN to force the listed order in FROM clause
• 8.0:
– JOIN_FIXED_ORDER /* replacement for STRAIGHT_JOIN*/
– JOIN_ORDER /* use specified order */
– JOIN_PREFIX /* use specified order for first tables */
– JOIN_SUFFIX /* use specified order for last tables */
– No need to reorganize the FROM clause to add join order hints like you will for
STRAIGHT_JOIN
37
t

Join Order Hints
Original query
EXPLAIN SELECT *
FROM customer JOIN orders ON c_custkey = o_custkey
WHERE c_acctbal < -1000 AND o_orderdate < '1993-01-01';
id
select
type
key
len
ref rows filtered extra
1 SIMPLE orders ALL
i_o_orderdate,
i_o_custkey
NULL NULL NULL 15000000 31.19
Using
where
1 SIMPLE customer
eq_
ref
PRIMARY PRIMARY 4
dbt3.orders.
o_custkey
1 33.33
Using
where

Join Order Hints
Change join order with hint
EXPLAIN SELECT /*+ JOIN_ORDER(customer, orders) */ *
FROM customer JOIN orders ON c_custkey = o_custkey
WHERE c_acctbal < -1000 AND o_orderdate < '1993-01-01';
id
select
type
key
len
ref rows filtered extra
1 SIMPLE customer ALL PRIMARY NULL NULL NULL 1500000 31.19
Using
where
1 SIMPLE orders ref
i_o_orderdate,
i_o_custkey
i_o_custkey 5
dbt3.
customer.
c_custkey
15 33.33
Using
where
Alternatives with same effect for this query:
JOIN_PREFIX(customer) JOIN_SUFFIX(orders) JOIN_FIXED_ORDER()

Hint: Index Merge
• Index merge: merge rows from multiple range scans on a single table
• Algorithms: union, intersection, sort union
• Users can specify which indexes to use for index merge
– /*+ INDEX_MERGE() */
– /*+ NO_INDEX_MERGE() */
40
10INDEX(a)
10INDEX(b)
a=10 AND b=10Result:
Intersection
SELECT * FROM t1 WHERE a=10 AND b=10

Index Merge Hint - Example
SELECT count(*) FROM users
WHERE user_type=2 AND status=0 AND parent_id=0;
id
select
type
table type
possible
keys
key
key
len
ref rows Extra
1 SIMPLE users
index_
merge
parent_id,
status,
user_type
user_type,
status,
parent_id
1,1,4 NULL 2511
Using intersect (user_type,
status, parent_id);
Using where; Using index
mysql> SELECT count(*) FROM users WHERE user_type=2 AND status=0 AND parent_id=0;
...
1 row in set (1.37 sec)
mysql> SELECT /*+ INDEX_MERGE(users user_type, status) */ count(*)
-> FROM users WHERE user_type=2 AND status=0 AND parent_id=0;
...
1 row in set (0.18 sec)
Low selectivity

Program Agenda
42
 Invisible index
 Cost model
 Hints
 UTF8 support
 Roadmap

Improved UTF-8 Support in MySQL 8.0
• Support for the latest Unicode 9.0
• utf8mb4 made default character set!
–utf8mb4_0900_ai_ci default collation
• Accent and case sensitive collations
–Including 20+ language specific collations
–Now also Japanese
• Significantly improved performance

What is in MySQL 5.7 and Earlier Versions?
• Default charset is “latin1” and default collation is “latin1_swedish_ci”
• utf8 = utf8mb3: support BMP only
• utf8mb4 character set:
–Only accent and case insensitive collations
–Default collation is utf8mb4_general_ci, compares all characters beyond
BMP e.g.emojis to be equal
–20+ language specific collations
–Recommend to use: utf8mb4_unicode_520_ci

New Default Character Set
• No changes to existing tables
• Only has effect on new tables/schemas where character set is not
explicitly defined.
• Separating character set/collation change from server upgrade
– Upgrade first, change charset/collation afterwards
• Recommend users to not mixing collations
– Error “Illegal mix of collations”
– Slower query because index can no longer be used

• GIS: Geography support
• Ellipsoidal ST_Distance()
• Improved Scan Query Performance
• GROUPING()
• Hint: Merge/materialize derived table
• JSON_PRETTY()
• Improved performance of sorting and
grouping of JSON values
• Parser Refactoring
46
All these features and more…

What is on Our Roadmap?
• JSON TABLE function
• Improved statistics: histogram
• Improve prepared statement/cache query plan
• Parallel query

Window functions: what are they?
● A window function performs a calculation across a set of rows that are related to the
current row, similar to an aggregate function.
● But unlike aggregate functions, a window function does not cause rows to become
grouped into a single output row.
● So, similar to normal function, can access values of other rows “in the vicinity” of the
current row
Aggreation function Window function

49
Window function example, no frame
PARTITION == disjoint
set of rows in result set
Name departme
nt_id
salary department_
total
Newt NULL 75000 75000
Dag 10 NULL 375000
Ed 10 100000 370000
Fred 10 60000 370000
Jon 10 60000 370000
Michael 10 70000 370000
Newt 10 80000 370000
Lebedev 20 65000 130000
Pete 20 65000 130000
Jeff 30 300000 370000
Will 30 70000 370000
Sum up totol salary for each department:
SELECT name, department_id, salary,
SUM(salary) OVER (PARTITION BY
department_id) AS
department_total
FROM employee
ORDER BY department_id, name;
The OVER keyword
signals a window function

name department
_id
salary total
Newt NULL 75000 75000
Dag 10 NULL NULL
Ed 10 100000 100000
Fred 10 60000 160000
Jon 10 60000 220000
Michael 10 70000 190000
Newt 10 80000 210000
Lebedev 20 65000 650000
Pete 20 65000 130000
Jeff 30 300000 300000
Will 30 70000 370000
50
Window function example, with frame
ORDER BY
name
within each
Partition
moving window frame:
SUM (salary).....
ROWS 2 PRECEDING
a frame is a subset of a
partition
SELECT name, department_id, salary,
SUM (salary) OVER (PARTITION BY
department_id
ORDER BY name
ROWS 2 PRECEDING) total
FROM employee
ORDER BY department_id, name;

Improved Performance of Scans
• Motivation:
– InnoDB already uses an internal buffer to fetch records in batches
– Optimizer knows the operation and the estimated number of records to read
– Why cannot optimizer tell InnoDB how much to read??
• 8.0 extends the handler API: provides a buffer for each table or index
where optimizer expects more than one row
– So far only InnoDB uses this API
• Queries with improved performance:
52
SELECT * FROM t;
SELECT * FROM t WHERE pk BETWEEN 1000 and 10 000;
SELECT * FROM t1, t2 WHERE t1.pk = t2.pk; /* buffer for the outer table, no need for
inner table which is accessed using eq_ref)

Bitwise Operations on Binary Data Types
• Prior to 8.0, bitwise operations only worked on integer, truncation beyond 64 bits
• 8.0: Extends bit-wise operations to work with BINARY/BLOB
Bit operators and functions:
& | ^ ~ << >>
BIT_COUNT
53
Aggregate bit functions:
BIT_AND
BIT_XOR
BIT_OR

• Time to do a table scan of 10
million records:
• Adjust cost model to support
different storage technologies
• Provide configurable cost constants
for different storage technologies
New Storage Technologies
Memory 5 s
SSD 20 - 146 s
Hard disk 32 - 1465 s
Provide a program that could
measure performance and
suggest good cost constant
configuration for a running
MySQL server?

MySQL Optimizer: What's New in 8.0

Recommended

More Related Content

What's hot (20)

Similar to MySQL Optimizer: What's New in 8.0 (20)

Recently uploaded (20)

MySQL Optimizer: What's New in 8.0