数据库学习笔记之事务、索引

---

• 数据库事务(Database Transaction)：广泛应用于订单系统、银行系统等多种场景，很多同步数据库操作大部分需要用到事务，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。
• 1.A(atomicity):表示原子性，原子性中整个数据库事务是不可分隔的工作单位。只有使事务中的所有操作都执行成功，整个事务的执行才算成功。任何一个事务中的sql语句执行失败，那么已经成功执行的sql语句也必须撤销。
  2.C(consistercy):表示一致型。表示事务将数据库从一种状态转变成另一种一致的状态，在事务开始之前和结束之后，数据库的完整性约束没有被破坏
  3.I(isolation):隔离性，也叫并发控制.／可串行化/锁. 隔离性要求每个读写事务的对象与其它事务的操作对象互相分离。
  4.D(durability):持久性，表示事务一旦提交，就写到数据库，发生持久改变，

事务分类：

• 扁平事务
• 带有保存点的扁平事务
• 链事务
• 嵌套事务
• 分布式事务
  扁平事务：最简单、最常用的一种

//成功执行并提交
begin
sql1;
sql2;
...
commit[work]
//撤销
begin
sql1;
sql2;
....
rollback

保存点：

savepoint p1;//在事务中创建一个保存点，
rollback to savepoint p1;//把事务回滚到标记点重新开始执行，事务中p1之后的sql全被忽略
release savepoint p1;//删除一个事务的保存点。当没有指定的保存点被删除时会抛出异常。

• 带有保存点的扁平事务：除了支持扁平事务的操作外，允许在事务执行过程中国回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会到所有的操作都无效，放弃整个事务不太合理，开销也大，保存点用来通知系统应该记住事务的当前状态，一边以后发生错误能够回到该状态。
• 链事务：回滚时只能回滚到最近的一个保存点。
• 嵌套事务：在事务中嵌套事务

begin
sql1;
    sql2;
        sql3:
        ....
sqln;
commit;

开启事务：

start transaction 或　　begin [work]

提交并关闭事务

commit [work]

放弃并关闭事务，也叫回滚事务。指放弃当前事务，回到上一次事务的状态

rollback [work]

eaxmple：

create table dbtest(
id int)default charset=utf8;

//开启事务
begin;
//插入数据
insert into dbtest values(5);
insert into dbtest values(6);
//查看
select * from dbtest;
//回滚
rollback;
//再次插入数据
insert into dbtest values(5);
insert into dbtest values(6);
//提交
commit;

• 数据库事务隔离：

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
• 脏读: 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
• 不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
• 幻读: 对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.
数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱
设置事务的隔离级别:

set transaction:用来设置事务的隔离级别
innodb 存储引擎提供事务的隔离级别有：
InnoDB,是MYSQL的数据引擎之一，innodb的最大特色是支持ACID兼容的事务功能

引擎(engine)是电子平台上开发程序或系统的核心组件。利用引擎，开发者可以迅速建立，铺设程序所需要的功能那，或者利用其辅助程序的运行。一般而言，引擎就是一个程序或一套系统的支持部分，常见的程序引擎有游戏引擎，搜索引擎，杀毒引擎。
隔离级别

read uncommitted 未提交读
read committed  提交读
repeatable read   可重复读
serializable          可串行化

1.read uncommitted（未提交读）.在这个隔离的级别下，事务A对数据做的修改，即使没有提交，对事务Ｂ来说也是可见的，这种问题叫脏读。这是隔离程序较低的一种隔离级别，在实际运用中会引起很多问题，一次一般不常用。

2.read committed（提交读）。在这种级别下，事务A对数据的修改，提交之后才对事务B可见。

3.repeatable read（可重复读）.事务A对数据做了修改。提交之后，对于先前事务A开启的事务是不可见的。
注:提交读和可重复读的区别在于，前者在本事务未提交之前其他事务的增删改操作提交后会会影响读的结果，读的是最新的结果。
A B
mony=100 money=200;
mony=200
可重复读在读的过程中数据始终是事务启动时的数据状态，未提交之前其他事务的增删改操作提交之后不会影响读的结果

系统默认的隔离模式：repeatable read
查看系统工作隔离模式：
select @@tx_isolation;

4.serializble(可串行化)。可串行化是最高的隔离级别。这种隔离级别强制要求所有的事务串行执行。在这种隔离的级别下，读取每行数据都会加锁，会导致大量的征锁用问题，性能最差。

可以用set transaction 语句改变单个会话或者新进连接的隔离级别
格式：

set [session | global] transaction isolation level 
read uncommitted | read committed | repeat read | serializable
1.不带session,global的set命令只对下一个事务游戏
2.set session 为当前会话设置隔离模式
3.set global :为以后新建的所有的mysql连接设置隔离模式

例：
隔离模式：

1.read uncommitted:未提交读(脏读)
情景：A向B转100块钱
//建立数据表account 并插入数据
create table account(
id int,
name varchar(20),
money int)default charset=utf8;
insert into account(id,name,money) values(1,'A',1000),(2,'B',1000),(3,'C',1000);
//查看下当前隔离模式
select @@tx_isolation;
//设置当前的隔离模式为read uncommitted
set session transaction isolation level read uncommitted;
//打开新窗口B
select @@tx_isolation;
//设置隔离模式
set session transaction isolation level read uncommitted;
begin;
select * from account;
//进入A
update account set money=money+100 where name='B';
//进入Ｂ
select * from account;
//进入A
rollback;
//进入B
select * from account;

2.read committed:不可重复读。

3.repeatable read（默认）

4.serializable  串行化
  事务B的执行等待事务A的结束才可以

 mysql默认是自动提交的，也就是你提交一个语句，就直接执行。
 set autocommit =0 禁止自动提交
 set autocommit =1　开启自动提交

索引：
索引是一种特殊的文件，它们包含着数据表里所有记录的引用指针。通俗的将数据库的索引就是一本书的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部数据后选择符合条件的。而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。
注：一般数据库默认都会为主键生成一个索引。

索引的优点：
提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度。使用分组，排序子句进行数据查询是，同样也可以节省时间。
注:mysql添加索引后数据库的查询优化器会自动判断是否使用索引

索引的缺点：
创建和维护索引要耗费时间，索引需要占用物理空间。增加、删除和修改数据的时候，要动态维护索引，造成数据的维护速度降低了

索引分类：
1.普通索引
2.唯一性索引
3.全文索引
4.单列索引
5.多列索引
6.空间索引

索引的设计原则：
1.选择唯一性索引
2.为经常需要排序，分组或联合操作的字段建立索引
3.为常作为查询条件的列建立索引
4.限制使用索引的数目
5.尽量使用数据量少的索引
6.删除不再使用或者很少使用的索引。

创建索引
1.在创建表的时候创建索引
格式：

create table table_name(
列名１　数据类型[完整行约束], 
列名 2　数据类型[完整行约束]
...
列名n    数据类型[完整性约束]
[unique | fulltext | spatial ]  index | key  [别名]　（列名 [(长度)]　[asc | desc])
);
注：长度指列名这个列作为索引的字符数
例1：普通索引
create table index1(
id int,
name varchar(20),
sex varchar(20),
index(id));

例2:创建唯一性索引
在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为primary key或unique索引

create table index2(
id int unique,
name varchar(20),
unique index index2_id(id asc)
);

例3:创建全文索引
只能创建在char,varchar 或text类型的字段上，而且，现在只有MyISAM存储引擎支持全文索引
查看mysql支持哪些引擎

show engines;

create table index3(
id int,
info varchar(20),
fulltext index index3_info(info)
)engine=myisam;

例４：创建单列索引

create table index4(
id int,
subject varchar(30),
index index4_st(subject(10)) //索引时使用列的前１０个字符
);

例5：创建多列索引

create table index5(
id int,
name varchar(20),
sex char(4),
index index5_na(name,sex)
);

例6：创建空间索引

create table index6(
id int,
space geometry not null,
spatial index index6_sp(space)
)engine=myisam

2.在已经存在的表上创建索引
格式：

create [ unique | fulltext |spatial ] index 索引名 on 表名 (列名[(长度)] [asc | desc]);

3.alter table 语句来创建索引
格式：

alter table 表名　add [unique | fulltext | spatial] index 索引名(列名[(长度)][asc|desc]);

查看索引:

show index from table_name;
show keys from table_name;

删除索引:
一些不再使用的索引会降低表的更新速度，影响效率

drop index 索引名　on 表名
alter table 表名　drop index 索引名
alter table 表名　drop primary key ;//只在删除primary key索引时使用，因为一个表只可能有一个primary key索引，因此不需要指定索引名。如果没有primary key索引，但表具有一个或者多个unique索引，则mysql将删掉第一个unique索引。
例:
create table test(
id int not null default 0,
age int not null default 0,
score int not null default 0)default charset=utf8;

delimiter $$
drop procedure if exists pro1 $$
create procedure pro1(in num int)
begin
    declare i int default 0;
    while i<num do
    insert into test(id,age,score) values(floor(rand()*num),floor(rand()*num),floor(rand()*num));
    set i=i+1;
    end while;
end
$$ 
delimiter ;
 call pro1(50000);
  select id from test where age>=1000 and age<=3000;
alter table test add index index_num(id,age);
 select id from test where age>=1000 and age<=3000;