mysql数据库索引，事务

索引

概念

1，索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。
2，使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表来找到该行数据对应的物理地址然后访问相应的数据，可以加快数据库的查询速度
3，索引是表中一列或者若干列值排序的方法
4，索引可以加快对表中数据的查询，还可以对表中数据排序。

作用

1，设置了合适的索引之后，能够利用各种快速定位技术，大大提高查询速度。
2，当涉及到多表联查时，索引能大大提高查询速度
3，能降低数据库的IO成本，而且还能降低数据库的排序成本
4，可以创建唯一索引来确保数据的唯一性
5，能减少分组和排序的时间

副作用

1，索引需要占用额外的磁盘空间
2，在插入和修改数据时，需要花费大量的时间。

创建索引的原则依据

虽然索引能大幅度提高查询速度，但也会消耗系统资源。
所以索引要适当使用
1，表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位。
,2，记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。
3，经常与其他表进行连接的表，在连接字段上应该建立索引。
4，唯一性太差的字段不适合建立索引。
5，更新太频繁地字段不适合创建索引。
6，经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
7，索引应该建在选择性高的字段上。
8，索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

索引的分类和创建

1，普通索引：最基本的索引类型，没有唯一性之类的地限制

直接创建
create index 索引名 on 表名 （列名）；

修改表方式创建
alter table 表名 add index 索引名 （列名）

创建表的时候制定索引
create table 表名 （字段1 数据类型，，，，index 索引名 （列名））；

2，唯一索引：唯一索引的值具有唯一性，但允许有空值，添加唯一键将自动创建唯一索引

直接创建
create unique index 索引名 on 表名（列名）；

修改表方式创建
alter table 表名 add unique 索引名 （列名）；

创建表时创建
create table表名 （字段 数据类型，，，unique 索引名（列名））；

3，主键索引：特殊的索引，必须指定为primary key。一个表只有一个主键，且不允许有空值。添加主键时将自动创建主键索引。

创建表时指定主键
create table 表名 （字段1 数据类型 ，，，primary key （列名））；

修改表方式创建
alter table 表名 add primary key （列名）；

4，组合索引：可以是单列，也可以是多列。select语句从左到右执行，所以要满足最左原则

创建表时创建组合索引
create table 表名 （列名1 数据类型，，，，index 索引名（列名1，列名2））

5，全文索引：适用在进行模糊查询时使用。

直接创建
create fulltext index 索引名 on 表名 （列名）；

修改表方式创建
alter table 表名 add fulltext 索引名 （列名）；

创建表时创建索引
create table 表名 （字段1 数据类型，，，fulltext 索引名 （列名））；

使用全文索引查询
select * from 表名 where match（列名） against（‘查询内容’）；

6， 查看索引

show index from 表名；
show keys from 表名；

7，删除索引

直接删除索引
drop index 索引名 on 表名；

修改表方式删除索引
alter table 表名 drop index 索引名；

删除主键缩影
alter table 表名 drop primary key；

事务

概念

1，是一种机制，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求。即这一组数据库命令要么都执行，要么都不执行
2，是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
3，适用于多用户同时操作的数据库系统场景。
4，通过事务的整体性以保证数据的一致性。

事务的ACID特点

原子性：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生，
1，事务是一个完整的操作，事务的各元素是不可再分的
2，事务中的所有元素必须作为一个整体提交或是回滚
3，如果事务中的任何元素失败，则整个事务将失败

一致性：指在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏
1，当事务完成时，数据必须处于一致的状态
2，在事务开始前，数据库中存储的数据处于一致状态
3，在正在进行的事务中，数据可能处于不一致的状态
4，当事务成功完成时，数据必须再次回到已知的一致状态

隔离性：指在开发环境中，当不同事务同时操纵相同的数据时，每个事务都有各自的的完整数据空间
1，对数据进行修改的所有事务是彼此隔离的，表名事务必须是独立的的，它不应以任何方式依赖于或影响其他事务。
2，修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

持久性：在事务完成之后，该事务所对数据库所做的更改便持久的保存在数据库之中，并不会回滚
1，指不管系统是否发生故障，事务处理的结果都是永久的
2，一旦事务被提交，事务的效果会被永久的保留在数据库之中

事务之间的相互影响

1，脏读：一个事务读取了另一个事务未提交的数据，而这个数据有可能会回滚。
2，不可重复读：一个事务内两个相同的查询却返回了不同的数据。这是由于查询是系统中其他事务修改的提交而引起的。
3，幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新的数据。那么，操作前一个事务的用户会发现表还存在没有被修改的数据行。即同时在对表修改数据。
4，丢失更新：两个事务同时读取同一条记录，先后对数据进行修改，先修改的被后修改的数据覆盖掉。

mysql事务隔离级别

1，read uncommitted ：读取尚未提交的数据：不能解决脏读
2，read committed ：读取已经提交的数据：能解决脏读
3，repeatable read ：重读读取：能解决脏读和不可重复读
4，serializable ：串行化：能解决脏读，不可重读，幻读

查询全局事务隔离级别
show global variable like ‘%isolation’；
select @@global.tx_isolation;

查询会话事务隔离级别
show session variable是 like “%isolation”；
select @@session.tx_isolation;
select @@tx_isolation;

设置全局事务隔离级别
set global transaction isolation level read committed；

设置会话事务隔离级别
set session transaction isolation level read committed；

总结：在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果

事务控制语句

BEGIN 或 START TRANSACTION：显式地开启一个事务。
COMMIT 或 COMMIT WORK：提交事务，并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
SAVEPOINT S1：使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 SAVEPOINT；“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点。

使用set设置控制事务

SET AUTOCOMMIT=0;						#禁止自动提交
SET AUTOCOMMIT=1;						#开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';		#查看Mysql中的AUTOCOMMIT值

如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。

如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。

当然无论开启与否，begin; commit|rollback; 都是独立的事务。