MySQL 5.7 New Features for Developers (slides)

MySQL, Presentations

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?

MySQL 5.7 New Features for Developers from Zohar Elkayam

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

view raw
mysql json demo.sql
hosted with ❤ by GitHub

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);

view raw
mysql demo sys.sql
hosted with ❤ by GitHub

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);

view raw
mysql_full_text_demo_not_used.sql
hosted with ❤ by GitHub

14/12/2016/0 Comments/by Zohar Elkayam/Estimated Reading Time: 2 Minutes

MySQL 5.7 New Features for Developers (slides)

Leave a Reply

Leave a Reply Cancel reply

MySQL 5.7 New Features for Developers (slides)

You might also like

Leave a Reply

Leave a Reply Cancel reply