MySQL 5.7 - Tirando o Máximo Proveito

Principais novidades do MySQL 5.7 em 3 frases:
1) O MySQL 5.7 trouxe melhorias no suporte a operações DDL em tempo real, armazenamento de dados JSON, colunas geradas e o novo schema "sys" para monitoramento.
2) As colunas geradas permitem indexar valores calculados sem ocupar espaço em disco, enquanto colunas armazenadas indexam esses valores de forma armazenada.
3) O schema "sys" fornece visões para monitorar consultas caras, índices não utilizados

Transcript

1. Gabriela D’Ávila
 @gabidavila
 
 Outubro/2016 MySQL 5.7
 Tirando o Máximo Proveito
2. Quem? • Data Engineer • Desenvolvedora por 8 anos • Palestrante ocasional • @gabidavila • http://gabriela.io
3. • Alterações em tempo real de DDL no InnoDB • JSON • Colunas Geradas (Generated Columns) • `sys` schema O Que Esperar? 3
4. Alterações em tempo real de DDL
5. • 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
6. JSON
7. • 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
8. 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
9. @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" }
10. Exemplos
11. • 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
12. • Usando o atalho json_column->“$.path” Acessando Valores 12 SELECT screen_name, response->"$.location" AS 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
13. Acessando e escapando Valores 13 • Usando a função • json_unquote(json_extract(json_column, “$.path”)) • Usando o atalho: • json_column->>“$.path”
14. 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_source FROM `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)
15. Funções suportadas
16. • 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
17. Colunas Geradas
 (Generated Columns)
18. • 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
19. • 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
20. • 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
21. Exemplos
22. -- Total rows SELECT COUNT(*) FROM `twitter_users`; -- 8906 rows -- Virtual Columns ALTER 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 Columns ALTER 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
23. 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
24. 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)
25. `sys` schema
26. • 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
29. 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
31. Demais Alterações
32. • `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
33. • 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
34. • Twitter: @gabidavila • Blog: http://gabriela.io • Freenode: gabidavila • References: MySQL documentation Obrigada! 34