MySQL實戰筆記

語言: CN / TW / HK

1.數字類型:

①如果一個數字是unsigned,它們相減的值如果是負數會報錯out of range。解決辦法:

SET sql_mode='NO_UNSIGNED_SUBTRACTION';

②不要用float和double類型,使用decimal表示小數,而如果表示金額最好用分。因為decimal最大(8,2),只能表示到百萬級別。

③自增主鍵要用big int,因為int最大值是42億,到達上限後插入會報錯。自增主鍵在5.7及以下版本不會持久化當前主鍵指針,重啟後接着最大值往後增加,這樣在關聯表的時候可能出問題。

2.字符類型:

①CHAR還是VARCHAR?CHAR最大長度255字符,VARCHAR最大65535長度字符。由於一般會用UTF8MB4,每個字符佔用4字節,用CHAR也是變長的。所以推薦用VARCHAR。

②排序規則。設置表的時候要設置排序規則,一般為utf8_general_ci,作用是設置數據庫比較字符串的方法,ci結尾表示不區分大小寫,cs結尾區分大小寫,bin是按二進制比較。

③數據庫字符枚舉。如性別字段只能寫M或F。

Create Table: CREATE TABLE `User` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB


或者


Create Table: CREATE TABLE `User` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_chk_1` CHECK (((`sex` = _utf8mb4'M') or (`sex` = _utf8mb4'F')))
) ENGINE=InnoDB

3.日期類型:

用timestamp、datetime還是int?timestamp最大值2038年1月19日,有隱患。int可維護性差。推薦datetime。

4.JSON格式數據:

MySQL5.7之後已經支持直接存JSON數據,適合存靜態數據。例子如下:

CREATE TABLE UserLogin (
    userId BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(userId)
);

--查詢方式:

SELECT
    userId,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;

--等義於

SELECT
    userId,
    loginInfo->>"$.cellphone" cellphone,
    loginInfo->>"$.wxchat" wxchat
FROM UserLogin;

對JSON設置索引的辦法是先產生虛擬字段,再對虛擬字段設置索引,語句如下:

ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");

ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);

5.主鍵設置:

①自增主鍵?只能用於非核心表。問題有:5.7及以下的重啟回溯;併發性能;分庫分表不能全局唯一

②UUID?UUID = 時間低(4字節)- 時間中高+版本(4字節)- 時鐘序列 - MAC地址,所以時間是逆序的,8.0提供UUID_TO_BIN解決時間逆序的問題。

6.表壓縮:

主要是頁壓縮,有兩種,一種是COMPRESS,一種是TPC。

COMPRESS頁壓縮內存緩衝池存在壓縮和解壓的兩個頁,會嚴重影響性能,一般用於日誌,監控等性能要求低的表。比如按時間分庫,舊庫基本不修改,用這種方式存儲,空間消耗小。

TPC是需要按操作系統的文件系統的最小空間對齊壓縮存儲數據,需要刷新到磁盤的時候壓縮一次,通過減少IO提高性能。

7.索引:

索引是提升查詢速度的一種數據結構,MYSQL中使用B+樹存儲索引。B+樹對無序索引進行增刪時,性能開銷較大,所以要求主鍵必須是有序的,所以不推薦UUID,推薦排序UUID。

SELECT * FROM schema_unused_indexes
WHERE object_schema != 'performance_schema';

以上是查看沒有用到的索引的語句,用於索引優化。

8.索引的組織(MVCC機制):

①聚集與非聚集:對於INNODB引擎,分為聚集索引和非聚集索引,主鍵排序後組織數據按B+樹存儲就是聚集索引。其他的索引排序後只保存一個主鍵,按B+樹存儲,就是非聚集索引。對於堆表,全都是非聚集索引。

②通過虛擬列實現索引優化:虛擬列不佔存儲空間。前面介紹過對JSON數據產生虛擬列,然後加索引的辦法。這個辦法同樣適用於where條件中有函數的情況,可以將函數作為虛擬列並加索引。

③索引覆蓋:當非聚集索引中的列已經覆蓋了要查詢的列,就不會再回表查詢主鍵的索引數據。這樣可以很大的提高性能。

9.JOIN連接:

有兩種實現,Nested Loop Join和Hash Join。Nested Loop Join先查驅動表,再逐條查關聯表。Hash Join先對驅動表的查出來的記錄建立哈希表,關聯表去關聯哈希表中的記錄。