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!