MySQL的索引、事务

MySQL的索引

索引的概念

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

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

索引的作用

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

加快数据库查询的速度。

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

优点

1、设置了索引之后，数据库可以利用索引可以快速的定位，大大提高查询的速度。（主要作用）

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

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

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

缺点

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

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

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

创建索引的依据和原则

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

2、记录超过三百行数据的表，应该创建索引

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

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

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

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

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

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

索引的类型

b-tree 索引

b-树索引

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

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

哈希索引：

索引对应的哈希值的方式获取表的巨鹿行，速度表叫慢，用的少。

创建索引

主键索引

是一种特殊的唯一索引。

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

命令行添加多个内容 insert into member values (3,'阳哥','1122','119','南京','VIP2'), (2,'王','123456','120','病','vip');

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

唯一索引：

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

alter table 表名 add unique index 索引名 （列名） alter table member add unique index phone_index (phone)

create unique index 索引名 on 表名 （列名） create unique index cardid_index on member (cardid)

explain select * from member where id=1

删除索引

drop index 索引名  on  表名 drop index phone_index on member

alter table 表名  drop index  索引名 alter table member drop index name_index

组合索引

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

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

组合索引的用法 select * from 表名  where 创建的索引名=查找条件 and 创建的索引名=查找条件  and 创建的索引名=查找条件 select * from member where name=  and cardid=  and phone=  ；

全文索引

：适用于模糊查询

创建全文索引 ​ create fulltext index remark_index on member (remark)

模糊查询 explain select * from 表名  where match （列名） against （‘条件’）； explain select * from member where match (remark) against ('vip');

实验

CREATE TABLE member ( id int NOT NULL, name varchar(10) NOT NULL, cardid int NOT NULL, address varchar(50) NOT NULL, time timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP, remark text, PRIMARY KEY (id), UNIQUE KEY cardid_index (cardid) USING BTREE, KEY name_index (name), fulltext index remark_inex (remark) );

事务

mysql的事务

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

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

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

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

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

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

A：ATOMICITY 原子性

C：CONSISTENCY 一致性

I：ISOLATION 隔离性

D：DURABILITY 持久性

原子性：

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

一致性：

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

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

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

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

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

隔离性：

1、未提交读，read uncommitted RU

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

2、提交读，read committed RC

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

oracle和sql sever

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

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

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

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

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

幻读：一个事务对一个表的数据进行了修改，另一个表也修改了表中的数据，前一个事务。

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

持久性：

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

事务的控制语句

begin ；star transaction 开启一个事务

commitl 提交事务

rollback 回滚

savepoint 名称 设置回滚点

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

还原点可以有多个，只能还原一次还原后其他还原点失效。

设置多个还原点，提交事务后还原点失效。