mysql的索引、事务和存储引擎-CSDN博客

本文链接：https://blog.csdn.net/QChestnut/article/details/140497371

1、索引

1.1概念

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

索引值和数据是一个映射关系，通过索引值可以对应数据所在的位置

1.2作用

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

加快数据库查询的速度

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

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

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

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

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

1.3索引的副作用

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

1.4创建索引的原则和依据

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

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

1.5索引的类型

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

特点：从索引左边的列开始，从左到右按顺序进行排列

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

1.6创建索引

1.6.1普通索引

create table member (

id int(10),
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
remark text,
#text也是数据类型的一种，和char、varchar性质相同，都是字符串，不需要长度的参数，可以作为大文本的列，可以储存65535个字符
index name_index (name)
#指定name为索引
);

#查看索引
show index from 表名;

#添加id为索引
alter table member add index id_index(id);

#查询当前语句使用索引的情况
explain select * from member where id=1;

查看索引

show index from 表名;

添加新的列为索引

alter table 表名 add index 列名_index(列名);

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

explain select * from 表名 where 条件;

1.6.2唯一索引

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

create unique index 列名_index on 表名(列名);

1.6.3主键索引

是一种特殊的唯一索引

1.6.4组合索引

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

alter table member add constraint uc_name_cardid_phone unique (name,cardid,phone);

select * from member where name= and cardid= and phone= ;

1.6.5全文索引

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

#创建
create fulltext index remark_index on member (remark);

#查询
select * from member where match (remark) against('vip');

1.7删除索引

方法一：
drop index 索引名 on 表名

方法二：
alter table 表名 drop index 索引名

1.8练习

商场做一个会员卡系统，包含以下字段：
会员编号 int 5
会员姓名 varchar 10
会员的身份证 int 11
会员的地址 varchar 50
会员的入会时间 timestamp 当前时间
会员的备注信息 text

要求：创建时设置好该表的索引
id 主键
姓名普通索引
身份证唯一索引
会员信息模糊查询全文索引

create table member2 (
id int(5) primary key,
name varchar(10),
index name_index (name),
cardid int(11),
unique index cardid_index (cardid),
address varchar(50),
date_time timestamp not null,
remark text,
fulltext index remark_index (remark)
);

show index from member2;

2、事务

2.1概念

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

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

2.2目的

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

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

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

2.3特点

2.3.1ACID

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

A：Atomicity原子性

C：Consistency一致性

I：Isolation隔离性

D：Durability持久性

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

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

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

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

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

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

2.3.2数据的隔离性

1、未提交读：read uncommitted RU

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

2、提交读：read committed RC

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

oracle和sql server是提交读

3、可重复读：repeatable read RR

一个事务在执行中，执行两次相同的select语句，得到的结果都是相同的

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

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

2.3.3其他

脏读：一个事务看到另一个事务未提交的结果

#查看全局隔离级别
show global variables like '%isolation%';

#查看当前会话隔离级别
show session variables like '%isolation%';

#修改隔离级别
set global transaction isolation level read uncommitted;

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

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

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

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

3、事务的控制语句

begin/start transaction：开启一个事务

commit：提交事务

rollback：回滚

设置回滚点：savepoint 名称

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

可以设置多个还原点，但是一旦还原到其中一个点，其他的全部消失；回滚点只能还原一次，一旦提交无法还原，是因为数据库的持久性，一旦写入无法更改

4、存储引擎

4.1概念

存储引擎是一种数据库存储数据的机制、索引的技巧、锁定水平

是一种存储的方式和存储的格式

存储引擎也属于mysql当中的组件，实际上操作的、执行的就是数据的读写I/O

mysql默认就是自动提交写入

oracle是提交才能写入

4.2分类

InnoDB：mysql5.5之后默认开始使用，事务型速记存储引擎，支持ACID，支持行锁定

MYisam：是mysql5.5之前默认的存储引擎，插入的速度和查询的速度很快，但是不支持事务

memory：内存型存储引擎，数据在写时都是保存在内存当中一旦重启所有数据全部消失

CSV：逗号分割数据存储引擎，数据文件以.csv文件保存的。保存的文件就是一个普通的文本文件，不支持索引