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

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

 

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.