mysql的存储引擎-事务-锁

第一层：
mysql 服务支持接口，也就是数据库访问技术，标准C的API，JDBC ，ODBC，NET，PHP，Python，等等
第二层：
mysql启动连接池，默认的连接池是150个，具体多少取决于上层web服务开多少连接。

第三层：
mysqlSQL接口，数据库管理语言，数据库定义语言，视图，触发器等等，接收工作台输入的SQL语句命令。

解析器，就是解析SQL语句的，查询，事务功能。

优化器，访问路径，统计，优化SQL语句。

缓存和缓冲池，全局和引擎的缓存和缓冲池相连。加快搜索速度。

第四层：

存储引擎。InnoDB MyISAM，Falcon，Archive 等等。

第五层：

存储引擎下层是硬盘，文件系统，日志等等

存储引擎：
调用在文件系统上的数据，然后用sql进行操作。存储引擎将内存中的操作结果等，存入硬盘。

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

看到所有的存储引擎
DEFAULT 当前mysql默认使用的存储引擎
YES 当前mysql可以使用这种存储引擎
NO 当前mysql无法使用

mysql 5.5.08 以前，默认的存储引擎是 MyISAM。以后，默认的存储引擎是 InnoDB。

MyISAM:
	适用于读频率远远大于写频率的应用场景。
	查询的效率相当高，而且占用内存很小。

	表的保存方式：
		.frm 表的框架
		.MYD 表的数据
		.MYI 表的索引

	缺点：
		不支持事务操作
		也不支持外键

InnoDB：
	为了处理巨大的数据量，如果表的数据量很大，选择使用innodb。

	提供了良好的事务处理，针对数据崩溃有很好的修复机制

	缺点：读写的效率差。
		
	表的存储方式：
		innodb存储引擎使用表空间存储数据。

		独立表空间：每个表都有自己的表空间文件
			.frm 表的框架结构
			.ibd 表的独立表空间文件
		共享表空间：所有的表共享使用同一个表空间文件
			.frm 表的框架结构
			ibdata1 所有表的共享表空间文件
			
表空间：是数据库里最大的逻辑单位，保存表数据
	一个表空间可以包含一个或多个数据文件
	一个数据文件只能属于一个表空间

切换独立表空间和共享表空间

mysql> show variables like "%per_table%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

mysql> set global innodb_file_per_table=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%per_table%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.01 sec)

这时创建的表就没有.ibd，独立表空间文件

因为整个数据库的数据都存储在ibdatal文件，共享表空间文件中了。

**注意：**默认为独立表空间文件，改为共享表空间文件后，已经创建的表不会改变，新创建的表才会没有ibd文件，存到ibdatal文件中。如果这时切换回独立表空间模式，已经是共享创建的也不会变回独立表空间。

当然，在这里改的话并不会永久生效，退出连接，再重新连接，就没有了，想要永久生效的话要填写配置文件。

[mysqld]
innodb-file-per-table=[01]
	0	共享
	1	独立，默认的
重启服务生效

memory
也叫：内存存储引擎
速度快。
缺点：
服务一重启，或虚拟机重启。
数据全部丢失。

mysql> use gsc1
mysql> create table t999(id int ,name char(10))engine=memory;
mysql> show tables;
+----------------+
| Tables_in_gsc1 |
+----------------+
| passwd         |
| shop           |
| t1             |
| t999           |
+----------------+
mysql> insert into t999 values (111,"name1");
mysql> select * from t999;
+------+-------+
| id   | name  |
+------+-------+
|  111 | name1 |
+------+-------+
mysql> exit
Bye

[root@localhost scripts]# netstat -antlup | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      6927/mysqld         
[root@localhost scripts]# kill 6927
[root@localhost scripts]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

mysql> use gsc1;
mysql> show tables;
+----------------+
| Tables_in_gsc1 |
+----------------+
| passwd         |
| shop           |
| t1             |
| t999           |
+----------------+
mysql> select * from t999;
Empty set (0.00 sec)
//重启服务数据丢失。

事务：
事务就是做的操作，哪怕做了1000个操作完成一件事，都叫一个事务。

特性：
A：atomic：原子性；对数据的修改，要么执行，要么不执行
C：consistency：一致性；事务在开始和结束时，数据必须保证是一致的状态
I：isolation：隔离性；事务的操作不允许外界干扰
D：duration：持久性；事务要一直执行到最后

不过默认情况下，事务是自动提交的，所以我们输入命令后即时生效，并且不能回滚。

mysql> select * from t8;
+------+-------+
| id   | name  |
+------+-------+
|  111 | kaili |
|  112 | kitty |
+------+-------+
2 rows in set (0.00 sec)

mysql> delete from t8 where id=111;
Query OK, 1 row affected (0.02 sec)

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

mysql> select * from t8;
+------+-------+
| id   | name  |
+------+-------+
|  112 | kitty |
+------+-------+
1 row in set (0.00 sec)

回滚也不生效。

可以手动关闭自动提交来进行测试。

mysql> set autocommit=off;
//关闭自动提交，变为手动提交

关闭自动提交后需要手动提交才会生效，否则就只在当前shell生效。

比如上图，添加了一条数据，当前端口也能够看到，但是另一个端口就不能看到了。

提交后再查就能看到了。commit提交。

事务的回滚，rollback；

另外有一些刺头非常的牛X，他们不受事务操作的约束。就是DDL语句。
这些不管事务提没提交或者怎么样，都直接生效。

DDL语句
	create
	drop
	alter
	truncate

右边的端口删除表，没提交，左边的也没有了。。

另外，我们并不是这么用事务的，常用的是spring的事务机制，在开发的过程中，总会遇到bug或者报错，但是报错之前，确是能正常执行的。

public method() {
    Dao1.save(People1);
    Dao1.save(People2);
    Dao1.save(People3);//这里
    Dao1.save(People4);
}

这里调用的是存储方法，向数据库里写数据。
假如Dao1.save(People3);执行报错，但是前边的都正常，造成的结果就是，前两条添加了，第三第四不添加，但是我们想要的逻辑确是这几条都不添加。

就拿银行取钱举例，账户扣钱正常，ATM出钱报错不执行，你是希望账户扣钱，还是不扣钱？？当然是不扣钱。

这时我们可以用spring的事务回滚机制来解决这个问题。

用@ Transaction 注解在对应方法上声明为一个事务方法。
这个在这里就不讨论了。

锁：
锁就是再多并发的情况下保证数据库的数据安全和完整的机制。让访问数据库的接口有序的进行操作。

按照粒度分类：

表级锁：

直接锁定整个表，在上锁以后，其他用户操作该表会受限。
锁定的粒度大，发生冲突的概率比较高，并发性差。

行级锁：

只锁定操作的那行，表中的其他行不受影响。
锁定的粒度小，几乎不会产生冲突，并发性高。
有可能出现死锁。
mysql5.7以后，会自动解开死锁。

这个是原始数据。

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | name11 |
|    1 | name22 |
+------+--------+
2 rows in set (0.00 sec)

在上图可以看到，开了两个终端，都关闭了自动提交，左边的 终端修改了，name11这个数据，没有提交。 右边的终端再修改这个name11，这时发现进行不下去，会卡住。证明锁生效了。

到一定时间或者左边的终端提交了。
右边的终端就会结束，并报错，此时没有做修改。

mysql> update t1 set name="name11aaaa" where name="name11";
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
//提交之后。
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | name11aa |
|    1 | name22   |
+------+----------+
2 rows in set (0.00 sec)
//看到修改的数据。

死锁：

终端1：
mysql> update t2 set b="niu111" where name="name11aa";

终端2：
mysql> update t2 set b="niu22" where name="name22";

终端1：
mysql> update t2 set b="niu222" where name="name22";

终端2：
mysql> update t2 set b="niu123" where name="name11aa";
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
	自动解开死锁

这个失策 了，id应该设成主键的，那样就不用写这些乱七八糟的name了。QAQ

按类型分：

读锁（共享锁）：
当上了读锁时任何人不能进行写操作。
在做备份时会使用读锁。

写锁（排它锁）：
当给表加写锁后，只有自己可以对表进行读写，其他用户没有任何权限。

mysql> lock table t2 read;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> insert into t2 values (3);
ERROR 1099 (HY000): Table 't2' was locked with a READ lock and can't be updated

mysql> insert into t1 values (12,"name33");

ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES


mysql> unlock tables;
//解锁

如果某个终端使用lock table tname read / write锁定某个表。

那么同一个终端中只能对锁定的表进行查询操作，不能对锁定的表进行修改、插入、删除，不允许对没有锁定的表进行查询、修改、插入、删除。

其他终端对锁定的表只能查询，不能进行修改，插入，删除操作。

其他终端对没有锁定的表仍然可以进行查询、修改、插入、delete操作。

mysql> lock table t3 write;
	给表加写锁

mysql> insert into t3 values(3);
mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

其他用户：
mysql> select * from t3;
mysql> insert into t3 values(4);
	被阻塞

查看表的上锁情况：

mysql> show open tables where in_use>0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| db400    | t1    |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.01 sec)