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)
緩存命中率.png
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
分享到:
閱讀更多 有夢愛幻想的女孩 的文章
