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

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!

81603b1fb3b31483780be0700ed183e8?s=128

Gabriela D'Ávila Ferrara

September 11, 2020
Tweet

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 Mentoria de banco de dados gabi.tips/office-hours

  3. @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
  4. @gabidavila "Aquela que fazia as coisas que ninguém queria pagar

    o DBA Oracle pra fazer." Fonte: Twitter
  5. @gabidavila Quantos anos tem o SQL?

  6. @gabidavila

  7. @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
  8. @gabidavila O PHP é qual geração? 1GL, 2GL, 3GL, 4GL

    ou 5GL?
  9. @gabidavila PHP

  10. @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
  11. @gabidavila Paginação!

  12. @gabidavila OFFSET e LIMIT foram contribuições do Rasmus

  13. @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
  14. @gabidavila Assim como a zueira, o SQL não tem LIMITES...

  15. @gabidavila LIMIT é lento • Faz leitura do resultado inteiro

    pra saber o que limitar • Se for uma tabela muito grande... • ROW_NUMBER é uma Window Function
  16. @gabidavila MySQL 8.0.21

  17. @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
  18. @gabidavila Gerenciamento de usuários

  19. @gabidavila User Management • Reusable Permissions/Roles • Password policy •

    New Password • Reuse of Password • Expiration • Rotation gabi.fyi/roles-mysql
  20. @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
  21. @gabidavila Creating a READONLY role CREATE ROLE 'readonly'; GRANT SELECT

    ON app.* TO 'readonly'; • Create the Role • Define the Privileges gabi.fyi/roles-mysql
  22. @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
  23. @gabidavila CREATE TABLE

  24. @gabidavila Resultados não determinísticos são suportados

  25. @gabidavila FUNCTIONS ou EXPRESSIONS dentro do CREATE TABLE

  26. @gabidavila Isso significa...

  27. @gabidavila Suporte a UUID()!

  28. @gabidavila UUID() Support CREATE TABLE users ( uid binary(16) NOT

    NULL DEFAULT (UUID_TO_BIN(UUID())), username varchar(255) NOT NULL, PRIMARY KEY (uid) );
  29. @gabidavila Demo https://youtu.be/nLd8lgP46C4

  30. @gabidavila Novos padrões e variáveis

  31. @gabidavila New default Charset • Default: • 5.7: latin1 •

    8.0: utf8mb4 • Improvements: • ➕ Mathematical Equations =·² • • & more SMP (Supplementary Multilingual Plane) Characters
  32. @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.
  33. @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
  34. @gabidavila CHECK Constraint

  35. @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.
  36. @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.
  37. @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) );
  38. @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.
  39. @gabidavila Índices

  40. @gabidavila Índices decrescentes

  41. @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.
  42. @gabidavila MySQL 8.0

  43. @gabidavila Descending Indexes • No longer ignored and forcibly created

    as ASC • Actually works!!! ALTER TABLE users ADD INDEX ix_username (username DESC);
  44. @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;
  45. @gabidavila Índices "invisíveis" (?)

  46. @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
  47. @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:
  48. @gabidavila Invisible Indexes SELECT * FROM users WHERE username =

    'fancy'; Visible Cost: 0.98 Rows: 1 Invisible Cost: 518,331.25 Rows: 5.04M
  49. @gabidavila ALGORITHM=INSTANT

  50. @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
  51. @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)
  52. @gabidavila Window Functions

  53. @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
  54. @gabidavila

  55. @gabidavila Window Functions • Examples: • Enumerate rows - ROW_NUMBER()

    • Show Aggregated sums - SUM() • Rank results - RANK() • Look at neighboring rows - LEAD(), LAG()
  56. @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 )
  57. @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 | +---------+---------+-----------+---------------------+
  58. @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;
  59. @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 | +---------+---------+-----------+---------------------+---------------------+---------------------+
  60. @gabidavila Break down window function column # rows preceding LAG(created_at,

    1) OVER (ORDER BY created_at)
  61. @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;
  62. @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;
  63. @gabidavila Demo https://youtu.be/PvzUYfpRMEc

  64. @gabidavila CTE Common Table Expressions CTE

  65. @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
  66. @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
  67. @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)
  68. @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;
  69. @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;
  70. @gabidavila Subqueries

  71. @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;
  72. @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;
  73. @gabidavila Demo https://youtu.be/_U7hLWUdGRM

  74. @gabidavila E o Microsoft Access, é banco de dados?

  75. @gabidavila

  76. @gabidavila

  77. @gabidavila Obrigada • About me: gabi.fyi/me • Twitter: @gabidavila (DMs

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