MySQL資料庫使用（二）

1.鍵的概念

• 鍵 ：資料庫中的鍵（key）又稱為關鍵字，是關係模型中的一個重要概念，它是邏輯結構，不是資料庫的物理部分；

• 唯一鍵 ：即一個或者一組列，其中沒有重複的記錄，可以唯一標示一條記錄；

• 主鍵 ：屬於唯一鍵，是一個比較特殊的唯一鍵，區別在於主鍵不可為空；

# id為主鍵  name是唯一鍵
create table Mark(id int not null primary key ,name varchar(250) unique key);
create table Mark(id int, name varchar(250), primary key(id), unique key(name));

• 外來鍵 ：一張表外來鍵的值一般來說是另一張表主鍵的值，因此，外來鍵的存在使得表與表之間可以聯絡起來；

create table students_to_teacher(to_id int, stu_id int, tea_id int, foreign key(stu_id) references students(id), foreign key(tea_id) references teacher(id));

2.索引的概念

• 索引就像是一張表的目錄，在查詢內容之前可以先在目錄中查詢索引位置，以此快速定位查詢資料，儲存索引資料的檔案一般會與儲存資料的目錄分開；

• 索引應該構建在經常被用作查詢條件的欄位上；

3.索引型別

• 普通索引 ：加速查詢；

# 建立普通索引
create index 索引名稱 on 表名(列名)
# 刪除普通索引
drop index 索引名稱 on 表名
# 檢視索引
show index from 索引名

• 唯一索引（UNIQUE KEY） ：加速查詢 + 列值唯一（可以有null）；

# 建立唯一索引
create unique index 索引名稱 on 表名(列名)
# 刪除唯一索引
drop unique  index 索引名稱 on 表名

• 主鍵索引（PRIMARY KEY） ：用於加速查詢，只能有一個主鍵欄位，不允許重複且不能為NULL；

• 組合索引 ：多列值組成一個索引，專門用於組合搜尋，其效率大於索引合併；

• 全文索引 ：對文字的內容進行分詞，進行搜尋；

• 空間索引 ：指依據空間物件的位置和形狀或空間物件之間的某種空間關係按一定的順序排列的一種資料結構；

4.引優化策略

• 獨立使用列，儘量避免其參與運算；
• 左字首索引：查詢欄位的時候，條件過濾時，最左字首精確匹配；
• 多列索引：AND 連線欄位時適合多列索引，選擇合適的索引次序，將選擇性最高的放在左側，範圍匹配的放在右側；

5.Innodb 儲存引擎

• 所有的InnoDB表的資料和索引儲存於同一個表檔案中，但是表資料和表結構分離；

    -rw-rw---- 1 mysql mysql    65 8月  27 14:31 db.opt
    -rw-rw---- 1 mysql mysql  8614 8月  27 14:31 students.frm
    -rw-rw---- 1 mysql mysql 98304 8月  27 14:31 students.ibd
    db.opt檔案：主要用來儲存當前資料庫的預設字符集和字元校驗規則
    students.frm檔案：存放表結構的
    students.ibd檔案：儲存了當前表的資料和相關的索引資料
    因此，表資料和表結構分離, 每個表單獨使用一個表檔案來儲存資料和索引

• Mariadb預設的儲存引擎是XtraDB，但是為了與MySQL相容，因此也取名做InnoDB，因為MySQL的預設儲存引擎是InnoDB；

• 使用聚集索引（資料和索引在一起），也支援自適應hash索引，鎖粒度為行級別，支援支援熱備工具；

• 支援事務的儲存引擎，適合處理大量的短期事務；

6.MyISAM 儲存引擎

• 所有的MyISAM表的資料和索引存放在不同的檔案中，表結構也分離；

-rw-rw---- 1 mysql mysql  10630 8月  27 13:12 user.frm
-rw-rw---- 1 mysql mysql    504 8月  27 13:15 user.MYD
-rw-rw---- 1 mysql mysql   2048 8月  27 14:30 user.MYI
user.frm：為表結構
user.MYD：為表資料
user.MYI：為表索引

• 支援全文索引（fulltext index），壓縮，空間函式；

• 不支援事物，表級鎖，適用於只讀，讀多寫少；

7.MySQL的併發訪問控制

• 任何的資料集只要支援併發訪問模型就必須基於鎖機制進行訪問控制；

• 讀鎖 ：共享鎖，允許給其他人讀，不允許他人寫；

• 寫鎖 ：獨佔鎖, 不允許其他人讀和寫；

• 顯示鎖 ：使用者手動請求讀鎖或寫鎖；

• 隱式鎖 ：由儲存引擎自行根據需要加的，無需我們管理；

• 給表施加鎖機制

# lock tables 方式施加鎖
lock tables 表名稱 read   # 讀鎖
lock tables 表名稱 write  # 寫鎖
# 給表解鎖    
unlock tables;    
# flush tables 方式施加鎖
flush tables 表名稱 with read lock;   #讀鎖
flush tables 表名稱 with write lock;  # 寫鎖

8.MySQL事務機制

• 一組原子性的SQL查詢，或多個SQL語句組成了一個獨立的單元，要麼這一組SQL語句全部執行，要麼全部不執行；

事物日誌：

• 管理事物機制的日誌；

• redo日誌 ：記錄SQL執行的語句，這些SQL語句還沒有同步到磁碟上，沒有修改資料。如果資料奔潰，可以通過撤銷SQL執行的語句來進行還原。但是，如果已經同步到磁碟上的SQL語句而言，就只能使用undo來回滾之前的資料了；

• undo日誌 ：記錄沒有執行SQL的樣子，也就是記錄修改資料之前的資料記錄下來；

ACID機制：

• automicity ：原子性，整個事物中的所有操作要麼全部成功提交，要麼全部失敗回滾；

• consistency ：一致性，資料庫總是從一個一致性狀態轉化為另一個一致性狀態；

• isolation ： 隔離性，事物不會相互影響，一個事物所作出的操作在提交之前，是不能為其他事物所見，隔離有多種級別，主要是為了併發；

• durability ：永續性，事物一旦提交，其所作的修改會儲存在資料庫中，不能丟失；

9.MySQL 查詢快取機制

• 快取的是查詢語句的整個查詢結果，是一個完整的select語句的快取結果；

• 哪些查詢可能不會被快取 ：查詢中包含UDF、儲存函式、使用者自定義變數、臨時表、mysql庫中系統表、或者包含列級別的許可權表、有著不確定值的函式；

1.query_cache_min_res_unit：查詢快取分配記憶體塊的最小的分配單位，較小的值較少記憶體浪費，但是會導致更加平凡的記憶體分配操作 ，較大的值會導致浪費
2.query_cache_limit：能夠快取的最大查詢結果，對有較大結果的查詢語句，建議在select中使用SQL_NO_CACHE
3.query_cache_size：查詢快取總共可用的記憶體空間，單位是位元組，必須是1024整數倍
4.query_cache_type：ON , OFF , DEMAND
5.query_cache_wlock_invalidate：如果某個資料表被其他的連線鎖定，是否仍然可以從查詢快取中返回結果，預設值為off，表示可以返回資料，on為不允許

• 快取命中率

快取命中率計算公式： Qcache_hits / (Qcache_hits+Com_select)

10.MySQL 日誌分類

• 查詢日誌 ：query log ，一般不啟用；

general_log = {ON|OFF}  # 是否啟用查詢日誌
general_log_file = /logs/mysql/general_log  # 當log_output為FILE型別時，日誌資訊的記錄位置；
log_output = {TABLE|FILE|NONE} 
log_output = TABLE,FILE

• 慢查詢日誌 ：slow_query_log ，用於對執行速率較慢的SQL語句就像過濾，有利於SQL程式碼的優化；

1.執行時長超出指定時長的操作 
show global variables like 'long_query_time'; 檢視指定的時長
set global long_query_time = 自定義時長
2.slow_query_log = {ON|OFF}：是否啟用慢查詢日誌
set global slow_query_log = ON
3.slow_query_log_file = mariadb1-slow.log
# 過濾條件
4.log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
5.log_slow_rate_limit = 1 指定記錄速率
6.log_slow_verbosity = 指定內容級別

• 錯誤日誌 ：error log ，必須啟用，二進位制日誌可以反應MySQL資料庫的錯誤資訊，用於除錯；

# 錯誤日誌資訊產生的來源
    mysqld啟動和關閉過程中輸出的資訊；
    mysqld執行中產生的錯誤資訊；
    event scheduler執行一個event時產生的日誌資訊
    在主從複製架構中的從伺服器上啟動從伺服器執行緒時產生的日誌資訊；
# 如何開啟錯誤日誌
    log_error = /path/to/somefile
    log_warnings = {ON|OFF}：是否記錄警告資訊於錯誤日誌中；

• 二進位制日誌 ：binary log，用於通過’重新執行’日誌檔案中的記錄的事件（SQL語句）來生成資料副本，也就是用於主從複製；

# 日誌記錄的格式分類
    基於“SQL語句”記錄: statement
    基於“行”記錄：row
    “混合模式” ：mixed，系統自行判斷
# 二進位制日誌檔案的構成
    日誌檔案：mysql-bin.檔案序號 
        例如： mysql-bin.000001
    索引檔案：mysql-bin.index 
        例如：mysql-bin.index

• 中繼日誌 ：relay log ，在主從複製架構中，從伺服器用於儲存從主伺服器的二進位制日誌中讀取到的時間；

• 事務日誌 ：transaction log ，事物日誌由事物型儲存引擎自行管理和使用，無需手動管理；

11.MySQL 備份策略的注意點

• 可容忍丟失多少資料；

• 恢復需要在多長時間內；

• 備份的物件: 資料、二進位制日誌和InnoDB的事務日誌、SQL程式碼（儲存過程和儲存函式、觸發器、事件排程器等）、伺服器配置檔案；

• 備份型別

1.站在資料集是否完整的角度上
    完全備份，部分備份 
2.站在完全備份的基礎上
    增量備份，差異備份 
3.站在是否影響資料集讀寫的角度上
    熱備份：線上備份，讀寫操作不受影響；
    溫備份：線上備份，讀操作可繼續進行，但寫操作不允許
    冷備份：離線備份，資料庫伺服器離線，備份期間不能為業務提供讀寫服務
    MyISAM儲存引擎: 能夠實現溫備
    InnoDB儲存引擎: 能夠實現熱備
4.站在資料儲存角度上
    物理備份：直接複製資料檔案進行的備份
    邏輯備份：從資料庫中“匯出”運算元據的SQL語句，再執行，實現備份

12.備份策略需要考慮的因素

• 持鎖的時長；

• 備份過程時長；

• 備份負載；

• 恢復過程時長；

13.資料庫備份具體解決方案

• 資料：完全備份 + 增量備份
• 備份：物理 + 邏輯

14.備份工具介紹

• mysqldump ：邏輯備份工具，適用於所有儲存引擎，溫備；但是對InnoDB儲存引擎支援熱備；
• scp, tar 等檔案系統工具：物理備份工具，適用於所有儲存引擎；冷備；完全備份，部分備份，不適用於Innodb儲存引擎；
• lvm2的快照 ：幾乎熱備；藉助於檔案系統工具實現物理備份；
• mysqlhotcopy ： 幾乎冷備；僅適用於MyISAM儲存引擎

15.MySQL 半同步複製模型

• 所謂的半同步複製指的是一臺主節點有多個從節點，在眾多的從節點之中有一個從節點在收到主節點的二進位制日誌資訊之後，儲存在中繼日誌中，執行中繼日誌後，給主節點一個反饋資訊，直接點收到這個反饋資訊之後，返回給執行這句SQL的ORM語句，表示資料已經儲存完畢；

參考： https://www.9xkd.com/user/plan-view.html?id=4211738675