贊！7000 字學習筆記，一天搞定 MySQL

MySQL資料庫簡介

MySQL近兩年一直穩居第二，隨時有可能超過Oracle計晉升為第一名，因為MySQL的性能一直在被優化，同時安全機制也是逐漸成熟，更重要的是開源免費的，

MySQL是一種關系資料庫管理系統，關系資料庫將資料保存在不同的表中，而不是將所有資料放在一個大倉庫內，這樣就增加了速度并提高了靈活性，

MySQL所使用的 SQL 語言是用于訪問資料庫的最常用標準化語言，MySQL 軟體采用了雙授權政策，分為社區版和商業版，由于其體積小、速度快、總體擁有成本低，尤其是開放原始碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站資料庫，

如果不會安裝MySQL請移步：MySQL服務安裝

MySQL InnoDB存盤引擎

• 存盤引擎InnoDB是目前MySQL版本默認的存盤引擎，也是MySQL推薦使用的存盤引擎，是集高可靠性和高性能于一身的存盤引擎，

• 在MySQL5.7版本中，除非在組態檔中顯視指定default storage engine或者創建表時顯視使用engine=陳述句指定其它的存盤引擎，否則默認都是InnoDB，

InnoDB存盤引擎的優勢：

• DML陳述句支持事務功能，保證ACID特性

• 行級鎖的使用保證了高并發的屬性

• InnoDB對有主鍵的表會依據主鍵優化查詢性能，也稱聚簇索引，將所有資料存盤在聚簇索引上以減少對主鍵查詢的IO消耗

• 為保證資料的一致性，InnoDB還支持外鍵屬性，確保有外鍵約束的表之間不會有不一致的資料

• 當服務器硬體或者軟體故障導致MySQL重啟后，InnoDB會自動識別已經在故障之前提交的資料，并回退所有故障時未提交的資料，最大限度的保護資料不會丟失(crash recovery)

1、事物（Transaction）
2、MVCC（多版本并發控制）
3、行級鎖（Row-level Lock）
4、支持外鍵
5、ACSR（Auto Crrash safe Recovery）自動的故障安全恢復
6、支持熱備份

MySQL復制集群原理與實戰

MySQL復制有兩種方法：

• 傳統方式：基于主庫的bin-log將日志事件和事件位置復制到從庫，從庫再加以 應用來達到主從同步的目的，

• Gtid方式：global transaction identifiers是基于事務來復制資料，因此也就不 依賴日志檔案位置，同時又能更好的保證主從庫資料一致性，

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

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

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

資料備份多種方式：

• 物理備份是指通過拷貝資料庫檔案的方式完成備份，這種備份方式適用于資料庫很大，資料重要且需要快速恢復的資料庫

• 邏輯備份是指通過備份資料庫的邏輯結構（create database/table陳述句)和資料內容（insert陳述句或者文本檔案）的方式完成備份，這種備份方式適用于資料庫不是很大，或者你需要對匯出的檔案做一定的修改，又或者是希望在另外的不同型別服務器上重新建立此資料庫的情況

• 通常情況下物理備份的速度要快于邏輯備份，另外物理備份的備份和恢復粒度范圍為整個資料庫或者是單個檔案，對單表是否有恢復能力取決于存盤引擎，比如在MyISAM存盤引擎下每個表對應了獨立的檔案，可以單獨恢復；但對于InnoDB存盤引擎表來說，可能每個表示對應了獨立的檔案，也可能表使用了共享資料檔案

• 物理備份通常要求在資料庫關閉的情況下執行，但如果是在資料庫運行情況下執行，則要求備份期間資料庫不能修改

• 邏輯備份的速度要慢于物理備份，是因為邏輯備份需要訪問資料庫并將內容轉化成邏輯備份需要的格式；通常輸出的備份檔案大小也要比物理備份大；另外邏輯備份也不包含資料庫的組態檔和日志檔案內容；備份和恢復的粒度可以是所有資料庫，也可以是單個資料庫，也可以是單個表；邏輯備份需要再資料庫運行的狀態下執行；它的執行工具可以是mysqldump或者是select … into outfile兩種方式

送你一份生產資料庫備份方案：高逼格企業級MySQL資料庫備份方案

MySQL資料庫物理備份方式：Xtrabackup實作資料的備份與恢復

MySQL復制有多種型別：

• 異步復制：一個主庫，一個或多個從庫，資料異步同步到從庫，

• 同步復制：在MySQL Cluster中特有的復制方式，

• 半同步復制：在異步復制的基礎上，確保任何一個主庫上的事務在提交之前至 少有一個從庫已經收到該事務并日志記錄下來，

• 延遲復制：在異步復制的基礎上，人為設定主庫和從庫的資料同步延遲時間， 即保證資料延遲至少是這個引數，

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

MySQL高可用架構設計與實戰

先來了解一下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 的索引是什么？怎么優化？

• 顧名思義，B-tree索引使用B-tree的資料結構存盤資料，不同的存盤引擎以不同的方式使用B-Tree索引，比如MyISAM使用前綴壓縮技術使得索引空間更小，而InnoDB則按照原資料格式存盤，且MyISAM索引在索引中記錄了對應資料的物理位置，而InnoDB則在索引中記錄了對應的主鍵數值，B-Tree通常意味著所有的值都是按順序存盤，并且每個葉子頁到根的距離相同，

• B-Tree索引驅使存盤引擎不再通過全表掃描獲取資料，而是從索引的根節點開始查找，在根節點和中間節點都存放了指向下層節點的指標，通過比較節點頁的值和要查找值可以找到合適的指標進入下層子節點，直到最下層的葉子節點，最終的結果就是要么找到對應的值，要么找不到對應的值，整個B-tree樹的深度和表的大小直接相關，

• 全鍵值匹配：和索引中的所有列都進行匹配，比如查找姓名為zhang san，出生于1982-1-1的人

• 匹配最左前綴：和索引中的最左邊的列進行匹配，比如查找所有姓為zhang的人

• 匹配列前綴：匹配索引最左邊列的開頭部分，比如查找所有以z開頭的姓名的人

• 匹配范圍值：匹配索引列的范圍區域值，比如查找姓在li和wang之間的人

• 精確匹配左邊列并范圍匹配右邊的列：比如查找所有姓為Zhang，且名字以K開頭的人

• 只訪問索引的查詢：查詢結果完全可以通過索引獲得，也叫做覆寫索引，比如查找所有姓為zhang的人的姓名

MySQL表磁區介紹：一文徹底搞懂MySQL磁區

• 可以允許在?個表?存盤更多的資料，突破磁盤限制或者?件系統限制，

• 對于從表?將過期或歷史的資料移除在表磁區很容易實作，只要將對應的磁區移除即可，

• 對某些查詢和修改陳述句來說，可以?動將資料范圍縮?到?個或?個表磁區上，優化陳述句執?效率，?且可以通過顯示指定表磁區來執?陳述句，?如 select * from temp partition(p1,p2) where store_id < 5;

• 表磁區是將?個表的資料按照?定的規則?平劃分為不同的邏輯塊，并分別進?物理存盤，這個規則就叫做磁區函式，可以有不同的磁區規則，

• MySQL5.7版本可以通過show plugins陳述句查看當前MySQL是否?持表磁區功能，

• MySQL8.0版本移除了show plugins?對partition的顯示，但社區版本的表磁區功能是默認開啟的，

• 但當表中含有主鍵或唯?鍵時，則每個被?作磁區函式的欄位必須是表中唯?鍵和主鍵的全部或?部分，否則就?法創建磁區表，

MySQL分庫分表

• 能不分就不分，1000萬以內的表，不建議分片，通過合適的索引，讀寫分離等方式，可以很好的解決性能問題，

• 分片數量盡量少，分片盡量均勻分布在多個DataHost上，因為一個查詢SQL跨分片越多，則總體性能越差，雖然要好于所有資料在一個分片的結果，只在必要的時候進 行擴容，增加分片數量，

• 分片規則需要慎重選擇，分片規則的選擇，需要考慮資料的增長模式，資料的訪 問模式，分片關聯性問題，以及分片擴容問題，最近的分片策略為范圍分片，列舉分片， 一致性Hash分片，這幾種分片都有利于擴容，

• 盡量不要在一個事務中的SQL跨越多個分片，分布式事務一直是個不好處理的問題，

• 查詢條件盡量優化，盡量避免Select * 的方式，大量資料結果集下，會消耗大量 帶寬和CPU資源，查詢盡量避免回傳大量結果集，并且盡量為頻繁使用的查詢陳述句建立索引，

資料庫分庫分表概述：資料庫分庫分表，何時分？怎樣分？

Mysql分庫分表方案：MySQL 分庫分表方案，總結的非常好！

Mysql分庫分表的思路：解救 DBA—資料庫分庫分表思路及案例分析

MySQL資料庫讀寫分離高可用

海量資料的存盤和訪問成為了系統設計的瓶頸問題，日益增長的業務資料，無疑對資料庫造成了相當大的負載，同時對于系統的穩定性和擴展性提出很高的要求，隨著時間和業務的發展，資料庫中的表會越來越多，表中的資料量也會越來越大，相應地，資料操作的開銷也會越來越大；另外，無論怎樣升級硬體資源，單臺服務器的資源（CPU、磁盤、記憶體、網路IO、事務數、連接數）總是有限的，最終資料庫所能承載的資料量、資料處理能力都將遭遇瓶頸，分表、分庫和讀寫分離可以有效地減小單臺資料庫的壓力，

MySQL讀寫分離高可用架構實戰案例：

ProxySQL+Mysql實作資料庫讀寫分離實戰

Mysql+Mycat實作資料庫主從同步與讀寫分離

MySQL性能監控

MySQL性能監控的指標大體可以分為以下4大類：

• 查詢吞吐量

• 查詢延遲與錯誤

• 客戶端連接與錯誤

• 緩沖池利用率

對于MySQL性能監控，官方也提供了相關的服務插件：MySQL-Percona，下面簡單介紹一下插件的安裝

[root@db01 ~]# yum -y install php php-mysql
[root@db01 ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm
[root@db01 ~]# rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm
warning: percona-zabbix-templates-1.1.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ################################# [100%]
Updating / installing...
   1:percona-zabbix-templates-1.1.8-1 ################################# [100%]

Scripts are installed to /var/lib/zabbix/percona/scripts
Templates are installed to /var/lib/zabbix/percona/templates

最后，可以配合其它監控工具來實作對MySQL的性能監控，

MySQL服務器配置插件：

• 修改php腳本連接MySQL的monitor@localhost用戶 

• 修改MySQL的sock檔案路徑

[root@db01 ~]# sed -i '30c $mysql_user = "monitor";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[root@db01 ~]# sed -i '31c $mysql_pass = "123456";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[root@db01 ~]# sed -i '33c $mysql_socket = "/tmp/mysql.sock";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php

測驗是否可用（ 可以從MySQL中獲取到監控值 ）

[root@db01 ~]# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
gg:12

# 確保當前檔案的 屬主 屬組 是zabbix，否則zabbix監控取值錯誤，
[root@db01 ~]# ll -sh /tmp/localhost-mysql_cacti_stats.txt
4.0K -rw-rw-r-- 1 zabbix zabbix 1.3K Dec 5 17:34 /tmp/localhost-mysql_cacti_stats.txt

移動zabbix-agent組態檔到 /etc/zabbix/zabbix_agentd.d/目錄

[root@db01 ~]# mv /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
[root@db01 ~]# systemctl restart zabbix-agent.service

匯入并配置Zabbix模板與主機：

默認模板監控時間為 5分鐘 （ 當前測驗修改為 30s） 同時也要修改Zabbix模板時間

# 如果要修改監控獲取值的時間不但要在zabbix面板修改取值時間，bash腳本也要修改，
[root@db01 scripts]# sed -n '/TIMEFLM/p' /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
TIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt`
if [ `expr $TIMENOW - $TIMEFLM` -gt 300 ]; then   
# 這個 300 代表 300s 同時也要修改，

默認模板版本為 2.0.9，無法在4.0版本使用，可以先從3.0版本匯出，然后再匯入4.0版本 ，

其實，在實際生產程序中，還是有相關的專業監控資料庫的第三方開源軟體的，民工哥之前也寫過相關的文章，今天發出來供大家參考：強大的開源企業級資料庫監控利器Lepus

MySQL用戶行為安全

• 假設這么一個情況，你是某公司mysql-DBA，某日突然公司資料庫中的所有被人為刪了，

• 盡管有資料備份，但是因服務停止而造成的損失上千萬，現在公司需要查出那個做洗掉操作的人，

• 但是擁有資料庫操作權限的人很多，如何排查，證據又在哪？

• 是不是覺得無能為力？

• mysql本身并沒有操作審計的功能，那是不是意味著遇到這種情況只能自認倒霉呢？

學完了就需要出去練一練，最后給大家一些企業面試題供大家練練手：24 個必須掌握的資料庫面試問題！

標籤：MySQL

上一篇：MySQL實驗 內連接優化order by+limit 以及添加索引再次改進

下一篇：MySQL學習筆記（12）：觸發器