資料庫學習之九：mysql 存儲引擎

九、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
將一個事務的結果與其他事務完全隔離