Mysql总结

考点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个字段组成的组合索引

参考:

1.15个必知的 MySQL 索引失效场景，别再踩坑了

9.聚集索引和非聚集索引

• 聚集还是非聚集指的是B+Tree叶节点存的是指针还是数据记录
• myISAM索引和数据分离,使用的是非聚集索引(非聚集索引也称辅助索引,先找到主键以后再根据主键找到数据)
• InnoDB数据文件就是索引文件,主键索引就是聚集索引

10.如何排查和消除慢查询

慢查询通常是缺少索引,索引不合理或者业务代码实现导致

• slow_query_log_file开启并且查询慢查询日志
• 通过explain排查索引问题
• 调整数据修改索引;业务代码层限制不合理访问

考点3 SQL语句

• 内连接:两个表都存在匹配时才会返回匹配行
• 外连接(left/right join):返回一个表的行,即使另外一个没有匹配
• 全连接(full join):只要一个表存在就返回

参考

1.sql语句练习50题(Mysql版)

考点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而不是直接依赖于主键