Mysql----索引与事务

1.索引

1.1什么是MYSQL的索引

MySQL官方对于索引的定义：索引是帮助Mysql高效获取数据的数据结构

Mysql在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用（指向）表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据，而这种数据结构就是索引。

简单理解为：排好序的可以快速查找数据的数据结构

1.2索引的数据结构

下图是一种可能的二叉树的索引方式

二叉树数据结构的弊端：当极端情况下，数据递增插入时，会一直向右插入，形成链表，查询效率会降低

Mysql中常用的索引数据结构有BTree索引（Myisam普通索引），B+Tree索引（Innodb普通索引），Hash索引（memory存储索引）等等

1.3索引的优势

提高数据检索的效率，降低数据库的IO成本

通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

1.4索引的劣势

索引实际上也是一张表，保持了主键和索引的字段，并且指向实体表的记录，所以索引也是需要占用内存空间的。在索引大大提高查询速度的同时，却会降低表的更新速度，在对表进行数据增删改的同时，Mysql不仅要更新数据，还需要保存一下索引文件。每次更新添加了的索引列的字段，都会去调整因为更新带来的减值变化后的索引的信息

1.5索引使用场景

哪些情况需要创建索引：

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引（where后面的语句）

3.查询中与其他表关联的字段，外键关系建立索引

4.多字段查询下倾向创建组合索引

5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6.查询中统计或者分组字段

---

哪些情况不推荐建立索引：

1.表记录太少

2.经常增删改的表

3.where条件里用不到的字段不建立索引

1.6索引分类

1.6.1主键索引

1.标准的列设定为主键后，数据库会自动建立主键索引。

2.单独创建和删除主键索引的语法：

创建主键索引的语法：alter table 表名 add primary key (字段);
删除主键索引语法：alter table 表名 drop primary key;

1.6.2唯一索引

1.表中的列创建了唯一约束时，数据库会自动建立唯一索引

2.单独创建和删除唯一索引语法：

创建唯一索引语法：
alter table 表名 add unique 索引名（字段）;
或
create unique index 索引名 on 表名（字段）;

删除唯一索引语法:
drop index 索引名 on 表名;

 1.6.3单值索引

即一个索引只包含单个列，一个表可以有多个单值索引

1.建表时可以随表一起建立单值索引

2.单独创建和删除单值索引：

创建单值索引：alter table 表名 add index 索引名（字段）;
或  
create index 索引名 on 表明（字段）;

删除单值索引： drop index 索引名 on 表名;

1.6.4符合索引

即一个索引包含多个列：

1.建表时可以随表一起建立复合索引

2.单独创建和删除复合索引：

创建复合索引：
create index 索引名 on 表名(字段1，字段2);
或
alter table 表名 add index 索引名（字段1，字段2）;

删除复合索引：
drop index 索引名 on 表名;

1.6.5创建删除索引演示

代码演示：

1.6.5.1随创建表创建索引

create table customer(
id int(10) auto_increment,
customer_no VARCHAR(20),
customer_name varchar(20),
--主键索引
primary key(id),
--唯一索引
unique idx_customer_no(customer_no),
--单值索引
key idx_customer_name(customer_name),
--复合索引
key idex_customer_no_name(customer_no,customer_name)
)

创建完表后，右键表名，点击设计表，可以看见有一个钥匙，这个钥匙对应id这一列，就代表id这一列是我们的主键，是主键就代表他拥有主键索引

点击索引，名称代表索引的名字，字段表示拥有该索引的字段，索引类型有唯一索引单值索引，复合索引，索引方式是二叉树

1.6.5.2单独创建和删除索引

创建表时，没有索引

create table customer(
id int(10) ,
customer_no VARCHAR(20),
customer_name varchar(20)
)

即没有主键索引，也没有唯一索引，什么索引也没有

四种索引的单独创建和删除：

#创建主键索引
alter table customer add primary key(id);
#删除主键索引
alter table customer drop primary key;

#创建唯一索引
alter table customer add unique idx_customer_no(customer_no);
#删除唯一索引
drop index idx_customer_no on customer;

#创建单值索引
alter table customer add index idx_customer_name(customer_name);
#删除单值索引
drop index idx_customer_name on customer;


#创建复合索引
alter table customer add index idx_customer_no_name(customer_no,customer_name);
#删除复合索引
drop index idx_customer_no_name on customer;

在单独创建唯一索引时，要保证当前表中创建唯一索引的列中的数据是唯一且不为空的

在单独创建唯一索引时，要保证当前表中创建唯一索引的列中的数据是唯一的

2.事务

2.1事务简介

●数据库的事务是一种机制，一个操作序列，包含了一组数据库的操作命令

●事务把所有的命令作为一个整体一起想系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败

●事务是一个不可分割的工作逻辑单元

2.2事务的操作

2.2.1问题场景

场景：

李四向张三借了1000元，李四的钱增加1000，张三的钱减少1000

创建一张表：表中信息包括id，姓名和钱

CREATE table account(
id int primary KEY auto_increment,
name varchar(20) not null,
money decimal(7,2)
);

添加数据：

insert into account(name,money) values('张三',3000);
insert into account(name,money) values('李四',1000);

 此时查询，张三的金额是3000，李四的金额是1000

借钱操作：

张三的金额-1000，李四的金额+1000

UPDATE account set money=money-1000 where name='张三';
UPDATE account set money=money+1000 where name='李四';

此时不管是在当前查询还是新建查询，account表中张三和李四的金额均发生了变化 

当我们将张三的金额恢复到3000，李四的金额恢复到1000后，再执行下面这段代码，会发现只有 张三借给李四钱的操作执行l，其余语句执行失败，这时不管是在当前查询还是新建查询，只有张三的金额变为2000，李四的金额还是1000，这在生活中，钱不翼而飞是不允许的

UPDATE account set money=money-1000 where name='张三';
出错了
UPDATE account set money=money+1000 where name='李四';

2.2.2事务的操作 

 为了解决上述问题，我们就需要用到事务

事务的关键字：

●开启事务：begin；       或者      start  transaction；

●提交事务：commit；

●回滚事务：rollback；

将张三和李四的金额恢复成3000和1000时，当我们开启事务再次执行出错的借钱操作，在当前查询，只有张三的金额变了，而李四的金额没有改变，在新建查询发现张三和李四的金额均没有改变，这是因为开启事务这个操作在没有commit之前都是临时的，只有在当前查询能看到，在其他查询看不到

开启事务之后，执行中出现错误，当前查询中一部分修改了数据，这时我们需要回滚事务，也就是将事务回滚到开启事务之前，将当前查询的数据变为开启事务之前

 但是当我们重复开启多条事务时，再执行回滚事务（rollback）时，这时只会回滚到最近的一条开启事务之前，多运行几次回滚事务，也只会执行一次，也就是不管连续执行多少次回滚事务，他都只会回滚到最近的一条开启事务之前，且只回滚一次，这是因为在开启事务之后再次开启事务，就已经将上次的事务提交了，提交之后就将数据写入硬盘了

在开启事务之后，在第二次开启事务或者提交事务之前，数据都是临时的，没有写入硬盘的，但我们确认事务的数据没问题后，就可以使用commit关键字提交事务了

commit;

 使用commit;提交事务后，不管是当前查询还是新建查询，均可看见修改后的数据

2.3事务的四大特性

●原子性（Atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败

●一致性（Consistency）：事务完成时，必须使所有的数据保存一致状态

●隔离性（Isolation）：多个事务之间，操作的可见性

●持久性（Durability）：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的

2.4事务补充

在mysql中，事务是默认提交的，当写一条sql语句，mysql会将这条语句当成一条事务，默认将其提交

查看提交方式：

select @@autocommit;

在查看提交方式时，1代表默认提交，0代表手动提交 

修改提交方式：

set @@autocommit=0;

 当提交方式修改为手动提交时，我们在写mysql语句时，就需要多执行一句commit语句