sql and nosql on mysql - percona€¦ · sql and nosql on mysql wonderful things you can do with...
TRANSCRIPT
SQL and NoSQL on MySQL Wonderful things you can do with MySQL 5.7
Peter Zaitsev CEO, Percona
July 23, 2016 Data Day Seattle
2
In This Presentation
Few Words about Percona
What is “NoSQL” ?
History of “NoSQL” support in MySQL
Wonderful things you can do with MySQL 5.7
3
About Percona
We Exist to help you to succeed with MySQL and MongoDB
3
4
Support Broad Ecosystem
Percona Server for MySQL
MySQL MariaDB AWS for
MySQL and Aurora
MongoDB Percona Server for MongoDB
Google CloudSQL
4
5
Percona Software – 100% Open Source
Percona Server for MySQL
Percona Server for MongoDB
Percona XtraDB Cluster
Percona Xtrabackup Percona Toolkit
Percona Monitoring and Management
5
6
Services
• Support • More than Support (Percona Care) • Managed Services (Percona Care Ultimate) • Consulting
6
NoSQL
8
What is NoSQL ?
• Non RelaUonal Data Model
• Something other than SQL
Way to access data
9
NoSQL is a broad class, not one thing
Key-‐Value Document Stores
Wide-‐Column Stores
Graph MulU-‐Model
10
Other take on NoSQL
11
Why NoSQL ?
Simplicity
Scalability
Performance
BeYer Data Model
NoSQL in MySQL
13
Focus with MySQL
Flexible Schema
Different Access Protocol
14
Access Protocol
• Non SQL access through standard client server protocol HANDLER Command
• NoSQL as naUve protocol MySQL Cluster (NDB) 2004
• Memcache Protocol for MySQL (Community) MyCacheD 2009
• Custom NoSQL access protocol HandlerSocket 2010
• For Innodb and NDB Storage Engines Official MemcacheD protocol Support 2011
• New Protocol combining SQL and NoSQL Protocol X 2016
15
Data Model
Store Serialized objects in BLOB/TEXT
Xpath Support in MySQL (2005)
Dynamic columns in MariaDB (2012)
JSON UDFS by Sveta Smirnova (2013)
NaUve JSON Data Type MySQL 5.7 (2015)
JSON Data Type in MySQL 5.7
17
Design Foundations
NaUve JSON Data Type
Index Decoupled from Column
SQL Syntax Support
18
JSON Data Type
Parse and ValidaUon on Insert Only
Index for fast access to Object Keys and Array Sells
Support for Space ReservaUon and Update in Place (future)
19
Support for Generated Columns
• sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
Column value is computed based on
values of other columns
• Computed on access or computed value stored
Can be VIRTUAL or STORED
• Both Types Can be Indexed
20
Generated (Virtual) Columns
CREATE TABLE `ontime` (
`id` int(11) NOT NULL AUTO_INCREMENT, `YearD` year(4) NOT NULL,
`FlightDate` datetime DEFAULT NULL,
`Carrier` char(2) DEFAULT NULL,
`OriginAirportID` int(11) DEFAULT NULL,
`OriginCityName` varchar(100) DEFAULT NULL,
`OriginState` char(2) DEFAULT NULL,
`DestAirportID` int(11) DEFAULT NULL,
`DestCityName` varchar(100) DEFAULT NULL,
`DestState` char(2) DEFAULT NULL,
... `Flight_dayofweek` tinyint(4)
GENERATED ALWAYS AS (dayofweek(FlightDate)) VIRTUAL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
alter table ontime add key (Flight_dayofweek);
https://www.percona.com/blog/2015/04/29/generated-‐virtual-‐columns-‐in-‐mysql-‐5-‐7-‐labs/
https://dev.mysql.com/worklog/task/?id=8114
Does not store the column But INDEX it
SELECT Flight_dayofweek, count(*) FROM ontime_sm_virtual GROUP BY Flight_dayofweek
21
mysql> EXPLAIN SELECT carrier, count(*)
FROM ontime_sm_virtual
WHERE Flight_dayofweek = 7 group by carrier\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ontime_sm_virtual
partitions: NULL
type: ref
possible_keys: Flight_dayofweek
key: Flight_dayofweek
key_len: 2
ref: const
rows: 165409
filtered: 100.00
Extra: Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
Using index
Generated (Virtual) Columns
22
Need Better SQL Syntax
• Using JSON Functions to refer to value in JSON is not convenient!
23
Using JSON Data Type Example
mysql> create table json_test (
id int primary key auto_increment,
data json
) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from json_test where data-‐>'$.type' = 'Point' limit 1;
+-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+
| id | data |
+-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+
| 1 | {"type": "Point", "coordinates": [-‐87.9101245, 41.7585879]} |
+-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+
Same as JSON_EXTRACT(data,"$.type”)
www.percona.com
JSON Support: Indexes mysql> explain select * from json_test where data-‐>'$.type' = 'Point' limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: json_test partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 996823 filtered: 100.00 Extra: Using where mysql> alter table json_test add data_type varchar(255) GENERATED ALWAYS AS (data-‐>'$.type') VIRTUAL; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table json_test add key (data_type); Query OK, 0 rows affected (2.51 sec) Records: 0 Duplicates: 0 Warnings: 0
www.percona.com
JSON Support: Indexes mysql> explain select * from json_test where data-‐>'$.type' = 'Point' limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: json_test partitions: NULL type: ref possible_keys: data_type key: data_type key_len: 258 ref: const rows: 1 filtered: 100.00 Extra: NULL
New Access Protocol
27
Would not it be less pain if we do not have to use SQL ?
Protocol X Introduced in MySQL 5.7 (Through Rapid Plugin)
Many Design Improvements
Support for SQL and CRUD Interface
New Command Line Client – MySQL Shell
28
Installing MySQL Shell
… • apt-‐get install mysql-‐apt-‐config • apt-‐get install mysql-‐shell • mysqlsh -‐u root -‐h localhost -‐p -‐-‐classic -‐-‐dba enableXProtocol
• mysqlsh -‐u root -‐-‐sql -‐-‐recreate-‐schema world_x < /tmp/world_x-‐db/world_x.sql
https://dev.mysql.com/doc/refman/5.7/en/document-‐store-‐setting-‐up.html
29
Using MySQL Shell
mysql-‐js> db = session.getSchema('world_x') <Schema:world_x> mysql-‐js> db.getCollections() { "CountryInfo": <Collection:CountryInfo> }
30
.Find()
31
.Modify()
32
How Does it Work Inside ?
• Each Collection is represented as table • Bonus: Works with different MySQL storage engines
33
Lets do the Query
34
And Check MySQL Server Logs…
• Seeing § 2016-05-17T21:02:21.213899Z 186 Query SELECT doc FROM `world_x`.`CountryInfo` WHERE (JSON_EXTRACT(doc,'$.Name') = 'United States') LIMIT 1
• MySQL Converts CRUD to SQL Queries on the low Level
35
Indexing Document Store
36
Implications of Such Design Approach
GOOD
• Developers get wanted Data Interface but DBAs do not have to learn new language
• All ExisUng tools work
BAD
• Converted Queries can be hard to understand
• Hard to find where various queries come from in the applicaUon
37
Other things to consider
• Support for full set of data consistency features § Isolation Modes § Transactions § Savepoints
• Can mix SQL and NoSQL within same transaction • Multiple Storage Engines within same Server • Instrumentation with Performance Schema • MySQL Replciation
38
Summary
You can Use SQL and NoSQL on the same data with MySQL
Mix RelaUonal and Non RelaUonal data within same consistent store
New Features as of MySQL 5.7 – work in progress
Does not offer Scale Out Advantages of some other NoSQL SoluUons
39
Join us at Percona Live Europe
When: October 3-5, 2016 Where: Amsterdam, Netherlands The Percona Live Open Source Database Performance Conference is a great event for users of any level using open source database technologies.
• Get briefed on the hottest topics • Learn about building and maintaining high-performing deployments • Listen to technical experts and top industry leaders Get the early bird rate before prices go up on July 26th! Register now Sponsorship opportunities available as well here.
Thank You!