1.字符集

字符集：其實就是字符的集合
字符（Character）是各種文字和符號的總稱，包括各國家文字、標點符號、圖形符號、數字等。
字符集（Character set）是多個字符的集合，字符集種類較多，每個字符集包含的字符個數不同。
常見字符集名稱：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。

幾種常見的字符集:

ASCII:ASCII ((American Standard Code for Information Interchange): 美國信息交換標準代碼）是基于拉丁字母的一套電腦編碼系統，主要用于顯示現代英語和其他西歐語言。

簡體中文字符集: GB2312字符集、 GB18030字符集

繁體中文字符集： BIG5字符集

Unicode（統一碼、萬國碼、單一碼）是計算機科學領域里的一項業界標準，包括字符集、編碼方案等。Unicode 是為了解決傳統的字符編碼方案的局限而產生的，它為每種語言中的每個字符設定了統一并且唯一的二進制編碼，以滿足跨語言、跨平臺進行文本轉換、處理的要求。

UTF-8：是針對Unicode的一種可變長度字符編碼。屬于unicode編碼,此外還有UTF-16、UTF-32，數字越大表示需要存放一個字符的空間就會多，能存放的字符的數量也會多。

注意：utf8編碼：1個英文字母占1個字節，1個漢字占3個字節。我們一般會使用utf8，比其他字符集更加通用一點

這邊可以使用show character set; 查看mysql支持的所有字符集

mysql總共支持41中字符集，這邊可以看下Default collaction是每種字符集所對應的排序方式，這邊去utf8為例，這邊支持的utf8_general_ci其實一種不分大小寫的排序，就好比一條字符串‘A a b c B’
插入數據庫，這樣的排序方式就將其排為‘A a b B c’,不會在乎其大小寫，按照字母本身順序進行排序

為什么utf8字符集漢字占三個字節，而字母只占一個字節？

注意這邊還有要注意Maxlen列表名了字符集所支持的存儲最大長度是多少，就比如常見漢字有5000種，這邊就需要3位來存儲漢字，“2^3*8”（一位等于8bit）來保證漢字不會超過存儲的最大限度，而字母只有26種這邊就只需要 2的8次方就足夠存儲了

庫表字段與字符集之間的關系

在建庫建表建字段的過程中，在不指定字符集時字符集默認是會進行繼承的，但是這個可以進行修改，這里就說明列對應的字段的字符集優先級最高

MySQL默認字符集:

rpm方式安裝的mysql默認的字符集是latin1，因為mysql作者是來自瑞典的，瑞典那邊其實使用Latin1字符集，編譯安裝的時候因為我們指定了字符集為utf8，所有默認的字符集是utf8

怎么查看與建表時指定字符集創造

這邊可以通過show create table + 已經創造好的表名; 這邊可以發現我們這邊的字符集為utf8

創造指定字符集表可以通過下面語句實現，這邊其實就是在建表后面指定下字符集

create table t4(id int ,name varchar(20)) default charset = 'latin1'

2.存儲引擎

什么是存儲引擎？

首先確定一點，存儲引擎的概念是MySQL里面才有的，不是所有的關系型數據庫都有存儲引擎這個概念，后面我們還會說，但是現在要確定這一點。存儲引擎的本質就是存取數據
　 　
在講清楚什么是存儲引擎之前，我們先來個比喻，我們都知道錄制一個視頻文件，可以轉換成不同的格式，例如mp4，avi，wmv等，但是，給我們或者用戶看懂實際視頻內容都是一樣的。直觀區別是，占用系統的空間大小與清晰程度可能不一樣。

但是對于用戶和應用程序來說同樣一張表的數據，無論用什么引擎來存儲，用戶能夠看到的數據是一樣的。不同儲引擎存取，引擎功能，占用空間大小，讀取性能等可能有區別。說白了，存儲引擎就是在如何存儲數據、提取數據、更新數據等技術方法的實現上，底層的實現方式不同，那么就會呈現出不同存儲引擎有著一些自己獨有的特點和功能，對應著不同的存取機制。
　　
　　因為在關系數據庫中數據的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型(即：對表的存儲、操作等的實現方法不同)，表是什么，表本質上就是磁盤上的文件。

其實MySQL支持多種存儲引擎，每種引擎有著一些自己獨特的功能，用戶在使用的時候，可以根據自己的業務場景來使用不同的存儲引擎，其中MySQL最常用的存儲引擎為：MyISAM和InnoDB。
在詳細介紹這些存儲引擎之前，我們先來看看MySQL的整個工作流程，看一下存儲引擎在哪里，MySQL都做了哪些事情。

這邊給一張MySQL體系結構的圖

如何查看表的存儲引擎

這里說明一下，存儲引擎默認是與表進行綁定的，這邊可以通過下面命令查看表的存儲引擎

show engines;  #查看MySQL所有的引擎

通過這個表其實就可以看到各種存儲引擎的特點了，我們默認的存儲引擎就是 InnoDB

幾種常見的存儲引擎

1、MyISAM

使用這個存儲引擎，每個MyISAM在磁盤上存儲成三個文件。

（1）frm文件：存儲表的定義數據
（2）MYD文件：存放表具體記錄的數據
（3）MYI文件：存儲索引

frm和MYI可以存放在不同的目錄下。MYI文件用來存儲索引，但僅保存記錄所在頁的指針，索引的結構是B+樹結構。下面這張圖就是MYI文件保存的機制：

這邊可以看出，myisam存儲引擎，這邊支持全局索引（索引的本質就是查詢數據的數據），速度毫無疑問肯定是相當快的，有一個重要的特點那就是不支持事務，但是這也意味著他的存儲速度更快，如果你的讀寫操作允許有錯誤數據的話，只是追求速度，可以選擇這個存儲引擎

MyISAM引擎特點：
        1.不支持事務
            事務是指邏輯上的一組操作，組成這組操作的各個單元，要么全成功要么全失敗。
        2.表級鎖定
            數據更新時鎖定整個表：其鎖定機制是表級鎖定，也就是對表中的一個數據進行操作都會將這個表鎖定，其他人不能操作這個表，這雖然可以讓鎖定的實現成本很小但是也同時大大降低了其并發性能。
        3.讀寫互相阻塞
            不僅會在寫入的時候阻塞讀取，MyISAM還會再讀取的時候阻塞寫入，但讀本身并不會阻塞另外的讀。
        4.只會緩存索引
            MyISAM可以通過key_buffer_size的值來提高緩存索引，以大大提高訪問性能減少磁盤IO，但是這個緩存區只會緩存索引，而不會緩存數據。
        
        5.讀取速度較快
            占用資源相對較少
        6.不支持外鍵約束，但只是全文索引
        7.MyISAM引擎是MySQL5.5版本之前的默認引擎，是對最初的ISAM引擎優化的產物。
MyISAM引擎特點

2.InnoDB

InnoDB引擎
        介紹：InnoDB引擎是MySQL數據庫的另一個重要的存儲引擎，正稱為目前MySQL AB所發行新版的標準，被包含在所有二進制安裝包里。和其他的存儲引擎相比，InnoDB引擎的優點是支持兼容ACID的事務(類似于PostGreSQL)，以及參數完整性(即對外鍵的支持)。Oracle公司與2005年10月收購了Innobase。Innobase采用雙認證授權。它使用GNU發行，也允許其他想將InnoDB結合到商業軟件的團體獲得授權。

InnoDB引擎特點：
        1.支持事務：支持4個事務隔離界別，支持多版本讀。
        2.行級鎖定(更新時一般是鎖定當前行)：通過索引實現，全表掃描仍然會是表鎖，注意間隙鎖的影響。
        3.讀寫阻塞與事務隔離級別相關(有多個級別，這就不介紹啦~)。
        4.具體非常高效的緩存特性：能緩存索引，也能緩存數據。
        5.整個表和主鍵與Cluster方式存儲，組成一顆平衡樹。(了解)
        6.所有SecondaryIndex都會保存主鍵信息。(了解)
        7.支持分區，表空間，類似oracle數據庫。
        8.支持外鍵約束，不支持全文索引(5.5之前)，以后的都支持了。
        9.和MyISAM引擎比較，InnoDB對硬件資源要求還是比較高的。
        
        小結：三個重要功能：Supports transactions，row-level locking，and foreign keys
InnoDB引擎特點

你正常使用數據庫的話，InnoDB絕對是你的首選存儲引擎，誤差小，功能多，這就是他最大的優勢點所在

3.Memory

將數據存在內存，為了提高數據的訪問速度，每一個表實際上和一個磁盤文件關聯。文件是frm。

（1）支持的數據類型有限制，比如：不支持TEXT和BLOB類型，對于字符串類型的數據，只支持固定長度的行，VARCHAR會被自動存儲為CHAR類型；

（2）支持的鎖粒度為表級鎖。所以，在訪問量比較大時，表級鎖會成為MEMORY存儲引擎的瓶頸；

（3）由于數據是存放在內存中，一旦服務器出現故障，數據都會丟失；

（4）查詢的時候，如果有用到臨時表，而且臨時表中有BLOB，TEXT類型的字段，那么這個臨時表就會轉化為MyISAM類型的表，性能會急劇降低；

（5）默認使用hash索引。

（6）如果一個內部表很大，會轉化為磁盤表。

在這里只是給出3個常見的存儲引擎。使用哪一種引擎需要靈活選擇，一個數據庫中多個表可以使用不同引擎以滿足各種性能和實際需求，使用合適的存儲引擎，將會提高整個數據庫的性能

Memory存儲引擎，適用于當你只需要創建臨時表，而不需要講表持久化保存的時候，這個絕對是不二選擇
創建臨時表方法

 create temporary table t5(id int,name varchar(20));

臨時表存在內存里，這邊直接使用select語法是查詢不到臨時表的，但是可以插入數據，但是你退出mysql后臨時表就會自動銷毀，其他用戶連接mysql是訪問不到臨時表的，這邊下圖說明了這其中的關系

4.CSV

CSV存儲引擎是MySQL專門處理csv文件的專屬引擎，csv文件其實就是以逗號分隔的文件，存儲格式就是下面的樣子

id,name,age
1,jack,12
2,rose,13
3,pitter,14

5.BlackHole

Blackhole引擎–“黑洞”. 其作用正如其名字一樣：任何寫入到此引擎的數據均會被丟棄掉， 不做實際存儲；Select語句的內容永遠是空。
下面是沒有BlackHole主從復制結構圖，這邊當主服務器進行數據更新后生成了一個二進制日志，然后講二進制日志給從服務器，從服務器按照日志復現主服務器執行的數據更新操作，從而達到與服務主服務器一摸一樣的執行效果

在大規模的Mysql服務器集群中，如果是存在一臺主服務，多臺從服務器，在繁忙的業務中，意味著主服務器每操作一個事件，都要往自己的二進制日志中寫數據，同時還要往多臺從服務器發一次，N臺服務器指向一臺主服務器，那么需要主服務器發送N次，會啟動N個線程，每個線程各自從線程里讀二進制日志，那么會有大量的IO，本來是為主服務器減輕負擔的，那么這樣只能造成壓力越來越大，那這樣master主機就會為每臺slave主機分配出一個binlog dump進程，這樣的話會嚴重影響master的性能

有了BlackHole存儲引擎之后的結構圖

這個存儲引擎解決了主從復制架構里，讓很多的從服務器直接到配置了blackhole存儲引擎的master上拿二進制日志，讓最上層的master的負載降低

BLACKHOLE總結
BLACKHOLE支持所有類型的索引
BLACKHOLE 表不存儲數據，如果復制基于SBR，語句可以記錄并在從庫執行；如果復制為RBR、MBR，UPDATE及DELETE操作將會跳過，不會記錄也從庫不執行。
Insert觸發器可以正常使用，Update、Delete觸發器因為不存儲數據不能觸發，FOR EACH ROW 也不能觸發。
BLACKHOLE 表Auto Increment字段不會自動遞增,也不保留自增字段的狀態
結合復制replicate-do和replicate-ignore規則，可使用BLACKHOLE當做一個分發主服務器
可用來驗證轉儲文件語法
測試binlog的開銷量，通過對比 BLACKHOLE 與 不啟動 binlog的性能
可能被用來查找與存儲引擎自身不相關的性能瓶頸

3.主外鍵

什么是主外鍵

假設這邊存在幾個表

學生表(學號，姓名，性別，班級)                      
課程表(課程編號,課程名,學分)                           
成績表(學號,課程號,成績)   

主鍵：關系型數據庫中的一條記錄中有若干個屬性，若其中某一個屬性組(注意是組)能唯一標識一條記錄，該屬性組就可以成為一個主鍵
那么這邊學生表里面學號以及課程表的課程編號就是主鍵了，成績表中單一一個屬性無法唯一標識一條記錄，學號和課程號的組合才可以唯一標識一條記錄，所以 學號和課程號的屬性組是一個主鍵 ，這種主鍵叫做聯合主鍵

外鍵：A表里的主鍵被引用到B表里做一個字段，這個字段就是B表里的外鍵，外鍵在另外表里要是是主鍵，但是外鍵不是必須的存在
成績表中的學號不是成績表的主鍵，但它和學生表中的學號相對應，并且學生表中的學號是學生表的主鍵，則稱成績表中的學號是學生表的外鍵，同理課程號也是

主鍵、外鍵和索引的區別

主鍵本質上其實就是唯一標識加不可重復（not null + unique）

如何創建主鍵

create table stu3(id int  comment '編號',name varchar(20) not null comment '姓名',primary key(id));

create table stu2(id int primary key comment '編號',name varchar(20) not null comment '姓名');

如何創造外鍵

這邊先創建一個子父表，外鍵在子表中肯定為父表的主鍵

創建父表
CREATE TABLE dept(
  deptid INTEGER,
  dname VARCHAR(20),
  PRIMARY KEY(deptid) 
); 

插入兩條數據

創建子表

CREATE TABLE emp(
  id INT PRIMARY KEY,
  NAME VARCHAR(20),
  deptid INT,
  FOREIGN KEY(deptid) REFERENCES dept(deptid)  # 建立名字叫做deptid的外鍵 ，外鍵來自于dept表的deptid 
);

外鍵命名最好與父表主鍵相同，這樣很好查看到此外鍵是來自于dept的deptid字段

這邊外鍵就構建成功了

數據庫中主鍵和外鍵的設計原則

主鍵和外鍵是把多個表組織為一個有效的關系數據庫的粘合劑。

主鍵和外鍵的設計對物理數據庫的性能和可用性都有著決定性的影響。

必須將數據庫模式從理論上的邏輯設計轉換為實際的物理設計，而主鍵和外鍵的結構是這個設計過程的癥結所在。一旦將所設計的數據庫用于了生產環境，就很難對這些鍵進行修改，所以在開發階段就設計好主鍵和外鍵就是非常必要和值得的。

兩個表建立主外鍵規則后，其里面有關系的字段就不能隨便刪除以及插入，子表插入字段時，必須考慮是否存在父表的主鍵字段，例如這邊deptpid只有 10 和 20 ，你如果在子表的deptpid插入30，那就會報錯

如何解除字父表主外鍵規則

ON DELETE CASCADE：級聯刪除

創建子表

CREATE TABLE emp8(
  id INTEGER PRIMARY KEY,
  NAME VARCHAR(20),
  deptid INTEGER,
   FOREIGN KEY(deptid) REFERENCES dept(deptid) 
   ON  DELETE  CASCADE
); 

插入數據

這邊 執行DELETE FROM dept WHERE deptid = 10; 發現成功，查看emp8里的文件發現不存在了，這就是級聯刪除

ON DELETE SET NULL方式

ON DELETE SET NULL：當刪除當刪除父表中的行時，如果子表中有依賴于被刪除父行的子行存在，那么不刪除中，而是將子行的外鍵列設置為null
這邊操作方式與上面相似這里就不重復操作了

借鑒文章

https://www.cnblogs.com/clschao/articles/9953550.html#part_1 存儲引擎總結
https://baijiahao.baidu.com/s?id=1655327558614401593&wfr=spider&for=pc 存儲引擎總結
https://www.cnblogs.com/wt645631686/p/8086682.html BlackHole總結