MySQL 8.0有那些值得注意的新發展 - 梶山隆輔
===

{%hackmd LcL4-VI0SGitSiINTuy4rA %}

- MySQL FB 社群推廣 - [MySQL User Group Taiwan](https://www.facebook.com/groups/taiwanmysqlusergroup/about/)

- MySQL 5.7
- 3x perfromance
- replication
- optimizer cost model
- json support
- improved security
- sys & performance schema
- GIS
- MySQL InnoDB Cluster
- Group Replication
- Router
- Shell
- MySQL 8.0
- NoSQL Document Store
- JSON
- CTEs
- Window Functions
- Data Dictionary
- big change, so not using 5.x as version number

- NoSQL + SQL = MySQL
- Flexible APIs
- Hybrid Data Models
- structural data (deticated column)
- unstructural data, sth that are hard to store in single table, say, product may be TV, DVD player, etc.. which require varity of column
- Porven Transaction Management
- Reliable Data Consistent
- Simplified Operations
- Pains when running seperated RDBMS + NoSQL DB
- data sync between JSON and table
- need to manage multiple products with different tools
- MySQL 8.0 provide unified API to manipulate table and JSON document
- still support SQL
- for people who hate SQL <3, say, CRUD API
- so data is always in sync, and you can *JOIN* table and document
- solve the above 2 pains
- Continuous Delivery Model (in MySQL 8.0)
- new feature will be available every single month
- no need wait for next major version
- large set of features can be added per minor versions
- so to responds to cusomers' bug report and feature requests (customer may need a bug fix, or need a new feature to resolve original need)
- Example - 8.0 - Alter Table - Instant Add Column
- only a metadata change

## MySQL Data Dictionary before 8.0

- Before - meta data are stored in different place, in
- file system, say, `*.frm`, `*.trg`, `*.opt`
- system tables, `mysql.*`
- innodb system tables
- In 8.0 - all meta in Data Dictionary tble
- simplify delivery process
- performance, as developer don't need to join across multiple resource

## 8.0 New Feature

- Enhanced GIS Support
- support Geography and Spatial Reference Systems (SRS) in 8.0
- Unicode as Default
- default char set: utf8mb4
- Up to 16X faster performance
- JSON data type
- JSON function
- JSON_TABLE Function - convert json data into table, so use sql to operate it.
- NoSQL API - X Dev API
- mysqlsh - CLI tool that support sql, js, python, etc..
- JS example:

  session.getSchema('world').getTable('city').select().where('name = "Tokyo"')

- table id can be generated by sibling json field
- replicate only changed field of document (Partial JSON Update)
- also reduce log size
- window function
- making application more scalable
- CATS - transaction scheduling to boost performance
- Better cost estimation
- column histograms - for optimization
- Better handling of hot row contention
- `SKIP LOCKED` and `NOWAIT`
- InnoDB Cluster
- MySQL HA solution
- MySQL Group Replication

- [MySQL version Parameters](http://tmtm.github.io/mysql-params), for checking MySQL parameter across multiple versions

###### tags: `COSCUP2019` `全面探究MySQL開源資料庫` `IB501`