一.MySQL架構

連接層:

? ? ? ? 處理客戶端連接服務,認證授權相關的操作

服務層:

? ? ? ? 最核心的一層（核心服務功能）,處理sql,包括sql優化,函數調用....

存儲引擎層:

? ? ? ? 存儲引擎是真正負責來操作數據的（mysql中數據的存儲和提取）, mysql中有不同存儲引擎,不同的引擎處理技術不同

物理文件層:

? ? ? ?存在電腦硬盤上的表數據,日志文件，負責與存儲引擎的數據交互

二.存儲引擎

? ? ? ? 存儲引擎是真正負責來操作數據的,不同的引擎處理技術不同

? ? ? ? ? ? ? ? ?(支持事務,行級鎖,外鍵約束,索引技術)

查看支持的引擎

? ? ? ?SHOWENGINES;

查看表引擎

? ? ? ?SHOWTABLE STATUS LIKE '表名'

修改引擎

? ? ? ?方式1:將mysql.ini 中default-storage-engine=InnoDB，重啟服務.

? ? ? ?方式2:建表時指定 CREATETABLE 表名(...)ENGINE=MYISAM;

? ? ? ?方式3:建表后修改 ALTERTABLE 表名 ENGINE=INNODB;

存儲引擎主要有：

? ? ? ? ? ? ?1.MyIsam,2.InnoDB,3.Memory,4.Blackhole,5.CSV,6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

但在實際開發中,需要根據具體的操作,來為表選擇合適的引擎

mysql中常用的兩種引擎:用MyIsam 和InnoDB

兩者的區別：

? ? ? innodb:支持事務,有行級鎖,外鍵約束

? ? ? myisam: 不支持事務,不支持行鎖,支持表鎖,不支持外鍵約束

? ? ? ? ? ? ? ? ? ?（因此當INSERT或UPDATE數據時即寫操作需要鎖定整個表）

eg:

? ?新增,修改,刪除操作比較多的表,建議使用innodb引擎

? ?查詢多的表(例如每月話費賬單表),只插入一次數據,后面都是查詢操作,建議使用myIsam引擎.

注：由于現在表數據量比較大,動輒需要對數據庫進行拆分,使用存儲過程,視圖,自定義函數這些內容移植性差,所以不建議使用了.

三.索引

1.為什么要用索引

? ? ? 表中存儲著大量數據,如果沒有索引,當我們需要查詢一條數據時,就要從第一條數據開始查詢,直到找到我們需要查詢的數據,這么查找效率太低,太耗時間和內存,所以需要索引

2.索引的優/缺點

優點:

? ? ?①減少了查詢次數, 降低IO成本(與硬盤交互次數),提高了數據檢索的效率

? ? ?②索引是已經排好序了的,通過索引給數據排序提高排序效率,減少來cpu的消耗

缺點:

? ? ?①索引的本質是一張表保存了主鍵與索引字段,并指向實體表的記錄,它也是占磁盤空間

? ? ?②索引提高了查詢速度,但也降低更新表的速度,每次更新段,都要調整索引信息

? 注：數據變化之后索引結構也是需要發生改變, 所以添加索引也是需要有規則的.

3.索引的原理

? ? ? 索引類似于書的目錄,通過目錄可以快速的定位到數據的物理位置.

4.什么是索引

? ? ? 索引是幫助mysql高效獲取數據的排好序的快速查找的數據結構

? ? ? 在向數據庫中插入數據時,mysql自動會為主鍵創建一個索引樹,通過主鍵查詢時,先在主鍵索引樹上查詢,可以提高查詢效率,找到主鍵后,就可以快速定位到數據.

5.索引分類

①主鍵索引

? ?一個表中只能有一個主鍵, primary key

創建主鍵索引：

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

//設定為主鍵后數據庫會自動建立索引

刪除主鍵索引：

ALTER TABLE 表名 drop PRIMARY KEY ;

②單值索引

一個索引只包含單個列，一個表可以有多個單列索引

創建單值索引 ：

CREATE INDEX 索引名 ON 表名(列名);

刪除索引：

DROP INDEX 索引名;

③組合索引(符合索引)

一個索引中可以包含多個列(建議)

創建復合索引 ：

CREATE INDEX 索引名 ON 表名(列1,列2...);

刪除索引：

DROP INDEX 索引名 ON 表名;

組合索引最左前綴原則

使用組合索引時,需要在查詢條件中用到最左側列,否則索引失效.

eg：? ? ??

? ? ? ?列如表中有a,b,c3列,為a,b兩列創建組合索引,那么在使用時需要滿足最左 側索引原則.在使用組合索引的列作為條件時,必須要出現最左側列為條件,否則 組合索引不生效.

? ? ? ? ? 列如? ?select * from table where a=’’and b=’’? ? ?索引生效

? ? ? ? ? ? ? ? ? ? select * from table where b=’’and a=’’? ? ?索引生效

? ? ? ? ? ? ? ? ? ? select * from table where a=’’and c=’’? ? ?索引生效

? ? ? ? ? ? ? ? ? ? select * from table where b=’’and c=’’? ? ?索引不生效

④全文索引

? 在mysql中 like 模糊查詢導致索引失效,這時就可以使用全文索引來解決這個問題

全局索引使用：

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名)WITH PARSER ngram;

SELECT 結果 FROM 表名 WHEREMATCH(列名)AGAINST(‘搜索詞')

查看索引：

SHOWINDEX FROM 表名;

6.索引創建原則

①哪些情況建議添加索引

? ?主鍵自動建立唯一索引

? ?作為查詢條件的字段應該創建索引(where 后面的語句)

? ?盡量使用聯合索引，減少單列索引

? ?針對于數據量較大，且查詢比較頻繁的表建立索引。

? ?查詢中排序的字段，分組中的字段,若通過索引去訪問將大大提高排序速

②哪些情況不應該加索引

? ? 表記錄太少

? ? 經常增刪改的表

? ? Where條件里用不到的字段不創建索引

? ? 數據重復且分布平均的表字段，因此應該只為最經常查詢和最經常排序的數 據列建立索引，某個數據列包含許多重復的內容，建立索引沒有太大實際效果

7.索引數據結構

mysql中索引數據結構使用的是B+樹

B+樹優點:

? ? ?一個節點中可以存儲多個數據,

? ? ?非葉子節點中不存儲表數據,只存儲索引數據,這樣一個節點中就可以存儲更多索引數據

? ? ?數據都存儲在葉子節點, 通過主鍵查詢,找到了索引,就找打到了數據

? ? ?葉子節點之間也是建立了連接, 所以非常適合范圍查詢

8.回表查詢

? ? ? ?在使用非主鍵索引查詢數據時,例如通過學號查詢數據,先在學號索引樹查詢,查詢到之后,還需要去主鍵索引樹查詢,因為數據都掛載在主鍵索引樹下,需要回表二次查詢,實際開發中,盡量減少回表查詢.

四.數據庫事務

? ? ? ?數據庫事務是數據對一次連接過程發送的多條sql執行進行管理,這多條sql要么都執行,要么都不執行.

? ? ? ?當一次連接過程中,所有操作都執行沒有問題時,向數據庫提交事務,數據庫才真正的執行sql.

eg：

? ? ? 轉賬: 對用戶來說轉賬是一個整體的操作:包含減錢和加錢, 必須要保證這多條sql要么都成功執行要么都失敗

sql1 從A賬戶減錢

//異常

sql2 向B賬戶加錢

提交事務

1.事務特性

? ? ? ?事務是必須滿足4個條件：原子性（Atomicity，或稱不可 分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

? ? ? 原子性: 不可拆分 要么都執行,要么都不執行

? ? ? 持久性: 事務一旦提交后,不能撤銷,保證數據持久保存

? ? ? 隔離性: 數據庫運行多個事務同時對數據庫數據進行操作,操作時給我們提供了4種隔離級別進行選擇, 保證操作之間相互之間的關系

? ? ? 一致性: 數據完整性 經過多次轉賬操作后, 最終的結果需要和我們預期結果是一致

2.事務隔離級別

查看隔離級別
SELECT @@session.transaction_isolation,@@transaction_isolation

①讀 未提交:

? ? ? 一個事務讀到另一個事務還未提交的數據，這會帶來臟讀，幻讀，不可重復讀問題

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

②讀 已提交:

? ? ? 一個事務只能讀到另一個事務提交后的數據，避免了臟讀，仍然存在不可以重復讀和幻讀問題

? ? ?不可重復讀問題: 在同 一個事務中,讀取相同的數據兩次,結果兩次結果不一樣

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

③可 重復讀:

? ? ? ?在同 一個事務中,讀取相同的數據兩次,結果兩次結果一樣

? ? ? ?可重復讀也稱為快照讀, 底層在第一次讀取的時候,進行拍照, 在同一個事物中,第二次讀的時候,直接讀取快照,解決不可重復讀問題,部分還存在幻讀問題

? ? 幻讀問題: 在同同一個事物中,查詢兩次,兩次的數量不一樣

? ? 可重復讀隔離級別 對于普通的查詢sql解決了幻讀問題，對于查詢語句后面添加了for update的語句,仍然存在幻讀問題

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

④串行化:

? ?一次只能允許一個事務 操作(多個事務對同一條數據) 可以解決以上問題,但是效率低

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

五.mysql中的鎖機制

1.全局鎖

鎖住整個數據庫, 只允許查詢操作

一般在備份數據庫時使用.

添加全局鎖：

FLUSH TABLES WITH READ LOCK

釋放全局鎖：

UNLOCK TABLES;

2.表鎖

? ? 鎖著整表, myisam引擎默認只支持表鎖, 一次只允許一個對該表事務進行操作.

3.行級鎖

行鎖 : 精確的鎖定操作的那一行數據

? ? ? ? ?例如 update test set age = 20 where id = 1; 鎖住id=1的這條記錄

間隙鎖: 鎖定一個區間

? ? ? ? ?例如 update test set age = 20 where id >1 and id < 5 鎖定了id=2,3,4的記錄

行鎖又可以分為:

共享鎖:

一般給查詢語句添加,

當一條查詢語句添加了共享鎖后, 允許其他事同時務讀, 但是不允許其他事務為該條記錄加排他鎖.

排他鎖:

新增,修改,刪除默認加排他鎖

查詢語句也可以加排他鎖, select ..... for update