53、数据库的索引和事务

mysql的索引、事务

一、mysql的索引

1.1、索引的概念

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

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

1.2、索引的作用

1.2.1、索引的优点

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

加快数据库的查询速度。

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

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

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

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

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

1.2.2、索引的副作用：

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

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

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

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

1.2.3、创建索引的原则和依据：

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

• 2、一张表超过300行的数据，应该要创建索引。

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

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

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

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

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

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

1.3、索引的类型

**b-tree 索引，**

b-树索引

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

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

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

1.4、创建索引

1.4.1、主键索引，主键不需要额外声明索引

SHOW INDEX FROM test1;##查看索引类型

alter table test1 drop name;##丢弃name列
SHOW INDEX FROM test1;##查询test1表的索引列
desc test1;##查看表结构
alter table test1 add name char(10) not null unique KEY;##添加name列
SHOW INDEX FROM test1;##查询test1表的索引列

1.4.2、创建表时创建普通索引

index 列名_index (列名)

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

show index FROM member;##查看索引的列

1.4.3、创建完成后，插入普通索引

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

alter table member add index id_index(id);

insert into member values(1,‘成’,‘432’,21324,‘nanjing’,‘jdi’),
(2,‘cj’,‘345’,2132,‘nan’,‘ji’);

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

explain select * from member where cardid = ‘345’;
explain 查询当前语句使用索引的情况。

1.4.5、创建唯一索引

唯一索引：可以用key的方式创建，也可以用索引的方式创建。

unique index 列名_index(列名);##在表里面创建

create unique index 列名_index on 表名 (列名);##表外插入索引方法1

alter table 表名 add unique index 列名_index(列名);##表外插入索引方法2

create unique index cardid_index on member (cardid);

alter table member add unique INDEX phone_index (phone);##表外创建phone列为唯一索引
alter table member modify COLUMN phone int(11) not null unique KEY;##改变列类型创建索引
show index FROM member;

alter table member add unique INDEX phone_index (phone);

主键索引是一种特殊的唯一索引。

1.5、删除索引

1.5.1、删除索引第一种：

drop index 列名_index on 表名;##删除列的索引

drop index name_index on member;##删除name的索引

1.5.2、删除索引第2种：

ALTER TABLE 表名 drop INDEX 列名_index;##删除列的索引

alter table member drop index id_index;##删除id列的索引
show index FROM member;

1.6、创建组合索引

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

alter table 表名 add CONSTRAINT uc_列名1_列名2_列名3 unique (列名1,列名2,列名3);##创建组合索引

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

select * from member where name='cj' and  cardid = '345'  and  phone = 2132;##使用组合索引

alter table 表名 drop CONSTRAINT uc_列名1_列名2_列名3;
alter table member drop CONSTRAINT uc_name_cardid_phone;##删除组合索引

1.7、删除组合索引

alter table 表名 drop CONSTRAINT uc_列名1_列名2_列名3;
alter table member drop CONSTRAINT uc_name_cardid_phone;##删除组合索引

普通索引

主键索引

组合索引

1.8、全文索引

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

create fulltext index 列名_index on 表名 (列名);##创建全文索引

create fulltext index remark_index on member (remark);

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

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

实操题：

要求商场做一个会员卡系统，包含以下字段：

会员编号 int 5

会员姓名 varchar 10

会员的身份证号 int 11

会员地址 varchar 50

会员的入会时间 timestamp 当前时间

会员的备注信息 text

创建时设计好该表的索引

主键id

姓名创建 普通索引

身份证 唯一索引

会员备注信息 模糊查询 全文索引

create table hy (
id int(5) PRIMARY KEY,
name varchar(10),
cardid int(11),
address varchar(50),
time timestamp,
xinxi text
);
CREATE INDEX name_index on hy(name);
ALTER TABLE hy add unique index cardid_index(cardid);
CREATE fulltext INDEX xinxi_index on hy(xinxi);
show INDEX from hy;

create table hy (
id int(5) PRIMARY KEY,
name varchar(10),
cardid int(11),
address varchar(50),
time timestamp,
xinxi text,
INDEX name_index (name),
unique key cardid_index (cardid),
fulltext INDEX xinxi_index (xinxi)
);

二、事务：

2.1、事务的概念

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

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

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

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

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

2.2、事务的特点：

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

• A:ATOMICITY原子性
• C:consistency一致性
• I:isolation隔离性
• D:durability持久性

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

2. 一致性：事务开始之前和事务结束之后，数据库的完整性没有被破坏。开始的时候数据是一致的，结束的时候数据也是一致的。在事务进行的时候，数据可以处于不一致的状态，但是一结束，数据必须回到一致。

3. 隔离性：并发环境中，不同的事务同时操作相同的数据时，每个事务都由自己完整的数据空间。对数据的修改所发生的并发事务是隔离的，每个事务之间都是独立。一个用户的事务不被其他事务所干扰。

2.2.2、数据库的隔离性：

1、未提交读，read uncommitted RU

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

2、提交读，read committed RC

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

3、repeatable read RR:可重复读，一个事务在执行中，执行两次相同的select语句，得到的结果都是相同。mysql的默认隔离选项 防止脏读和不可重复读。

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

##查看系统的隔离性

SHOW GLOBAL VARIABLES LIKE '%ISOLATION%';

SHOW SESSION VARIABLES LIKE '%ISOLATION%';

##隔离性改为脏读

set global transaction isolation level read uncommitted;##未提交读

set session transaction isolation level read uncommitted;##未提交读

commit;##提交

begin;##开始

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

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

set global transaction isolation level read committed;##更改隔离性为提交读。

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

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

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

2.3、事务的控制语句：

begin：start：transaction：

开启一个事务

commit：提交事务

rollback：回滚

savepoint 名称

savepoint s1##存档点，一旦提交所有存档即消失。

设置回滚点。

rollback to savepoint 名称

rollback to savepoint s1##回滚到s1存档，若有多个存档，回滚一次，其他存档失效，若commit提交，所有存档失效。

回滚到指定的点。

##只能回滚一次。

##一旦提交，不能回滚。

会覆盖先提交的。

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