MySQL高级特性——约束、存储引擎、事务

MySQL

1、 约束

约束（constraint） 是在从创建表时，给表中的一些字段加上约束，保证表中数据的完整性、有效性。

1.1、 非空约束

Not Null约束

在建表时，字段后加上一个 not null ，即表示该字段不能为null

1.2、 唯一性约束

unique 约束

可以为null，可以重复加入null字段

1. 建表时加上唯一性约束

CREATE TABLE `t_user` (
      `Id` int(11) NOT NULL AUTO_INCREMENT,  -- 自增
      `name` varchar(18) NOT NULL unique,  -- 唯一性约束
      `password` varchar(18) NOT NULL,	--非空约束
PRIMARY KEY (`Id`)						--主键约束
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=utf8;

2. 给已经建好的表加上唯一性约束

   alter table 表名 add unique(字段名);
    
   栗子：alter table  user add unique(name) 
   

1.3、 主键约束

primary key（PK约束）

主键约束可以保持唯一性，但不能为null

1.4、 外键约束

foreign key（FK约束）

指向另一个表的主键列，用于预防破坏表连接的行为。

外键值也可以为null

2、存储引擎

存储引擎，在数据库底层，操作数据库的不同实现方式，就是不同的存储引擎，但最终呈现的数据库样式是一样的。

MySQL的默认存储引擎是InnoDB，其他常用的有MyISAM、Memory等

2.1、 InnoDB

mysql中的描述是：Supports transactions, row-level locking, and foreign keys

特点：

• 支持事务：支持4个事物隔离界别，支持多版本读。

• 行级锁定：更新时锁定当前行。

• 外键：支持外键索引，不支持全文索引。

主要业务场景：

        1.需要事务支持(具有较好的事务特性，例银行业务)
        2.行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成。
        3.数据更新较为频繁的场景，如：BBS(论坛)、SNS(社交平台)、微博等
		4.数据一致性要求较高的业务，例如：充值转账，银行卡转账。
    	5.硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO，可以通过一些参数来设置，这个就不细讲啦~~~
    
    	6.相比MyISAM引擎，Innodb引擎更消耗资源，速度没有MyISAM引擎快

InnoDB调优：

	    1.主键尽可能小，避免给Secondery index带来过大的空间负担。
	    
        2.避免全表扫描，因为会使用表锁。
        
        3.尽可能缓存所有的索引和数据，提高响应速度，较少磁盘IO消耗。
        
        4.在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交，有开关可以控制提交方式。
        
        5合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
        如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事		 务的时候不做任何操作。
        
        6.避免主键更新，因为这会带来大量的数据移动。

​

2.2、MyISAM

特点：

• 不支持事务
• 表级锁定
• 读写互相阻塞
• 占用资源较少，速度快
• 不支持外键约束，支持全文索引

3、 事务

事务指逻辑上的一组sql语句，执行时要么全部成功，要么全部失败。

3.1、四大特性（ACID）：

• 原子性（Atomicity）：
  • 事务不可分割，要么全部成功，要么全部失败。若中途错误，则会回滚到事务开始前的状态。
• 一致性（Consistency）：
  • 事务发生前和发生后，数据的完整性必须保持一致
• 隔离性（Isolation）：
  • 当并发访问数据库时，一个正在执行的事务在执行结束前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的。
• 持久性（Durability）：
  • 一个事务一旦被提交，它对数据库中的数据改变就是永久性的。

3.2、事务的隔离级别

• 读未提交：所有事务都可以看到未提交事务的数据。
• 读已提交：事务成功提交后才可以被查询到。 可避免脏读
• 可重复读：保证同一事务的多个实例并发读取数据时，在某一范围内所读到的数据项相同，但由于有可能在此过程中添加一个数据行，就会导致读取到“幻影行”，也就是所谓的幻读。可避免脏读和不可重复读
• 串行化：强制排序，并对每个数据行上添加共享锁。 可避免脏读、不可重复读和幻读

• 脏读：
  • 当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。（数据未提交就被读取）
• 不可重复读：
  • 是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（读取了已提交的数据项）
• 幻读：
  • 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样，幻读是数据行记录变多了或者少了。（读取了已提交的数据整体）

mysql 默认的隔离级别是可重复读。