MySQL学习03 事务与索引

最新推荐文章于 2024-10-09 22:03:37 发布

中行独复

最新推荐文章于 2024-10-09 22:03:37 发布

阅读量74

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/wuaopan/article/details/108173905

版权

事务(Transaction)

ACID : 原子性(Atomicity) 一致性(Consistency) 隔离性(Isolation) 持久性(Durability)
原子性 : 要么一起成功要么一起失败
一致性 : 事务前后数据保持一致 (总和保持一致)
隔离性: 多个用户并发访问数据库时数据库为每一个用户开启的事务不能被其他事物操作所干扰多个并发事务之间要相互隔离
持久性: 事务一旦提交则不可逆被持久化到数据库中
脏读: 一个事务读取了另一个事务未提交的数据
不可重复读: 在一个事务内读取表中某一行数据多次读取结果不同
虚读(幻读): 在一个事务内读取到了别的事务内插入的数据导致前后读取不一致

-- mysql 是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认)

-- 手动处理事务
SET autocommit = 0 
-- 事务开启
START TRANSACTION  -- 标记一个事务的开始 从这以后sql都在同一个事务内

-- 提交: 持久化
COMMIT
-- 回滚: 回到最初的样子
ROLLBACK
-- 事务结束
SET autocommit =1

SAVEPOINT [保存点名] -- 设置一个事务保存点
ROLLBACK TO SAVEPINT [保存点名]  -- 回滚到保存点
RELEASE SAVEPINT [保存点名] -- 撤销保存点

-- example
create database shop default character set utf8mb4 collate utf8mb4_unicode_ci;
create table account(
`id` int auto_increment not null,
`user` varchar(10) not null,
`salary` decimal(6,2) not null,
primary key(`id`)) engine = innodb default charset=utf8mb4;
insert into account(`user`,`salary`) values('张三',2000.00),(`李四`,3000.00);

set autocommit =0;
start transaction;
update `account` set `salary` = `salary` + 200 where `id`=1;
update `account` set `salary` = `salary` - 200 where `id` =2;

commit;

set autocommit =1;

索引

MySQL官方对索引定义: 索引(index)是帮助MySQL高效获取数据的数据结构

索引本质:索引是数据结构

分类:
- 主键索引(PRIMARY KEY) 唯一的标识,主键不可重复只能有一个列作为主键
- 唯一索引(UNIQUE KEY) 避免重复的列出现唯一索引可以重复多个列都可以标识唯一索引
- 常规索引(KEY / INDEX) 默认
- 全文索引(FullText) 特定数据库引擎下才有快速定位数据

-- 显示所有的索引信息
SHOW INDEX FROM tableName;
-- 增加索引
ALTER TABLE tableName ADD FullText IndexName(field);
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM tableName;  -- 非全文索引
EXPLAIN SELECT * FROM tableName WHERE MATCH(field) AGAINST();

-- example
create table `app_user`(
    `id` bigint(20) unsigned not null auto_increment,
    `name` varchar(50) default '' comment '用户昵称',
    `email` varchar(20) not null comment '用户邮箱',
    `phone` varchar(20) default '' comment '手机号',
    `gender` tinyint(4) unsigned default '0' comment '性别(0:男, 1:女)',
    `password` varchar(100) not null comment '密码',
    `age` tinyint(4) default '0' comment '年龄',
    `create_time` datetime default current_timestamp,
    `update_time` timestamp null default current_timestamp on update current_timestamp,
    primary key(`id`))engine = innodb default charset=utf8mb4 comment='app用户表';
    
-- 插入100万条数据
DELIMITER $$  -- 写函数前的标志
create function mock_data()
returns int
begin
	declare num int default 1000000;
	declate i int default 0;
	while i < num do
		insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) values
(concat('用户:',i),'123@163.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(); -- 本机用时: 158.281s

-- 查询
select * from app_user where `name`='用户名:9999';
explain select * from app_user where `name`='用户名:9999';
-- 添加索引 creat 索引名 on 表明(字段);
create index id_app_user on app_user(`name`);