数据库——MySQL(事务、索引)

一： 事务

概念：要么都成功，要么都失败

事务原则 ACID

原子性：atomicity

要么都成功，要么都失败

一致性：Consistency

事务前后的完整性要保持一致， 比如转账 两个人的总钱数不可能因为互相转账和发生改变

隔离性：Isolation

事务的隔离性是多个用户并发的访问数据库时，数据库为每个用户开启的事务，不能被其他的操作数据所干扰，多个并发事务之间要相互隔离

持久性：Durability

事务一旦提交则不可逆，被持久化到数据库中

隔离所导致的问题

脏读 指一个事务读取到另一个事务未提交的数据

不可重复读 在一个事务中读取到表的某一行数据，多次读取的结果不同(这个不一定是错误，只是某些场合不对)

虚读(幻读) 指在一个事务中读取到别人插入的数据，导致前后读取不一致

执行事务

-- MySQL是默认开启事务自动提交的
set autocommit = 0; -- 关闭自动提交事务
set autocommit = 1; -- 开启自动提交事务,
start transaction -- 标记着一个事务的开始，从这个之后的sql都在同一个事务内

commit -- 提交：数据持久化
rollback -- 回滚：数据 回到原来的样子

savepoint -- 保存点名 设置一个事务的保存点 比如游戏中的剧情关卡
rollback to savepoint -- 回滚到保存点
release savepoint -- 撤销保存点

转账案例实现

-- 转账案例

create database shop character set utf8 collate utf8_general_ci;
use shop;

create table `account`(
`id` int(3) not null auto_increment,
`name` varchar(30) not null,
`money` decimal(9,2) not null,
primary key(`id`)
)engine=innodb default charset=utf8;

insert into account(`name`,`money`)values
('A',2000.00),
('B',3000.00);

-- 模拟转账
set autocommit = 0;	-- 关闭自动提交
start transaction; -- 开启一个事务(一组事务)

update account set money = money-500 where `name` = 'A';
update account set money = money+500 where `name` = 'B';

commit; -- 提交事务 ，数据被持久化 当没commit时 原数据都不会改变

rollback; -- 回滚事务 数据恢复原状

set autocommit = 1;	-- 恢复默认值

二：索引

索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引分类

在一个表中，主键索引只能有一个，唯一索引 可以有多个

主键索引：primary key

• 唯一的标识，主键不可重复，只能有一个列作为主键

唯一索引：unique key

• 避免重复的列出现，唯一索引可以重复，多个列都可以标识成 唯一索引

常规索引：key/index

• 默认的， index,key 关键字来设置

全文索引：fulltext

• 在特定的数据库引擎下才有，例如MyISAM 现在innodb也支持了
• 快速定位数据

基础语法

-- 索引的使用
-- 显示所有的索引信息
show index from student;

-- 增加一个全文索引 索引名(列名)
alter table `student` add fulltext index `studentname` (`studentname`);
-- explain 分析sql执行的情况
explain select * from student; -- 非全文索引
explain select * from student where match(studentname) against ('陈');

测试索引

create table `user`(
`id` bigint(20) unsigned not null auto_increment,
`name` varchar(50) default '' comment '用户昵称',
`email` varchar(50) not null comment '用户邮箱',
`phone` varchar(20) default '' comment '手机号',
`gender` tinyint(4) unsigned default 0 comment '性别',
`password` varchar(100) not null comment '密码',
`age` tinyint(4) default '0' comment '年龄',
`create_time` datetime default current_timestamp,
`update_time` timestamp not null default current_timestamp on update current_timestamp,
primary key(`id`)
)engine = innodb default charset = utf8mb4 comment = '用户表';


-- 测试插入语句
insert into user(`name`,`email`,`phone`,`gender`,`password`,`age`)
			values(concat('用户1'),'124510@qq.com',concat('18',floor(rand()*((999999999-100000000)+100000000))),
						floor(rand()*2),uuid(),floor(rand()*100)
			);

-- 插入一百万条数据 时间: 48.006s
delimiter $$ -- 写函数之前必须要写， 标志

create function mock_data()
returns int deterministic
BEGIN
		declare num int  default 1000000;
		declare i int default 0;

		while i < num do
		-- 插入语句
			insert into user(`name`,`email`,`phone`,`gender`,`password`,`age`)
			values(concat('用户',i),'124510@qq.com',concat('18',floor(rand()*((999999999-100000000)+100000000))),
						floor(rand()*2),uuid(),floor(rand()*100)
			);
			set i = i+1;
		end while;
		return i;
end;

-- 执行此函数
select mock_data();


-- 开始查询
select * from user where `name` = '用户9999';	-- 时间: 0.495s

explain select * from user where `name` = '用户9999'; -- rows查询了992690行数据才找到它

-- 发现大数据的时候 查询会变得很慢
-- 所以我们要改进它 给它加上索引

-- create fulltext/index 索引名 on 表（字段）
-- id_表名_字段名
create index id_user_name on user(`name`); 	-- 添加索引 会在内存中新添一块区域 时间: 3.293s

-- 再次查询

select * from user where `name` = '用户9999';		-- 时间: 0.004s 时间飞速

explain select * from user where `name` = '用户9999'; -- rows查询了1行数据找到它 直接定位

注意点：mysql8之后的函数返回值类型 int 后面需加上 deterministic

索引在小数据量的时候，用处不大，但是在大数据量的时候，区别十分明显

索引原则

索引不是越多越好

• 不要对经常变动的数据 加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引
Btree：InnoDB的默认数据结构