mysql堵塞等级_MySQL之DML,事务及隔离级别详解

DML之INSERT,UPDATE

INSERT INTO:插入数据 第一种: 1: INSERT INTO tb_name [(col1, col2,...)] {VALUES|VALUE} (val1, val2,...)[,(val21,val22,...),...]

第二种:

1: INSERT INTO tb_name SET col1=val1, col2=val2, ...

第三种:

REPLACE:有时候插入数据的时候,在不明情况的时候,对主键或者唯一键插入了一条相同的数据,就会拒绝数据插入,只是可以使用REPLACE将要插入的数据替换,用法同INSERT一样

UPDATE:更新数据,UPDATE在通常情况下,要使用WHERE或者LIMIT限制要修改的行数

1: UPDATE [LOW_PRIORITY] [IGNORE] table_reference2: SET col_name1=val1 [, col_name2=val2] ...3: [WHERE where_condition]4: [ORDER BY ...]5: [LIMIT row_count]

DELECT:删除某行数据,同上,在删除数据的时候是有那个WHERE或者LIMIT

1: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name2: [WHERE where_condition]3: [ORDER BY ...]4: [LIMIT row_count]

--safe-updates对UPDATE和DELETE都起作用......切记要使用这个啊...

如果目的是清空整张表,请使用TRUNCATE tb_name,不要使用DELECT..TRUNCATE可以清空自动增长的字段

或者把原表的属性复制出来,直接DROP原表.

MySQL简易的三层结构:

第一层:连接层,连接池.管理用户的连接,并发请求等关于连接的操作的.每种基于线程连接的程序都有一个线程池,MySQL也有一个线程池,线程池的大小决定了他的并发用户数,当线程池中的线程被用完了,后续的用户就必须排队,只有前一个用户占用的资源被释放了,后续的用户才能使用被释放的资源连接MySQL,MySQL的资源释放并不是销毁老的线程,创建新的线程,而是将被释放资源的线程清理干净,重新放回线程池,使用的是这种线程重用的机制.而对于用户连接进来的请求,MySQL有数据传输方式,一种是文本模式,一种是二进制格式,相对来说二进制的传输更为高效,但是两种都是明文传输.如果需要加密,需使用SSL协议.

第二层:核心功能层,MySQL的核心运作都在这一层.包括查询语句,查询优化,缓存视图.在做查询时MySQL会把用户输入的查询语句做切片,然后做词法分析,错误分析,选择最优的执行路径,而用户可以使用explain查看语句的执行顺序,这里的查询指的是广义上的查询并不是SELECT,如果是狭义的查询SELECT,MySQL中有一个查询缓存是专门为SELECT准备的,不论是什么方式的提高性能,都会有额外的开销,查询缓存也不例外,但是如果使用查询缓存换来的效率比开销大很多,那我们就认为这个缓存功能是有必要开启的,否则,建议关闭.而其中的一个评判标准就是缓存命中率.

第三层:存储引擎.真正负责存储和提取的.MySQL有很多存储引擎.后面介绍.

MySQL锁:

执行操作是施加锁的模式

读锁:共享锁,彼此间不阻塞

写锁:独占锁,排他锁,阻塞的

根据锁的范围分锁粒度:

表锁:锁定整张表,每个锁操作都会消耗资源,相对行锁,开销小,锁的粒度大,并发性低

行锁:锁定需要的行,可以是多行,开销大,锁的粒度小,并发性高

根据锁的实现位置分

MySQL锁:可以手动锁定(显示锁)

1: LOCK TABLES2: UNLOCK TABLES #释放锁的时候一次释放所有手动添加的锁

存储引擎锁:自动进行的(隐式锁)

注意:在生产环境中,如果不是备份数据,不建议手动施加锁

例:读锁

1: mysql> LOCK TABLES classes READ;2: Query OK, 0 rows affected (0.03 sec)3: #对classes表施加了读锁,当其他用户修改时会阻塞,自己修改时会提醒该表已被锁

写锁

1: mysql> LOCK TABLES classes WRITE;2: Query OK, 0 rows affected (0.08 sec)3: #当执行了写锁,其他用户读的时候会阻塞,自己可以读 行级锁1: mysql> ALTER TABLE classes ENGINE 'InnoDB';2: Query OK, 9 rows affected (0.17 sec)3: Records: 9 Duplicates: 0 Warnings: 04: #先改classes的存储引擎为InnoDB,MyISAM不支持行级锁.2: #这锁只在执行的过程中锁定,当语句执行结果后,自动释放锁事务:事务就是一组原子性的查询语句,也即将多个查询当作一个独立的工作单元

ACID测试:能满足ACID测试就说明器支持事务或兼容事务A:原子性,一个事务是不能分割的,要么都执行,要么都不执行

C一致性:一个一致性状态转到另外一个一致性状态

I隔离性:一个事务所做的修改在提交之前,对其他事务是不可见的

D:持久性:一旦事务提交了,其所做的修改会永远保留在数据库中

事务的目的是为是数据的安全,事务的安全性越高并发月底,事务如果完全隔离了,就成串行的了,所以事务的隔离有等级

隔离等级:

READ UNCOMMITTED:读未提交,安全型最差,但是并发最好,会产生脏读,读别人未提交的数据.

比如A修改了一个数据,还未点击提交,但是B可以看到A修改过后的数据,这就是脏读,可以读别人未提交的数据.脏读两次产生的结果不一样,所以脏读一定是不可重复读

READ COMMITTED:读提交,大多数数据库都是这个级别

比如A修改了一条数据,还未提交,B是看不到的,所以没有了脏读,但是A的两次查询结果是不一样的,所以是不可重复读

REPEATABLE READ:可重读,MySQL就是这个级别

比如A修改了一条数据,提交后,B是看不到的,B只有在自己也提交事务后才能看到,所以是可重读的.但是B之前明明看到有数据,自己未做任何修改,一提交,数据就没了,这就是幻读

SERIALIZABLE:串行化,强制事务的串行执行避免了幻读,性能很低

比如A修改了一条数据,只要未提交,B在查询该数据时会被阻塞,解决了幻读(以上3级都有幻读)

对可重读来讲,为什么提交了,别人还是看不到数据.这就是MVCC多版本并发控制,对于可重读来说,每个事务启动时,InnoDB会为每个启动的事务提供一个当下时刻的快照为了实现此功能，InnoDB会为每个表提供两隐藏的字段，一个用于保存行的创建时间，一个用于保存行的失效时间,只在两个隔离级别下有效：READ COMMITTED和REPEATABLE READ

建议:对事务要求不特别严格的场景下,可以使用读提交

跟事务相关的常用命令1: mysql> START TRANSACTION 启动事务2: mysql> COMMIT 提交事务3: mysql> ROLLBACK 事务回滚4: mysql> SAVEPOINT identifier 这就是回滚的保存点5: mysql> ROLLBACK [WORK] TO [SAVEPOINT] identifier 回滚到保存点注:MyISAM是并不支持事务的.在事务启动之后的所有如果没有显示启动事务,每个语句都会被当作一个独立的事务,器执行完成后会被自动提交,建议生产环境关闭此功能1: mysql> SELECT @@global.autocommit;2: mysql> SET GLOBAL autocommit = 0;

关闭自动提交,请手动启动事务,手动进行提交

查看MySQL的事务隔离级别

1: mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation';2: mysql> SELECT @@global.tx_isolation;