文章目錄
-
- 一、事務的概念
- 二、事務的ACID特點
- 三、事務的操作
- 四、視圖
-
- 4.1、建立視圖查詢
- 4.2、多表相連查詢
- 五、存儲引擎
-
- 5.1、MyISAM
-
- 5.1.1、什麼是MyISAM ？
- 5.1.2、MyISAM的特點
- 5.1.3、适用于哪些生産場景 ?
- 5.2、InnpDB引擎
-
- 5.2.1、InnpDB的特點
- 5.2.2、适用生産場景
- 5.3、配置存儲引擎
-
- 5.3.1、 檢視表使用的存儲引擎
- 5.3.2、修改存儲引擎
一、事務的概念
- 事務是一種機制、一個操作序列，包含了一組資料庫操作指令，并且把所有的指令作為一個整體一起向系統送出或撤銷操作請求，即這一組資料庫指令要麼都執行，要麼都不執行。
- 事務是一個不可分割的工作邏輯單元，在資料庫系統上執行并發操作時，事務是最小的控制單元。
- 适用于多使用者同時操作的資料庫系統的場景，如銀行、保險公司及證券交易系統等等。
- 通過事務的整體性以保證資料的一緻性。
二、事務的ACID特點
- 原子性(Atomicity)
- 事務是一個完整的操作，事務的各元素是不可分的 (原子的)。
- 事務中的所有元素必須作為一個整體送出或復原。
- 如果事務中的任何元素失敗，則整個事務将失敗。
- 一緻性(Consistency)
- 當事務完成時，資料必須處于一緻狀态。在事務開始之前，資料庫中存儲的資料處于一緻狀态；在正在進行的事務中，資料可能處于不一緻的狀态；當事務成功完成時，資料必須再次回到已知的一緻狀态。
- 隔離性(Isolation)
- 對資料進行修改的所有并發事務是彼此隔離的，這表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務。
-
修改資料的事務可以在另一個使用相同資料的事務開始之前通路這
些資料，或者在另一一個使用相同資料的事務結束之後通路這些資料。
- 持久性(Durability)
- 事務持久性指不管系統是否發生故障，事務處理的結果都是永久的。
- 一旦事務被送出，事務的效果會被永久地保留在資料庫中。
三、事務的操作
- 預設情況下MySQL的事務是自動送出的，當sq|語句送出時事務便自動送出
- 手動對事務進行控制的方法：
- 事務處理指令控制。
- 使用 set 設定事務處理方式。
- 事務處理指令控制事務
begin：開始一個事務
commit：送出一個事務
rollback：復原一個事務
- 使用set指令進行控制
set autocommit = 0 #禁止自動送出
set autocommit = 1 #開啟自動送出
注意：
- begin 時開始事務時，我們的資料是先儲存到緩存區，然後輸入commit 指令才送出上，修改磁盤内容。
- 事務的起點也可以通過set autocommit=1（即等同于 begin）
#存檔節點
savepoint 節點名;
我們做復原操作 rollback to 節點名;
單獨使用 rollback 指令直接回到開始事務的時間狀态。
四、視圖
可以了解為虛拟的表，它和資料庫中真實存在的資料表不同，視圖中的資料是基于真實表查詢得到的。
作用：
- 1、提升真實表的安全性：視圖是虛拟的，可以隻授予使用者視圖的權限而不授予真實表的權限，起到保護真實表的作用。
- 2、定制化展示資料：基于同樣的實際表，可以通過不同的視圖來向不同需求的使用者定制化展示資料。
- 3、簡化資料操作：适用于查詢語句比較複雜使用頻率較高的場景，可以通過視圖來實作。
4.1、建立視圖查詢
#視圖建立格式：
create view 視圖名稱 as select 查詢内容 from 表名 where條件
#多表相連查詢用 on，單表用 where
create view info_view as select id,name,address,age from info where id=2 or id=7; #要id=2,7 的視圖
4.2、多表相連查詢
格式：select 表名1.字段,表名2.字段 from 表名1 inner join 表名2 on 相連部分
例：
select stu02.id,stu02.name,stu02.score,hob.hobname from stu02 inner join hob on stu02.hobby=hob.id;
例如，下面有兩張表，第一個表為 stu02；第二個表為 hob，因為這兩張表中有部分資訊時相關聯的，如何操作：
#建立表sut02的結構
mysql> create table stu02 (id int(4) not null primary key auto_increment,name char(10) not null,score decimal not null,hobby int(4) not null);
Query OK, 0 rows affected (0.01 sec)
#插入表stu02的記錄
mysql> insert into stu02 (name,score,hobby) values ('lisi',85,1),('zhangsan',92,2),('yiyi',87,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
#建立表hob的結構
mysql> create table hob (id int(4) not null primary key,hobname varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)
#插入表hob的記錄
mysql> insert into hob values (1,'看書'),(2,'跑步'),(3,'遊戲');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
#再給表stu02插入一條記錄
mysql> insert into stu02 (name,score,hobby) values ('xiaoyi',92,2);
Query OK, 1 row affected (0.01 sec)
1、不按條件查詢
2、多表按條件查詢
3、如果資料表的名字長，可以使用别名。
五、存儲引擎
- MySQL中的資料用各種不同的技術存儲在檔案中，每一種技術都使用不同的存儲機制、索引技巧、鎖定水準并最終提供不同的功能和能力，這些不同的技術以及配套的功能在MySQL中稱為存儲引擎。
- 存儲引擎就是MySQL将資料存儲在檔案系統中的存儲方式或者存儲格式。
- 目前MySQL常用的兩種存儲引擎：
- MyISAM
- InnoDB
- MySQL存儲引擎是MySQL資料庫伺服器中的元件負責為資料庫執行實際的資料I/O操作。
- 使用特殊存儲引擎的主要優點之一在于， 僅需提供特殊應用所需的特性，資料庫中的系統開銷較小，具有更有效和更高的資料庫性能。
-
MySQL系統中，存儲引擎處于檔案系統之上,在資料儲存到資料檔案之前會傳輸到存儲引擎,之後按照各個
存儲引擎的存儲格式進行存儲。
5.1、MyISAM
5.1.1、什麼是MyISAM ？
- MyISAM存儲引擎是MySQL關系資料庫系統5.5版本之前預設的存儲引擎，前身是ISAM。
- ISAM是一個定義明确且曆經時間考驗的資料表格管理方法，在設計之時就考慮到資料庫被查詢的次數要遠大于更新的次數。
- ISAM的特點：
- ISAM執行讀取操作的速度很快
- 它不支援事務處理
- 而且不占用大量的記憶體和存儲資源
- 不能夠容錯
- MyISAM管理非事務表，是ISAM的擴充格式。
- 提供ISAM裡所沒有的索引和字段管理的大量功能。
- MyISAM使用-種表格鎖定的機制，以優化多個并發的讀寫操作。
- MyISAM提供高速存儲和檢索，以及全文搜尋能力，受到web開發的青睐。
5.1.2、MyISAM的特點
- 不支援事務型
- 表級鎖定形式，資料在更新時鎖定整個表
- 資料庫在讀寫過程中互相阻塞
- 會在資料寫入的過程阻塞使用者資料的讀取
- 也會在資料讀取的過程中阻塞使用者的資料寫入
- 可通過key_buffer_size來設定緩存索引，提高通路性能，減少磁盤IO的壓力
- 但緩存隻會緩存索引檔案，不會緩存資料
- 采用MyISAM存儲引擎資料單獨寫入或讀取，速度過程較快且占用資源相對少
- MyISAM存儲引擎它不支援外鍵限制，隻支援全文索引
- 每個MyISAM在磁盤.上存儲成三個檔案，每一個檔案的名字以表的名字開始，擴充名指出檔案類型
- MyISAM在磁盤上存儲的檔案
- .frm檔案存儲表定義
- 資料檔案的擴充名為.MYD (MYData)
- 索引檔案的擴充名是.MYI (MYIndex)
5.1.3、适用于哪些生産場景 ?
- 公司業務不需要事務的支援。
- 一般單方面讀取資料比較多的業務，或單方寫入資料比較多的業務。
- MyISAM存儲引擎資料讀寫都比較頻繁場景不适合。
- 使用讀寫并發通路相對較低的業務
- 資料修改相對較少的業務
- 對資料業務一緻性要求不是非常高的業務
- 伺服器硬體資源相對比較差
5.2、InnpDB引擎
5.2.1、InnpDB的特點
- 支援事務:支援4個事務隔離級别
- 行級鎖定，但是全表掃描仍然會是表級鎖定
- 讀寫阻塞與事務隔離級别相關
- 具有非常高效的緩存特性：能緩存索引，也能緩存資料
- 表與主鍵以簇的方式存儲
- 支援分區、表空間，類似oracle資料庫
- 支援外鍵限制，5.5以前不支援全文索引，5.5版本以後支援全文索引
- 對硬體資源要求還是比較高的場合
5.2.2、适用生産場景
- 業務需要事務的支援
- 行級鎖定對高并發有很好的适應能力，但需確定查詢是通過索引來完成
- 業務資料更新較為頻繁的場景，如:論壇，微網誌等
- 業務資料一緻性要求較高，例如:銀行業務
- 硬體裝置記憶體較大，利用Innodb較好的緩存能力來提高記憶體使用率，減少磁盤IO的壓力
5.3、配置存儲引擎
- 在企業中選擇好合适的存儲引擎之後，就可以進行修改了
- 修改步驟：
- 檢視資料庫可配置的存儲引擎
- 檢視表正在使用的存儲引擎
- 配置存儲引擎為所選擇的類型
- 使用show engines檢視系統支援的存儲引擎
5.3.1、 檢視表使用的存儲引擎
方法1：
show table status from 庫名 where name='表名'\G
使用 \G 代替 分号 表示垂直顯示結果。
例：show table status from student where name='stu02';
方法2：常用方法。
show create table 表名;
例：show create table stu02;
5.3.2、修改存儲引擎
- 方法1： alter table 修改。
alter table table_name engine=引擎類型;
- 方法2：修改my.cnf檔案， 指定預設存儲引擎并重新開機服務。
vim /etc/my.cnf
#在mysqld段插入下行代碼。
default-storage-engine=myisam
systemctl restart mysqld
- 方法3： create table 建立表時指定存儲引擎
create table 表名 (字段) engine = 引擎
- 方法4： Mysql_ convert_ table_ format 轉化存儲引擎
Mysql_convert_table_format -user=root -password=密碼
-sock=/tmp/mysql.sock -engine =引擎庫名表名