数据库学习记录(基本概念)

1. 数据库基础

1.1表

• 左外连接与内连接的区别

  • 内连接：显示两个表符合条件的数据
  • 左外连接：左表显示全部，右表匹配条件则显示，不匹配以NULL值填充

• SQL执行顺序

  • from --> join...on --> where --> group by --> 聚合函数 --> having --> select --> distinct --> order by --> limit

1.2视图

• 伪表：数据来源于原表，本身不存储数据

• 对视图的增删改操作都是在操作原表数据

  • 视图最大的目的是为了辅助查询，所以一般不对视图做增删改操作。设置为只读视图

  • create view 视图名称 as select 语句

• 作用

  • 辅助查询，提升查询性能

  • 屏蔽敏感数据

  • 主分同步

1.3存储过程

• 概念：是一组为了完成特定功能的SQL语句集合。例如：转账

• 一般禁止使用存储过程，银行系统可能会用

1.4触发器

• 概念：一种特殊的存储过程

• 作用：在对表中数据做增删改操作之前或者之后的时机，触发一些事件【如：记录日志、数据校验等】

---

2. 存储引擎

• 2.1.InnoDB

  • 默认的存储引擎

  • 支持事务、支持物理外键【一般不用】

  • 天生支持行锁、手动支持表锁

  • 使用聚簇(集)索引：1.索引跟数据在同一个文件(表名.ibd)；2.只有主键索引是聚簇索引，其他索引是非聚簇索引

• 2.2.MyISAM

  • 不支持事务、不支持外键

  • 只支持表锁，不支持行锁

  • 使用非聚簇索引：索引跟数据不在一个文件

---

3. 索引

• 概念：一种排好序，能够提升查询性能的数据结构

• 分类：

  • 聚簇（集）索引【主键索引】：索引和行数据都在一个叶子节点上
  • 非聚簇（集）索引【非主键索引】：索引对应存储的数据是主键值

• 底层选择B+树的原因：

  • 非叶子节点和叶子节点存储的数据不一样，非叶子节点存储的索引和指针仅占14B，可以使用尽量深度低的树存储大量的数据，树的深度越低，查询的次数就越少，性能就越高

  • 叶子节点是一个双向链表，支持范围查询，也能够提升访问效率

• 主键索引

  • InnoDB引擎的表一定需要主键，如果不创建，数据库会自动创建并维护一个主键索引。创建主键的列建议是没有业务意义的列

  • 推荐使用主键自增：
    1、因为插入的数据始终会放在最后面，可以快速的找到插入的位置，无需做额外的开销，如移动数据的位置，旋转树等；
    2.如果不是自增，那么就无法判断要插入的数据具体是插入到索引树的哪个一位置，所以也无法判断树中数据的变化与树的旋转。那么就会带来不必要的开销。

• 非主键索引

  • 普通索引：在普通字段上建立索引

  • 唯一索引：给唯一列创建索引

  • 联合索引：给主键以外的多个列创建索引，也叫联合索引【组合索引

  • 全文索引：ES

• 创建索引的要求

  • 单表索引不超过5个

  • 联合索引的字段不超过5个

  • 经常增删改的字段不适合创建索引

  • 枚举值字段不适合创建索引

  • 不经常修改，经常查询的字段适合创建索引

  • 大长度的字段，可以设置前缀索引（为字段的前几个字符建立索引）

• 创建索引语法

  • 主键索引

    命名要求：pk_列名

  • 普通索引

    语法：create index 索引名称 on 表名(具体的列名)

    命名要求：idx_列名

  • 唯一索引

    语法：create unique index 索引名称 on 表名(列名)

    命名要求：uk_列名

  • 联合索引

    语法：create index 索引名称 on 表名(列1,列2)

    命名要求：idx_列1_列2