【MySQL】5.7新特性之六

haoge0205發表於2016-11-22
MySql
寫在前面
  本系列文章基於 5.7.12 版本講述MySQL的新特性。從安裝，檔案結構，SQL ,最佳化 ，運維層面 複製，GITD等幾個方面展開介紹 5.7 的新特性和功能。同時也建議大家跟蹤官方blog和官方文件，以儘快知悉其新的變化。
6.1  最佳化(工具方面)增強
  5.7 版本中如果一個會話正在執行sql，且該sql 是支援explain的，那麼我們可以透過指定會話id，檢視該sql的執行計劃。
  1. EXPLAIN [options] FOR CONNECTION connection_id
該功能可以在一個會話裡面檢視另外一個會話中正在執行的長查詢。
  1. mysql> show processlist;
  2. +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
  3. | Id | User        | Host            | db   | Command | Time | State                                                  | Info             |
  4. +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
  5. | 1  | system user |                 | NULL | Connect | 78   | Connecting to master                                   | NULL             |
  6. | 2  | system user |                 | NULL | Connect | 78   | Slave has read all relay log; waiting for more updates | NULL             |
  7. | 3  | system user |                 | NULL | Connect | 78   | Waiting for an event from Coordinator                  | NULL             |
  8. | 4  | system user |                 | NULL | Connect | 78   | Waiting for an event from Coordinator                  | NULL             |
  9. | 5  | system user |                 | NULL | Connect | 78   | Waiting for an event from Coordinator                  | NULL             |
  10. | 6  | system user |                 | NULL | Connect | 78   | Waiting for an event from Coordinator                  | NULL             |
  11. | 8  | root        | localhost:47896 | NULL | Query   | 0    | starting                                               | show processlist |
  12. | 9  | root        | localhost:47897 | NULL | Query   | 3    | User sleep                                             | select sleep(10) |
  13. +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
  14. 8 rows in set (0.00 sec)
  15. mysql> explain FOR CONNECTION 9;
  16. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  17. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  18. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  19. | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
  20. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  21. 1 row in set (0.00 sec)
可以參考官方文件 和 work log 

6.2 hint 功能增強
 相比於MySQL5.6版本的hint 主要是index 級別的hint和控制表join 順序的hint,5.7.7之後，MySQL增加了最佳化器hint,來控制sql執行的方式，因為目前MySQL支援nest loop join，故暫時無hint來修改sql 的join方式。熟悉Oracle 的朋友是否會發現MySQL 和Oracle 在功能上越來越近了。話說回來5.7的hint (先別和 index hint 比較)的用法 ，和oracle 的類似: 
  1. SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
  2. SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
  3. SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
  4. SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
  5. EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...
最佳化器級別的hint分四種型別 
  1. Global: The hint affects the entire statement
  2. Query block: The hint affects a particular query block within a statement ，什麼是query block
  3. (SELECT ... ) UNION (SELECT /*+ ... */ ... ) --後面的括號裡面的稱為 query block 。
  4. Table-level: The hint affects a particular table within a query block
  5. Index-level: The hint affects a particular index within a table
其他更加詳細的資訊請參考  官方文件 

6.3 觸發器功能增強
 5.7版本之前一個表 對於每種action(INSERT,UPDATE, DELETE)和時機(BEFORE or AFTER) 只能支援一種型別的觸發器。新版本可以針對同一個action支援多個觸發器。

6.4 syslog 功能
  之前的版本，*nix系統上的MySQL支援將錯誤日誌傳送到syslog是透過mysqld_safe捕獲錯誤輸出然後傳遞到syslog來實現的。新的版本原生支援將錯誤日誌輸出到syslog,且適用於windows系統，只需要透過簡單的引數(log_syslog等)配置即可。參考 官方文件
  MySQL支援–syslog選項，可將在互動式模式下執行過的命令輸出到syslog中（*nix系統下一般是.mysql_history）。對於匹配“ignore”過濾規則（可透過 –histignore選項或者 MYSQL_HISTIGNORE環境變數進行設定）的語句不會被記入。關於mysql客戶端的日誌使用參見：官方文件
6.5 虛擬列
在MySQL 5.7中，支援兩種Generated Column，
1 Virtual Generated Column :只將Generated Column儲存在資料字典中表的後設資料，每次讀取該列時進行計算，並不會將這一列資料持久化到磁碟上；
注意:MySQL 5.7.8 以前 虛擬列欄位不支援建立索引。5.7.8之後Innodb支援在虛擬列建立輔助索引。
2 Stored Generated Column : 將Column持久化到儲存，會佔用一定的儲存空間。與Virtual Column相比並沒有明顯的優勢，因此，MySQL 5.7中，不指定Generated Column的型別，預設是Virtual Column。
建立虛擬列語法：
  1. col_name data_type [GENERATED ALWAYS] AS (expression)
  2.   [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
  3.   [[NOT] NULL] [[PRIMARY] KEY]
具體的例子
  1. CREATE TABLE triangle (
  2.   id int(10) not null primary key auto_increment,
  3.   sidea DOUBLE,
  4.   sideb DOUBLE,
  5.   sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
  6. );
  7. INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8),(12,16);
  8. mysql> select * from triangle;
  9. +----+-------+-------+--------------------+
  10. | id | sidea | sideb | sidec              |
  11. +----+-------+-------+--------------------+
  12. | 1  | 1     | 1     | 1.4142135623730951 |
  13. | 2  | 3     | 4     | 5                  |
  14. | 3  | 6     | 8     | 10                 |
  15. | 4  | 12    | 16    | 20                 |
  16. +----+-------+-------+--------------------+
  17. 4 rows in set (0.00 sec)
  18. mysql> explain select * from triangle where sidec > 10;
  19. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  20. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  21. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  22. | 1  | SIMPLE      | triangle | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4    | 33.33    | Using where |
  23. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  24. 1 row in set, 1 warning (0.00 sec)
  25. mysql> alter table triangle add key idx_sidec(sidec);
  26. Query OK, 0 rows affected (0.03 sec)
  27. Records: 0 Duplicates: 0 Warnings: 0
  28. mysql> explain select * from triangle where sidec > 10;
  29. +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  30. | id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
  31. +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  32. | 1  | SIMPLE      | triangle | NULL       | range | idx_sidec     | idx_sidec | 9       | NULL | 1    | 100.00   | Using where |
  33. +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  34. 1 row in set, 1 warning (0.00 sec)
看到這個例子,熟悉oracle的朋友可能會和函式索引作比較，兩者比較類似.使用虛擬列達到函式索引或者解決業務上的設計缺陷，但是個人不建議使用類似的功能，因為虛擬列在一定程度上也會給後期運維帶來潛在的風險和複雜度。網路上的例子基本都是使用虛擬列解決業務邏輯上的問題，違背了資料庫只儲存資料的初衷，思考一下MVC 框架的基本邏輯,業務邏輯要放到C 層或者V層，M層只存放資料即可。
各位看官自己要了解更詳細的虛擬列資料請移步官方文件 
小結
  本文算是對5.7版本功能類新特性做收尾，後面會介紹主從複製,GTID,InnoDB 效能最佳化方面以及資料庫升級相關的知識，這些特性更具有操作性，相對前面幾篇文章會更難寫。另外學習5.7的過程中，我檢視了很多MySQL的worklog，發現這裡簡直是一個MySQL資料寶庫，很多功能的需求和原理討論都可以在這裡找到。想深入學習MySQL的朋友可以多鑽研 http://dev.mysql.com/worklog/ 

相關文章
[1] 《MySQL 5.7 官方文件》
[2] 《MySQL 5.7 初探》
[3] 《MySQL 5.7新特性之一》
[4] 《MySQL 5.7新特性之二》
[5] 《MySQL 5.7新特性之三》
[6] 《MySQL 5.7新特性之四》
[7] 《MySQL 5.7新特性之五》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28939273/viewspace-2128849/，如需轉載，請註明出處，否則將追究法律責任。

相關文章