讀高性能MySQL(第4版)筆記07_優化數據類型(下)

来源:https://www.cnblogs.com/lying7/archive/2023/09/13/17695871.html
-Advertisement-
Play Games

1. ENUM(枚舉) 1.1. MySQL在存儲枚舉時非常緊湊,會根據列表值的數量壓縮到1或者2位元組中 1.2. 轉換為ENUM會使表變小 1.3. 如果表中有其他索引,減少主鍵大小也會使這些非主鍵索引小得多 1.4. ENUM列可以存儲一組預定義的不同字元串值 1.5. ENUM欄位是根據內部整 ...


1. ENUM(枚舉)

1.1. MySQL在存儲枚舉時非常緊湊,會根據列表值的數量壓縮到1或者2位元組中

1.2. 轉換為ENUM會使表變小

1.3. 如果表中有其他索引,減少主鍵大小也會使這些非主鍵索引小得多

1.4. ENUM列可以存儲一組預定義的不同字元串值

1.5. ENUM欄位是根據內部整數值排序的,而不是根據字元串本身

1.6. 查詢中使用FIELD()函數顯式地指定排序順序,但這會導致MySQL無法利用索引消除排序

1.7. MySQL將每個枚舉值存儲為整數,並且必須進行查找以將其轉換為字元串表示,因此ENUM列有一些開銷

1.8. 將CHAR/VARCHAR列聯接到ENUM列可能比聯接到另一個CHAR/VARCHAR列更慢

1.9. 通常的設計實踐是使用帶有整數主鍵的“查找表”,以避免在聯接中使用字元串

1.10. 更改ENUM中的有效值會導致需要做schema變更

2. 日期和時間類型

2.1. MySQL可以存儲的最小時間粒度是微秒

2.2. DATETIME

2.2.1. 從1000年到9999年,精度為1微秒

2.2.2. 以YYYYMMDDHHMMSS格式存儲壓縮成整數的日期和時間

2.2.3. 與時區無關

2.2.4. 8位元組的存儲空間

2.2.5. 以可排序、無歧義的格式顯示DATETIME值

2.2.6. ANSI表示日期和時間的標準方式

2.2.7. 保留日期和時間的文本表示

2.3. TIMESTAMP

2.3.1. 自1970年1月1日格林尼治標準時間(GMT)午夜以來經過的秒數

2.3.1.1. 與UNIX時間戳相同

2.3.2. 從1970年到2038年1月19日

2.3.2.1. 會遇到2038年的問題

2.3.2.1.1. 使用帶符號的32位INT,可以表達直到2038年的時間
2.3.2.1.2. 使用無符號的32位INT,可以表達直到2106年的時間
2.3.2.1.3. 使用64位,還可以超出這些範圍

2.3.3. 時間戳顯示的值依賴於時區

2.3.3.1. MySQL伺服器、操作系統和客戶端連接都有時區設置

2.3.3.2. 存儲值0的TIMESTAMP在美國東部標準時間(EST)中顯示為1969-12-31   19:00:00,與格林尼治標準時間(GMT)差5小時

2.3.4. 4位元組的存儲空間

2.3.5. FROM_UNIXTIME()函數來將UNIX時間戳轉換為日期

2.3.6. UNIX_TIMESTAMP()函數將日期轉換為UNIX時間戳

2.3.7. 保留與所使用時區相關的值

2.3.8. TIMESTAMP的行為規則很複雜,並且在不同的MySQL版本中會發生變化,因此你應該驗證資料庫的行為是否符合需要。在對TIMESTAMP列進行更改後,通常最好檢查SHOW CREATE TABLE命令的輸出

2.3.9. 特殊屬性

2.3.9.1. 當插入一行記錄時沒有指定第一個TIMESTAMP列的值,MySQL會將該列的值設置為當前時間

2.3.9.2. 當更新一行記錄時沒有指定第一個TIMESTAMP列的值,MySQL預設也會將該列的值更新為當前時間

2.3.9.3. 可以為任何TIMESTAMP列配置插入和更新行為

2.3.9.4. TIMESTAMP列在預設情況下為NOT NULL,這也和其他的數據類型不一樣

3. 位壓縮數據類型

3.1. 從技術上來說都是字元串類型

3.2. BIT

3.2.1. 可以使用BIT列存儲一個或多個true/false值

3.2.2. 視為字元串類型,而不是數字類型

3.2.3. 避免使用這種類型

3.2.3.1. 建議使用TINYINT

3.3. SET

3.3.1. 如果需要存儲多個true/false值,可以考慮使用MySQL原生的SET數據類型

3.3.2. 以一組打包的位的集合來表示的

3.3.3. 更有效地利用存儲空間

3.3.4. FIND_IN_SET()和FIELD()等函數,使其易於在查詢中使用

3.3.5. 替代方法是使用整數作為二進位位的打包集合

3.3.5.1. 可以在不使用ALTER TABLE的情況下更改欄位表示的“枚舉”

3.3.5.2. 查詢更難編寫和理解

4. JSON數據類型

4.1. 決定使用原生SQL還是JSON取決於在資料庫中存儲JSON的便捷性是否大於性能

4.2. 如果每天訪問這些數據數百萬次或數十億次,速度差異就會累加起來

4.3. 使用SQL列的速度仍然更好於JSON列

5. 標識符

5.1. 引用行及通常使其唯一的方式

5.2. 為標識符列選擇數據類型時,應該與聯接表中的對應列保持一致

5.3. 在為標識符列選擇類型時,不僅需要考慮存儲類型,還需要考慮MySQL如何對該類型執行計算和比較

5.4. 在可以滿足值的範圍的需求,並且預留未來增長空間的前提下,應該選擇最小的數據類型

5.5. 整數通常是標識符的最佳選擇

5.5.1. 速度快

5.5.2. 自動遞增

5.6. 對於標識符來說,ENUM和SET類型通常是糟糕的選擇

5.6.1. ENUM和SET列適用於保存訂單狀態或產品類型等信息

5.7. 應避免使用字元串類型作為標識符的數據類型

5.7.1. 很消耗空間

5.7.2. 比整數類型慢

5.8. 對於完全“隨機”的字元串要非常小心

5.8.1. MD5()、SHA1()或UUID()生成的字元串

5.9. 新值會任意分佈在很大的空間內,這會減慢INSERT和某些類型的SELECT查詢的速度

5.9.1. 插入的值會寫到索引的隨機位置,所以會使得INSERT查詢變慢

5.9.2. 導致頁分裂、磁碟隨機訪問,以及對於聚簇存儲引擎產生聚簇索引碎片

5.9.3. SELECT查詢也會變慢,因為邏輯上相鄰的行會廣泛分佈在磁碟和記憶體中

5.9.4. 對於所有類型的查詢,隨機值都會導致緩存的性能低下,因為它們會破壞引用的局部性,而這正是緩存的工作原理

5.10. 存儲通用唯一標識符(UUID)值,則應該刪除破折號

5.10.1. 更好的做法是,使用UNHEX()函數將UUID值轉換為16位元組的數字,並將其存儲在一個BINARY(16)列中

5.10.2. 可以使用HEX()函數以十六進位格式檢索值

6. 特殊數據類型

6.1. IPv4地址

6.1.1. 使用VARCHAR(15)列來存儲

6.1.2. 實際上是32位無符號整數,而不是字元串

6.1.2.1. 小數點將地址分成四段的表示方法只是為了讓人們閱讀容易

6.1.2.2. 存儲為無符號整數

6.1.3. INET_ATON()和INET_NTOA()函數來在這兩種表示形式之間進行轉換

6.1.4. 從VARCHAR(15)的約16位元組縮減到無符號32位整數的4位元組

7. schema設計中的陷阱

7.1. 太多的列

7.2. 太多的聯接

7.2.1. MySQL限制每個聯接有61個表

7.3. 全能的枚舉

7.3.1. 要小心過度使用ENUM

7.4. 變相的枚舉

7.4.1. ENUM列允許在列中保存一組已定義值中的單個值

7.4.2. SET列則允許在列中保存一組已定義值中的一個或多個值

7.4.3. 如果真和假兩種情況不會同時出現,那麼毫無疑問應該使用ENUM列而不是SET列

7.5. NULL不是虛擬值

7.5.1. 在表中存儲事實上的“空值”,可以使用0、特殊值或空字元串作為代替

7.5.2. 當需要表示未知值時,不要太害怕使用NULL

7.5.3. 在某些情況下,使用NULL比使用某個虛擬常數更好

7.5.4. MySQL會對NULL值進行索引,而Oracle則不會

8. schema管理

8.1. 修改schema是資料庫工程師必須承擔的最常見任務之一

8.2. schema變更管理視為“數據存儲平臺”的一部分

8.3. 儘可能靠近現有的軟體部署工具和工作流程

8.4. 應該使用能夠集成針對schema更改的基本檢測的工具,以確保滿足一些基線需求

8.5. 如果所在的組織使用多種編程語言且發展迅速,請確保不會意外地引入人為瓶頸

8.6. Skeema是一個在跨多個環境的版本控制中管理schema更改的傑出開源解決方案

8.7. gh-ost是由GitHub的數據工程團隊創建的,專門作為一種管理schema更改過程的解決方案,既不影響服務,也不使用觸發器

8.7.1. 其使用的是二進位日誌而不是觸發跟蹤變化,這是更安全的選擇,所以不必擔心觸發器的性能影響


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 1. 什麼是IDisposable? IDisposable介面是一個用於約定可進行釋放資源操作的介面,一個類實現該介面則意味著可以使用介面約定的方法Dispose來釋放資源。其定義如下: public interface IDisposable { void Dispose(); } 上述描述中可 ...
  • 針對工業來講,特殊是流程行業,視圖建模(Web組態)是必不可少應用場景,因為有很多工序要直觀的展示工藝流程圖。該功能也廣泛應用在:鋼鐵、煤炭、水務、中醫葯、能源、航天、加工製造、風電、儲能等領域。 ...
  • 前言 組合模式,英文名稱是:Composite Pattern。當我們談到這個模式的時候,有一個物件和這個模式很像,也符合這個模式要表達的意思,那就是“俄羅斯套娃”。“俄羅斯套娃”就是大的瓷器娃娃裡面裝著一個小的瓷器娃娃,小的瓷器娃娃裡面再裝著更小的瓷器娃娃,直到最後一個不能再裝更小的瓷器娃娃的那個 ...
  • 引子 "某呼"網友提問“C#為什麼非要把函數叫方法?”,看到這個問題還真不知道怎麼回答。要想知道C#為什麼叫方法,還得從傳統面向過程語言說起。 方法的由來 函數(function)是面向過程編程語言里,對可以獨立調用代碼段的另一種叫法,因為通常有數個輸入和一個輸出,很像數學里的那個函數。比如C語言中 ...
  • 在前面隨筆《循序漸進介紹基於CommunityToolkit.Mvvm 和HandyControl的WPF應用端開發(1)》中介紹了Mvvm 的開發,以及一些界面效果,本篇隨筆繼續深入探討基於CommunityToolkit.Mvvm 和HandyControl的WPF應用端開發,介紹如何整合Sql... ...
  • 文件與文件流的區別(自己的話): 在軟體開發過程中,我們常常把文件的“讀寫操作”,與“創造、移動、複製、刪除操作”區分開來。其中,文件的“讀寫操作”是由System.IO命名空間下fileStream類控制的。而文件的“創造、移動、複製、刪除操作”是由file類控制的。 當然了,還有很多和這兩個類功 ...
  • 1、背景描述 出於安全考慮,需要禁止使用root用戶通過ssh遠程登錄Linux 禁用root用戶遠程登錄後,需要提供一個許可權用戶用於ssh遠程登錄 2、創建擁有sudo許可權的用戶 2.1、創建一個普通用戶rain useradd命令用於創建一個用戶, 選項 -m 表示創建用戶的主目錄, -c 表示 ...
  • Intel 海光 鯤鵬920 飛騰2500 CPU性能對比 為了讓程式能快點,特意瞭解了CPU的各種原理,比如多核、超線程、NUMA、睿頻、功耗、GPU、大小核再到分支預測、cache_line失效、加鎖代價、IPC等各種指標(都有對應的代碼和測試數據)都會在這系列文章中得到答案。當然一定會有程式員 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...