您的位置: 首页  >  文章  >  MySQL一些概念

MySQL一些概念

分类: 文章 • 2023-11-08 15:56:46

数据库设计三大范式

  • 1NF：数据表每一列不可拆分（原子性）
  • 2NF：每一列都与主键相关（只描述一件事）
  • 3NF：每张表都只与主键直接相关

Left join/Right join
使用事务保持数据一致性和完整性
Begin：
Commit
使用外键
在频繁进行排序分组的列上建立索引

事务

  1. 原子性：事务包含的操作要么全部成功，要不全部失败回滚
  2. 一致性：事务执行前后处于一致性状态
  3. 隔离性：当多个用户并发访问数据库时，数据库为每个用户开启的事务不被其他事务的操作所干扰，多个并发事务之间要相互隔离
  4. 持久性：事务一旦提交了，改变就是永久性的

MVCC（多版本并发控制）：保存数据的多个版本来实现并发控制
当需要更新某条数据时，不会立即用新的数据覆盖原始数据，而是创建该记录的一个新的版本。（MySQL是悲观锁机制）

四种隔离级别

  1. Serializable：串行化：不会幻读，重复度，脏读
  2. Repeatable：可重复读：会幻读
  3. Read Committed：读已提交：仅能读取到已经提交的数据（默认），会不可重复读，会幻读
  4. Read Uncommitted：读未提交

事务并发调度问题

  1. 脏读：读取未提交，使用version
  2. 不可重复读：读取之前提交的，更改的数据
  3. 幻读：读取之前提交的整批数据，新增与删除的数据
隔离级别 脏读 不可重复读 幻读
读未提交 会 会 会
读已提交 不会 会 会
可重复读 不会 不会 会
串行化 不会 不会 不会

数据库的锁：

  1. 共享锁：读锁，阻止其他事务修改表数据
  2. 排他锁：写锁，阻止其他事务读写
  3. 行级锁(开销大，枷锁慢，会出现死锁，粒度小，并发度高)，表级锁(开销小，加锁块，不会出现死锁，粒度大，并发度低)，页锁
  4. 意向锁：先申请意向共享锁，成功之后申请行锁：判断表是否被其他事务用表锁锁定；发现表上有共享意向锁，表明表中有些行被锁定了，因此写锁会被阻塞

*协议

  • 一级*协议：如果事务有写操作，在读操作之前加排他锁，事务结束后释放
  • 二级*协议：事务读取之前加上共享锁，读完之后释放
  • 三级*协议：事务读取数据之前加共享锁，事务完成以后释放

死锁-相互等待

  • 一次*法
  • 顺序*法
  • 超时等待

MySQL数据库引擎

MyISAM InnoDB
不支持事务 支持
不支持外键 支持
支持全文索引 5.6以后版本支持
只有表锁

MySQL主备模式
主服务器会将每次改动写入到日志中
从服务器会读取日志并执行一遍
MySQL的特点：

  • 核心完全多线程，支持多处理器
  • 能够工作在不同的平台上
  • 通过高度优化的类库实现SQL函数库并能够快速，查询初始化以后没有内存分配和内存泄漏

索引

索引的存储类型：B树索引(Innodb,MyISAM)和哈希索引(MEMORY)

  1. 普通索引：不加任何条件限制的索引，可以创建在任何数据字段上
  2. 主键索引：根据主键构成的索引，不允许重复，不允许空值
  3. 唯一索引：限制索引值必须是唯一的（自动索引-完整性约束时创建的索引，设置为主键或者为唯一时和手动索引）
  4. 全文索引：在数据类型为CHAR，VARCHAR，TEXT上，不区分大小写，为二进制数据类型时区分大小写
  5. 多列索引：多个字段构成的索引，只有查询条件使用了锁关联字段中的第一个字段，多列索引才会使用(最左前缀原则)

BTree索引 度为2d，高为h

  • 每个叶子节点的高度一样，等于h
  • 每个非叶子节点由n-1个key和n个指针组成，d<=n<=2d，节点两端一定是key
  • 叶子节点的指针都是null
  • 非叶子节点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据
  • 查找复杂度为hlog(n)

B+Tree索引 d为树的度数，h为树的高度

  • B+Tree的非叶子节点不存储数据，只存储键值
  • B+Tree的叶子节点没有指针，所有的键值都会出现在叶子节点上，且key存储的键值对应data数据的物理地址
  • B+Tree的每个非叶子节点由n个键值key和point组成
对比
  1. 磁盘读写代价更低：尽可能的减少磁盘IO来加快读取速度
  2. 查询速度更稳定：B+Tree非叶子节点不存储数据，因此所有的数据都要查询至叶子节点，叶子节点的高度是相同的

MyISAM-非聚簇索引

  • 非聚簇索引的主索引和辅助索引(如果查询条件不是主键)几乎一样，只是主索引不允许重复，不允许空值，叶子节点的key都存储指向键值对应的数据的物理地址
  • 非聚簇索引的数据表和索引表是分开存储的
  • 非聚簇索引的数据是根据数据的插入顺序保存，因此非聚簇索引更适合单个数据的查询
    图

MySQL一些概念

InnoDB-聚簇索引

  • 聚簇索引的主索引的叶子节点存储的是键值对应的数据本身，辅助索引的叶子节点存储的是键值对应的数据的主键键值，因此主键的值长度越小越好，类型越简单越好
  • 聚簇索引的数据和主键索引存储在一起
  • 聚簇索引的数据是根据主键的顺序保存，因此合适按主键索引的区间查找。
  • InnoDB表是基于聚簇索引建立的，辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。过长的主索引会导致辅助索引变得过大
    图

MySQL一些概念
MySQL一些概念

char和varchar

  • char定义从列的长度是固定的，右边以空格填充，检索时删去空格，不进行大小写转换(0-255)
  • varchar变长(0-65535,除去起始符和结束符最大65532)，保存时只保存需要的字符数，然后一个字节来保存大小，超过255时需要两个字节

相关推荐