数据表中的键
数据表中的键 由一个或者多个属性组成
- 超键 能唯一标示元组的属性集 叫做超键 能唯一标示这一条记录的属性(一个或者多个)或者和其他属性的任意组合
- 候选键 不包含其他属性的超键
- 主键 从候选键中选一个。一张表 只能有一个主键
- 外键
- 主属性 包含在任一 候选键中的属性叫候选键
- 非主属性 与主属性相对
三范式
- 1NF 数据表中任何属性都是原子的 不可拆分
- 2NF 非主属性完全依赖于候选键 也就是 确定候选键就能唯一确定这些非主属性
- 3NF 任何非主属性都不传递依赖于候选键
BCNF
巴斯-科德范式 消除主属性对候选键的部分依赖或者传递依赖
反范式设计
- 满足范式设计 会出现表的层级越来越多 如果数据量大 需要多表关联 那么会影响查询效率
- 带来的问题 会对维护成本有所增加
- 适用场景 比如历史数据 或者 增删改的操作不多 的情况
索引
- 什么是索引 是一个数据结构 帮助数据库管理系统高效获取数据的数据结构 索引就像是一本书的目录 帮助我们快速查找数据库中的数据
- 当数据量少的时候 使用索引和不使用索引区别不大 当要建索引的属性重复率在10%以上是就不需要建索引 比如 性别列 如果加上索引 反而会使查询变慢
- 使用索引的同时也要注意数据的分布情况 当需要查询的数据量少的情况 使用索引 查询效率会大大增加
索引的种类
功能逻辑上分
- 普通索引
- 唯一索引
- 主键索引
- 全文索引 (Mysql 只支持英文的全文索引 一般使用专门的全文索引引擎 ElasticSearch)
物理实现方式分
- 聚集索引 叶子节点存储的直接就是数据 一张表只能有一个聚集索引
- 非聚集索引 叶子节点存储的是数据的位置 需要先找到位置 在找到真实的数据。
联合索引的最左侧原则
比如说 对userid 和 userName 做一个联合索引 如果用select userName from where userid=10 and userName=“abc” 和 select userName from where userid=10都是可以用上索引的 但是使用 select userName from where use rName=“abc” 就用不上索引了
索引的实现原理
- 什么是 B 树
平衡多路搜索树
每个节点都有M个子节点 每个子节点保存了关键字和子节点的指针 磁盘IO的次数比二叉树低 但总体上效率也不是很高 - 什么是B+树
非叶子节点的关键字同时也会存在在子节点中
非叶子节点仅用于索引 不保存数据记录 跟数据记录有关的都保存在叶子节点中 所有关键字都在叶子节点出现 叶子节点构成一个有序链表 叶子节点本身按照从小到大顺序链接 - B树和B+树的比较
B+树比B树更稳定 因为数据都存在叶子节点中 每次都只有访问到叶子节点才能拿到数据
B+树由于 叶子节点之间是采用链表的形式 所以范围查找的情况下效率也更高
通常情况下B+比B树更矮胖 磁盘的I/O 会更少
Hash 索引
-
Hash 索引是 键值通过Hash 映射到一个桶(bucket) 桶是一个能存储一条或者多条数据的存储单位 一个桶包含一个内存指针数组 桶中的每一行都会指向下一行形成链表结构
-
Hash 索引 和 B+ 索引区别
Hash 索引 不能进行范围查找 B+ 索引可以
Hash 索引 不支持联合索引的最左侧原则
Hash 索引 无序 B+ 有序
Hash 索引 不支持order_by
Hash 索引 不能进行模糊查询 -
对于等值查询来说 Hash 索引比 B+ 要快 但是当重复数据多的情况下 就会出现大量的Hash 冲突 也会降低效率
索引创建规律
-
需要创建索引的情况
- 字段是唯一的 可以创建唯一索引 或者主键索引
- 频繁使用的 where 条件
- Group By 或者 Order By 的字段
- 多表链接的时候 一定要有where 条件并且对where条件做索引 同时还需要对链接的字段做索引 并且链接的字段的类型必须相同
-
什么时候不需要创建索引
- where 条件里用不到的数据
- 表记录少
- 字段大量重复
- 频繁更新的字段不一定创建索引
-
什么时候索引会失效
- 如果对索引字段进行了表达式计算 比如 where user_id + 1 > 1000
- 对索引使用函数也会失效 比如WHERE SUBSTRING(comment_text, 1,3)=‘abc’
- where 条件中 如果 or 前边的使用了索引 后边的没有索引 索引会失效
- 联合索引最左侧原则
- 索引字段最好加上not null 约束
数据库的存储结构
- 页 数据库存储的基本单位是 页 一个页可以存储 一个数据页中存储一条或者多条记录 不管数据库读取一行还是多行记录最终都需要加载整页到内存中
- 区 Innodb中 一个区分配64个连续的页 一个页16kb 一个区是64*16=1M
- 段 由一个区或者多个区组成
- 表空间 存储一个或者多个段 一个段只能属于一个表空间
- 数据库由一个或者多个表空间组成 在innodb 中 分为独立表空间和共享表空间 共享表空间 就是多张表共用一个表空间 独立表空间就是一个表对应一个表空间
数据页的存储结构
- 文件头尾 保存两个指针指针指向上一个页和下一个页形成一个双向链表 同时通过hash算法实现数据完整性检验
- 记录部分 最大最小记录 用户记录 空闲空间
- 索引部分 对数据进行分组 重点是页目录 页目录中提供了二分查找的方式提高检索效率
磁盘I/O
一、缓冲池
- DBMS 为了让数据更快的被我们访问到 所以会申请一部分内存来作为数据缓冲池 并且会定时的清理掉不常用的数据 把常用的数据放在缓冲池中 如果修改的数据在缓冲池中 那么其实修改结果不会立刻同步到磁盘中，而是采用一种checkpoint的机制 写入磁盘
二、数据页加载方式
- 内存加载
- 随机读取 直接查找数据页
- 顺序读取 一种批量读取的方式
三、last_query_cost
mysql> SHOW STATUS LIKE 'last_query_cost';
查看查询成本 就是 访问了多少了数据页
索引
一、 窄索引和宽索引
- 一般情况下 窄索引就是包含列数小于等于2的
- 宽索引就是包含字段大于2的
- 每个非聚集索引都会存储主键
二、如果避免回表
- 什么是回表 是通过索引找到数据行之后 还要通过主键再去表中找到数据等情况
- where条件要加索引 group by order by的字段要加索引 着重考虑需要查找的字段是否要加上联合索引来避免回表 以此来提高查询效率
- 小总结： 在某个字段上增加了索引 相当于把这个字段以及主键 放在了B+树的叶子节点上，可以更快的访问到这个字段 以及找到主键 这是要想拿这条数据的完整信息就需要通过主键回表再查找
- 索引过多所带来的问题 1. 缓冲池占用过大 磁盘占用过大 2.每更新一条数据就要更新索引造成时间浪费3.造成索引变宽 让一页的存储数据量表少 增加页的访问成本
悲观锁和乐观锁
一、按照粒度进行划分
- 行锁
- 页锁
- 表锁
Innodb 支持 1 3
二、按照数据库管理的角度划分
- 共享锁也叫做读锁 在读取数据的时候会对数据加锁 防止读取数据的时候被修改，加上了共享锁之后这个表就变成了只读的了 要想修改必须释放锁 也可以对数据行加共享锁
- 排他锁 也是独占锁 写锁 只有获得锁操作的事务才能对数据进行查询和修改 其他事务不能对数据进行查询和修改
- 意向锁 给更大一级的空间示意里面是否已经上过锁 比如对某一行加上了排他锁 数据库回对更大一级的比如数据页加上意向锁 告诉别人已经有人获得了排他锁了
- 共享锁 容易产生死锁 比如两个事务都获得了读锁 但是 都没有释放
三、从数据库角度划分
- 乐观锁 适用于读多写少的情况下 认为同一时间又读又写的操作不多
- 悲观锁 需要利用数据库的锁
事务补充
一、事务带来的三个问题
- 脏读 读到了其他事务还没有提交的数据
- 不可重复读 第一次读和第二次读不一致
- 幻读 第一次读数据5条第二次 6条
二、事务的隔离级别
- 读已提交 解决脏读
- 可重复读解决不可重复读（mysql中的默认）
- 可串行化 解决所有
MVCC多版本并发控制
一、什么是快照读 什么是当前读
- 不加锁的select 都是快照读
- 加锁的select或者 增加删除修改都是当前读
二、MVCC的实现机制
- 事务版本号 没开启一个事务 都会从数据库中获得一个事务ID 也就是事务的版本号 通过ID 的大小就可以判断事务的时间顺序
- 行的隐藏列db_row_id 隐藏行ID 默认生成聚集索引 如果创建表的时候没有指定聚集索引 Innodb就会使用这个隐藏字段来作为聚集索引
- db_trx_id 操作这个数据的事务ID 也就是最后一个操作该数据的事务ID
- db_roll_ptr 回滚指针 指向记录的undo log 信息
三、undolog
保存行记录快照 通过回滚指针形成链表保存
四、Read View 如何工作 下面是重要属性
-
trx_ids 系统当前增在活跃的事务ID
-
low_limit_id 活跃事务中最大的事务ID
-
up_limit_id 活跃事务中最小的事务ID
-
creator_trx_id 创建这个ReadView的事务ID
如果现在有一个事务相访问一条数据 假设这条数据的事务ID是 A
-
如果A 小于 up_limit_id 说明事务已经提交 对读取操作可见
-
如果A大于low_limit_id 说明事务还没有提交对读取操作不可见
-
如果在up_limit_id和low_limit_id之间 那么事务可能还没有提交 此时要看A在不在trx_ids中如果在就对读操作不可见 如果不在就对读操作可见
五、InnoDB是如何解决幻读的
- 记录锁 对单行数据记录添加锁
- 间隙锁 可以锁定一个范围 但是不包含记录本身
- Next-Key 锁 可以锁定一个范围 同时包括记录本身
735
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
