文章目录
考点1:事务
1. 什么是事务(transaction):
- 事务是一系列sql语句的集合
- 事务是数据库并发控制的基本单位
2.事务的CIAD特性:
- 一致性(Consistency):事务开始和结束之后数据完整性没有被破坏
- 隔离性(Isolation):允许多个事务同时对数据库修改和读写
- 原子性(atomicity):要么全部成功,要么全部失败(常见操作:转账场景)
- 持久性(durability):事务结束之后,修改是永久生效的
3.事务并发控制四种异常
- 幻读(phantom read):一个事务第二次查出现第一次没有的结果
- 非重复读(nonrepeatable read):一个事务重复读两次得到不同结果
- 脏读(dirty read):一个事务读取到另外一个事务没有提交的修改
- 丢失修改(lost update):并发写入造成一些修改丢失
4.四种事务隔离级别
- 读未提交(read uncommitted):别的事务可以读取到未提交改变
- 读已提交(read committed):只能读取已经提交的数据
- 可重复读(repeatable read):表示一个事务中,对一个数据读取的值,永远跟第一次读取的值一致,不受其他事务中数据操作的影响.这也是 MySQL 的默认选项
- 串行化(serializable): 表示任何一个事务,一旦对某一个数据进行了任何操作,那么,一直到这个事务结束,MySQL 都会把这个数据锁住,禁止其他事务对这个数据进行任何操作
5.高并发场景下的插入重复如何解决
- 使用数据库的唯一索引
- 使用队列异步写入
- 使用redis等实现分布式锁
6.乐观锁,乐观锁
-
悲观锁就是先获取锁再进行操作.一锁二查三更新 select for update
-
乐观锁先修改,更新的时候发现数据已经变了就回滚(check and set)
- 乐观锁一般通过版本号或时间戳来实现
-
需要根据响应速度,冲突频率,重试代价来判断使用哪一种
3.InnoDB 与 MyIsAM的区别
- MyISAM不支持事务,InnoDB支持事务
- MyISAM不支持外键,InonoDB支持外键
- MyIsAM支持表锁,InnoDB支持行锁和表锁
- MyIsAM支持全文索引
考点2:索引
1.什么是索引
- 索引是数据库表中一个或多个列进行排序的数据结构
- 索引能够大幅度提升检索速度
- 创建,更新索引本身也会耗费空间和时间的
2.为什么要使用B-Tree
- 线性查找:一个一个找,实现简单,但是太慢了
- 二分查找: 有序,简单,要求是有序的,插入特别慢
- hash : 查询快,占用空间,不适合存储大规模数据
- 二叉树:插入和查询很快(log(n)),无法存大规模数据,复杂度退化
- 平衡树:解决bst 退化的问题,树是平衡的
- 多路查找树:一个父亲多个孩子节点,节点过多树高不会特别深
- 多路平衡查找树:B-Tree
3.什么是B-Tree
- 多路平衡查找树(每个节点最多m(m>=2个孩子,称为m阶或者度))
- 叶节点具有相同的深度
- 叶节点中的数据key从左到右是递增的
4.什么是B+Tree
-
B+Tree是B-Tree的变形
-
Mysql实际使用的B+Tree作为索引的数据结构
-
只能在叶子节点带有指向记录的指针(为什么可?可以增加树的度)
-
叶子节点通过指针相连,为什么?实现范围查询
5.Mysql索引类型
- 普通索引(create index)
- 唯一索引,索引列的值必须唯一(create unique index)
- 多列索引
- 主键索引(primary key),一个表只能有一个
- 全文索引(fulltext index),innnodb不支持
6.什么时候创建索引
- 经常用作查询条件的字段(where条件)
- 经常用作表连接的字段
- 经常出现order by,group by之后的字段
7.创建索引有哪些需要注意的
- 非空字段not null,mysql很难对空值作查询优化,很多建表规范要求索引字段有默认值
- 区分度高.离散度大,作为索引的字段尽量不要有大量的相同值
- 索引长度不要太长(比较消耗时间)
- mysql最多支持16个字段组成的组合索引
8.索引什么时候失效
记忆口诀:模糊匹配,类型隐转,最左匹配
-
模糊匹配
- 以%开头的like语句,模糊搜索
- 如果筛选的是一个范围,无法精确定位也相当于中断索引
-
组合索引没有满足最左前缀原则
- 组合索引的多个字段是有序的,遵循左对齐的原则
- 使用关键字or的,只有or的前后表达式中都建有索引,查询才能生效
-
出现隐式类型转换(在python这种动态语言查询中需要注意)
-
mysql最多支持16个字段组成的组合索引
参考:
9.聚集索引和非聚集索引
- 聚集还是非聚集指的是B+Tree叶节点存的是指针还是数据记录
- myISAM索引和数据分离,使用的是非聚集索引(非聚集索引也称辅助索引,先找到主键以后再根据主键找到数据)
- InnoDB数据文件就是索引文件,主键索引就是聚集索引
10.如何排查和消除慢查询
慢查询通常是缺少索引,索引不合理或者业务代码实现导致
- slow_query_log_file开启并且查询慢查询日志
- 通过explain排查索引问题
- 调整数据修改索引;业务代码层限制不合理访问
考点3 SQL语句
- 内连接:两个表都存在匹配时才会返回匹配行
- 外连接(left/right join):返回一个表的行,即使另外一个没有匹配
- 全连接(full join):只要一个表存在就返回
参考
考点4 数据库性能优化
慢查询通常是缺少索引,索引不合理或者业务代码实现导致
设计
- 整数类型比文本类型占用的空间更少(int只占用3字节,mediumint只占用3字节,四百万的数据,后者节约25%)
- 合理增加冗余字段,减少表的连接(冗余字段需要不经常修改,且冗余字段不可或缺,四百万的数据连表查询6.64,单表数据查询6.38)
- 在大表查询中将查询高的字段和查询低的字段拆分成不同的表
- 在设计字段时,在业务允许的情况下尽量使用非空字段,减少判空的开销,节省存储空间(每个字段1bit),更容易创建索引
查询:
- 使用explain对查询语句进行分析,排查索引问题,为合适的字段创建索引
- slow_query_log_file开启并且查询慢查询日志
- 调整数据修改索引;业务代码层限制不合理访问
架构:
- 数据库读写分离
考点5 数据表三范式:
第一范式:所有的字段都是基本数据字段,不可进行拆分(强调的是列的原子性)
第二范式:所有的非主键字段必须完全依赖主键,不能只依赖主键的一部分(主键可能是由多个字段组成)
例如: 如果订单表中主键是(OrderID,ProductID),Quantity完全依赖于主键,而 UnitPrice,ProductName只依赖于ProductID
第三范式:不能存在依赖于非主键字段的字段
例如:如果订单表中主键是 OrderID,CustomerID完全依赖于主键,而CustomerName直接依赖的CustomerID而不是直接依赖于主键