9 months ago

昨天參加人生第一次的Coscup,當初看到議程有一間教室排了整天的OSS(開源軟體) Database的課程,課程主要都是MySQL(部分PostgreSQL),而MySQL講者都是來自Oracle和MySQL國外的核心開發者!
早上兩場講了MySQL 8.0的新功能,下午主講MySQL 5.7的HA,聽完收穫很大,十分感謝開源社群的經營,決定自己來整理一篇分享。

MySQL 8.0

MySQL下一版決定直接跳到8.0,目前還在測試的階段,官網有詳細條列所有的更動 What Is New in MySQL 8.0,這邊條列一些講者有提到的新功能

  1. Account management
    在8.0會加入Role增強帳號管理功能,以往只能為每個帳號設定不同的權限,8.0後可以設定好Role的權限並分配給不同的帳號,這樣在帳號權限管理會方便許多。
  2. InnoDB enhancements 從InnoDB被設為默認引擎後,MySQL的開發越來越偏重於InnoDB上(我也沒用過其他引擎lol),很多新功能都優先甚至獨家支援InnoDB,以下有幾個功能的增加與修正: a. AUTO_INCREMENT會寫入log並保證不會重用
    b. memcached插件支援Multiple Get與Range Query
    c. innodb_deadlock_detect新的選項:在高併發環境開啟死鎖檢測會造成性能消耗,可以透過此選項關閉;並透過innodb_lock_wait_timeout來處理死鎖問題比較有效率
  3. JSON功能的強化,包含更多的內建函式
  4. 支援Common table expression(CTE),CTE有點像是Derived Table,生命週期同樣僅限於單一Query,但是效能比較好而且可以做到RECURSIVE 遞迴的功能,十分的好玩,以下示例參考 An Introduction to MySQL CTEMySQL 13.2.11.9 WITH Syntax (Common Table Expressions): ##### RECURSIVE CTE 遞迴式的CTE語法是
    SELECT ...      -- return initial row set
    UNION ALL
    SELECT ...      -- return additional row sets
    
    第一個SELECT代表初始化,第二個SELECT會產生而外的欄位並決定遞迴的結束方式 以下為官方的範例
    WITH RECURSIVE cte AS
    (
    SELECT 1 AS n, 1 AS p, -1 AS q
    UNION ALL
    SELECT n + 1, q  2, p  2 FROM cte WHERE n < 5
    )
    SELECT  FROM cte
    
    可以看見n/p/q一開始分別初始化(1,1,-1),接著遞迴五次並輸出
    +------+------+------+
    | n    | p    | q    |
    +------+------+------+
    |    1 |    1 |   -1 |
    |    2 |   -2 |    2 |
    |    3 |    4 |   -4 |
    |    4 |   -8 |    8 |
    |    5 |   16 |  -16 |
    +------+------+------+
    
    這樣要寫Fabnocci數列都不成問題了!
    WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
    )
    SELECT  FROM fibonacci;
    /* 上下兩寫法都是一樣的,只是變數宣告方式不同 /
    WITH RECURSIVE fibonacci AS
    (
    SELECT 1 as n, 0 as fib_n, 1 as next_fib_n
    UNION ALL
    SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
    )
    SELECT  FROM fibonacci;
    
    因為遞迴可能會沒注意就爆掉造成性能影響,所以MySQL有參數可以設定cte_max_recursion_depth 遞迴深度max_execution_time SELECT最大運算時間 接著官網有一個很有趣的範例: 有一個Table存放職員的id、名稱與直屬上司,但公司架構有很多層所以直屬上司可能還有直屬上司,此時要條列出每位員工的所有上司,Data Fiddle連結
CREATE Table test (
    _id INT ,
    name VARCHAR(255),
    boss INT
);
INSERT INTO test VALUES (1,"Jerry",1);
INSERT INTO test VALUES (2,"Audi",1);
INSERT INTO test VALUES (3,"Jessie",1);
INSERT INTO test VALUES (4,"YJ",2);
INSERT INTO test VALUES (5,"Pan1",3);
INSERT INTO test VALUES (6,"Pan2",3);
INSERT INTO test VALUES (7,"Pan3",2);

/*找出員工所有的直屬上司*/
WITH RECURSIVE test_all_subordinates(id,name,boss,path) AS
(
SELECT id, name, boss, CAST(id AS CHAR(255))
    FROM test
    WHERE boss IS NULL
UNION ALL
SELECT e.id, e.name, e.boss, CONCAT(ep.path, "," , e.id)
    FROM test_all_subordinates AS ep JOIN test AS e
      ON ep.id = e.boss
)
SELECT * FROM test_all_subordinates;

/* 找出員工的下屬,但此SQL結果不合乎我的預期QQ */
WITH RECURSIVE test_all_subordinates(id,name,boss,path) AS
(
SELECT id, name, boss, CAST(id AS CHAR(255))
    FROM test
    WHERE boss IS NOT NULL
UNION ALL
SELECT e.id, e.name, e.boss, CONCAT(ep.path, "," , e.id)
    FROM test_all_subordinates AS ep JOIN test AS e
      ON ep.boss = e.id
)
SELECT * FROM test_all_subordinates;

MySQL 5.7

還有一些功能是我之前沒有注意到的,講師提了才大吃一驚 原來MySQL早已支援JSON!

JSON

MySQL對於JSON的操作支援度相當不錯,CRUD不是問題,還可以透過virtual column支援index,可以參考我在DB fiddle上的操作,

CREATE TABLE test (
  id INT,
  content JSON,
  name VARCHAR(255) AS (JSON_UNQUOTE(content->'$.name')) UNIQUE KEY
);

INSERT INTO test (id, content) VALUES (1, '{"name":"Jerry", "age": 14}');
INSERT INTO test (id, content) VALUES (2, '{"name":"Audi", "age": 24, "score": 10, "children":[{"name":"child1"},{"name":"child2"},{"name":"child3"}]}');

INSERT INTO test (id, content) VALUES (3, '{"name":"Jessie", "age": 18, "score": 10}');

/* 判斷是否為合法json格式 */
SELECT json_valid(content) FROM test;

/* 取出所有的Key */
SELECT JSON_KEYS(content) FROM test;

/* 取出json物件的某個內容 */
SELECT json_extract(content, "$.name") FROM test;
/* 不同寫法,相同效果 */
SELECT content->'$.name' FROM test;

/* 若Key存在則更新值,反之創建 */
SELECT JSON_SET(content, '$.company', 'panmedia') FROM test;
/* 可以改用Replace,如果Key不存在則什麼事都不會發生 */
SELECT JSON_REPLACE(content, '$.company', 'panpanmedia') FROM test;

/* 刪除 */
SELECT JSON_REMOVE(content, '$.company') FROM test;

/* 測試Index */
EXPLAIN SELECT name from test;

參考資料: Indexing JSON documents via Virtual Columns

MySQL 高可用(HA)

Group Replication & InnoDB Cluster


這部分是下午的精華,講者表示目前MySQL已經走到HA的願景,但我之前沒有使用MySQL都是在單機上面跑,對於這方面有聽沒有懂,講者後來有Demo用MySQL Shell在單機上部署多台MySQL Server,且可以設定要單機寫入或是多機寫入,MySQL會自己同步多台Server的資料,並在某台Server掛掉或加入後不影響資料。
更詳細可以看官方的Mysql High Availability部落格

MySQL Shell與X Protocol


目前在5.7可以先在MySQL Server 安裝插件的方式使用MySQL X,X Protocol會要求佔用port 33060,接著Mysql Shell在連接Server,開啟默認使用javascript,也提供python的操作介面;
用法看起來跟MongoDB Shell有點相似,都是提供document文檔的操作方式,詳細的內容可以參考MySQL支援JSON文件面面觀(之二),以下是我在OSX上測試的流程

// 先進Mysql monitor
> mysql -u root -hlocalhost -P3306 -p 
// 我用OSX他提示要換密碼
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPass';
// 安裝插件
mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
// 查看插件是否安裝成功,此時理論上會看msqlx在列表中
mysql> show plugins;

接著到官網下載 MySQL Shell並安裝

// 連線方式與mysql雷同,可以用不同的帳號與權限
> mysqlsh -u root -h localhost -p
mysql-js> session
// schema 對應就是 database
mysql-js> session.createSchema('mydemo')
mysql-js> \u mydemo
// collection就是table
mysql-js> db.createCollection('collection1');
// 新增
mysql-js> db.collection1.add({"name":"Jerry", "age": 14})
mysql-js> db.collection1.add({"name":"Audi", "age": 24, "score": 10, "children":[{"name":"child1"},{"name":"child2"},{"name":"child3"}]})
// 查詢
mysql-js> db.collection1.find()
mysql-js> db.collection1.find("age > 10").fields(["name", "age"]).sort("name ASC")
// 修改
mysql-js> db.collection1.modify("name='Audi'").set("age", 30)
// 刪除
mysql-js> db.collection1.remove("name='Audi'")

接著用MySQL Client連接Server(3306 port),我使用Workbench進入後可以看到Table中有剛才創建的Mydemo,接著看裡頭的Table collection1的Schema,_id是由UUID產生而doc欄位則是JSON格式

結論,MySQL可支援SQL與NoSQL,並原生支援HA

← 後端開發練習:使用Koa2 + Graphql + Mysql並採用CI/CD開發流程(下) 粉絲專頁留言自動回話與啟動Messenger私訊 →
 
comments powered by Disqus