Slide 1
Slide 1 text
Frédéric Descamps
Community Manager
Oracle MySQL
CZJUG - April 2024
Discover the MySQL Document
Store
MySQL for Developers
Slide 2
Slide 2 text
Who am I ?
about.me/lefred
Copyright @ 2024 Oracle and/or its affiliates.
2
Slide 3
Slide 3 text
@lefred
MySQL Evangelist
using MySQL since version 3.20
devops believer
likes
living in
h ps://lefred.be
Frédéric Descamps
Copyright @ 2024 Oracle and/or its affiliates.
3
Slide 4
Slide 4 text
MySQL 8.0 Document Store
discovery of a new world
Copyright @ 2024 Oracle and/or its affiliates.
4
Slide 5
Slide 5 text
NoSQL Document Store
Schemaless
Copyright @ 2024 Oracle and/or its affiliates.
5
Slide 6
Slide 6 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
Copyright @ 2024 Oracle and/or its affiliates.
5
Slide 7
Slide 7 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Copyright @ 2024 Oracle and/or its affiliates.
5
Slide 8
Slide 8 text
NoSQL Document Store
Schemaless
no schema design, no normalization, no foreign keys, no data types, ...
very quick initial development
Flexible data structure
Copyright @ 2024 Oracle and/or its affiliates.
5
Slide 9
Slide 9 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 @ 2024 Oracle and/or its affiliates.
5
Slide 10
Slide 10 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 @ 2024 Oracle and/or its affiliates.
5
Slide 11
Slide 11 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 @ 2024 Oracle and/or its affiliates.
5
Slide 12
Slide 12 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 @ 2024 Oracle and/or its affiliates.
5
Slide 13
Slide 13 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 @ 2024 Oracle and/or its affiliates.
5
Slide 14
Slide 14 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 @ 2024 Oracle and/or its affiliates.
5
Slide 15
Slide 15 text
How DBAs see data
Copyright @ 2024 Oracle and/or its affiliates.
6
Slide 16
Slide 16 text
How DBAs see data How Developers see data
{
{
"GNP"
"GNP" :
: 249704
249704,
,
"Name"
"Name" :
: "Belgium"
"Belgium",
,
"government"
"government" :
: {
{
"GovernmentForm"
"GovernmentForm" :
:
"Constitutional Monarchy, Federation"
"Constitutional Monarchy, Federation",
,
"HeadOfState"
"HeadOfState" :
: "Philippe I"
"Philippe I"
}
},
,
"_id"
"_id" :
: "BEL"
"BEL",
,
"IndepYear"
"IndepYear" :
: 1830
1830,
,
"demographics"
"demographics" :
: {
{
"Population"
"Population" :
: 10239000
10239000,
,
"LifeExpectancy"
"LifeExpectancy" :
: 77.8000030517578
77.8000030517578
}
},
,
}
}
Copyright @ 2024 Oracle and/or its affiliates.
6
Slide 17
Slide 17 text
And they still need to do Analytics
SQL
SQL
SQL
Copyright @ 2024 Oracle and/or its affiliates.
7
Slide 18
Slide 18 text
... mmm but...how ?
?
SQL
SQL
SQL
Copyright @ 2024 Oracle and/or its affiliates.
8
Slide 19
Slide 19 text
Migrating the data (ETL)
SQL
SQL
SQL
ETL: Extraction-Transformation-Load
Copyright @ 2024 Oracle and/or its affiliates.
9
Slide 20
Slide 20 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 @ 2024 Oracle and/or its affiliates.
10
Slide 21
Slide 21 text
RDBMS or NoSQL ?
Copyright @ 2024 Oracle and/or its affiliates.
11
Slide 22
Slide 22 text
RDBMS or NoSQL ?
Why not both ?
Copyright @ 2024 Oracle and/or its affiliates.
11
Slide 23
Slide 23 text
The MySQL Document Store !
Where SQL is now optional !
Copyright @ 2024 Oracle and/or its affiliates.
12
Slide 24
Slide 24 text
Is SQL now optional ?!
Copyright @ 2024 Oracle and/or its affiliates.
13
Slide 25
Slide 25 text
Utsing MySQL Document Store !
SQL
SQL
SQL
Copyright @ 2024 Oracle and/or its affiliates.
14
Slide 26
Slide 26 text
The Solution
MySQL Document Store
Copyright @ 2024 Oracle and/or its affiliates.
15
Slide 27
Slide 27 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
Compatible with all existing MySQL solutions
Copyright @ 2024 Oracle and/or its affiliates.
16
Slide 28
Slide 28 text
Table Columns*
_id
varbinary(32)
doc
JSON
_json_schema
JSON
* others if you add indexes
Schema
Schema - database
Collection
Table
Document
Row in table
a Record
Anatomy of MySQL Document Store
Copyright @ 2024 Oracle and/or its affiliates.
17
Slide 29
Slide 29 text
X Protocol Connectors
Copyright @ 2024 Oracle and/or its affiliates.
18
Slide 30
Slide 30 text
Installation & Setup
MySQL Document Store
Copyright @ 2024 Oracle and/or its affiliates.
19
Slide 31
Slide 31 text
Installing MySQL Document Store
install MySQL 8.0
Copyright @ 2024 Oracle and/or its affiliates.
20
Slide 32
Slide 32 text
Installing MySQL Document Store
install MySQL 8.0
install MySQL Shell
Copyright @ 2024 Oracle and/or its affiliates.
20
Slide 33
Slide 33 text
Installing MySQL Document Store
install MySQL 8.0
install MySQL Shell
install MySQL Connector for your programming language
Copyright @ 2024 Oracle and/or its affiliates.
20
Slide 34
Slide 34 text
Installing MySQL Document Store
install MySQL 8.0
install MySQL Shell
install MySQL Connector for your programming language
Connector/J for Java
php-pecl-mysql-xdevapi for PHP
mysql-connector-python for Python, ...
Copyright @ 2024 Oracle and/or its affiliates.
20
Slide 35
Slide 35 text
Installing MySQL Document Store
install MySQL 8.0
install MySQL Shell
install MySQL Connector for your programming language
Connector/J for Java
php-pecl-mysql-xdevapi for PHP
mysql-connector-python for Python, ...
Simply ensure that your rewall se ings permit connections via port 33060 (X Protocol); no
additional installations or plugins are required.
Copyright @ 2024 Oracle and/or its affiliates.
20
Slide 36
Slide 36 text
MySQL HeatWave Database Service
MySQL HeatWave Database Service on OCI is the only MySQL DBaaS including X Protocol.
Copyright @ 2024 Oracle and/or its affiliates.
21
Slide 37
Slide 37 text
Migration from MongoDB to MySQL DS
For this example, I am using the well known restaurants collection:
$ mongoexport
$ mongoexport -c
-c restaurants
restaurants >
> restaurants.json
restaurants.json
connected to:
connected to: 127.0
127.0.0.1
.0.1
exported
exported 25359
25359 records
records
Copyright @ 2024 Oracle and/or its affiliates.
22
Slide 38
Slide 38 text
Migration from MongoDB to MySQL DS
For this example, I am using the well known restaurants collection:
$ mongoexport
$ mongoexport -c
-c restaurants
restaurants >
> restaurants.json
restaurants.json
connected to:
connected to: 127.0
127.0.0.1
.0.1
exported
exported 25359
25359 records
records
JS
JS>
> util
util.
.importJson
importJson(
('restaurants.json'
'restaurants.json',
,{
{convertBsonOid
convertBsonOid:
: true
true}
})
)
Importing from file
Importing from file "restaurants.json"
"restaurants.json" to collection
to collection `
`docstore
docstore`
`.
.`
`restaurants
restaurants`
`
in
in MySQL Server at localhost
MySQL Server at localhost:
:33060
33060
.
..
. 25359.
25359..
. 25359
25359
Processed
Processed 15.60
15.60 MB
MB in
in 25359
25359 documents
documents in
in 0.9976
0.9976 sec
sec (
(25
25.
.36K documents
36K documents/
/s
s)
)
Total successfully imported documents
Total successfully imported documents 25359
25359 (
(25
25.
.36K documents
36K documents/
/s
s)
)
Copyright @ 2024 Oracle and/or its affiliates.
22
Slide 39
Slide 39 text
Copyright @ 2024 Oracle and/or its affiliates.
23
Slide 40
Slide 40 text
Copyright @ 2024 Oracle and/or its affiliates.
24
Slide 41
Slide 41 text
Copyright @ 2024 Oracle and/or its affiliates.
25
Slide 42
Slide 42 text
Copyright @ 2024 Oracle and/or its affiliates.
26
Slide 43
Slide 43 text
Let's make a query
JS > restaurants.find()
That's too much records to show in here... let's limit it
Copyright @ 2024 Oracle and/or its affiliates.
27
Slide 44
Slide 44 text
Copyright @ 2024 Oracle and/or its affiliates.
28
Slide 45
Slide 45 text
Another example
Copyright @ 2024 Oracle and/or its affiliates.
29
Slide 46
Slide 46 text
With a selection lter:
Copyright @ 2024 Oracle and/or its affiliates.
30
Slide 47
Slide 47 text
With a selection lter (2):
Copyright @ 2024 Oracle and/or its affiliates.
31
Slide 48
Slide 48 text
Syntax slightly di erent than MongoDB
Copyright @ 2024 Oracle and/or its affiliates.
32
Slide 49
Slide 49 text
Syntax slightly di erent than MongoDB
Copyright @ 2024 Oracle and/or its affiliates.
32
Slide 50
Slide 50 text
And for developers ?
Copyright @ 2024 Oracle and/or its affiliates.
33
Slide 51
Slide 51 text
And for developers ?
$session
$session =
= mysql_xdevapi
mysql_xdevapi\
\getSession
getSession(
("mysqlx://fred:MyP@ssw0rd%@localhost"
"mysqlx://fred:MyP@ssw0rd%@localhost")
);
;
$schema
$schema =
= $session
$session->
->getSchema
getSchema(
("docstore"
"docstore")
);
;
$collection
$collection =
= $schema
$schema->
->getCollection
getCollection(
("restaurants"
"restaurants")
);
;
$results
$results =
= $collection
$collection->
->find
find(
($search
$search)
)->
->execute
execute(
()
)->
->fetchAll
fetchAll(
()
);
;
...
...
foreach
foreach (
($results
$results as
as $doc
$doc)
) {
{
echo
echo "${doc[name]}"
"${doc[name]}";
;
echo
echo "${doc[borough]}${doc[cuisine]}"
"${doc[borough]}${doc[cuisine]}";
;
}
}
Copyright @ 2024 Oracle and/or its affiliates.
34
Slide 52
Slide 52 text
And for developers ?
$session
$session =
= mysql_xdevapi
mysql_xdevapi\
\getSession
getSession(
("mysqlx://fred:MyP@ssw0rd%@localhost"
"mysqlx://fred:MyP@ssw0rd%@localhost")
);
;
$schema
$schema =
= $session
$session->
->getSchema
getSchema(
("docstore"
"docstore")
);
;
$collection
$collection =
= $schema
$schema->
->getCollection
getCollection(
("restaurants"
"restaurants")
);
;
$results
$results =
= $collection
$collection->
->find
find(
($search
$search)
)->
->execute
execute(
()
)->
->fetchAll
fetchAll(
()
);
;
...
...
foreach
foreach (
($results
$results as
as $doc
$doc)
) {
{
echo
echo "${doc[name]}"
"${doc[name]}";
;
echo
echo "${doc[borough]}${doc[cuisine]}"
"${doc[borough]}${doc[cuisine]}";
;
}
}
Easy, using only CRUD operations !
Copyright @ 2024 Oracle and/or its affiliates.
34
Slide 53
Slide 53 text
And for developers ?
$session
$session =
= mysql_xdevapi
mysql_xdevapi\
\getSession
getSession(
("mysqlx://fred:MyP@ssw0rd%@localhost"
"mysqlx://fred:MyP@ssw0rd%@localhost")
);
;
$schema
$schema =
= $session
$session->
->getSchema
getSchema(
("docstore"
"docstore")
);
;
$collection
$collection =
= $schema
$schema->
->getCollection
getCollection(
("restaurants"
"restaurants")
);
;
$results
$results =
= $collection
$collection->
->find
find(
($search
$search)
)->
->execute
execute(
()
)->
->fetchAll
fetchAll(
()
);
;
...
...
foreach
foreach (
($results
$results as
as $doc
$doc)
) {
{
echo
echo "${doc[name]}"
"${doc[name]}";
;
echo
echo "${doc[borough]}${doc[cuisine]}"
"${doc[borough]}${doc[cuisine]}";
;
}
}
Easy, using only CRUD operations !
Not a single SQL statement !
Copyright @ 2024 Oracle and/or its affiliates.
34
Slide 54
Slide 54 text
With
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 @ 2024 Oracle and/or its affiliates.
com
com.
.mysql
mysql.
.cj
cj.
.xdevapi
xdevapi.
.
35
Slide 55
Slide 55 text
Li le Demo
$ javac
$ javac -classpath
-classpath "lib/*"
"lib/*" -d
-d bin src/Resto.java
bin src/Resto.java
$
$ java
java -classpath
-classpath "bin:lib/*"
"bin:lib/*" Resto
Resto
Connecting to database
Connecting to database..
...
.
Enter a string to search
Enter a string to search in
in the database: fred
the database: fred
Searching
Searching for
for 'fred'
'fred' in
in the database
the database..
...
.
Name:
Name: |
| Borough:
Borough:
Cafe
Cafe 212
212/Columbia Catering Kitchen - Alfred Lerner Hall
/Columbia Catering Kitchen - Alfred Lerner Hall |
| Manhattan
Manhattan
Ferris Booth Commons - Alfred Lerner Hall
Ferris Booth Commons - Alfred Lerner Hall |
| Manhattan
Manhattan
Cafe East - Alfred Lerner Hall
Cafe East - Alfred Lerner Hall |
| Manhattan
Manhattan
Alfredo
Alfredo 100
100 |
| Manhattan
Manhattan
Copyright @ 2024 Oracle and/or its affiliates.
36
Slide 56
Slide 56 text
CRUD operations
The computer acronym CRUD (for Create, Read, Update, Delete) designates the four basic operations for data
persistence, in particular the storage of information in a database.
Copyright @ 2024 Oracle and/or its affiliates.
37
Slide 57
Slide 57 text
CRUD operations on collections
Add a document
collection
collection.
.add
add(
({
{ name
name:
: 'fred'
'fred',
, age
age:
: 46
46 }
})
)
.
.add
add(
({
{ name
name:
: 'scott'
'scott',
, age
age:
: 47
47 }
})
)
.
.execute
execute(
()
)
collection
collection.
.add
add(
([
[
{
{ name
name:
: 'dimo'
'dimo',
, age
age:
: 50
50 }
},
,
{
{ name
name:
: 'kenny'
'kenny',
, age
age:
: 25
25 }
}
]
])
).
.execute
execute(
()
)
Copyright @ 2024 Oracle and/or its affiliates.
38
Slide 58
Slide 58 text
collection
collection.
.modify
modify(
('name = :name'
'name = :name')
)
.
.bind
bind(
('name'
'name',
, 'fred'
'fred')
)
.
.set
set(
('age'
'age',
, 43
43)
)
.
.sort
sort(
('name ASC'
'name ASC')
)
.
.limit
limit(
(1
1)
)
.
.execute
execute(
()
)
collection
collection.
.modify
modify(
('name = :name'
'name = :name')
)
.
.bind
bind(
('name'
'name',
, 'fred'
'fred')
)
.
.patch
patch(
({
{ age
age:
: 43
43,
, active
active:
: false
false }
})
)
.
.sort
sort(
('name DESC'
'name DESC')
)
.
.limit
limit(
(1
1)
)
.
.execute
execute(
()
)
CRUD operations on collections
Modify a document
Copyright @ 2024 Oracle and/or its affiliates.
39
Slide 59
Slide 59 text
CRUD operations on collections
Delete of a document
collection
collection.
.remove
remove(
('name = :name'
'name = :name')
)
.
.bind
bind(
('name'
'name',
, 'fred'
'fred')
)
.
.sort
sort(
('age ASC'
'age ASC')
)
.
.limit
limit(
(1
1)
)
.
.execute
execute(
()
)
Copyright @ 2024 Oracle and/or its affiliates.
40
Slide 60
Slide 60 text
MySQL Document Store Objects Summary
Copyright @ 2024 Oracle and/or its affiliates.
41
Slide 61
Slide 61 text
All you need to know about the X Dev API is here:
h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html
Copyright @ 2024 Oracle and/or its affiliates.
42
Slide 62
Slide 62 text
MySQL Document Store
is full ACID compliant
we do care about your data
Copyright @ 2024 Oracle and/or its affiliates.
43
Slide 63
Slide 63 text
Document Store Full ACID !
The solution relies on the proven MySQL InnoDB strength & robustness:
Copyright @ 2024 Oracle and/or its affiliates.
44
Slide 64
Slide 64 text
Document Store Full ACID !
The solution relies on the proven MySQL InnoDB strength & robustness:
innodb_ ush_log_at_trx_commit = 1
Copyright @ 2024 Oracle and/or its affiliates.
44
Slide 65
Slide 65 text
Document Store Full ACID !
The solution relies on the proven MySQL InnoDB strength & robustness:
innodb_ ush_log_at_trx_commit = 1
innodb_doublewrite = ON
Copyright @ 2024 Oracle and/or its affiliates.
44
Slide 66
Slide 66 text
Document Store Full ACID !
The solution relies on the proven MySQL InnoDB strength & robustness:
innodb_ ush_log_at_trx_commit = 1
innodb_doublewrite = ON
sync_binlog = 1
Copyright @ 2024 Oracle and/or its affiliates.
44
Slide 67
Slide 67 text
Document Store Full ACID !
The solution relies on the proven MySQL InnoDB strength & robustness:
innodb_ ush_log_at_trx_commit = 1
innodb_doublewrite = ON
sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
Copyright @ 2024 Oracle and/or its affiliates.
44
Slide 68
Slide 68 text
Document Store Full ACID !
The solution relies on the proven MySQL InnoDB strength & robustness:
innodb_ ush_log_at_trx_commit = 1
innodb_doublewrite = ON
sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
Durability is important.
Copyright @ 2024 Oracle and/or its affiliates.
44
Slide 69
Slide 69 text
Document Store Full ACID !
The solution relies on the proven MySQL InnoDB strength & robustness:
innodb_ ush_log_at_trx_commit = 1
innodb_doublewrite = ON
sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
Durability is important.
We do care about your data !
Copyright @ 2024 Oracle and/or its affiliates.
44
Slide 70
Slide 70 text
ACID - Support of Transactions
Copyright @ 2024 Oracle and/or its affiliates.
45
Slide 71
Slide 71 text
ACID - Support of Transactions
Copyright @ 2024 Oracle and/or its affiliates.
46
Slide 72
Slide 72 text
OK we have Doc Store, CRUD & ACID
but what makes MySQL Document Store unique?
Copyright @ 2024 Oracle and/or its affiliates.
47
Slide 73
Slide 73 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 @ 2024 Oracle and/or its affiliates.
48
Slide 74
Slide 74 text
Copyright @ 2024 Oracle and/or its affiliates.
49
Slide 75
Slide 75 text
NoSQL as SQL - aggregation
Copyright @ 2024 Oracle and/or its affiliates.
50
Slide 76
Slide 76 text
NoSQL as SQL - aggregation
Copyright @ 2024 Oracle and/or its affiliates.
51
Slide 77
Slide 77 text
NoSQL as SQL - aggregation
Copyright @ 2024 Oracle and/or its affiliates.
52
Slide 78
Slide 78 text
NoSQL as SQL - aggregation
Copyright @ 2024 Oracle and/or its affiliates.
53
Slide 79
Slide 79 text
NoSQL or SQL
You have the possibility to write clean and neat code:
Copyright @ 2024 Oracle and/or its affiliates.
54
Slide 80
Slide 80 text
NoSQL or SQL
You have the possibility to write clean and neat code:
$results
$results =
= $collection
$collection->
->find
find(
('cuisine like "italian"'
'cuisine like "italian"')
)->
->execute
execute(
()
)->
->fetchAll
fetchAll(
()
);
;
Copyright @ 2024 Oracle and/or its affiliates.
54
Slide 81
Slide 81 text
NoSQL or SQL
You have the possibility to write clean and neat code:
$results
$results =
= $collection
$collection->
->find
find(
('cuisine like "italian"'
'cuisine like "italian"')
)->
->execute
execute(
()
)->
->fetchAll
fetchAll(
()
);
;
And use SQL only when it's necessary:
Copyright @ 2024 Oracle and/or its affiliates.
54
Slide 82
Slide 82 text
NoSQL or SQL
You have the possibility to write clean and neat code:
$results
$results =
= $collection
$collection->
->find
find(
('cuisine like "italian"'
'cuisine like "italian"')
)->
->execute
execute(
()
)->
->fetchAll
fetchAll(
()
);
;
And use SQL only when it's necessary:
$results
$results =
= $session
$session->
->sql
sql(
(' select * from (with cte1 as (select doc->>"$.name" as name,
' select * from (with cte1 as (select doc->>"$.name" as name,
doc->>"$.cuisine" as cuisine, ( select avg(score) from json_table(doc, "$.grades[*]"
doc->>"$.cuisine" as cuisine, ( select avg(score) from json_table(doc, "$.grades[*]"
columns (score int path "$.score")) as r) as avg_score from restaurants)
columns (score int path "$.score")) as r) as avg_score from restaurants)
select *, row_number() over ( partition by cuisine order by avg_score desc) as `rank`
select *, row_number() over ( partition by cuisine order by avg_score desc) as `rank`
from cte1 order by `rank`, avg_score desc) b where `rank`=1'
from cte1 order by `rank`, avg_score desc) b where `rank`=1')
)->
->execute
execute(
()
);
;
Copyright @ 2024 Oracle and/or its affiliates.
54
Slide 83
Slide 83 text
All in the same MySQL X Session !
Copyright @ 2024 Oracle and/or its affiliates.
55
Slide 84
Slide 84 text
You can mix NoSQL & SQL as you want:
Copyright @ 2024 Oracle and/or its affiliates.
56
Slide 85
Slide 85 text
Best of Both Worlds: JSON_TABLE
What are the maximum 10 ratings ever given to a restaurant?
Copyright @ 2024 Oracle and/or its affiliates.
57
Slide 86
Slide 86 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 @ 2024 Oracle and/or its affiliates.
57
Slide 87
Slide 87 text
Best of Both Worlds: JSON_TABLE (2)
With JSON output:
Copyright @ 2024 Oracle and/or its affiliates.
58
Slide 88
Slide 88 text
Best of Both Worlds: JSON_TABLE (3)
Remember the challenge ?
Copyright @ 2024 Oracle and/or its affiliates.
59
Slide 89
Slide 89 text
Best of Both Worlds: JSON_TABLE (3)
Remember the challenge ?
Copyright @ 2024 Oracle and/or its affiliates.
59
Slide 90
Slide 90 text
Going further !
Best of both worlds: mixing constraints and JSON validation:
Copyright @ 2024 Oracle and/or its affiliates.
60
Slide 91
Slide 91 text
Going further !
Best of both worlds: mixing constraints and JSON validation:
Result in action:
Copyright @ 2024 Oracle and/or its affiliates.
60
Slide 92
Slide 92 text
JSON Validation: best practice
It's nicer to validate the JSON at the collection level:
Copyright @ 2024 Oracle and/or its affiliates.
61
Slide 93
Slide 93 text
JSON Validation: best practice
Copyright @ 2024 Oracle and/or its affiliates.
62
Slide 94
Slide 94 text
JSON Validation: best practice
And now we see an usage's illustration of _json_schema:
Copyright @ 2024 Oracle and/or its affiliates.
62
Slide 95
Slide 95 text
Another Example with validation
Copyright @ 2024 Oracle and/or its affiliates.
63
Slide 96
Slide 96 text
Another Example with validation (2)
Copyright @ 2024 Oracle and/or its affiliates.
64
Slide 97
Slide 97 text
Another Example with validation (2)
Copyright @ 2024 Oracle and/or its affiliates.
65
Slide 98
Slide 98 text
Acceleration with MySQL HeatWave
MySQL HeatWave Database Service supports the Dev X API allowing the X Protocol, but if
you want to also bene t from HeatWave Cluster, query accelerator, you need to make a
change to your collection:
MySQL
MySQL >
> ALTER
ALTER TABLE
TABLE restaurants
restaurants MODIFY
MODIFY _id
_id CHAR
CHAR(
(28
28)
) CHARSET
CHARSET latin1
latin1
GENERATED ALWAYS
GENERATED ALWAYS AS
AS
(
(JSON_UNQUOTE
JSON_UNQUOTE(
(JSON_EXTRACT
JSON_EXTRACT(
(`
`doc
doc`
`,
,_utf8mb4
_utf8mb4'$._id'
'$._id')
))
))
) STORED
STORED NOT
NOT NULL
NULL;
;
Copyright @ 2024 Oracle and/or its affiliates.
66
Slide 99
Slide 99 text
Acceleration with MySQL HeatWave (2)
Let's have a look at our collection:
JS
JS >
> db
db.
.restaurants
restaurants.
.count
count(
()
)
2821923
2821923
We have 2,821,923 documents !
Copyright @ 2024 Oracle and/or its affiliates.
67
Slide 100
Slide 100 text
Acceleration with MySQL HeatWave (3)
We can now load the collection to HeatWave Cluster:
MySQL
MySQL >
> CALL
CALL sys
sys.
.heatwave_load
heatwave_load(
(JSON_ARRAY
JSON_ARRAY(
('docstore'
'docstore')
),
, NULL
NULL)
);
;
.
..
..
.
+
+-------------------------------------------------------------------------------+
-------------------------------------------------------------------------------+
|
| LOAD
LOAD SUMMARY
SUMMARY |
|
+
+-------------------------------------------------------------------------------+
-------------------------------------------------------------------------------+
|
| |
|
|
| SCHEMA
SCHEMA TABLES
TABLES TABLES
TABLES COLUMNS
COLUMNS LOAD
LOAD |
|
|
| NAME LOADED FAILED LOADED DURATION
NAME LOADED FAILED LOADED DURATION |
|
|
| ------ ------ ------ ------- -------- |
------ ------ ------ ------- -------- |
|
| `
`docstore
docstore`
` 1
1 0
0 2
2 11.41
11.41 s
s |
|
|
| |
|
+
+-------------------------------------------------------------------------------+
-------------------------------------------------------------------------------+
Copyright @ 2024 Oracle and/or its affiliates.
68
Slide 101
Slide 101 text
For example with 2,162,520 documents we go from 1.1713 seconds to
0.1922 seconds when using HeatWeave Cluster.
Copyright @ 2024 Oracle and/or its affiliates.
69
Slide 102
Slide 102 text
MySQL Document Store is compatible with all
MySQL Architectures
Copyright @ 2024 Oracle and/or its affiliates.
70
Slide 103
Slide 103 text
Conclusion
what do I gain ?
Copyright @ 2024 Oracle and/or its affiliates.
71
Slide 104
Slide 104 text
RDMBS
Data integrity
ACID Compliant
Transactions
SQL
Conclusion
This is the best of the two worlds in one product !
Copyright @ 2024 Oracle and/or its affiliates.
72
Slide 105
Slide 105 text
RDMBS
Data integrity
ACID Compliant
Transactions
SQL
NoSQL
schemaless
exible data structure
easy to start (CRUD)
Conclusion
This is the best of the two worlds in one product !
Copyright @ 2024 Oracle and/or its affiliates.
72
Slide 106
Slide 106 text
NoSQL + SQL = MySQL
Copyright @ 2024 Oracle and/or its affiliates.
73
Slide 107
Slide 107 text
NoSQL + SQL = MySQL
Copyright @ 2024 Oracle and/or its affiliates.
73
Slide 108
Slide 108 text
Share your
❤ to MySQL
#mysql #MySQLCommunity
Join our slack channel!
bit.ly/mysql-slack
Copyright @ 2024 Oracle and/or its affiliates.
74
Slide 109
Slide 109 text
Questions ?
Copyright @ 2024 Oracle and/or its affiliates.
75