六大范式

  1. 所有字段明确不可再分
  2. 表明确不可分割

也就是说表中的非主键字段必须完全依赖主键
若某个字段没有完全依赖主键的关系，则应该拆分为两张表

  1. 非主键字段之间彼此独立不相互依赖

例如 工号 姓名 职位 工资 其中职位依赖于工号，工资依赖于职位

  1. 属性不能多值依赖于主键

即多对多关系建立中间表

  1. 表必须可以分解为较小的表，除非逻辑上拥有与原始表相同的主键

即表字段数量尽可能少

事务

四大特性

  1. 原子性 ：要么全部成功要么全部失败，不存在部分成功的状态
  2. 一致性 ：只有事务开启前状态和事务提交后状态可见，未提交状态对外不可见
  3. 隔离性 ：事务与事务之间相互隔离，互不影响

  4. 持久性 ：事务对数据库中数据的改变是永久性的

    隔离级别

  5. 读未提交 (Read Uncommitted)

事务在执行写数据时不允许其他事务一同写数据
事务在执行写数据时可以访问其他事务未提交的数据(可能出现脏读)

  1. 读已提交 (Read Committed)

事务在执行写数据时禁止其他事务访问未提交状态
事务在执行读数据时允许其他事务对读取的数据进行修改(可能出现不可重复读)

  1. 可重复读 (Repeatable Read)

事务在执行读数据时禁止其他事物对读取的数据进行修改
事务在执行过程中允许其他事务并发执行(可能出现幻读)

  1. 序列化执行 (Serializable)

事务禁止并发执行，即同一时刻只能有一个事务执行
Mysql InnoDB 存储引擎的默认隔离级别是 可重复读(Repeatable Read)

传播行为

  1. PROPAGATION_REQUIRED ： 如果当前没有事务就开启事务，否则加入当前事务
  2. PROPAGATION_NESTED ： 如果当前没有事务就开启事务，否则在当前事务内嵌套开启任务

(外部事物出现异常，内部事务也会回滚)

  1. PROPAGATION_SUPPORTS ： 如果当前存在事务就加入当前事务，否则以非事务执行，
  2. PROPAGATION_MANDATORY ： 如果当前存在事务就加入，否则报错

  3. PROPAGATION_REQUIRED _NEW ： 无论当前存不存在事务都开启新事务

    (内部事务完成后，外部事物出现错误也不会回滚)

  4. PROPAGATION_NOT_SUPPORTED ： 以非事务方式执行，如果当前存在事务就把事务挂起

  5. PROPAGATION_NEVER ： 以非事务方式执行，如果当前存在事务就抛出异常

    索引和SQL优化

    索引的分类

  6. 主键索引 ：数据列不允许重复，不能为null，一个表中只能由一个主键索引

  7. 组合索引 ：由多个列值组成的索引
  8. 唯一索引 ：数据列不允许重复，如果是组合索引，则列值的组合必须唯一
  9. 普通索引 ：基本类型的索引，可以重复，可以为null
  10. 聚簇索引 ：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行

  11. 非聚簇索引 ：将数据和索引分开存储，索引结构的叶子节点是指向数据的地址

    索引覆盖

    InnoDB中非聚簇索引中叶子节点存储的都是主键id，也就是说当我们要先先过非聚簇索引找到主键，然后通过聚簇索引找到主键所对应的数据。但并不是所有搜索都会回表查询，当我们只查找非聚簇索引依赖的列和主键时，通过非聚簇索引就可以检索出数据

    索引使用及索引级别

    使用 explain + 查询语句 根据结果中的 type 字段判断索引是否使用
    type值 ：

  12. system 表只有一行记录（等于系统表），这是const类型的特列

  13. const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引

system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
all Full Table Scan 将遍历全表以找到匹配的行

SQL优化

  1. 根据sql语句的执行顺序，条件筛选应尽量放在on上
  2. 合理利用索引
  3. 避免全字段查询，应当只查询自己需要的字段
  4. 避免索引失效(or , like ‘%…’)
  5. 强制使用索引 force index 索引名

    存储引擎InnoDB和MyISAM的区别

    | | InnoDB | MyISAM | | —- | —- | —- | | 事务 | 支持 | 不支持 | | 锁 | 支持行锁、表锁(行锁建立在索引上) | 支持表锁 | | 主键 | 必须有，不指定会默认生成隐藏列作为主键 | 可以没有 | | 自增 | 必须包含只有该字段的索引
    如果和其他列建立组合索引自动增长列必须是第一列 | 可以和其他字段一起建立联合索引
    引自动增长列必须是索引 | | 外键 | 支持 | 不支持 | | 索引 | 利用主键创建的索引为主索引(聚簇索引)
    在主索引之上创建的索引为辅索引(非聚簇索引)
    辅索引的叶子节点中保存的是主键 | 主索引和辅索引都为非聚簇索引
    索引结构的叶子节点上存储的都是指向数据行的地址 | | 优点 | 支持事务、并发量较大、适合大量的update | 适合大量的select | | 缺点 | 查询相对较慢 | 不支持事务、并发量小 |

1 人点赞

  • MySQL - 图1

1