Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Old but Gold - O SQL é mais poderoso que você i...

Old but Gold - O SQL é mais poderoso que você imagina

O SQL é velho, mas é muito poderoso, veja aqui as novidades presentes - finalmente - no MySQL 8.0 e aprenda coisas que vocês nem imaginaram!

Avatar for Gabriela D'Ávila Ferrara

Gabriela D'Ávila Ferrara

September 11, 2020
Tweet

More Decks by Gabriela D'Ávila Ferrara

Other Decks in Programming

Transcript

  1. OLD but GOLD: SQL é mais poderoso que você imagina

    Gabriela D'Ávila Ferrara (Gabi) @gabidavila Developer Advocate @ Google Cloud gabi.dev
  2. @gabidavila Gabi, o que você faz agora ou já fez?

    • Developer Advocate para o Google Cloud • Foco em bancos de dados • Engenheira de Dados (Data Engineer) • Desenvolvedora PHP/Ruby/JavaScript por muito tempo. • SQL Nerd
  3. @gabidavila Fatos sobre SQL • Criado em 1974 (46 anos!)

    • 4GL - Linguagem de Quarta Geração • Sub-linguagens: DQL, DML, DDL, DCL • Padrão ANSI em 1986 • Padrão ISO em 1987
  4. @gabidavila Qual a contribuição do PHP para o SQL? Tem

    a ver com uma pessoinha que você deve conhecer... Rasmus Lerdorf criador do PHP
  5. @gabidavila Peraíííííí... Como deve ser paginação pelo padrão ANSI/ISO? SELECT

    * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY date ASC) AS row_number, columns FROM posts ) AS ordered_posts WHERE row_number <= 10
  6. @gabidavila LIMIT é lento • Faz leitura do resultado inteiro

    pra saber o que limitar • Se for uma tabela muito grande... • ROW_NUMBER é uma Window Function
  7. @gabidavila Brief History • Created to handle from up to

    10 to 100M rows or around 100MB/table • Now supports terabyte-sized databases • Supports SQL standards as new as SQL 2016 • But... some stuff from SQL 2003 just became available (Window Functions, CTEs) • What is new? 23 OF MAY 1995 GPL V2
  8. @gabidavila User Management • Reusable Permissions/Roles • Password policy •

    New Password • Reuse of Password • Expiration • Rotation gabi.fyi/roles-mysql
  9. @gabidavila User Management • GRANT doesn’t create users anymore, it

    just… grants!!! • No more googling all the time how to create users with the right permissions! gabi.fyi/roles-mysql
  10. @gabidavila Creating a READONLY role CREATE ROLE 'readonly'; GRANT SELECT

    ON app.* TO 'readonly'; • Create the Role • Define the Privileges gabi.fyi/roles-mysql
  11. @gabidavila Creating a user with READONLY role • Create the

    User • Grant the Role CREATE USER 'gabriela'@'%' IDENTIFIED BY 'my_pwd'; GRANT 'readonly' TO 'gabriela'@'%'; gabi.fyi/roles-mysql
  12. @gabidavila UUID() Support CREATE TABLE users ( uid binary(16) NOT

    NULL DEFAULT (UUID_TO_BIN(UUID())), username varchar(255) NOT NULL, PRIMARY KEY (uid) );
  13. @gabidavila New default Charset • Default: • 5.7: latin1 •

    8.0: utf8mb4 • Improvements: • ➕ Mathematical Equations =·² • • & more SMP (Supplementary Multilingual Plane) Characters
  14. @gabidavila New default Collation • utf8mb4_0900_ai_ci • UTF-8 version 9.0

    support • Accent Insensitive • Case Insensitive • No more = bug • Caused by utf8mb4_general_ci or utf8mb4_unicode_ci More information on how collations behave here.
  15. @gabidavila Other defaults & variables • Binary log (log_bin) is

    enabled by default • SHA-2 for authentication • Mandatory default value for TIMESTAMP • New variable to dedicated servers (default OFF), innodb_dedicated_server=ON , controls dynamically: ◦ innodb_buffer_pool_size ◦ innodb_log_file_size ◦ innodb_flush_method
  16. @gabidavila CHECK Constraint Rules • Non-generated and generated columns are

    permitted, except columns with the AUTO_INCREMENT attribute. • Literals, deterministic built-in functions, and operators are permitted. • Non-deterministic built-in functions (such as AVG, COUNT, RAND, LAST_INSERT_ID, FIRST_VALUE, LAST_VALUE, …) are not permitted. • Sub-queries are not permitted.
  17. @gabidavila CHECK Constraint Rules • Environmental variables (such as CURRENT_USER,

    CURRENT_DATE, …) are not permitted. • Variables (system variables, user-defined variables, and stored program local variables) are not permitted. • Stored functions and user-defined functions are not permitted.
  18. @gabidavila CHECK CREATE TABLE order_items ( id INTEGER AUTO_INCREMENT NOT

    NULL, product_id INTEGER NOT NULL, user_id INTEGER NOT NULL, quantity INTEGER, price DECIMAL(10, 2), PRIMARY KEY (id), CONSTRAINT `chk_quantity` CHECK (quantity > 0) );
  19. @gabidavila CHECK mysql> INSERT INTO orders_items(product_id, user_id, quantity, price) ->

    VALUES(5, 2, 1, 1.99); Query OK, 1 row affected (0.15 sec) mysql> INSERT INTO orders(product_id, user_id, quantity, price) -> VALUES(5, 2, -2, 1.99); ERROR 3819 (HY000): Check constraint 'chk_quantity' is violated.
  20. @gabidavila Descending Indexes Up to 5.7 • Syntax allowed ASC

    or DESC when defining an index However... An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.
  21. @gabidavila Descending Indexes • No longer ignored and forcibly created

    as ASC • Actually works!!! ALTER TABLE users ADD INDEX ix_username (username DESC);
  22. @gabidavila Descending Indexes Can be scanned as intended or backwards

    SELECT * FROM users WHERE username LIKE 'g%' ORDER BY username DESC; -- OR WITH THE SAME COST SELECT * FROM users WHERE username LIKE 'g%' ORDER BY username;
  23. @gabidavila DESCRIBE `users`; Type Null Key Default Extra `id` bigint(20)

    unsigned NO PRI auto_increment `username` varchar(255) NO `created_at` timestamp NO CURRENT_TIMESTAMP DEFAULT_GENERATED `updated_at` timestamp NO CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
  24. @gabidavila Invisible Indexes • Not used by the optimizer •

    Visible by default, to create an invisible index: ALTER TABLE users ADD INDEX ix_username (username) INVISIBLE; ALTER TABLE users ALTER INDEX ix_username INVISIBLE; ALTER TABLE users ALTER INDEX ix_username VISIBLE; • Toggle visibility:
  25. @gabidavila Invisible Indexes SELECT * FROM users WHERE username =

    'fancy'; Visible Cost: 0.98 Rows: 1 Invisible Cost: 518,331.25 Rows: 5.04M
  26. @gabidavila Instant • Add columns without doing a INPLACE/COPY operation

    • Must be appending a column to a table • Must not have a DEFAULT on the new column • Rename Table • Modify Columns • Virtual Columns • SET/DROP DEFAULT value of a column
  27. @gabidavila Example mysql> ALTER TABLE orders ADD COLUMN total DECIMAL(10,2)

    NOT NULL, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT count(*) FROM orders; +----------+ | COUNT(*) | +----------+ | 50996708 | +----------+ 1 row in set (7.80 sec)
  28. @gabidavila What they do? • Allows to analyze the rows

    of a given result set • Can behave like a GROUP BY without changing the result set • Allows you to use a frame to "peek" OVER a PARTITION of a window
  29. @gabidavila Window Functions • Examples: • Enumerate rows - ROW_NUMBER()

    • Show Aggregated sums - SUM() • Rank results - RANK() • Look at neighboring rows - LEAD(), LAG()
  30. @gabidavila DESCRIBE `orders`; CREATE TABLE `orders` ( `id` bigint(20) UNSIGNED

    NOT NULL AUTO_INCREMENT, `user_id` bigint(20) UNSIGNED NOT NULL, `status` varchar(20) NOT NULL DEFAULT 'new', `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL )
  31. @gabidavila SELECT … FROM `orders` WHERE … ORDER BY created_at

    LIMIT 10; +---------+---------+-----------+---------------------+ | id | user_id | status | created_at | +---------+---------+-----------+---------------------+ | 6534954 | 654321 | canceled | 2011-08-21 19:06:59 | | 6534949 | 654321 | canceled | 2013-11-16 09:02:19 | | 6534953 | 654321 | completed | 2015-10-28 02:21:30 | | 6534951 | 654321 | new | 2018-01-05 17:12:23 | | 6534952 | 654321 | new | 2018-07-19 04:23:41 | | 6534955 | 654321 | new | 2018-11-12 05:37:48 | | 6534950 | 654321 | pending | 2018-12-20 06:11:23 | +---------+---------+-----------+---------------------+
  32. @gabidavila Previous and Next orders | LAG and LEAD SELECT

    id, user_id, status, LAG(created_at) OVER(ORDER BY created_at) AS previous_order, created_at, LEAD(created_at) OVER(ORDER BY created_at) AS next_order FROM orders WHERE user_id = 654321 ORDER BY created_at LIMIT 10;
  33. @gabidavila SELECT … FROM `orders` WHERE … ORDER BY created_at

    LIMIT 10; +---------+---------+-----------+---------------------+---------------------+---------------------+ | id | user_id | status | previous_order | created_at | next_order | +---------+---------+-----------+---------------------+---------------------+---------------------+ | 6534954 | 654321 | canceled | null | 2011-08-21 19:06:59 | 2013-11-16 09:02:19 | | 6534949 | 654321 | canceled | 2011-08-21 19:06:59 | 2013-11-16 09:02:19 | 2015-10-28 02:21:30 | | 6534953 | 654321 | completed | 2013-11-16 09:02:19 | 2015-10-28 02:21:30 | 2018-01-05 17:12:23 | | 6534951 | 654321 | new | 2015-10-28 02:21:30 | 2018-01-05 17:12:23 | 2018-07-19 04:23:41 | | 6534952 | 654321 | new | 2018-01-05 17:12:23 | 2018-07-19 04:23:41 | 2018-11-12 05:37:48 | | 6534955 | 654321 | new | 2018-07-19 04:23:41 | 2018-11-12 05:37:48 | 2018-12-20 06:11:23 | | 6534950 | 654321 | pending | 2018-11-12 05:37:48 | 2018-12-20 06:11:23 | null | +---------+---------+-----------+---------------------+---------------------+---------------------+
  34. @gabidavila Repetition? SELECT id, user_id, status, LAG(created_at) OVER(ORDER BY created_at)

    AS previous_order, created_at, LEAD(created_at) OVER(ORDER BY created_at) AS next_order FROM orders WHERE user_id = 654321 ORDER BY created_at LIMIT 10;
  35. @gabidavila Named Windows! SELECT id, user_id, status, LAG(created_at) OVER(dates) AS

    previous_order, created_at, LEAD(created_at) OVER(dates) AS next_order FROM orders WINDOW dates AS (ORDER BY created_at) WHERE user_id = 654321 ORDER BY created_at LIMIT 10;
  36. @gabidavila Common Table Expressions • Similar to CREATE [TEMPORARY] TABLE

    • Doesn’t need CREATE privilege • Can reference other CTEs (if those are already defined) • Can be recursive • Easier to read
  37. @gabidavila Recursive CTE • Useful with hierarchical data • The

    Recipe is: • Base query comes first • Second query comes after an UNION statement • And the stop condition should be on the recursive call
  38. @gabidavila SELECT * FROM `store`.`categories`; +------+-------------------------------+--------------------+ | id | name

    | parent_category_id | +------+-------------------------------+--------------------+ | 1 | Animal | 0 | | 2 | Plant | 0 | | 3 | Dog | 1 | | 4 | Cat | 1 | | 5 | Tulip | 10 | | 6 | West Highlander White Terrier | 12 | | 7 | Lettuce | 11 | | 8 | Sunflower | 10 | | 10 | Flowers | 2 | | 11 | Veggies | 2 | | 12 | Terrier | 3 | +------+-------------------------------+--------------------+ 11 rows in set (0.00 sec)
  39. @gabidavila Recursive CTE WITH RECURSIVE tree (depth_level, node, path, node_id)

    AS ( SELECT 1, CAST('root' AS CHAR(255)), CAST('root' AS CHAR(65535)), 0 ) SELECT * FROM tree;
  40. @gabidavila Recursive CTE WITH RECURSIVE tree (depth_level, node, path, node_id)

    AS ( SELECT 1, CAST('root' AS CHAR(255)), CAST('root' AS CHAR(65535)), 0 UNION ALL SELECT tree.depth_level + 1, categories.name, CONCAT_WS('/', tree.path, categories.name), categories.id FROM tree INNER JOIN categories ON tree.node_id = categories.parent_category_id WHERE tree.depth_level < 5 ) SELECT * FROM tree ORDER BY path;
  41. @gabidavila Who never did this? | The most expensive order

    for each user SELECT users.id, users.username, (SELECT id FROM orders WHERE users.id = user_id ORDER BY total LIMIT 1) AS order_id, (SELECT total FROM orders WHERE users.id = user_id ORDER BY total LIMIT 1) AS order_total FROM users ORDER BY users.id LIMIT 10;
  42. @gabidavila LATERAL SELECT users.id, users.username, total_orders.id AS order_id, total_orders.total AS

    order_total FROM users, LATERAL( SELECT id, total FROM orders WHERE users.id = user_id ORDER BY total LIMIT 1 ) AS total_orders ORDER BY users.id LIMIT 10;
  43. @gabidavila Obrigada • About me: gabi.fyi/me • Twitter: @gabidavila (DMs

    are open!) • Office Hours: gabi.tips/office-hours • Host at: gcppodcast.com