mysql como document store php conference 2017
TRANSCRIPT
![Page 1: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/1.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
MySQL comoDocument Store
Airton LastoriConsultor [email protected]
Dez-2017
![Page 2: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/2.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Você usa MySQL?
![Page 3: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/3.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL é o banco de dados de código aberto mais
popular do mundo.
3
![Page 4: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/4.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Popularidade dos Bancos de Dados
http://db-engines.com/en/ranking_trend (339 systems in ranking, December 2017)
![Page 5: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/5.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
“The demand for database administrators and developers is much higher than the current supply, as well, leading to lots of open positions.”
https://stackoverflow.blog/2017/03/09/developer-hiring-trends-2017/
![Page 6: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/6.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Algumas novidades
![Page 7: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/7.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Read Scale-Out
Async Replication + Auto Failover
Write Scale-Out
Sharding
S1
S2
S3
S4
MySQL Vision – 4 Steps
Timeline
MySQL Document Store
Relational & Document Model
MySQL HA
Out-Of-Box HA
![Page 8: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/8.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL Standalone MySQL Replication
8
MySQL InnoDB Cluster MySQL Cluster (NDB)
Arquiteturas para Alta Disponibilidade
![Page 9: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/9.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL InnoDB Cluster
Group Replication
Router Router
App App• Banco de dados replicado altamente
disponível e tolerante a falhas
• Permite topologias Multi-Primary (ativo-ativo) ou Single-Primary
• Arquitetura cloud-friendly, shared-nothing, sem storage compartilhado
• Automatiza failover e failback, reconfiguração e reconexão após falhas
• GA, pronto para produção desde a versão 5.7.17 (dez/16)
MySQL Shell
![Page 10: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/10.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
• 2 anos em Desenvolvimento
• 400+ Worklogs
• 5000+ Bugs corrigidos
• 500 novos testes
• Suporte melhorado para JSON• API Document Store mais completa• CTEs e Window Functions• Melhor manipulação de Hot Rows• Unicode 9.0, utf8 charset padrão• Performance mais consistente via
estatísticas• GIS• Mais facilidades para administração em
Cloud• Novo Data Dictionary nativo• Invisible Indexes• Atualizações das configurações padrão• Refactoring, C++11, etc…
10
MySQL 8.0 Release Candidate 1
![Page 11: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/11.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 11
![Page 12: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/12.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Por que Document Store?
![Page 13: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/13.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Read Scale-Out
Async Replication + Auto Failover
Write Scale-Out
Sharding
S1
S2
S3
S4
MySQL Vision – 4 Steps
Timeline
MySQL Document Store
Relational & Document Model
MySQL HA
Out-Of-Box HA
![Page 14: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/14.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 14
Relacional vs Não-relacional
id name email city_id
3412 John Smith [email protected] 45
id city country_id
45 San Francisco US
customer
city
id id_customer date total
381 3412 2017-08-24 312.20
412 3412 2017-10-02 24.95
shop_order
Schemaless, NoSQL, Não-estruturado
{"name": "John Smith","email": "[email protected]","city": "San Francisco","country": "US","orders": [
{"date": "2017-08-24", "total": 312.20},{"date": "2017-10-02", "total": 24.95}
]}
Schema, SQL, Estruturado
![Page 15: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/15.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Modelo Relacional
• Maior esforço antecipado durante a fase de design
–modelagem de dados e normalização
– definição do esquema físico
• O banco de dados assegura a integridade (dados inválidos são rejeitados)– Tipos de dados
– Restrições (chaves estrangeiras etc.)
Modelo baseado em Documentos
• Menor esforço antecipado
– estrutura de dados definida no código do aplicativo
• Aplicação é responsável pela integridade dos dados
– possibilidade de dados de má qualidade serem inseridos no sistema (por causa de bugs, alterações na aplicação, etc.)
Oracle Confidential – Internal/Restricted/Highly Restricted 15
Qual o melhor?
![Page 16: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/16.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Modelo Relacional
• Pequena curva de aprendizagem
– Requer conhecimento de SQL
• SQL permite que o otimizador execute consultas complexas de forma eficiente - especialmente quando há JOINS de várias tabelas
• Alterações no modelo (schema) ao longo da evolução da aplicação podem ser problemáticas -especialmente para atualizações
Modelo baseado em Documentos
• Mais fácil de começar
• JOINS limitados ou inexistentes, otimizador simples - complexidade de consultas passadas para a aplicação resolver
• Implementação de alterações mais simples - os documentos criados em diferentes versões da aplicação podem coexistir.
Oracle Confidential – Internal/Restricted/Highly Restricted 16
Qual o melhor?
![Page 17: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/17.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Modelo Relacional
• Mais integridade no schema, independente da aplicação
• Schema torna a aplicação mais fácil de manter no longo prazo, pois há maiorcontrole nas mudanças
– Espera-se uma maior qualidade dos dados
– Permite aplicação de algumas validaçõesautomáticas sobre os dados
• JOINS, consultas complexas resolvidas pelo otimizador
Modelo baseado em Documentos
• Menor preocupação na definição de Tipos de Dados, protótipos rápidos, comece armazenar dados imediatamente
• Mais liberdade para representar dados (ex. Campos custom)
• Menos dor-de-cabeça para aplicarmudanças no modelo de dados
• Denormalização natural, registro auto-contido (ex. facilita escalabilidadehorizontal via sharding)
Oracle Confidential – Internal/Restricted/Highly Restricted 17
Pontos fortes
![Page 18: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/18.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 18
Banco de Dados Híbrido: Confiabilidade + Flexibilidade
MySQL 5.7
Document Store
Suporte JSON
Nova API
BDs RelacionaisTecnologia madura,
comprovadamente segura.Transações, queries, JOINs
complexos e conjunto extenso de ferrametnas
operacionais
NoSQLFlexibilidade. Escalabilidade
horizontal e facilidade de uso, schemaless, document
store (JSON)
Aplicações ModernasAgile + DevOps com proteção robusta dos dados
Banco de Dados HíbridoMelhor dos mundos, semtrade-offs. Propriedades ACID & confiabilidade de SGBDR + flexibilidade de document store
![Page 19: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/19.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 19
Document Store
_id customer_shop
3412
customer
{"name": "John Smith","email": "[email protected]","city": "San Francisco","country": "US","orders": [
{"date": "2017-08-24", "total": 312.20},{"date": "2017-10-02", "total": 24.95}
]}
![Page 20: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/20.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
SQL - Document Store
CREATE DATABASE customer_doc;
USE customer_doc;
CREATE TABLE customer (
_id INT NOT NULL AUTO_INCREMENT,
customer_shop JSON NOT NULL,
PRIMARY KEY (_id)
);
20
![Page 21: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/21.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
SQL - Document Store
INSERT INTO customer (_id, customer_shop) VALUES (3412,
'{
"name": "John Smith",
"email": "[email protected]",
"city": "San Francisco",
"country": "US",
"orders": [
{"date": "2017-08-24", "total": 312.20},
{"date": "2017-10-02", "total": 24.95}
]
}');
21
![Page 22: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/22.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 22
Relacional + Document Store
id name email city_id shop
3412 John Smith [email protected] 45
id city country_id
45 San Francisco US
customer_and_orders
city{
"orders": [{"date": "2017-08-24", "total": 312.20},{"date": "2017-10-02", "total": 24.95}
]}
![Page 23: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/23.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 23
Relacional + Document Store
![Page 24: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/24.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
CREATE DATABASE customer_hybrid;
USE customer_hybrid;
CREATE TABLE city (
id INT NOT NULL AUTO_INCREMENT,
city VARCHAR(60) NOT NULL,
country_id CHAR(2),
PRIMARY KEY(id)
);
CREATE TABLE customer_and_orders (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(60) NOT NULL,
email VARCHAR(50) NOT NULL,
city_id INT NOT NULL,
shop JSON NOT NULL,
PRIMARY KEY (id),
CONSTRAINT FK_customer_city
FOREIGN KEY (city_id)
REFERENCES city(id)
);
24
SQL - Relacional + Document Store
![Page 25: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/25.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
INSERT INTO city (id, city,
country_id)
VALUES (45, 'San Francisco', 'US');
SELECT * FROM city;
INSERT INTO customer_and_orders
(id, name, email, city_id, shop)
VALUES (3412, 'John Smith',
'[email protected]', 45,
'{
"orders": [
{"date": "2017-08-24",
"total": 312.20},
{"date": "2017-10-02",
"total": 24.95}
]
}');
SELECT * FROM customer_and_orders;
25
SQL - Relacional + Document Store
![Page 26: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/26.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Como usar o MySQL como Document Store
![Page 27: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/27.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
SQL – modelo híbrido
• Native JSON datatype
• JSON Functions
• Generated Columns
XDev API – modelo em documentos
• Document Store plugin
• X Protocol
• MySQL Shell
• Connector/J
• Connector/Net
• Connector/Node.js
• Connector/Python
27
2 formas de utilização de MySQL como Document Store
![Page 28: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/28.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
SQL – modelo híbrido
• Native JSON datatype
• JSON Functions
• Generated Columns
XDev API – modelo em documentos
• Document Store plugin
• X Protocol
• MySQL Shell
• Connector/J
• Connector/Net
• Connector/Node.js
• Connector/Python
28
2 formas de utilização de MySQL como Document Store
![Page 29: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/29.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Tipo nativo JSON
29
CREATE TABLE employees (data JSON);
INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}');
INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}');
SELECT * FROM employees;
+---------------------------+
| data |
+---------------------------+
| {"id": 1, "name": "Jane"} |
| {"id": 2, "name": "Joe"} |
+---------------------------+
2 rows in set (0,00 sec)
![Page 30: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/30.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Vantagens sobre tipos TEXT/VARCHAR
1. Validação
2. Eficiência no armazenamento físicoFormato binário otimizado que permite um acesso mais rápido aos elementos de matrizes
30
INSERT INTO employees VALUES ('texto que não é JSON válido');
ERROR 3130 (22032): Invalid JSON text: "Expect a value here." at
position 0 in value (or column) 'texto que não é JSON válido'.
![Page 31: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/31.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 31
Funções JSON
JSON_ARRAY_APPEND()
JSON_ARRAY_INSERT()
JSON_ARRAY()
JSON_CONTAINS_PATH()
JSON_CONTAINS()
JSON_DEPTH()
JSON_EXTRACT()
JSON_INSERT()
JSON_KEYS()
JSON_LENGTH()
JSON_MERGE()
JSON_OBJECT()
JSON_QUOTE()
JSON_REMOVE()
JSON_REPLACE()
JSON_SEARCH()
JSON_SET()
JSON_TYPE()
JSON_UNQUOTE()
JSON_VALID()
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
![Page 32: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/32.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Exemplo: JSON_EXTRACT
32
SET @document = '[10, 20, [30, 40]]';
SELECT JSON_EXTRACT(@document, '$[1]');
+---------------------------------+
| JSON_EXTRACT(@document, '$[1]') |
+---------------------------------+
| 20 |
+---------------------------------+
1 row in set (0.01 sec)
![Page 33: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/33.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Generated Columns
33
id my_integer my_integer_plus_one
1 10 11
2 20 21
3 30 31
4 40 41
UPDATE t1 SET my_integer_plus_one = 10 WHERE id = 1;
ERROR 3105 (HY000): The value specified for generated column
'my_integer_plus_one' in table 't1' is not allowed.
Coluna atualizada automaticamente com base na definição CREATE.
Somente leitura, é claro!
CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY auto_increment,
my_integer INT,
my_integer_plus_one INT AS (my_integer + 1)
);
![Page 34: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/34.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Testes com dados reais
• San Francisco OpenData
• 206K objetos JSON
• Importado de https://github.com/zemirco/sf-city-lots-json + ajustes
34
CREATE TABLE features (
id INT NOT NULL auto_increment primary key,
feature JSON NOT NULL
);
![Page 35: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/35.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 35
{
"type":"Feature",
"geometry":{
"type":"Polygon",
"coordinates":[
[
[-122.42200352825247,37.80848009696725,0],
[-122.42207601332528,37.808835019815085,0],
[-122.42110217434865,37.808803534992904,0],
[-122.42106256906727,37.80860105681814,0],
[-122.42200352825247,37.80848009696725,0]
]
]
},
"properties":{
"TO_ST":"0",
"BLKLOT":"0001001",
"STREET":"UNKNOWN",
"FROM_ST":"0",
"LOT_NUM":"001",
"ST_TYPE":null,
"ODD_EVEN":"E",
"BLOCK_NUM":"0001",
"MAPBLKLOT":"0001001"
}
}
![Page 36: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/36.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Comparação de Performance: JSON vs TEXT
36
# tipo JSON
SELECT DISTINCT
feature->"$.type" as json_extract
FROM features;
+--------------+
| json_extract |
+--------------+
| "Feature" |
+--------------+
1 row in set (1.25 sec)
# tipo TEXT
SELECT DISTINCT
feature->"$.type" as json_extract
FROM features;
+--------------+
| json_extract |
+--------------+
| "Feature" |
+--------------+
1 row in set (12.85 sec)
JSON 10x mais rápido que TEXT numa busca full scan(sem índice, 206K documentos)
![Page 37: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/37.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Indexando via Generated Columns
37
ALTER TABLE features ADD feature_type VARCHAR(30) AS (feature->"$.type");
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE features ADD INDEX (feature_type);
Query OK, 0 rows affected (0.73 sec)
Records: 0 Duplicates: 0 Warnings: 0
SELECT DISTINCT feature_type FROM features;
+--------------+
| feature_type |
+--------------+
| "Feature" |
+--------------+
1 row in set (0.06 sec)
Down from 1.25 sec to 0.06 sec
Creates index only. Does not modify table rows.
Meta data change only (FAST). Does not need to touch table.
![Page 38: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/38.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 38
Funções JSON: além do CRUD
JSON_ARRAY_APPEND()
JSON_ARRAY_INSERT()
JSON_ARRAY()
JSON_CONTAINS_PATH()
JSON_CONTAINS()
JSON_DEPTH()
JSON_EXTRACT()
JSON_INSERT()
JSON_KEYS()
JSON_LENGTH()
JSON_MERGE()
JSON_OBJECT()
JSON_QUOTE()
JSON_REMOVE()
JSON_REPLACE()
JSON_SEARCH()
JSON_SET()
JSON_TYPE()
JSON_UNQUOTE()
JSON_VALID()
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
![Page 39: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/39.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
JSON Object Creation
39
SELECT JSON_OBJECT('id', id,
'street', feature->"$.properties.STREET",
'type', feature->"$.type"
) AS json_object
FROM features ORDER BY RAND() LIMIT 3;
+--------------------------------------------------------+
| json_object |
+--------------------------------------------------------+
| {"id": 122976, "type": "Feature", "street": "RAUSCH"} |
| {"id": 148698, "type": "Feature", "street": "WALLACE"} |
| {"id": 45214, "type": "Feature", "street": "HAIGHT"} |
+--------------------------------------------------------+
3 rows in set (3.11 sec)
![Page 40: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/40.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
JSON Array Creation
40
SELECT JSON_ARRAY(id,
feature->"$.properties.STREET",
feature->'$.type") AS json_array
FROM features ORDER BY RAND() LIMIT 3;
+-------------------------------+
| json_array |
+-------------------------------+
| [65298, "10TH", "Feature"] |
| [122985, "08TH", "Feature"] |
| [172884, "CURTIS", "Feature"] |
+-------------------------------+
3 rows in set (2.66 sec)
![Page 41: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/41.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
JSON_REPLACE()
41
SELECT JSON_REPLACE(feature, '$.type', JSON_ARRAY('feature', 'bug')) as
json_object FROM features LIMIT 1;
+--------------------------------------------------------+
| json_object |
+--------------------------------------------------------+
| {"type": ["feature", "bug"], "geometry": {"type": ..}} |
+--------------------------------------------------------+
![Page 42: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/42.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 42
Explore mais funções JSON
JSON_ARRAY_APPEND()
JSON_ARRAY_INSERT()
JSON_ARRAY()
JSON_CONTAINS_PATH()
JSON_CONTAINS()
JSON_DEPTH()
JSON_EXTRACT()
JSON_INSERT()
JSON_KEYS()
JSON_LENGTH()
JSON_MERGE()
JSON_OBJECT()
JSON_QUOTE()
JSON_REMOVE()
JSON_REPLACE()
JSON_SEARCH()
JSON_SET()
JSON_TYPE()
JSON_UNQUOTE()
JSON_VALID()
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
![Page 43: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/43.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 43
Exemplo CRUD modelo híbridoMySQL 5.7.12+
![Page 44: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/44.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Exemplo Modelo híbrido: Schema + Schemaless
44
mysql> CREATE DATABASE product_hybrid_test;
mysql> USE product_hybrid_test;
mysql> CREATE TABLE product_info_hybrid (
product_id INT NOT NULL PRIMARY KEY,
description VARCHAR(60) NOT NULL,
price FLOAT NOT NULL,
attributes JSON NOT NULL
);
![Page 45: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/45.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
CREATE 1
mysql> INSERT INTO product_info_hybrid VALUES (
9,
't-shirt',
20.0,
'{
"size" : "M",
"color" : "red",
"fabric" : "cotton"
}');
Query OK, 1 row affected (0.01 sec)
45
![Page 46: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/46.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
CREATE 2
mysql> INSERT INTO product_info_hybrid VALUES (
10,
'socks',
15.0,
'{
"size" : "40"
}');
Query OK, 1 row affected (0.01 sec)
46
![Page 47: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/47.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
READmysql> SELECT * FROM product_info_hybrid;
+------------+-------------+-------+---------------------------------------------------+
| product_id | description | price | attributes |
+------------+-------------+-------+---------------------------------------------------+
| 9 | t-shirt | 20 | {"size": "M", "color": "red", "fabric": "cotton"} |
| 10 | socks | 15 | {"size": "40"} |
+------------+-------------+-------+---------------------------------------------------+
2 rows in set (0.00 sec)
47
![Page 48: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/48.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
READ com filtromysql> SELECT * FROM product_info_hybrid WHERE attributes->"$.size"="40";
+------------+-------------+-------+----------------+
| product_id | description | price | attributes |
+------------+-------------+-------+----------------+
| 10 | socks | 15 | {"size": "40"} |
+------------+-------------+-------+----------------+
1 row in set (0.00 sec)
48
![Page 49: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/49.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
UPDATEmysql> UPDATE product_info_hybrid SET attributes = '{"size": "42"}' WHERE
product_id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM product_info_hybrid;
+------------+-------------+-------+---------------------------------------------------+
| product_id | description | price | attributes |
+------------+-------------+-------+---------------------------------------------------+
| 9 | t-shirt | 20 | {"size": "M", "color": "red", "fabric": "cotton"} |
| 10 | socks | 15 | {"size": "42"} |
+------------+-------------+-------+---------------------------------------------------+
2 rows in set (0.00 sec)
49
![Page 50: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/50.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
DELETEmysql> DELETE FROM product_info_hybrid WHERE attributes->"$.size"="42";
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM product_info_hybrid;
+------------+-------------+-------+---------------------------------------------------+
| product_id | description | price | attributes |
+------------+-------------+-------+---------------------------------------------------+
| 9 | t-shirt | 20 | {"size": "M", "color": "red", "fabric": "cotton"} |
+------------+-------------+-------+---------------------------------------------------+
1 row in set (0.00 sec)
50
![Page 51: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/51.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
SQL – modelo híbrido
• Native JSON datatype
• JSON Functions
• Generated Columns
XDev API – modelo em documentos
• Document Store plugin
• X Protocol
• MySQL Shell
• Connector/J
• Connector/Net
• Connector/Node.js
• Connector/Python
51
2 formas de utilização de MySQL como Document Store
![Page 52: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/52.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
XDev API
• Foco: facilidade de uso
• Abstração sobre SQL para operaçõesCRUD
• Add
• Find
• Modify
• Remove
• Sort
• Create Index
• X Sessions & Node Sessions
– abstração da conexão com BD
• Atualização dos conectores
– Connector/J
– Connector/Net
– Connector/Node.js
– Connector/Python
52
![Page 53: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/53.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Arquitetura SQL + NoSQL
53
MySQL
Plugins
X Protocol Plugin Memcached PluginCore
X ProtocolStd Protocol
X Protocol33060
Std Protocol3306
SQL API CRUD API
X and StdProtocols
MySQLShell
![Page 54: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/54.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Arquitetura SQL + NoSQL
54
MySQL
Plugins
X Protocol Plugin Memcached PluginCore
X ProtocolStd Protocol
X Protocol33060
Std Protocol3306
SQL API CRUD API
X and StdProtocols
MySQLShell
![Page 55: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/55.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 55
MySQL Shell
![Page 56: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/56.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 56
Exemplo CRUD Document APIMySQL 5.7.13+
![Page 57: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/57.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 57
CRUD: Documento vs Relacional
![Page 58: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/58.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 58
![Page 59: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/59.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 59
![Page 60: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/60.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 60
![Page 61: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/61.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 61
![Page 62: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/62.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
![Page 64: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/64.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
![Page 65: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/65.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL como Key-value Store
![Page 66: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/66.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Arquitetura SQL + NoSQL
66
MySQL
Plugins
X Protocol Plugin Memcached PluginCore
X ProtocolStd Protocol
X Protocol33060
Std Protocol3306
SQL API CRUD API
X and StdProtocols
MySQLShell
![Page 67: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/67.jpg)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 67
MySQL com memcached: Key-Value Store
+1 000 000 QPS
*X86 48 cores
http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html
![Page 68: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/68.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Conclusão
![Page 69: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/69.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Um database flexível
–Mão-de-obra com conhecimentosprofundos
– Estável
– Custos efecientes e controlados
– Fácil mover dados entre instâncias
–Menos Drivers
–Menos Ferramentas
– SQL funciona, CRUD funciona
–Operacional e Analítico juntos
Muitos databases diferentes
– Requer repertório maior de habilidades, dificulta encontrar mão de obra com conhecimentos profundos
–Muitos Drivers
–Muitas Ferramentas
–Mais esforço para integrar, compartilhar e mover dados
–Muito mais trabalho
– Separação muitas vezes desnecessáriade Operacional e Analítico
Por que combinar em um único database?
![Page 70: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/70.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Livro:Introduciong the MySQL 8 Document Store
– Chuck Bell, Editora Apress, 2018
![Page 71: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/71.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Momento jabá
![Page 72: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/72.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Treinamentos e Certificações
MySQL 5.6 DatabaseAdministrator
MySQL 5.6 Developer
education.oracle.com/mysql
![Page 73: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/73.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Enterprise Edition
EscalabilidadeAutenticação
FirewallAuditorianovo TDE
Criptografia
MySQL Enterprise MonitorOracle EM for MySQL
Plug-ins
Suporte
HotBackup
Monitor & Workbench
![Page 74: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/74.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
cloud.oracle.com/pt_BR/mysql
![Page 75: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/75.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/apaas/php/php-mysql-service/php-mysql-service.html
![Page 76: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/76.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
@MySQLBR meetup.com/MySQL-BRfacebook.com/MySQLBR
pt.planet.mysql.com
![Page 77: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/77.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Perguntas?
Contatos:[email protected]/mysqlbrfacebook.com/mysqlbr
![Page 78: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/78.jpg)
![Page 79: MySQL como Document Store PHP Conference 2017](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a64c4f97f8b9a824a8b4863/html5/thumbnails/79.jpg)
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Safe Harbor Statement
The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.