mysql索引 事务和存储引擎

mysql索引 事务和存储引擎

索引的概念

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

索引值和数据是一个映射关系

索引的作用

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

加快数据库的查询速度。

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

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

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

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

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

索引的副作用：

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

2、更新一个包含索引的表比没有索引的表需要花费更多的时间

表需要更新，索引也要更新，所以速度要慢很多。

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

创建索引的原则和依据：

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

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

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

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

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

6、经常使用 groupby 和 order by的字段上要建立索引

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

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

索引的类型：

b-tree 索引

b-树索引

绝大部分的数据都使用b-树索引。

从左到右匹配

哈希索引：

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

创建索引

show index from 表名 查看索引

主键索引：

创建表的时候添加索引

在外添加索引

创建表

create table member (id int(10),
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
remark text
);

alter table member add

添加多个数据

查看当前使用索引

explain 查询当前语句使用索引的情况

唯一索引

可以用key的方式创建 也可以用索引的方式创建。

唯一索引

alter table member add unique index phone_index(phone);

alter table member add unique index phone_index(phone);

普通

alter table member add index phone_index(phone);

删除索引

两种方法

drop index 索引号 on member表名                     alter table 表名  drop index  索引名

组合索引

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

constraint

普通索引

主键索引

唯一索引

组合索引

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

全文索引 模糊搜索

create fulltext index remark_index on member (列名)

explain select * from member where match (remark) against('vip');

区分

create table huiyuan1 (
id int(5) primary key,
name varchar(10),
sfz int(11),
dizhi varchar(50),
shijian timestamp,
remark text,
index name_index(name),
unique index sfz_index(sfz),
fulltext index remark_index (remark)
);

insert into student (id,name,score) values(1,'张三',1234,);

事务的概念

mysql的事务

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

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

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

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

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

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

A：ATOMICTY 原子性

C：consistency 一致性

I：lsolation 隔离性

D：durability 持久性

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

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

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

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

隔离性：并发环境中，不同的事务同时操作相同的数据时，每个事物都有自己完整的数据空间。

对数据的修改所发生的并发事务是隔离的，每个事务之间都是独立的。

一个用户的事务不给其他事务干扰

数据库的隔离性

1、未提交读，read uncommitted RU

允许脏读，一个事物

2、提交读，read commotted RC

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

oracle 和 sql sever

3、可重复读 ，一个事务在执行中，执行两次相同的select语句，得到的结果都是相同的

mysql 的默认隔离选项，防止脏读和不可重复读

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

show global variables like ‘%isolation%’

​         session 

show global variables like ‘%isolation%’

​ session

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

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

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

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

持久性：

一旦写入数据库，数据不可更改。

事物的控制语句

begin、start transaction

开启一个事物

commitl

提交事务

rollback：回滚

savepoint 名称

设置回滚点

rolback to savepoint 名称

回滚到

可以设置多个还原点 但是只可以还原任意一个点 还原完后 其他点失效

一旦提交所有还原点失效，一旦写入不可更改