MySQL事务管理与存储引擎

MySQL事务管理与存储引擎

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: 0mysql> 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 -ADatabase changed```sql
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: 0mysql> 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: 0mysql> 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: 0mysql> 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 |
+----+入代码片

MySQL数据库存储引擎
概述
MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式MySQL常用的存储引擎：
MyISAM
lnnoDBMySQL数据库中的组件，负责执行实际的数据l/O操作MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储
关于MyISAM存储引擎
MyISAM概述
MyISAM不支持事务，也不支持外键访问速度快对事务完整性没有要求MyISAM在磁盘上存储成三个文件：
.frm文件存储表定义
数据文件的扩展名为.MYD (MYData)
索引文件的扩展名是.MYI (MYIndex)表级锁定形式，数据在更新时锁定整个表数据库在读写过程中相互阻塞：
会在数据写入的过程阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入数据单独写入或读取，速度过程较快且占用资源相对少MylAM支持的存储格式：
静态表
动态表
压缩表
InnoDB存储引擎
InnoDB的概述
支持4个事务隔离级别行级锁定，但是全表扫描仍然会是表级锁定读写阻塞与事务隔离级别相关非常高效的缓存索引和数据表与主键以簇的方式存储支持分区、表空间，类似oracle数据库支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

相关操作
查看引擎
相关语法

show engine    //查看数据库引擎
show table status from 表名 where name='表名'      //查看表引擎
show create table 表名  //同样可以用于查看表引擎
在这里插入代码片

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=引擎类型   //修改表的引擎类型

修改默认引擎
修改/f 配置文件中配置

[root@localhost ~]# vi /etc/myf
[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: user2Engine: MyISAMVersion: 10Row_format: FixedRows: 0Avg_row_length: 0Data_length: 0
Max_data_length: 16044073672507391Index_length: 1024Data_free: 0Auto_increment: NULLCreate_time: 2020-09-12 06:07:58Update_time: 2020-09-12 06:07:58Check_time: NULLCollation: utf8_general_ciChecksum: NULLCreate_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)