Gabriela D’Ávila@gabidavila
Outubro/2016
MySQL 5.7 Tirando o Máximo Proveito
Quem?
• Data Engineer
• Desenvolvedora por 8 anos
• Palestrante ocasional
• @gabidavila
• http://gabriela.io
• Alterações em tempo real de DDL no InnoDB
• JSON
• Colunas Geradas (Generated Columns)
• `sys` schema
O Que Esperar?
3
Alterações em tempo real de DDL
• In Place (ALGORITHM=INPLACE)
• Renomear índices
• VARCHAR de 1b para 255b*
• Adicionar uma coluna virtual
• Table-copy (ALGORITHM=COPY)
• VARCHAR de 256b para 65535b*
• Adicionar colunas
• Conversão de tipos
Online DDL
5
* Depende de encoding da tabela e coluna, para latin1 seriam 255 caracteres, para utf8 seriam até 63 caracteres
JSON
• Armazenamento é binário no banco de dados
• É possível converter campos TEXT se JSON_VALID() retorna TRUE para toda a tabela
• Conversão de TEXT para JSON é uma operação COPY
• É impossível de se armazenar um JSON inválido
• Colunas do tipo JSON não podem ter um valor DEFAULT
• Índices são possíveis devido à Generated Columns
Recursos
7
Tabela `twitter_users`
8
Field Type Null Key Default Extra
id int(11) NO PRI auto_increment
id_str varchar(255) NO UNI
screen_name varchar(255) NO INDEX
response json YES
created_at datetime NO CURRENT_TIMESTAMP
updated_at datetime NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
CREATE TABLE `twitter_users` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `id_str` VARCHAR(255) NOT NULL, `screen_name` VARCHAR(255) NOT NULL, `response` JSON NOT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `un_id_str` (`id_str`), KEY `id_str` (`id_str`), KEY `ix_screen_name` (`screen_name`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
@PHPesteNE
9
SELECT response FROM `twitter_users` WHERE `screen_name` = 'PHPesteNE'; { "id": 3331483283, "url": "http:\/\/t.co\/vKElFb77Te", "lang": "pt", "name": "PHPeste", "id_str": "3331483283", "profile_image_url": "http:\/\/pbs.twimg.com\/profile_images\/621823785025564673\/64yvbfWw_normal.jpg", "profile_banner_url": "https:\/\/pbs.twimg.com\/profile_banners\/3331483283\/1470600499", "location": "Salvador, Brasil", "verified": false, "following": true, "protected": false, "time_zone": "Brasilia", "created_at": "Wed Jun 17 16:33:34 +0000 2015", "utc_offset": -10800, "description": "PHP Conference Northeast Brazil.", "geo_enabled": true, "screen_name": "phpestene", "listed_count": 15, "friends_count": 314, "statuses_count": 186, "default_profile": true, "followers_count": 318, "favourites_count": 166, "profile_link_color": "0084B4", "profile_text_color": "333333", "profile_image_url_https": "https:\/\/pbs.twimg.com\/profile_images\/621823785025564673\/64yvbfWw_normal.jpg", "profile_background_color": "C0DEED", "profile_sidebar_fill_color": "DDEEF6", "profile_background_image_url": "http:\/\/abs.twimg.com\/images\/themes\/theme1\/bg.png", "profile_sidebar_border_color": "C0DEED", "profile_use_background_image": true, "profile_background_image_url_https": "https:\/\/abs.twimg.com\/images\/themes\/theme1\/bg.png"}
Exemplos
• Usando json_extract(json_column, “$.path”)
Acessando Valores
11
SELECT screen_name, json_extract(response,"$.location")FROM `twitter_users`LIMIT 5;
-- Results-- +---------------+------------------------------+-- | screen_name | location |-- +---------------+------------------------------+-- | wimgtr | "Belgium" |-- | wilw | "Los Angeles" |-- | n0x13 | "Amsterdam, The Netherlands" |-- | stoker | "DFW Texas" |-- | anthonyjuanes | "Exeter" |-- +---------------+------------------------------+-- 5 rows in set (0.00 sec)
• Valores extraídos estarão sempre com aspas e contra-barra
• Usando o atalho json_column->“$.path”
Acessando Valores
12
SELECT screen_name, response->"$.location" AS locationFROM `twitter_users`LIMIT 5;
-- Results-- +---------------+------------------------------+-- | screen_name | location |-- +---------------+------------------------------+-- | wimgtr | "Belgium" |-- | wilw | "Los Angeles" |-- | n0x13 | "Amsterdam, The Netherlands" |-- | stoker | "DFW Texas" |-- | anthonyjuanes | "Exeter" |-- +---------------+------------------------------+-- 5 rows in set (0.00 sec)
• Valores extraídos estarão sempre com aspas e contra-barra
Acessando e escapando Valores
13
• Usando a função
• json_unquote(json_extract(json_column, “$.path”))
• Usando o atalho:
• json_column->>“$.path”
SELECT screen_name, json_extract(response, "$.status.source") AS location, json_unquote(json_extract(response, "$.status.source")) AS esc_loc, response->"$.status.source" AS source, response->>"$.status.source" AS esc_sourceFROM `twitter_users`LIMIT 2;-- Resultado---- *************************** 1. row ***************************-- screen_name: wimgtr-- location: "<a href=\"https://about.twitter.com/products/tweetdeck\" rel=\"nofollow\">TweetDeck</a>"-- esc_loc: <a href="https://about.twitter.com/products/tweetdeck" rel="nofollow">TweetDeck</a>-- source: "<a href=\"https://about.twitter.com/products/tweetdeck\" rel=\"nofollow\">TweetDeck</a>"-- esc_source: <a href="https://about.twitter.com/products/tweetdeck" rel="nofollow">TweetDeck</a>-- *************************** 2. row ***************************-- screen_name: wilw-- location: "<a href=\"http://twitter.com\" rel=\"nofollow\">Twitter Web Client</a>"-- esc_loc: <a href="http://twitter.com" rel="nofollow">Twitter Web Client</a>-- source: "<a href=\"http://twitter.com\" rel=\"nofollow\">Twitter Web Client</a>"-- esc_source: <a href="http://twitter.com" rel="nofollow">Twitter Web Client</a>-- 2 rows in set (0.00 sec)
Funções suportadas
• JSON_ARRAY()
• JSON_ARRAY_APPEND()
• JSON_ARRAY_INSERT()
• JSON_ARRAY_APPEND()
• JSON_ARRAY_INSERT()
• JSON_CONTAINS()
• JSON_CONTAINS_PATH()
• JSON_DEPTH()
• JSON_EXTRACT() or ->
• JSON_INSERT()
• JSON_KEYS()
• JSON_LENGTH()
• JSON_MERGE()
• JSON_OBJECT()
• JSON_QUOTE()
• JSON_REMOVE()
• JSON_REPLACE()
• JSON_SEARCH()
• JSON_SET()
• JSON_TYPE()
• JSON_UNQUOTE() or ->>
• JSON_VALID()
Manipulação de JSON
16
Colunas Geradas (Generated Columns)
• Não ocupa espaço em disco
• A criação é INPLACE
• O valor da coluna é gerado em tempo real
• Chamado quando uma trigger de evento BEFORE é chamada
Virtual Columns
18
• Ocupa espaço em disco
• Valor atualizado para cada operação de escrita (INSERT, UPDATE)
• Usa o algoritmo COPY para criação e remoção da coluna
Stored Columns
19
• Ambas podem ser indexadas
• Permitem que expressões como essas sejam usadas:
• Operadores ( +, -, *, /, etc)
• Funções nativas (LEFT(), SUBSTR(), JSON_EXTRACT(), etc)
• Literais (‘novo’, 2, etc)
• Subqueries não são suportadas
Virtual and Stored Columns
20
Exemplos
-- Total rowsSELECT COUNT(*) FROM `twitter_users`;-- 8906 rows
-- Virtual ColumnsALTER TABLE `twitter_users` ADD COLUMN `profile_image_url` VARCHAR(255) GENERATED ALWAYS AS (response->>"$.profile_image_url");-- [2016-10-04 14:16:24] completed in 46ms
-- Stored ColumnsALTER TABLE `twitter_users` ADD COLUMN `location` VARCHAR(255) GENERATED ALWAYS AS (response->>"$.location") STORED ADD INDEX `ix_location` (`location`);-- [2016-10-04 14:20:09] 8906 rows affected in 2s 689ms
Tabela `twitter_users`
23
Field Type Null Key Default Extra
id int(11) NO PRI auto_increment
id_str varchar(255) NO UNI
screen_name varchar(255) NO INDEX
response json YES
location varchar(255) YES INDEX STORED GENERATED
profile_image_url varchar(255) YES VIRTUAL GENERATED
created_at datetime NO CURRENT_TIMESTAMP
updated_at datetime NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
Busca
24
SELECT screen_name, location, profile_image_url FROM `twitter_users` WHERE location LIKE "%Brasil%" LIMIT 5;
-- Results-- +---------------+-----------------------+----------------------------+-- | screen_name | location | profile_image_url | -- +---------------+-----------------------+----------------------------+-- | hussanii | Brasil | http://pbs.twimg.com/(...) |-- | fplcampos | São Paulo, Brasil | http://pbs.twimg.com/(...) |-- | ivanrosolen | Sampa - Brasil | http://pbs.twimg.com/(...) |-- | rogeriopradoj | Brasil, São Paulo, SP | http://pbs.twimg.com/(...) |-- | rafinhabastos | Brasil | http://pbs.twimg.com/(...) |-- +---------------+-----------------------+----------------------------+-- 5 rows in set (0.00 sec)
`sys` schema
• Precisa ser instalado na versão 5.6
• Instalado por padrão no 5.7
• MySQL Workbench vem com um cliente
• Em produção é recomendado apenas estar ativado em casos críticos
MySQL `sys` schema
26
SELECT * FROM
29
Description Table*High Cost SQL statements sys.`x$statement_analysis`
*Top 5% slower queries sys.`x$statements_with_runtimes_in_95th_percentile`
Use temporary tables sys.`statements_with_temp_tables`
Unused Indexes sys.`schema_unused_indexes`
Full table scans sys.`schema_tables_with_full_table_scans`
*x$ é um prefixo para views criadas para um formato mais amigável ao usuário
Demais Alterações
• `sql_mode` agora é mais estrito e os seguintes modos vêm habilitados por padrão:
• ONLY_FULL_GROUP_BY
• NO_ZERO_DATE
• NO_ZERO_IN_DATE
• ERROR_FOR_DIVISION_BY_ZERO
Demais Alterações
32
• Senhas agora podem ter uma data de expiração
• Tabelas agora suportam mais de uma trigger pro mesmo evento
• Alterações em YEAR(2):
• marcado como deprecated na 5.6
• removido na 5.7
33
Demais Alterações
• Twitter: @gabidavila
• Blog: http://gabriela.io
• Freenode: gabidavila
• References: MySQL documentation
Obrigada!
34
Top Related