Mysql学习笔记六_python

事务和索引

一、事务
- 1.何为事务
- 2.自定义事务
二、索引
- 1. 索引的分类
- 2.索引的使用
- 3. *** 作一百万条数据
- 4.索引原则

一、事务 1.何为事务

要么都成功，要么都失败

sql执行：A给B转账
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.索引的使用

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

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';