RivieraJUG - MySQL 8.0 : What's new for developers

Frédéric Descamps
Community Manager
Oracle MySQL
RivieraJUG - September 2022
MySQL 8.0
What's new for developers ?

View Slide

Who am I ?
about.me/lefred
Copyright @ 2022 Oracle and/or its affiliates.
2

View Slide

Frédéric Descamps
@lefred
MySQL Evangelist
Managing MySQL since 3.20
devops believer
living in Belgium
h ps://lefred.be
3

View Slide

New Volcano Iterator
MySQL Optimizer Refactoring
4

View Slide

Modular
Easy to Extend
Each iterator encapsulates one
operation
Same interface for all iterators
All operations can be connected
MySQL New Iterator Executor
5

View Slide

EXPLAIN FORMAT=TREE
6

View Slide

EXPLAIN ANALYZE
Instruments and executes the query
Estimated cost
Actual execution statistics
Time to return rst row
Time to return all rows
Number of rows returned
Number of loops
Uses the new tree output format also available in EXPLAIN
7

View Slide

EXPLAIN ANALYZE
8

View Slide

EXPLAIN ANALYZE
9

View Slide

EXPLAIN ANALYZE
10

View Slide

EXPLAIN ANALYZE
11

View Slide

EXPLAIN ANALYZE
12

View Slide

Hash Joins
bye bye Block Nested Loop
13

View Slide

Hash Join
Typically faster than nested loop for large result sets
In-memory if possible
Spill to disk if necessary
Used in all types of joins (inner, equi, ...)
Replaces BNL in query plans
In MySQL 8.0.20 and later, hash joins are used for outer joins (including antijoins and
semijoins) as well.
14

View Slide

Hash Join: performance
15

View Slide

MySQL 8.0
one giant leap for SQL
16

View Slide

"This is a landmark release as MySQL
eventually evolved beyond SQL-92 and the
purely relational dogma. Among a few
other standard SQL features, MySQL now
supports window functions (over) and
common table expressions (with). Without a
doubt, these are the two most important
post-SQL-92 features.=
MySQL 8.0: one giant leap for SQL
17

View Slide

A CTE is just like a derived table, but its
declaration is put before the query block
instead of in the FROM clause:
be er readability
can be referenced multiple times
can refer to other CTEs
improved performance
Non-recursive
WITH cte AS (subquery) SELECT ...
FROM cte, t1 ...
Recursive
WITH RECURSIVE cte AS
( SELECT ... FROM table_name
UNION [DISTRINCT|ALL]
SELECT ... FROM cte, table_name)
SELECT ... FROM cte;
SQL: Common Table Expression (WITH clause)
18

View Slide

Credits: @MarkusWinand - @ModernSQL
SQL: RECURSION / CTEs (WITH clause)
19

View Slide

SQL: LATERAL DERIVED TABLES
Can refer to other tables in the same FROM clause
Sometimes referred to as the SQL "for each" equivalent
SELECT ... FROM t1, LATERAL
(
SELECT ... FROM ... WHERE ... = t1.col
) AS derived,
t2 ...
20

View Slide

SQL: LATERAL DERIVED TABLES
21

View Slide

SQL: Analytical / Window Functions (OVER clause)
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.
Window functions can access values of other rows "in the vicinity" of the current row.
22

View Slide

SQL: Analytical / Window Functions
23

View Slide

SQL: Analytical / Window Functions
24

View Slide

SQL: JSON_TABLE
From JSON Document to SQL Table
25

View Slide

SQL: JSON_TABLE
From JSON Document to SQL Table
26

View Slide

This function is described in SQL 2016,
chapter 6.27 and is also implemented in:
Oracle
SQL Server
DB2
SQL: JSON_VALUE
27

View Slide

MySQL > TABLE t;
+----+--------+------+
| id | date | num |
+----+--------+------+
| 1 | 202201 | 3363 |
| 2 | 202202 | 5363 |
| 3 | 202203 | 4344 |
| 4 | 202204 | 1404 |
| 5 | 202205 | 2300 |
+----+--------+------+
5 rows in set (0.0079 sec)
MySQL > INSERT INTO t VALUES
ROW(0, 202206, 3100),
ROW(0, 202207, 2456);
MySQL > TABLE t LIMIT 2 OFFSET 5;
+----+--------+------+
| id | date | num |
+----+--------+------+
| 6 | 202206 | 3100 |
| 7 | 202207 | 2456 |
+----+--------+------+
SQL: TVC Support
Support explicit tables clauses and table value constructors according the SQL standard:
28

View Slide

SQL: FUNCTIONAL INDEXES
Index over an expression
CREATE TABLE t1 (col1 INT, col2 INT);
CREATE INDEX idx1 ON t1 ((col1 + col2), (col1 - col2), col1) ;
Document content, e.g. JSON array
CREATE TABLE lottery (data JSON);
CREATE INDEX ticket_idx ON lottery
((CAST(data->'$.lottery_tickets' AS UNSIGNED INT ARRAY))) ;
29

View Slide

SQL: INVISIBLE INDEXES
Indexes are "hidden" to the MySQL Optimizer
Not the same as "disabled indexes"
Contents are fully up to date and maintained by DML
Two use cases:
Soft Delete: What will happen if I delete this index?
Staged Rollout: I will create this index over night and make it visible when I am at
work tomorrow
30

View Slide

SQL: CHECK CONSTRAINT
Standard SQL Syntax
[ CONSTRAINT [symbol] ] CHECK ( condition) [ [ NOT ] ENFORCED ]
Example
CREATE TABLE t1 ( c1 INTEGER CONSTRAINT c1_chk CHECK (c1 > 0) ,
c2 INTEGER CONSTRAINT c2_chk CHECK (c2 > 0) ,
CONSTRAINT c1_c2_chk CHECK (c1 + c2 < 9999) );
31

View Slide

SQL: Expressions as DEFAULT Values
No longer limited to literal values
CREATE TABLE t2 (a INT, b INT, c INT DEFAULT (a+b) );
CREATE TABLE t3 (a INT, b INT, c POINT DEFAULT (POINT(0,0)) );
CREATE TABLE t4 (a INT, b INT, c JSON DEFAULT (8[]9) );
Useful for types without literal values
GEOMETRY, POINT, LINESTRING, POLYGON, ...
32

View Slide

SELECT * FROM tickets
WHERE id IN (1,2,3,4)
AND order_id IS NULL
FOR UPDATE
NOWAIT;
FOR UPDATE
SKIP LOCKED;
SQL: NOWAIT & SKIP LOCKED
33

View Slide

FOR UPDATE
NOWAIT;
FOR UPDATE
SKIP LOCKED;
Error immediately if a row is
already locked
Non deterministically skip over
locked rows
SQL: NOWAIT & SKIP LOCKED
33

View Slide

MySQL 8.0 GIPK Mode
make the DBAs happy !
34

View Slide

InnoDB Primary Keys, Invisible column and GIPK
For InnoDB, a Primary Key is required and a good one is even be er !
35

View Slide

Some theory
InnoDB stores data in table spaces. The records are stored and sorted using the clustered
index (PK).
35

View Slide

Some theory
InnoDB stores data in table spaces. The records are stored and sorted using the clustered
index (PK).
All secondary indexes also contain the primary key as the right-most column in the index
(even if this is not exposed). That means when a secondary index is used to retrieve a
record, two indexes are used: rst the secondary one pointing to the primary key that will be
used to nally retrieve the record.
35

View Slide

InnoDB Primary Key (2)
So, the primary key impact how the values are inserted and the size of the secondary
indexes. A non sequential PK can lead to many random IOPS.
36

View Slide

Also, it's more and more common to use
application that generates complete
random primary keys...that means if the
Primary Key is not sequential, InnoDB will
have to heavily re-balance all the pages on
inserts.
So, the primary key impact how the values are inserted and the size of the secondary
indexes. A non sequential PK can lead to many random IOPS.
36

View Slide

If we compare the same load (inserts) when using an auto_increment integer as Primary
Key, we can see that only the latest pages are recently touched:
Generated with h ps://github.com/jeremycole/innodb_ruby from @jeremycole
37

View Slide

InnoDB Primary Key ? No Key !
Another common mistake when using InnoDB is to not de ne any Primary Key.
38

View Slide

When no primary key is de ned, the rst unique not null key is used. And if none is
available, InnoDB will create an hidden primary key (6 bytes).
38

View Slide

The problem with such key is that you don’t have any control on it and worse, this value is
global to all tables without primary keys and can be a contention problem if you perform
multiple simultaneous writes on such tables (dict_sys->mutex).
38

View Slide

The problem with such key is that you don’t have any control on it and worse, this value is
global to all tables without primary keys and can be a contention problem if you perform
multiple simultaneous writes on such tables (dict_sys->mutex).
And if you plan for High Availability, tables without Primary Key are not supported !
38

View Slide

InnoDB Primary Key ? No Key ! (2)
Luckily since MySQL 8.0.23 there is a solution: Invisible Column !
39

View Slide

Luckily since MySQL 8.0.23 there is a solution: Invisible Column !
You can now add an invisible auto_increment Primary Key to a table not having any
Primary Key !
39

View Slide

To identify those tables, run the following SQL statement:
SELECT
SELECT tables
tables.
.table_schema
table_schema ,
, tables
tables.
.table_name
table_name ,
, tables
tables.
.engine
engine
FROM
FROM information_schema
information_schema.
.tables
tables LEFT
LEFT JOIN
JOIN (
(
SELECT
SELECT table_schema
table_schema ,
, table_name
table_name
FROM
information_schema.
.statistics
statistics
GROUP
GROUP BY
BY table_schema
table_schema,
, table_name
table_name,
, index_name
index_name
HAVING
HAVING SUM
SUM(
(
CASE
CASE WHEN
WHEN non_unique
non_unique =
= 0
0
AND
AND nullable
nullable !=
!= 'YES'
'YES' then
then 1
1 else
else 0
0 end
end )
) =
= count
count(
(*
*)
) )
) puks
puks
ON
ON tables
tables.
.table_schema
table_schema =
= puks
puks.
.table_schema
table_schema
AND
AND tables
tables.
.table_name
table_name =
= puks
puks.
.table_name
table_name
WHERE
WHERE puks
puks.
.table_name
table_name IS
IS null
null
AND
AND tables
tables.
.table_type
table_type =
= 'BASE TABLE'
'BASE TABLE'
AND
AND Engine
Engine=
="InnoDB"
"InnoDB";
;
40

View Slide

To identify those tables, run the following SQL statement:
SELECT
SELECT tables
tables.
.table_schema
table_schema ,
, tables
tables.
.table_name
table_name ,
, tables
tables.
.engine
engine
FROM
information_schema.
.tables
tables LEFT
LEFT JOIN
JOIN (
(
SELECT
SELECT table_schema
table_schema ,
, table_name
table_name
FROM
information_schema.
.statistics
statistics
GROUP
GROUP BY
BY table_schema
table_schema,
, table_name
table_name,
, index_name
index_name
HAVING
HAVING SUM
SUM(
(
CASE
CASE WHEN
WHEN non_unique
non_unique =
= 0
0
AND
AND nullable
nullable !=
!= 'YES'
'YES' then
then 1
1 else
else 0
0 end
end )
) =
= count
count(
(*
*)
) )
) puks
puks
ON
ON tables
tables.
.table_schema
table_schema =
= puks
puks.
.table_schema
table_schema
AND
AND tables
tables.
.table_name
table_name =
= puks
puks.
.table_name
table_name
WHERE
WHERE puks
puks.
.table_name
table_name IS
IS null
null
AND
AND tables
tables.
.table_type
table_type =
= 'BASE TABLE'
'BASE TABLE'
AND
AND Engine
Engine=
="InnoDB"
"InnoDB";
;
+--------------+-----------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+-----------------+--------+
| slack | some_table | InnoDB |
| test | default_test | InnoDB |
| test | t1 | InnoDB |
| world | orders | InnoDB |
| world | sales | InnoDB |
| dbt3 | time_statistics | InnoDB |
+--------------+-----------------+--------+
40

View Slide

Another nice query to identify the tables using an hidden clustered index is to lookup for
GEN_CLUST_INDEX like this:
SELECT
SELECT i
i.
.TABLE_ID
TABLE_ID,
,
t
t.
.NAME
NAME
FROM
FROM INFORMATION_SCHEMA
INFORMATION_SCHEMA.
.INNODB_INDEXES i
INNODB_INDEXES i
JOIN
JOIN
INFORMATION_SCHEMA
INFORMATION_SCHEMA.
.INNODB_TABLES t
INNODB_TABLES t ON
ON (
(i
i.
.TABLE_ID
TABLE_ID =
= t
t.
.TABLE_ID
TABLE_ID)
)
WHERE
WHERE
i
i.
.NAME
NAME=
='GEN_CLUST_INDEX'
'GEN_CLUST_INDEX';
;
see h ps://elephantdolphin.blogspot.com/2021/08/ nding-your-hidden-innodb-primary.html
41

View Slide

Another nice query to identify the tables using an hidden clustered index is to lookup for
GEN_CLUST_INDEX like this:
SELECT
SELECT i
i.
.TABLE_ID
TABLE_ID,
,
t
t.
.NAME
NAME
FROM
FROM INFORMATION_SCHEMA
INFORMATION_SCHEMA.
.INNODB_INDEXES i
INNODB_INDEXES i
JOIN
JOIN
INFORMATION_SCHEMA
INFORMATION_SCHEMA.
.INNODB_TABLES t
INNODB_TABLES t ON
ON (
(i
i.
.TABLE_ID
TABLE_ID =
= t
t.
.TABLE_ID
TABLE_ID)
)
WHERE
WHERE
i
i.
.NAME
NAME=
='GEN_CLUST_INDEX'
'GEN_CLUST_INDEX';
;
see h ps://elephantdolphin.blogspot.com/2021/08/ nding-your-hidden-innodb-primary.html
+----------+----------------------+
| TABLE_ID | NAME |
+----------+----------------------+
| 1198 | slack/some_table |
| 1472 | test/default_test |
| 1492 | test/t1 |
| 2018 | world/orders |
| 2019 | world/sales |
| 2459 | dbt3/time_statistics |
+----------+----------------------+
41

View Slide

42

View Slide

Perfect for replication !
42

View Slide

43

View Slide

InnoDB GIPK mode
Since MySQL 8.0.30, MySQL supports generated invisible primary keys when running in
GIPK mode !
GIPK mode is controlled by the sql_generate_invisible_primary_key server system variable.
44

View Slide

InnoDB GIPK mode
Since MySQL 8.0.30, MySQL supports generated invisible primary keys when running in
GIPK mode !
GIPK mode is controlled by the sql_generate_invisible_primary_key server system variable.
When MySQL is running in GIPK mode, a primary key is added to a table by the server, the
column and key name is always my_row_id.
44

View Slide

InnoDB GIPK mode - example
MySQL > SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
| 1 |
+--------------------------------------+
MySQL > CREATE TABLE rivierajug (name varchar(20), beers int unsigned);
MySQL > INSERT INTO rivierajug VALUES ('Yannis', 0), ('lefred',1);
Query OK, 2 rows affected (0.0073 sec)
MySQL > SELECT * FROM rivierajug;
+--------+-------+
| name | beers |
+--------+-------+
| Yannis | 0 |
| lefred | 1 |
+--------+-------+
45

View Slide

InnoDB GIPK mode - example (2)
MySQL > SHOW CREATE TABLE rivierajug\G
*************************** 1. row ***************************
Table: rivierajug
Create Table: CREATE TABLE `rivierajug` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`name` varchar(20) DEFAULT NULL,
`beers` int unsigned DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
46

View Slide

*************************** 1. row ***************************
Table: rivierajug
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`beers` int unsigned DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
MySQL > SELECT *, my_row_id FROM rivierajug;
+--------+-------+-----------+
| name | beers | my_row_id |
+--------+-------+-----------+
| Yannis | 0 | 1 |
| lefred | 1 | 2 |
+--------+-------+-----------+
46

View Slide

It's also possible to hide it completely (for some legacy application that could rely on
information_schema and SHOW CREATE TABLE):
MySQL > SET show_gipk_in_create_table_and_information_schema = 0;
*************************** 1. row ***************************
Table: rivierajug
`beers` int unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
47

View Slide

. generally they are completely random
and cause clustered index re-banlancing
. they are included in each secondary
indexes (consuming disk and memory)
InnoDB Primary Key - What about UUID ?
There are 2 major problems with UUID's as Primary Key:
49

View Slide

Let's insert 2 new records:
MySQL > INSERT INTO rivierajug2 (name, beers) VALUES ("Benoît",1), ("Sarah",5);
51

View Slide

OUPS ! We have rebalanced the clustered index !
What does that mean again ??
52

View Slide

What does that mean again ??
Let me try to explain this with this high level and simpli ed example:
52

View Slide

Let's imagine one InnoDB Page can store 4
records (this is just a ction), and we have
inserted some records using a random
Primary Key:
53

View Slide

Let's imagine one InnoDB Page can store 4
records (this is just a ction), and we have
inserted some records using a random
Primary Key:
And now we insert a new record and the
Primary Key is AA:
All pages were modi ed to clustered index ! Imagine if this was a 4TB
table !!
53

View Slide

And just for info, each entry in the Primary Key Index could take up to 146 bytes(*)
:
MySQL > EXPLAIN SELECT * FROM rivierajug2 WHERE
uuid='36f1d158-1fa1-11ed-ba36-c8cb9e32df8e'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rivierajug2
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 146
ref: const
rows: 1
filtered: 100
Extra: NULL
(*) worse case when using characters using 4 bytes each (uft8mb4)
54

View Slide

Recommended solution
. use a smaller datatype: BINARY(16)
. store the UUID sequentially: UUID_TO_BIN(..., swap_flag)
The time-low and time-high parts (the rst and third groups of hexadecimal digits,
respectively) are swapped.
55

View Slide

Recommended solution - example
uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
+------------------------------------+--------+-------+
+------------------------------------+--------+-------+
| 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 |
| 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 |
+------------------------------------+--------+-------+
56

View Slide

uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
+------------------------------------+--------+-------+
+------------------------------------+--------+-------+
+------------------------------------+--------+-------+
MySQL > SELECT BIN_TO_UUID(uuid,1), name, beers FROM rivierajug3;
+--------------------------------------+--------+-------+
| BIN_TO_UUID(uuid,1) | name | beers |
+--------------------------------------+--------+-------+
| 633ecb6c-1f9f-11ed-ba36-c8cb9e32df8e | Yannis | 0 |
| 633ecd6f-1f9f-11ed-ba36-c8cb9e32df8e | lefred | 1 |
+--------------------------------------+--------+-------+
56

View Slide

Take a look at the size of each entry in the INDEX (and same amount added to each secondary index)
MySQL > EXPLAIN SELECT * FROM rivierajug3
WHERE uuid=UUID_TO_BIN("37c5752d-1fa5-11ed-ba36-c8cb9e32df8e",1)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rivierajug3
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 16
ref: const
rows: 1
filtered: 100
Extra: NULL
58

View Slide

MySQL UUID
MySQL generates UUID v1 as described in RFC4122.
UUID v1 : is a universally unique identi er that is generated using a timestamp and the
MAC address of the computer on which it was generated.
UUID v4 : is a universally unique identi er that is generated using random numbers.
59

View Slide

MySQL UUID
MySQL generates UUID v1 as described in RFC4122.
UUID v1 : is a universally unique identi er that is generated using a timestamp and the
MAC address of the computer on which it was generated.
UUID v4 : is a universally unique identi er that is generated using random numbers.
With UUID v4, it's not possible to generate any sequential output.
59

View Slide

MySQL Shell for VS Code
MySQL in Visual Studio Code
60

View Slide

61

View Slide

Visual Studio Code is the most
popular IDE with developers
61

View Slide

62

View Slide

MySQL 8.0 Document Store
discovery of a new world !
63

View Slide

NoSQL Document Store
Schemaless
64

View Slide

Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
64

View Slide

Schemaless
very quick initial development
64

View Slide

Schemaless
Flexible data structure
64

View Slide

Schemaless
embedded arrays or objects
64

View Slide

Schemaless
valid solution when natural data can't be modelized optimaly into a relational model
64

View Slide

Schemaless
objects persistance without the use of any ORM - mapping oobject-oriented
64

View Slide

Schemaless
JSON
64

View Slide

Schemaless
JSON
close to frontend
64

View Slide

Schemaless
JSON
close to frontend
easy to learn
64

View Slide

How DBAs see data
65

View Slide

How DBAs see data How Developers see data
{
"GNP" : 249704,
"Name" : "Belgium",
"government" : {
"GovernmentForm" :
"Constitutional Monarchy, Federation",
"HeadOfState" : "Philippe I"
},
"_id" : "BEL",
"IndepYear" : 1830,
"demographics" : {
"Population" : 10239000,
"LifeExpectancy" : 77.8000030517578
},
"geography" : {
"Region" : "Western Europe",
"SurfaceArea" : 30518,
"Continent" : "Europe"
}
}
65

View Slide

And they still need to do Analytics
SQL
SQL
SQL
66

View Slide

... mmm...how ?
?
SQL
SQL
SQL
67

View Slide

How ?
SQL
SQL
SQL
68

View Slide

What if there was a way to provide both SQL and NoSQL
on one stable platform that has proven stability on well
know technology with a large Community and a diverse
ecosystem ?
69

View Slide

DBMS or NoSQL ?
70

View Slide

DBMS or NoSQL ?
Why not both ?
70

View Slide

The MySQL Document Store !
SQL is now optional ?!
71

View Slide

SQL is now optional ?!
72

View Slide

Using MySQL Document Store !
SQL
SQL
SQL
73

View Slide

the Solution
MySQL Document Store
74

View Slide

Built on
the MySQL
JSON Data
type and
Proven
MySQL
Server
Technology
Provides a schema exible JSON Document Store
No SQL required
No need to de ne all possible a ributes, tables, etc.
Uses new X DevAPI
Can leverage generated column to extract JSON values into materialized
columns that can be indexed for fast SQL searches.
Document can be ~1GB
It's a column in a row of a table
It cannot exceed max_allowed_packet
Allows use of modern programming styles
No more embedded strings of SQL in your code
Easy to read
Also works with relational Tables
Proven MySQL Technology
75

View Slide

X Protocol Connectors
76

View Slide

X DevAPI We provide connectors for
C++, Java, .Net, Node.js, Python, PHP
working with Communities to help them supporting it too
New MySQL Shell
Command Completion
Python, JavaScrips & SQL modes
Admin functions
New Util object
A new high-level session concept that can scale from single MySQL
server to a multiple server environment
Non-blocking, asynchronous calls follow common language pa erns
Supports CRUD operations
77

View Slide

Setup
78

View Slide

Installing MySQL Document Store
install MySQL 8.0
79

View Slide

install MySQL 8.0
install MySQL Shell
79

View Slide

install MySQL 8.0
install MySQL Shell
install MySQL Connector for your programming language
79

View Slide

install MySQL 8.0
install MySQL Shell
php-pecl-mysql-xdevapi for PHP
mysql-connector-python for Python
...
79

View Slide

install MySQL 8.0
install MySQL Shell
php-pecl-mysql-xdevapi for PHP
mysql-connector-python for Python
...
And nothing else, no need to install anything else or load any plugin, just be sure your
rewall allows you to connect through port 33060 (X Protocol).
79

View Slide

MySQL Database Service
X Protocol is also available in MDS !!
80

View Slide

Migration from MongoDB to MySQL DS
For this example, I will use the well known restaurants collection:
81

View Slide

82

View Slide

83

View Slide

Let's make a query
JS > restaurants.find()
84

View Slide

Let's make a query
JS > restaurants.find()
That's too much records to show in here... let's limit it
84

View Slide

85

View Slide

Some more examples
86

View Slide

Let's add a selection criteria:
87

View Slide

Syntax slightly di erent than MongoDB
88

View Slide

And for developers ?
89

View Slide

$session = mysql_xdevapi\getSession("mysqlx://fred:MyP@ssw0rd%@localhost");
$schema = $session->getSchema("docstore");
$collection = $schema->getCollection("restaurants");
$results = $collection->find($search)->execute()->fetchAll();
...
foreach ($results as $doc) {
echo "${doc[name]}";
echo "${doc[borough]}${doc[cuisine]}";
}
90

View Slide

...
}
Easy, using only CRUD operations !
90

View Slide

...
}
Easy, using only CRUD operations !
Not a single SQL statement !
90

View Slide

For Java
import
import *
*;
;
class
class Main
Main {
{
public
public static
static void
void main
main(
(String
String args
args[
[]
])
) {
{
Session
Session mySession
mySession =
= new
new SessionFactory
SessionFactory(
()
)
.
.getSession
getSession(
("mysqlx://localhost:33060/docstore?user=resto&password=Passw0rd!"
"mysqlx://localhost:33060/docstore?user=resto&password=Passw0rd!")
);
;
Schema
Schema myDb
myDb =
= mySession
mySession.
.getSchema
getSchema(
("docstore"
"docstore")
);
;
Collection
Collection myColl
myColl =
= myDb
myDb.
.getCollection
getCollection(
("restaurants"
"restaurants")
);
;
DocResult
DocResult myDocs
myDocs =
= myColl
myColl.
.find
find(
("name like :param"
"name like :param")
).
.limit
limit(
(1
1)
)
.
.bind
bind(
("param"
"param",
, "Green%"
"Green%")
).
.execute
execute(
()
);
;
System
System.
.out
out.
.println
println(
(myDocs
myDocs.
.fetchOne
fetchOne(
()
))
);
;
mySession
mySession.
.close
close(
()
);
;
}
}
}
}
com
com.
.mysql
mysql.
.cj
cj.
.xdevapi
xdevapi.
.
91

View Slide

CRUD operations
92

View Slide

CRUD operations for collections
Add a document
collection.add({ name: 'fred', age: 42 })
.add({ name: 'dave', age: 23 })
.execute()
collection.add([
{ name: 'dimo', age: 50 },
{ name: 'kenny', age: 25 }
]).execute()
93

View Slide

Modify a document
collection.modify('name = :name')
.bind('name', 'fred')
.set('age', 43)
.sort('name ASC')
.limit(1)
.execute()
collection.modify('name = :name')
.patch({ age: 43, active: false })
.sort('name DESC')
.limit(1)
.execute()
94

View Slide

Remove a document
collection.remove('name = :name')
.sort('age ASC')
.limit(1)
.execute()
95

View Slide

MySQL Document Store Objects Summary
96

View Slide

All you need to know is here:
h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html
97

View Slide

is Full ACID Compliant
we do care about your data
98

View Slide

Document Store Full ACID !
It relies on the proven MySQL InnoDB's strength & robustness:
99

View Slide

innodb_flush_log_at_trx_commit = 1
99

View Slide

innodb_doublewrite = ON
99

View Slide

sync_binlog = 1
99

View Slide

sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
99

View Slide

sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
We do care about your data !
99

View Slide

Full ACID - Transactions support
100

View Slide

Full ACID - Transactions support
101

View Slide

OK we have Document Store,
CRUD and ACID
but what makes MySQL Document Store unique ?
102

View Slide

Challenge: list the best restaurant of each type of food
and show the top 10, with the best one rst !
don't forget that all these restaurants are just JSON documents
103

View Slide

NoSQL as SQL - aggregation
104

View Slide

105

View Slide

106

View Slide

107

View Slide

NoSQL or SQL
You have the possibility to write clean and neat code:
108

View Slide

NoSQL or SQL
$results = $collection->find('cuisine like "italian"')->execute()->fetchAll();
108

View Slide

NoSQL or SQL
Or use SQL only when it's really needed:
108

View Slide

NoSQL or SQL
$results = $session->sql('WITH cte1 AS (SELECT doc->>"$.name" AS name,
doc->>"$.cuisine" AS cuisine,
(SELECT AVG(score) FROM JSON_TABLE(doc, "$.grades[*]" COLUMNS (score INT
PATH "$.score")) AS r) AS avg_score FROM docstore.restaurants)
SELECT *, RANK()
OVER ( PARTITION BY cuisine ORDER BY avg_score) AS `rank`
FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10;')->execute();
108

View Slide

NoSQL or SQL
$results = $session->sql('WITH cte1 AS (SELECT doc->>"$.name" AS name,
doc->>"$.cuisine" AS cuisine,
(SELECT AVG(score) FROM JSON_TABLE(doc, "$.grades[*]" COLUMNS (score INT
PATH "$.score")) AS r) AS avg_score FROM docstore.restaurants)
SELECT *, RANK()
OVER ( PARTITION BY cuisine ORDER BY avg_score) AS `rank`
FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10;')->execute();
All in the same MySQL X Session !
108

View Slide

You can mix NoSQL & SQL as you want
109

View Slide

Best of Both Worlds: JSON_TABLE
What are the maximum 10 ratings ever given to a restaurant?
110

View Slide

Best of Both Worlds: JSON_TABLE
What are the maximum 10 ratings ever given to a restaurant?
Cool... but my app only processes JSON !
110

View Slide

Best of Both Worlds: JSON_TABLE (2)
With JSON output:
111

View Slide

CHECK CONSTRAINTS
We already saw that MySQL 8.0 supports Check Constraints:
112

View Slide

JSON Schema Validation
113

View Slide

JSON Schema Validation (2)
114

View Slide

And the best of both worlds:
115

View Slide

And the best of both worlds:
And the result in action:
115

View Slide

Conclusion
what do I gain ?
116

View Slide

schemaless
exible data structure
easy to start (CRUD)
Conclusion
This is the best of the two worlds in one product !
Data integrity
ACID Compliant
Transactions
SQL
117

View Slide

Migrating to Connector/J 8.0
some info for the Java developers
118

View Slide

Migration from Connector/J 5.1 to 8.0
You may have encountered problems with migration from Connector/J 5.1 to Connector/J
8.0 (before 8.0.23).
They were caused by the early decision that Connector/J 8.0 should always try to preserve
an instant point on the time-line while Connector/J 5.1 does it optionally and, by default,
preserves the original visual representation.
119

View Slide

Migration from Connector/J 5.1 to 8.0 (2)
For example, the following code will store di erent results with Connector/J 5.1 and
Connector/J 8.0 in case the client and server time zones are di erent:
Statement
Statement st
st =
= conn
conn.
.createStatement
createStatement(
()
);
;
st
st.
.executeUpdate
executeUpdate(
("CREATE TABLE t1 (ts TIMESTAMP)"
"CREATE TABLE t1 (ts TIMESTAMP)")
);
;
PreparedStatement
PreparedStatement ps
ps =
= conn
conn.
.prepareStatement
prepareStatement(
("INSERT INTO t1 VALUES (?)"
"INSERT INTO t1 VALUES (?)")
);
;
ps
ps.
.setTimestamp
setTimestamp(
(1
1,
, Timestamp
Timestamp.
.valueOf
valueOf(
("2020-01-01 12:00:00"
"2020-01-01 12:00:00")
))
);
;
ps
ps.
.executeUpdate
executeUpdate(
()
);
;
120

View Slide

For example, the following code will store di erent results with Connector/J 5.1 and
Connector/J 8.0 in case the client and server time zones are di erent:
Statement
Statement st
st =
= conn
conn.
.createStatement
createStatement(
()
);
;
st
st.
.executeUpdate
executeUpdate(
("CREATE TABLE t1 (ts TIMESTAMP)"
"CREATE TABLE t1 (ts TIMESTAMP)")
);
;
PreparedStatement
PreparedStatement ps
ps =
= conn
conn.
.prepareStatement
prepareStatement(
("INSERT INTO t1 VALUES (?)"
"INSERT INTO t1 VALUES (?)")
);
;
ps
ps.
.setTimestamp
setTimestamp(
(1
1,
, Timestamp
Timestamp.
.valueOf
valueOf(
("2020-01-01 12:00:00"
"2020-01-01 12:00:00")
))
);
;
ps
ps.
.executeUpdate
executeUpdate(
()
);
;
If the client is running in the UTC+2 time zone and server is running in UTC+1 the internal
value of the TIMESTAMP eld will be <2020-01-01 11:00:00Z= with Connector/J 5.1 but
<2020-01-01 10:00:00Z= with Connector/J 8.0.
120

View Slide

Another client in the UTC+3 time zone is reading this value:
ResultSet
ResultSet rs
rs =
= st
st.
.executeQuery
executeQuery(
("SELECT * FROM t1"
"SELECT * FROM t1")
);
;
Timestamp
Timestamp ts
ts =
= rs
rs.
.getTimestamp
getTimestamp(
(1
1)
);
;
121

View Slide

Another client in the UTC+3 time zone is reading this value:
ResultSet
ResultSet rs
rs =
= st
st.
.executeQuery
executeQuery(
("SELECT * FROM t1"
"SELECT * FROM t1")
);
;
Timestamp
Timestamp ts
ts =
= rs
rs.
.getTimestamp
getTimestamp(
(1
1)
);
;
The result will be <2020-01-01 12:00:00= with Connector/J 5.1 but <2020-01-01 13:00:00=
with Connector/J 8.0.
121

View Slide

By default, Connector/J 5.1 sends values as
they are rendered locally and, on retrieval,
constructs values using the client's local
time zone.
Connector/J 8.0.22 and before converts the
original value to the session time zone
before sending, thus the internal UTC value
of a TIMESTAMP matches the expected
instant value. When retrieved, a value is
constructed after converting the on-wire
value from session time zone to the local
one, so it still represents the same instant,
but the visual representation is di erent in
di erent client time zones.
122

View Slide

Since Connector/J 8.0.23, both ways are now possible. The following connection properties
de ne the time zone handling:
connectionTimeZone=LOCAL|SERVER|user-defined time zone (previously known as
serverTimezone, now with additional xed values) de nes how the server's session time
zone is to be determined by Connector/J.
forceConnectionTimeZoneToSession=true|false controls whether the session time_zone
variable is to be set to the value speci ed in connectionTimeZone.
preserveInstants=true|false turns on|o the conversion of instant values between JVM
and connectionTimeZone.
123

View Slide

The most useful con gurations are:
connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=false –
corresponds with the Connector/J 5.1 behavior with useLegacyDatetimeCode=true.
connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=true – the
new mode which provides the most natural way for handling date-time values.
124

View Slide

connectionTimeZone=SERVER & preserveInstants=true – corresponds to the
previous Connector/J 8.0 behavior and Connector/J 5.1 behavior with
useLegacyDatetimeCode=false.
connectionTimeZone=user_defined & preserveInstants=true – helps to overcome
the situation when the server time zone cannot be recognized by the connector because
it is set as a generic abbreviation like CET/CEST.
More info on h ps://dev.mysql.com/blog-archive/support-for-date-time-types-in-
connector-j-8-0/
125

View Slide

MySQL 8.0 DBA Certi cation
126

View Slide

MySQL 8.0 Developer Certi cation
127

View Slide

Thank you !
128

View Slide

RivieraJUG - MySQL 8.0 : What's new for developers

RivieraJUG - MySQL 8.0 : What's new for developers

lefred

More Decks by lefred

Other Decks in Technology

Featured

Transcript