mysql的索引事务和存储引擎

一、索引

1、索引

索引的概念：索引是一个排序的列表，在列表当中存储索引的值以及索引值对应数据所在的物理行。

索引的引用：

使用索引之后，就不需要扫描全表来定位某行的数据。

加快数据库的查询速度。

索引可以是表中的一列，也可以是多个列。

索引优点：

1、设置了索引之后，数据库可以利用索引快速定位，大大提高查询速度，这也是索引的主要原因。

2、尤其是表的数据很大，以及设计多个表查询时，索引可以大大的提高查询速度。

3、建立索引，不仅能够提高查询速度，在恢复数据库的数据时，也能提高性能。

4、可以加快表与表之间连接查询的速度。

索引缺点：

1、创建的索引也需要占用额外的磁盘空间，INNODB存储引擎表数据文件和索引文件在一块，一体的，相对来说占是空间小一点。

2、更新一个包含索引的表比没有索引的表需要花费更多的时间，表需要更新，索引也要更新，所以速度要慢很多。

理想的做法：经常被作为搜索条件的列上面创建索引。

2、创建索引的原则和依据

1、表的主键和外键必须有索引，主键是唯一的，外键是关联主表的，查询时可以快速定位。

2、一张表有超过300行的数据，应该要创建索引。

3、经常与其他表进行连接的表，在连接字段上创建索引。

4、更新太频繁的字段不适合创建索引，

5、经常作为where语句的条件列，应该创建索引，

6、经常使用group by 和 order by的字段上要创立索引。

7、选择一个性能高的字段作为索引，字段的值不同的越多越好。

8、索引应该建立在小字段上，字符串的长度比较小的字段，对于长文本字段，超长的字段不适合建立索引。

3、索引的类型

b-tree （b-树索引）索引：绝大部分的数据都是使用B-树索引。

索引的左边的列开始，到右按顺序进行排列。

哈希索引：索引对应的hash值的方法获取表的记录行。速度比较慢，赢的方法少。

4、创建索引

1、主键索引：主键即索引

主键索引是一种特殊的唯一索引。

2、普通索引

在创建表的时候创建普通索引

create table member (
id int(10),
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
remark text,
index name_index (name) #创建普通索引
);

3、唯一索引（unique index ）

创建之后修改，可以用key 的方式创建也可以使用索引方式创建

格式

create unique index 索引名 on 表名（）；

ALTER TABLE member add unique index phone_index (phone);
#或者
CREATE UNIQUE index cardid_index on member(cardid);

4、组合索引

组合索引：一次性给多个列创建索引，形成一个组合。

适应于被经常搜索的列

alter table member add constraint 索引名 unique (字段1,字段1,字段1);
例如
ALTER TABLE member add CONSTRAINT uc_name_address UNIQUE (name,address);

查看索引，组合索引共用一个索引名称

使用组合索引

select * from member where name= ... and address......；

5、查看索引

show index from 表名;

6、删除索引

删除索引要先查看，根据索引名（Key_name）进行删除

drop index 索引名称 on 表名;
或者
alter TABLE 表名 drop index 索引名称;

7、全文索引

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

create fulltext index remark_index on member (remark);

数据内容

使用索引

select * from member where match（remark） against （'vip'）

在创建的时候添加多个索引

create table sysvip(
id int(5) PRIMARY KEY,#主键索引
name varchar(10),
cardid int(11),
address varchar(50),
times timestamp,
remark text,
index name_index (name),#普通索引
unique index cardid_index (cardid), #唯一索引
FULLTEXT index remark_index (remark)#全文索引
);

查看

二、 mysql的事务

1、事务

事务是一个机制，一个操作序列。一组或者一条数据库的操作命令。

把所有的命令作为一个整体向系统提交或者撤销的操作。要么都成功要么都失败。

数据的一致性，数据的一致性非常重要

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

数据库通过事务的控制和事务的整体性保证数据的一致性。

2、事务的特点

ACID在数据库的管理系统中，事务的特性有ACID这四种：

A：ATIMICITY 原子性：事务的最小控制单位，不可分割，要么都成功，要么都失败

C:CONSISTENCY 一致性：事务开始之前和事务结束之后数据库的完整性没有被破坏

开始的时候数据是一致的，结束的时候数据也是一致的

在事务进行的时候，数据可以处于不一致的状态，但是一旦结束，数据必须回到一致性。

I:ISOLATION 隔离性：在并发环境中，不同的事务同时操作相同的数据时，每个事务都有自己完整的数据空间。对数据的修改所发生的并发事务是隔离的，每个事务之间都是独立的。

一个用户的事务不被其他事务所干扰。

3、数据库的隔离性

有以下四种：

1、未提交读 read uncommitted RU

允许脏读，一个事务可以看到其他事务未提交的修改

2、提交读，read committed RC

一个事务只能看到其他事务已经提交的修改，未提交的修改不可见，防止脏读

oracle和sql sever

3、可重复读，repeatable read RR 一个事务在执行中，执行两次相同的select语句，得到的结果都想同的mysql的默认隔离选项。防止脏读和不可重复读。

4、串行读，相当于锁表，完全串行化的读，一个事务在使用，其他事务的读写都会阻塞。

脏读：

查看当前会话的隔离级别

show global/session variables like '%isolation%'

修改隔离级别

set global transaction isolation level Read Uncommitted；

set session transaction isolation level Read Uncommitted；

不可重复读：在一个事务内，多次读同一数据，一个事务没有结束，另外一个事务也访问该数据。

其中一个事务连续两次查询发现结果不一致。另外一个事务在对着这个进行修改。两次看到的数据不一致。

set global transaction isolation level Read committed；

set session transaction isolation level Read committed；

终端也要同时进行操作一样的操作。

幻读：一个事务对一个表的数据进行了修改，另一个表也修改了表中数据，前一个事务会返现修改的结果不正确类似于出现了幻觉。

不可更新：两边同时对数据进行修改，但是一方先提交，一方后提交，后提交会覆盖先提交的。

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

4、事务控制语句

begin start transaction：开启一个事务

commit ：提交事务

rollback ：回滚

设置回滚点：savepoint 名称

回滚到指定的点：rollback to savepoint 名称

可以设置多个还原点，但是只能回一次还原点，一旦提交，所有的还原点全部取消（持久性）。