DPC18 - OMG MySQL 8.0 is out! are we there yet?

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? We’ll also talk about new JSON features and extended UUID support! Be prepared to drink from the firehose of what’s new and awesome about MySQL 8.0.

Transcript

1. OMG MySQL 8.0 is out! Are we there yet? Gabriela Ferrara @gabidavila http://gabriela.io/feedback | https://joind.in/talk/2e783
2. @gabidavila Gabi 
 Developer Advocate @ Google
3. @gabidavila MySQL 8.0.11
4. @gabidavila Are we there yet? 🤔 • 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 can we go from here?
5. @gabidavila What Happened to 6.0 and 7.0?
6. @gabidavila Topics • New Defaults & Variables • InnoDB Performance • Window Functions • CTE - Common Table Expressions
7. @gabidavila New Defaults & Variables
8. @gabidavila New default Charset • Default: • 5.7: latin1 • 8.0: utf8mb4 • Improvements: • ➕ Mathematical Equations 𝑒=𝑚·𝑐² • 😁 🙄 % • & more SMP (Supplementary Multilingual Plane) Characters
9. @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.
10. @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
11. @gabidavila InnoDB Performance
12. @gabidavila Transaction Scheduling RDBMS usually works on a FIFO architecture Transaction A Transaction B Transaction C Start End
13. @gabidavila CATS First database in the world to implement
14. @gabidavila Contention Aware Transaction Scheduling
15. @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
16. @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
17. @gabidavila Indexes
18. @gabidavila DESCRIBE `lines`; type null extra id int(11) NO AUTO_INCREMENT number int(11) YES timpestamp_in_ms timestamp YES character_id int(11) NO episode_id int(11) NO location_id int(11) NO character_line text YES word_count int(11) YES
19. @gabidavila Invisible Indexes • Not used by the optimizer • Visible by default, to create an invisible index: ALTER TABLE lines ADD INDEX ix_word_count (word_count) INVISIBLE; ALTER TABLE lines ALTER INDEX ix_word_count INVISIBLE; ALTER TABLE lines ALTER INDEX ix_word_count VISIBLE; • Toggle visibility:
20. @gabidavila Invisible Indexes SELECT * FROM lines WHERE word_count > 25 ORDER BY word_count DESC; Invisible Query cost: 59175.37 Visible Query cost: 2847.86
21. @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.
22. @gabidavila MySQL 8.0 has it!!!
23. @gabidavila Descending Indexes • No longer ignored and forcibly created as ASC ALTER TABLE lines ADD INDEX ix_word_count (word_count DESC);
24. @gabidavila Descending Indexes Can be scanned as intended or backwards SELECT * FROM lines WHERE word_count > 25 ORDER BY word_count DESC; -- OR WITH THE SAME COST SELECT * FROM lines WHERE word_count > 25 ORDER BY word_count;
25. @gabidavila DEMO
26. @gabidavila More cool features!
27. @gabidavila Window Functions
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 `episodes`; CREATE TABLE `episodes` ( `id` int(11) NOT NULL, `title` varchar(255), `episode_date` date, `season` int(11), `number_in_season` int(11), `number_in_series` int(11), `million_viewers` decimal(10, 2), `imdb_rating` decimal(10, 2), `imdb_votes` decimal(10, 2), `image_url` text, `video_url` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
31. @gabidavila SELECT … FROM `episodes` LIMIT 10; +----+--------------+-----------------------------------+--------+------------------+-----------------+ | id | episode_date | title | season | number_in_season | million_viewers | +----+--------------+-----------------------------------+--------+------------------+-----------------+ | 1 | 1989-12-17 | Simpsons Roasting on an Open Fire | 1 | 1 | 26.70 | | 2 | 1990-01-14 | Bart the Genius | 1 | 2 | 24.50 | | 3 | 1990-01-21 | Homer's Odyssey | 1 | 3 | 27.50 | | 4 | 1990-01-28 | There's No Disgrace Like Home | 1 | 4 | 20.20 | | 5 | 1990-02-04 | Bart the General | 1 | 5 | 27.10 | | 6 | 1990-02-11 | Moaning Lisa | 1 | 6 | 27.40 | | 7 | 1990-02-18 | The Call of the Simpsons | 1 | 7 | 27.60 | | 8 | 1990-02-25 | The Telltale Head | 1 | 8 | 28.00 | | 9 | 1990-03-18 | Life on the Fast Lane | 1 | 9 | 33.50 | | 10 | 1990-03-25 | Homer's Night Out | 1 | 10 | 30.30 | +----+--------------+-----------------------------------+--------+------------------+-----------------+ 10 rows in set (0.00 sec)
32. @gabidavila Previous and Next episodes | LAG and LEAD SELECT id, title, LAG(episode_date, 1) OVER (ORDER BY episode_date) AS previous_episode, episode_date, LEAD(episode_date, 1) OVER (ORDER BY episode_date) AS next_episode, million_viewers FROM episodes;
33. @gabidavila SELECT … FROM `episodes` LIMIT 10; +----+-----------------------------------+------------------+--------------+--------------+-----------------+ | id | title | previous_episode | episode_date | next_episode | million_viewers | +----+-----------------------------------+------------------+--------------+--------------+-----------------+ | 1 | Simpsons Roasting on an Open Fire | NULL | 1989-12-17 | 1990-01-14 | 26.70 | | 2 | Bart the Genius | 1989-12-17 | 1990-01-14 | 1990-01-21 | 24.50 | | 3 | Homer's Odyssey | 1990-01-14 | 1990-01-21 | 1990-01-28 | 27.50 | | 4 | There's No Disgrace Like Home | 1990-01-21 | 1990-01-28 | 1990-02-04 | 20.20 | | 5 | Bart the General | 1990-01-28 | 1990-02-04 | 1990-02-11 | 27.10 | | 6 | Moaning Lisa | 1990-02-04 | 1990-02-11 | 1990-02-18 | 27.40 | | 7 | The Call of the Simpsons | 1990-02-11 | 1990-02-18 | 1990-02-25 | 27.60 | | 8 | The Telltale Head | 1990-02-18 | 1990-02-25 | 1990-03-18 | 28.00 | | 9 | Life on the Fast Lane | 1990-02-25 | 1990-03-18 | 1990-03-25 | 33.50 | | 10 | Homer's Night Out | 1990-03-18 | 1990-03-25 | 1990-04-15 | 30.30 | +----+-----------------------------------+------------------+--------------+--------------+-----------------+ 10 rows in set (0.01 sec)
34. @gabidavila Break down LAG(episode_date, 1) OVER (ORDER BY episode_date) window function column # rows preceding
35. @gabidavila Repetition? SELECT id, title, LAG(episode_date, 1) OVER (ORDER BY episode_date) AS previous_episode, episode_date, LEAD(episode_date, 1) OVER (ORDER BY episode_date) AS next_episode, million_viewers FROM episodes;
36. @gabidavila SELECT id, title, LAG(episode_date, 1) OVER (dates) AS previous_episode, episode_date, LEAD(episode_date, 1) OVER (dates) AS next_episode, million_viewers FROM episodes WINDOW dates AS (ORDER BY episode_date); Named Windows!
37. @gabidavila DEMO Rank episodes by million_viewers
38. @gabidavila CTE
 Common Table Expressions
39. @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
40. @gabidavila Rank episodes by million_viewers SELECT id, season, number_in_season AS '# in season', title, episode_date, million_viewers, RANK() OVER (season_viewers) AS episode_rank FROM episodes WINDOW season_viewers AS (PARTITION BY season ORDER BY million_viewers DESC)
41. @gabidavila DEMO Filter ranked episodes
42. @gabidavila Recursive CTE • Useful with hierarchical data • The Recipe is: • Base query comes first • Second query comes after a UNION statement • And the stop condition should be on the recursive call
43. @gabidavila DEMO Fibonacci
44. @gabidavila Fibonacci WITH RECURSIVE fibonacci(recursion_level, fibonacci_number, next_number) AS ( # Base Case SELECT 0 AS recursion_level, 0 AS fibonacci_number, 1 AS next_number UNION ALL # Recursion query SELECT recursion_level + 1, next_number, fibonacci_number + next_number FROM fibonacci # Stopping condition WHERE recursion_level < 10 ) SELECT * FROM fibonacci;
45. @gabidavila Fibonacci WITH RECURSIVE fibonacci(recursion_level, fibonacci_number, next_number) AS ( # Base Case SELECT 0 AS recursion_level, 0 AS fibonacci_number, 1 AS next_number UNION ALL # Recursion query SELECT recursion_level + 1, next_number, fibonacci_number + next_number FROM fibonacci # Stopping condition WHERE recursion_level < 10 ) SELECT * FROM fibonacci;
46. @gabidavila Fibonacci +-----------------+------------------+-------------+ | recursion_level | fibonacci_number | next_number | +-----------------+------------------+-------------+ | 0 | 0 | 1 | | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 3 | | 4 | 3 | 5 | | 5 | 5 | 8 | | 6 | 8 | 13 | | 7 | 13 | 21 | | 8 | 21 | 34 | | 9 | 34 | 55 | | 10 | 55 | 89 | +-----------------+------------------+-------------+ 11 rows in set (0.00 sec) Base case Recursive Query
47. @gabidavila Data Dictionary and 
 Atomic DDL
48. @gabidavila Data Dictionary • No more meta-data on MyISAM tables into the mysql schema • No more meta-data on files: .FRM, .PAR, .TRG, .TRN • System tables still exists, but in InnoDB tables
49. @gabidavila Data Dictionary • Accessible through the information_schema: • implemented as views over Data Dictionary • uses the optimizer to read the meta-data from the Data Dictionary • up to 100x faster
50. @gabidavila Atomic DDL • A DDL operation consists of: • Data Dictionary updates • Storage Engine Operations • Binary logs writes
51. @gabidavila Atomic DDL • Either all DDL works or no change is made • DDL Executed as an internal InnoDB Transactions • No more crashes when executing DDL in multiple objects (DROP TABLES, TRUNCATE TABLE)
52. @gabidavila Other Bits
53. @gabidavila Other MySQL 8.0 Bits • Data Dictionary • Users now can have ROLES • Instantaneously add columns (ALGORITHM=INSTANT) • CATS instead of FIFO for high concurrency • JSON partial update • Better Geospatial support • Better UUID support • Use MySQL without SQL (through MySQL Shell or X)
54. @gabidavila Questions?
55. @gabidavila Thank you! Please rate my talk! https:/ /joind.in/talk/2e783 or gabriela.io/feedback