MYSQL索引和事务

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

索引的作用

设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建所有的最主要的原因。

当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。

可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。

通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。

可以加快表与表之间的连接。

在使用分组和排序时，可大大减少分组和排序的时间。

索引的原则依据

设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建所有的最主要的原因。

当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。

可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。

通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。

可以加快表与表之间的连接。

在使用分组和排序时，可大大减少分组和排序的时间。

直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

修改表方式创建索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

唯一索引的创建

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

主键索引

CREATE TABLE 表名 ([…],PRIMARY KEY (列名));

全文索引（FULLTEXT）

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

删除索引

直接删除：DROP INDEX 索引名 ON 表名;
修改表方式删除：alter table member drop id_index;

MySQL事务

1、事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这组数据库命令要么都执行，要么都不执行。

2、事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。

3、事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。

4、事务通过事务的整体性以保证数据的一致性。

事务的ACID特点

A：
原子性：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

事务是一个完整的操作，事务的各元素是不可分的。事务中的所有元素必须作为一个整体提交或回滚。
如果事务中的任何元素失败，则整个事务将失败。

C：
一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

当事务完成时，数据必须处于一致状态。
在事务开始前，数据库中存储的数据处于一致状态。
在正在进行的事务中，数据可能处于不一致的状态。
当事务成功完成时，数据必须再次回到已知的一致状态。

I：
隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

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

事务之间的关系

事务的具体操作

create database SCHOOL;
use SCHOOL;
create table CLASS(  
id int(10) primary key not null,  
name varchar(40),  
money double  
);

insert into CLASS values(1,'A',1000);  
insert into CLASS values(2,'B',1000); 
select * from CLASS;

begin;
update CLASS set money= money - 100 where name='A';
select * from CLASS;

commit;
quit

mysql -u root -p
use SCHOOL;
select * from CLASS;

测试回滚事务

begin;
update CLASS set money= money + 100 where name='A';
select * from CLASS;

rollback;
quit
mysql -u root -p
use SCHOOL;
select * from CLASS;

测试多点回滚

begin;
update CLASS set money= money + 100 where name='A';
select * from CLASS;
SAVEPOINT S1;
update CLASS set money= money + 100 where name='B';
select * from CLASS;
SAVEPOINT S2;
insert into CLASS values(3,'C',1000);

select * from CLASS;
ROLLBACK TO S1;
select * from CLASS;

使用 set 设置控制事务

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

use SCHOOL;
select * from CLASS;
SET AUTOCOMMIT=0;
SHOW VARIABLES LIKE 'AUTOCOMMIT';
update CLASS set money= money + 100 where name='B';
select * from CLASS;
quit

mysql -u root -p
use SCHOOL;
select * from CLASS;