mysql(一)：mysql的一些基础常识

2018-12-18 本文已影响92人骑着母猪砍大象

转载请注明作者和出处：https://www.jianshu.com/p/4bfbe89c9c1f

作者简介： 一个本该成为游戏职业选手却被编程耽误的程序员

线程

mysql是线程级别的，每个客户端连接会在服务器进程中拥有一个线程，该线程只能轮流在某个cpu核心或者cpu中运行，服务器只会缓存线程，因此不会为每个新的链接创建新的线程或者销毁线程

锁

mysql的锁从功能上分为共享锁（shared lock）和排他锁（exclusive lock），也可以称为读锁（read lock）和写锁（write lock），顾名思义，读锁的是多个用户在读取同一个资源，互不干扰，写锁是排他的，也就是说一个写锁会阻塞其他写锁和读锁，也就是说，在一个用户（一个链接）执行写锁的时候，其他用户处于等待状态。
注意，锁的各种操作都是需要消耗资源的，检查锁是否解除，释放锁，都会增加系统的开销。所以锁的数量和数据的安全性在大型项目中需要一个中间平衡点。

锁的粒度

锁的粒度在不同的存储引擎表现是不一样的。
表锁（table lock）顾名思义，就是锁住整张表，其他连接不能进行任何操作，是开销最小的锁。
行锁（table lock）顾名思义，就是锁住修改的数据，其他连接不能进行任何操作，是开销最大的锁。

ACID的概念

原子性（atomicity）：不可切割的最小工作单元，一个事务一旦开始，要么就不做，要么全做，不可能停在中间环节，中间环节出错的话，会出现回滚成事务开始的状态，所有的sql语句就像没发生过一样；
一致性（consistency）：要么一起成功，要么一起失败；
隔离性（isolation）：一个事务在最终修改之前，其他事务是不可见的，并发执行的时候与其他事务相互隔离
持久性（durability）：也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。简单来说就是修改会保存到真正的数据库中

隔离级别

Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：
脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

死锁

指两个以上的事务在统一资源上相互占用，并请求锁定对方的资源，从而导致恶循循环的现象

上面都是一些理论知识，我们来看看一些实际的例子

死锁

事务1

START TRANSACTION
UPDATE USER SET name = '张三' WHERE id = 1;//A
UPDATE USER SET name = '李四' WHERE id = 2;//B
COMMIT;

事务2

START TRANSACTION
UPDATE USER SET age = 100 WHERE id = 2;//C
UPDATE USER SET age = 101 WHERE id = 1;//D
COMMIT;

我们看两个例子，正常来说这ABCD四个例子相互之间互不干扰，可是巧了，正好A和C同时执行完语句，同时也锁定住了这条数据，双方在准备执行下一条语句的时候发现B和D都被锁住了，于是等啊等，都需要对方等待释放锁，同时又持有对方所需要的锁，陷入了死循环，这就是死锁，目前市面上常用的InnoDB处理死锁的办法就是将最少行级的排他锁释放回滚，这也是相比较而言比较简单，大多数存储引擎所考虑的的算法

隔离级别

创建数据库

CREATE TABLE test( id int unsigned AUTO_INCREMENT, name varchar(100) not null, age int(2) not null, primary key(id) )engine=innodb default charset=utf8;

插入测试数据

insert into test (age,name) values(12,'老王'),(20,'老李');

数据截图

--- --- --- --- --- --- --- --- -- --- --- --- --- --- --- --- --- --- 帅气的分割线--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---