一、事務
1.什么是事務
MySQL事務是一組sql語句或一個獨立運行的工作單元。這個工作單元要么全部執(zhí)行,要么全部不執(zhí)行。
2. 事務的四大特性
原子性:一個事務不可再分割,事務中的所有操作,要么全部完成,要么全部不完成。
一致性:一個事務執(zhí)行會使數(shù)據(jù)從一個一致狀態(tài)切換到另外一個一致狀態(tài)。也就是說,在事務開始之前和事務結束以后,數(shù)據(jù)庫的完整性沒有被破壞。
隔離性:一個事務所做的修改在最終提交之前,對其它事務是不可見的。也就是說一個事務的執(zhí)行不會受到其它事務的干擾。
持久性:一個事務一旦提交以后,對數(shù)據(jù)的修改就會永久的保存到數(shù)據(jù)庫中,即便系統(tǒng)故障也不會丟失。
2.事務的并發(fā)問題
① 事務并發(fā)問題什么時候發(fā)生?
當多個事務同時操作同一個數(shù)據(jù)庫的相同數(shù)據(jù)時,就會發(fā)生并發(fā)問題。
② 事務的并發(fā)問題有哪些?
臟讀:對于兩個事務T1、T2, T1 讀取了已經(jīng)被T2 更新但還沒有被提交的數(shù)據(jù)。如果事務T2進行了回滾, T1讀取到的數(shù)據(jù)就是臨時且無效的。
不可重復讀:對于兩個事務T1、T2, T1 讀取了一個數(shù)據(jù), 然后 T2 更新了該數(shù)據(jù)。如果T1再次讀取同一個數(shù)據(jù), 值就不相同了。
幻讀:對于兩個事務T1、T2, T1 從一個表中讀取了一個數(shù)據(jù), 然后T2 在該表中插入了一些新的行。如果T1 再次讀取同一個表, 就會多出幾行。
二、索引
1. 什么是索引
索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結構,使用索引可以提高數(shù)據(jù)庫中特定數(shù)據(jù)的查詢速度
2. 索引的優(yōu)缺點
優(yōu)點
① 使用索引可以大大加快數(shù)據(jù)的查詢速度;② 通過創(chuàng)建唯一索引,可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性;③ 在使用分組和排序子句進行數(shù)據(jù)查詢時,使用索引可以減少在查詢中分組和排序的時間。
缺點
① 創(chuàng)建索引和維護索引需要時間,隨著數(shù)據(jù)量的增加所需要時間也會增加;② 當對表中的數(shù)據(jù)進行增加、刪除和修改的時候,索引也要動態(tài)地維護,這樣就降低了數(shù)據(jù)的維護速度;③ 索引需要占磁盤空間,除了數(shù)據(jù)表占數(shù)據(jù)空間之外,每一個索引還要占定的物理空間,如果有大量的索引,索引文件可能比數(shù)據(jù)文件更快達到最大文件尺寸
2. 索引的設計原則
① 索引并非越多越好,一個表中如有大量的索引,不僅占用磁盤空間,而且會影響 INSERT、 DELETE、UPDATES等語句的性能,因為當表中的數(shù)據(jù)更改的同時索引也會進行調整和更新。
② 避免對經(jīng)常更新的表進行過多的索引,并且索引中的列盡可能少。而對經(jīng)常用于查詢的字段應該創(chuàng)建索引,但要避免添加不必要的字段。
③ 數(shù)據(jù)量小的表最好不要使用索引,由于數(shù)據(jù)較少,查詢花費的時間可能比遍歷索引的時間還要短,索引可能不會產(chǎn)生優(yōu)化效果。
④ 在條件表達式中經(jīng)常用到的不同值較多的列上建立索引,在不同值很少的列上不要建立索引。比如在學生表的“性別”字段上只有“男”與“女”兩個不同值,因此就無須建立索引。如果建立索引不但不會提高查詢效率,反而會嚴重降低數(shù)據(jù)更新速度。
⑤ 當唯一性是某種數(shù)據(jù)本身的特征時,指定唯一索引。使用唯一索引副需能確保定義的列的數(shù)據(jù)完整性,以提高查詢速度。
⑥ 在頻繁進行排序或分組(即進行group by或 order by操作)的列上建立索引,如果待排序的列有多個,可以在這些列上建立組合索引。
三、數(shù)據(jù)庫設計三大范式
1. 第一范式:確保每列保持原子性
2. 第二范式:確保表中的每列都和主鍵相關
3. 第三范式:確保每列都和主鍵列直接相關,而不是間接相關
四、MySQL的主從復制
1. 概念
MySQL 主從復制是指數(shù)據(jù)可以從一個MySQL數(shù)據(jù)庫服務器主節(jié)點復制到一個或多個從節(jié)點。
2. 原理
在這里插入圖片描述
從庫生成兩個線程,一個I/O線程,一個SQL線程。
I/O線程 去請求主庫 的binlog,并將得到的binlog日志寫到relay log(中繼日志) 文件中;
主庫會生成一個 log dump 線程,用來給從庫 I/O線程傳binlog。
SQL線程 會讀取relay log文件中的日志,并解析成具體操作,來實現(xiàn)主從的操作一致,而最終數(shù)據(jù)一致。
3. 主從復制用途
① 讀寫分離,在開發(fā)工作中,有時候會遇見某個sql
② 數(shù)據(jù)實時備份,當系統(tǒng)中某個節(jié)點發(fā)生故障時,可以方便的故障切換。
五、MySQL中的鎖
1. 為什么要加鎖
數(shù)據(jù)庫的鎖是為了支持對共享資源進行并發(fā)訪問,提供數(shù)據(jù)的完整性和一致性。在數(shù)據(jù)庫中加鎖是保證在高并發(fā)的情況下,訪問數(shù)據(jù)庫的時候,數(shù)據(jù)不會出現(xiàn)問題。
2. 鎖的分類
按操作分:① 讀鎖(共享鎖);② 寫鎖(排它鎖)。
按粒度分:① 表鎖;② 行鎖;③ 頁鎖。
六、如何做 MySQL的性能優(yōu)化
① 當只需要一條數(shù)據(jù)的時候,使用 limit 1
② 盡量避免使用 select * ,在查詢的時候列出需要查詢的字段。
③ 使用 join 代替子查詢。
④ 減少使用 or,使用 in 或者 union(union all) 代替。
⑤ 為搜索字段創(chuàng)建索引。但是不要過度索引,索引越多,占用空間越大,反而性能變慢。
⑥ 避免進行類型轉換,不然會導致索引失效。
⑦ 對查詢語句使用explain 。 使用explain ,可以幫助了解MySQL是如何處理sql語句的, 可以查看到sql的執(zhí)行計劃,這樣就能更好的去了解的sql語句的不足,然后優(yōu)化語句。
⑧ 垂直分割,將常用和有關系的字段放在相同的表中,把一張表的數(shù)據(jù)分成幾張表 這樣可以降低表的復雜度和字段的數(shù)目,從而達到優(yōu)化的目的。
來源:CSDN網(wǎng)