事务
- 要么都成功,要么都失败(类似try代码块?)
一一一一一一一一一一一一一
A给B转账
SQL1 A账面转出
SQL2 B账面收到
一一一一一一一一一一一一一
若SQL2执行时或执行前出现错误,则SQL1也不生效,否则,A账面白白减少。 - 事务原则:ACID原则:原子性,一致性,隔离性,持久性(脏读,幻读)
- 原子性:对于一个事务中的多个动作,要么都成功要么都失败,不可分离
- 一致性:针对一个事务操作前与后的状态一致
- 持久性:事务结束后的数据不会因外界原因导致数据丢失(若事务没有提交则应该恢复原状,否则保持改变)
- 隔离性:针对多个用户同时操作,主要排除事务间的相互影响
- 脏读:读取了其他事务还未提交的数据
- 不可重复读:在一个事务内多次读取同一数据时结果不同
- 幻读:读取到其他事务插入的数据,导致前后读取结果不一致
-- ============事务===========
-- mysql默认开启事务自动提交
SET autocommit = 0;-- 关闭事务自动提交
SET autocommit = 1;-- 开启事务自动提交
-- 手动处理事务
SET autocommit = 0;-- 1.关闭事务自动提交
-- 事务开启
START TRANSACTION; -- 2.标记一个事务的开始
UPDATE xxx;-- 3. 事务内容
UPDATE xxx;
-- 提交:持久化(成功)
COMMIT;
-- 回滚:回到执行前的状态(失败)
ROLLBACK;
-- 事务结束
SET autocommit = 1;-- 开启事务自动提交
-- 了解内容:
SAVEPOINT xxx; -- 设置一个名为xxx的保存点
ROLLBACK TO SAVEPOINT xxx;-- 回滚到xxx保存点
RELEASE SAVEPOINT xxx;-- 删除保存点xxx
索引
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。即索引其实是一种数据结构。
- 索引的分类
- 主键索引(PRIMARY KEY)
- 唯一标识,不可重复,主键只能有一个
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,一个表中可以多个字段设置为唯一索引
- 常规索引(KEY/INDEX)
- 默认 使用KEY或者INDEX来定义
- 全文索引 (FullTest)
- 快速定位数据,只有特定数据库引擎下支持(MyISAM)
- 主键索引(PRIMARY KEY)
测试索引
- 创建测试表并且插入100万条数据
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 取消函数声明
DROP FUNCTION mock_data;
-- 插入100万数据.
DELIMITER $$;
-- 写函数之前必须要写,标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
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','123456789',FLOOR(RAND()*2),RAND()*999999,FLOOR(RAND()*90));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data(); -- 执行此函数 生成一百万条数据
- 测试查询效率
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999';
-- rows=993816 select用时0.472sec
-- 添加索引
CREATE INDEX id_app_user_name ON app_user(`name`);
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999';
-- rows=1 select用时0.001 sec
在数据量很大时,索引在对查找的优化特别明显
- 索引原则:
- 索引不是越多越好
- 经常变动最好不要添加索引(因为删改效率比没索引低)
- 小数据量不需要索引
- 索引一般添加在常用查询的字段上
用户管理
用户表:mysql.user
本质:对这张表进行增删改
- 创建用户 修改密码和重命名
-- 创建用户
CREATE USER hyx IDENTIFIED BY '123456';
-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456');
-- 修改制定用户密码
SET PASSWORD FOR xxx = PASSWORD('123456');
-- 重命名
RENAME USER xxx TO yyy;
- 授予和查看权限
-- 用户授权
-- 授予全部权限(在所有库中的所有表上) 给yyy(默认没有给别人授权的权限)
-- 要想加入grant权限 在创建语句后添加“with grant option”
GRANT ALL PRIVILEGES ON *.* TO yyy;
-- 查看权限
SHOW GRANTS FOR yyy;-- 查看指定用户的权限
SHOW GRANTS FOR root@localhost;-- 查看root权限
- 撤销权限和删除用户
-- 撤销权限revoke
-- 撤销所有权限 在所有库中所有表中 yyy的
REVOKE ALL PRIVILEGES ON *.* FROM yyy;
-- 删除用户
DROP USER yyy;
MySQL数据库备份
- 保证重要的数据不丢失
- 做数据库转移
- 导出
- 拷贝物理文件
- 使用可视化工具中,手动导出
- 使用命令行 mysqldump(cmd)
# mysqldump -h主机 -u用户名 -p密码 数据库名 表名 >保存路径/文件名
# 可以一次导出一个数据库的多张表,也可以去除表名直接导出数据库
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
- 导入
source 备份文件
规范数据库设计
- 当数据库比较复杂的时候,就需要进行数据库设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序性能差
良好的数据库设计:
- 节约内存空间
- 保证数据库的完整性
- 方便开发系统
软件开发中,关于数据库的设计:
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图 E-R图
设计数据库的步骤:(个人博客为例)
- 收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,创建者)
- 文章表(文章的信息)
- 友链表(友链信息)
- 自定义表(系统信息,某个关键字,或者一些主字段) key:value
- 标识实体
- 写博客:user→blog
- 创建分类:user→category
- 关注:user→user
- 友链:links
- 评论:user→user→blog
- 。。。
三大范式
问题:
- 信息重复
- 更新异常
- 插入异常
- 删除异常
三大范式
第一范式(1NF)
- 要求数据库表中每一列都是不可再分的原子数据项
- 比如:若某表中学生班级格式为“X级X班”,可把其分为年级和班级两项,则不符合第一范式。
第二范式(2NF) - 在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码部分的函数依赖)
- 第二范式需要确保数据库表中每一列都与主键相关,而不能只与主键的一部分相关,而不能只与主键的一部分相关。
- 如图转自百度百科的例子,此表中货物类型和id共同组成主键,而注意事项一栏显然之和主键的一部分“货物类型”有关,因此此例不满足第二范式。
第三范式(3NF) - 在2NF基础上,任何非属性不依赖于其他非主属性(在2NF基础上消除传递依赖)
- 第三范式要求确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
- 如某学生表中属性有:学号、姓名、性别、年级、班级、班主任姓名、班主任年龄。其中班主任姓名和班主任年龄明显依赖于年级、班级,不符合第三范式。
(规范数据库的设计)
规范和性能的问题
阿里要求:关联查询的表不能超过三张
- 考虑商业化的需求和目标,(成本,用户体验)数据库的性能更加重要
- 在规范性能的问题的时候,需要适当考虑一下规范性
- 故意给某些表增加一些冗余字段(减少联表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)