Mysql數據庫進階

一、Mysql組織架構

連接層

1.驗證用戶的身份，用戶名密碼是否匹配
2.提供兩種連接方式（TCP/IP連接、socket連接）
3.連接層提供了一個與sql層交互的線程

SQL層

1.接收連接層傳過來的SQL語句
2.驗證執行的SQL語法
3.驗證SQL的語義（DDL,DML,DQL,DCL）
4.解析器：解析SQL語句，生成執行計劃
5.優化器：將解析器傳來的執行計劃選擇最優的一條執行
6.執行器：將最優的一條執行
6.1 與存儲引擎層建立交互的線程
6.2 將要執行的sql發給存儲引擎層
7.如果有緩存，則走緩存
8.記錄日志（如binlog）

存儲引擎層

1.接收SQL層傳來的語句
2.與磁盤交互，獲取數據，返回給sql層
3.建立與sql層交互的線程

mysql數據庫服務端-----》innodb存儲引擎
操作系統------------》文件系統
計算機硬件----------》硬盤

二、存儲引擎

定義

存儲引擎------說白了就是如何存儲數據、如何為存儲的數據建立索引和如何更新、查詢數據等技術的實現方法，專門處理其對應的類型的表。

存儲引擎決定了表的類型

存儲引擎---------表
視頻播放---------mp4
文本編輯器-------txt

#InnoDB 存儲引擎 ----默認在內存中已經建立了自適應的hash索引
#MyISAM 存儲引擎
只是讀取和插入，不支持事務、表級鎖設計、支持全文索引,不支持故障自動恢復，主要面向一些 OLAP 數據庫應用,在 MySQL 5.5.8 版本之前是默認的存儲引擎(除 Windows 版本外)。它的緩沖池只緩存(cache)索引文件,而不緩存數據文件,這與大多數的數據庫都不相同。 （.frm表結構 .MYD表數據 .MYI表索引）
#Memory 存儲引擎
正如其名,Memory 存儲引擎中的數據都存放在內存中,數據庫重啟或發生崩潰,表中的數據都將消失。它非常適合于存儲 OLTP 數據庫應用中臨時數據的臨時表,也可以作為 OLAP 數據庫應用中數據倉庫的維度表。Memory 存儲引擎默認使用哈希索引,而不是通常熟悉的 B+ 樹索引。
#Infobright 存儲引擎
第三方的存儲引擎。其特點是存儲是按照列而非行的,因此非常適合 OLAP 的數據庫應用。
#BLACKHOLE
黑洞存儲引擎，可以應用于主備復制中的分發主庫。

innodb存儲引擎概述（默認）

----------------三大特性

事務
行級鎖：innodb支持行級鎖，myisam是表級鎖，鎖的粒度越小并發能力越強（一次只運行一個，保障安全性）
支持外鍵

MVCC 多版本并發控制
備份和恢復 innodb支持支持熱備，myisam不支持
自動故障恢復（CSR） Crash Safe Recovery

[root@egon db1]# cd /var/lib/mysql/db1/
[root@egon db1]# ls
db.opt innodb_t1.frm innodb_t1.ibd innodb_t2.frm innodb_t2.ibd

### .frm表的元數據文件（表結構） .ibd表的數據文件+索引文件

查看

MariaDB [(none)]> show engines\G  #查看所有支持的存儲引擎
MariaDB [(none)]> show variables like 'storage_engine%'; #查看正在使用的存儲引擎#查看使用innodb的表有哪些
# table_schema字段的值即表所在的庫
select table_schema,table_name,engine from information_schema.tables where engine='innodb';

#mysql5.6以后默認使用innodb存儲引擎

使用

1、建表時，使用不同的存儲引擎

create table t1(id int)engine=blackhole;
   create table t2(id int)engine=memory;
   create table t3(id int)engine=myisam;
   create table t4(id int)engine=innodb;

2、修改配置文件指定默認的存儲引擎

/etc/my.cnf
[mysqld]
default-storage-engine=INNODB #指定
innodb_file_per_table=1 # 讓每個表都有自己獨立的的ibd文件，如果不指定的話，所有表的數據文件都會集中在/var/lib/mysql/ibdata1這個共享數據文件中

案例：升級存儲引擎

準備工作----一臺新機器

源碼包安裝，二進制安裝# 配置yum源
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
enabled=1
gpgcheck=0# 獲取mysql5.7初始密碼
grep "temporary password" /var/log/mysqld.log# 設置密碼，密碼已經不能再用弱密碼了，弱密碼會報錯
set password=password("Egon@123");

在舊機器上備份生產庫數據

# –triggers (默認導出觸發器，使用–skip-triggers屏蔽導出)
# -R:–routines,導出存儲過程以及自定義函數mysqldump -uroot -p123 -B db1 --triggers -R  > /tmp/db1.sql

處理備份數據（更改默認存儲引擎）

[root@db01 ~]# sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#gi' /tmp/db1.sql

將備份的數據傳到新的數據庫服務器上并將修改后的備份恢復到新庫

scp  rsync 硬件設備  NFS
mysql -uroot -p123 < /tmp/db1.sql

應用測試環境連接新庫，測試所有功能
停止應用，將備份之后的生產庫發生的新變化補償到新庫
應用切割到新數據庫

Innodb邏輯架構

mysql數據庫服務端---》innodb存儲引擎--》用戶態內存空間（應用程序的內存空間）
操作系統------------》文件系統--------》os cache（操作系統緩存）
計算機硬件----------》硬盤------------》硬盤結構（硬盤中的數據）

內存架構

innodb內存

緩沖池（優化讀）
寫緩沖（優化寫）
日志緩沖
自適應哈希索引

操作系統緩存

為了提升性能而降低磁盤IO的次數（攢一波再寫入），在InnoDB的緩存體系與磁盤文件之間，加了一層操作系統的緩存/頁面緩存。用戶態innodb存儲引擎的進程向操作系統發起write系統調用時，在內核態完成頁面緩存寫入后即返回，如果想立即將頁面緩存的內容立即刷入磁盤，innodb存儲引擎需要發起fsync系統調用才可以。

兩個系統調用(按一定頻率配合使用)：

write：將數據寫入操作系統的頁面緩存后立即返回（存在丟失數據的風險）
fsync：將數據立即提交到硬盤中，強制硬盤同步（大量進行會出現性能瓶頸）

O_DIRECT

選項是在Linux系統中的選項，使用該選項后，對文件進行直接IO操作，不經過文件系統緩存，直接寫入磁盤

硬盤上的架構

表文件----一堆二進制亂碼
表空間 ibd文件
索引
雙寫緩沖：位于表空間，記錄innodb緩存改動之前的數據
redo日志：記錄尚未完成的操作，斷電則用其重做（崩潰恢復）
undo日志：記錄改動之前的舊數據，一旦改錯可以回滾

### 默認情況下，創建InnoDB表的時候innodb_file_per_table參數是開啟的，它表明用戶創建的表和索引，會被以單表單文件的形式放入到file-per-table表空間中。如果禁用了該參數innodb_file_per_table，那么表及索引會被放入系統表空間（共享表空間）中

Innodb存儲引擎執行流程

執行一條更新sql語句------三大階段，8小步驟

執行階段
數據加載到內存，寫undo log，更新內存中的數據，寫redo log buffer
事務提交階段
redo log 和 binlog 刷盤，commit標記寫入redo log 中
最后
后臺io線程隨機把被內存中的臟數據刷到磁盤上

把該行數據從磁盤加載到buffer pool 中，并對該行數據進行加鎖
把舊數據寫入undo log以便修改出錯的情況下進行回滾
在buffer pool 中的數據更新，得到臟數據
將修改后的臟數據寫入redo log buffer中
準備提交事務，redo log 刷入磁盤
把修改的操作記錄準備寫入binlog日志(事務提交時)
把binlog的文件名和位置寫入commit標記，commit標記寫入redo log 中（redo log 中存放的修改后的數據與binlog中的修改操作對應上，雙管齊下），事務的提交才算成功，否則不會成功
IO線程buffer pool 中的臟數據刷入磁盤文件，完成最終修改

補充：

redo接受臟數據（改動后的數據）-----先放到緩沖區再放到磁盤
binlog接受詳細改動操作的信息-----直接寫入磁盤
第八步IO操作最耗時間
innodb_flush_log_at_trx_commit參數（redo log刷盤策略）
1 默認值，事務提交時必須把redo log從內存刷入磁盤（安全性最高，最耗時）
0 等待innodb主動執行刷新磁盤（風險最高）
2 直接把日志放到操作系統緩存，等待操作系統刷新磁盤(mysql掛了機器沒掛數據不會丟失)
sync_binlog參數（binlog刷盤策略）
0