COSCUP Why should we move to MySQL 8.0筆記

講者彭立勛

議程連結

 

引言

這是一篇研討會的筆記,再加上個人的理解和實驗。對一些東西不太了解,所以有些地方會一筆帶過。

解決的BUG

1. Bug #199 Innodb autoincrement stats los on restart

從bug編號就能看出這個BUG多麼遠古……

這個問題是每次MySQL重啟時,AUTO_INCREMENT的值是以這個資料表的最大AUTO_INCREMENT值為準,這個問題可能影響有外鍵的表。參考這個範例

以下範例(參考資料2)

建立一個有auto_increment主鍵的表,並插入三筆資料

mysql> CREATE TABLE a(id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT);

Query OK, 0 rows affected (0.05 sec)



mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)



mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)



mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

+----+

3 rows in set (0.00 sec)

 

接下來刪除、插入資料

mysql> DELETE FROM a WHERE id = 3;

Query OK, 1 row affected (0.01 sec)



mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)



mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

|  4 |

+----+

3 rows in set (0.00 sec)



mysql> DELETE FROM a WHERE id = 4;

Query OK, 1 row affected (0.01 sec)



mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

+----+

2 rows in set (0.00 sec)

 

重啟MySQL,再插入一筆資料

MySQL5.7MySQL8.0
mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)



mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

+----+

3 rows in set (0.00 sec)
mysql> insert into a values(NULL);

Query OK, 1 row affected (0.02 sec)



mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

|  5 |

+----+

3 rows in set (0.00 sec)

可以發現兩個版本插入的id不同,5.7版插入id3(迷惑行為),其他表有關聯但沒設外鍵約束估計會出錯

 

另外,實測5.7版的MyISAM沒有這個問題。

2. Bug #25922  InnoDB crash recovery changes: make DDL in MySQL 'atomic'

 

新功能

CTE (Common Table Expressions)

根據文檔的解釋:「公用表表達式(CTE)是一個命名的臨時結果集,僅存在於單個SQL語句範圍內」

恭喜,這下終於可以在MySQL用遞迴了

。例如之前的購物車專案,商品用鄰接表(Adjacency List)紀錄階層

1.jpg

 

這時候要取出Breadcrumbs就很簡單(?)了

2.jpg

 

另外新功能還有Windows function、Instant Add Columns等等

 

重大更新

1. 放棄MyISAM

8.0以後的版本的系統資料庫(即mysql資料庫)完全不使用MyISAM引擎,改用InnoDB

幾種情況可以考慮繼續使用MyISAM,例如需要經常在不同的機器間copy資料庫,或是只插入不修改資料的資料庫。但是仍建議使用InnoDB而非MyISAM

 

2. 持久化系統變數

在8.0以前,要更改系統變數,用SET GLOBAL指令只是「暫時」的,因為只要重啟MySQL,系統變數又會變回原樣。

例如要把ONLY_FULL_GROUP_BY這個模式刪掉

3.jpg

重啟MySQL之後,會發現ONLY_FULL_GROUP_BY又跑回來了

4.jpg

要永久修改,必須修改/etc/mysql/my.cnf檔案(Windows為my.ini檔)

上例刪掉ONLY_FULL_GROUP_BY,就在my.ini檔加上紅框中的那幾行。重啟MySQL後會發現ONLY_FULL_GROUP_BY不會再跑回來了

5.jpg

但是這樣的修改方式對雲端伺服器不友好,因為不方便更改伺服器的配置

8.0版本有了新的解決方案,使用關鍵字”PERSIST”可以永久修改系統變數

依然是刪掉ONLY_FULL_GROUP_BY的例子

6.jpg

這時候會產生一個mysqld-auto檔(在Laragon的情況,檔案是"laragon\data\mysql-8\mysqld-auto.cnf")

這是一個json格式的檔案,記錄了何時更動了哪些系統變數

{

  "Version":1,

  "mysql_server":{

     "sql_mode":{

        "Value":"STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION",

        "Metadata":{

           "Timestamp":1596296883902530,

           "User":"root",

           "Host":"localhost"

        }

      }

   }

}

 

之後重啟MySQL,會發現sql_mode不會變回原樣了!

也可以重置PERSIST,詳情請參考文檔

 

3. 降序索引(Descending Index)

在8.0版本前,即使在複合索引裡面指定其中一個欄位為DESC,實際上還是ASC

例如一個customers有三萬筆客戶資料,並且新增了一個姓名複合索引

CREATE INDEX first_name_last_name_index ON customers (first_name ASC, last_name DESC)

 

接下來排序姓名,可以看到升序的情況下有用到索引

SELECT * FROM customers ORDER BY first_name ASC, last_name ASC LIMIT 100

7.jpg

但是用last_name DESC,卻沒用到索引

SELECT * FROM customers ORDER BY first_name ASC, last_name DESC LIMIT 100

8.jpg

在8.0以上的版本中,DESC索引終於名符其實了

9.jpg

 

4. Resource Groups

目前僅支持CPU

例如user1僅能使用第2、3號CPU等管理

 

4. 安全性

數據加密、REDO/UNDO、資料表加密

 

5. 高併發(High Concurrency)性能提升

6. IO-Bound

7. utf8mb4性能提升

8. Histogram

數據分布更加精準

9. Code Style

使用Google C++ coding style。

8.0後,MySQL Server和InnoDB使用統一的code stylec

 

附錄

在Windows安裝MySQL8.0的坑

安裝完後啟動MySQL會噴錯

10.jpg

解決方法是安裝Microsoft Visual C++ 2015 - 2019 Redistributable(下載連結

11.jpg

之後就能正常啟動了

 

Laragon無法啟動MySQL8的問題

參考答案

12.jpg

參考資料

  1. https://www.cnblogs.com/linux130/p/5945958.html
  2. https://lefred.be/content/bye-bye-bug-199/
  3. https://www.cnblogs.com/linux130/p/5945958.html
  4. https://www.mysqltutorial.org/mysql-adjacency-list-tree/
  5. https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/
  6. https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
  7. https://dev.mysql.com/doc/dev/mysql-server/8.0.12/PAGE_CODING_GUIDELINES.html

 

秋末預言 PHP 與 Python 大師
名稱太中二,給推
sharkHead
11個月前