Mysql学习笔记六

一、事务

1.何为事务

要么都成功，要么都失败

1. sql执行：A给B转账
2. sql执行：B收到A的钱

转账的两个操作必须都成功或都失败，不可能A给了钱B却没收到钱。

事务原则（acid）：

• 原子性（atomicity，或称不可分割性）
  要么都成功，要么都失败
• 一致性（consistency）
  事务前后的数据完整性要保证一致
• 隔离性（isolation，又称独立性）
  多个用户并发访问数据库时，数据库为每个用户开启的事务不能被其他事务的操作数据所干扰，事务之间要相互隔离
• 持久性（durability）
  事务一旦提交则不可逆，被持久化到数据库中

隔离产生的一些问题：

脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

详细解释

2.自定义事务

流程：

代码描述：

-- 事务
-- mysql默认开启事务自动提交
-- 别运行下面两句话，只是做个示例
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启

-- 手动处理事务
set autocommit = 0 -- 关闭
-- 事务开启
start transaction -- 标记一个事务的开始，从这之后的sql都在同一个事务内

insert xxx

-- 提交：成功则持久化
commit
-- 回滚：失败回到原来的样子
rollback

-- 事务结束
set autocommit = 1 -- 开启

• 模拟转账

-- 准备工作，创建数据库和表
create database shop;
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)
)

insert into account(name,money) values('A',2000),('B',10000);


-- 模拟转账
-- 一行一行执行
set autocommit = 0; -- 关闭自动提交
start transaction
update account set money = money - 500 where `name` = 'A' -- A减500
update account set money = money + 500 where name = 'B' -- B加500

commit; -- 提交事务
rollback; -- 回滚
set autocommit = 1; -- 回复默认值

二、索引

Mysql官方对索引的定义为：索引（index）是帮助Mysql高校获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

1. 索引的分类

• 主键索引（primary key）
  • 唯一的标识，主键不可重复
• 唯一索引（unique key）
  • 避免重复的列出现，唯一索引可以重复（多个列都可以标识为唯一索引）
• 常规索引（key）
  • 默认，可以用index，key关键字来设置
• 全文索引（fulltext）
  • 在特定的数据库引擎下才有
  • 快速定位数据

2.索引的使用

1. 在创建表的时候给字段添加索引
2. 创建完毕后添加索引

show index from student -- 显示所有的索引信息
alter table student add fulltext index studentname(studentname); -- 增加一个全文索引

3.操作一百万条数据

插入100万条数据：

use school;
CREATE TABLE `app_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 '性别（0：男;1:女）',
`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=utf8 COMMENT = 'app用户表'


-- 插入100万数据
DROP FUNCTION IF EXISTS mock_data;
-- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT DETERMINISTIC
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i<num DO
   INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@qq.com',CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data(); -- 执行此函数 生成一百万条数`app_user`据

测试：

-- 不添加索引时，看看查询速度：
explain select * from app_user where name = '用户99999';

- id_表名_字段名
- create index 索引名 on 表(字段)
create index id_app_user_name on app_user(name);

-- 添加索引后，看看查询速度：
explain select * from app_user where name = '用户99999';

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

4.索引原则

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

索引的数据结构：

• hash类型
• Btree：innoDB的默认数据结构