面试知识点整理之存储篇

面试时所必要，或者被问到的问题的记录，如果有理解错误或者疏漏，烦请指出，万分感谢：）

MyISAM和InnoDB的理解与区别

  • 两者采用的都是B+树，MyISAM是非聚集索引，即索引文件和数据文件是分离的，data域保存的是数据文件的指针。索引表只保存数据记录的地址；InnoDB是聚集索引，因此必须需要主键，data域保存完整的数据记录。但是通过辅助索引需要两次查询，因此主键不应该过大，否则会导致其他索引也很大。
  • MyISAM只支持表锁，因此加锁开销小，不会出现死锁，但并发性能差；InnoDB可以支持行锁与表锁，但是仅仅在使用索引时会使用行锁
  • MyISAM不支持事务和崩溃后的安全恢复，但是InnoDB支持。
  • MyISAM不支持外键，InnoDB支持
  • InnoDB不保存表的具体行数，执行select count(*) 时需要全表扫描，而MyISAM使用变量保存了整表行数，执行select count(*) 速度很快。

由于MySQL默认认为写优先级高于读优先级，因此当大量读写场景时，MyISAM的锁调度总是写进程先获得锁，因此它并不适合有大量更新操作和查询操作应用的原因，可能会造成查询操作很难获得读锁,从而可能永远阻塞。

Mysql索引

MyISAM是非聚集索引，InnoDB是聚集索引，因此在使用InnoDB作为引擎设计表时，不建议使用过长的字段作为主键(以免辅助索引过大)，也不建议使用非单调的字段作为主键(造成树的频繁调整)。

因此索引会降低增删改查的速度。

索引的优化与失效

  • 最左匹配原则，MySQL会一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配(>=，<=是可以的)。因此尽管没有按照索引的顺序来写，MySql仍然会自动优化，因此in、 =的顺序并不重要
  • 选择区分度高的列作为索引，即COUNT(DISTINCT col)/COUNT(*)
  • 索引列不能参与计算
  • 尽可能扩展索引，而不是新建立索引。比如表中已经有了a的索引，现在要(a,b)的索引，那么只需要修改原来的索引即可。
  • 尽量使用覆盖索引，而不是使用select *
  • 索引字段不要使用is null或者is not null，会变成全表扫描
  • 索引字段使用like以通配符开头，即'%xxx'时，会导致索引失效（以通配符结尾并不会导致失效），如果使用%xxx%时，请使用覆盖索引。
  • 索引是字符串，查询时不加单引号，会导致索引失效。
  • 索引字段使用or时，会导致索引失效
  • 表数据较少时，可以全表扫描，不需要建立索引

EXPLAIN

[MySQL 性能优化神器 Explain 使用分析](https://segmentfault.com/a/1190000008131735)

使用EXPLAIN命令可以对select语句进行分析，以实现针对性优化。

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

字段包括有：

  • id: SELECT查询的标识符，每个 SELECT都会自动分配一个唯一的标识符.
  • select_type: SELECT查询的类型，常用的是SIMPLE
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型，性能从高到低为：
    • system：表中只有一条数据
    • const：针对主键或唯一索引的等值查询扫描，最多只返回一条数据
    • ef_ref：多表join查询，前表每一个结果都只能匹配后表的一条记录
    • ref：多表join查询，针对非主键或者非唯一索引，或者使用了最左前缀规则
    • range：使用索引范围查询，通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作，此时key_len使用到的索引最长的那个
    • index：全部索引进行扫描
    • all：全表扫描
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引
  • key_len：使用的索引字节数
  • ref: 哪个字段或常数与key 一起被使用
  • rows: 显示此查询一共扫描了多少行， 这个是一个估计值
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息，例如using filesort(需要优化)/using index/using temporary(需要优化)

group by的本质是先分组后排序，因此会使用using filesort，可以使用order by null禁用排序进行优化。

锁

数据库两大神器【索引和锁】

  • 对于UPDATE、DELETE、INSERT语句，**InnoDB**会自动给涉及数据集加排他锁(X)
  • **MyISAM**在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预

锁根据粒度可以分为：

  • 表锁：开销小，加锁快，不会出现死锁，发生冲突概率高，并发低。表锁可以分为读锁与写锁
    • 读锁：只能加读锁，不能加写锁
    • 写锁：不能加读锁与写锁
  • 行锁：开销大，加锁慢，会出现死锁，发生冲突概率低，并发高。InnoDB实现了两种类型的行锁：
    • 共享锁：也称为读锁，多个客户可以读取同一个资源，但是不允许修改
    • 排他锁：也称为写锁，会阻塞其他的写锁和读锁。

InnoDB支持表锁与行锁(基于索引的)，但是MyISAM支持表锁。默认情况下，InnoDB中select是不加任何锁的，可以通过显示加锁：

  • 共享锁：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
  • 排他锁：SELECT * FROM table_name WHERE ... FOR UPDATE。

为了实现多粒度锁机制，InnoDB还有两种意向锁，都是表锁，也是自动添加的：

  • 意向共享锁(IS)：事务在加共享锁之前，必须先取得该表的IS锁
  • 意向排他锁(IX)：事务在加排他锁之前，必须先取得IX锁

MVCC和事务隔离级别

MVCC多版本控制，一般是读写不阻塞的，可以提升并发性能，它是通过数据快照的方式来实现的。隐含的增加两列，一个保存了行的创建时间，一个保存行的过期时间。对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，使得读取时可以完全不加锁。基于MVCC的实现基本上免除了大部分的锁等待问题。

事务隔离级别是通过锁来实现的，只是隐藏了加锁的细节。

事务具有四大特性(ACID)，即原子性，一致性，隔离性和持久性。

如果不考虑隔离性，可能会出现：

  • 脏读：读到其他事务未提交的数据
  • 不可重复读：读到其他事务提交的数据
  • 幻读：读到其他事务提交的数据，但是针对的是一批数据整体，例如针对同一查询条件，多出几行。

因而MySQL提供了四种隔离级别：

  1. Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
  2. Repeatable read (可重复读)：可避免脏读、不可重复读的发生(Innodb和Xtradb通过MVCC解决幻读的问题)。
  3. Read committed (读已提交)：可避免脏读的发生(只能读取其他事务已经提交的结果，因而会带来不可重复读)。
  4. Read uncommitted (读未提交)：最低级别，任何情况都无法保证。(事务中的修改，即使没有提交，对其他事务也是可见的，很少使用）

默认情况下，MySQL采用的是RR级别。MVCC只和隔离级别RR和RC相兼容：对于RR，需要读取创建版本小于等于当前版本的最新的数据记录；对于RC，只要读取创建版本最新的数据记录。

乐观锁和悲观锁

  • 乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。

  • 悲观锁是数据库层面加锁，会阻塞等待锁。

因此MVCC可以看做是一种解决读写冲突的无锁并发控制，乐观并发控制是用来解决写写冲突的无锁并发控制。

间隙锁

当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，也会加间隙锁。它可以保证，在事务未提交之前，其他事务不能插入满足其锁定条件的任何记录。例如：

Select * from  emp where empid > 100 for update;

会对大于100的所有行加上锁，即使实际上记录只到101。

死锁的避免

一般Mysql会通过回滚解决死锁的问题，但是我们仍然可以避免：

  • 以固定顺序访问表和行
  • 大事务拆小
  • 一个事务中做到一次锁定所需要的资源
  • 降低隔离级别

优化锁性能

  • 尽量使用较低的隔离级别
  • 设计索引使得加锁更精确
  • 选择合理的事务大小
  • 要修改数据的话，最好直接申请排他锁
  • 不同程序访问表时，尽量以相同的顺序访问各表
  • 尽量用相等条件访问数据，避免间隙锁的影响
  • 除非必要，查询时不要显式加锁
  • 特定的事务可以使用表锁来减少死锁的可能

数据库大表优化

常见优化措施：

  • 限定数据范围，禁止不带任何限制数据范围的查询
  • 读/写分离
  • 使用缓存
  • 垂直分区，根据功能分表分库，可以使得行数据变小，减少I/O，简化表的结构，易于维护，但可能会有冗余列
  • 水平分区，比如可以根据用户的地域或者名字等将数据分散到不同表或者库

redis与memcached的区别

  • redis支持丰富的数据类型
  • redis支持持久化，发布订阅等附加功能
  • redis拥有集群模式
  • memcached是多线程，非阻塞IO;redis使用单线程，IO多路复用

redis常见数据结构

  1. String
  2. Hash
  3. List
  4. Set：可以提供交集、并集、差集等操作
  5. Sorted Set：可以实现排行榜等功能

redis过期删除和内存淘汰

过期删除采用定期删除+惰性删除。

内存淘汰有6种策略：

  • volatile-lru：从已设置过期的keys中挑选最少使用的数据淘汰
  • volatile-ttl：从已设置过期的keys选择马上要过期的数据淘汰
  • volatile-random：从已设置过期的keys中随机选择数据淘汰
  • allkeys-lru：从所有keys中选择最少使用的数据淘汰
  • allkeys-random：所有keys中随机释放
  • no-enviction：不删除，内存不足时直接报错

redis持久化

持久化有两种方式：RDB和AOF。

RDB是默认使用的方式，根据数据库中的字典生成文件；而如果打开了AOF，则默认使用AOF恢复数据库，AOF是将更改数据库的命令写入。它有三种方式：

  • appendfsync always：每次有修改都写入
  • appendfsync everysec：每秒钟同步一次
  • appendfsync no：由操作系统决定

AOF重写是指，当AOF文件过大时，产生新的AOF文件，它是根据现有数据的键值对实现，无须对现有的AOF文件进行读写分析，而是将需要通过多条命令实现的某个键值对状态，尽可能使用更短的命令实现。

缓存雪崩和缓存穿透、缓存击穿

缓存雪崩是指同一时间缓存大面积失效(或者缓存宕机)，导致请求落在数据库上，使得数据库短时间内接受大量请求崩掉。

解决方案：

  • 使用集群保证redis服务可用性
  • 过期时间加上随机值，避免缓存集体失效
  • 针对缓存雪崩发生时，对数据库采用限流，防止其崩掉

缓存穿透：因为大量请求redis中不存在的key导致，缓存不起作用所有请求落在数据库上

解决方案：

  • 布隆过滤器，拦截非法key
  • 即使空结果，也进行缓存，但设置短的过期时间

缓存击穿：大量查询一个正好失效的key，导致瞬时DB请求量大

解决方案：

  • 互斥锁

redis的并发竞争问题

多个进程同时操作一个key时，需要使用分布式锁。可以使用redis，也可以使用zookeeper或者mysql实现。

针对单机情况，可以使用事务机制。

如何保证缓存与数据库双写一致

如何用消息系统避免分布式事务

看了知乎，有如下机制：

  1. 等待redis中的数据自动过期，但是会有过期时间这么长一段时间的数据不一致
  2. 使用代码更新DB，然后删除redis中的key，但是也会有一小段不一致的时间，可以满足绝大部分场景
  3. redis中的数据不过期，只要DB更新，就采用背景任务更新redis，访问者永远只访问redis。不一致的时间取决于，如果是定时更新任务，那么就是更新任务的时间间隔；如果是队列模式，取决于队列的产生和消费的延迟
  4. 针对3的变体，用户写往DB写入，但是永远只读redis，仍然由DB更新redis

综上，总是会有一段时间不一致。即CAP问题，如果需要保证强一致问题CP，那么需要牺牲A:

  1. 不使用redis，只使用单点DB
  2. 使用分布式协议，比如2PC、3PC，或者分布式锁。

所以，要么接受最终一致，要么要求强一致，在分布式情况下就要牺牲A。

CHAR和VARCHAR

MySQL的char和varchar针对空格的处理

  • char：存储定长数据，最大只能存储255个字符。CHAR字段上的索引效率级高，必须在括号里定义长度，可以有默认值，比如定义char(10)，那么不论你存储的数据是否达到了10个字符，都要占去10个字符的空间（自动用空格填充），且在检索的时候查询条件后面的空格会被移除(左边的会保留)，存储的数据也会移除右侧的空格。
  • varchar：存储变长数据，但存储效率没有CHAR高，必须在括号里定义长度，可以有默认值。保存数据的时候，不进行空格自动填充，而且如果数据存在空格时，当值保存和检索时尾部的空格仍会保留。另外，varchar类型的实际长度会长1-2个字节(用来指示保存值的字节数)，如果小于等于255个字节，使用1个字节，大于255个字节，使用2个字节。存储时VARCHAR并不会移除左右的空格，但是查询时会移除查询条件右边的空格。
  • text：存储可变长度的非Unicode数据，最大长度为2^31-1个字符。text列不能有默认值，存储或检索过程中，不存在大小写转换，后面如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是你插入数据的时候，超过你指定的长度还是可以正常插入。

MySQL事务的实现

『浅入深出』MySQL 中事务的实现

Mysql事务实现原理

原子性：需要在异常发生时进行回滚，因此需要依靠回滚日志(undo log)。他有如下特点：

  • 每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上
  • 所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete，update的逆向为update等

持久性：使用重做日志(redo log)实现的，它包括两个部分：

  • 内存中的重做日志缓冲区
  • 磁盘上的重做日志文件

这样一来，尝试对事务进行修改时，会经历以下步骤：

  1. 数据从磁盘读入内存
  2. 更新内存中缓存的数据
  3. 生成重做日志写入重做日志缓冲区
  4. 提交时，将缓冲区中的内容刷新到磁盘的重做日志文件
  5. 将内存中的数据更新到磁盘

因此，重做日志是用来记录已经成功提交事务的修改信息(在日志刷新到磁盘中之后，在数据从内存更新到磁盘中之前)，即使宕机了，也可以在读取redo log之后恢复最新数据。

重做日志是以512字节块的形式存储的，块大小与磁盘扇区大小相同。重做日志可以保证原子性。

隔离性：由锁+MVCC进行实现。

drop、delete和truncate

  1. drop直接删掉表，将表所占有的空间全部释放掉
  2. delete每次从表中删除一行，并且将操作作为事务记录保存在日志中以便进行回滚，不会减少表或者索引所占的空间
  3. truncate删除的是表中的数据，表和索引占用的空间会恢复初始大小
  4. truncate和drop都无法回滚
  5. 如果需要保留id的计数，使用delete
  6. 如果具有外键约束，不能使用truncate

局部性原理

硬盘的读写原理

磁盘读取过程：

  1. 磁头寻找柱面，所耗费的时间即寻道时间
  2. 目标扇区旋转到磁头下，所耗费的时间即旋转时间
  3. 数据在磁盘与内存之间的实际传输时间

遵循从上到下，从外到内，按照柱面进行的规则。

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百万分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理，即``CPU访问存储器时，无论是存取指令还是存取数据，所访问的存储单元都趋于聚集在一个较小的连续区域中：

  • 时间局部性：如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。
  • 空间局部性：在最近的将来将用到的信息很可能与现在正在使用的信息在空间地址上是临近的。

预读的长度一般为页(page)的整倍数。

数据库范式

一看就懂就懂的数据库范式介绍（1NF,2NF,3NF,BC NF,4NF,5NF)

如何解释关系数据库的第一第二第三范式？ - 王红波的回答 - 知乎

第一范式：原子性，每个字段都不可再分割

第二范式：唯一性，有主键标识其唯一性，非主属性不能部分依赖码

第三范式：非主键字段不能相互依赖，每一列只与主键有直接关系，不存在传递依赖

存储过程与触发器

只有调用的时候才会执行存储过程，而触发器是一种特殊的存储过程，主要通过事件执行触发而被执行。

内连接与外连接

内连接(inner join，inner可以省略)：只有某个条件是左表与右表中相同，才会保留结果

外连接：

  • 左连接：以左表为主，保留左表所有行，右表不能匹配的置为空
  • 右连接：与左连接相反

Mysql架构

Mysql架构包括三层：

  1. 客户端的连接管理与认证：每个客户端在服务器中拥有一个线程，服务器会负责缓存线程，并且基于用户名和密码对客户端进行认证
  2. 服务器层：解析查询，分析、优化，缓存(select查询)，内建函数。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。
  3. 引擎层：负责存取数据，服务器层通过API与其通信。
posted @ 2020-10-25 16:04  yuyinzi  阅读(700)  评论(0)    收藏  举报