mysql的索引和事务

mysql的索引

索引的概念

索引是一个排序的列表，当列表当中存储索引的值以及索引值对应数据所在的物理行。
索引值和数据是一个映射关系

索引的作用

使用索引之后，就不需要扫描全表来定位某行数据。
加快数据库的查询速度。
索引可以是表中的一列，也可以是多个列。

索引的优点

1.设置了索引之后，数据库可以利用索引快速定位，大大提高查询速度。这也是创建索引的主要原因
2.尤其是在表的数据很大，以及涉及多个表查询时，索引可以大大提高查询速度。
3.建立索引不仅能够提高查询速度，在恢复数据库的数据时，也能提高性能。
4.可以加快表与表之间连接查询的速度。

索引的副作用

1.创建的索引也需要占用额外的磁盘空间。INNODB存储引擎表数据文件和索引文件在一块。一体的。相对来说占的空间小一点。
2.更新一个包含索引的表比没有索引的表需要花费更多的时间，表需要更新，索引也要更新，所以速度要慢很多。

理想的做法

经常被作为搜索条件的列上面创建索引。

创建索引的条件和依据

1.表的主键和外键必须有索引，主键是唯一的，外键是关联主表的，查询时可以快速定位。
2.一张表的记录有超过300行的数据，应该要创建索引。
3.经常与其他表进行连接的表，在连接字段上应该创建索引。
4.更新太频繁的字段，不适合创建索引
5.经常作为where语句的条件列，应该创建索引
6.经常使用group by和order by的字段上要建立索引
7.选择一个性能高的字段作为索引，字段的值不同的越多越好
8.索引要建立在小字段上，字符串的长度比较小的字段，对于长文本字段，超长的字段不适合建立索引。

索引的类型

b-tree 索引
b-树索引
绝大部分的数据都是使用b-数索引。
索引的左边的列开始，从左到右按顺序进行排列

哈希索引
索引对于的哈希值的方法获取表的记录行，速度比较慢，用的比较少。

创建索引

主键索引

主键和唯一约束不需要额外声明，创建时自动添加索引。

#创建表的时候直接创建索引
create table member (
id int(10),
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
#text也是数据类型的一种，和char，varchar性质相同，都是字符串，不需要长度的参数，可以作为大文本的列。可以存储65535个字符
remark text,
#创建普通索引
index name_index (name)
);
#创建完表后添加索引
alter table member add index id_index(id);
#查询索引
show index from member;
#查询当前语句使用索引的情况
explain select * from member where id = 1; 

唯一索引

#唯一索引，可以用key的方式创建，也可以用索引的方式创建。
create unique index cardid_index on member(cardid);
#phone这一列，用alter的方式创建一个唯一索引
alter table member add unique index phone_index(phone);
#主键索引是一种特殊的唯一索引。

删除索引

#删除索引
drop index phone_index on member;
alter table member drop index id_index;

组合索引

#组合索引，一次性给多个列创建索引，形成一个组合。
alter table member add constraint uc_name_cardid_phone unique(name,cardid,phone);
#然后使用组合索引
select * from member where name = and careid =  and phone = ;

全文索引

全文索引：适用于模糊查询，检索大文本使用的。

#创建全文索引
create fulltext index remark_index on member(remark);
#使用全文索引
explain select * from member where match(remark) against('vip');

mysql的事务

事务是一个机制，一个操作序列。一组或者一条数据库的操作命令。
把所有的命令作为一个整体向系统提交或者撤销的操作。
要么都成功，要么都失败。
数据的一致性。数据的一致性非常重要。
事务是一个不可分割的工作逻辑单元，在数据库上执行并发操作时，事务是最小的控制单元。
数据库通过事务的控制和事务的整体性保证数据的一致性。

事务的特点

ACID 在数据库的管理系统中，事务的特性有ACID这四种：
A:ATOMICITY 原子性
C:consistency 一致性
I:isolation 隔离性
D:durability 持久性
原子性：事务的最小控制单位，不可分割。要么都成功，要么都失败。

一致性：事务开始之前和事务结束之后，数据库的完整性没有被破坏。
开始的时候数据是一致的。结束的时候，数据也是一致的。
在事务进行的时候，数据可以处于不一致的状态。但是一旦结束，数据必须处于一致的状态。

隔离性：并发环境中，不同的事务同时操作相同的数据时，每个事务都有自己完整的数据空间，对数据的修改所发生的并发事务是隔离的。每个事物之间都是独立的。
一个用户的事务不被其他事务所干扰的。
数据库的隔离性：
1.未提交读，read uncommitted RU
运行脏读。一个事务可以看到其他事务未提交的修改。
2.提交读，read committed RC
一个事务只能看到其他事务已经提交的修改，未提交的修改不可见，防止脏读
oracle和sql server 都是提交读
3.可重复读，repeatable read RR 一个事务在执行中，执行两次相同的select语句，得到的结果都是相同的
mysql的默认隔离选项，防止脏读和不可重复读
4.川航读，相当于锁表，完全串行化的读，一个事务在使用，其他事物的读写都会阻塞。

持久性：一旦写入数据库，数据不可更改。

#脏读
#查询数据库的隔离方式
show global variables like '%isolation%'
show session variables like '%isolation%'
#修改数据库的隔离方式
set global transaction isolation level READ Uncommitted;
set session transaction isolation level READ Uncommitted;

不可重复读：在一个事务内，多次读同一数据，一个事务没有结束，另外一个事务也访问该数据。
其中一个事务连续两次查询发现结果不一致。另外一个事务在对这个数据进行修改。两次看到的数据不能一致。

幻读：一个事务对一个表的数据进行了修改，另一个表也修改了表中的数据，前一个事务会发现修改的结果不正确，类似于出现了幻觉。
不可更新：两边同时对数据进行修改，但是一方先提交，一方后提交，后提交会覆盖先提交的。

事务控制语句

begin：start transaction
开启一个事务
commit：
提交事务
rollback：
回滚
savepoint 回滚点的名称
设置回滚点
rollback to savepoint 回滚点的名称
回滚到指定的点

设置多个回滚点，在使用其中一个回滚点之后，其他回滚点全部失效。
使用commit提交之后，所有的回滚点全部失效