SQL-2

SQL基础

并发事务与锁机制

一个事务往往是需要多条语句共同组成，来完成较为复杂的数据操作。
在MySQL数据库中，事务是进行数据管理的基本操作单元，锁机制是用于实现并发控制的主要方法。
事务本身必须具有以下4个特性。

1. 原子性：每个事务都必须被看作一个不可分割的单元。假设一个事务由两个或多个任务组成，其中的语句必须同时成功才能认为整个事务是成功的。
2. 一致性：事务执行完成后，都将数据库从一个一致状态转变到另一个状态，不能违背数据库的任何完整性检查。
3. 隔离性：每个事务在自己的会话空间发生，和其他发生在系统中的事务隔离。事务的结果只有在完全执行后才能看到。
4. 持久性：事务一旦提交，对数据库的修改将是持久的。

事务分类

1. 自动提交事务：默认情况下，MySQL采用autocommit模式运行。当执行一个用于修改表数据语句后，MySQL会将结果存储到磁盘中。如果没有定义事务，MySQL会自己定义事务。每个语句在完成时，都被提交或回滚。如果一个语句成功地完成，则提交该语句。如果遇到错误，则回滚。
2. 用户定义事务：显式事务是指显式定义了启动和结束的事务。
3. 分布式事务：一个比较复杂的环境，可能有多台服务器，要保证在多服务器环境中事务的完整性和一致性，就需要分布式事务。在分布式事务中，所有的操作都可以设计对多个服务器的操作，当这些操作都成功时，这些操作都会提交到相应的服务器的数据库中，如果存在一条失败，那么全部取消。

事务管理

MySQL中，当会话开始时，自动提交功能是打开的，@@autocommit=1，用户执行SQL语句后，就会被立即提交。
关闭自动提交：
set @@autocommit=0;

1. 启动事务：
   第一条SQL语句或者在commit或rollback语句后的第一条SQL执行后，新的事务就开始了。也可以显示地启动一个事务。
   start transaction | begin work
2. 结束事务：
   commit提交语句，使事务开始后所执行的所有数据修改成为数据库的永久部分，也标志一个事务的结束。
   注意：MySQL是平面事务模型，所有嵌套的事务不允许。在第一个事务使用start transaction后，第二个事务开始时，自动提交第一个事务。
3. 回滚事务
   rollback回滚语句，回滚事务所做的修改，并结束当前事务。
   rollback work;
4. 设置事务检查点
   rollback to语句使事务回滚到某个点，实现事务部分回滚。需要使用savepoint来设置一个保存点。
   savepoint identifier
   identifier为保存点的名称
   rollback to savepoint会向保存点回滚一个事务。
   rollback to savepoing identifier
   当回滚到某个保存点后，该保存点后设置的保存点将被删除。
   释放保存点：
   release savepoint identifier
5. 隐式关闭自动提交
   start transaction可以隐式关闭自动提交,不会修改@@autocommit，
   例：

   delimiter //
   create procedure update_cno()
   begin start transaction;
   update course set cname='高等数学' where courseno='c3333';
   commit;
   select * from course where courseno='c3333';
   end//
   delimiter ;
   call update_cno();

6. 运用事务保存点
   向course添加一条记录，设置保存点，删除该记录，然后回滚，提交，可以发现没有删除:

   delimiter //
   create procedure po_in()
   begin start transaction; insert into course values('c1111','111','111','111');
   savepoint spcno1;
   delete from course where courseno='c1111';
   rollback work to savepoint spcno1;
   select * from course where courseno='c1111';
   commit;
   end//
   delimiter ;

事务并发处理

MySQL中，并发控制通过锁来实现。保证同一时刻执行多个事务时，一个事务的执行不能被其它事务干扰。

并发问题影响

多个用户访问同一个数据库时，如果数据存储系统没有并发控制，就有并发问题。如果不加以控制，就有可能产生数据不一致问题。
数据库并发操作导致数据库不一致主要有4种：更新丢失，不可重复性，脏读，幻读数据。而且也有可能死锁。

1. 更新丢失：多个事务选择同一行更新，每个事务不知道其他事务的存在，最后的更新将覆盖其他事务，可能导致数据丢失。
2. 脏读：即读出的不正确的临时数据。
3. 不可重复读：同一个事务内两条相同的查询语句，查询结果不一致。
4. 幻读：当对某行执行插入或删除操作，而该行属于某事务正在读取的行的范围时，就会出现幻读。
5. 死锁：如果两个用户并发访问数据库，互相等待对方的数据，就会产生死锁。

设置事务隔离级别

SQL标准定义了4种隔离级别：
read uncommitted(读取未提交的数据) 隔离级别低：
所有事务都可以看到其他未提交事务的执行结果，该隔离级别很少用于实际应用。
read committed(读取提交的数据)：
大多数数据库系统默认隔离级别（不是MySQL的），满足隔离简单定义，一个事务只能看见已提交事务所做的改变。
repeatable read(可重复读)：
MySQL默认事务隔离级别，确保同一事务内相同的查询语句执行结果一致。
serializable(串行化) 隔离级别高：
通过强制事务排序，使之不可能相互冲突。换言之，在每条select语句后加上lock in share mode，为每个查询操作加一个共享锁。
这个级别，可能导致大量的锁等待现象。该隔离级别主要用于InnoDB存储引擎的分布式事务。
 
低级别的事务隔离可以提高事务的并发访问性能，可能导致较多的并发问题。
高级别的事务隔离可以有效避免并发问题，但会降低事务的并发访问性能，可能导致出现大量锁等待，甚至死锁现象。
查询当前隔离级别：
select @@tx_isolation
设置：set session transaction isolation level read uncommitted;

管理锁

MySQL通过锁来防止数据并发操作过程中引起的问题。它是实现并发控制的主要方法，是多个用户能够同时操作同一个数据库中额数据而不发生数据不一致性现象的重要保障。
MySQL通过不同类型的锁来管理多用户并发访问，实现数据访问的一致性。

锁机制基本概念

1. 锁的粒度：指锁的作用范围。锁的粒度可以分为服务器级锁和存储引擎级锁。MyISAM支持表锁，InnoDB支持表锁以及行级锁。
2. 隐式锁和显式锁：自动加锁叫隐式锁，手动加锁称为显式锁。
3. 锁的类型：读锁(共享锁)和写锁(独占锁)。读锁允许其他用户对数据同时读，不允许写。写锁不允许读也不允许写。
4. 锁的钥匙：多个用户并发访问同一数据时，如果用户A对该数据成功加锁，那么只有A拥有钥匙，只有A能够对锁进行解锁。
5. 锁的生命周期：指在同一个MySQL服务器连接内，对数据加锁到解锁之间的时间间隔。

锁定和解锁

1. 锁定表：
   表锁定支持以下类型：read锁，write锁：
   lock tables score read;
   lock tables course write;
2. 解锁：
   unlock tables;
    
   在对事务表使用表锁定需要注意：在锁定表时会隐式地提交所有事务，在开始一个事务时，start transaction会隐式解开所有表锁定。系统变量@@autocommit必须为0，否则，MySQL会在调用lock tables之后会立即释放表锁定，很容易形成死锁。

锁的分类

MySQL中3种锁定机制

1. 表锁：一个特殊类型的访问，整个表被客户锁定。根据锁定的类型其他用户不能写数据，甚至不能读数据。表锁有两种：读锁和写锁。
2. 行锁：行级锁比表级锁或页级锁对锁定过程提供了更精细的控制。在这种情况下，只有线程使用的行是被锁定的。表中的其他行对于其他线程都是可用的。行级锁并不是由MySQL提供的锁定机制，而是由存储引擎自己实现。InnoDB的锁定机制就是行级锁定。行级锁定的类型包括3种：排他锁，共享锁，意向锁。

• 排他锁(X锁)：如果事务T获得了数据行D上的排他锁，则T对数据行既可读又可写。事务T对数据行D加上排他锁，则其他事务对数据行D的任务封锁请求都不会成功，直至事务T释放数据行D上的排他锁。
• 共享锁(S锁)：如果事务T获得了数据行D上的共享锁，则T对数据行可以读，不能写。事务T对数据行D加上共享锁，则其他事务对数据行D的排他锁请求不会成功，对数据行D的共享锁请求可以成功。
• 意向锁：是一种表锁，锁定粒度是整张表，分为意向共享锁，意向排他锁。意向锁表示一个事务有意对数据上共享锁或排他锁。
  • 意向共享锁：事务打算给数据行加共享锁，事务在取得一个数据行的共享锁之前必须先取得该表的意向共享锁。
  • 意向排他锁：事务打算给数据行加排他锁，事务在取得一个数据行的排他锁之前必须先取得该表的意向排他锁。

InnoDB表的行锁是通过对索引施加锁的方式实现的，这就是说，只有通过索引字段检索数据的查询语句或者更新语句，才可能施加行级锁。否则InnoDB将使用表级锁，会降低表的并发访问性能。
3. 页锁：页级锁开锁和加锁时间介于表级锁和行级锁之间，会出现死锁，锁定粒度介于表级锁和行级锁之间。
上述三种锁的特性可大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

分表分库

什么是分表分库？

就是把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。

为什么要分库分表？

数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大。
数据操作的开销也会越来越大。而且一台服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。

分表

就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户 id 来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内。

分库

一般数据库最多支撑到并发 2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒 1000 左右。所以需要将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

垂直切分

把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。
将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数据库workDB、商品数据库payDB、用户数据库userDB、日志数据库logDB等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表等。

水平切分

把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来扛更高的并发，还有就是用多个库的存储容量来进行扩容。

分库分表中间件

• sharding-jdbc：属于 client 层方案。 SQL 语法支持也比较多，没有太多限制。持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。
• mycat：属于 proxy 层方案，支持的功能非常完善，相比于 sharding jdbc 来说，年轻一些，经历的锤炼少一些。
• 区别：
  • sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合 sharding-jdbc 的依赖。适合中小型公司。
  • mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。适合大型公司

应用

如果数据库是因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰、低耦合，那么规则简单明了、容易实施的垂直切分必是首选。
如果数据库中的表并不多，但单表的数据量很大、或数据热度很高，这种情况之下就应该选择水平切分。

分库分表的问题

• 事务问题：数据存储到了不同的库上，事务管理出现困难。
• 跨库跨表的join问题：执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上。无法join位于不同分库的表，可能需要多次查询才能完成。
• 额外的数据管理负担和数据运算压力：比如查看前100条数据，分表前只需一个order by，分表后需要查出每个分表的前100，然后再合并整理。