天天看點
傳回

卷妹帶你學資料庫---5天沖刺Day4

卷妹帶你學資料庫—5天沖刺Day4

卷妹帶你學資料庫---5天沖刺Day4

👩‍💻部落格首頁：京與舊鋪的部落格首頁

✨歡迎關注🖱點贊🎀收藏⭐留言✒

🔮本文由京與舊鋪原創

😘系列專欄：java學習

👕參考網課：尚矽谷

💻首發時間：🎞2022年6月13日🎠

🎨你做三四月的事，八九月就會有答案，一起加油吧

🀄如果覺得部落客的文章還不錯的話，請三連支援一下部落客哦

🎧最後的話，作者是一個新人，在很多方面還做的不好，歡迎大佬指正，一起學習哦，沖沖沖

💬推薦一款模拟面試、刷題神器👉​​​點選進入網站​​

卷妹帶你學資料庫---5天沖刺Day4

🛒導航小助手🎪

文章目錄

  • ​​卷妹帶你學資料庫---5天沖刺Day4​​
  • ​​🛒導航小助手🎪​​
  • ​​@[toc]​​
  • ​​外鍵限制：foreign key​​
  • ​​存儲引擎​​
  • ​​MyISAM存儲引擎​​
  • ​​InnoDB存儲引擎​​
  • ​​MEMORY存儲引擎​​
  • ​​事務​​
  • ​​送出事務、復原事務​​
  • ​​事務四個特性​​
  • ​​事務的隔離性​​
  • ​​序列化/串行化：serializable（最高的隔離級别）​​
  • ​​索引（index）​​
  • ​​id(PK) name 每一行記錄在硬碟上都有實體存儲編号​​
  • ​​添加索引的條件​​
  • ​​索引的建立和删除​​

外鍵限制：foreign key

外鍵限制涉及到的相關術語：

外鍵限制：一種限制（ foreign key）

外鍵字段：該字段上添加了外鍵限制

外鍵值：外鍵字段當中的每一個值。

存儲引擎

什麼是存儲引擎，有什麼用呢？

存儲引擎是MySQL中特有的一個術語，其它資料庫中沒有。（Oracle中有，但是不叫這個名字）

存儲引擎這個名字高端大氣上檔次。實際上存儲引擎是一個表存儲/組織資料的方式。

不同的存儲引擎，表存儲資料的方式不同。

怎麼給表添加/指定“存儲引擎”呢？

show create table t_student;

可以在建表的時候給表指定存儲引擎。

CREATE TABLE ​​

​t_student​

​​ (

​​

​no​

​​ int(11) NOT NULL AUTO_INCREMENT,

​​

​name​

​​ varchar(255) DEFAULT NULL,

​​

​cno​

​​ int(11) DEFAULT NULL,

PRIMARY KEY (​​

​no​

​​),

KEY ​​

​cno​

​​ (​

​cno​

​​),

CONSTRAINT ​​

​t_student_ibfk_1​

​​ FOREIGN KEY (​

​cno​

​​) REFERENCES ​

​t_class​

​​ (​

​classno​

​​)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

在建表的時候可以在最後小括号的")"的右邊使用：

ENGINE來指定存儲引擎。

CHARSET來指定這張表的字元編碼方式。

結論：

mysql預設的存儲引擎是：InnoDB

mysql預設的字元編碼方式是：utf8

怎麼檢視mysql支援哪些存儲引擎呢？

指令： show engines \G

MyISAM存儲引擎

它管理的表具有以下特征：

使用三個檔案表示每個表：

格式檔案 — 存儲表結構的定義（mytable.frm）

資料檔案 — 存儲表行的内容（mytable.MYD）

索引檔案 — 存儲表上索引（mytable.MYI）：索引是一本書的目錄，縮小掃描範圍，提高查詢效率的一種機制。

可被轉換為壓縮、隻讀表來節省空間

提示一下：

對于一張表來說，隻要是主鍵，

或者加有unique限制的字段上會自動建立索引。

MyISAM存儲引擎特點：

可被轉換為壓縮、隻讀表來節省空間

這是這種存儲引擎的優勢！！！！

MyISAM不支援事務機制，安全性低。

InnoDB存儲引擎

這是mysql預設的存儲引擎，同時也是一個重量級的存儲引擎。

InnoDB支援事務，支援資料庫崩潰後自動恢複機制。

InnoDB存儲引擎最主要的特點是：非常安全。

它管理的表具有下列主要特征：

– 每個 InnoDB 表在資料庫目錄中以.frm 格式檔案表示

– InnoDB 表空間 tablespace 被用于存儲表的内容（表空間是一個邏輯名稱。表空間存儲資料+索引。）

– 提供一組用來記錄事務性活動的日志檔案

– 用 COMMIT(送出)、SAVEPOINT 及ROLLBACK(復原)支援事務處理

– 提供全 ACID 相容

– 在 MySQL 伺服器崩潰後提供自動恢複

– 多版本（MVCC）和行級鎖定

– 支援外鍵及引用的完整性，包括級聯删除和更新

InnoDB最大的特點就是支援事務：

以保證資料的安全。效率不是很高，并且也不能壓縮，不能轉換為隻讀，

不能很好的節省存儲空間。

MEMORY存儲引擎

使用 MEMORY 存儲引擎的表，其資料存儲在記憶體中，且行的長度固定，

這兩個特點使得 MEMORY 存儲引擎非常快。

MEMORY 存儲引擎管理的表具有下列特征：

– 在資料庫目錄内，每個表均以.frm 格式的檔案表示。

– 表資料及索引被存儲在記憶體中。（目的就是快，查詢快！）

– 表級鎖機制。

– 不能包含 TEXT 或 BLOB 字段。

MEMORY 存儲引擎以前被稱為HEAP 引擎。

MEMORY引擎優點：查詢效率是最高的。不需要和硬碟互動。

MEMORY引擎缺點：不安全，關機之後資料消失。因為資料和索引都是在記憶體當中。

事務

什麼是事務？

一個事務其實就是一個完整的業務邏輯。

是一個最小的工作單元。不可再分。

什麼是一個完整的業務邏輯？

假設轉賬，從A賬戶向B賬戶中轉賬10000.

将A賬戶的錢減去10000（update語句）

将B賬戶的錢加上10000（update語句）

這就是一個完整的業務邏輯。

以上的操作是一個最小的工作單元，要麼同時成功，要麼同時失敗，不可再分。

這兩個update語句要求必須同時成功或者同時失敗，這樣才能保證錢是正确的。

隻有DML語句才會有事務這一說，其它語句和事務無關！！！

insert

delete

update

隻有以上的三個語句和事務有關系，其它都沒有關系。

因為 隻有以上的三個語句是資料庫表中資料進行增、删、改的。

隻要你的操作一旦涉及到資料的增、删、改，那麼就一定要考慮安全問題。

InnoDB存儲引擎：提供一組用來記錄事務性活動的日志檔案

事務開啟了：

insert

insert

insert

delete

update

update

update

事務結束了！

在事務的執行過程中，每一條DML的操作都會記錄到“事務性活動的日志檔案”中。

在事務的執行過程中，我們可以送出事務，也可以復原事務。

送出事務？

清空事務性活動的日志檔案，将資料全部徹底持久化到資料庫表中。

送出事務标志着，事務的結束。并且是一種全部成功的結束。

復原事務？

将之前所有的DML操作全部撤銷，并且清空事務性活動的日志檔案

復原事務标志着，事務的結束。并且是一種全部失敗的結束。

送出事務、復原事務

送出事務：commit; 語句

復原事務：rollback; 語句（復原永遠都是隻能復原到上一次的送出點！）

事務對應的英語單詞是：transaction

測試一下，在mysql當中預設的事務行為是怎樣的？

mysql預設情況下是支援自動送出事務的。（自動送出）

什麼是自動送出？

每執行一條DML語句，則送出一次！

這種自動送出實際上是不符合我們的開發習慣，因為一個業務

通常是需要多條DML語句共同執行才能完成的，為了保證資料

的安全，必須要求同時成功之後再送出，是以不能執行一條

就送出一條。

怎麼将mysql的自動送出機制關閉掉呢？

先執行這個指令：start transaction;

代碼示範

示範事務：
 ---------------------------------復原事務----------------------------------------
 mysql> use bjpowernode;
 Database changed
 mysql> select * from dept_bak;
 Empty set (0.00 sec)mysql> start transaction;
 Query OK, 0 rows affected (0.00 sec)mysql> insert into dept_bak values(10,‘abc’, ‘tj’);
 Query OK, 1 row affected (0.00 sec)mysql> insert into dept_bak values(10,‘abc’, ‘tj’);
 Query OK, 1 row affected (0.00 sec)mysql> select * from dept_bak;
 ±-------±------±-----+
 | DEPTNO | DNAME | LOC |
 ±-------±------±-----+
 | 10 | abc | tj |
 | 10 | abc | tj |
 ±-------±------±-----+
 2 rows in set (0.00 sec)mysql> rollback;
 Query OK, 0 rows affected (0.00 sec)mysql> select * from dept_bak;
 Empty set (0.00 sec)---------------------------------送出事務----------------------------------------
 mysql> use bjpowernode;
 Database changed
 mysql> select * from dept_bak;
 ±-------±------±-----+
 | DEPTNO | DNAME | LOC |
 ±-------±------±-----+
 | 10 | abc | bj |
 ±-------±------±-----+
 1 row in set (0.00 sec)mysql> start transaction;
 Query OK, 0 rows affected (0.00 sec)mysql> insert into dept_bak values(20,'abc
 Query OK, 1 row affected (0.00 sec)mysql> insert into dept_bak values(20,'abc
 Query OK, 1 row affected (0.00 sec)mysql> insert into dept_bak values(20,'abc
 Query OK, 1 row affected (0.00 sec)mysql> commit;
 Query OK, 0 rows affected (0.01 sec)mysql> select * from dept_bak;
 ±-------±------±-----+
 | DEPTNO | DNAME | LOC |
 ±-------±------±-----+
 | 10 | abc | bj |
 | 20 | abc | tj |
 | 20 | abc | tj |
 | 20 | abc | tj |
 ±-------±------±-----+
 4 rows in set (0.00 sec)mysql> rollback;
 Query OK, 0 rows affected (0.00 sec)mysql> select * from dept_bak;
 ±-------±------±-----+
 | DEPTNO | DNAME | LOC |
 ±-------±------±-----+
 | 10 | abc | bj |
 | 20 | abc | tj |
 | 20 | abc | tj |
 | 20 | abc | tj |
 ±-------±------±-----+
 4 rows in set (0.00 sec)

事務四個特性

A：原子性

說明事務是最小的工作單元。不可再分。

C：一緻性

所有事務要求，在同一個事務當中，所有操作必須同時成功，或者同時失敗，

以保證資料的一緻性。

I：隔離性

A事務和B事務之間具有一定的隔離。

教室A和教室B之間有一道牆，這道牆就是隔離性。

A事務在操作一張表的時候，另一個事務B也操作這張表會那樣？？？

D：持久性

事務最終結束的一個保障。事務送出，就相當于将沒有儲存到硬碟上的資料

儲存到硬碟上！

事務的隔離性

A教室和B教室中間有一道牆，這道牆可以很厚，也可以很薄。這就是事務的隔離級别。

這道牆越厚，表示隔離級别就越高。

事務和事務之間的隔離級别有哪些呢？4個級别

讀未送出：read uncommitted（最低的隔離級别）《沒有送出就讀到了》

什麼是讀未送出？

事務A可以讀取到事務B未送出的資料。

這種隔離級别存在的問題就是：

髒讀現象！(Dirty Read)

我們稱讀到了髒資料。

這種隔離級别一般都是理論上的，大多數的資料庫隔離級别都是二檔起步！

讀已送出：read committed《送出之後才能讀到》

什麼是讀已送出？

事務A隻能讀取到事務B送出之後的資料。

這種隔離級别解決了什麼問題？

解決了髒讀的現象。

這種隔離級别存在什麼問題？

不可重複讀取資料。

什麼是不可重複讀取資料呢？

在事務開啟之後，第一次讀到的資料是3條，目前事務還沒有

結束，可能第二次再讀取的時候，讀到的資料是4條，3不等于4

稱為不可重複讀取。

這種隔離級别是比較真實的資料，每一次讀到的資料是絕對的真實。

oracle資料庫預設的隔離級别是：read committed

可重複讀：repeatable read《送出之後也讀不到，永遠讀取的都是剛開啟事務時的資料》

什麼是可重複讀取？

事務A開啟之後，不管是多久，每一次在事務A中讀取到的資料

都是一緻的。即使事務B将資料已經修改，并且送出了，事務A

讀取到的資料還是沒有發生改變，這就是可重複讀。

可重複讀解決了什麼問題？

解決了不可重複讀取資料。

可重複讀存在的問題是什麼？

可以會出現幻影讀。

每一次讀取到的資料都是幻象。不夠真實！

早晨9點開始開啟了事務，隻要事務不結束，到晚上9點，讀到的資料還是那樣！

讀到的是假象。不夠絕對的真實。

mysql中預設的事務隔離級别就是這個！！！！！！！！！！！

序列化/串行化：serializable（最高的隔離級别）

這是最高隔離級别，效率最低。解決了所有的問題。

這種隔離級别表示事務排隊，不能并發！

synchronized，線程同步（事務同步）

每一次讀取到的資料都是最真實的，并且效率是最低的。

檢視隔離級别：SELECT @@tx_isolation

±----------------+

| @@tx_isolation |

±----------------+

| REPEATABLE-READ |

±----------------+

mysql預設的隔離級别

索引（index）

什麼是索引？

索引是在資料庫表的字段上添加的，是為了提高查詢效率存在的一種機制。

一張表的一個字段可以添加一個索引，當然，多個字段聯合起來也可以添加索引。

索引相當于一本書的目錄，是為了縮小掃描範圍而存在的一種機制。

對于一本字典來說，查找某個漢字有兩種方式：

第一種方式：一頁一頁挨着找，直到找到為止，這種查找方式屬于全字典掃描。

效率比較低。

第二種方式：先通過目錄（索引）去定位一個大概的位置，然後直接定位到這個

位置，做局域性掃描，縮小掃描的範圍，快速的查找。這種查找方式屬于通過

索引檢索，效率較高。

select * from t_user where name = ‘jack’;

以上的這條SQL語句會去name字段上掃描，為什麼？

因為查詢條件是：name=‘jack’

如果name字段上沒有添加索引（目錄），或者說沒有給name字段建立索引，

MySQL會進行全掃描，會将name字段上的每一個值都比對一遍。效率比較低。

MySQL在查詢方面主要就是兩種方式：

第一種方式：全表掃描

第二種方式：根據索引檢索。

注意：

在實際中，漢語字典前面的目錄是排序的，按照a b c d e f…排序，

為什麼排序呢？因為隻有排序了才會有區間查找這一說！（縮小掃描範圍

其實就是掃描某個區間罷了！）

在mysql資料庫當中索引也是需要排序的，并且這個是以的排序和TreeSet

資料結構相同。TreeSet（TreeMap）底層是一個自平衡的二叉樹！在mysql

當中索引是一個B-Tree資料結構。

遵循左小又大原則存放。采用中序周遊方式周遊取資料。

實作原理

假設有一張使用者表：t_user

id(PK) name 每一行記錄在硬碟上都有實體存儲編号

100 zhangsan 0x1111

120 lisi 0x2222

99 wangwu 0x8888

88 zhaoliu 0x9999

101 jack 0x6666

55 lucy 0x5555

130 tom 0x7777

提醒1：在任何資料庫當中主鍵上都會自動添加索引對象，id字段上自動有索引，

因為id是PK。另外在mysql當中，一個字段上如果有unique限制的話，也會自動

建立索引對象。

提醒2：在任何資料庫當中，任何一張表的任何一條記錄在硬碟存儲上都有

一個硬碟的實體存儲編号。

提醒3：在mysql當中，索引是一個單獨的對象，不同的存儲引擎以不同的形式

存在，在MyISAM存儲引擎中，索引存儲在一個.MYI檔案中。在InnoDB存儲引擎中

索引存儲在一個邏輯名稱叫做tablespace的當中。在MEMORY存儲引擎當中索引

被存儲在記憶體當中。不管索引存儲在哪裡，索引在mysql當中都是一個樹的形式

存在。（自平衡二叉樹：B-Tree）

添加索引的條件

什麼條件下，我們會考慮給字段添加索引呢？

條件1：資料量龐大（到底有多麼龐大算龐大，這個需要測試，因為每一個硬體環境不同）

條件2：該字段經常出現在where的後面，以條件的形式存在，也就是說這個字段總是被掃描。

條件3：該字段很少的DML(insert delete update)操作。（因為DML之後，索引需要重新排序。）

建議不要随意添加索引，因為索引也是需要維護的，太多的話反而會降低系統的性能。

建議通過主鍵查詢，建議通過unique限制的字段進行查詢，效率是比較高的。

索引的建立和删除

建立索引：

mysql> create index emp_ename_index on emp(ename);

給emp表的ename字段添加索引，起名：emp_ename_index

删除索引：

mysql> drop index emp_ename_index on emp;

将emp表上的emp_ename_index索引對象删除。

是否使用索引進行檢索

1.5、在mysql當中，怎麼檢視一個SQL語句是否使用了索引進行檢索？

mysql> explain select * from emp where ename = ‘KING’;

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

掃描14條記錄：說明沒有使用索引。type=ALL

mysql> create index emp_ename_index on emp(ename);

mysql> explain select * from emp where ename = ‘KING’;

±—±------------±------±-----±----------------±----------------±--------±------±-----±------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

±—±------------±------±-----±----------------±----------------±--------±------±-----±------------+

| 1 | SIMPLE | emp | ref | emp_ename_index | emp_ename_index | 33 | const | 1 | Using where |

±—±------------±------±-----±----------------±----------------±--------±------±-----±------------+

----------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

±—±------------±------±-----±----------------±----------------±--------±------±-----±------------+

| 1 | SIMPLE | emp | ref | emp_ename_index | emp_ename_index | 33 | const | 1 | Using where |

±—±------------±------±-----±----------------±----------------±--------±------±-----±------------+

卷妹帶你學資料庫---5天沖刺Day4

結束語🏆🏆🏆

🔥推薦一款模拟面試、刷題神器網站

點選跳轉進入網站​​點選進入​​ 1、算法篇（398題）：面試必刷100題、算法入門、面試高頻榜單

資料庫 mysql java 存儲引擎 資料
上一篇: ALEXA站長全攻略（轉）
下一篇: 終端服務全攻略 

繼續閱讀