NoSQL + SQL = MySQLMySQL‫ْٖ؞غ‬٤‫ؓعتع‬Best of the both world唟㿊 ꥐ鰊 / KAJIYAMA, Ryusuke

MySQL Solution Engineering Director, Asia Pacific & JapanMySQL Global Business UnitSafe harbor statement⟃♴‫✲ך‬갪‫כ‬ծ䒦爡‫♧ך‬菙涸‫ז‬醡ㅷ‫ך‬倯ぢ䚍‫ח‬ꟼ‫׷ׅ‬嚊銲‫׾‬铡僇‫ׅדך׮׷ׅ‬կ‫׋ת‬ծ䞔㜠䲿⣘‫׾‬㈓♧‫ך‬湡涸‫׶֮דך׮׷ׅה‬ծְַ‫׷ז‬㤍秈‫׮ח‬穈‫׫‬鴥‫׿ׇתֹדכהֿ׬‬կ⟃♴‫✲ך‬갪‫כ‬ծ‫ٔذو‬،ٕ‫؝װ‬٦‫س‬ծ堣腉‫׾‬䲿⣘‫ًزحى؝׾הֿ׷ׅ‬ٝ‫ز‬然秈‫׭׋ְזכדך׮׷ׅ‬ծ飑顠寸㹀‫׾‬遤ֲꥷ‫ⴻך‬倖勞俱‫ְׁ♴דְז׵ׁזח‬կؔٓ‫ؙٕ‬醡ㅷ‫ח‬ꟼ‫ג׃‬鎸鯹ׁ‫׷ְג׸‬堣腉‫ך‬Ꟛ涪ծٔٔ٦‫ן״ֶأ‬儗劍‫כגְאח‬ծ䒦爡‫ך‬鄩ꆀ‫״ח‬‫׶‬寸㹀ׁ‫ׅת׸‬կ
0SBDMF‫ה‬+BWB‫כ‬ծ0SBDMF$PSQPSBUJPO⿹‫ך׉ן‬㶨⠓爡ծꟼ鸬⠓爡‫ך‬碛㕂⿹‫ך➭ך׉ן‬㕂‫׷ֶֽח‬涫ꐮ㉀垥‫ׅד‬կ俑⚥‫ך‬爡せծ㉀ㅷせ瘝‫כ‬ぐ爡‫ך‬㉀垥‫כ׋ת‬涫ꐮ㉀垥‫׷֮ד‬㜥さָ֮‫ׅת׶‬կ

2Copyright © 2020 Oracle and/or its affiliates.

㹑⠗傈劤.Z42-ِ٦‫ؠ‬⠓ :PV5VCF‫ٍث‬ٝ‫ٕط‬Ꟛ鏣MySQL Casual‫ٍثגׇ׻֮ה‬ٝ‫ٕط‬涫ꐮֶ격ְְ‫ׅת׃׋‬

https://www.youtube.com/channel/UCbNxgqu2iB8iT1X1iZnH3Ig

The world's most popular open source database⚅歲‫ד‬剑‫׮‬兛⿹‫ؔ׷ְג׃‬٦‫ف‬ٝ‫ا‬٦‫ر أ‬٦‫كة‬٦‫أ‬

4Copyright © 2020 Oracle and/or its affiliates.

The world's most popular open source database⢽) DB-Engines‫׷״ח‬锃叨穠卓
http://db-engines.com/en/

5Copyright © 2020 Oracle and/or its affiliates.

LiteSQccessM
SAedisRearc
h2
DBEl
astics

B
M
ongo
DL
reSQ

Po
stg

erver
SQ
LSQ
L
M
SM
ySO

≪MySQL≫• ‫ؚٗ‬٦‫דٕغ‬㢳ֻ‫ⵃך‬欽罏• ‫ؚٗ‬٦‫דٕغ‬㢳ֻ‫➂ך‬勞• 㢳珏٥㢳欽‫ז‬盖椚‫خ‬٦ٕ• ‫ُر‬،ٕٓ؎‫إ‬ٝ‫أ‬(‫؍ذصُى؝‬٥㉀欽)• Oracle爡ָ⟰噟‫ג׃ה‬䲿⣘• ‫غ‬٦‫ّآ‬ٝٔٔ٦‫أ‬ծ‫ثحػ‬• http://bugs.mysql.com/• ‫ךي؎ةٕؿ‬㼔⟣Ꟛ涪罏• Ꟛ涪ٗ٦‫فحوس‬ِ٦‫كؠ‬٦‫أ‬• 穈‫׫‬鴥‫ⵃ׫‬欽儗‫ך‬IP㼎䘔racle‫ر‬٦‫كة‬٦‫ؑؐزؿاأ‬،‫ך‬兛⿹䏝‫➂װ‬孡‫׾‬ծ؎ٝ‫ة‬٦‫ך♳زحط‬実➂䞔㜠‫װ‬耵⹡穗娖♳‫ד‬‫ך‬穗꿀ծֶ‫ן״‬嗚稊ؒٝ‫آ‬ٝ‫װ‬SNS‫ךד‬䞔㜠ꆀ‫ח⯋׾‬嫣剢⡲䧭‫Ⱅ׃‬Ꟛկ

6

Copyright © 2020 Oracle and/or its affiliates.

Web‫ؙؒحغך‬ٝ‫يذأءس‬傊勻㘗‫ך‬圓䧭Web servers

InternetBack-end

Loadbalancer

MySQL

10

Copyright © 2020 Oracle and/or its affiliates.

Web‫ؙؒحغך‬ٝ‫يذأءس‬‫يذأء׷ֻ֮״‬圓䧭‫ך‬⢽‫ٗؿ‬ٝ‫ؒز‬ٝ‫؟س‬٦‫غ‬٦• Apache• HTML, CSS• PHP/Ruby, Python, Java, AngularJS, Node.js‫ؙؒحغ‬ٝ‫؟س‬٦‫غ‬٦• ٖٔ٦‫رٕشّء‬٦‫كة‬٦‫أ‬: MySQL• ꬊٖٔ٦‫رٕشّء‬٦‫زأة‬،

11

Copyright © 2020 Oracle and/or its affiliates.

Web‫ؙؒحغך‬ٝ‫يذأءس‬剑鵚‫ךف؎ة׷׫ֻ״‬圓䧭Web servers

InternetLoadbalancer

Back-end

MySQLNoSQL‫ًُؗس‬ٝ‫زأز‬،

12

Copyright © 2020 Oracle and/or its affiliates.

ٖٔ٦‫ ٕرٌٕشّء‬vs ‫ًُؗس‬ٝ‫زأز‬،ٌ‫ٕر‬ٖٔ٦‫ٕرٌٕشّء‬‫ًُؗس‬ٝ‫زأز‬،ٌ‫ٕر‬customerid

name3412 John Smithemail

city_id{_id: 3412,
name: "John Smith",email: john@oracle.com,
city: "San Francisco",
country: "US",orders: [{date: "2017-08-24", total: 312.20},{date: "2017-10-02", total: 24.95}]

foobar@example.co 45
mcity
idcitycountry_id

45

San FranciscoUSshop_order
id

id_customer date

total

381

3412

2017-08-24

312.20412

3412

2017-10-02

24.95

13

Copyright © 2020 Oracle and/or its affiliates.

}

MySQL Document StoreNoSQL API‫ٔةأؙٓ‬ٝ‫ؚ‬

JSONMySQLDocument
Store

14

Copyright © 2020 Oracle and/or its affiliates.

MySQL Document Store‫سحٔـ؎ع‬㘗‫ؙؒحغ‬ٝ‫س‬• ⽃♧‫رך‬٦‫كة‬٦‫يذأءأ‬• ٖٔ٦‫הٕشّء‬ꬊٖٔ٦‫( ٕشّء‬JSON)• 麊欽⸬桦‫ך‬ぢ♳• ‫ךزأ؝‬⡚幾Web servers

Back-end

NoSQLInternet‫ًُؗس‬ٝ‫زأز‬،♶銲

Loadbalancer

MySQL15Copyright © 2020 Oracle and/or its affiliates.

،‫؛ٔف‬٦‫ّء‬ٝꟚ涪罏‫ח‬厫鮾䚍‫׾‬‫ر‬٦‫ة‬㘗{}

JSON ‫ر‬٦‫ة‬㘗ٖٔ٦‫ذזٕشّء‬٦‫הٕـ‬ꬊ圓鸡‫ر‬٦‫ءהة‬٦‫חأٖي‬窟さկׁ‫ ח׵‬.Z42-‫ד‬‫כ‬刿倜䚍腉‫ך‬剑黝⻉

16

Copyright © 2020 Oracle and/or its affiliates.

42-ꟼ侧

JSON ꟼ侧();+40/‫ر‬٦‫⿫ךة‬撑刿倜‫׋ך‬‫ך׭‬ぐ珏 42-ꟼ侧‫׾‬㹋鄲կ.Z42- ‫ כד‬+40/‫ر‬٦‫ ׾ة‬42-‫ⴓד‬匿‫ך׭׋׷ׅ‬㢌䳔ꟼ侧‫׮‬鷄⸇‫"سحٔـ؎ع‬1*

MySQL X DevAPI
42-‫ ה‬$36%‫ ז‬/P42- ‫ך‬‫"سحٔـ؎ع‬1*‫׷״ח‬Ꟛ涪厫鮾䚍‫ًُؗس‬ٝ‫زأز‬،‫ה‬SQLJSON‫ر‬٦‫ة‬㘗‫ה‬JSONꟼ侧

Insert儗‫ך‬JSON圓俑‫رٔغ‬٦‫ّء‬ٝ堣腉

CREATE TABLE employees (data JSON);‫ًُؗس‬ٝ‫؎חز‬ٝ‫أؙحر‬鏣㹀‫׃‬넝鸞،‫أإؙ‬INSERT INTO employees VALUES('{"id": 1, "name": "Jane"}’),('{"id": 2, "name": "Joe"}’);

JSON‫ًُؗس‬ٝ‫׾ز‬乼⡲‫׷ׅ‬秈30‫ך‬ꟼ侧纇• JSON‫ر‬٦‫《׾ة‬䖤(JSON_EXTRACT, JSON_KEYS ‫)וז‬• JSON‫ر‬٦‫װة‬圓鸡‫ך‬嗚鏾
(JSON_SEARCH, JSON_CONTAINS ‫)וז‬• JSON‫ر‬٦‫ךة‬㢌刿
(JSON_SET, JSON_INSERT, JSON_REMOVE ‫)וז‬• JSONꂁ⴨‫ךزؙؑآـؔװ‬欰䧭
(JSON_ARRAY, JSON_OBJECT)
17Copyright © 2020 Oracle and/or its affiliates.

SELECT * FROM employees;+-------------------------------------+| data|+-------------------------------------+| {"id": 1, "name": "Jane"}|| {"id": 2, "name": "Joe"}|+-------------------------------------+SET @document = '[10, 20, [30, 40]]';
SELECT JSON_EXTRACT(@document, '$[1]');+---------------------------------+| JSON_EXTRACT(@document, '$[1]') |+---------------------------------+| 20|+---------------------------------+

JSON_EXTRACT‫ך‬満殛怴皾㶨Accepts a JSON Path, which is similar to a selector:$("#type")

JSON_EXTRACT(column_name, "$.type")

JSON_EXTRACT‫כד‬ծ2‫ךא‬満殛䕎‫ه؟׾‬٦‫ز‬column_name->"$.type"(= JSON_EXTRACT)
column_name->>"$.type" (= JSON_EXTRACT + JSON_UNQUOTE)

18

Copyright © 2020 Oracle and/or its affiliates.

MySQL Document Store‫ذ‬٦‫הٕـ‬JSON‫ך‬JOIN

Table statesCollection people{}_id: 101,FirstName: "John",
LastName: "Smith",
Street: "123 Elm Street",State_ID: 5

id

name

1Alabama…

…

5California…

…

mysql> SELECT
50Wyoming->`people`.`doc`->>'$.FirstName',->`people`.`doc`->>'$.LastName'-> FROM->`people`, `states`-> WHERE->`people`.`doc`->'$.State_ID' = `states`.`id` AND->`states`.`name` = 'California'
19

Copyright © 2020 Oracle and/or its affiliates.

MySQL Document StoreJSON‫ر‬٦‫ة‬㘗‫ה‬Windowꟼ侧‫ך‬穈‫׫‬さ‫ׇ׻‬
mysql> SELECT->`sales`.`doc`->>'$.employee' AS `Employee`,->`sales`.`doc`->>'$.date' AS `Date`,->`sales`.`doc`->'$.sales' AS `Sales`,->SUM(`sales`.`doc`->'$.sales')->OVER (PARTITION BY `sales`.`doc`->'$.employee') AS `Total`-> FROM `sales`;+----------+------------+-------+-------+| Employee | Date| Sales | Total |+----------+------------+-------+-------+| Odin| 2019-03-01 | 300 | 1200 || Odin
| 2019-03-02 | 400 | 1200 || Odin
| 2019-03-03 | 500 | 1200 || Thor
| 2019-03-01 | 200 | 1400 || Thor
| 2019-03-02 | 400 | 1400 || Thor
| 2019-03-03 | 800 | 1400 |+----------+------------+-------+-------+
6 rows in set (0.0011 sec)
20Copyright © 2020 Oracle and/or its affiliates.

MySQL Native NoSQL API
X Protocol (NoSQL)
X DevAPI
33060

MySQL Shell

MySQL C / Node.js

X Plugin
X DevAPI

CommandResultJavaScript / Python

Classic MySQL Protocol (SQL)
3306

MySQL Connector

MySQL
Connection
handling

SQL利用アプリ

Optimizer

Node.js Application
Storage
MySQL Connector/Net

X DevAPIWindows Application

...

21

Copyright © 2020 Oracle and/or its affiliates.

MySQL Server

X DevAPIX Plugin (MySQL) ⇔ X Protocol ⇔ X DevAPI (Driver)
X Plugin‫׾‬剣⸬‫ד✲׷ׅח‬ծX Protocol穗歋‫ד‬鸐⥋〳腉‫ًُؗس‬ٝ‫ذהز‬٦‫ّءؙٖ؝ךٕـ‬ٝ‫ח‬㼎‫ךג׃‬$36%Ⳣ椚
NoSQLٓ؎‫זؙ‬圓俑‫ًُؗسד‬ٝ‫חز‬㼎‫׃‬CRUDⳢ椚〳腉
Fluent API

22

Copyright © 2020 Oracle and/or its affiliates.

Tables or Collections?
MySQL Document Store‫ّءؙٖ؝׷ֶֽח‬ٝ‫כ‬2‫׾⴨ךא‬䭯‫א‬暴ⴽ‫ذז‬٦‫ٕـ‬:• ⚺‫ؗ‬٦: `_id`• JSON‫ًُؗس‬ٝ‫ز‬: `doc`- JSON‫ًُؗس‬ٝ‫_`ךز‬id`‫؍ؿ‬٦ٕ‫⟣כس‬䠐‫؟ַ⦼ך‬٦‫غ‬٦ָUUID‫׾‬荈⹛欰䧭- ֿ‫؍ؿך‬٦ٕ‫ؗ⚺ָ⦼ךس‬٦‫ג׃ה‬㾜Ꟛׁ‫׷׸‬䗳銲‫ח‬䘔ׄ‫؎װ⴨ג‬ٝ‫ךأؙحر‬鷄⸇‫כ‬〳腉SQL, NoSQL, ‫ذ‬٦‫ٕـ‬, ‫ّءؙٖ؝‬ٝ‫ء׸׊׸׉כ‬٦‫ⵃחأٖي‬欽〳腉‫ךגץׅ‬乼⡲‫؛ٔفٖכ‬٦‫ّء‬ٝ‫⿾ח‬僥ׁ‫׷׸‬

26

Copyright © 2020 Oracle and/or its affiliates.

SHOW CREATE TABLE `myCollection`¥GTable: myCollection
Create Table: CREATE TABLE `myCollection`(`doc` json DEFAULT NULL,`_id` varchar(32) GENERATED ALWAYS AS(json_unquote(json_extract(`doc`,'$._id')))STORED NOT NULL,PRIMARY KEY (`_id`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

27

Copyright © 2020 Oracle and/or its affiliates.

MySQL Connectors include X Dev API
Use SQL, CRUD APIs‫ؗأ‬٦‫ًُؗسأٖو‬ٝ‫ٖٔן״ֶز‬٦‫ذٕشّء‬٦‫חٕـ‬㼎䘔• Classic APIs‫גִ⸇ח‬ծֿ‫ָגⰋך׵׸‬鷄⸇ׁ‫ׅת׸‬

Operation

Document

RelationalCreate
ReadUpdate
DeleteCollection.add()
Collection.find()
Collection.modify()

Table.insert()
Table.select()
Table.update()Collection.remove()

Table.delete()⿫撑) http://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html
28

Copyright © 2020 Oracle and/or its affiliates.

‫ًُؗس‬ٝ‫ךز‬嗚稊

products.find("color = 'yellow'").sort(["name"]).execute();29Copyright © 2020 Oracle and/or its affiliates.

鎉铂➬圫SQL‫ⵃ( زحإـ؟ך‬欽‫כ‬知⽃ծַ‫ػא‬ٙ‫)ٕؿ‬铣‫ֻׅװ׫‬圓鸡ָ椚鍑‫ְׅװ׃‬‫ךגץׅ‬Connector‫ד‬〳腉‫ז‬ꣲ‫Ⱏ׶‬鸐‫ך‬圓俑 (‫ֻץ׷ז‬獳嗂‫ְׅװ׃‬圓俑)// JavaScriptcollection.find("name = 'foo' AND age > 42").fields("name", "age").groupBy("name", "age").sort("name ASC", "age DESC").limit(4).offset(2).execute()

33

Copyright © 2020 Oracle and/or its affiliates.

// Javacollection.find("name = 'foo' AND age > 42").fields("name", "age").groupBy("name", "age").sort("name ASC", "age DESC").limit(4).offset(2).execute()

MySQL Native NoSQL
Node.JS‫ך‬⢽var schema = session.getSchema("ProductsDB");var products = schema.getCollection("products_collection");var query = "$.name == :name";
products.find(query).bind("name", "Vacuum cleaner").execute(function (doc) {console.log(doc);}).catch(function (err) {console.log(err.message);console.log(err.stack);});

35

Copyright © 2020 Oracle and/or its affiliates.

.Z42-4IFMM‫ך‬暴䗙

36΍ 㢳鎉铂‫ه؟׾‬٦‫ز‬
٥+BWB4DSJQU٥1ZUIPO٥42-Ύ 㹋遤䕎䒭‫׾‬鼅䫛〳腉٥‫ثحغ‬٥؎ٝ‫ـ؍ذؙٓة‬Ώ ぐ珏ِ٦‫؍ذٔ؍ذ‬䭁䓸堣腉٥،‫ؚٖفح‬٦‫ؕحؑثس‬٦٥+40/؎ٝ‫ه‬٦‫ز‬٥넝鸞‫ر‬٦‫ٗة‬٦‫س‬٥ٖ‫ه‬٦‫ز‬堣腉٥‫؎ؚٓف‬ٝ堣腉ΐ 窟さׁ‫"׋׸‬1*٥‫ًُؗس‬ٝ‫زأز‬،乼⡲٥*OOP%#‫ةأؙٓ‬盖椚

Copyright © 2020, Oracle and/or its affiliates

RDBMS‫ה‬NoSQL‫ر‬٦‫زأة‬،‫׾‬⢘欽‫׷ׅ‬ꥷ‫ך‬䧄䙀✲갪

SQL

NoSQL
API

NoSQL
Datastore

MySQL
RelationalTables

42

Copyright © 2020 Oracle and/or its affiliates.

Ꟛ涪罏醱侧‫ך‬API‫׾‬㷕统‫׷ׅ‬䗳銲ָ֮‫׷‬{}

JSONDocuments‫ر‬٦‫ة‬盖椚‫ذ‬٦‫הٕـ‬JSON‫ًُؗس‬ٝ‫ךز‬然㹋‫رז‬٦‫ة‬ず劍ָ㔭ꨇ麊欽⦐ⴽ‫ח‬麊欽‫خ‬٦ٕ‫׾‬㼪Ⰵ‫ג׃‬ⴽղ‫ך‬麊欽盖椚ָ実‫׷׸׵׭‬MySQL Document Store: NoSQL + SQL = MySQL 8.0Ꟛ涪罏‫ךג׏הח‬厫鮾䚍窟さׁ‫׋׸‬API‫׷״ח‬厫鮾䚍‫ر‬٦‫ة‬盖椚‫⥋ך‬걾䚍‫ה‬厫鮾䚍⽃♧‫رך‬٦‫زأة‬،‫رדךז‬٦‫ة‬ず劍♶銲 ‫ذ‬٦‫הٕـ‬JSON‫ًُؗس‬ٝ‫ךز‬JOIN‫׮‬〳腉

X DevAPISQL + CRUD API

MySQL
8.0
RelationalTables

43{}

Copyright © 2020 Oracle and/or its affiliates.

JSONDocuments麊欽⸬桦‫ך‬ぢ♳⽃♧‫ر‬٦‫كة‬٦‫ך׫ךأ‬麊欽‫ד‬幥‫׬‬‫דך‬盖椚頾蚚⡚幾NoSQL + SQL = MySQL
Flexible APIs for Developers
Hybrid Data Models
Proven Transaction Management
Reliable Data Consistency
Simplified Operations
54Copyright © 2020 Oracle and/or its affiliates.

㹑⠗‫آؖوزفؙٔإٔؑء‬ٝ 7PMַ‫׵‬⚅歲‫)׿ע׋(ד‬㈓♧‫ך‬MySQL Shell‫װ‬X DevAPI‫ח‬ꟼ‫׷ׅ‬鸬鯹痥1㔐: MySQL Shell嚊銲痥2㔐: MySQL Shell‫ך‬麊欽ِ٦‫؍ذٔ؍ذ‬痥4㔐: X Dev APIPython箟痥3㔐: X Dev APIJavaScript箟

痥5㔐: InnoDB Cluster‫شؙحغ‬ٝ‫غ‬٦顋㡰⚥痥6㔐: InnoDB ReplicaSet

https://shell-mag.com/category/back-number/Thank you56

Copyright © 2020 Oracle and/or its affiliates.