OLD but GOLD: SQL is better than you know! - Darkmira Tour 2020

OLD but GOLD: SQL is better than you know!

So you think you know all about SQL? Well, you may be assisted by all of those ORM query builders, but do you know what else SQL can provide to you?

This deck contains examples of Window Functions, CTEs (Common Table Expressions) and other cool features.

It is not Database agnostic since every database implement the SQL standard in their own way, but the features are similar enough if you use the same keywords plus your favorite database engine!

Transcript

1. OLD but GOLD: SQL is better than you know! Gabriela D'Ávila Ferrara (Gabi) @gabidavila Developer Advocate @ Google Cloud gabi.fyi/me
2. @gabidavila Office Hours for EVERYONE at ANY level gabi.tips/office-hours
3. @gabidavila MySQL 8.0.22
4. @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
5. @gabidavila User Management
6. @gabidavila User Management • Reusable Permissions/Roles • Password policy • New Password • Reuse of Password • Expiration • Rotation gabi.fyi/roles-mysql
7. @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
8. @gabidavila Creating a READONLY role CREATE ROLE 'readonly'; GRANT SELECT ON app.* TO 'readonly'; • Create the Role • Define the Privileges gabi.fyi/roles-mysql
9. @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
10. @gabidavila CREATE TABLE
11. @gabidavila Non-deterministic defaults are now supported
12. @gabidavila FUNCTIONS or EXPRESSIONS inside CREATE TABLE
13. @gabidavila That means…
14. @gabidavila Better UUID() support!
15. @gabidavila UUID() Support CREATE TABLE users ( uid binary(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID())), username varchar(255) NOT NULL, PRIMARY KEY (uid) );
16. @gabidavila Demo https://youtu.be/nLd8lgP46C4
17. @gabidavila New Defaults & Variables
18. @gabidavila New default Charset • Default: • 5.7: latin1 • 8.0: utf8mb4 • Improvements: • ➕ Mathematical Equations =·² • • & more SMP (Supplementary Multilingual Plane) Characters
19. @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.
20. @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
21. @gabidavila CHECK Constraint
22. @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.
23. @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.
24. @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) );
25. @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.
26. @gabidavila Indexes
27. @gabidavila Descending Indexes
28. @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.
29. @gabidavila MySQL 8.0
30. @gabidavila Descending Indexes • No longer ignored and forcibly created as ASC • Actually works!!! ALTER TABLE users ADD INDEX ix_username (username DESC);
31. @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;
32. @gabidavila Invisible (?) indexes
33. @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
34. @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:
35. @gabidavila Invisible Indexes SELECT * FROM users WHERE username = 'fancy'; Visible Cost: 0.98 Rows: 1 Invisible Cost: 518,331.25 Rows: 5.04M
36. @gabidavila ALGORITHM=INSTANT
37. @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
38. @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)
39. @gabidavila Window Functions
40. @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
41. @gabidavila
42. @gabidavila Window Functions • Examples: • Enumerate rows - ROW_NUMBER() • Show Aggregated sums - SUM() • Rank results - RANK() • Look at neighboring rows - LEAD(), LAG()
43. @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 )
44. @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 | +---------+---------+-----------+---------------------+
45. @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;
46. @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 | +---------+---------+-----------+---------------------+---------------------+---------------------+
47. @gabidavila Break down window function column # rows preceding LAG(created_at, 1) OVER (ORDER BY created_at)
48. @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;
49. @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;
50. @gabidavila Demo https://youtu.be/PvzUYfpRMEc
51. @gabidavila CTE Common Table Expressions CTE
52. @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
53. @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
54. @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)
55. @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;
56. @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;
57. @gabidavila Subqueries
58. @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;
59. @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;
60. @gabidavila Demo https://youtu.be/_U7hLWUdGRM
61. @gabidavila Thank you! • About me: gabi.fyi/me • Twitter: @gabidavila (DMs are open!) • Office Hours: gabi.tips/office-hours • Host at: gcppodcast.com