mysql的索引,事务,存储引擎
mysql的索引,事务,存储引擎
mysql的索引
索引的概念
索引是一个排序的列表,在列表当中存储索引的值以及索引值对应数据所在的物理行.
索引值和数据是一个映射关系.
索引的作用
1.设置了索引之后,数据库可以利用索引快速定位,大大提高查询速度,这也是创建索引的主要原因.
2.尤其是在表的数据很大,以及涉及多个表查询时,索引可以大大的提高查询速度.
3.建立索引不仅能够提高查询速度,在恢复数据库的数据时,也能提高性能.
4.可以加快表与表之间连接查询的速度
索引的副作用
1.创建的索引还是需要占据磁盘空间的.innodb存储引擎表数据和索引文件在一块,相对来说占的空间小一点
2.更新一个包含索引的表,比没有索引的表需要花费更多的时间(表更新了,索引也要更新,所以速度要慢的很多.)
给某一列添加索引之后,就不需要扫描全表来定位数据.
加快数据库查询的速度.
索引可以是表中的一列,也可以是多个列.
创建索引的原则和依据
理想的做法:经常被做为搜索条件的列上创建索引
1.表的主键和外键必须有索引,主键是唯一的,外键是关联主表的,查询时可以快速定位
2.一张表有超过300行的数据,应该要创建索引
3.经常与其他表进行连接的表,在连接字段上应该创建索引
4.更新太频繁的字段,不适合创建索引
5.经常做为where语句的条件列,应该创建索引
6.经常使用grour by和order by的字段上药建立索引
7.选择一个性能高的字段做为索引,字段的值不同的越多越好
8.索引要建立在小字段上,字符串的长度比较小的字段,对于长文本字段,超长的字段不适合建立索引.
索引的类型
b-tree
b树索引,绝大部分的数据都是使用b树索引
是从索引的左边的列开始,从左到右按顺序进行排列
哈希索引
索引对应的bash值的方法获取表的记录行,速度比较慢,用的比较少
创建索引
主键索引
主键约束,唯一约束,默认索引,
#查询索引
show index from 表名;
指定普通列为索引
#创建表的时候
create table member (
id int(10),
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
#text也是数据类型的一种,和char,varchard性质相同,都是字#符串
#不需要长度的参数,可以作为大文本的列.可以储存65535个字#符
remark text,
index name_index (name)
);
show index from member;
#后天添加的索引
alter table member add index id_index (id);
insert into member values
(1,'成某','320123',187051587,'南京','supervip'),
(2,'翟某','320124',187051588,'怀宁','supervip'),
(3,'郝某','320125',187051589,'安宁','supervip'),
(4,'呆某','444444',1911911910,'西天','dazhong');
#查询当前语句使用索引的情况
explain select * from member where id =i;
唯一索引:
可以用key的方式创建,也可以用索引的方式创建
#建表的时候
unique index membercardid_index (membercardid),
#建表之后,创建唯一索引
create unique index cardid_index on member(cardid);
#建表之后,修改列为唯一索引
ALTER TABLE member ADD UNIQUE INDEX phone_index(phone);
删除索引
drop index phone_index on member;
alter table member drop index id_index;
组合索引
一次性给多个列创建索引,形成一个组合.
alter table member add constraint uc_name_cardid_phone unique (name,cardid,phone);
#查看
SELECT * from member where name = '成某' and cardid = '320123' and phone = '187051587';
#select * from member where name= and cardid= and phone=;
全文索引
#建表的时候
FULLTEXT INDEX membernotes_index (membernotes)
#查看一下
show index from member;
#建表之后,创建全文索引
create fulltext index remark_index on member(remark);
#查询单独的vip
explain select * from member where match(remark) against('vip');
事务
事务的概念
mysql的事务,是一种机制,一个操作序列,每一个完整的sql语句就是一个事务,或者说,一组,一条,数据库的操作命令,
那所有的命令做为一个整体向系统提交或者撤销的操作.
将多条sql语句作为整体执行的时候,要么都成功,要么都失败.
是为了数据的一致性,数据的一致性非常重要.
事务是一个不可分割的工作逻辑单元,在数据库上执行并发操作时,事务是最小的,不可分割的控制单元
数据库通过事务的控制和事务的整体性保证数据的一致性.
事务的特点
ACID在数据库的管理系统中,事务的特性有ACID这四种
A:ATOMICITY原子性:
事务的最小控制单位,不可分割,要么都成功,要么都失败.
C:CONSISTENCY一致性:
事务开始之前和事务结束之后,数据库的完整性没有被破坏
开始的时候,数据是一致的,结束的时候数据也是一致的
在事务进行的时候,数据可以处于不一致的状态,但是一旦结束,数据必须回到一致.
I:ISOLATION隔离性:
在并发环境中,不同的事务同时操作相同的数据,每个事务都有自己完整的数据空间,对数据的修改所发生的并发事务,是隔离的,每个事务之间是独立的.
一个用户的事务不被其他事务所干扰的.
数据库的隔离性:
1.read uncommitted RU 未提交读:
允许脏读,一个事务可以看到其他事务未提交的修改.
2.read committed RC 提交读:
一个事务只能看到其他事务已经提交的修改,未提交的修改不可见,防止脏读
oracle和sql server
3.不可重复度:
在一个事务内,多次读同一数据,一个事务没有结束,另外一个事务也访问该数据
4.repeatable read RR可重复读:
一个事务在执行中,执行两次相同的select语句,得到的结果都是相同的,mysql的默认隔离选项,防止脏读和不可重复度.
其中一个事务连续两次查询发现结果不一致,另外一个事务在对这个数据进行修改,两次看到的数据不能一致
5.幻读
一个事务对一个表的数据进行了修改,另一个表也修改了表中的数据,前一个事务会发现修改的结果不正确,怀疑自己到底改没改,类似于出现了幻觉
不可更新:两边同时对数据进行修改,但是一方先提交,一方后提交,后提交的会覆盖先提交的
6.串行读
相当于锁表,完全串行化的读,一个事务在使用,其他事务的读写都会堵塞
D:DURABILITY持久性:
一旦提交,数据写入磁盘,数据不可更改
查看隔离级别
#全局改成未提交读
show global variables like '%isolation%'
show session variables like '%isolation%'
#修改成脏读
set global transaction isolation level Read Uncommitted;
set session transaction isolation level Read Uncommitted;
事务的控制语句
begin或者start transaction
开始一个事务
commit
提交事务
rollback
回滚
savepoint 名称
设置回滚点
rollback to savepoint 名称
回滚到指定的点
多个还原点只能有生效一个,一旦还原,其余全部失效
一但commit提交,还原点全部失效
回滚的注意事项：
- 已提交的事务无法被回滚，事务提交后，事务内的回滚点自动失效
- 回滚到某一个回滚点后，在这个回滚点之后创建的回滚点都会失效
- **不指定回滚点时，默认行为是回滚整个当前的事务。**这意味着，MySQL 将会撤销自上次
BEGIN或START TRANSACTION开始以来所有未提交的更改。
create table cost (
id int(4),
customer char(4),
money int(6)
);
insert into cost values
(1,'丁一',1),
(2,'孙二',10),
(3,'张三',100),
(4,'李四',1000),
(5,'王五',10000);
begin;
update cost set money=money+1 where id=1;
savepoint s1;
update cost set money=money+1 where id=2;
savepoint s2;
update cost set money=money+1 where id=3;
savepoint s3;
update cost set money=money+1 where id=4;
savepoint s4;
update cost set money=money+1 where id=5;
rollback;
rollback to savepoint s1;
rollback to savepoint s2;
rollback to savepoint s3;
rollback to savepoint s4;
select * from cost;
commit;
在update cost set money=money+1 where id=5;之后：
可以先回滚到s4，还可以再回滚到s3，再回滚到s2，再回滚到s1；
但如果直接回滚到s2，则不能再回滚到s3，s4，因为这两个回滚点在s2之后，已经在回滚的过程中消失了，但还可以回滚到s1,；
如果直接rollback，则所有事务的改动全部消失，表变为一开始的状态。
练习
商场做一个会员卡系统,包含以下字段:
会员编号 int 5
会员姓名 varchar 10
会员身份证号 int 11
会员的地址 varchar 50
会员的入会时间 timestamp 当前时间
会员的备注信息 text
创建时,设计好该表的索引
id 主键
姓名 普通索引
身份证号 唯一索引
会员信息 模糊查询,全文索引
原文地址：https://blog.csdn.net/qq_59169480/article/details/140664965
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！