展开查看详情
1 . MySQL 8 for Developers
Lots of things to be looking forward to
Peter Zaitsev, CEO, Percona
December 12, 2018
Percona Technical Webinars
© 2018 Percona.
1
2 .MySQL 8 – Adoption
Where do we stand ?
© 2018 Percona.
2
3 .MySQL 8 News
Good News and Bad
News
© 2018 Percona.
3
4 .MySQL 8 Adoption by Wordpress Users
https://wordpress.org/about/stats/
© 2018 Percona.
4
5 .MySQL 8 Performance Regressions
Transactions/sec TPCC 4 CPU Cores 128 Clients
1400
MySQL 8 may be 1200
slower than MySQL 1000
5.7 on low powered
800
systems and at low
600
400
concurrency 200
0
5.7.22 8.0.11
© 2018 Percona.
5
6 .Having Said that
Most Serious MySQL users are
actively planning MySQL 8
migration
© 2018 Percona.
6
7 .MySQL 8 Development Model
Excited to see new features are being released in
Maintenance releases
More “Cloud Friendly” Development Model
Much higher requirements for Quality Assurance
© 2018 Percona.
7
8 .Source Notes
Examples liberally borrowed
from Oracle team
presentations and Blog Posts
© 2018 Percona.
8
9 .MySQL 8 for Devs
© 2018 Percona.
9
10 .Much Better and Faster UTF8
• utf8mb4 as Default Charset
© 2018 Percona.
10
11 .Fast Information Schema
Scaling from 5.000 to
1.000.000 tables
© 2018 Percona.
11
12 .Instant Add Column (8.0.13)
Add
•ALTER TABLE t1 ADD
Column
COLUMN d INT
without
DEFAULT 1000,
rebuilding
ALGORITHM=INSTANT;
table
© 2018 Percona.
12
13 .Functions in DEFAULT (8.0.13)
CREATE TABLE t2 (a BINARY(16)
DEFAULT uuid_to_bin(uuid()));
© 2018 Percona.
13
14 .Functional Indexes (8.0.13)
CREATE INDEX idx1 ON t1
((col1+col2));
© 2018 Percona.
14
15 .Innodb NO WAIT and SKIP LOCKED
© 2018 Percona.
15
16 .Descending Indexes
Descending flag in index definition is
no more ignored
Allows efficient handling of ORDER
BY A ASC, B DESC queries
© 2018 Percona.
16
17 .Persistent Auto Increment
Another feature 10 years overdue
Do not reset AUTO INCREMENT to the
max value in the table on restart
© 2018 Percona.
17
18 .Partial In-Place Update for JSON
Can update field in JSON object without full re-write
Great for counters, statuses, timestamps etc
Only update and removal of element is supported
Full Support Added in Maintenance Releases
© 2018 Percona.
18
19 .Better Optimizer
Many Optimizer improvements in
MySQL 8
Your queries will run faster (or not)
© 2018 Percona.
19
20 .Optimizer Histograms
• Detailed Statistics on Columns, not just Indexes
© 2018 Percona.
20
21 .Improved Optimizer Cost Model
Keep in account how
much of data is cached vs
on disk
© 2018 Percona.
21
22 .Optimizer Hints
Set optimizer_switch settings for given query/tables
© 2018 Percona.
22
23 .Invisible Indexes
• Test impact of dropping indexes before actually dropping them
• Can use use_invisible_indexes to use invisible indexes in a
session
© 2018 Percona.
23
24 .Multibyte Safe Regular Expressions
Using Regexp with utf8 ?
© 2018 Percona.
24
25 .Remote Management Features
RESTART (Command)
SET PERSIST innodb_buffer_pool_size =
1024 * 1024 * 1024;
© 2018 Percona.
25
26 .Aggregation of Relational into JSON
• JSON_ARRAYAGG() and JSON_OBJECTAGG()
© 2018 Percona.
26
27 .JSON to Table Conversion
© 2018 Percona.
27
28 .Better JSON Document Data Extraction
© 2018 Percona.
28
29 .Common Table Expression
• Recursive and Non-Recursive
© 2018 Percona.
29
热门城市
