Slide 1
Slide 1 text
Frédéric Descamps
Community Manager
Oracle MySQL
RivieraJUG - September 2022
MySQL 8.0
What's new for developers ?
Slide 2
Slide 2 text
Who am I ?
about.me/lefred
Copyright @ 2022 Oracle and/or its affiliates.
2
Slide 3
Slide 3 text
Frédéric Descamps
@lefred
MySQL Evangelist
Managing MySQL since 3.20
devops believer
living in Belgium
h ps://lefred.be
Copyright @ 2022 Oracle and/or its affiliates.
3
Slide 4
Slide 4 text
New Volcano Iterator
MySQL Optimizer Refactoring
Copyright @ 2022 Oracle and/or its affiliates.
4
Slide 5
Slide 5 text
Modular
Easy to Extend
Each iterator encapsulates one
operation
Same interface for all iterators
All operations can be connected
MySQL New Iterator Executor
Copyright @ 2022 Oracle and/or its affiliates.
5
Slide 6
Slide 6 text
EXPLAIN FORMAT=TREE
Copyright @ 2022 Oracle and/or its affiliates.
6
Slide 7
Slide 7 text
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
Copyright @ 2022 Oracle and/or its affiliates.
7
Slide 8
Slide 8 text
EXPLAIN ANALYZE
Copyright @ 2022 Oracle and/or its affiliates.
8
Slide 9
Slide 9 text
EXPLAIN ANALYZE
Copyright @ 2022 Oracle and/or its affiliates.
9
Slide 10
Slide 10 text
EXPLAIN ANALYZE
Copyright @ 2022 Oracle and/or its affiliates.
10
Slide 11
Slide 11 text
EXPLAIN ANALYZE
Copyright @ 2022 Oracle and/or its affiliates.
11
Slide 12
Slide 12 text
EXPLAIN ANALYZE
Copyright @ 2022 Oracle and/or its affiliates.
12
Slide 13
Slide 13 text
Hash Joins
bye bye Block Nested Loop
Copyright @ 2022 Oracle and/or its affiliates.
13
Slide 14
Slide 14 text
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.
Copyright @ 2022 Oracle and/or its affiliates.
14
Slide 15
Slide 15 text
Hash Join: performance
Copyright @ 2022 Oracle and/or its affiliates.
15
Slide 16
Slide 16 text
MySQL 8.0
one giant leap for SQL
Copyright @ 2022 Oracle and/or its affiliates.
16
Slide 17
Slide 17 text
"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
Copyright @ 2022 Oracle and/or its affiliates.
17
Slide 18
Slide 18 text
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)
Copyright @ 2022 Oracle and/or its affiliates.
18
Slide 19
Slide 19 text
Credits: @MarkusWinand - @ModernSQL
SQL: RECURSION / CTEs (WITH clause)
Copyright @ 2022 Oracle and/or its affiliates.
19
Slide 20
Slide 20 text
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 ...
Copyright @ 2022 Oracle and/or its affiliates.
20
Slide 21
Slide 21 text
Credits: @MarkusWinand - @ModernSQL
SQL: LATERAL DERIVED TABLES
Copyright @ 2022 Oracle and/or its affiliates.
21
Slide 22
Slide 22 text
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.
Copyright @ 2022 Oracle and/or its affiliates.
22
Slide 23
Slide 23 text
Credits: @MarkusWinand - @ModernSQL
SQL: Analytical / Window Functions
Copyright @ 2022 Oracle and/or its affiliates.
23
Slide 24
Slide 24 text
Credits: @MarkusWinand - @ModernSQL
SQL: Analytical / Window Functions
Copyright @ 2022 Oracle and/or its affiliates.
24
Slide 25
Slide 25 text
Credits: @MarkusWinand - @ModernSQL
SQL: JSON_TABLE
From JSON Document to SQL Table
Copyright @ 2022 Oracle and/or its affiliates.
25
Slide 26
Slide 26 text
Credits: @MarkusWinand - @ModernSQL
SQL: JSON_TABLE
From JSON Document to SQL Table
Copyright @ 2022 Oracle and/or its affiliates.
26
Slide 27
Slide 27 text
This function is described in SQL 2016,
chapter 6.27 and is also implemented in:
Oracle
SQL Server
DB2
SQL: JSON_VALUE
Copyright @ 2022 Oracle and/or its affiliates.
27
Slide 28
Slide 28 text
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:
Copyright @ 2022 Oracle and/or its affiliates.
28
Slide 29
Slide 29 text
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))) ;
Copyright @ 2022 Oracle and/or its affiliates.
29
Slide 30
Slide 30 text
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
Copyright @ 2022 Oracle and/or its affiliates.
30
Slide 31
Slide 31 text
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) );
Copyright @ 2022 Oracle and/or its affiliates.
31
Slide 32
Slide 32 text
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, ...
Copyright @ 2022 Oracle and/or its affiliates.
32
Slide 33
Slide 33 text
SELECT * FROM tickets
WHERE id IN (1,2,3,4)
AND order_id IS NULL
FOR UPDATE
NOWAIT;
SELECT * FROM tickets
WHERE id IN (1,2,3,4)
AND order_id IS NULL
FOR UPDATE
SKIP LOCKED;
SQL: NOWAIT & SKIP LOCKED
Copyright @ 2022 Oracle and/or its affiliates.
33
Slide 34
Slide 34 text
SELECT * FROM tickets
WHERE id IN (1,2,3,4)
AND order_id IS NULL
FOR UPDATE
NOWAIT;
SELECT * FROM tickets
WHERE id IN (1,2,3,4)
AND order_id IS NULL
FOR UPDATE
SKIP LOCKED;
Error immediately if a row is
already locked
Non deterministically skip over
locked rows
SQL: NOWAIT & SKIP LOCKED
Copyright @ 2022 Oracle and/or its affiliates.
33
Slide 35
Slide 35 text
MySQL 8.0 GIPK Mode
make the DBAs happy !
Copyright @ 2022 Oracle and/or its affiliates.
34
Slide 36
Slide 36 text
InnoDB Primary Keys, Invisible column and GIPK
For InnoDB, a Primary Key is required and a good one is even be er !
Copyright @ 2022 Oracle and/or its affiliates.
35
Slide 37
Slide 37 text
InnoDB Primary Keys, Invisible column and GIPK
For InnoDB, a Primary Key is required and a good one is even be er !
Some theory
InnoDB stores data in table spaces. The records are stored and sorted using the clustered
index (PK).
Copyright @ 2022 Oracle and/or its affiliates.
35
Slide 38
Slide 38 text
InnoDB Primary Keys, Invisible column and GIPK
For InnoDB, a Primary Key is required and a good one is even be er !
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.
Copyright @ 2022 Oracle and/or its affiliates.
35
Slide 39
Slide 39 text
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.
Copyright @ 2022 Oracle and/or its affiliates.
36
Slide 40
Slide 40 text
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.
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.
Copyright @ 2022 Oracle and/or its affiliates.
36
Slide 41
Slide 41 text
InnoDB Primary Key (3)
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
Copyright @ 2022 Oracle and/or its affiliates.
37
Slide 42
Slide 42 text
InnoDB Primary Key ? No Key !
Another common mistake when using InnoDB is to not de ne any Primary Key.
Copyright @ 2022 Oracle and/or its affiliates.
38
Slide 43
Slide 43 text
InnoDB Primary Key ? No Key !
Another common mistake when using InnoDB is to not de ne any Primary Key.
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).
Copyright @ 2022 Oracle and/or its affiliates.
38
Slide 44
Slide 44 text
InnoDB Primary Key ? No Key !
Another common mistake when using InnoDB is to not de ne any Primary Key.
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).
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).
Copyright @ 2022 Oracle and/or its affiliates.
38
Slide 45
Slide 45 text
InnoDB Primary Key ? No Key !
Another common mistake when using InnoDB is to not de ne any Primary Key.
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).
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 !
Copyright @ 2022 Oracle and/or its affiliates.
38
Slide 46
Slide 46 text
InnoDB Primary Key ? No Key ! (2)
Luckily since MySQL 8.0.23 there is a solution: Invisible Column !
Copyright @ 2022 Oracle and/or its affiliates.
39
Slide 47
Slide 47 text
InnoDB Primary Key ? No Key ! (2)
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 !
Copyright @ 2022 Oracle and/or its affiliates.
39
Slide 48
Slide 48 text
InnoDB Primary Key ? No Key ! (2)
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
FROM information_schema
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";
;
Copyright @ 2022 Oracle and/or its affiliates.
40
Slide 49
Slide 49 text
InnoDB Primary Key ? No Key ! (2)
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
FROM information_schema
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";
;
Copyright @ 2022 Oracle and/or its affiliates.
+--------------+-----------------+--------+
| 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
Slide 50
Slide 50 text
InnoDB Primary Key ? No Key ! (3)
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
Copyright @ 2022 Oracle and/or its affiliates.
41
Slide 51
Slide 51 text
InnoDB Primary Key ? No Key ! (3)
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
Copyright @ 2022 Oracle and/or its affiliates.
+----------+----------------------+
| 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
Slide 52
Slide 52 text
InnoDB Primary Key ? No Key ! (4)
Copyright @ 2022 Oracle and/or its affiliates.
42
Slide 53
Slide 53 text
InnoDB Primary Key ? No Key ! (4)
Perfect for replication !
Copyright @ 2022 Oracle and/or its affiliates.
42
Slide 54
Slide 54 text
InnoDB Primary Key ? No Key ! (5)
Copyright @ 2022 Oracle and/or its affiliates.
43
Slide 55
Slide 55 text
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.
Copyright @ 2022 Oracle and/or its affiliates.
44
Slide 56
Slide 56 text
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.
Copyright @ 2022 Oracle and/or its affiliates.
44
Slide 57
Slide 57 text
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 |
+--------+-------+
2 rows in set (0.0002 sec)
Copyright @ 2022 Oracle and/or its affiliates.
45
Slide 58
Slide 58 text
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
Copyright @ 2022 Oracle and/or its affiliates.
46
Slide 59
Slide 59 text
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
MySQL > SELECT *, my_row_id FROM rivierajug;
+--------+-------+-----------+
| name | beers | my_row_id |
+--------+-------+-----------+
| Yannis | 0 | 1 |
| lefred | 1 | 2 |
+--------+-------+-----------+
2 rows in set (0.0003 sec)
Copyright @ 2022 Oracle and/or its affiliates.
46
Slide 60
Slide 60 text
InnoDB GIPK mode - example (3)
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;
MySQL > SHOW CREATE TABLE rivierajug\G
*************************** 1. row ***************************
Table: rivierajug
Create Table: CREATE TABLE `rivierajug` (
`name` varchar(20) DEFAULT NULL,
`beers` int unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copyright @ 2022 Oracle and/or its affiliates.
47
Slide 61
Slide 61 text
InnoDB GIPK mode - example (4)
MySQL > SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "rivierajug";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| beers | 3 | int | |
| name | 2 | varchar | |
+-------------+------------------+-----------+------------+
MySQL > SET show_gipk_in_create_table_and_information_schema = 1;
MySQL > SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "rivierajug";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| beers | 3 | int | |
| my_row_id | 1 | bigint | PRI |
| name | 2 | varchar | |
+-------------+------------------+-----------+------------+
Copyright @ 2022 Oracle and/or its affiliates.
48
Slide 62
Slide 62 text
. 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:
Copyright @ 2022 Oracle and/or its affiliates.
49
Slide 63
Slide 63 text
InnoDB Primary Key - What about UUID ? (2)
Example:
MySQL > CREATE TABLE rivierajug2 (
uuid VARCHAR(36) DEFAULT (UUID()) PRIMARY KEY,
name VARCHAR(20), beers int unsigned);
MySQL > SELECT * FROM rivierajug2;
+--------------------------------------+---------+-------+
| uuid | name | beers |
+--------------------------------------+---------+-------+
| 17cd1188-1fa0-11ed-ba36-c8cb9e32df8e | Yannis | 0 |
| 17cd12e2-1fa0-11ed-ba36-c8cb9e32df8e | lefred | 1 |
| 478368a0-1fa0-11ed-ba36-c8cb9e32df8e | Valérie | 0 |
| 47836a77-1fa0-11ed-ba36-c8cb9e32df8e | Laurent | 1 |
+--------------------------------------+---------+-------+
Copyright @ 2022 Oracle and/or its affiliates.
50
Slide 64
Slide 64 text
InnoDB Primary Key - What about UUID ? (3)
Let's insert 2 new records:
MySQL > INSERT INTO rivierajug2 (name, beers) VALUES ("Benoît",1), ("Sarah",5);
Query OK, 2 rows affected (0.0069 sec)
Copyright @ 2022 Oracle and/or its affiliates.
51
Slide 65
Slide 65 text
InnoDB Primary Key - What about UUID ? (3)
Let's insert 2 new records:
MySQL > INSERT INTO rivierajug2 (name, beers) VALUES ("Benoît",1), ("Sarah",5);
Query OK, 2 rows affected (0.0069 sec)
MySQL > SELECT * FROM rivierajug2;
+--------------------------------------+---------+-------+
| uuid | name | beers |
+--------------------------------------+---------+-------+
| 17cd1188-1fa0-11ed-ba36-c8cb9e32df8e | Yannis | 0 |
| 17cd12e2-1fa0-11ed-ba36-c8cb9e32df8e | lefred | 1 |
| 36f1ce9a-1fa1-11ed-ba36-c8cb9e32df8e | Benoît | 1 |
| 36f1d158-1fa1-11ed-ba36-c8cb9e32df8e | Sarah | 5 |
| 478368a0-1fa0-11ed-ba36-c8cb9e32df8e | Valérie | 0 |
| 47836a77-1fa0-11ed-ba36-c8cb9e32df8e | Laurent | 1 |
+--------------------------------------+---------+-------+
Copyright @ 2022 Oracle and/or its affiliates.
51
Slide 66
Slide 66 text
InnoDB Primary Key - What about UUID ? (4)
OUPS ! We have rebalanced the clustered index !
What does that mean again ??
Copyright @ 2022 Oracle and/or its affiliates.
52
Slide 67
Slide 67 text
InnoDB Primary Key - What about UUID ? (4)
OUPS ! We have rebalanced the clustered index !
What does that mean again ??
Let me try to explain this with this high level and simpli ed example:
Copyright @ 2022 Oracle and/or its affiliates.
52
Slide 68
Slide 68 text
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:
InnoDB Primary Key - What about UUID ? (5)
OUPS ! We have rebalanced the clustered index !
Copyright @ 2022 Oracle and/or its affiliates.
53
Slide 69
Slide 69 text
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
Slide 70
Slide 70 text
InnoDB Primary Key - What about UUID ? (6)
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)
Copyright @ 2022 Oracle and/or its affiliates.
54
Slide 71
Slide 71 text
InnoDB Primary Key - What about UUID ? (7)
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.
Copyright @ 2022 Oracle and/or its affiliates.
55
Slide 72
Slide 72 text
InnoDB Primary Key - What about UUID ? (8)
Recommended solution - example
MySQL > CREATE TABLE rivierajug3 (
uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
name VARCHAR(20), beers int unsigned);
MySQL > SELECT * FROM rivierajug3;
+------------------------------------+--------+-------+
| uuid | name | beers |
+------------------------------------+--------+-------+
| 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 |
| 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 |
+------------------------------------+--------+-------+
Copyright @ 2022 Oracle and/or its affiliates.
56
Slide 73
Slide 73 text
InnoDB Primary Key - What about UUID ? (8)
Recommended solution - example
MySQL > CREATE TABLE rivierajug3 (
uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
name VARCHAR(20), beers int unsigned);
MySQL > SELECT * FROM rivierajug3;
+------------------------------------+--------+-------+
| uuid | name | beers |
+------------------------------------+--------+-------+
| 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 |
| 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 |
+------------------------------------+--------+-------+
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 |
+--------------------------------------+--------+-------+
Copyright @ 2022 Oracle and/or its affiliates.
56
Slide 74
Slide 74 text
InnoDB Primary Key - What about UUID ? (9)
Recommended solution - example
MySQL > INSERT INTO rivierajug3 (name, beers) VALUES ("Benoît",1), ("Sarah",5);
MySQL > SELECT * FROM rivierajug3;
+------------------------------------+---------+-------+
| uuid | name | beers |
+------------------------------------+---------+-------+
| 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 |
| 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 |
| 0x11ED1FA537C57361BA36C8CB9E32DF8E | Benoît | 1 |
| 0x11ED1FA537C5752DBA36C8CB9E32DF8E | Sarah | 5 |
+------------------------------------+---------+-------+
Copyright @ 2022 Oracle and/or its affiliates.
57
Slide 75
Slide 75 text
InnoDB Primary Key - What about UUID ? (9)
Recommended solution - example
MySQL > INSERT INTO rivierajug3 (name, beers) VALUES ("Benoît",1), ("Sarah",5);
MySQL > SELECT * FROM rivierajug3;
+------------------------------------+---------+-------+
| uuid | name | beers |
+------------------------------------+---------+-------+
| 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 |
| 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 |
| 0x11ED1FA537C57361BA36C8CB9E32DF8E | Benoît | 1 |
| 0x11ED1FA537C5752DBA36C8CB9E32DF8E | Sarah | 5 |
+------------------------------------+---------+-------+
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 |
| 37c57361-1fa5-11ed-ba36-c8cb9e32df8e | Benoît | 1 |
| 37c5752d-1fa5-11ed-ba36-c8cb9e32df8e | Sarah | 5 |
+--------------------------------------+---------+-------+
Copyright @ 2022 Oracle and/or its affiliates.
57
Slide 76
Slide 76 text
InnoDB Primary Key - What about UUID ? (10)
Recommended solution - example
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
Copyright @ 2022 Oracle and/or its affiliates.
58
Slide 77
Slide 77 text
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.
Copyright @ 2022 Oracle and/or its affiliates.
59
Slide 78
Slide 78 text
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.
Copyright @ 2022 Oracle and/or its affiliates.
59
Slide 79
Slide 79 text
MySQL Shell for VS Code
MySQL in Visual Studio Code
Copyright @ 2022 Oracle and/or its affiliates.
60
Slide 80
Slide 80 text
MySQL Shell for VS Code
Copyright @ 2022 Oracle and/or its affiliates.
61
Slide 81
Slide 81 text
Visual Studio Code is the most
popular IDE with developers
MySQL Shell for VS Code
Copyright @ 2022 Oracle and/or its affiliates.
61
Slide 82
Slide 82 text
MySQL Shell for VS Code
Copyright @ 2022 Oracle and/or its affiliates.
62
Slide 83
Slide 83 text
MySQL 8.0 Document Store
discovery of a new world !
Copyright @ 2022 Oracle and/or its affiliates.
63
Slide 84
Slide 84 text
NoSQL Document Store
Schemaless
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 85
Slide 85 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 86
Slide 86 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 87
Slide 87 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Flexible data structure
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 88
Slide 88 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Flexible data structure
embedded arrays or objects
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 89
Slide 89 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Flexible data structure
embedded arrays or objects
valid solution when natural data can't be modelized optimaly into a relational model
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 90
Slide 90 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Flexible data structure
embedded arrays or objects
valid solution when natural data can't be modelized optimaly into a relational model
objects persistance without the use of any ORM - mapping oobject-oriented
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 91
Slide 91 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Flexible data structure
embedded arrays or objects
valid solution when natural data can't be modelized optimaly into a relational model
objects persistance without the use of any ORM - mapping oobject-oriented
JSON
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 92
Slide 92 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Flexible data structure
embedded arrays or objects
valid solution when natural data can't be modelized optimaly into a relational model
objects persistance without the use of any ORM - mapping oobject-oriented
JSON
close to frontend
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 93
Slide 93 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Flexible data structure
embedded arrays or objects
valid solution when natural data can't be modelized optimaly into a relational model
objects persistance without the use of any ORM - mapping oobject-oriented
JSON
close to frontend
easy to learn
Copyright @ 2022 Oracle and/or its affiliates.
64
Slide 94
Slide 94 text
How DBAs see data
Copyright @ 2022 Oracle and/or its affiliates.
65
Slide 95
Slide 95 text
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"
}
}
Copyright @ 2022 Oracle and/or its affiliates.
65
Slide 96
Slide 96 text
And they still need to do Analytics
SQL
SQL
SQL
Copyright @ 2022 Oracle and/or its affiliates.
66
Slide 97
Slide 97 text
... mmm...how ?
?
SQL
SQL
SQL
Copyright @ 2022 Oracle and/or its affiliates.
67
Slide 98
Slide 98 text
How ?
SQL
SQL
SQL
Copyright @ 2022 Oracle and/or its affiliates.
68
Slide 99
Slide 99 text
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 ?
Copyright @ 2022 Oracle and/or its affiliates.
69
Slide 100
Slide 100 text
DBMS or NoSQL ?
Copyright @ 2022 Oracle and/or its affiliates.
70
Slide 101
Slide 101 text
DBMS or NoSQL ?
Why not both ?
Copyright @ 2022 Oracle and/or its affiliates.
70
Slide 102
Slide 102 text
The MySQL Document Store !
SQL is now optional ?!
Copyright @ 2022 Oracle and/or its affiliates.
71
Slide 103
Slide 103 text
SQL is now optional ?!
Copyright @ 2022 Oracle and/or its affiliates.
72
Slide 104
Slide 104 text
Using MySQL Document Store !
SQL
SQL
SQL
Copyright @ 2022 Oracle and/or its affiliates.
73
Slide 105
Slide 105 text
the Solution
MySQL Document Store
Copyright @ 2022 Oracle and/or its affiliates.
74
Slide 106
Slide 106 text
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
Copyright @ 2022 Oracle and/or its affiliates.
75
Slide 107
Slide 107 text
X Protocol Connectors
Copyright @ 2022 Oracle and/or its affiliates.
76
Slide 108
Slide 108 text
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
Copyright @ 2022 Oracle and/or its affiliates.
77
Slide 109
Slide 109 text
Setup
MySQL Document Store
Copyright @ 2022 Oracle and/or its affiliates.
78
Slide 110
Slide 110 text
Installing MySQL Document Store
install MySQL 8.0
Copyright @ 2022 Oracle and/or its affiliates.
79
Slide 111
Slide 111 text
Installing MySQL Document Store
install MySQL 8.0
install MySQL Shell
Copyright @ 2022 Oracle and/or its affiliates.
79
Slide 112
Slide 112 text
Installing MySQL Document Store
install MySQL 8.0
install MySQL Shell
install MySQL Connector for your programming language
Copyright @ 2022 Oracle and/or its affiliates.
79
Slide 113
Slide 113 text
Installing MySQL Document Store
install MySQL 8.0
install MySQL Shell
install MySQL Connector for your programming language
php-pecl-mysql-xdevapi for PHP
mysql-connector-python for Python
...
Copyright @ 2022 Oracle and/or its affiliates.
79
Slide 114
Slide 114 text
Installing MySQL Document Store
install MySQL 8.0
install MySQL Shell
install MySQL Connector for your programming language
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).
Copyright @ 2022 Oracle and/or its affiliates.
79
Slide 115
Slide 115 text
MySQL Database Service
X Protocol is also available in MDS !!
Copyright @ 2022 Oracle and/or its affiliates.
80
Slide 116
Slide 116 text
Migration from MongoDB to MySQL DS
For this example, I will use the well known restaurants collection:
Copyright @ 2022 Oracle and/or its affiliates.
81
Slide 117
Slide 117 text
Migration from MongoDB to MySQL DS
For this example, I will use the well known restaurants collection:
Copyright @ 2022 Oracle and/or its affiliates.
81
Slide 118
Slide 118 text
Copyright @ 2022 Oracle and/or its affiliates.
82
Slide 119
Slide 119 text
Copyright @ 2022 Oracle and/or its affiliates.
83
Slide 120
Slide 120 text
Let's make a query
JS > restaurants.find()
Copyright @ 2022 Oracle and/or its affiliates.
84
Slide 121
Slide 121 text
Let's make a query
JS > restaurants.find()
That's too much records to show in here... let's limit it
Copyright @ 2022 Oracle and/or its affiliates.
84
Slide 122
Slide 122 text
Copyright @ 2022 Oracle and/or its affiliates.
85
Slide 123
Slide 123 text
Some more examples
Copyright @ 2022 Oracle and/or its affiliates.
86
Slide 124
Slide 124 text
Let's add a selection criteria:
Copyright @ 2022 Oracle and/or its affiliates.
87
Slide 125
Slide 125 text
Syntax slightly di erent than MongoDB
Copyright @ 2022 Oracle and/or its affiliates.
88
Slide 126
Slide 126 text
Syntax slightly di erent than MongoDB
Copyright @ 2022 Oracle and/or its affiliates.
88
Slide 127
Slide 127 text
And for developers ?
Copyright @ 2022 Oracle and/or its affiliates.
89
Slide 128
Slide 128 text
And for developers ?
$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]}";
}
Copyright @ 2022 Oracle and/or its affiliates.
90
Slide 129
Slide 129 text
And for developers ?
$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]}";
}
Easy, using only CRUD operations !
Copyright @ 2022 Oracle and/or its affiliates.
90
Slide 130
Slide 130 text
And for developers ?
$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]}";
}
Easy, using only CRUD operations !
Not a single SQL statement !
Copyright @ 2022 Oracle and/or its affiliates.
90
Slide 131
Slide 131 text
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(
()
);
;
}
}
}
}
Copyright @ 2022 Oracle and/or its affiliates.
com
com.
.mysql
mysql.
.cj
cj.
.xdevapi
xdevapi.
.
91
Slide 132
Slide 132 text
CRUD operations
Copyright @ 2022 Oracle and/or its affiliates.
92
Slide 133
Slide 133 text
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()
Copyright @ 2022 Oracle and/or its affiliates.
93
Slide 134
Slide 134 text
CRUD operations for collections
Modify a document
collection.modify('name = :name')
.bind('name', 'fred')
.set('age', 43)
.sort('name ASC')
.limit(1)
.execute()
collection.modify('name = :name')
.bind('name', 'fred')
.patch({ age: 43, active: false })
.sort('name DESC')
.limit(1)
.execute()
Copyright @ 2022 Oracle and/or its affiliates.
94
Slide 135
Slide 135 text
CRUD operations for collections
Remove a document
collection.remove('name = :name')
.bind('name', 'fred')
.sort('age ASC')
.limit(1)
.execute()
Copyright @ 2022 Oracle and/or its affiliates.
95
Slide 136
Slide 136 text
MySQL Document Store Objects Summary
Copyright @ 2022 Oracle and/or its affiliates.
96
Slide 137
Slide 137 text
All you need to know is here:
h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html
Copyright @ 2022 Oracle and/or its affiliates.
97
Slide 138
Slide 138 text
MySQL Document Store
is Full ACID Compliant
we do care about your data
Copyright @ 2022 Oracle and/or its affiliates.
98
Slide 139
Slide 139 text
Document Store Full ACID !
It relies on the proven MySQL InnoDB's strength & robustness:
Copyright @ 2022 Oracle and/or its affiliates.
99
Slide 140
Slide 140 text
Document Store Full ACID !
It relies on the proven MySQL InnoDB's strength & robustness:
innodb_flush_log_at_trx_commit = 1
Copyright @ 2022 Oracle and/or its affiliates.
99
Slide 141
Slide 141 text
Document Store Full ACID !
It relies on the proven MySQL InnoDB's strength & robustness:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = ON
Copyright @ 2022 Oracle and/or its affiliates.
99
Slide 142
Slide 142 text
Document Store Full ACID !
It relies on the proven MySQL InnoDB's strength & robustness:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = ON
sync_binlog = 1
Copyright @ 2022 Oracle and/or its affiliates.
99
Slide 143
Slide 143 text
Document Store Full ACID !
It relies on the proven MySQL InnoDB's strength & robustness:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = ON
sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
Copyright @ 2022 Oracle and/or its affiliates.
99
Slide 144
Slide 144 text
Document Store Full ACID !
It relies on the proven MySQL InnoDB's strength & robustness:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = ON
sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
We do care about your data !
Copyright @ 2022 Oracle and/or its affiliates.
99
Slide 145
Slide 145 text
Full ACID - Transactions support
Copyright @ 2022 Oracle and/or its affiliates.
100
Slide 146
Slide 146 text
Full ACID - Transactions support
Copyright @ 2022 Oracle and/or its affiliates.
101
Slide 147
Slide 147 text
OK we have Document Store,
CRUD and ACID
but what makes MySQL Document Store unique ?
Copyright @ 2022 Oracle and/or its affiliates.
102
Slide 148
Slide 148 text
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
Copyright @ 2022 Oracle and/or its affiliates.
103
Slide 149
Slide 149 text
NoSQL as SQL - aggregation
Copyright @ 2022 Oracle and/or its affiliates.
104
Slide 150
Slide 150 text
NoSQL as SQL - aggregation
Copyright @ 2022 Oracle and/or its affiliates.
105
Slide 151
Slide 151 text
NoSQL as SQL - aggregation
Copyright @ 2022 Oracle and/or its affiliates.
106
Slide 152
Slide 152 text
NoSQL as SQL - aggregation
Copyright @ 2022 Oracle and/or its affiliates.
107
Slide 153
Slide 153 text
NoSQL or SQL
You have the possibility to write clean and neat code:
Copyright @ 2022 Oracle and/or its affiliates.
108
Slide 154
Slide 154 text
NoSQL or SQL
You have the possibility to write clean and neat code:
$results = $collection->find('cuisine like "italian"')->execute()->fetchAll();
Copyright @ 2022 Oracle and/or its affiliates.
108
Slide 155
Slide 155 text
NoSQL or SQL
You have the possibility to write clean and neat code:
$results = $collection->find('cuisine like "italian"')->execute()->fetchAll();
Or use SQL only when it's really needed:
Copyright @ 2022 Oracle and/or its affiliates.
108
Slide 156
Slide 156 text
NoSQL or SQL
You have the possibility to write clean and neat code:
$results = $collection->find('cuisine like "italian"')->execute()->fetchAll();
Or use SQL only when it's really needed:
$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();
Copyright @ 2022 Oracle and/or its affiliates.
108
Slide 157
Slide 157 text
NoSQL or SQL
You have the possibility to write clean and neat code:
$results = $collection->find('cuisine like "italian"')->execute()->fetchAll();
Or use SQL only when it's really needed:
$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 !
Copyright @ 2022 Oracle and/or its affiliates.
108
Slide 158
Slide 158 text
You can mix NoSQL & SQL as you want
Copyright @ 2022 Oracle and/or its affiliates.
109
Slide 159
Slide 159 text
Best of Both Worlds: JSON_TABLE
What are the maximum 10 ratings ever given to a restaurant?
Copyright @ 2022 Oracle and/or its affiliates.
110
Slide 160
Slide 160 text
Best of Both Worlds: JSON_TABLE
What are the maximum 10 ratings ever given to a restaurant?
Cool... but my app only processes JSON !
Copyright @ 2022 Oracle and/or its affiliates.
110
Slide 161
Slide 161 text
Best of Both Worlds: JSON_TABLE (2)
With JSON output:
Copyright @ 2022 Oracle and/or its affiliates.
111
Slide 162
Slide 162 text
CHECK CONSTRAINTS
We already saw that MySQL 8.0 supports Check Constraints:
Copyright @ 2022 Oracle and/or its affiliates.
112
Slide 163
Slide 163 text
JSON Schema Validation
Copyright @ 2022 Oracle and/or its affiliates.
113
Slide 164
Slide 164 text
JSON Schema Validation (2)
Copyright @ 2022 Oracle and/or its affiliates.
114
Slide 165
Slide 165 text
JSON Schema Validation (3)
And the best of both worlds:
Copyright @ 2022 Oracle and/or its affiliates.
115
Slide 166
Slide 166 text
JSON Schema Validation (3)
And the best of both worlds:
And the result in action:
Copyright @ 2022 Oracle and/or its affiliates.
115
Slide 167
Slide 167 text
JSON Schema Validation (3)
And the best of both worlds:
And the result in action:
Copyright @ 2022 Oracle and/or its affiliates.
115
Slide 168
Slide 168 text
Conclusion
what do I gain ?
Copyright @ 2022 Oracle and/or its affiliates.
116
Slide 169
Slide 169 text
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
Copyright @ 2022 Oracle and/or its affiliates.
117
Slide 170
Slide 170 text
Migrating to Connector/J 8.0
some info for the Java developers
Copyright @ 2022 Oracle and/or its affiliates.
118
Slide 171
Slide 171 text
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.
Copyright @ 2022 Oracle and/or its affiliates.
119
Slide 172
Slide 172 text
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(
()
);
;
Copyright @ 2022 Oracle and/or its affiliates.
120
Slide 173
Slide 173 text
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(
()
);
;
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.
Copyright @ 2022 Oracle and/or its affiliates.
120
Slide 174
Slide 174 text
Migration from Connector/J 5.1 to 8.0 (3)
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)
);
;
Copyright @ 2022 Oracle and/or its affiliates.
121
Slide 175
Slide 175 text
Migration from Connector/J 5.1 to 8.0 (3)
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.
Copyright @ 2022 Oracle and/or its affiliates.
121
Slide 176
Slide 176 text
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.
Migration from Connector/J 5.1 to 8.0 (4)
Copyright @ 2022 Oracle and/or its affiliates.
122
Slide 177
Slide 177 text
Migration from Connector/J 5.1 to 8.0 (5)
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.
Copyright @ 2022 Oracle and/or its affiliates.
123
Slide 178
Slide 178 text
Migration from Connector/J 5.1 to 8.0 (5)
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.
Copyright @ 2022 Oracle and/or its affiliates.
124
Slide 179
Slide 179 text
Migration from Connector/J 5.1 to 8.0 (6)
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/
Copyright @ 2022 Oracle and/or its affiliates.
125
Slide 180
Slide 180 text
MySQL 8.0 DBA Certi cation
Copyright @ 2022 Oracle and/or its affiliates.
126
Slide 181
Slide 181 text
MySQL 8.0 Developer Certi cation
Copyright @ 2022 Oracle and/or its affiliates.
127
Slide 182
Slide 182 text
Thank you !
Copyright @ 2022 Oracle and/or its affiliates.
128