MySQL como Document Store PHP Conference 2017

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/

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

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

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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 14 Relacional vs Não-relacional id name email city_id 3412 John Smith john@oracle.com 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": "john@oracle.com", "city": "San Francisco", "country": "US", "orders": [ {"date": "2017-08-24", "total": 312.20}, {"date": "2017-10-02", "total": 24.95} ] } Schema, SQL, Estruturado

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?

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?

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á maior controle nas mudanças – Espera-se uma maior qualidade dos dados – Permite aplicação de algumas validações automá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 aplicar mudanças no modelo de dados • Denormalização natural, registro auto- contido (ex. facilita escalabilidade horizontal via sharding) Oracle Confidential – Internal/Restricted/Highly Restricted 17 Pontos fortes

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 Relacionais Tecnologia madura, comprovadamente segura. Transações, queries, JOINs complexos e conjunto extenso de ferrametnas operacionais NoSQL Flexibilidade. Escalabilidade horizontal e facilidade de uso, schemaless, document store (JSON) Aplicações Modernas Agile + DevOps com proteção robusta dos dados Banco de Dados Híbrido Melhor dos mundos, sem trade-offs. Propriedades ACID & confiabilidade de SGBDR + flexibilidade de document store

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 19 Document Store _id customer_shop 3412 customer { "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} ] }

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

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": "john@oracle.com", "city": "San Francisco", "country": "US", "orders": [ {"date": "2017-08-24", "total": 312.20}, {"date": "2017-10-02", "total": 24.95} ] }'); 21

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 22 Relacional + Document Store id name email city_id shop 3412 John Smith john@oracle.com 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} ] }

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

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', 'john@oracle.com', 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

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

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)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Vantagens sobre tipos TEXT/VARCHAR 1. Validação 2. Eficiência no armazenamento físico Formato 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'.

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

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)

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) );

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 );

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" } }

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)

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.

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

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)

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)

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": ..}} | +--------------------------------------------------------+

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

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 );

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

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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | READ 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": "40"} | +------------+-------------+-------+---------------------------------------------------+ 2 rows in set (0.00 sec) 47

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | READ com filtro mysql> 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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | UPDATE mysql> 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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | DELETE mysql> 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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | XDev API • Foco: facilidade de uso • Abstração sobre SQL para operações CRUD • 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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Arquitetura SQL + NoSQL 53 MySQL Plugins X Protocol Plugin Memcached Plugin Core X ProtocolStd Protocol X Protocol 33060 Std Protocol 3306 SQL API CRUD API X and Std Protocols MySQL Shell

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

Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | Um database flexível – Mão-de-obra com conhecimentos profundos – 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ária de Operacional e Analítico Por que combinar em um único database?

Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | Enterprise Edition Escalabilidade Autenticação Firewall Auditoria novo TDE Criptografia MySQL Enterprise Monitor Oracle EM for MySQL Plug-ins Suporte Hot Backup Monitor & Workbench

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.

MySQL como Document Store PHP Conference 2017

More Related Content

What's hot

Similar to MySQL como Document Store PHP Conference 2017

More from MySQL Brasil

Recently uploaded

MySQL como Document Store PHP Conference 2017