mysql （三）

mysql索引

索引：

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

索引值和数据是映射关系

作用

使用索引之后，不需要扫描全表来定位某行数据。加快数据库的查询速度

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

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

2.表的数据很大，或多表查询时 索引可以大大提高查询速度

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

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

副作用：

1.创建的索引额外占用磁盘空间。innodb存储引擎表数据文件和索引文件在一块。相对来说占用空间小

2.更新一个包含索引的表，比没有索引的表花费更多的时间

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

理想的做法：在经常s被搜索条件的列上创建索引

创建索引的原则 依据：

1.表的主键和外键必须有索引，主键唯一 、 外键关联主表，这样创建索引可以快速定位

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

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

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

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

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

7.选择一个性能高的字段作为索引(字段不同的值比较多的适合)

8.索引要建立在小字段上(字符串长度短)，长文本，超长字段不适合建立索引

索引的类型：

b-tree索引 或 b-树 索引 绝大多数数据都使用b-树索引

哈希索引：散列 索引对应的哈希值的方法获取表的记录 速度慢 用的少

创建索引
create table member (
id int(10),
name varchar(10),
remark text,
#text是一种数据类型，和char varchar性质相同，都是字符串，不需要长度参数，可以作为大文本的列。可存储65535个字符
INDEX name_index (name)
#创建表时创建索引
);

查看索引
SHOW INDEX FROM member;
创建、添加索引
ALTER TABLE member ADD INDEX id_index(id);
CREATE INDEX id_index ON member(id);

EXPLAIN SELECT * FROM member WHERE id =1;
#explain查询当前语句使用索引的情况

#删除索引
DROP INDEX phone_index on member;
ALTER TABLE member DROP INDEX id_index;

主键索引：设置了主键，会自动作为索引

唯一索引：唯一约束也自动添加为索引

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

CREATE FULLTEXT INDEX remark_index ON member(remark);

事务

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

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

数据的一致性非常重要。

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

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

事务的特点：ACID 在数据库的管理系统中，事务的特性有A C I D四种

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

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

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

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

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

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

数据库的隔离性：

1.未提交读 read uncommitted =RU

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

2.提交读：read committed =RC

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

oracle 、sql server 提交读

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

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

4.串行读，相当于锁表，完全串行化的读，一个事务在使用，其他事务的读写都阻塞（同时只能由一个人操作）

D:durability持久性： 一旦提交写入了数据库，数据不可更改

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

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

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

不可重复读是正常的情况

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

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

事务的控制语句：

开启一个事务 begin; start transaction;

提交事务 commit;

rollback：回滚

savepoint +名称 :设置回滚点

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

多个回滚点只能选一个，提交事务后所有回滚点都失效