MySQL事務管理
- MySQL事務概念
- 事務的ACID特點
- 事務控制陳述句
MySQL資料庫存盤引擎
- 概述
- 關于MyISAM存盤引擎
- - MyISAM概述
  - MyISAM的適用場景
- InnoDB存盤引擎
- - InnoDB的概述
  - InnoDB的適用場景
- 企業選擇存盤引擎的依據
- 相關操作
- - 查看引擎
  - - 相關語法
    - 操作
  - 修改表的存盤引擎
  - - 常用陳述句
    - 操作
  - 修改默認引擎
  - - 修改/etc/my.cof 組態檔中配置
    - 操作
  - 轉化存盤引擎

MySQL事務管理

MySQL事務概念

是一種機制、一個操作序列，包含了一組資料庫操作命令，并且把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組資料庫命令要么都執行，要么都不執行
是一個不可分割的作業邏輯單元，在資料庫系統上執行并發操作時，事務是最小的控制單元
適用于多用戶同時操作的資料庫系統的場景，如銀行、保險公司及證券交易系統等等
通過事務的整體性以保證資料的一致性

事務的ACID特點

原子性(Atomicity)

事務是一個完整的操作，事務的各元素是不可分的
事務中的所有元素必須作為一個整體提交或回滾
如果事務中的任何元素失敗，則整個事務將失敗

一致性(Consistency)

當事務完成時，資料必須處于一致狀態
在事務開始前，資料庫中存盤的資料處于一致狀態
在正在進行的事務中，資料可能處于不一致的狀態
當事務成功完成時，資料必須再次回到已知的一致狀態

隔離性(lsolation)

對資料進行修改的所有并發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務
修改資料的事務可在另一個使用相同資料的事務開始之前訪問這些資料，或者在另一個使用相同資料的事務結束之后訪問這些資料

持久性(Durability)

指不管系統是否發生故障，事務處理的結果都是永久的
—旦事務被提交，事務的效果會被永久地保留在資料庫中

事務控制陳述句

BEGIN或START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT identifier
RELEASE SAVEPOINT identifier
ROLLBACK TO identifier
SET TRANSACTION

mysql> create table user(
    -> id int(10) not null,
    -> name char(16) default '無名小卒',
    -> age int(3) default'0',
    -> primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;
+-------+----------+------+-----+--------------+-------+
| Field | Type     | Null | Key | Default      | Extra |
+-------+----------+------+-----+--------------+-------+
| id    | int(10)  | NO   | PRI | NULL         |       |
| name  | char(16) | YES  |     | 無名小卒 	   |       |
| age   | int(3)   | YES  |     | 0            |       |
+-------+----------+------+-----+--------------+-------+
3 rows in set (0.00 sec)

mysql> insert into user values (1,'zhangshan',25);   //插入資料
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;   //查看資料
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangshan |   25 |
+----+-----------+------+
1 row in set (0.00 sec)

mysql> begin;    //開始一個事務
Query OK, 0 rows affected (0.00 sec)

mysql> update user set id=2;   //更改資料，將id改為2
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;   //查看更改后的資料
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | zhangshan |   25 |
+----+-----------+------+
1 row in set (0.00 sec)

mysql> \q   //不提交事務，直接退出
Bye
[root@localhost ~]# mysql -u root -p   //再次登入
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use bbs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user;  //查詢資料，發現資料有恢復原樣，沒有保存修改
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangshan |   25 |
+----+-----------+------+
1 row in set (0.00 sec)
###################### 不提交的事務是不會保存的
mysql> begin;   //開始一個事務
Query OK, 0 rows affected (0.00 sec)

mysql> update user set id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id from user;   //查看id資訊，id為2
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> savepoint a;   //設定一個回滾點
Query OK, 0 rows affected (0.00 sec)

mysql> update user set id=3;   //將id改為3
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id from user;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

mysql> rollback to a;   //回到回滾點a之前
Query OK, 0 rows affected (0.00 sec)

mysql> select id from user;  查看id資訊，id變回了2，a之前的資訊
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> update user set id=4;   //修改id=4
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id from user;
+----+
| id |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

mysql> savepoint b;    //設定回滾點b
Query OK, 0 rows affected (0.00 sec)

mysql> rollback to a;   //回到回滾點a之前
Query OK, 0 rows affected (0.00 sec)

mysql> rollback to b;   //再回滾到b，發現回滾點吧不存在，完完全全回到a之前，包括a之后設定的回滾點也會消失
ERROR 1305 (42000): SAVEPOINT b does not exist
mysql> commit;  //提交事務
Query OK, 0 rows affected (0.00 sec)

mysql> select id from user;   
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

MySQL資料庫存盤引擎

概述

MySQL中的資料用各種不同的技術存盤在檔案中，每一種技術都使用不同的存盤機制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術以及配套的功能在MySQL中稱為存盤引擎
存盤引擎是MySQL將資料存盤在檔案系統中的存盤方式或者存盤格式
MySQL常用的存盤引擎：
MyISAM
lnnoDB
MySQL資料庫中的組件，負責執行實際的資料l/O操作
MySQL系統中，存盤引擎處于檔案系統之上，在資料保存到資料檔案之前會傳輸到存盤引擎，之后按照各個存盤引擎的存盤格式進行存盤

關于MyISAM存盤引擎

MyISAM概述

MyISAM不支持事務，也不支持外鍵
訪問速度快
對事務完整性沒有要求
MyISAM在磁盤上存盤成三個檔案：
.frm檔案存盤表定義
資料檔案的擴展名為.MYD (MYData)
索引檔案的擴展名是.MYI (MYIndex)
表級鎖定形式，資料在更新時鎖定整個表
資料庫在讀寫程序中相互阻塞：
會在資料寫入的程序阻塞用戶資料的讀取
也會在資料讀取的程序中阻塞用戶的資料寫入
資料單獨寫入或讀取，速度程序較快且占用資源相對少
MylAM支持的存盤格式：
靜態表
動態表
壓縮表

MyISAM的適用場景

公司業務不需要事務的支持
單方面讀取或寫入資料比較多的業務
MyISAM存盤引擎資料讀寫都比較頻繁場景不適合
使用讀寫并發訪問相對較低的業務
資料修改相對較少的業務
對資料業務一致性要求不是非常高的業務
服務器硬體資源相對比較差

InnoDB存盤引擎

InnoDB的概述

支持4個事務隔離級別
行級鎖定，但是全表掃描仍然會是表級鎖定
讀寫阻塞與事務隔離級別相關
非常高效的快取索引和資料
表與主鍵以簇的方式存盤
支持磁區、表空間，類似oracle資料庫
支持外鍵約束，5.5前不支持全文索引，5.5后支持全文索引

InnoDB的適用場景

業務需要事務的支持
行級鎖定對高并發有很好的適應能力，但需確保查詢是通過索引來完成
業務資料更新較為頻繁的場景（如:論壇，微博等）
業務資料一致性要求較高（如:銀行業務）
硬體設備記憶體較大，利用lnnodb較好的快取能力來提高記憶體利用率，減少磁盤IO的壓力

企業選擇存盤引擎的依據

需要考慮每個存盤引擎提供的核心功能及應用場景
支持的欄位和資料型別：
所有引擎都支持通用的資料型別
但不是所有的引擎都支持其它的欄位型別，如二進制物件
鎖定型別:不同的存盤引擎支持不同級別的鎖定：
表鎖定
行鎖定
索引的支持
建立索引在搜索和恢復資料庫中的資料時能顯著提高性能
不同的存盤引擎提供不同的制作索引的技術
有些存盤引擎根本不支持索引
事務處理的支持
提高在向表中更新和插入資訊期間的可靠性
可根據企業業務是否要支持事務選擇存盤引擎

相關操作

查看引擎

操作

mysql> show engines;   //查看引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show create table user \G;   //可以查看表的引擎  \G代表豎向排列
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE "user" (
  "id" int(10) NOT NULL,
  "name" char(16) DEFAULT '無名小卒',
  "age" int(3) DEFAULT '0',
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改表的存盤引擎

常用陳述句

  create table 表名 engine=引擎型別    //創建表時指定引擎型別
  alter table 表名 engine=引擎型別   //修改表的引擎型別

操作

mysql> alter table user engine=MyISAM;  //修改表引擎為MyISAM
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table user \G;   //查看，修改成功
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE "user" (
  "id" int(10) NOT NULL,
  "name" char(16) DEFAULT '無名小卒',
  "age" int(3) DEFAULT '0',
  PRIMARY KEY ("id")
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> CREATE TABLE "user1" (   //創建表時指定存盤引擎
    ->   "id" int(10) NOT NULL,
    ->   "name" char(16) DEFAULT '無名小卒',
    ->   "age" int(3) DEFAULT '0',
    ->   PRIMARY KEY ("id")
    -> ) ENGINE=innodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table user1 \G;  //查看，指定成功
*************************** 1. row ***************************
       Table: user1
Create Table: CREATE TABLE "user1" (
  "id" int(10) NOT NULL,
  "name" char(16) DEFAULT '無名小卒',
  "age" int(3) DEFAULT '0',
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> create table name(id int)engine=MyISAM;  //創建表時指定引擎
Query OK, 0 rows affected (0.01 sec)

mysql> show create table name \G; //查看驗證
*************************** 1. row ***************************
       Table: name
Create Table: CREATE TABLE "name" (
  "id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table name engine=innodb;  //使用alter陳述句修改引擎
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table name \G;  //查看驗證，修改成功
*************************** 1. row ***************************
       Table: name
Create Table: CREATE TABLE "name" (
  "id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

修改默認引擎

修改/etc/my.cof 組態檔中配置

 [mysqld]區域下配置
   	  default-storage-engine=引擎型別     //插入這條配置
 例如：default-storage-engine=InnoDB

操作

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
……省略部分
default-storage-engine=myisam   //插入這條陳述句，配置默認引擎為myisam
[root@localhost ~]# systemctl restart mysqld  //重啟生效


mysql> CREATE TABLE "user2" (   //創建新表測驗
    -> "id" int(10) NOT NULL,
    -> "name" char(16) DEFAULT '無名小卒',
    -> "age" int(3) DEFAULT '0',
    -> PRIMARY KEY ("id"));
Query OK, 0 rows affected (0.01 sec)

mysql> show table status where name='user2'\G;   //查看新表，默認存盤引擎變為myisam型別
*************************** 1. row ***************************
           Name: user2
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 16044073672507391
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-09-12 06:07:58
    Update_time: 2020-09-12 06:07:58
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> show variables like '%storage_engine%';   //使用查看變數的方式查看默認引擎
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | MyISAM |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

轉化存盤引擎

這一種方式目前不是很懂，只是聽說能用，希望了解的大佬能指點一下；

格式：
Mysql_convert_table_format-user=root--password=密碼--sock=/tmp/mysql.sock-engine=引擎 庫名 表名

[root@localhost ~]#  yum -y install perl-DBI perl-DBD-MySQL
[root@localhost ~]# /usr/local/mysql/bin/mysql_convert_table_format --user=root --password='123456' --sock=/tmp/mysql.sock auth

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/26931.html

標籤：其他

上一篇：Windows 10安裝Mysql和SQLyog-----很全！！！！

下一篇：2020 全國大學生數學建模競賽題目

MySQL事務管理及存盤引擎