半個月時間把 MySQL 重新鞏固了一遍，梳理了一篇幾萬字 “超硬核” 文章！

民工哥發表於2023-02-15
MySql

在這之前，我們學習過了：資料庫的核心知識點是哪些？關係型資料工作流程、設計理論與流程，以及SQL 語法基礎、語句練習與最佳化。以上這些，都是學習資料庫的必備入門基礎知識點，需要重點掌握。

今天開始，我們進入死磕資料庫系列之：MySQL 資料庫的學習之旅。

注：本文是不定期更新內容的，如有幫助，大家點贊支援一波！！！！

MySQL 資料庫簡介

MySQL 是最流行的關係型資料庫管理系統，在 WEB 應用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關聯式資料庫管理系統)應用軟體之一。

MySQL 是一個關係型資料庫管理系統，由瑞典 MySQL AB 公司開發，目前屬於 Oracle 公司。MySQL 是一種關聯資料庫管理系統，關聯資料庫將資料儲存在不同的表中，而不是將所有資料放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

  • MySQL 是開源的，目前隸屬於 Oracle 旗下產品。
  • MySQL 支援大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。
  • MySQL 使用標準的 SQL 資料語言形式。
  • MySQL 可以執行於多個系統上，並且支援多種語言。這些程式語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
  • MySQL 對PHP有很好的支援，PHP 是目前最流行的 Web 開發語言。
  • MySQL 支援大型資料庫，支援 5000 萬條記錄的資料倉儲，32 位系統表檔案最大可支援 4GB，64 位系統支援最大的表檔案為8TB。
  • MySQL 是可以定製的，採用了 GPL 協議，你可以修改原始碼來開發自己的 MySQL 系統。

在日常工作與學習中，無論是開發、運維、還是測試，對於資料庫的學習是不可避免的，同時也是日常工作的必備技術之一。在網際網路公司，開源產品線比較多，網際網路企業所用的資料庫佔比較重的還是MySQL。更多關於MySQL資料庫的介紹，有興趣的讀者可以參考官方網站的文件和這篇文章：可能是全網最好的MySQL重要知識點

MySQL 安裝

MySQL 8正式版8.0.11已釋出，官方表示MySQL8要比MySQL 5.7快2倍，還帶來了大量的改進和更快的效能！到底誰最牛呢？請看：MySQL 5.7 vs 8.0，哪個效能更牛？

詳細的安裝步驟請參閱：CentOS 下 MySQL 8.0 安裝部署，超詳細！

MySQL 多例項配置

MySQL資料庫入門——多例項配置

MySQL 命令基礎操作

Windows服務

-- 啟動MySQL
net start mysql

-- 建立Windows服務
sc create mysql binPath= mysqld_bin_path(注意：等號與值之間有空格)

連線與斷開伺服器

mysql -h 地址 -P 埠 -u 使用者名稱 -p 密碼

SHOW PROCESSLIST -- 顯示哪些執行緒正在執行
SHOW VARIABLES -- 顯示系統變數資訊

資料庫操作

-- 檢視當前資料庫
SELECT DATABASE();

-- 顯示當前時間、使用者名稱、資料庫版本
SELECT now(), user(), version();

-- 建立庫
CREATE DATABASE[ IF NOT EXISTS] 資料庫名 資料庫選項
    資料庫選項：
        CHARACTER SET charset_name
        COLLATE collation_name

-- 檢視已有庫
    SHOW DATABASES[ LIKE 'PATTERN']

-- 檢視當前庫資訊
    SHOW CREATE DATABASE 資料庫名

-- 修改庫的選項資訊
    ALTER DATABASE 庫名 選項資訊

-- 刪除庫
    DROP DATABASE[ IF EXISTS] 資料庫名
        同時刪除該資料庫相關的目錄及其目錄內容

更多相關的操作基礎知識點請參閱以下文章：

1047 行 MySQL 詳細學習筆記（值得學習與收藏）

MySQL 資料型別

欄位型別

整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分別使用 8, 16, 24, 32, 64 位儲存空間，一般情況下越小的列越好。

INT(11) 中的數字只是規定了互動工具顯示字元的個數，對於儲存和計算來說是沒有意義的。

浮點數

FLOAT 和 DOUBLE 為浮點型別，DECIMAL 為高精度小數型別。CPU 原生支援浮點運算，但是不支援 DECIMAl 型別的計算，因此 DECIMAL 的計算比浮點型別需要更高的代價。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列寬，例如 DECIMAL(18, 9) 表示總共 18 位，取 9 位儲存小數部分，剩下 9 位儲存整數部分。

字串

主要有 CHAR 和 VARCHAR 兩種型別，一種是定長的，一種是變長的。

VARCHAR 這種變長型別能夠節省空間，因為只需要儲存必要的內容。但是在執行 UPDATE 時可能會使行變得比原來長，當超出一個頁所能容納的大小時，就要執行額外的操作。MyISAM 會將行拆成不同的片段儲存，而 InnoDB 則需要分裂頁來使行放進頁內。

VARCHAR 會保留字串末尾的空格，而 CHAR 會刪除。

時間和日期

MySQL 提供了兩種相似的日期時間型別: DATETIME 和 TIMESTAMP。

1.DATETIME

能夠儲存從 1001 年到 9999 年的日期和時間，精度為秒，使用 8 位元組的儲存空間。

它與時區無關。

預設情況下，MySQL 以一種可排序的、無歧義的格式顯示 DATETIME 值，例如“2008-01-16 22:37:08”，這是 ANSI 標準定義的日期和時間表示方法。

2.TIMESTAMP

和 UNIX 時間戳相同，儲存從 1970 年 1 月 1 日午夜(格林威治時間)以來的秒數，使用 4 個位元組，只能表示從 1970 年 到 2038 年。

它和時區有關，也就是說一個時間戳在不同的時區所代表的具體時間是不同的。

MySQL 提供了 FROM_UNIXTIME() 函式把 UNIX 時間戳轉換為日期，並提供了 UNIX_TIMESTAMP() 函式把日期轉換為 UNIX 時間戳。

預設情況下，如果插入時沒有指定 TIMESTAMP 列的值，會將這個值設定為當前時間。

應該儘量使用 TIMESTAMP，因為它比 DATETIME 空間效率更高。

關於MySQL 資料型別與儲存引擎的更多詳細介紹：死磕資料庫系列（五）：MySQL 資料型別與儲存引擎介紹

MySQL 索引詳解

什麼是索引？

索引是輔助儲存引擎高效獲取資料的一種資料結構。

很多人形象的說索引就是資料的目錄，便於儲存引擎快速的定位資料。

索引的分類

我們經常從以下幾個方面對索引進行分類

從資料結構的角度對索引進行分類
  • B+tree
  • Hash
  • Full-texts索引
從物理儲存的角度對索引進行分類
  • 聚簇索引
  • 二級索引(輔助索引)
從索引欄位特性角度分類
  • 主鍵索引
  • 唯一索引
  • 普通索引
  • 字首索引
從組成索引的欄位個數角度分類
  • 單列索引
  • 聯合索引（複合索引）
  • 資料結構角度看索引

下表是MySQL常見的儲存引擎InnoDB，MyISAM和Memory分別支援的索引型別

在實際使用中，InnoDB作為MySQL建表時預設的儲存引擎

對上表進行橫向檢視可以瞭解到，B+tree是MySQL中被儲存引擎採用最多的索引型別。

詳細內容：死磕資料庫系列（六）：MySQL 索引詳解

MySQL 效能最佳化

MySQL最佳化方案

想必大家都知道，面試期間一提到資料庫，就會聊到資料庫最佳化相關問題。網上關於資料庫最佳化的文章也是眼花繚亂，層出不窮。今天將會透過這篇文章細分幾點給大家彙總整理出一套關於MySQL資料庫的最佳化方案，讓大家透過學習這篇文章不再被面試官吊打！

成本：硬體最佳化 > 系統配置最佳化 > 表結構最佳化 > SQL語句最佳化 > 索引最佳化。

效果：索引最佳化 > SQL語句最佳化 > 表結構最佳化 > 系統配置最佳化 > 硬體最佳化。

死磕資料庫系列（七）：MySQL 效能最佳化（硬體，系統配置，表結構，SQL語句）

MySQL 主從複製

MySQL複製有兩種方法：

  • 傳統方式：基於主庫的bin-log將日誌事件和事件位置複製到從庫，從庫再加以 應用來達到主從同步的目的。
  • Gtid方式：global transaction identifiers是基於事務來複制資料，因此也就不 依賴日誌檔案位置，同時又能更好的保證主從庫資料一致性。

MySQL資料庫主從同步實戰過程

MySQL 主從同步架構中你不知道的“坑”（上）

MySQL 主從同步架構中你不知道的“坑”（下）

這裡順便提一下：ROW 還是 STATEMENT？線上 MySQL Binlog 怎麼選？ 這篇文章，就這三種模式有何區別，在生產如何選擇，做一個具體的案例詳解。

MySQL複製有多種型別：

  • 非同步複製：一個主庫，一個或多個從庫，資料非同步同步到從庫。
  • 同步複製：在MySQL Cluster中特有的複製方式。
  • 半同步複製：在非同步複製的基礎上，確保任何一個主庫上的事務在提交之前至 少有一個從庫已經收到該事務並日志記錄下來。
  • 延遲複製：在非同步複製的基礎上，人為設定主庫和從庫的資料同步延遲時間， 即保證資料延遲至少是這個引數。

MySQL主從複製延遲解決方案：高可用資料庫主從複製延時的解決方案

MySQL另一種主從同步的方案：基於 Gtid 的 MySQL 主從同步實踐

MySQL 資料備份與恢復

資料備份多種方式：

  • 物理備份是指透過複製資料庫檔案的方式完成備份，這種備份方式適用於資料庫很大，資料重要且需要快速恢復的資料庫
  • 邏輯備份是指透過備份資料庫的邏輯結構（create database/table語句)和資料內容（insert語句或者文字檔案）的方式完成備份。這種備份方式適用於資料庫不是很大，或者你需要對匯出的檔案做一定的修改，又或者是希望在另外的不同型別伺服器上重新建立此資料庫的情況
  • 通常情況下物理備份的速度要快於邏輯備份，另外物理備份的備份和恢復粒度範圍為整個資料庫或者是單個檔案。對單表是否有恢復能力取決於儲存引擎，比如在MyISAM儲存引擎下每個表對應了獨立的檔案，可以單獨恢復；但對於InnoDB儲存引擎表來說，可能每個表示對應了獨立的檔案，也可能表使用了共享資料檔案
  • 物理備份通常要求在資料庫關閉的情況下執行，但如果是在資料庫執行情況下執行，則要求備份期間資料庫不能修改
  • 邏輯備份的速度要慢於物理備份，是因為邏輯備份需要訪問資料庫並將內容轉化成邏輯備份需要的格式；通常輸出的備份檔案大小也要比物理備份大；另外邏輯備份也不包含資料庫的配置檔案和日誌檔案內容；備份和恢復的粒度可以是所有資料庫，也可以是單個資料庫，也可以是單個表；邏輯備份需要再資料庫執行的狀態下執行；它的執行工具可以是mysqldump或者是select … into outfile兩種方式
  • 生產資料庫備份方案：高逼格企業級MySQL資料庫備份方案
  • MySQL資料庫物理備份方式：Xtrabackup實現資料的備份與恢復

MySQL 高可用架構設計與實戰

高可用架構對於網際網路服務基本是標配，無論是應用服務還是資料庫服務都需要做到高可用。對於一個系統而言，可能包含很多模組，比如前端應用，快取，資料庫，搜尋，訊息佇列等，每個模組都需要做到高可用，才能保證整個系統的高可用。對於資料庫服務而言，高可用可能更復雜，對使用者的服務可用，不僅僅是能訪問，還需要有正確性保證，因此資料庫的高可用方案是一直以來的討論熱點，今天就各種的高可用方案，談一下個人的一些看法，如有錯誤，還請指正！！

具體的幾種高可用方案介紹請參閱：淺談MySQL叢集高可用架構

MySQL高可用方案：MySQL 同步複製及高可用方案總結
官方也提供一種高可用方案：官方工具｜MySQL Router 高可用原理與實戰
MHA
  • MHA（Master High Availability）目前在MySQL高可用方面是一個相對成熟的解決方案，該軟體由兩部分組成：MHA Manager（管理節點）和MHA Node（資料節點。
  • MHA Manager: 可以單獨部署在一臺獨立的機器上管理多個master-slave叢集，也可以部署在一臺slave節點上。
  • MHA Node: 行在每臺MySQL伺服器上。
  • MHA Manager會定時探測叢集中的master節點，當master出現故障時，它可以自動將最新資料的slave提升為新的master，然後將所有其他的slave重新指向新的master。整個故障轉移過程對應用程式完全透明。

MHA高可用方案實戰：MySQL叢集高可用架構之MHA

MGR
  • Mysql Group Replication(MGR)是從5.7.17版本開始釋出的一個全新的高可用和高擴張的MySQL叢集服務。
  • 高一致性，基於原生複製及paxos協議的組複製技術，以外掛方式提供一致資料安全保證；
  • 高容錯性，大多數服務正常就可繼續工作，自動不同節點檢測資源徵用衝突，按順序優先處理，內建動防腦裂機制；
  • 高擴充套件性，自動新增移除節點，並更新組資訊；
  • 高靈活性，單主模式和多主模式。單主模式自動選主，所有更新操作在主進行；多主模式，所有server同時更新。

MySQL 資料庫讀寫分離高可用（待更新）

MySQL 分庫分庫（待更新）

MySQL效能監控（待更新）

MySQL 管理工具（待更新）

MySQL 開發規範與使用技巧（待更新）

。。。。。。。。。。。還有，暫時目錄沒有完成。敬請期待。

相關文章