Mysql中的隐式COMMIT以及Savepoints的作用以及MySQL的Innodb分空间存储、设计优化、索引等几个小知识点整理

一、Mysql中的隐式COMMIT以及Savepoints的作用

    Mysql默认是自动提交的，如果要开启使用事务，首先要关闭自动提交后START TRANSACTION 或者 BEGIN 来开始一个事务，使用ROLLBACK/COMMIT来结束一个事务。但即使如此，也并不是所有的操作都能被ROLLBACK，以下语句在执行后会导致回滚失效，比如DDL语句创建一个数据库，而且不止此，这样的语句包括以下这些等：

ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES，CREATE TABLE, CREATE DATABASE DROP DATABASE, TRUNCATE TABLE, ALTER FUNCTION, ALTER PROCEDURE, CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION和DROP PROCEDURE...

    这些语句（以及同义词）均隐含地结束一个事务，即在执行本语句前，它已经隐式进行了一个COMMIT。InnoDB中的CREATE TABLE语句被作为一个单一事务进行处理。所以ROLLBACK不会撤销用户在事务处理过程中操作的CREATE TABLE语句。另外上面的语句中包括START TRANSACTION，这即是说明事务不能被嵌套。事物嵌套会隐式进行COMMIT，即一个事务开始前即会把前面的事务默认进行提交。

    在这个页面 https://blog.csdn.net/qingsong3333/article/details/77018567 上看到这个例子，如下图：

        看图上说：是因为CREATE语句已经隐式地commit了。之后的语句都是自动提交的。我就感觉这里有问题，自己试了一下，果真是有问题，如下命令：

#执行SQL命令
set autocommit=0;
start transaction;
	create table teachers(id int  AUTO_INCREMENT, tname varchar(50), PRIMARY KEY (id) ); 
	INSERT INTO students(username) VALUES('lisi');
rollback;

#执行结果：
[SQL]set autocommit=0;
受影响的行: 0
时间: 0.001s
[SQL]
start transaction;
受影响的行: 0
时间: 0.000s
[SQL]
	create table teachers(id int  AUTO_INCREMENT, tname varchar(50), PRIMARY KEY (id) );
受影响的行: 0
时间: 0.171s
[SQL] 
	INSERT INTO students(username) VALUES('lisi');
受影响的行: 1
时间: 0.001s
[SQL]
rollback;
受影响的行: 0
时间: 0.126s

     上面的在执行过程中，ROLLBACK虽然不能撤回create table语句，但是数据插入行是会回滚的（表students数据未增加，但自增字段会增加1）。真不知道它的结论是怎么来的，误导人啊，真捉急！！真捉急！！

#. 总结：

        总之关于START TRANSACTION 和autocommit，

1.不管autocommit 是1还是0，START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚（不能回滚的DDL语句等除外）。
2.当autocommit 为 0 时，不管有没有START TRANSACTION。只有当commit数据才会生效，ROLLBACK后就会回滚。
3.如果autocommit 为1，并且没有START TRANSACTION。调用ROLLBACK是没有用的。即便设置了SAVEPOINT。

        上面谈到了设置SAVEPOINT， savepoint正如其字面意思，保存点，在事务中可以设定保存点，回滚的时候可以自由定义回滚至某个保存点，而不用一定要回滚到事务开始的时候的数据状态，官方介绍：保存点（savepoint）是事务过程中的一个逻辑点，用于取消部分事务，当结束事务时，会自动的删除该事务中所定义的所有保存点。当执行rollback时，通过指定保存点可以回退到指定的点。如下示例一看便懂。

set autocommit = 0;
start transaction;
	INSERT INTO students(username) VALUES('haha');
	SAVEPOINT tempa;
	INSERT INTO students(username) VALUES('haha_2');
	ROLLBACK TO SAVEPOINT tempa;
    #此处使用不使用RELEASE都可以，会自动删除
	RELEASE SAVEPOINT tempa;
COMMIT;

二、MySQL的Innodb分空间存储、设计优化、索引等几个小知识点记一下

备注：迁移时发现第二篇文章中可能有一些从其它文章中复制过来的成分，如作者认为侵权，请联系我删除。

1, mysql使用Innodb存储引擎时的存储优化

在mysql5.5及之上，Innodb是默认的存储引擎，也是MySQL推荐使用的存储引擎。其提供事务，行级锁定，外键约束的存储引擎，是一个事务安全型存储引擎，更加注重数据的完整性和安全性。但Innodb存储引擎默认是所有的innodb表的表空间文件都在同一个空间中，如ibdata文件（myisam数据索引分别存储于不同的文件中），这不利于数据存储\维护、迁移。可以通过配置innodb_file_per_table项，达到每张innodb表的数据和索引放在一个独立表空间文件里。
#默认为0，存放在一个文件中

> set global innodb_file_per_table =0;

#独立存放

> set global innodb_file_per_table =1;

2, Innodb和Myisam存储顺序区别

        关于Innodb存储引擎和Myisam引擎的一个插入的数据存储顺序区别。Innodb存储引擎插入的数据会按照主键顺序存储，即在插入的时候会做排序工作，所以插入效率较低。而Myisam存储方式，数据的存储顺序为插入顺序，不会去排序，便利插入速度极快，空间占用量小。在多年前我曾经做过一个开发工作，要将一大堆的数据连续插入数据库中，并且业务需求中这些数据是原始存储数据，不用再进行修改，当时被推荐使用Innodb引擎，但是在使用的时候我感觉插入速度很慢，特别是在数据量大了之后感觉越来越慢，之后我换成了Myisam引擎，插入效率极快。从这里也就能找到答案了。总之对于那些只是数据插入查询而很少进行数据更新删除的表，使用MYISAM引擎很合适。如果完全是不用更新删除的数据，可考虑使用Archive存档型存储引擎，其仅提供插入和查询操作。可非常高效地实现无阻塞的插入和查询。

3, Mysql数据库设计优化的几个小点

A，占用存储空间尽可能小，
    能用Tinyint不要用smallint，能用mediumint不要用int；字符串设计成varchar时尽量使用小N：Varchar(N)；日期格式使用Datetime, timestamp。
B,占用存储空间尽可能固定定长
    Char,varchar，Decimal（变长）, double(float)(定长)
C,尽可能使用整数：IPV4， int unsigned， varchar(15)，Enum，Set
D,多使用位运算。

4, 复习一下数据库设计要满足的3个范式和逆规范化

范式：Normal format，是一种离散数学中都知识，是为了解决一种数据的存储与优化的问题，保证数据的存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储，其终极目标是为了减少数据的冗余。

1NF：第一范式：字段原子性
        在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前，还需要额外的处理（拆分），那么说表的设计不满足第一范式，第一范式要求字段的数据具有原子性：不可再分。比如将商品的长宽高用逗号连接放在一个字段里，取出来时又要切开，则不符合第一范式。

2NF：第二范式：不允许出现部分依赖。
        数据表设计中如果有复合主键（多字段主键），而表中有字段并不是由整个主键来确定，而是依赖主键中的某个字段，称为部分依赖。第二范式就是不允许出现部分依赖。比如有两张表学生表和课程表，另外一张学生选课表中会有学生的名称或ID，但是学生课程表中再出现学生的性别的话，就出现了部分依赖。

在2NF的基础上的3NF：第三范式：无传递依赖
        理论上讲，应该一张表中的所有字段都应该直接依赖主键（逻辑主键：代表的是业务主键。无传递依赖），如果表设计中存在一个字段，并非直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键，把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖。在上面的2NF的问题中，解决方法除了拆表外，还能添加取消复合主键，使用逻辑主键（比如自增ID）来实现满足2NF，但是这样的后果就是出现传递依赖，即学生性别依赖学生，学生依赖主键。

        逆规范化：有时候，在设计表的时候，如果一张表中有几个字段是需要从另外的表中去获取信息，理论上讲，的确可以获取到想要的数据，但是就是效率低一点，会刻意的在某些表中，不去保存另外一张表的主键（逻辑主键）而是直接保存想要的数据信息，这样一来，在查询数据的时候，一张表可以直接提供数据，而不需要多表查询（效率低），但是会导致数据冗余。

5, MYSQL其它：

1，select查询用不到任何索引，但如果order by排序需要的字段上存在索引，也可能使用到索引。
2，复合索引会覆盖首字符单独索引！即避免重复索引。
3，索引的多关键字内容，覆盖了查询所select的全部数据，此时就不需要在数据区获取数据，而仅仅在索引区取内容即可。查询的时候避免全量select *.
4, 建立索引索引时，不要仅仅考虑where检索，同时考虑其他的使用场景。在所有的where字段上增加索引不合理
5, 要使用某个字段的索引，sql中要保证字段独立在一侧。而不能使用 age-1>10 这种。
6, Like查询左原则：匹配模式的左边必须确定，而不能用通配符。 像like %..% 需要使用全文索引。
7，MYSQL弃用索引：查询即使使用索引，也会导致出现大量的随机IO，甚至比全部顺序遍历IO开销还要大,则MYSQL会智能选择弃用索引。