1. Copyright201,Oracleand/oritsaliates.Allrightsreserved.| dbtechshowcaseTokyo2015 #dbts2015#D23 RyusukeKajiyama//@RKajiyama MySQLSalesConsulIngSeniorManager,AsiaPacic&Japan D:23MySQLLabs 2. Copyright201Oracleand/oritsaliates.Allrightsreserved.| SAFEHARBORSTATEMENT 2 3. Copyright201Oracleand/oritsaliates.Allrightsreserved.| Theworld'smostpopularopensourcedatabase 4. Copyright201Oracleand/oritsaliates.Allrightsreserved.| 4 2015 105 AYearofAnniversaries! 5. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQL & Oracle,RedHat,CentOS Fedora Ubuntu,Debian SUSE DevOps MySQLDatabase MySQLWorkbench MySQLConnector/ODBC MySQLConnector/Python MySQLConnector/NET MySQLUIliIes 6 MySQL:Yum,APT,NuGET 6. Copyright201Oracleand/oritsaliates.Allrightsreserved.| GitHubMySQL MySQLGit GitHubforMySQLCommunity haps://github.com/mysql :hap://mysqlrelease.com 7 7. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQL5.6 h;p://dev.mysql.com/doc/refman/5.6/ja/index.html 8 8. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLEnterpriseEdiIon 9 9. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLEnterpriseBackup / & MySQLEnterpriseSecurity (PAM, Windows,LDAP,etc.) MySQLEnterpriseMonitor MySQLEnterpriseEncrypMon AES256 / MySQLEnterpriseAudit SQL XML MySQLEnterpriseEdiIon 10. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLEnterpriseFirewall SQLInjecIonProtecIonwithPosiIveSecurityModel OutofpolicydatabasetransacIonsdetectedandblocked Logging&Analysis Select *.* from employee where id=22! Select *.* from employee where id=22 or 1=1! Block&Log Allow&Log WhiteListApplicaMons 11. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLCluster7.4GA 12 12. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLCluster:SQLandNoSQLHybridAPIs MySQLClusterDataNodes AppsAppsAppsAppsAppsAppsAppsAppsAppsAppsAppsApps JPA ClusterJPA PHPPerlPythonRubyJDBCClusterJJSApacheMemcached MySQLJNINode.JSmod_nd b ndb_eng NDBAPI(C++) 13. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLCluster7.4GA 200MillionNoSQL Reads/Sec 2.5MSQLOps/Sec 50%FasterReads 40%FasterMixed Performance AcIve-AcIve Geographic Redundancy ConictDetecIon/ ResoluIon AcIve-AcIve 5XFaster MaintenanceOps DetailedReporIng Management 9thApril2015 Copyright2015,oracleand/oritsaliates.Allrightsreserved 14 14. Copyright201,Oracleand/oritsaliates.Allrightsreserved.| MySQL 15. Copyright201Oracleand/oritsaliates.Allrightsreserved.| 16 4.0 /GIS(MyISAM) UPDATE/DELETE OracleMySQL Sun 3.23 MyISAM InnoDB 5.1 5.6 memcachedAPI UNDO GlobalTransacIonID ALTERTABLE 5.5 InnoDB PERFORMANCE_SCHEMA 1.0-3.22 (ISAM,HEAP) Windows/64bit (SJIS/UJIS) 5.0 // XA INFORMATION_SCHEMA 4.1 Unicode CSV,ARCHIVE ndbcluster 19952000200520102015 5.7+ CJK/GIS(InnoDB) NoSQL 16. Copyright201Oracleand/oritsaliates.Allrightsreserved.| SSD UNDO JSONEXPLAIN MemcachedAPI GlobalTransacIonID Binlog 17 ALTERTABLE SHA256 MySQL5.6 17. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQL5.7ReleaseCandidateAvailable! 18 InnoDB: Online&Bulkload (mulI-source,mulI-threadedslaves) : greaterusercontrol&beaerqueryperformance PerformanceSchema MySQLSYSSchema & MySQL5.62 : ,& NEW!JSONSupport(nowinlabs) RC Andmanymorenewfeaturesandenhancements...hap://mysqlserverteam.com/the-mysql-5-7-7-release-candidate-is-available/ 18. Copyright201Oracleand/oritsaliates.Allrightsreserved.| InnoDB-FullTextSearch(FTS)-ngram 19 InnoDBFullTextSearch(FTS) , N-gramsupportforChineseandKorean,addiIonalMeCabsupportforJapanese CREATETABLE`N_DEMO`(`FTS_N_ID`bigint(20)unsignedNOTNULLAUTO_INCREMENT,`Itle`varchar(100)DEFAULTNULL, PRIMARYKEY(`FTS_N_ID`),FULLTEXTKEY`ngram_idx`(`Itle`)/*!50100WITHPARSER`ngram`*/ )ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=uv8mb4; 19. Copyright201Oracleand/oritsaliates.Allrightsreserved.| InnoDB-FullTextSearch(FTS)-mecab 20 InnoDBFullTextSearch(FTS) , N-gramsupportforChineseandKorean,addiIonalMeCabsupportforJapanese CREATETABLE`M_DEMO`(`FTS_M_ID`bigint(20)unsignedNOTNULLAUTO_INCREMENT,`Itle`varchar(100)DEFAULTNULL, PRIMARYKEY(`FTS_M_ID`),FULLTEXTKEY`mecab_idx`(`Itle`)/*!50100WITHPARSER`mecab`*/ )ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=uv8mb4; 20. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLLabs 21 21. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLLabs MySQLMySQLCluster MySQL5.6memcachedAPI MySQLCluster7.2memcachedAPI MySQL5.6PerformanceSchema MySQL5.6Intra-schemaMulIThreadSlave MySQL5.6OnlineAlterTable MySQL5.7MulISourceReplicaIon MySQL5.7NewOpImizerCostModel 22 22. Copyright201Oracleand/oritsaliates.Allrightsreserved.| ( OpImisIcStateMachine / InnoDB GTID PERFORMANCE_SCHEMA MySQL5.7: ApplicaIon MySQLMasters ReplicaIon Plugin API MySQL Server GroupComms (Corosync) 23 labs.mysql.com 23. Copyright201Oracleand/oritsaliates.Allrightsreserved.| EarlyAccessFeature(EAF):DataDicIonary ReplacingtheFRMs Asinglerepositoryfordatabaseobjectmetadata InnoDBtablesreplace.frm,.trg,.trn,.parles Atomic&crash-safeoperaIonstoday TransacIonalinthefuture Makesaddingnewfeaturesmucheasier Eliminatescomplexity,resolvesbugs Improvesperformance LeveragesInnoDBstrengths 24 24. Copyright201Oracleand/oritsaliates.Allrightsreserved.| InnoDB NewDataDicIonary:Architecture QueryExecuIoner Parser OpImizer Data DicIonary Tablespace DataDicIonaryInternalAPI InternalSE DataDicIonaryExternalAPI Plugin Storage Engine Plugin Storage Engine Plugin Plugin Plugin UserTableTablespace 25. Copyright201Oracleand/oritsaliates.Allrightsreserved.| EAF:InnoDBCompression Thankyou,SanDiskFusion-io IO OS IO MySQL SSD InnoDBUNDO 26 labs.mysql.com 26. Copyright201Oracleand/oritsaliates.Allrightsreserved.| HTTPPluginforMySQL MySQLHTTP(S) UTF8JSON 3 SQL CRUD-Key-Value JSON-Document Formoredetails; hap://www.slideshare.net/nixnutz/hap-plugin-for-mysql-39598656 27 27. Copyright201Oracleand/oritsaliates.Allrightsreserved.| HTTPPluginforMySQL TheSQLendpointandJSON 28 shell> curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/db/SELECT+1 [ { "meta":[ {"type":8,"catalog":"def","database":"","table":", "org_table":"","column":"1","org_column":"","charset":63, "length":1,"flags":129,"decimals":0} ], "data":[ ["1"] ], "status":[{"server_status":2,"warning_count":0}] } ] 28. Copyright201Oracleand/oritsaliates.Allrightsreserved.| HTTPPluginforMySQL-IniIalversion HTTPBasicAuthenIcaIonforSSLandNon-SSL Noquerycachesupport Nocommercialthreadpoolpluginsupport NotallMySQLpluggableauthmethodssupported Unlimited:allSQLstatements Unlimited:everythingtheserverreturns 29 shell> curl --user basic_auth_user:basic_auth_passwd --url "http://.../sql/db/SELECT+col_float+FROM+sql_types" 29. Copyright201Oracleand/oritsaliates.Allrightsreserved.| HTTPPluginforMySQL GET=SELECT 30 shell> curl ... --url "http://../crud/db/sql_types/1" { "id" : "1", "col_char" : "CHAR(127)" , "col_null" : null, "col_date" : "2014-12-23", "col_decimal" : "123.45", "col_float" : "0.9999", "col_bigint" : "9223372036854775807" } shell> curl ... --url 'http://../crud/db/simple/' { "errno" : 2000, "error" : "The request URL must include a primary key value } 30. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLBinlogEvents FormerlyknownasBinlogAPI C++libraryforreadingBinarylog Canreadbinarylogfromserverorfromle Onetransportforeachkindofsource Currentlyhaveleandmysqltransport Decodebinarylogevents Containcodetodecodetheevents EventDrivenAPI 31 31. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQLBinlogEvents BinlogAPI C++ 2"transport" TCPTransport&FileTransport API hap://mysqlhighavailability.com/author/nehakumari/ 32 32. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQL5.7:JSON OpImizedforreadintensiveworkload NaIveJSONdatatypes NaIveinternalbinaryformatforecientprocessing&storage Built-inJSONfuncIons Allowingyoutoecientlystore,search,update,andmanipulateDocuments JSONComparator AllowsforeasyintegraIonofDocumentdatawithinyourSQLqueries IndexingofDocumentsusingGeneratedColumns InnoDBsupportsindexesonbothstoredandvirtualGeneratedColumns NewexpressionanalyzerautomaIcallyusesthebestfuncIonalindexavailable 6/19/15 Copyright2015,Oracleand/oritsaliates.Allrightsreserved 33 33. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQL5.7:JSON 34 mysql> CREATE TABLE employees (data JSON); Query OK, 0 rows affected (0,01 sec) mysql> INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}'); Query OK, 1 row affected (0,00 sec) mysql> INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}'); Query OK, 1 row affected (0,00 sec) mysql> select * from employees; +---------------------------+ | data | +---------------------------+ | {"id": 1, "name": "Jane"} | | {"id": 2, "name": "Joe"} | +---------------------------+ 2 rows in set (0,00 sec) 34. Copyright201Oracleand/oritsaliates.Allrightsreserved.| MySQL5.7:JSON DocumentValidaIon oninsertonly EcientAccess 35 mysql> INSERT INTO employees VALUES ('some random text'); ERROR 3130 (22032): Invalid JSON text: "Expect a value here." at position 0 in value (or column) 'some random text'. mysql> select jsn_extract(data, '$.name') from employees; +-----------------------------+ | jsn_extract(data, '$.name') | +-----------------------------+ | "Jane" | | "Joe" | +-----------------------------+ 2 rows in set (0,00 sec) 35. Copyright201Oracleand/oritsaliates.Allrightsreserved.| jsn_array() BuildaJSONarrayfromlistofexpressions jsn_object() BuildsJSONobjectsfromavariablelengthlist ofkey/valuepairs jsn_insert() Adds'missing'datatoJSONdocuments jsn_remove() RemovesaaributesfromexisIngJSON documents jsn_set() SetsaaributeswithinJSONdocuments jsn_replace() Replaces(butdoesn'tadd)aaributeswithin JSONdocuments jsn_append() Addsavaluetotheendofanarray jsn_merge() Mergestwoarrays jsn_extract() ReturnsavaluenestedinsideofaJSON document 6/19/15 36 JSONFuncIonsforCreaIng&ManipulaIng JSONDocuments Copyright2015,Oracleand/oritsaliates.Allrightsreserved 36. Copyright201Oracleand/oritsaliates.Allrightsreserved.| jsn_search() SearchforvalueswithinJSONdocumentsand returntheirlocaIons jsn_contains() Checksforaspecicelementandvalue jsn_contains_path() Determineifaspecicelementispresentina documentinaspecicposiIon jsn_valid() CheckifdocumentisavalidJSONdocument jsn_type() Findthetypeofavaluewithinadocument jsn_keys() ReturnsarraysofthekeynamesfortheJSON documents jsn_length() NumberofelementsinJSONdocument jsn_depth() LevelofnesInginJSONdocument jsn_unquote() HelpsmovefromJSONtootherMySQLtypes jsn_quote() HelpsmovefromotherMySQLtypestoJSON 6/19/15 37 QueryandSearchJSONFuncIons Copyright2015,Oracleand/oritsaliates.Allrightsreserved 37. Copyright201Oracleand/oritsaliates.Allrightsreserved.| GeneratedColumns VirtualGeneratedColumn Generatedontheywhenthecolumnisread Canbeindexed StoredGeneratedColumn Generatedwhenthereferencedcolumniswriaento Canbeindexed mysql> ALTER TABLE employees ADD name VARCHAR(100) GENERATED ALWAYS AS(jsn_extract(info, '$.name')) VIRTUAL; mysql> ALTER TABLE employees ADD INDEX(name); hap://mysqlserverteam.com/ 38 DiggingintoyourDocuments labs.mysql.com 6/19/15 Copyright2015,Oracleand/oritsaliates.Allrightsreserved 38. Copyright201Oracleand/oritsaliates.Allrightsreserved.| JOIN / 6/19/15 39 ? Copyright2015,Oracleand/oritsaliates.Allrightsreserved 39. Copyright201Oracleand/oritsaliates.Allrightsreserved.| 5.6 MySQLServerGA InnoDB& &NoSQL MySQLCluster-GA 2NoSQL200SQL 7.4 5.7 MySQLServerRC & JSONGroupReplicaMon 40. Copyright201Oracleand/oritsaliates.Allrightsreserved.| Theworld'smostpopularopensourcedatabase