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?
http://www.slideshare.net/zohare/my-sql-5-7-for-developers
Demo file for JSON Native Datatype:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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":"zohar@nope.com" | |
}'); | |
INSERT INTO employees Values ('{ | |
"id":4, | |
"userId":"Ido", | |
"jobTitleName":"Team leader", | |
"firstName":"Ido", | |
"lastName":"Elkayam", | |
"phoneNumber":"555-1234567", | |
"emailAddress":{"work":"ido@nope.com", | |
"private":"nope@gmail.com"} | |
}'); | |
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', 'ido@nope.com') 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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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!