RivieraJUG - MySQL 8.0 : What's new for developers

This session was delivered at RivieraJUG, Nice, France on September 2022.

During this lecture, I presented some of the key new features that are very useful for an audience of developers. Such as the new GIPK mode, Hash Joins, Histograms, CTEs and Window functions.
Then focused on the new MySQL Store that allows users to store and retrieve data out of the database without a single line of SQL !

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL RivieraJUG - September 2022 MySQL 8.0 What's new for developers ?
2. Who am I ? about.me/lefred Copyright @ 2022 Oracle and/or its affiliates. 2
3. Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.20 devops believer living in Belgium h ps://lefred.be Copyright @ 2022 Oracle and/or its affiliates. 3
4. New Volcano Iterator MySQL Optimizer Refactoring Copyright @ 2022 Oracle and/or its affiliates. 4
5. Modular Easy to Extend Each iterator encapsulates one operation Same interface for all iterators All operations can be connected MySQL New Iterator Executor Copyright @ 2022 Oracle and/or its affiliates. 5
6. EXPLAIN FORMAT=TREE Copyright @ 2022 Oracle and/or its affiliates. 6
7. EXPLAIN ANALYZE Instruments and executes the query Estimated cost Actual execution statistics Time to return rst row Time to return all rows Number of rows returned Number of loops Uses the new tree output format also available in EXPLAIN Copyright @ 2022 Oracle and/or its affiliates. 7
8. EXPLAIN ANALYZE Copyright @ 2022 Oracle and/or its affiliates. 8
9. EXPLAIN ANALYZE Copyright @ 2022 Oracle and/or its affiliates. 9
10. EXPLAIN ANALYZE Copyright @ 2022 Oracle and/or its affiliates. 10
11. EXPLAIN ANALYZE Copyright @ 2022 Oracle and/or its affiliates. 11
12. EXPLAIN ANALYZE Copyright @ 2022 Oracle and/or its affiliates. 12
13. Hash Joins bye bye Block Nested Loop Copyright @ 2022 Oracle and/or its affiliates. 13
14. Hash Join Typically faster than nested loop for large result sets In-memory if possible Spill to disk if necessary Used in all types of joins (inner, equi, ...) Replaces BNL in query plans In MySQL 8.0.20 and later, hash joins are used for outer joins (including antijoins and semijoins) as well. Copyright @ 2022 Oracle and/or its affiliates. 14
15. Hash Join: performance Copyright @ 2022 Oracle and/or its affiliates. 15
16. MySQL 8.0 one giant leap for SQL Copyright @ 2022 Oracle and/or its affiliates. 16
17. "This is a landmark release as MySQL eventually evolved beyond SQL-92 and the purely relational dogma. Among a few other standard SQL features, MySQL now supports window functions (over) and common table expressions (with). Without a doubt, these are the two most important post-SQL-92 features.= MySQL 8.0: one giant leap for SQL Copyright @ 2022 Oracle and/or its affiliates. 17
18. A CTE is just like a derived table, but its declaration is put before the query block instead of in the FROM clause: be er readability can be referenced multiple times can refer to other CTEs improved performance Non-recursive WITH cte AS (subquery) SELECT ... FROM cte, t1 ... Recursive WITH RECURSIVE cte AS ( SELECT ... FROM table_name UNION [DISTRINCT|ALL] SELECT ... FROM cte, table_name) SELECT ... FROM cte; SQL: Common Table Expression (WITH clause) Copyright @ 2022 Oracle and/or its affiliates. 18
19. Credits: @MarkusWinand - @ModernSQL SQL: RECURSION / CTEs (WITH clause) Copyright @ 2022 Oracle and/or its affiliates. 19
20. SQL: LATERAL DERIVED TABLES Can refer to other tables in the same FROM clause Sometimes referred to as the SQL "for each" equivalent SELECT ... FROM t1, LATERAL ( SELECT ... FROM ... WHERE ... = t1.col ) AS derived, t2 ... Copyright @ 2022 Oracle and/or its affiliates. 20
21. Credits: @MarkusWinand - @ModernSQL SQL: LATERAL DERIVED TABLES Copyright @ 2022 Oracle and/or its affiliates. 21
22. SQL: Analytical / Window Functions (OVER clause) A window function performs a calculation across a set of rows that are related to the current row, similar to an aggregate function. But unlike aggregate functions, a window function does not cause rows to become grouped into a single output row. Window functions can access values of other rows "in the vicinity" of the current row. Copyright @ 2022 Oracle and/or its affiliates. 22
23. Credits: @MarkusWinand - @ModernSQL SQL: Analytical / Window Functions Copyright @ 2022 Oracle and/or its affiliates. 23
24. Credits: @MarkusWinand - @ModernSQL SQL: Analytical / Window Functions Copyright @ 2022 Oracle and/or its affiliates. 24
25. Credits: @MarkusWinand - @ModernSQL SQL: JSON_TABLE From JSON Document to SQL Table Copyright @ 2022 Oracle and/or its affiliates. 25
26. Credits: @MarkusWinand - @ModernSQL SQL: JSON_TABLE From JSON Document to SQL Table Copyright @ 2022 Oracle and/or its affiliates. 26
27. This function is described in SQL 2016, chapter 6.27 and is also implemented in: Oracle SQL Server DB2 SQL: JSON_VALUE Copyright @ 2022 Oracle and/or its affiliates. 27
28. MySQL > TABLE t; +----+--------+------+ | id | date | num | +----+--------+------+ | 1 | 202201 | 3363 | | 2 | 202202 | 5363 | | 3 | 202203 | 4344 | | 4 | 202204 | 1404 | | 5 | 202205 | 2300 | +----+--------+------+ 5 rows in set (0.0079 sec) MySQL > INSERT INTO t VALUES ROW(0, 202206, 3100), ROW(0, 202207, 2456); MySQL > TABLE t LIMIT 2 OFFSET 5; +----+--------+------+ | id | date | num | +----+--------+------+ | 6 | 202206 | 3100 | | 7 | 202207 | 2456 | +----+--------+------+ SQL: TVC Support Support explicit tables clauses and table value constructors according the SQL standard: Copyright @ 2022 Oracle and/or its affiliates. 28
29. SQL: FUNCTIONAL INDEXES Index over an expression CREATE TABLE t1 (col1 INT, col2 INT); CREATE INDEX idx1 ON t1 ((col1 + col2), (col1 - col2), col1) ; Document content, e.g. JSON array CREATE TABLE lottery (data JSON); CREATE INDEX ticket_idx ON lottery ((CAST(data->'$.lottery_tickets' AS UNSIGNED INT ARRAY))) ; Copyright @ 2022 Oracle and/or its affiliates. 29
30. SQL: INVISIBLE INDEXES Indexes are "hidden" to the MySQL Optimizer Not the same as "disabled indexes" Contents are fully up to date and maintained by DML Two use cases: Soft Delete: What will happen if I delete this index? Staged Rollout: I will create this index over night and make it visible when I am at work tomorrow Copyright @ 2022 Oracle and/or its affiliates. 30
31. SQL: CHECK CONSTRAINT Standard SQL Syntax [ CONSTRAINT [symbol] ] CHECK ( condition) [ [ NOT ] ENFORCED ] Example CREATE TABLE t1 ( c1 INTEGER CONSTRAINT c1_chk CHECK (c1 > 0) , c2 INTEGER CONSTRAINT c2_chk CHECK (c2 > 0) , CONSTRAINT c1_c2_chk CHECK (c1 + c2 < 9999) ); Copyright @ 2022 Oracle and/or its affiliates. 31
32. SQL: Expressions as DEFAULT Values No longer limited to literal values CREATE TABLE t2 (a INT, b INT, c INT DEFAULT (a+b) ); CREATE TABLE t3 (a INT, b INT, c POINT DEFAULT (POINT(0,0)) ); CREATE TABLE t4 (a INT, b INT, c JSON DEFAULT (8[]9) ); Useful for types without literal values GEOMETRY, POINT, LINESTRING, POLYGON, ... Copyright @ 2022 Oracle and/or its affiliates. 32
33. SELECT * FROM tickets WHERE id IN (1,2,3,4) AND order_id IS NULL FOR UPDATE NOWAIT; SELECT * FROM tickets WHERE id IN (1,2,3,4) AND order_id IS NULL FOR UPDATE SKIP LOCKED; SQL: NOWAIT & SKIP LOCKED Copyright @ 2022 Oracle and/or its affiliates. 33
34. SELECT * FROM tickets WHERE id IN (1,2,3,4) AND order_id IS NULL FOR UPDATE NOWAIT; SELECT * FROM tickets WHERE id IN (1,2,3,4) AND order_id IS NULL FOR UPDATE SKIP LOCKED; Error immediately if a row is already locked Non deterministically skip over locked rows SQL: NOWAIT & SKIP LOCKED Copyright @ 2022 Oracle and/or its affiliates. 33
35. MySQL 8.0 GIPK Mode make the DBAs happy ! Copyright @ 2022 Oracle and/or its affiliates. 34
36. InnoDB Primary Keys, Invisible column and GIPK For InnoDB, a Primary Key is required and a good one is even be er ! Copyright @ 2022 Oracle and/or its affiliates. 35
37. InnoDB Primary Keys, Invisible column and GIPK For InnoDB, a Primary Key is required and a good one is even be er ! Some theory InnoDB stores data in table spaces. The records are stored and sorted using the clustered index (PK). Copyright @ 2022 Oracle and/or its affiliates. 35
38. InnoDB Primary Keys, Invisible column and GIPK For InnoDB, a Primary Key is required and a good one is even be er ! Some theory InnoDB stores data in table spaces. The records are stored and sorted using the clustered index (PK). All secondary indexes also contain the primary key as the right-most column in the index (even if this is not exposed). That means when a secondary index is used to retrieve a record, two indexes are used: rst the secondary one pointing to the primary key that will be used to nally retrieve the record. Copyright @ 2022 Oracle and/or its affiliates. 35
39. InnoDB Primary Key (2) So, the primary key impact how the values are inserted and the size of the secondary indexes. A non sequential PK can lead to many random IOPS. Copyright @ 2022 Oracle and/or its affiliates. 36
40. Also, it's more and more common to use application that generates complete random primary keys...that means if the Primary Key is not sequential, InnoDB will have to heavily re-balance all the pages on inserts. InnoDB Primary Key (2) So, the primary key impact how the values are inserted and the size of the secondary indexes. A non sequential PK can lead to many random IOPS. Copyright @ 2022 Oracle and/or its affiliates. 36
41. InnoDB Primary Key (3) If we compare the same load (inserts) when using an auto_increment integer as Primary Key, we can see that only the latest pages are recently touched: Generated with h ps://github.com/jeremycole/innodb_ruby from @jeremycole Copyright @ 2022 Oracle and/or its affiliates. 37
42. InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. Copyright @ 2022 Oracle and/or its affiliates. 38
43. InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. When no primary key is de ned, the rst unique not null key is used. And if none is available, InnoDB will create an hidden primary key (6 bytes). Copyright @ 2022 Oracle and/or its affiliates. 38
44. InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. When no primary key is de ned, the rst unique not null key is used. And if none is available, InnoDB will create an hidden primary key (6 bytes). The problem with such key is that you don’t have any control on it and worse, this value is global to all tables without primary keys and can be a contention problem if you perform multiple simultaneous writes on such tables (dict_sys->mutex). Copyright @ 2022 Oracle and/or its affiliates. 38
45. InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. When no primary key is de ned, the rst unique not null key is used. And if none is available, InnoDB will create an hidden primary key (6 bytes). The problem with such key is that you don’t have any control on it and worse, this value is global to all tables without primary keys and can be a contention problem if you perform multiple simultaneous writes on such tables (dict_sys->mutex). And if you plan for High Availability, tables without Primary Key are not supported ! Copyright @ 2022 Oracle and/or its affiliates. 38
46. InnoDB Primary Key ? No Key ! (2) Luckily since MySQL 8.0.23 there is a solution: Invisible Column ! Copyright @ 2022 Oracle and/or its affiliates. 39
47. InnoDB Primary Key ? No Key ! (2) Luckily since MySQL 8.0.23 there is a solution: Invisible Column ! You can now add an invisible auto_increment Primary Key to a table not having any Primary Key ! Copyright @ 2022 Oracle and/or its affiliates. 39
48. InnoDB Primary Key ? No Key ! (2) To identify those tables, run the following SQL statement: SELECT SELECT tables tables. .table_schema table_schema , , tables tables. .table_name table_name , , tables tables. .engine engine FROM FROM information_schema information_schema. .tables tables LEFT LEFT JOIN JOIN ( ( SELECT SELECT table_schema table_schema , , table_name table_name FROM FROM information_schema information_schema. .statistics statistics GROUP GROUP BY BY table_schema table_schema, , table_name table_name, , index_name index_name HAVING HAVING SUM SUM( ( CASE CASE WHEN WHEN non_unique non_unique = = 0 0 AND AND nullable nullable != != 'YES' 'YES' then then 1 1 else else 0 0 end end ) ) = = count count( (* *) ) ) ) puks puks ON ON tables tables. .table_schema table_schema = = puks puks. .table_schema table_schema AND AND tables tables. .table_name table_name = = puks puks. .table_name table_name WHERE WHERE puks puks. .table_name table_name IS IS null null AND AND tables tables. .table_type table_type = = 'BASE TABLE' 'BASE TABLE' AND AND Engine Engine= ="InnoDB" "InnoDB"; ; Copyright @ 2022 Oracle and/or its affiliates. 40
49. InnoDB Primary Key ? No Key ! (2) To identify those tables, run the following SQL statement: SELECT SELECT tables tables. .table_schema table_schema , , tables tables. .table_name table_name , , tables tables. .engine engine FROM FROM information_schema information_schema. .tables tables LEFT LEFT JOIN JOIN ( ( SELECT SELECT table_schema table_schema , , table_name table_name FROM FROM information_schema information_schema. .statistics statistics GROUP GROUP BY BY table_schema table_schema, , table_name table_name, , index_name index_name HAVING HAVING SUM SUM( ( CASE CASE WHEN WHEN non_unique non_unique = = 0 0 AND AND nullable nullable != != 'YES' 'YES' then then 1 1 else else 0 0 end end ) ) = = count count( (* *) ) ) ) puks puks ON ON tables tables. .table_schema table_schema = = puks puks. .table_schema table_schema AND AND tables tables. .table_name table_name = = puks puks. .table_name table_name WHERE WHERE puks puks. .table_name table_name IS IS null null AND AND tables tables. .table_type table_type = = 'BASE TABLE' 'BASE TABLE' AND AND Engine Engine= ="InnoDB" "InnoDB"; ; Copyright @ 2022 Oracle and/or its affiliates. +--------------+-----------------+--------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | +--------------+-----------------+--------+ | slack | some_table | InnoDB | | test | default_test | InnoDB | | test | t1 | InnoDB | | world | orders | InnoDB | | world | sales | InnoDB | | dbt3 | time_statistics | InnoDB | +--------------+-----------------+--------+ 40
50. InnoDB Primary Key ? No Key ! (3) Another nice query to identify the tables using an hidden clustered index is to lookup for GEN_CLUST_INDEX like this: SELECT SELECT i i. .TABLE_ID TABLE_ID, , t t. .NAME NAME FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_INDEXES i INNODB_INDEXES i JOIN JOIN INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_TABLES t INNODB_TABLES t ON ON ( (i i. .TABLE_ID TABLE_ID = = t t. .TABLE_ID TABLE_ID) ) WHERE WHERE i i. .NAME NAME= ='GEN_CLUST_INDEX' 'GEN_CLUST_INDEX'; ; see h ps://elephantdolphin.blogspot.com/2021/08/ nding-your-hidden-innodb-primary.html Copyright @ 2022 Oracle and/or its affiliates. 41
51. InnoDB Primary Key ? No Key ! (3) Another nice query to identify the tables using an hidden clustered index is to lookup for GEN_CLUST_INDEX like this: SELECT SELECT i i. .TABLE_ID TABLE_ID, , t t. .NAME NAME FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_INDEXES i INNODB_INDEXES i JOIN JOIN INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_TABLES t INNODB_TABLES t ON ON ( (i i. .TABLE_ID TABLE_ID = = t t. .TABLE_ID TABLE_ID) ) WHERE WHERE i i. .NAME NAME= ='GEN_CLUST_INDEX' 'GEN_CLUST_INDEX'; ; see h ps://elephantdolphin.blogspot.com/2021/08/ nding-your-hidden-innodb-primary.html Copyright @ 2022 Oracle and/or its affiliates. +----------+----------------------+ | TABLE_ID | NAME | +----------+----------------------+ | 1198 | slack/some_table | | 1472 | test/default_test | | 1492 | test/t1 | | 2018 | world/orders | | 2019 | world/sales | | 2459 | dbt3/time_statistics | +----------+----------------------+ 41
52. InnoDB Primary Key ? No Key ! (4) Copyright @ 2022 Oracle and/or its affiliates. 42
53. InnoDB Primary Key ? No Key ! (4) Perfect for replication ! Copyright @ 2022 Oracle and/or its affiliates. 42
54. InnoDB Primary Key ? No Key ! (5) Copyright @ 2022 Oracle and/or its affiliates. 43
55. InnoDB GIPK mode Since MySQL 8.0.30, MySQL supports generated invisible primary keys when running in GIPK mode ! GIPK mode is controlled by the sql_generate_invisible_primary_key server system variable. Copyright @ 2022 Oracle and/or its affiliates. 44
56. InnoDB GIPK mode Since MySQL 8.0.30, MySQL supports generated invisible primary keys when running in GIPK mode ! GIPK mode is controlled by the sql_generate_invisible_primary_key server system variable. When MySQL is running in GIPK mode, a primary key is added to a table by the server, the column and key name is always my_row_id. Copyright @ 2022 Oracle and/or its affiliates. 44
57. InnoDB GIPK mode - example MySQL > SELECT @@sql_generate_invisible_primary_key; +--------------------------------------+ | @@sql_generate_invisible_primary_key | +--------------------------------------+ | 1 | +--------------------------------------+ MySQL > CREATE TABLE rivierajug (name varchar(20), beers int unsigned); MySQL > INSERT INTO rivierajug VALUES ('Yannis', 0), ('lefred',1); Query OK, 2 rows affected (0.0073 sec) MySQL > SELECT * FROM rivierajug; +--------+-------+ | name | beers | +--------+-------+ | Yannis | 0 | | lefred | 1 | +--------+-------+ 2 rows in set (0.0002 sec) Copyright @ 2022 Oracle and/or its affiliates. 45
58. InnoDB GIPK mode - example (2) MySQL > SHOW CREATE TABLE rivierajug\G *************************** 1. row *************************** Table: rivierajug Create Table: CREATE TABLE `rivierajug` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `name` varchar(20) DEFAULT NULL, `beers` int unsigned DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Copyright @ 2022 Oracle and/or its affiliates. 46
59. InnoDB GIPK mode - example (2) MySQL > SHOW CREATE TABLE rivierajug\G *************************** 1. row *************************** Table: rivierajug Create Table: CREATE TABLE `rivierajug` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `name` varchar(20) DEFAULT NULL, `beers` int unsigned DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MySQL > SELECT *, my_row_id FROM rivierajug; +--------+-------+-----------+ | name | beers | my_row_id | +--------+-------+-----------+ | Yannis | 0 | 1 | | lefred | 1 | 2 | +--------+-------+-----------+ 2 rows in set (0.0003 sec) Copyright @ 2022 Oracle and/or its affiliates. 46
60. InnoDB GIPK mode - example (3) It's also possible to hide it completely (for some legacy application that could rely on information_schema and SHOW CREATE TABLE): MySQL > SET show_gipk_in_create_table_and_information_schema = 0; MySQL > SHOW CREATE TABLE rivierajug\G *************************** 1. row *************************** Table: rivierajug Create Table: CREATE TABLE `rivierajug` ( `name` varchar(20) DEFAULT NULL, `beers` int unsigned DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Copyright @ 2022 Oracle and/or its affiliates. 47
61. InnoDB GIPK mode - example (4) MySQL > SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "rivierajug"; +-------------+------------------+-----------+------------+ | COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY | +-------------+------------------+-----------+------------+ | beers | 3 | int | | | name | 2 | varchar | | +-------------+------------------+-----------+------------+ MySQL > SET show_gipk_in_create_table_and_information_schema = 1; MySQL > SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "rivierajug"; +-------------+------------------+-----------+------------+ | COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY | +-------------+------------------+-----------+------------+ | beers | 3 | int | | | my_row_id | 1 | bigint | PRI | | name | 2 | varchar | | +-------------+------------------+-----------+------------+ Copyright @ 2022 Oracle and/or its affiliates. 48
62. . generally they are completely random and cause clustered index re-banlancing . they are included in each secondary indexes (consuming disk and memory) InnoDB Primary Key - What about UUID ? There are 2 major problems with UUID's as Primary Key: Copyright @ 2022 Oracle and/or its affiliates. 49
63. InnoDB Primary Key - What about UUID ? (2) Example: MySQL > CREATE TABLE rivierajug2 ( uuid VARCHAR(36) DEFAULT (UUID()) PRIMARY KEY, name VARCHAR(20), beers int unsigned); MySQL > SELECT * FROM rivierajug2; +--------------------------------------+---------+-------+ | uuid | name | beers | +--------------------------------------+---------+-------+ | 17cd1188-1fa0-11ed-ba36-c8cb9e32df8e | Yannis | 0 | | 17cd12e2-1fa0-11ed-ba36-c8cb9e32df8e | lefred | 1 | | 478368a0-1fa0-11ed-ba36-c8cb9e32df8e | Valérie | 0 | | 47836a77-1fa0-11ed-ba36-c8cb9e32df8e | Laurent | 1 | +--------------------------------------+---------+-------+ Copyright @ 2022 Oracle and/or its affiliates. 50
64. InnoDB Primary Key - What about UUID ? (3) Let's insert 2 new records: MySQL > INSERT INTO rivierajug2 (name, beers) VALUES ("Benoît",1), ("Sarah",5); Query OK, 2 rows affected (0.0069 sec) Copyright @ 2022 Oracle and/or its affiliates. 51
65. InnoDB Primary Key - What about UUID ? (3) Let's insert 2 new records: MySQL > INSERT INTO rivierajug2 (name, beers) VALUES ("Benoît",1), ("Sarah",5); Query OK, 2 rows affected (0.0069 sec) MySQL > SELECT * FROM rivierajug2; +--------------------------------------+---------+-------+ | uuid | name | beers | +--------------------------------------+---------+-------+ | 17cd1188-1fa0-11ed-ba36-c8cb9e32df8e | Yannis | 0 | | 17cd12e2-1fa0-11ed-ba36-c8cb9e32df8e | lefred | 1 | | 36f1ce9a-1fa1-11ed-ba36-c8cb9e32df8e | Benoît | 1 | | 36f1d158-1fa1-11ed-ba36-c8cb9e32df8e | Sarah | 5 | | 478368a0-1fa0-11ed-ba36-c8cb9e32df8e | Valérie | 0 | | 47836a77-1fa0-11ed-ba36-c8cb9e32df8e | Laurent | 1 | +--------------------------------------+---------+-------+ Copyright @ 2022 Oracle and/or its affiliates. 51
66. InnoDB Primary Key - What about UUID ? (4) OUPS ! We have rebalanced the clustered index ! What does that mean again ?? Copyright @ 2022 Oracle and/or its affiliates. 52
67. InnoDB Primary Key - What about UUID ? (4) OUPS ! We have rebalanced the clustered index ! What does that mean again ?? Let me try to explain this with this high level and simpli ed example: Copyright @ 2022 Oracle and/or its affiliates. 52
68. Let's imagine one InnoDB Page can store 4 records (this is just a ction), and we have inserted some records using a random Primary Key: InnoDB Primary Key - What about UUID ? (5) OUPS ! We have rebalanced the clustered index ! Copyright @ 2022 Oracle and/or its affiliates. 53
69. Let's imagine one InnoDB Page can store 4 records (this is just a ction), and we have inserted some records using a random Primary Key: And now we insert a new record and the Primary Key is AA: All pages were modi ed to <rebalance= the clustered index ! Imagine if this was a 4TB table !! InnoDB Primary Key - What about UUID ? (5) OUPS ! We have rebalanced the clustered index ! Copyright @ 2022 Oracle and/or its affiliates. 53
70. InnoDB Primary Key - What about UUID ? (6) And just for info, each entry in the Primary Key Index could take up to 146 bytes(*) : MySQL > EXPLAIN SELECT * FROM rivierajug2 WHERE uuid='36f1d158-1fa1-11ed-ba36-c8cb9e32df8e'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rivierajug2 partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 146 ref: const rows: 1 filtered: 100 Extra: NULL (*) worse case when using characters using 4 bytes each (uft8mb4) Copyright @ 2022 Oracle and/or its affiliates. 54
71. InnoDB Primary Key - What about UUID ? (7) Recommended solution . use a smaller datatype: BINARY(16) . store the UUID sequentially: UUID_TO_BIN(..., swap_flag) The time-low and time-high parts (the rst and third groups of hexadecimal digits, respectively) are swapped. Copyright @ 2022 Oracle and/or its affiliates. 55
72. InnoDB Primary Key - What about UUID ? (8) Recommended solution - example MySQL > CREATE TABLE rivierajug3 ( uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY, name VARCHAR(20), beers int unsigned); MySQL > SELECT * FROM rivierajug3; +------------------------------------+--------+-------+ | uuid | name | beers | +------------------------------------+--------+-------+ | 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 | | 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 | +------------------------------------+--------+-------+ Copyright @ 2022 Oracle and/or its affiliates. 56
73. InnoDB Primary Key - What about UUID ? (8) Recommended solution - example MySQL > CREATE TABLE rivierajug3 ( uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY, name VARCHAR(20), beers int unsigned); MySQL > SELECT * FROM rivierajug3; +------------------------------------+--------+-------+ | uuid | name | beers | +------------------------------------+--------+-------+ | 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 | | 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 | +------------------------------------+--------+-------+ MySQL > SELECT BIN_TO_UUID(uuid,1), name, beers FROM rivierajug3; +--------------------------------------+--------+-------+ | BIN_TO_UUID(uuid,1) | name | beers | +--------------------------------------+--------+-------+ | 633ecb6c-1f9f-11ed-ba36-c8cb9e32df8e | Yannis | 0 | | 633ecd6f-1f9f-11ed-ba36-c8cb9e32df8e | lefred | 1 | +--------------------------------------+--------+-------+ Copyright @ 2022 Oracle and/or its affiliates. 56
74. InnoDB Primary Key - What about UUID ? (9) Recommended solution - example MySQL > INSERT INTO rivierajug3 (name, beers) VALUES ("Benoît",1), ("Sarah",5); MySQL > SELECT * FROM rivierajug3; +------------------------------------+---------+-------+ | uuid | name | beers | +------------------------------------+---------+-------+ | 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 | | 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 | | 0x11ED1FA537C57361BA36C8CB9E32DF8E | Benoît | 1 | | 0x11ED1FA537C5752DBA36C8CB9E32DF8E | Sarah | 5 | +------------------------------------+---------+-------+ Copyright @ 2022 Oracle and/or its affiliates. 57
75. InnoDB Primary Key - What about UUID ? (9) Recommended solution - example MySQL > INSERT INTO rivierajug3 (name, beers) VALUES ("Benoît",1), ("Sarah",5); MySQL > SELECT * FROM rivierajug3; +------------------------------------+---------+-------+ | uuid | name | beers | +------------------------------------+---------+-------+ | 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Yannis | 0 | | 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred | 1 | | 0x11ED1FA537C57361BA36C8CB9E32DF8E | Benoît | 1 | | 0x11ED1FA537C5752DBA36C8CB9E32DF8E | Sarah | 5 | +------------------------------------+---------+-------+ MySQL > SELECT BIN_TO_UUID(uuid,1), name, beers FROM rivierajug3; +--------------------------------------+---------+-------+ | BIN_TO_UUID(uuid,1) | name | beers | +--------------------------------------+---------+-------+ | 633ecb6c-1f9f-11ed-ba36-c8cb9e32df8e | Yannis | 0 | | 633ecd6f-1f9f-11ed-ba36-c8cb9e32df8e | lefred | 1 | | 37c57361-1fa5-11ed-ba36-c8cb9e32df8e | Benoît | 1 | | 37c5752d-1fa5-11ed-ba36-c8cb9e32df8e | Sarah | 5 | +--------------------------------------+---------+-------+ Copyright @ 2022 Oracle and/or its affiliates. 57
76. InnoDB Primary Key - What about UUID ? (10) Recommended solution - example Take a look at the size of each entry in the INDEX (and same amount added to each secondary index) MySQL > EXPLAIN SELECT * FROM rivierajug3 WHERE uuid=UUID_TO_BIN("37c5752d-1fa5-11ed-ba36-c8cb9e32df8e",1)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rivierajug3 partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 16 ref: const rows: 1 filtered: 100 Extra: NULL Copyright @ 2022 Oracle and/or its affiliates. 58
77. MySQL UUID MySQL generates UUID v1 as described in RFC4122. UUID v1 : is a universally unique identi er that is generated using a timestamp and the MAC address of the computer on which it was generated. UUID v4 : is a universally unique identi er that is generated using random numbers. Copyright @ 2022 Oracle and/or its affiliates. 59
78. MySQL UUID MySQL generates UUID v1 as described in RFC4122. UUID v1 : is a universally unique identi er that is generated using a timestamp and the MAC address of the computer on which it was generated. UUID v4 : is a universally unique identi er that is generated using random numbers. With UUID v4, it's not possible to generate any sequential output. Copyright @ 2022 Oracle and/or its affiliates. 59
79. MySQL Shell for VS Code MySQL in Visual Studio Code Copyright @ 2022 Oracle and/or its affiliates. 60
80. MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 61
81. Visual Studio Code is the most popular IDE with developers MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 61
82. MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 62
83. MySQL 8.0 Document Store discovery of a new world ! Copyright @ 2022 Oracle and/or its affiliates. 63
84. NoSQL Document Store Schemaless Copyright @ 2022 Oracle and/or its affiliates. 64
85. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... Copyright @ 2022 Oracle and/or its affiliates. 64
86. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Copyright @ 2022 Oracle and/or its affiliates. 64
87. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure Copyright @ 2022 Oracle and/or its affiliates. 64
88. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects Copyright @ 2022 Oracle and/or its affiliates. 64
89. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model Copyright @ 2022 Oracle and/or its affiliates. 64
90. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented Copyright @ 2022 Oracle and/or its affiliates. 64
91. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented JSON Copyright @ 2022 Oracle and/or its affiliates. 64
92. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented JSON close to frontend Copyright @ 2022 Oracle and/or its affiliates. 64
93. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented JSON close to frontend easy to learn Copyright @ 2022 Oracle and/or its affiliates. 64
94. How DBAs see data Copyright @ 2022 Oracle and/or its affiliates. 65
95. How DBAs see data How Developers see data { "GNP" : 249704, "Name" : "Belgium", "government" : { "GovernmentForm" : "Constitutional Monarchy, Federation", "HeadOfState" : "Philippe I" }, "_id" : "BEL", "IndepYear" : 1830, "demographics" : { "Population" : 10239000, "LifeExpectancy" : 77.8000030517578 }, "geography" : { "Region" : "Western Europe", "SurfaceArea" : 30518, "Continent" : "Europe" } } Copyright @ 2022 Oracle and/or its affiliates. 65
96. And they still need to do Analytics SQL SQL SQL Copyright @ 2022 Oracle and/or its affiliates. 66
97. ... mmm...how ? ? SQL SQL SQL Copyright @ 2022 Oracle and/or its affiliates. 67
98. How ? SQL SQL SQL Copyright @ 2022 Oracle and/or its affiliates. 68
99. What if there was a way to provide both SQL and NoSQL on one stable platform that has proven stability on well know technology with a large Community and a diverse ecosystem ? Copyright @ 2022 Oracle and/or its affiliates. 69
100. DBMS or NoSQL ? Copyright @ 2022 Oracle and/or its affiliates. 70
101. DBMS or NoSQL ? Why not both ? Copyright @ 2022 Oracle and/or its affiliates. 70
102. The MySQL Document Store ! SQL is now optional ?! Copyright @ 2022 Oracle and/or its affiliates. 71
103. SQL is now optional ?! Copyright @ 2022 Oracle and/or its affiliates. 72
104. Using MySQL Document Store ! SQL SQL SQL Copyright @ 2022 Oracle and/or its affiliates. 73
105. the Solution MySQL Document Store Copyright @ 2022 Oracle and/or its affiliates. 74
106. Built on the MySQL JSON Data type and Proven MySQL Server Technology Provides a schema exible JSON Document Store No SQL required No need to de ne all possible a ributes, tables, etc. Uses new X DevAPI Can leverage generated column to extract JSON values into materialized columns that can be indexed for fast SQL searches. Document can be ~1GB It's a column in a row of a table It cannot exceed max_allowed_packet Allows use of modern programming styles No more embedded strings of SQL in your code Easy to read Also works with relational Tables Proven MySQL Technology Copyright @ 2022 Oracle and/or its affiliates. 75
107. X Protocol Connectors Copyright @ 2022 Oracle and/or its affiliates. 76
108. X DevAPI We provide connectors for C++, Java, .Net, Node.js, Python, PHP working with Communities to help them supporting it too New MySQL Shell Command Completion Python, JavaScrips & SQL modes Admin functions New Util object A new high-level session concept that can scale from single MySQL server to a multiple server environment Non-blocking, asynchronous calls follow common language pa erns Supports CRUD operations Copyright @ 2022 Oracle and/or its affiliates. 77
109. Setup MySQL Document Store Copyright @ 2022 Oracle and/or its affiliates. 78
110. Installing MySQL Document Store install MySQL 8.0 Copyright @ 2022 Oracle and/or its affiliates. 79
111. Installing MySQL Document Store install MySQL 8.0 install MySQL Shell Copyright @ 2022 Oracle and/or its affiliates. 79
112. Installing MySQL Document Store install MySQL 8.0 install MySQL Shell install MySQL Connector for your programming language Copyright @ 2022 Oracle and/or its affiliates. 79
113. Installing MySQL Document Store install MySQL 8.0 install MySQL Shell install MySQL Connector for your programming language php-pecl-mysql-xdevapi for PHP mysql-connector-python for Python ... Copyright @ 2022 Oracle and/or its affiliates. 79
114. Installing MySQL Document Store install MySQL 8.0 install MySQL Shell install MySQL Connector for your programming language php-pecl-mysql-xdevapi for PHP mysql-connector-python for Python ... And nothing else, no need to install anything else or load any plugin, just be sure your rewall allows you to connect through port 33060 (X Protocol). Copyright @ 2022 Oracle and/or its affiliates. 79
115. MySQL Database Service X Protocol is also available in MDS !! Copyright @ 2022 Oracle and/or its affiliates. 80
116. Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2022 Oracle and/or its affiliates. 81
117. Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2022 Oracle and/or its affiliates. 81
118. Copyright @ 2022 Oracle and/or its affiliates. 82
119. Copyright @ 2022 Oracle and/or its affiliates. 83
120. Let's make a query JS > restaurants.find() Copyright @ 2022 Oracle and/or its affiliates. 84
121. Let's make a query JS > restaurants.find() That's too much records to show in here... let's limit it Copyright @ 2022 Oracle and/or its affiliates. 84
122. Copyright @ 2022 Oracle and/or its affiliates. 85
123. Some more examples Copyright @ 2022 Oracle and/or its affiliates. 86
124. Let's add a selection criteria: Copyright @ 2022 Oracle and/or its affiliates. 87
125. Syntax slightly di erent than MongoDB Copyright @ 2022 Oracle and/or its affiliates. 88
126. Syntax slightly di erent than MongoDB Copyright @ 2022 Oracle and/or its affiliates. 88
127. And for developers ? Copyright @ 2022 Oracle and/or its affiliates. 89
128. And for developers ? $session = mysql_xdevapi\getSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection->find($search)->execute()->fetchAll(); ... foreach ($results as $doc) { echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Copyright @ 2022 Oracle and/or its affiliates. 90
129. And for developers ? $session = mysql_xdevapi\getSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection->find($search)->execute()->fetchAll(); ... foreach ($results as $doc) { echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Easy, using only CRUD operations ! Copyright @ 2022 Oracle and/or its affiliates. 90
130. And for developers ? $session = mysql_xdevapi\getSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection->find($search)->execute()->fetchAll(); ... foreach ($results as $doc) { echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Easy, using only CRUD operations ! Not a single SQL statement ! Copyright @ 2022 Oracle and/or its affiliates. 90
131. For Java import import * *; ; class class Main Main { { public public static static void void main main( (String String args args[ [] ]) ) { { Session Session mySession mySession = = new new SessionFactory SessionFactory( () ) . .getSession getSession( ("mysqlx://localhost:33060/docstore?user=resto&password=Passw0rd!" "mysqlx://localhost:33060/docstore?user=resto&password=Passw0rd!") ); ; Schema Schema myDb myDb = = mySession mySession. .getSchema getSchema( ("docstore" "docstore") ); ; Collection Collection myColl myColl = = myDb myDb. .getCollection getCollection( ("restaurants" "restaurants") ); ; DocResult DocResult myDocs myDocs = = myColl myColl. .find find( ("name like :param" "name like :param") ). .limit limit( (1 1) ) . .bind bind( ("param" "param", , "Green%" "Green%") ). .execute execute( () ); ; System System. .out out. .println println( (myDocs myDocs. .fetchOne fetchOne( () )) ); ; mySession mySession. .close close( () ); ; } } } } Copyright @ 2022 Oracle and/or its affiliates. com com. .mysql mysql. .cj cj. .xdevapi xdevapi. . 91
132. CRUD operations Copyright @ 2022 Oracle and/or its affiliates. 92
133. CRUD operations for collections Add a document collection.add({ name: 'fred', age: 42 }) .add({ name: 'dave', age: 23 }) .execute() collection.add([ { name: 'dimo', age: 50 }, { name: 'kenny', age: 25 } ]).execute() Copyright @ 2022 Oracle and/or its affiliates. 93
134. CRUD operations for collections Modify a document collection.modify('name = :name') .bind('name', 'fred') .set('age', 43) .sort('name ASC') .limit(1) .execute() collection.modify('name = :name') .bind('name', 'fred') .patch({ age: 43, active: false }) .sort('name DESC') .limit(1) .execute() Copyright @ 2022 Oracle and/or its affiliates. 94
135. CRUD operations for collections Remove a document collection.remove('name = :name') .bind('name', 'fred') .sort('age ASC') .limit(1) .execute() Copyright @ 2022 Oracle and/or its affiliates. 95
136. MySQL Document Store Objects Summary Copyright @ 2022 Oracle and/or its affiliates. 96
137. All you need to know is here: h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2022 Oracle and/or its affiliates. 97
138. MySQL Document Store is Full ACID Compliant we do care about your data Copyright @ 2022 Oracle and/or its affiliates. 98
139. Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: Copyright @ 2022 Oracle and/or its affiliates. 99
140. Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_flush_log_at_trx_commit = 1 Copyright @ 2022 Oracle and/or its affiliates. 99
141. Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_flush_log_at_trx_commit = 1 innodb_doublewrite = ON Copyright @ 2022 Oracle and/or its affiliates. 99
142. Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_flush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 Copyright @ 2022 Oracle and/or its affiliates. 99
143. Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_flush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... Copyright @ 2022 Oracle and/or its affiliates. 99
144. Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_flush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... We do care about your data ! Copyright @ 2022 Oracle and/or its affiliates. 99
145. Full ACID - Transactions support Copyright @ 2022 Oracle and/or its affiliates. 100
146. Full ACID - Transactions support Copyright @ 2022 Oracle and/or its affiliates. 101
147. OK we have Document Store, CRUD and ACID but what makes MySQL Document Store unique ? Copyright @ 2022 Oracle and/or its affiliates. 102
148. Challenge: list the best restaurant of each type of food and show the top 10, with the best one rst ! don't forget that all these restaurants are just JSON documents Copyright @ 2022 Oracle and/or its affiliates. 103
149. NoSQL as SQL - aggregation Copyright @ 2022 Oracle and/or its affiliates. 104
150. NoSQL as SQL - aggregation Copyright @ 2022 Oracle and/or its affiliates. 105
151. NoSQL as SQL - aggregation Copyright @ 2022 Oracle and/or its affiliates. 106
152. NoSQL as SQL - aggregation Copyright @ 2022 Oracle and/or its affiliates. 107
153. NoSQL or SQL You have the possibility to write clean and neat code: Copyright @ 2022 Oracle and/or its affiliates. 108
154. NoSQL or SQL You have the possibility to write clean and neat code: $results = $collection->find('cuisine like "italian"')->execute()->fetchAll(); Copyright @ 2022 Oracle and/or its affiliates. 108
155. NoSQL or SQL You have the possibility to write clean and neat code: $results = $collection->find('cuisine like "italian"')->execute()->fetchAll(); Or use SQL only when it's really needed: Copyright @ 2022 Oracle and/or its affiliates. 108
156. NoSQL or SQL You have the possibility to write clean and neat code: $results = $collection->find('cuisine like "italian"')->execute()->fetchAll(); Or use SQL only when it's really needed: $results = $session->sql('WITH cte1 AS (SELECT doc->>"$.name" AS name, doc->>"$.cuisine" AS cuisine, (SELECT AVG(score) FROM JSON_TABLE(doc, "$.grades[*]" COLUMNS (score INT PATH "$.score")) AS r) AS avg_score FROM docstore.restaurants) SELECT *, RANK() OVER ( PARTITION BY cuisine ORDER BY avg_score) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10;')->execute(); Copyright @ 2022 Oracle and/or its affiliates. 108
157. NoSQL or SQL You have the possibility to write clean and neat code: $results = $collection->find('cuisine like "italian"')->execute()->fetchAll(); Or use SQL only when it's really needed: $results = $session->sql('WITH cte1 AS (SELECT doc->>"$.name" AS name, doc->>"$.cuisine" AS cuisine, (SELECT AVG(score) FROM JSON_TABLE(doc, "$.grades[*]" COLUMNS (score INT PATH "$.score")) AS r) AS avg_score FROM docstore.restaurants) SELECT *, RANK() OVER ( PARTITION BY cuisine ORDER BY avg_score) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10;')->execute(); All in the same MySQL X Session ! Copyright @ 2022 Oracle and/or its affiliates. 108
158. You can mix NoSQL & SQL as you want Copyright @ 2022 Oracle and/or its affiliates. 109
159. Best of Both Worlds: JSON_TABLE What are the maximum 10 ratings ever given to a restaurant? Copyright @ 2022 Oracle and/or its affiliates. 110
160. Best of Both Worlds: JSON_TABLE What are the maximum 10 ratings ever given to a restaurant? Cool... but my app only processes JSON ! Copyright @ 2022 Oracle and/or its affiliates. 110
161. Best of Both Worlds: JSON_TABLE (2) With JSON output: Copyright @ 2022 Oracle and/or its affiliates. 111
162. CHECK CONSTRAINTS We already saw that MySQL 8.0 supports Check Constraints: Copyright @ 2022 Oracle and/or its affiliates. 112
163. JSON Schema Validation Copyright @ 2022 Oracle and/or its affiliates. 113
164. JSON Schema Validation (2) Copyright @ 2022 Oracle and/or its affiliates. 114
165. JSON Schema Validation (3) And the best of both worlds: Copyright @ 2022 Oracle and/or its affiliates. 115
166. JSON Schema Validation (3) And the best of both worlds: And the result in action: Copyright @ 2022 Oracle and/or its affiliates. 115
167. JSON Schema Validation (3) And the best of both worlds: And the result in action: Copyright @ 2022 Oracle and/or its affiliates. 115
168. Conclusion what do I gain ? Copyright @ 2022 Oracle and/or its affiliates. 116
169. schemaless exible data structure easy to start (CRUD) Conclusion This is the best of the two worlds in one product ! Data integrity ACID Compliant Transactions SQL Copyright @ 2022 Oracle and/or its affiliates. 117
170. Migrating to Connector/J 8.0 some info for the Java developers Copyright @ 2022 Oracle and/or its affiliates. 118
171. Migration from Connector/J 5.1 to 8.0 You may have encountered problems with migration from Connector/J 5.1 to Connector/J 8.0 (before 8.0.23). They were caused by the early decision that Connector/J 8.0 should always try to preserve an instant point on the time-line while Connector/J 5.1 does it optionally and, by default, preserves the original visual representation. Copyright @ 2022 Oracle and/or its affiliates. 119
172. Migration from Connector/J 5.1 to 8.0 (2) For example, the following code will store di erent results with Connector/J 5.1 and Connector/J 8.0 in case the client and server time zones are di erent: Statement Statement st st = = conn conn. .createStatement createStatement( () ); ; st st. .executeUpdate executeUpdate( ("CREATE TABLE t1 (ts TIMESTAMP)" "CREATE TABLE t1 (ts TIMESTAMP)") ); ; PreparedStatement PreparedStatement ps ps = = conn conn. .prepareStatement prepareStatement( ("INSERT INTO t1 VALUES (?)" "INSERT INTO t1 VALUES (?)") ); ; ps ps. .setTimestamp setTimestamp( (1 1, , Timestamp Timestamp. .valueOf valueOf( ("2020-01-01 12:00:00" "2020-01-01 12:00:00") )) ); ; ps ps. .executeUpdate executeUpdate( () ); ; Copyright @ 2022 Oracle and/or its affiliates. 120
173. Migration from Connector/J 5.1 to 8.0 (2) For example, the following code will store di erent results with Connector/J 5.1 and Connector/J 8.0 in case the client and server time zones are di erent: Statement Statement st st = = conn conn. .createStatement createStatement( () ); ; st st. .executeUpdate executeUpdate( ("CREATE TABLE t1 (ts TIMESTAMP)" "CREATE TABLE t1 (ts TIMESTAMP)") ); ; PreparedStatement PreparedStatement ps ps = = conn conn. .prepareStatement prepareStatement( ("INSERT INTO t1 VALUES (?)" "INSERT INTO t1 VALUES (?)") ); ; ps ps. .setTimestamp setTimestamp( (1 1, , Timestamp Timestamp. .valueOf valueOf( ("2020-01-01 12:00:00" "2020-01-01 12:00:00") )) ); ; ps ps. .executeUpdate executeUpdate( () ); ; If the client is running in the UTC+2 time zone and server is running in UTC+1 the internal value of the TIMESTAMP eld will be <2020-01-01 11:00:00Z= with Connector/J 5.1 but <2020-01-01 10:00:00Z= with Connector/J 8.0. Copyright @ 2022 Oracle and/or its affiliates. 120
174. Migration from Connector/J 5.1 to 8.0 (3) Another client in the UTC+3 time zone is reading this value: ResultSet ResultSet rs rs = = st st. .executeQuery executeQuery( ("SELECT * FROM t1" "SELECT * FROM t1") ); ; Timestamp Timestamp ts ts = = rs rs. .getTimestamp getTimestamp( (1 1) ); ; Copyright @ 2022 Oracle and/or its affiliates. 121
175. Migration from Connector/J 5.1 to 8.0 (3) Another client in the UTC+3 time zone is reading this value: ResultSet ResultSet rs rs = = st st. .executeQuery executeQuery( ("SELECT * FROM t1" "SELECT * FROM t1") ); ; Timestamp Timestamp ts ts = = rs rs. .getTimestamp getTimestamp( (1 1) ); ; The result will be <2020-01-01 12:00:00= with Connector/J 5.1 but <2020-01-01 13:00:00= with Connector/J 8.0. Copyright @ 2022 Oracle and/or its affiliates. 121
176. By default, Connector/J 5.1 sends values as they are rendered locally and, on retrieval, constructs values using the client's local time zone. Connector/J 8.0.22 and before converts the original value to the session time zone before sending, thus the internal UTC value of a TIMESTAMP matches the expected instant value. When retrieved, a value is constructed after converting the on-wire value from session time zone to the local one, so it still represents the same instant, but the visual representation is di erent in di erent client time zones. Migration from Connector/J 5.1 to 8.0 (4) Copyright @ 2022 Oracle and/or its affiliates. 122
177. Migration from Connector/J 5.1 to 8.0 (5) Since Connector/J 8.0.23, both ways are now possible. The following connection properties de ne the time zone handling: connectionTimeZone=LOCAL|SERVER|user-defined time zone (previously known as serverTimezone, now with additional xed values) de nes how the server's session time zone is to be determined by Connector/J. forceConnectionTimeZoneToSession=true|false controls whether the session time_zone variable is to be set to the value speci ed in connectionTimeZone. preserveInstants=true|false turns on|o the conversion of instant values between JVM and connectionTimeZone. Copyright @ 2022 Oracle and/or its affiliates. 123
178. Migration from Connector/J 5.1 to 8.0 (5) The most useful con gurations are: connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=false – corresponds with the Connector/J 5.1 behavior with useLegacyDatetimeCode=true. connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=true – the new mode which provides the most natural way for handling date-time values. Copyright @ 2022 Oracle and/or its affiliates. 124
179. Migration from Connector/J 5.1 to 8.0 (6) connectionTimeZone=SERVER & preserveInstants=true – corresponds to the previous Connector/J 8.0 behavior and Connector/J 5.1 behavior with useLegacyDatetimeCode=false. connectionTimeZone=user_defined & preserveInstants=true – helps to overcome the situation when the server time zone cannot be recognized by the connector because it is set as a generic abbreviation like CET/CEST. More info on h ps://dev.mysql.com/blog-archive/support-for-date-time-types-in- connector-j-8-0/ Copyright @ 2022 Oracle and/or its affiliates. 125
180. MySQL 8.0 DBA Certi cation Copyright @ 2022 Oracle and/or its affiliates. 126
181. MySQL 8.0 Developer Certi cation Copyright @ 2022 Oracle and/or its affiliates. 127
182. Thank you ! Copyright @ 2022 Oracle and/or its affiliates. 128