Diving into MySQL 5.7: advanced features

This talk discusses new features in MySQL 5.7 including online DDL modes, the JSON data type, generated columns, the sql_mode variable, and the sys schema. Some key points covered are:
- MySQL 5.7 introduces new online DDL modes like INPLACE and COPY for making changes to InnoDB tables without locking them.
- The JSON data type allows storing JSON documents in columns and extracting values using functions like JSON_EXTRACT.
- Generated columns allow specifying expressions to populate column values automatically based on other columns.
- The sql_mode variable has additional modes in 5.7 like ONLY_FULL_GROUP_BY that affect query behavior.
- The sys schema provides views for monitoring

Transcript

1. Diving into MySQL 5.7 Advanced features Gabriela Ferrara 
 @gabidavila gabriela.io
2. @gabidavila But first... gabi Developer Advocate @ Google
3. @gabidavila MySQL 5.7 Adoption
4. @gabidavila 5.7
5. @gabidavila What to expect? • Online DDL modes • JSON Data type • Generated Columns • sql_mode • `sys` schema @gabidavila
6. Online DDL changes On InnoDB • INPLACE • COPY @gabidavila
7. @gabidavila New Online DDL Changes on 5.7 | InnoDB Only In-place | ALGORITHM=INPLACE • Rename Index • Add a Virtual Column • VARCHAR from 1 to 255 @gabidavila More info 🔗
8. @gabidavila New Online DDL Changes on 5.7 | InnoDB Only Table-copy | ALGORITHM=COPY • VARCHAR from 255 to 65535 • Drop Stored Column @gabidavila More info 🔗
9. JSON
10. @gabidavila Table `laps` Column Type id INT(10) athlete VARCHAR(255) distance (m) INT(10) time (s) INT(10) summary JSON
11. @gabidavila SELECT * FROM `laps` LIMIT 1; ************************** 1. row ***************************
 id: 1
 athlete: Martha
 distance: 3200
 time: 1800
 summary: {"weight": 55, "nickname": "M", "professional": true}
 1 row in set (0.04 sec) @gabidavila
12. @gabidavila Display athlete, nickname and professional status @gabidavila +---------+----------+--------------+ | athlete | nickname | professional | +---------+----------+--------------+ | Martha | "M" | true | | Marcus | "Flash" | false | | Martha | "M" | true | | James | NULL | NULL | | Marcus | "Flash" | false | +---------+----------+--------------+ 5 rows in set (0.04 sec) SELECT athlete, JSON_EXTRACT(summary, '$.nickname') AS nickname, JSON_EXTRACT(summary, '$.professional') AS professional FROM laps;
13. @gabidavila JSON_EXTRACT shortcut @gabidavila SELECT athlete, summary->'$.nickname' AS nickname, summary->'$.professional' AS professional FROM laps; +---------+----------+--------------+ | athlete | nickname | professional | +---------+----------+--------------+ | Martha | "M" | true | | Marcus | "Flash" | false | | Martha | "M" | true | | James | NULL | NULL | | Marcus | "Flash" | false | +---------+----------+--------------+ 5 rows in set (0.04 sec)
14. @gabidavila JSON_UNQUOTE + JSON_EXTRACT shortcut @gabidavila SELECT athlete, summary->>'$.nickname' AS nickname, summary->>'$.professional' AS professional FROM laps; +---------+----------+--------------+
 | athlete | nickname | professional |
 +---------+----------+--------------+
 | Martha | M | true |
 | Marcus | Flash | false |
 | Martha | M | true |
 | James | NULL | NULL |
 | Marcus | Flash | false |
 +---------+----------+--------------+
 5 rows in set (0.04 sec)
15. @gabidavila JSON_UNQUOTE + JSON_EXTRACT vs. ->> @gabidavila SELECT athlete, summary->>'$.nickname' AS nickname, summary->>'$.professional' AS professional FROM laps; 😎 SELECT athlete, JSON_UNQUOTE(JSON_EXTRACT(summary, '$.nickname')) AS nickname, JSON_UNQUOTE(JSON_EXTRACT(summary, '$.professional')) AS professional FROM laps; 😕
16. @gabidavila JSON Path • Access key: • `$` refers to the document itself • Keys can contain spaces • JSON_EXTRACT() and -> are the same • JSON_UNQUOTE(JSON_EXTRACT()) and ->> are the same
17. Generated Columns @gabidavila
18. @gabidavila Types Virtual • No disk space • In-place change operation • Value generated on demand and on every BEFORE triggers Stored • Uses disk space • Copy operation • Updated on every INSERT and UPDATE @gabidavila
19. @gabidavila Virtual & Stored similarities • They only know the table domain • They have a type • Allows expressions to be used such as: ◦ Operators (product * quantity) ◦ Built-in functions (YEAR(dob)) ◦ Literals ("expression", 1) • Can be indexed
20. @gabidavila Virtual & Stored limitations • Subqueries ARE NOT allowed • Custom functions ARE NOT supported • Column rename is not possible • Non deterministic functions are not supported (i.e. NOW())
21. @gabidavila Example
22. @gabidavila Modified table `laps` Column Type id INT(10) athlete VARCHAR(255) distance (m) INT(10) time (s) INT(10) summary JSON speed (m/s) DOUBLE Should it be VIRTUAL or STORED?
23. @gabidavila Virtual Adding the `speed` field to the `laps` table Dropping the `speed` field to the `laps` table Gist 🔗 ALTER TABLE `laps` ADD COLUMN speed DOUBLE GENERATED ALWAYS AS (`distance` / `time`); ALTER TABLE `laps` DROP COLUMN speed;
24. @gabidavila Virtual Adding the `speed` field to the `laps` table Dropping the `speed` field to the `laps` table Gist 🔗 ALTER TABLE `laps` ADD COLUMN speed DOUBLE GENERATED ALWAYS AS (`distance` / `time`); -- completed in 82ms ALTER TABLE `laps` DROP COLUMN speed; -- completed in 103ms
25. @gabidavila Stored Adding the `speed` field to the `laps` table Dropping the `speed` field to the `laps` table Gist 🔗 ALTER TABLE `laps` ADD COLUMN speed DOUBLE GENERATED ALWAYS AS (`distance` / `time`) STORED; -- 548 rows affected in 778ms ALTER TABLE `laps` DROP COLUMN speed; -- completed in 3s 142ms
26. Generated Columns & JSON Indexing
27. DEMO!
28. sql_mode @gabidavila
29. @gabidavila SQL Modes | SELECT @@GLOBAL.sql_mode; | MySQL 5.6 On MySQL 5.6: +--------------------------------------------+ | @@GLOBAL.sql_mode | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ 1 row in set (0.00 sec) @gabidavila
30. @gabidavila SQL Modes | SELECT @@GLOBAL.sql_mode; | MySQL 5.7 On MySQL 5.7: +-----------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-----------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, |
 | ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------+ 1 row in set (0.00 sec) @gabidavila
31. @gabidavila SQL Modes | Comparison On MySQL 5.6: ! STRICT_TRANS_TABLES ! NO_ENGINE_SUBSTITUTION On MySQL 5.7: ! STRICT_TRANS_TABLES ! NO_ENGINE_SUBSTITUTION ! NO_ZERO_IN_DATE* ! NO_ZERO_DATE* ! ERROR_FOR_DIVISION_BY_ZERO* ! ONLY_FULL_GROUP_BY @gabidavila
32. @gabidavila Example @gabidavila
33. @gabidavila Table `laps` Column Type id INT(10) athlete VARCHAR(255) distance (m) INT(10) time (s) INT(10) summary JSON speed (m/s) DOUBLE Gist 🔗 @gabidavila
34. @gabidavila SELECT * FROM `laps`; +----+---------+----------+------+
 | id | athlete | distance | time |
 +----+---------+----------+------+
 | 1 | Martha | 3200 | 1800 |
 | 2 | Marcus | 10000 | 2100 |
 | 3 | Martha | 200 | 50 |
 | 4 | James | 1600 | 480 |
 | 5 | Marcus | 1600 | 240 |
 +----+---------+----------+------+
 5 rows in set (0.04 sec) @gabidavila
35. @gabidavila mysql> SELECT * FROM `laps` GROUP BY athlete; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'olympics.laps.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT * FROM `laps` GROUP BY athlete; SELECT `id`, `athlete`, `distance`, `time`, `summary` FROM `laps` GROUP BY athlete;
36. @gabidavila mysql> SELECT * FROM `laps` GROUP BY athlete; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'olympics.laps.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT * FROM `laps` GROUP BY athlete; SELECT `id`, `athlete`, `distance`, `time`, `summary` FROM `laps` GROUP BY athlete;
37. @gabidavila Why? "Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns." @gabidavila
38. @gabidavila Translation This happens because the query is grouping by athlete and MySQL is magically trying to “discover” which field should it bring as a result to the other columns since there are more than one possible value per row. 
 This mode is known as ONLY_FULL_GROUP_BY. More info 🔗 @gabidavila
39. @gabidavila
40. @gabidavila Group By • Columns should either be on the GROUP BY clause OR be using an aggregator on the SELECT clause. • Aggregators: • ANY_VALUE() • MIN() • MAX() • SUM() • COUNT()
41. @gabidavila @gabidavila
42. `sys` schema
43. @gabidavila MySQL `sys` schema • Needs to be installed on 5.6 • Installed by default on 5.7 • MySQL Workbench 6.3 ships with a client for it • If enabled, in production should be used for critical analytical cases
44. @gabidavila
45. @gabidavila What can I do with it? *High Cost SQL statements select * from sys.`x$statement_analysis` Top 5% slower queries select * from sys.`x$statements_with_runtimes_in_95th_percentile` Use temporary tables select * from sys.`statements_with_temp_tables` Unused Indexes select * from sys.`schema_unused_indexes` Full table scans select * from sys.`schema_tables_with_full_table_scans` *x$ is actually a view duplicated from a table with a more user friendly format
46. @gabidavila
47. Other Changes
48. @gabidavila Changes on 5.7 • Passwords can now have expiration date • NO_ZERO_DATE, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO are deprecated and being default in STRICT mode in future versions • Triggers now supports more than one event per table • YEAR(2) was deprecated on 5.6, removed on 5.7
49. @gabidavila Thank you! • Twitter: @gabidavila • Blog: http://gabriela.io • Freenode: gabidavila • Feedback: https://joind.in/talk/18d42 • References: MySQL documentation