九、mysql 存盤引擎
1、課程大綱
存盤引擎的介紹
mysql中的存盤引擎分類
mysql存盤引擎設定
mysql，innodb引擎存盤結構
mysql中的事物
mysql中的鎖
2、mysql存盤引擎介紹
mysql存盤引擎介紹
檔案系統：
作業系統組織和存取資料的一種機制，
檔案系統是一種軟體，
型別：ext2,3,4,xfs資料：
不管使用什么檔案系統，資料內容不會變化
不同的是，存盤空間，大小，速度
mysql引擎：
可以理解為，mysql的“檔案系統”，只不過功能更加強大，
mysql引擎功能：
除了可以提供基本的存取功能，還有更多功能事物功能，鎖定，備份和恢復，優化以及特殊功能，
3、mysql存盤引擎種類
MySQL 提供以下存盤引擎：
InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV
還可以使用第三方存盤引擎（TokuDB），
4、資料庫的存盤引擎
存盤引擎查詢
存盤引擎查詢：
show engines;
select @@default_storage_engine;
show create table city;
show table status like 'city'\G
use information_schema
select table_schema,table_name,engine from information_schema.tables where table_schema='world';
select table_schema,table_name,engine from information_schema.tables where table_schema='mysql';
select table_schema,table_name,engine from information_schema.tables where engine='csv';
存盤引擎的配置：
查看存盤引擎：
show engines;
select @@default_storage_engine;
mysql> show variables like '%engine%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
+----------------------------+--------+
將存盤引擎設定在組態檔中，重啟mysql生效，
配置存盤引擎：
1、在啟動組態檔中設定服務器存盤引擎：
[mysqld]
default-storage-engine=<Storage Engine>
2、使用 SET 命令為當前客戶機會話設定：
SET @@storage_engine=<Storage Engine>;
3、在 CREATE TABLE 陳述句指定：
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
mysql> show variables like '%engine%';
mysql> use world
mysql> create table test3 (id int ) engine=innodb;
mysql> show create table test3;
5、innodb體系結構：
表空間：
共享表空間：主要存放系統元資料
獨立表空間：主要存放用戶資料
邏輯概念，資料存盤的概念
表空間資料檔案：idb檔案，在/app/mysql/data/lufei
共享表空間的設定：
默認的配置：
innodb_data_file_path /application/mysql/data/ ibdata1:12M:autoextend
共享表空間的設定：
共享表空間設定：
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend ----錯誤的配置XXX
在 /application/mysql/data/查看idbdata1實際的大小，然后在增加idbdata2的大小，進行擴展，此命令配置在mysql的組態檔中，
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend -----正確的配置
獨立表空間：
除了系統表空間之外，InnoDB 還在資料庫目錄中創建另外的表空間，用于每個 InnoDB 表的 .ibd 檔案，
InnoDB 創建的每個新表在資料庫目錄中設定一個 .ibd 檔案來搭配表的 .frm 檔案，
可以使用 innodb_file_per_table 選項控制此設定
更改該設定僅會更改已創建的新表的默認值，
注：在mysql5.6開始，默認的配置為：
| innodb_file_per_table | ON |
獨立表空間洗掉表空間命令：
alter table testtab discard tablespace;
該命令執行后/app/mysql/data/lufei中的testtab的ibd檔案就被洗掉，
6、Innodb引擎-事務
組資料操作執行步驟，這些步驟被視為一個作業單元:
用于對多個陳述句進行分組
可以在多個客戶機并發訪問同一個表中的資料時使用
所有步驟都成功或都失敗:
如果所有步驟正常，則執行
如果步驟出現錯誤或不完整，則取消
事務ACID:
Atomic（原子性）
所有陳述句作為一個單元全部成功執行或全部取消，
Consistent（一致性）
如果資料庫在事務開始時處于一致狀態，則在執行該事務期間將保留一致狀態，
Isolated（隔離性）
事務之間不相互影響，
Durable（持久性）
事務成功完成后，所做的所有更改都會準確地記錄在資料庫中，所做的更改不會丟失，
7、mysql事務控制陳述句
事務中的標準陳述句：DML陳述句
insert,update,delete三類事物陳述句，
如果遇到以上三個命令，都是事物型的操作，都會默認在前面添加begin命令，
START TRANSACTION（或 BEGIN）：顯式開始一個新事務
1
2
3
COMMIT：永久記錄當前事務所做的更改
BEGIN
1 打標記a
2 打標記b（savepoint時候）
3
ROLLBACK：取消當前事務所做的更改（回滾到所打的標記）
SAVEPOINT：分配事務程序中的一個位置，以供將來參考
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后執行的更改
RELEASE SAVEPOINT：洗掉 savepoint 識別符號
SET AUTOCOMMIT：為當前連接禁用或啟用默認 autocommit 模式
保存：commit是自動保存的：
mysql> show variables like "%auto%";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_stats_auto_recalc | ON |
| sql_auto_is_null | OFF |
+-----------------------------+-------+
8 rows in set (0.00 sec)
需要將自動保存的commit改成關閉：（只修改當前會話）
mysql> set autocommit=0;
查看全域commit配置：
mysql> show global variables like "%auto%";
在有些業務繁忙企業場景下，這種配置可能會對性能產生很大影響，但對于安全性上有很大提高，
將來，我們需要去權衡我們的業務需求去調整是否自動提交，
我們可以通過以下命令進行修改關閉（0是關閉，1是開啟）：
SET GLOBAL AUTOCOMMIT=0; -所有新建會話
SET SESSION AUTOCOMMIT=0; -當前會話
SELECT @@AUTOCOMMIT; -查看設定結果
我們也可以修改組態檔讓其永久生效：
vi /etc/my.cnf
[mysqld]
AUTOCOMMIT=0
隱式提交陳述句（commit）：
用于隱式提交的 SQL 陳述句：
START TRANSACTION
SET AUTOCOMMIT = 1
導致提交的非事務陳述句：
DDL陳述句： （ALTER、CREATE 和 DROP）
DCL陳述句： （GRANT、REVOKE 和 SET PASSWORD）
鎖定陳述句：（LOCK TABLES 和 UNLOCK TABLES）
導致隱式提交的陳述句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
如：
BEGIN
1
2
3
BGEIN(開始之前就默認commit)
1
2
3
8、事務的日志
Redo是什么？
redo,顧名思義“重做日志”，是事務日志的一種，
作用是什么？
在事務ACID程序中，實作的是“D”持久化的作用，
undo是什么？
undo,顧名思義“回滾日志”，是事務日志的一種，
作用是什么？
在事務ACID程序中，實作的是“A、C”原子性和一致性的作用，
什么是“鎖”？
“鎖”顧名思義就是鎖定的意思，
“鎖”的作用是什么？
在事務ACID程序中，“鎖”和“隔離級別”一起來實作“I”隔離性的作用，
鎖的粒度：
1、MyIasm：低并發鎖——表級鎖
2、Innodb：高并發鎖——行級鎖
四種隔離級別：
READ UNCOMMITTED
允許事務查看其他事務所進行的未提交更改
READ COMMITTED
允許事務查看其他事務所進行的已提交更改
REPEATABLE READ******
確保每個事務的 SELECT 輸出一致
InnoDB 的默認級別
SERIALIZABLE
將一個事務的結果與其他事務完全隔離
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/44460.html
標籤：MySQL