MySQL 5.6 through 8.0

New feature of 5.6, 5.7 and what's coming in 8.0.
Bonus: How to use some of theses features in Rails.

Transcript

1. MySQL From 5.6 to 8.0
2. About me Michael BENSOUSSAN Jacques of all trades at Drivy.
 Product owner of the Data / Operations / Tooling team (DOT). MySQL 5.6 1 2 MySQL 8.0 - What we know so far 3 Bonus: Rails ♥ MySQL 4 Agenda @mickeyben github.com/mickey MySQL 5.7 drivy.engineering
3. What this talk is about 1 3 2 New Developer features How we use some of these
 at Drivy How to use with Rails
4. What this talk is not about MySQL vs Other DBs New operational features Whether you should
 use new features
5. Why begin at 5.6? MongoDB 1.4 2012 2015 2017 2010 MySQL 5.5 PostgreSQL 9.2 MySQL 5.6 MySQL 5.7 MySQL 8 Oracle buys Sun 2013
6. Stack overflow posts by tags 0 % 25 % 50 % 75 % 100 % 2010 2011 2012 2013 2014 2015 2016 MongoDB MySQL PostgreSQL Source: data.stackexchange.com
7. MySQL 5.6
8. 5.6: Performance Schema MySQL tables to track performance metrics like: • What queries are running • IO wait statistics • Historical performance data Enabled per default in 5.6
9. 5.6: Unused indexes SELECT object_schema, object_name, index_name FROM table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name LIMIT 10;
10. 5.6: 10 most frequent UPDATE queries SELECT digest_text, count_star / update_total * 100 as percentage_of_all FROM events_statements_summary_by_digest, ( SELECT sum(count_star) update_total FROM events_statements_summary_by_digest WHERE digest_text LIKE 'UPDATE%' ) update_totals WHERE digest_text LIKE 'UPDATE%' ORDER BY percentage_of_all DESC LIMIT 10
11. 5.6 Online DDL DDL = Data Definition Language 
 Online = Allow more changes to be made on the table while accessing and writing to the table being changed
12. 5.6 Online DDL ALGORITHM: • COPY: copying data to a temporary table, rebuilding the table and reconstructing the secondary indexes will be made • INPLACE: the table change will be made in-place without rebuilding the entire table LOCK: • NONE: Read and write operations are allowed during the altering process. • SHARED: Only read operations are allowed during the altering operations. • EXCLUSIVE: The entire table will be locked for both reading and writing
13. 5.6 Online DDL: Limitations • Only work with INNODB • Even with LOCKING=NONE there’s a brief exclusive locking on the table at the beginning and end of the operation • Replication is blocked during the operation
14. 5.6 Online DDL: Documentation
15. 5.6 FULLTEXT indexes ALTER TABLE users ADD FULLTEXT fulltext_index(first_name, last_name); SELECT * FROM users WHERE MATCH(first_name, last_name) against("Michael");
16. 5.6 FULLTEXT indexes 2 modes: • BOOLEAN: • Spaces are treated as an OR operator. • It allows operators like +, - and *. • There is no relevance for the keywords, just matches. • NATURAL LANGUAGE: return matches based on relevance. This means the more times the words appear in the text the more relevant it is.
17. 5.6 But also… • Memcache integration • Optimised subqueries • EXPLAIN for UPDATE, DELETE, INSERT • EXPLAIN FORMAT=JSON • Transportable tablespace files • Delayed replication • Checksums replication • Index merge optimization • ….
18. MySQL 5.7 Released in 2015 Biggest MySQL release There are over 150 new features in MySQL 5.7
19. 5.7 GIS New geometry types: • geometry • point • curve • linestring • surface • polygon • geometrycollection • multipoint • multicurve • multilinestring • multisurface • multipolygon
20. 5.7 GIS Create a POINT column: 
 ALTER TABLE `cities` ADD COLUMN `center` POINT NULL DEFAULT NULL; Insert a POINT: INSERT INTO cities SET point = POINT(48.8651494,2.3479951); Create a SPATIAL index: CREATE SPATIAL INDEX `index_center` USING BTREE ON `cities`(`point`);
21. 5.7 GIS Search city centers closest to a point: SELECT cities.name, ST_Distance_Sphere(cities.center, POINT(48.8651494, 2.3479951) as distance FROM cities ORDER BY distance;
22. 5.7 GIS | How we use it Points of interests (POI) close to a city
23. 5.7 GIS | How we use it Data analysis
24. 5.7 JSON • Native JSON Data type • JSON Comparator • Short-Hand JSON_EXTRACT operator • A lot of functions to extract, inspect, modify and search.
 Specific functions to work with objects or arrays.
25. 5.7 JSON Create a table with a JSON column: CREATE TABLE documents (document JSON); Insert values in a JSON document: INSERT INTO documents VALUES('{"k1": "v1", "k2": "v2"}');
26. 5.7 JSON Select a JSON column: SELECT JSON_EXTRACT(document, “$.k1") FROM documents; Short-hand: SELECT document->"$.k1" FROM documents;
27. 5.7 JSON functions
28. 5.7 JSON | How we use it • Store multiple translations in the same column • Store all transformations applied to a photo • Store related articles IDs (help-center) • Store tags • ….
29. 5.7 JSON | Limitations No index for JSON columns But…
30. 5.7 Generated columns It is a way of storing data without actually sending it through the INSERT or UPDATE clauses in SQL. The database resolves what the data will be. They work with:
 • Mathematical expressions. product_price * quantity • Built-in functions. RIGHT, CONCAT, FROM_UNIXTIME, JSON_EXTRACT, … • Literals. “2”, “new”, 0
31. 5.7 Generated columns There is 2 types of storage: • VIRTUAL - recalculated on every access • STORED - updated on each UPDATE / INSERT Whatever the storage you can index Generated columns!
32. 5.7 Generated columns ALTER TABLE users ADD full_name varchar(255) GENERATED ALWAYS AS ( CONCAT(users.first_name, ' ', users.last_name) ) VIRTUAL; ALTER TABLE tweets ADD location varchar(255) GENERATED ALWAYS AS (response->"$.location") STORED;
33. 5.7 But also… • More DDL operations • Faster and better Performance Schema • Parallel replication • Multi source replication • EXPLAIN FOR CONNECTION • Fulltext search optimisations and support for more languages • Performances improved everywhere • Multiple triggers per event per table • SYS schema bundled by default • New mysqlpump utility • ….. http://www.thecompletelistoffeatures.com
34. MySQL 8 | What we know so far • Invisible indexes • CTE (Common Table Expression) • Windowing functions • UUID type • User roles • JSON_ARRAYAGG, JSON_OBJECTAGG • Document store • New Data Dictionary • UTF8MB4 per default • Descending indexes
35. Rails ♥ MySQL
36. Rails ♥ MySQL - Generated columns Available in Rails 5.1
37. Rails ♥ MySQL - JSON Available in Rails 5.1
38. Rails ♥ MySQL - GIS PR is opened - Ongoing discussions
39. Rails ♥ MySQL - FULLTEXT and SPATIAL indexes Available in Rails 5.1
40. Rails ♥ MySQL - How to use JSON and GIS NOW https://gist.github.com/mickey/3f9186bbee1eefa98076a891303e5e83
41. Sources and interesting articles • https://githubengineering.com/using-mysql-performance-schema-for-workload-analysis/ • https://www.percona.com/resources/mysql-webinars/using-performance-schema-monitor-and-troubleshoot-mysql-56 • http://www.thecompletelistoffeatures.com/ • https://www.percona.com/blog/2015/06/18/getting-explain-information-already-running-queries-mysql-5-7/ • https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html • https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html • http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/ • http://mysqlserverteam.com/the-unified-data-dictionary-lab-release/ • http://mysqlserverteam.com/mysql-8-0-data-dictionary-architecture-and-design/ • http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/ • https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/ • https://www.slideshare.net/IkeWalker/practical-json-in-mysql-57-and-beyond • https://www.slideshare.net/datacharmer/a-quick-tour-of-mysql-80-roles • http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/ • https://www.percona.com/blog/2016/10/20/mysql-8-0-descending-indexes-can-speedup-your-queries/ • https://www.percona.com/blog/2015/05/27/mysql-5-7-key-features/ • https://www.percona.com/blog/2016/10/18/upgrading-to-mysql-5-7-beware-of-the-new-strict-mode/ • https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/ • https://www.percona.com/blog/2016/02/03/new-gis-features-in-mysql-5-7/ • https://www.percona.com/blog/2014/11/20/sys-schema-mysql-5-6-5-7/ • https://www.percona.com/blog/2013/03/29/mysql-5-6-innodb-memcached-plugin-as-a-caching-layer/
42. 42 Questions?