数据库:​Mysql事务、索引优化、缓存及Redis

《Python服务端工程师面试宝典-PegasusWang》学习笔记,第六章:Mysql事务、索引优化、缓存及Redis
343阅读 · 2020-6-3 23:12发布

6.1 Mysql基础

什么是事务?

  • Transaction事务是数据库并发控制的基本单位。
  • 事务可以看作是一系列SQL语句的集合。
  • 事务必须要么全部执行成功,要么全部执行失败(回滚)。
  • 代码示例:
    session.begin()
      try:
          item1 = session.query(Item).get(1)
          item2 = session.query(Item).get(2)
          item1.foo = 'bar'
          item2.bar = 'foo'
          session.commit()
      except:
          session.rollback()
          raise
    

事务的ACID特性

  • 原子性(Atomicity):一个事务中所有操作全部完成或失败。
  • 一致性(Consistency):事务开始和结束之后数据完整性没有被破坏。
  • 隔离性(Isolation):允许多个事务同时对数据库修改和读写。
  • 持久性(Durability):事务结束之后,修改是永久的不会丢失。

事务的并发控制可能产生哪些问题?

  • 幻读(phantom read):一个事务第二次查出现第一次没有的结果。
  • 非重复读(nonrepeatable read):一个事务重复读两次得到不同结果。
  • 脏读(dirty read):一个事务读取到另一个事务没有提交的修改。
  • 丢失修改(lost update):并发写入造成其中一些修改丢失。

四种事务隔离级别

为了解决并发控制异常,定义了4种事务隔离级别。

  • 读未提交(read uncommitted):别的事务可以读取到未提交的改变。
  • 读已提交(read committed):只能读取已经提交的数据。
  • 可重复读(repeatable read):同一个事务先后查询结果一样。(Mysql InnoDB默认级别)
  • 串行化(Serializable):事务完全串行化的执行,隔离级别最高,执行效率最低。

如何解决高并发场景下的插入重复?

  • 使用数据库的唯一索引。(不允许某些字段重复,如果重复了数据库则抛出异常)
  • 使用队列异步写入。(写入队列一个一个执行)
  • 使用redis等实现分布式锁。(插入时持有锁,插入完释放锁)

乐观锁和悲观锁

  • 悲观锁是先获取锁再进行操作。一锁二查三更新select for update指的就是悲观锁。
  • 乐观锁先修改,更新的时候发现数据已经变了就回滚(check and set)。一般通过版本号或者时间戳实现。
  • 选择使用哪种锁,需要根据响应速度、冲突频率、重试代价来判断。

InnoDB和MyISAM两种引擎的区别

  • MyISAM不支持事务,InnoDB支持事务。
  • MyISAM不支持外键,InnoDB支持外键。
  • MyISAM只支持表锁,InnoDB支持行锁和表锁。
  • MyISAM支持全量索引,InnoDB不支持全量索引。

6.2 Mysql索引优化

什么是索引?

  • 索引是数据表中一个或者多个列进行排序的数据结构。
  • 索引能够大幅提升检索速度。
  • Mysql使用的索引结构是B+Tree。

什么是B-Tree?

整个查找结构的进化史

动画演示: https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

  • 线性查找:一个一个查找;实现简单;速度慢。
  • 二分查找:有序结构;实现简单;要求有序所以插入慢。
  • HASH:查询快;占用空间、不适合存储大规模数据。
  • 二叉查找树:插入和查询很快(log(n));无法存大规模数据,复杂度退化(如果只向一边插入则变成线性复杂度,既退化)。
  • 平衡树:解决个bst退化的问题;但是一个父亲只有两个孩子节点,节点多时导致树很高。
  • 多路查找树:一个父亲可以有多个孩子节点(度、阶);解决节点过多的时候树高不会特别深。
  • 多路平衡查找树(B-Tree):平衡树结合多路查找树的结果。

B-Tree的概念

  • 多路平衡查找树(每个节点最多m(m<2)个孩子,称为m阶或者度)。
  • 叶节点具有相同的深度。
  • 节点中的数据key从左到右是递增的。

什么是B+Tree

  • Mysql实际使用的B+Tree作为索引的数据结构。
  • 只有在叶子节点带有指向记录的指针(该方式可以增加树的度)。
  • 叶子节点通过指针相连,实现范围查找。
  • 建议阶的大小根据磁盘块大小来决定,这样操作系统在读取或缓存时会更加友好。

Mysql索引的类型

  • 普通索引(CREATE INDEX)
  • 唯一索引,索引列的值必须唯一(CREATE UNIQUE INDEX)
  • 多列索引(key的值由多个列组成)
  • 主键索引(PRIMARY KEY),一个表只能有一个
  • 全文索引(FULLTEXT INDEX),InnoDB不支持

什么时候创建索引?

  • 建表的时候需要根据查询需求来创建索引。
  • 经常用作查询条件的字段(where条件)。
  • 经常用作表连接的字段。
  • 经常出现在order by,group by之后的字段。

创建索引有哪些需要注意的地方?

  • 不要使用非空字段,Mysql很难对空值作查询优化,通常用默认值。
  • 选为索引的字段需要区分度高、离散度大。尽量不要有大量相同值。
  • 索引的长度不要太长(比较会耗费时间)。

索引什么时候失效?

  • 模糊匹配:以%开头的LIKE语句,模糊匹配时,B+Tree的key无法直接进行比较。
  • 类型隐转:出现隐式类型转换,传入的参数类型和表的类型不一致,key的类型不同无法直接比较。
  • 最左匹配:没有满足最左前缀原则(多列索引需要满足最左原则)。
  • 总结:B+Tree的key无法直接比较时,索引就会失效。

什么是聚集索引和非聚集索引?

  • 聚集还是非聚集指的是B+Tree叶节点存的是指针还是数据记录。
  • MyISAM所以和数据分离,使用的是非聚集索引。
  • InnoDB数据文件就是索引文件,主键索引就是聚集索引。
  • 非聚集索引:叶子节点存储的是指向数据的指针,指向表文件。索引文件和数据文件是两个文件。(可以查看系统中MyISAM对应的数据文件会有.MYD和.MYI两个文件)
  • 聚集索引:叶子节点存储了主键又存储了数据表里的内容。只有一个文件(可以查看系统中InnoDB对应的数据文件只有.ibd一个文件)。
  • 聚集索引和辅助索引:InnoDB的辅助索引(其他列),叶子节点存储主键的值,再通过主键找到对应数据。

如何排查慢查询?

  • 慢查询通常是缺少索引,索引不合理或者业务代码实现导致。
  • slow_query_log_file开启并且查询慢查询日志。
  • 通过explain排查索引问题。
  • 调整数据修改索引;业务代码层限制不合理访问(例如一次获取太多数据等)

6.3 SQL语句连接语句

  • 内连接(INNER JOIN):连个表都存在匹配时,才会返回匹配行。
  • 外连接(LEFT/RIGHT JOIN):返回一个表的行,即使另一个没有匹配。
  • 全连接(FULL JOIN):只要某一个表存在匹配就返回。

内连接

  • 将左表和右表能够关联起来的数据连接后返回。
  • 类似于求两个表的“交集”。
  • select * from A inner join B on a.id = b.id;

外链接

  • 左连接返回左表中所有记录,即使右表中没有匹配的记录。
  • 右连接返回右表中所有记录,即使左表中没有匹配的记录。
  • 没有匹配的字段会设置成NULL。
  • select A.id,B.id,A.val,B.val from A left join B on A.id = B.id;

全连接

A表: id | num ---|--- a | 5 b | 10 c | 15 d |10

B表: id | num ---|--- b | 5 c | 15 d | 20 e | 99

查询结果: id | num ---|--- a | 5 b | 15 c | 30 d | 30 e | 99

6.4 缓存及Redis

什么是缓存?为什么要使用缓存?

  • 常见的缓存有Redis和Memcached。
  • 缓存用于缓解关系数据库(常见的是Mysql)并发访问的压力:例如热点数据。
  • 减少响应时间:内存IO速度比磁盘快。
  • 提升吞吐量:Redis等内存数据库单机就可以支持很大并发。

Redis和Memcached主要区别?

对比参数 Redis Memcached
类型 1、支持内存
2、非关系型数据库
1、支持内存
2、key-value键值对形式
3、缓存系统
数据存储类型 1、String
2、List
3、Set
4、Hash
5、Sort Set
1、文本型
2、二进制类型
查询【操作】类型 1、批量操作
2、事务支持
3、每个类型不同的CRUD
1、CRUD
2、少量的其他命令
附加功能 1、发布/订阅模式
2、主从分区
3、序列化支持
4、脚本支持【lua脚本】
1、多线程服务支持
网络IO模型 1、单进程模式 1、多线程、非阻塞IO模式
事件库 自封装简易事件库AeEvent LibEvent事件库
持久化支持 1、RDB
2、AOF
不支持

简述Redis常用数据类型和使用场景?

  • String(字符串):用来实现简单的KV键值对存储,比如计数器。
  • List(链表):实现双向链表,比如用户的关注,粉丝列表。
  • Hash(哈希表):用来存储彼此相关信息的键值对。
  • Set(集合):存储不重复元素,比如用户的关注者。
  • Sorted Set(有序集合):实时信息排行榜。

Redis内置实现?

  • String:整数或者sds(Simple Dynamic String)
  • List:ziplist或者double linked list
  • Hash:ziplist或者hashtable
  • Set:intset或者hashtable
  • SortedSet:skiplist跳跃表
  • 深入学习请参考:《Redis设计与实现》

Redis有哪些持久化方式?

  • 快照方式:指定时间间隔把数据快照(状态)压缩保存在磁盘二进制文件中,dump.rdb。优点:有时间段的快照,缺点:时间段down机丢失时间间隔的数据。
  • AOF(Append Only File):每一个写命令追加到appendonly.aof中。缺点:aof文件大,恢复慢。
  • 可以通通过修改Redis配置实现持久化方式。

什么是Redis事务?

  • Redis事务是将多个请求打包,一次性、按序执行多个命令的机制。
  • Redis通过MULTI,EXEC,WATCH等命令实现事务功能。
  • Python实现方式:
    # redis-py,通过以下配置实现事务
      pipeline = conn.pipeline(transaction=True)
    

Redis如何实现分布式锁?

  • 使用setnx实现加锁,可以同时通过expire添加超时时间。
  • 锁的value值可以使用一个随机的uuid或者特定的命名。
  • 释放锁的时候,通过uuid判断是否是该锁,是则执行delete释放锁。

使用缓存的模式:

  • Cache Asie:同时更新缓存和数据库。
  • Read/Write Through:先更新缓存,缓存负责同步更新数据库。
  • Write Behind Caching:先更新缓存,缓存定期异步更新数据库。

如何解决缓存穿透问题?

  • 缓存穿透:大量查询不到的数据请求落到后端数据库,数据库压力大增。
  • 由于大量缓存差不到就去数据库取,数据库也没有要查的数据。
  • 解决方式:没有查到返回None的数据也缓存。
  • 解决方式:插入数据的时候删除响应缓存,或者设置较短的超时时间。

如何解决缓存击穿问题?

  • 缓存击穿:某些非常热点的数据key过期,大量请求打到后端数据库。
  • 热点数据key失效导致大量请求打到数据库增加数据库压力。
  • 解决方式一:分布式锁:获取锁的线程才能从数据库拉数据更新缓存,其他线程等待。
  • 解决方式二:异步后台更新:后台任务针对过期的key自动刷新。

如何解决缓存雪崩问题?

  • 缓存雪崩:缓存不可用或者大量缓存key同时失效,大量请求直接打到数据库。
  • 解决方式一:多级缓存:不同级别的key设置不同的超时时间。
  • 解决方式二:随机超时:key的超时时间随机设置,防止同时超时。
  • 解决方式三:架构层:提升系统可用性。监控、报警完善。