MySQL 5.7 New Features for Developers (slides)
This is a presentation from outside my comfort zone – MySQL. So as most of you know, I usually do sessions about Oracle core technologies (rdbms), Big Data (Hadoop) and Spark. Even though I’ve been working with MySQL for quite some time, I never considered myself to be much of an expert – it was just always in the background.
Since I decided 2016 is going to be a year of challenges in my last year resolution, I choose to take the things I learned using MySQL 5.7 and make them into a public session. I consulted some of my co-workers, and decided that the best thing will be to start with MySQL for Developers. I took everything I thought that might interest developers and off we went.
I submitted this session to couple of user group conferences (and to the Oracle Open World) – but the first conference that it actually was presented at was DOAG 2016 in Germany. Since then, I presented this in two other events (the MySQL meetup in Israel and in a private event) and got some really positive feedback.
In the agenda:
- Optimizer, Performance and InnoDB changes
- Native JSON datatype
- The Performance Schema and SYS Schema
- Other features we should know
- What are we waiting for in MySQL 8.0?
Demo file for JSON Native Datatype:
| use doag | |
| drop TABLE employees; | |
| drop table employees_old; | |
| CREATE TABLE employees_old | |
| (id NUMERIC, | |
| first_name varchar(20), | |
| last_name varchar(20), | |
| user_id varchar(20), | |
| job_title varchar(20) | |
| ); | |
| insert into employees_old values (1, 'Tamar', 'Elkayam', 'tamare', 'CFO'); | |
| insert into employees_old values (2, 'Efrat', 'Elkayam', 'efratg', 'CEO'); | |
| insert into employees_old values (3, 'Zohar', 'Elkayam', 'zohare', 'CTO'); | |
| insert into employees_old values (4, 'Ido', 'Elkayam', 'idodo', 'Team Leader'); | |
| commit; | |
| select JSON_OBJECT('id', id, 'firstName', first_name, 'lastName', last_name) from employees_old; | |
| select JSON_ARRAY(first_name, last_name) from employees_old; | |
| — | |
| drop table employees; | |
| CREATE TABLE employees (data JSON); | |
| INSERT INTO employees VALUES ('{"id": 1, "firstName": "Efrat"}'); | |
| INSERT INTO employees VALUES ('{"id": 2, "firstName": "Tamar"}'); | |
| INSERT INTO employees Values ('{ | |
| "id":3, | |
| "userId":"zohare", | |
| "jobTitleName":"CTO", | |
| "firstName":"Zohar", | |
| "lastName":"Elkayam", | |
| "phoneNumber":"555-1234567", | |
| "emailAddress":"[email protected]" | |
| }'); | |
| INSERT INTO employees Values ('{ | |
| "id":4, | |
| "userId":"Ido", | |
| "jobTitleName":"Team leader", | |
| "firstName":"Ido", | |
| "lastName":"Elkayam", | |
| "phoneNumber":"555-1234567", | |
| "emailAddress":{"work":"[email protected]", | |
| "private":"[email protected]"} | |
| }'); | |
| SELECT * FROM employees; | |
| INSERT INTO employees VALUES ('i am not a json'); | |
| — | |
| SELECT DISTINCT JSON_EXTRACT(data, "$.firstName") as first_name FROM employees; | |
| SELECT DISTINCT data–>"$.firstName" as first_name FROM employees; | |
| SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(data, "$.firstName")) as first_name FROM employees; | |
| SELECT DISTINCT data–>>"$.firstName" as first_name FROM employees; | |
| SELECT * FROM employees | |
| WHERE data–>"$.firstName" = 'Tamar'; | |
| — if holder does not exist, value is null | |
| SELECT data–>"$.emailAddress.work" as first_name FROM employees; | |
| select * from | |
| (SELECT data, data–>"$.emailAddress.work" as email FROM employees) a | |
| where email is not null; | |
| select * from | |
| (SELECT data, data–>"$.emailAddress.private" as email FROM employees) a | |
| where email is null; | |
| SELECT * FROM employees WHERE data–>"$.emailAddress.work" like '%nope.com'; | |
| —– | |
| SELECT JSON_SEARCH(data,'all', '[email protected]') from employees; | |
| —– Modify JSON | |
| — original data (no holder) | |
| select * from employees WHERE data–>"$.firstName" = 'Tamar'; | |
| — using set | |
| select JSON_SET(data, '$.phoneNumber', '555-1234') as JSON_OBJECT from employees WHERE data–>"$.firstName" = 'Tamar'; | |
| — using replace | |
| select JSON_REPLACE(data, '$.phoneNumber', JSON_ARRAY('555-1234', '555-4321')) from employees WHERE data–>"$.firstName" = 'Tamar'; | |
| — Using set and replace with holder | |
| select * from employees WHERE data–>"$.firstName" = 'Zohar'; | |
| select JSON_REPLACE(data, '$.phoneNumber', JSON_ARRAY('555-1234', '555-4321')) as JSON_OBJECT from employees | |
| WHERE data–>"$.firstName" = 'Zohar'; | |
| select JSON_SET(data, '$.phoneNumber', JSON_ARRAY('555-1234', '555-4321')) as JSON_OBJECT from employees | |
| WHERE data–>"$.firstName" = 'Zohar'; | |
| —– Virtual columns and indexes | |
| — creating an index on json column. this does not work | |
| create index emp_name_idx on employees(data–>>"$.userId"); | |
| — we add the generated (virtual or stored) column (preferably, virtual): | |
| alter table employees add id numeric as (data–>>"$.id"); | |
| alter table employees add id_stored numeric as (data–>>"$.id") STORED; | |
| explain select * From employees where id = 3; | |
| create index emp_id on employees(id); | |
| explain select * From employees where id = 3; | |
| — eof |
Demo file for SYS Schema:
| use sys; — or sys. | |
| — | |
| select * from sys.version; | |
| show full tables in sys; | |
| — summary views | |
| select * From sys.host_summary\G | |
| — show view definition -> show x$ definition | |
| select * from `performance_schema`.`events_waits_summary_by_host_by_event_name`; | |
| select * From sys.host_summary | |
| — which is better? | |
| select * from sys.user_summary_by_file_io_type; | |
| select * from io_by_thread_by_latency; | |
| — Schema analysis views | |
| — (only on disk information) | |
| select * from schema_table_statistics where table_schema = 'doag' and table_name = 'employees_old'\G | |
| — (with memory information) | |
| select * from schema_table_statistics_with_buffer where table_schema = 'doag' and table_name = 'employees_old'\G | |
| — list of unused indexes | |
| select * From schema_unused_indexes; | |
| — helper functions | |
| select sys.format_time(82798811507) as time | |
| union select sys.format_time(9710741185261) | |
| union select sys.format_time(116865898735315); | |
| select sys.format_bytes(85857938) | |
| union select sys.format_bytes(8885793826) | |
| union select sys.format_bytes(1911885793826); |
and a demo I didn’t get to use after all so I’m not sure it is complete or if it is any good at all:
| use doag; | |
| set autocommit=0; | |
| drop table if exists quotes; | |
| create table quotes | |
| ( id int unsigned auto_increment primary key | |
| , author varchar(64) , quote varchar(4000) | |
| , source varchar(64) | |
| , fulltext(quote) | |
| ) engine=innodb; | |
| insert into quotes (author, quote, source) values | |
| ('Abraham Lincoln', 'Fourscore and seven years ago…', | |
| 'Gettysburg Address') | |
| , ('George Harrison', 'All those years ago…', | |
| 'Live In Japan') | |
| , ('Arthur C. Clarke', 'Then 10 years ago the monolith was discovered.', | |
| '2010: The Year We Make Contact') | |
| , ('Benjamin Franklin', | |
| 'Early to bed and early to rise, makes a man healthy, wealthy, and wise.', | |
| 'Poor Richard''s Almanack') | |
| , ('James Thurber', | |
| 'Early to rise and early to bed makes a male healthy and wealthy and dead.', | |
| 'The New Yorker') | |
| , ('K', '1500 hundred years ago, everybody knew that the Earth was the center of the universe.', | |
| 'Men in Black') | |
| ; | |
| commit; | |
| select * from quotes | |
| select author as "Monolith" from quotes | |
| where match(quote) against ('monolith' in natural language mode); |
Leave a Reply
Want to join the discussion?Feel free to contribute!