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:

	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

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

MySQL 5.7 New Features for Developers (slides)

Leave a Reply

Leave a ReplyCancel reply

MySQL 5.7 New Features for Developers (slides)

You might also like

Leave a Reply

Leave a ReplyCancel reply