2020-11-08

MySQL学习周报

存储引擎

1、存储引擎？

1.1、完整的建表语句
	CREATE TABLE `t_x` (
	  `id` int(11) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

	注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

	建表的时候可以指定存储引擎，也可以指定字符集。

	mysql默认使用的存储引擎是InnoDB方式。
	默认采用的字符集是UTF8

1.2、什么是存储引擎呢？
	存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，
	就是“表的存储方式”）

	mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
	每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

2.3、查看当前mysql支持的存储引擎？
	show engines \G

	mysql 5.5.36版本支持的存储引擎有9个：
		*************************** 1. row ***************************
				Engine: FEDERATED
			  Support: NO
			  Comment: Federated MySQL storage engine
		Transactions: NULL
					 XA: NULL
		  Savepoints: NULL
		*************************** 2. row ***************************
				Engine: MRG_MYISAM
			  Support: YES
			  Comment: Collection of identical MyISAM tables
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 3. row ***************************
				Engine: MyISAM
			  Support: YES
			  Comment: MyISAM storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 4. row ***************************
				Engine: BLACKHOLE
			  Support: YES
			  Comment: /dev/null storage engine (anything you write to it disappears)
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 5. row ***************************
				Engine: CSV
			  Support: YES
			  Comment: CSV storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 6. row ***************************
				Engine: MEMORY
			  Support: YES
			  Comment: Hash based, stored in memory, useful for temporary tables
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 7. row ***************************
				Engine: ARCHIVE
			  Support: YES
			  Comment: Archive storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		*************************** 8. row ***************************
				Engine: InnoDB
			  Support: DEFAULT
			  Comment: Supports transactions, row-level locking, and foreign keys
		Transactions: YES
					 XA: YES
		  Savepoints: YES
		*************************** 9. row ***************************
				Engine: PERFORMANCE_SCHEMA
			  Support: YES
			  Comment: Performance Schema
		Transactions: NO
					 XA: NO
		  Savepoints: NO

1.4、常见的存储引擎？

		Engine(事务): MyISAM
			  Support: YES
			  Comment: MyISAM storage engine
		Transactions（处理）: NO
					 XA: NO
		  Savepoints（保存点）: NO
		
		MyISAM这种存储引擎不支持事务。
		MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
		MyISAM采用三个文件组织一张表：
			xxx.frm（存储格式的文件）
			xxx.MYD（存储表中数据的文件）
			xxx.MYI（存储表中索引的文件）
		优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
		缺点：不支持事务。

	-----------------------------------------------------------------------------

			  Engine: InnoDB
			  Support: DEFAULT
			  Comment: Supports transactions, row-level locking, and foreign keys
		Transactions: YES
					 XA: YES
		  Savepoints: YES
			
		优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
		
		表的结构存储在xxx.frm文件中
		数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
		这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
		InnoDB支持级联删除和级联更新。
	
	-------------------------------------------------------------------------------------

				Engine: MEMORY
			  Support: YES
			  Comment: Hash based, stored in memory, useful for temporary tables
		Transactions: NO
					 XA: NO
		  Savepoints: NO
		
		缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
		优点：查询速度最快。
		以前叫做HEPA引擎。

事务和演示事务

2、事务（Transaction）

2.1、什么是事务？

	一个事务是一个完整的业务逻辑单元，不可再分。

	比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
		update t_act set balance = balance - 10000 where actno = 'act-001';
		update t_act set balance = balance + 10000 where actno = 'act-002';
	
	以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

	要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

2.2、和事务相关的语句只有：DML语句。（insert delete update）
	为什么？因为它们这三个语句都是和数据库表当中的“数据”相关的。
	事务的存在是为了保证数据的完整性，安全性。

2.3、假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？
	不需要事务。
	但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。

2.4、事务的特性？
	事务包括四大特性：ACID
	A: 原子性：事务是最小的工作单元，不可再分。
	C: 一致性：事务必须保证多条DML语句同时成功或者同时失败。
	I：隔离性：事务A与事务B之间具有隔离。
	D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

2.5、关于事务之间的隔离性
	事务隔离性存在隔离级别，理论上隔离级别包括4个：
		第一级别：读未提交（read uncommitted）
			对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
			读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
		第二级别：读已提交（read committed）
			对方事务提交之后的数据我方可以读取到。
			这种隔离级别解决了: 脏读现象没有了。
			读已提交存在的问题是：不可重复读。
		第三级别：可重复读（repeatable read）
			这种隔离级别解决了：不可重复读问题。
			这种隔离级别存在的问题是：读取到的数据是幻象。
		第四级别：序列化读/串行化读（serializable） 
			解决了所有问题。
			效率低。需要事务排队。
		
		oracle数据库默认的隔离级别是：读已提交。
		mysql数据库默认的隔离级别是：可重复读。

2.6、演示事务
	* mysql事务默认情况下是自动提交的。
		（什么是自动提交？只要执行任意一条DML语句则提交一次。）怎么关闭自动提交？start transaction;
	
	* 准备表：
		drop table if exists t_user;
		create table t_user(
			id int primary key auto_increment,
			username varchar(255)
		);
	
	* 演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。
		mysql> insert into t_user(username) values('zs');
		Query OK, 1 row affected (0.03 sec)

		mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		+----+----------+
		1 row in set (0.00 sec)

		mysql> rollback;
		Query OK, 0 rows affected (0.00 sec)

		mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		+----+----------+
		1 row in set (0.00 sec)
	
	* 演示：使用start transaction;关闭自动提交机制。
		mysql> start transaction;
		Query OK, 0 rows affected (0.00 sec)

		mysql> insert into t_user(username) values('lisi');
		Query OK, 1 row affected (0.00 sec)

		mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		|  2 | lisi     |
		+----+----------+
		2 rows in set (0.00 sec)

		mysql> insert into t_user(username) values('wangwu');
		Query OK, 1 row affected (0.00 sec)

		mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		|  2 | lisi     |
		|  3 | wangwu   |
		+----+----------+
		3 rows in set (0.00 sec)

		mysql> rollback;
		Query OK, 0 rows affected (0.02 sec)

		mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		+----+----------+
		1 row in set (0.00 sec)
		--------------------------------------------------------------------
		mysql> start transaction;
		Query OK, 0 rows affected (0.00 sec)

		mysql> insert into t_user(username) values('wangwu');
		Query OK, 1 row affected (0.00 sec)

		mysql> insert into t_user(username) values('rose');
		Query OK, 1 row affected (0.00 sec)

		mysql> insert into t_user(username) values('jack');
		Query OK, 1 row affected (0.00 sec)

		mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		|  4 | wangwu   |
		|  5 | rose     |
		|  6 | jack     |
		+----+----------+
		4 rows in set (0.00 sec)

		mysql> commit;
		Query OK, 0 rows affected (0.04 sec)

		mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		|  4 | wangwu   |
		|  5 | rose     |
		|  6 | jack     |
		+----+----------+
		4 rows in set (0.00 sec)

		mysql> rollback;
		Query OK, 0 rows affected (0.00 sec)

		mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		|  4 | wangwu   |
		|  5 | rose     |
		|  6 | jack     |
		+----+----------+
		4 rows in set (0.00 sec)

	* 演示两个事务，假如隔离级别
		演示第1级别：读未提交
			set global transaction isolation level read uncommitted;
		演示第2级别：读已提交
			set global transaction isolation level read committed;
		演示第3级别：可重复读
			set global transaction isolation level repeatable read;