1 MySQL總體架構介紹
1.1 MySQL總體架構介紹
引言
MySQL是一個關系型資料庫
應用十分廣泛
在學習任何一門知識之前
對其架構有一個概括性的了解是非常重要的
比如索引、sql是在哪個地方執行的
流程是什麼樣的
今天我們就先來學習一下MySQL的總體架構 總的來說：MySQL架構是一個用戶端-伺服器系統。
MySQL主要包括以下幾部分:
Server 層：主要包括連接配接器、查詢緩存、分析器、優化器、執行器等，所有跨存儲引擎的功能都在這一層實作，比如存儲過程、觸發器、視圖，函數等，還有一個通用的日志子產品 binglog 日志子產品。
存儲引擎： 主要負責資料的存儲和讀取，采用可以替換的插件式架構，支援 InnoDB、MyISAM、Memory 等多個存儲引擎，其中 InnoDB 引擎有自己的日志子產品 redolog 子產品。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始就被當做預設存儲引擎了
連接配接器： 身份認證和權限相關(登入 MySQL 的時候)。
查詢緩存: 執行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本後移除，因為這個功能不太實用）mysql的server層增加一層緩存子產品，類似一個記憶體的kv層，k是sql，value是結果
分析器: 沒有命中緩存的話，SQL 語句就會經過分析器，分析器說白了就是要先看你的 SQL 語句要幹嘛，再檢查你的 SQL 語句文法是否正确。
優化器： 按照 MySQL 認為最優的方案去執行。
執行器: 執行語句，然後從存儲引擎傳回資料。
1.2 MySQL存儲引擎介紹
引言
和大多數的資料庫不同, MySQL中有一個存儲引擎的概念
針對不同的存儲需求可以選擇最優的存儲引擎。
存儲引擎就是存儲資料，建立索引，更新查詢資料等等技術的實作方式 。
存儲引擎是基于表的，而不是基于庫的
是以存儲引擎也可被稱為表類型。 MySQL提供了插件式的存儲引擎架構。是以MySQL存在多種存儲引擎，可以根據需要使用相應引擎，或者編寫存儲引擎。
MySQL5.0支援的存儲引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等
可以通過指定 show engines ， 來查詢目前資料庫支援的存儲引擎 ：
SHOW ENGINES; 
表含義:
- support : 指伺服器是否支援該存儲引擎
- transactions : 指存儲引擎是否支援事務
- XA : 指存儲引擎是否支援分布式事務處理
- Savepoints : 指存儲引擎是否支援儲存點（實作復原到指定儲存點） - 檢視MySQL資料庫存儲引擎配置
SHOW VARIABLES LIKE '%storage_engine%'; 1.2.1 如何更改資料庫表引擎
- 建表語句後面加入引擎指派即可 ,指令舉例如下 ,
CREATE TABLE t1(
id INT ,
name VARCHAR(20)
) ENGINE = MyISAM; - 修改已有的表引擎 , 指令舉例如下 ,
ALTER TABLE t1 ENGINE = InnoDB; 1.2.2 常用引擎及其特性對比
-
常見的存儲引擎 :
MyISAM存儲引擎 : 通路快,不支援事務和外鍵。表結構儲存在.frm檔案中，表資料儲存在.MYD檔案中，索引儲存在.MYI檔案中。
[root@linux-141 itcast]# ll
-rw-r-----. 1 mysql mysql 8630 9月 10 16:01 t_account_myisam.frm
-rw-r-----. 1 mysql mysql 52 9月 10 16:06 t_account_myisam.MYD
-rw-r-----. 1 mysql mysql 2048 9月 10 17:56 t_account_myisam.MYI
[root@linux-141 itcast]# **innoDB存儲引擎(**5.5版本開始預設) : 支援事務 ,占用磁盤空間大 ,支援并發控制。表結構儲存在.frm檔案中，如果是共享表空間，資料和索引儲存在 innodb_data_home_dir 和 innodb_data_file_path定義的表空間中，可以是多個檔案。如果是多表空間存儲，每個表的資料和索引單獨儲存在 .ibd 中。
[root@linux-141 itcast]# ll
-rw-r-----. 1 mysql mysql 8630 9月 10 16:02 t_account_innodb.frm
-rw-r-----. 1 mysql mysql 98304 9月 14 15:50 t_account_innodb.ibd
[root@linux-141 itcast]# MEMORY存儲引擎 : 記憶體存儲 , 速度快 ,不安全 ,适合小量快速通路的資料。表結構儲存在.frm中。
!
特性對比 :
| 特點 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
| 存儲限制 | 64TB | 有 | 有 | 沒有 | 有 |
| 事務安全 | ==支援== | ||||
| 鎖機制 | ==行鎖(适合高并發)== | ==表鎖== | 表鎖 | 表鎖 | 行鎖 |
| B樹索引 | 支援 | 支援 | 支援 | 支援 | 支援 |
| 哈希索引 | 支援 | ||||
| 全文索引 | 支援(5.6版本之後) | 支援 | |||
| 叢集索引 | 支援 | ||||
| 資料索引 | 支援 | 支援 | 支援 | ||
| 索引緩存 | 支援 | 支援 | 支援 | 支援 | 支援 |
| 資料可壓縮 | 支援 | ||||
| 空間使用 | 高 | 低 | N/A | 低 | 低 |
| 記憶體使用 | 高 | 低 | 中等 | 低 | 高 |
| 批量插入速度 | 低 | 高 | 高 | 高 | 高 |
| 支援外鍵 | ==支援== |
1.2.3 如何選擇不同類型的引擎
在選擇存儲引擎時，應該根據應用系統的特點選擇合适的存儲引擎。對于複雜的應用系統，還可以根據實際情況選擇多種存儲引擎進行組合。
以下是幾種常用的存儲引擎的使用環境。
- InnoDB : 是Mysql的預設存儲引擎，用于事務處理應用程式，支援外鍵。如果應用對事務的完整性有比較高的要求，在并發條件下要求資料的一緻性，資料操作除了插入和查詢以外，還包含更新、删除操作，那麼InnoDB存儲引擎是比較合适的選擇。InnoDB存儲引擎除了有效的降低由于删除和更新導緻的鎖定， 還可以確定事務的完整送出和復原，對于類似于計費系統或者财務系統等對資料準确性要求比較高的系統，InnoDB是最合适的選擇。
- MyISAM ： 如果應用是以讀操作和插入操作為主，隻有很少的更新和删除操作，并且對事務的完整性、并發性要求不是很高，那麼選擇這個存儲引擎是非常合适的。
- MEMORY：将所有資料儲存在RAM中，在需要快速定位記錄和其他類似資料環境下，可以提供極快的通路。MEMORY的缺陷就是對表的大小有限制，太大的表無法緩存在記憶體中，其次是要確定表的資料可以恢複，資料庫異常終止後表中的資料是可以恢複的。MEMORY表通常用于更新不太頻繁的小表，用以快速得到通路結果。
1.3 SQL的執行流程是什麼樣的
- 用戶端發送一條查詢給伺服器。
- 伺服器先檢查查詢緩存，如果命中了緩存，則立刻傳回存儲在緩存中的結果。否則進入下一階段。
- 伺服器端進行SQL解析、預處理，再由優化器生成對應的執行計劃。
- MySQL根據優化器生成的執行計劃，再調用存儲引擎的API來執行查詢。
- 将結果傳回給用戶端。
2 MySQL存儲引擎調優
2.1 MySQL伺服器硬體優化
tips
硬體（cpu、記憶體等）相關
了解即可
關于提升硬體裝置性能：
例如選擇盡量高頻率的**記憶體（**頻率不能高于主機闆的支援）、提升網絡帶寬、使用SSD高速磁盤、提升CPU性能等。
CPU的選擇:
- 對于資料庫并發比較高的場景，CPU的數量比頻率重要。
- 對于CPU密集型場景和頻繁執行複雜SQL的場景，CPU的頻率越高越好
磁盤的選擇
影響資料庫最大的性能問題就是磁盤I/O
為提高資料庫的IOPS性能，可使用SSD或PCIE-SSD高速磁盤裝置
磁盤IO的優化
可以用RAID來進行優化
常用RAID（磁盤陣列）級别：
RAID0：也稱為條帶，就是把多個磁盤連結成一個硬碟使用，這個級别IO最好
RAID1：也稱為鏡像，要求至少有兩個磁盤，每組磁盤存儲的資料相同
RAID5：也是把多個（最少3個）硬碟合并成一個邏輯盤使用，資料讀寫時會建立奇偶校驗資訊，并且奇偶校驗資訊和相對應的資料分别存儲在不同的磁盤上。當RAID5的一個磁盤資料發生損壞後，利用剩下的資料和響應的奇偶校驗資訊去恢複被損壞的資料
RAID1+0（建議使用）：就是RAID0和RAID1的組合。同時具備兩個級别的優缺點，一般建議資料庫使用這個級别。
2.2 MySQL資料庫配置優化
tips：
以下為生産環境中最常用的DB參數配置
-
表示緩沖池位元組大小，大的緩沖池可以減少磁盤IO次數。
innodb_buffer_pool_size = 推薦值為實體記憶體的50%~80%。
-
用來控制redo log buffer重新整理到磁盤的政策。
innodb_flush_log_at_trx_commit=1
select @@innodb_flush_log_at_trx_commit; 0 : 送出事務的時候，不立即把 redo log buffer 裡的資料刷入磁盤檔案中，而是依靠 InnoDB 的主線程每秒執行一次重新整理到磁盤。此時可能你送出事務了，結果 mysql 當機了，然後此時記憶體裡的資料全部丢失。
1 : 送出事務的時候，立即把 redo log buffer 裡的資料刷入磁盤檔案中，隻要事務送出成功，那麼資料就必然在磁盤裡了。
2 : 送出事務的時候，把 redo log buffer日志寫入磁盤檔案對應的系統緩存，而不是直接進入磁盤檔案，這時可能1秒後才會把系統緩存裡的資料寫入到磁盤檔案。 -
每送出1次事務就同步寫到磁盤中，可以設定為1。
sync_binlog=1
0：預設值。事務送出後，将二進制日志從緩沖寫入作業系統緩沖，但是不進行重新整理操作（fsync()），此時隻是寫入了作業系統緩沖而沒有重新整理到磁盤，若作業系統當機則會丢失部分二進制日志。
1：事務送出後，将二進制檔案寫入磁盤并立即執行重新整理操作，相當于是同步寫入磁盤，不經過作業系統的緩存。
N：每寫N次作業系統緩沖就執行一次重新整理操作。 -
髒頁占innodb_buffer_pool_size的比例，觸發刷髒頁到磁盤。 推薦值為25%~50%。
innodb_max_dirty_pages_pct=30
髒頁：記憶體資料頁和磁盤資料頁上的内容不一緻 -
背景程序最大IO性能名額。
預設200，如果SSD，調整為5000~20000
PCIE-SSD可調整為5w左右
預設：innodb_io_capacity=200
-
指定innodb共享表空間檔案的大小。
innodb_data_file_path ＝ ibdata:1G:autoextend：預設10M，
一般設定為1GB
-
慢查詢日志的門檻值設定，機關秒。
long_query_time=0.3
合理設定區間0.1s~0.5s，
-
mysql複制的形式，row為MySQL8.0的預設形式。
binlog_format=row
建議binlog的記錄格式為row模式
STATEMENT模式：每一條會修改資料的sql語句都會記錄到binlog中。
ROW模式：不記錄每條sql語句的上下文資訊，僅需記錄哪條資料被修改了，修改成什麼樣了。
MIXED模式：以上兩種模式的混合使用。 -
降低interactive_timeout、wait_timeout的值。
互動等待時間和非互動等待時間，值一緻，建議300~500s，預設8小時
在用mysql用戶端對資料庫進行操作時，打開終端視窗，如果一段時間（8小時）沒有操作，再次操作時，會報錯：目前的連接配接已經斷開，需要重建立立連接配接 - 資料庫最大連接配接數max_connections=200
-
過大，執行個體恢複時間長；過小，造成日志切換頻繁。
innodb_log_file_size=預設
redo log空間大小
-
全量日志建議關閉。
預設關閉general_log=0
開啟 general log 将所有到達MySQL Server的SQL語句記錄下來，general_Log檔案就會産生很大的檔案，建議關閉 2.3 Mysql中查詢緩存優化
tips：
在MySQL 8.0之後廢棄這個功能
原理：複雜、實用性不高
作為了解即可
1） 查詢緩存概述
開啟Mysql的查詢緩存，當執行完全相同的SQL語句的時候，伺服器就會直接從緩存中讀取結果，當資料被修改，之前的緩存會失效，修改比較頻繁的表不适合做查詢緩存。
2） 操作流程
回顧
- 用戶端發送一條查詢給伺服器；
- 伺服器先會檢查查詢緩存，如果命中了緩存，則立即傳回存儲在緩存中的結果。否則進入下一階段；
- 伺服器端進行SQL解析、預處理，再由優化器生成對應的執行計劃；
- MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢；
- 将結果傳回給用戶端。
3） 查詢緩存配置
- 檢視目前的MySQL資料庫是否支援查詢緩存：
SHOW VARIABLES LIKE 'have_query_cache'; - 檢視目前MySQL是否開啟了查詢緩存 ：
SHOW VARIABLES LIKE 'query_cache_type'; - 檢視查詢緩存的占用大小 ：
SHOW VARIABLES LIKE 'query_cache_size'; - 檢視查詢緩存的狀态變量：
SHOW STATUS LIKE 'Qcache%'; 各個變量的含義如下：
| 參數 | 含義 |
| Qcache_free_blocks | 查詢緩存中的可用記憶體塊數 |
| Qcache_free_memory | 查詢緩存的可用記憶體量 |
| Qcache_hits | 查詢緩存命中數 |
| Qcache_inserts | 添加到查詢緩存的查詢數 |
| Qcache_lowmen_prunes | 由于記憶體不足而從查詢緩存中删除的查詢數 |
| Qcache_not_cached | 非緩存查詢的數量（由于 query_cache_type 設定而無法緩存或未緩存） |
| Qcache_queries_in_cache | 查詢緩存中注冊的查詢數 |
| Qcache_total_blocks | 查詢緩存中的塊總數 |
4） 開啟查詢緩存
MySQL的查詢緩存預設是關閉的，需要手動配置參數 query_cache_type ， 來開啟查詢緩存。query_cache_type 該參數的可取值有三個 ：
| 值 | 含義 |
| OFF 或 0 | 查詢緩存功能關閉 |
| ON 或 1 | 查詢緩存功能打開，SELECT的結果符合緩存條件即會緩存，否則，不予緩存，顯式指定 SQL_NO_CACHE，不予緩存 |
| DEMAND 或 2 | 查詢緩存功能按需進行，顯式指定 SQL_CACHE 的SELECT語句才會緩存；其它均不予緩存 |
在 my.cnf 配置中，增加以下配置 ：
#開啟查詢緩存
query_cache_type=1 配置完畢之後，重新開機服務既可生效 ；
然後就可以在指令行執行SQL語句進行驗證 ，執行一條比較耗時的SQL語句，然後再多執行幾次，檢視後面幾次的執行時間；擷取通過檢視查詢緩存的緩存命中數，來判定是否走查詢緩存。
-- 執行SQL語句進行驗證 查詢緩存
SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';
-- 将SELECT修改為小寫，發現緩存失效
SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店'; 5） 查詢緩存SELECT選項
可以在SELECT語句中指定兩個與查詢緩存相關的選項 ：
SQL_CACHE : 如果查詢結果是可緩存的，并且 query_cache_type 系統變量的值為ON或 DEMAND ，則緩存查詢結果 。
SQL_NO_CACHE : 伺服器不使用查詢緩存。它既不檢查查詢緩存，也不檢查結果是否已緩存，也不緩存查詢結果。
例子：
SELECT SQL_CACHE id, name FROM xxx;
SELECT SQL_NO_CACHE id, name FROM xxx; 6） 查詢緩存失效的情況
tips
需要注意的問題
1） SQL 語句不一緻的情況， 要想命中查詢緩存，查詢的SQL語句必須一緻。
SQL1 : select count(*) from xxx;
SQL2 : Select count(*) from xxx; 2） 當查詢語句中有一些不确定的值，則不會緩存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。
SQL1 : select * from xxx where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database(); 3） 不使用任何表查詢語句。
select 'A'; 4） 查詢 mysql， information_schema或 performance_schema 資料庫中的表時，不會走查詢緩存。
select * from information_schema.engines; 5） 在存儲的函數，觸發器或事件的主體内執行的查詢。
6） 如果表更改，則使用該表的所有高速緩存查詢都将變為無效并從高速緩存中删除。這包括使用
MERGE
映射到已更改表的表的查詢。一個表可以被許多類型的語句，如被改變 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。
将查詢緩存關閉，因為後面還需要進行索引的驗證，是以不希望走查詢緩存
[root@linux-141 itcast]# vi /etc/my.cnf
[root@linux-141 itcast]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS! 2.4. Mysql記憶體管理及優化
1）記憶體優化原則
1） 将盡量多的記憶體配置設定給MySQL做緩存，但要給作業系統和其他程式預留足夠記憶體。
2） MyISAM 存儲引擎的資料檔案讀取依賴于作業系統自身的IO緩存，是以，如果有MyISAM表，就要預留更多的記憶體給作業系統做IO緩存。
3） 排序區、連接配接區等緩存是配置設定給每個資料庫會話（session）專用的，其預設值的設定要根據最大連接配接數合理配置設定，如果設定太大，不但浪費資源，而且在并發連接配接較高時會導緻實體記憶體耗盡。
2） MyISAM 記憶體優化
MyISAM 存儲引擎使用 key_buffer 緩存索引塊，加速myisam索引的讀寫速度。對于myisam表的資料塊，mysql沒有特别的緩存機制，完全依賴于作業系統的IO緩存。
key_buffer_size
key_buffer_size決定MyISAM索引塊緩存區的大小，直接影響到MyISAM表的存取效率。可以在MySQL參數檔案中設定key_buffer_size的值，對于一般MyISAM資料庫，建議至少将1/4可用記憶體配置設定給key_buffer_size。
在my.cnf 中做如下配置：
key_buffer_size=512M read_buffer_size
如果需要經常順序掃描MyISAM 表，可以通過增大read_buffer_size的值來改善性能。但需要注意的是read_buffer_size是每個session獨占的，如果預設值設定太大，就會造成記憶體浪費。
read_rnd_buffer_size
對于需要做排序的MyISAM 表的查詢，如帶有order by子句的sql，适當增加 read_rnd_buffer_size 的值，可以改善此類的sql性能。
但需要注意的是 read_rnd_buffer_size 是每個session獨占的，如果預設值設定太大，就會造成記憶體浪費。
3） InnoDB 記憶體優化
innodb用一塊記憶體區做IO緩存池，該緩存池不僅用來緩存innodb的索引塊，而且也用來緩存innodb的資料塊。
innodb_buffer_pool_size
該變量決定了 innodb 存儲引擎表資料和索引資料的最大緩存區大小。在保證作業系統及其他程式有足夠記憶體可用的情況下，innodb_buffer_pool_size 的值越大，緩存命中率越高，通路InnoDB表需要的磁盤I/O 就越少，性能也就越高。
innodb_buffer_pool_size=512M innodb_log_buffer_size
決定了innodb重做日志緩存的大小，對于可能産生大量更新記錄的大事務，增加innodb_log_buffer_size的大小，可以避免innodb在事務送出前就執行不必要的日志寫入磁盤操作。
innodb_log_buffer_size=10M 2.5. Mysql并發參數調整
從實作上來說，MySQL Server 是多線程結構，包括背景線程和客戶服務線程。多線程可以有效利用伺服器資源，提高資料庫的并發性能。在Mysql中，控制并發連接配接和線程的主要參數包括 max_connections、back_log、thread_cache_size、table_open_cahce。
1） max_connections
最大可支援的連接配接數
采用max_connections 控制允許連接配接到MySQL資料庫的最大數量，預設值是 151。如果狀态變量 connection_errors_max_connections 不為零，并且一直增長，則說明不斷有連接配接請求因資料庫連接配接數已達到允許最大值而失敗，這時可以考慮增大max_connections 的值。
Mysql 最大可支援的連接配接數，取決于很多因素，包括給定作業系統平台的線程庫的品質、記憶體大小、每個連接配接的負荷、CPU的處理速度，期望的響應時間等。在Linux 平台下，性能好的伺服器，支援 500-1000 個連接配接不是難事，需要根據伺服器性能進行評估設定。
2） back_log
積壓請求棧大小
back_log 參數控制MySQL監聽TCP端口時設定的積壓請求棧大小。如果MySql的連接配接數達到max_connections時，新來的請求将會被存在堆棧中，以等待某一連接配接釋放資源，該堆棧的數量即back_log，如果等待連接配接的數量超過back_log，将不被授予連接配接資源，将會報錯。5.6.6 版本之前預設值為 50 ， 之後的版本預設為 50 + （max_connections / 5）， 但最大不超過900。
如果需要資料庫在較短的時間内處理大量連接配接請求， 可以考慮适當增大back_log 的值。
3） table_open_cache
執行線程可打開表緩存個數
該參數用來控制所有SQL語句執行線程可打開表緩存的數量， 而在執行SQL語句時，每一個SQL執行線程至少要打開 1 個表緩存。該參數的值應該根據設定的最大連接配接數 max_connections 以及每個連接配接執行關聯查詢中涉及的表的最大數量來設定 ：
max_connections x N ；
4） thread_cache_size
緩存客戶服務線程的數量
為了加快連接配接資料庫的速度，MySQL 會緩存一定數量的客戶服務線程以備重用，通過參數 thread_cache_size 可控制 MySQL 緩存客戶服務線程的數量。
5）lock_wait_timeout
innodb_lock_wait_timeout