PHPDay 2019 - MySQL 8, not only good, great!

Sick and tired of “X technology is only good for starting out; after you do, move to Y”? Good news - you don’t need to move away, you just need to get in further! In this talk, you’ll learn about improvements in the newest version of the most used database in the world. What are Window Functions? How do you use CTEs? How can the new default encoding help me and what should I look for when upgrading versions? Is MySQL just an OLAP database or there is more to it?

Transcript

1. MySQL 8.0: not only good, it’s GREAT! Gabriela D'Ávila Ferrara (Gabi) @gabidavila Developer Advocate @ Google Cloud gabi.fyi/me
2. @gabidavila MySQL 8.0.16
3. @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
4. @gabidavila User Management
5. @gabidavila User Management • Reusable Permissions/Roles • Password policy • New Password • Reuse of Password • Expiration • Rotation gabi.fyi/roles-mysql
6. @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
7. @gabidavila Creating a READONLY role CREATE ROLE 'readonly'; GRANT SELECT ON app.* TO 'readonly'; • Create the Role • Define the Privileges gabi.fyi/roles-mysql
8. @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
9. @gabidavila InnoDB Performance
10. @gabidavila Transaction Scheduling RDBMS usually works on a FIFO architecture Transaction A Transaction B Transaction C Start End
11. @gabidavila CATS First database in the world to implement
12. @gabidavila Contention Aware Transaction Scheduling
13. @gabidavila CATS vs FIFO | Transactions per Second (TPS) 0 5500 11000 16500 22000 32 64 128 256 512 FIFO CATS Data extracted from MySQLServerTeam website. Transactions per Second x # Clients Higher is better
14. @gabidavila CATS vs FIFO | Latency 0 45 90 135 180 32 64 128 256 512 FIFO CATS Data extracted from MySQLServerTeam website. Mean Latency x # Clients Lower is better
15. @gabidavila CREATE TABLE
16. @gabidavila Non-deterministic defaults are now supported
17. @gabidavila FUNCTIONS or EXPRESSIONS inside CREATE TABLE
18. @gabidavila That means…
19. @gabidavila Better UUID() support!
20. @gabidavila UUID() Support CREATE TABLE users ( uid binary(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID())), username varchar(255) NOT NULL, PRIMARY KEY (uid) );
21. @gabidavila Demo https://youtu.be/nLd8lgP46C4
22. @gabidavila New Defaults & Variables
23. @gabidavila New default Charset • Default: • 5.7: latin1 • 8.0: utf8mb4 • Improvements: • ➕ Mathematical Equations 𝑒=𝑚·𝑐² • 😁 🙄 $ • & more SMP (Supplementary Multilingual Plane) Characters
24. @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.
25. @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
26. @gabidavila CHECK Constraint
27. @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.
28. @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.
29. @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) );
30. @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.
31. @gabidavila Indexes
32. @gabidavila Descending Indexes
33. @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.
34. @gabidavila MySQL 8.0
35. @gabidavila Descending Indexes • No longer ignored and forcibly created as ASC • Actually works!!! ALTER TABLE users ADD INDEX ix_username (username DESC);
36. @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;
37. @gabidavila Invisible (?) indexes
38. @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
39. @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:
40. @gabidavila Invisible Indexes SELECT * FROM users WHERE username = 'fancy'; Visible Cost: 0.98
 Rows: 1 Invisible Cost: 518,331.25
 Rows: 5.04M
41. @gabidavila Demo https://youtu.be/E29OzLzfH-8
42. @gabidavila ALGORITHM=INSTANT
43. @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
44. @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)
45. @gabidavila Window Functions
46. @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
47. @gabidavila
48. @gabidavila Window Functions • Examples: • Enumerate rows - ROW_NUMBER() • Show Aggregated sums - SUM() • Rank results - RANK() • Look at neighboring rows - LEAD(), LAG()
49. @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 )
50. @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 | +---------+---------+-----------+---------------------+
51. @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;
52. @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 | +---------+---------+-----------+---------------------+---------------------+---------------------+
53. @gabidavila Break down window function column # rows preceding LAG(created_at, 1) OVER (ORDER BY created_at)
54. @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;
55. @gabidavila 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 WHERE user_id = 654321 WINDOW dates AS (ORDER BY created_at) ORDER BY created_at LIMIT 10; Named Windows!
56. @gabidavila Demo https://youtu.be/PvzUYfpRMEc
57. @gabidavila CTE
 Common Table Expressions CTE
58. @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
59. @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
60. @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)
61. @gabidavila Demo https://youtu.be/rOzM5YQhtfg
62. @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;
63. @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;
64. @gabidavila Subqueries
65. @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;
66. @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;
67. @gabidavila Demo https://youtu.be/_U7hLWUdGRM
68. @gabidavila Thank you! • About me: gabi.fyi/me • Twitter: @gabidavila (DMs are open!) • Host at: gcppodcast.com