MySQL学习第五篇

事务和事物回滚

1. 事务定义：一件事开始发生到结束的过程
2. 作用：确保数据的一致性、准确性、有效性
3. 事务操作

1.开启事务：begin;|start transaction;
2.开始执行事务中的1条或n条命令
3.终止事务：commit; # 命令都执行成功，提交到数据库，结束
rollback; # 存在sql命令执行失败，回滚到初始状态，结束

4. 事务四大特性

• 1、原子性：每个事务都必须视为一个整体，对于在一个事务中进行的操作必须全部执行成功或者失败回滚，不存在部分命令执行成功
• 2、一致性：
• 3、隔离性：在一个事物内所做的修改在执行commit;命令之前对其他的事物是不可见的
• 4、持久性：事务在执行commit;命令后，在其内所做的修改就会永久保存到数据库中
  注：事务只对表数据的增、删、改有效，对库、表的其他操作无效

E-R模型

1. 定义：E-R模型即实体-关系数据模型,用于数据库设计，反映了现实世界中存在的事物或数据以及他们之间的关系
2. 实体 属性 关系

• 实体：客观事物（用矩形表示）
• 属性：实体即客观事物的某种特性（用椭圆形表示）
• 关系：实体之间的关系（实体之中的关系有一对一、一对多、多对多关联）

3. 绘制：矩形代表实体，菱形代表关系、椭圆形代表属性
4. 创建

• 一对一

  create table c01(id int primary key,
                  name varchar(16))charset=utf8mb4;
  create table c02(id int unique,
                  hobby varchar(32),
                  foreign key(id) references c01(id))charset=utf8mb4;
  

• 一对多

  create table c01(id int primary key,
                  name varchar(16),)charset=utf8mb4;
  create table c02(id int,
                  hobby varchar(32),
                  foreign key(id) references c01(id))charset=utf8mb4;
  

• 多对多

  create table c01(id int primary key,
                  name varchar(16))charset=utf8mb4;
  create table c02(id int primary key,
                  hobby varchar(32))charset=utf8mb4;
  create table c03(id int primary key,
                  c01_id int,
                  c02_id int,
                  foreign key(c01_id) references c01(id),
                  foreign key(c02_id) references c02(id))charset=utf8mb4;
  # 查询
  select c01.name,c02.hobby from c01 inner join c03 on c01.id=c03.c01_id inner join c02 on c02.id=c03.c02_id;
  

MySQL调优

1. 存储引擎优化

• 读操作多使用MyISAM
• 写操作多使用InnoDB

2. 索引优化：在 select、where、order by常涉及到的字段建立索引
3. SQL语句优化

• 单条查询最后添加 limit 1，停止全表扫描
• where子句中不使用 != ,否则放弃索引全表扫描
• 尽量避免 NULL 值判断,否则放弃索引全表扫描
  优化前：select number from t1 where number is null;
  优化后：select number from t1 where number=0;
  注：在number列上设置默认值0,确保number列无NULL值
• 尽量避免 or 连接条件,否则放弃索引全表扫描
  优化前：select id from t1 where id=10 or id=20;
  优化后：select id from t1 where id=10 union all select id from t1 where id=20
• 模糊查询尽量避免使用前置 % ,否则全表扫描
  select name from t1 where name like “%c%”;
• 尽量避免使用 in 和 not in,否则全表扫描
  优化前：select id from t1 where id in(1,2,3,4);
  优化后：select id from t1 where id between 1 and 4;
• 尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段