自学内容网 自学内容网

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 名称

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


原文地址:https://blog.csdn.net/ibertine_P/article/details/140498492

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!