MySQL 8 For Developers

MySQL8有很多新的功能,但是它们到底能为您的应用程序提供什么帮助呢?本课程将从实际的角度研究MySQL8的特性,并讨论MySQL8克服了以前的MySQL8版本的哪些局限性,以及您可以对以前无法完成的MySQL8做些什么。

展开查看详情

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