$30 off During Our Annual Pro Sale. View Details »

RivieraJUG - MySQL 8.0 : What's new for developers

lefred
September 13, 2022

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 !

lefred

September 13, 2022
Tweet

More Decks by lefred

Other Decks in Technology

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