面试整理-mysql

发表于 | | 次阅读

摘要

  • 主要是总结MySQL相关得知识点，做一个知识图谱
  • MySQL事务，索引，亿级数据量如何处理。

mysql事务

  • MySQL的数据库引擎包括两种，innodb myisam

    • innodb 支持事务，行锁，根据索引条件检索数据使用行锁，支持外键，更多的适用于更新插入的情况中。支持外键
    • myisam 不支持事务，表锁（独占锁和共享锁），读与读之间使用共享锁，读与写之间使用独占锁。==>不支持外键

  • 事务的隔离性，

    • 前提问题：
      • 脏读：一个事务读取了另一个事务未提交的数据，但是读取了这个数据后，另外那个事务回滚了。
      • 不可重复读：一个事务多次读取一个数据，但是另外一个事务读取一次后进行了修改（已提交），导致两次的数据不一致
      • 幻读：指代一个数据整体，事务1多次读一个表，查找所有小于3的数据，一共四条，但是事务2又插入了一条并提交，事务1的下一次处理就发现又多了一条，、不同在于数据整体
    • 进而出现了四种隔离级别
      • 读未提交：别人事务未提交，我可读它未提交数据
      • 读已提交：我只能读取已提交的数据
      • 可重复读：我的事务未完成前，不去读其他人提交的数据，保证和第一次读取的数据一样，mysql默认的隔离等级
      • 串行化：只有我的事务完成了，你们才能处理数据。
  • 事务的隔离实现：
    • 视图和锁实现：
      • 可重复读：事务开始的时候就重建一个读视图使用历史的undo.log mvcc（并发控制）
      • 读已提交：每条sql创建一个视图，写加锁，undo.log 查找未修改的时候值
      • 读未提交，直接返回最新值，事务执行的过程中会修改到记录中，但是可能会回滚 读不加锁，写加行级排他锁，不存在脏写
      • 串行处理,直接加锁。表级读写锁。

MySQL索引

  • 提升查找性能，但是对于亿级记录的表单效果仍然不行了。索引创建的
  • 索引的类型：
    • 唯一索引： 可为null
    • 主键索引
    • 组合索引： 最左原则，（a,,)会使用，（，发，） 不会使用索引
    • 普通索引
    • 全文索引：仅myisam支持，
  • myisam==>非聚簇索引—–索引文件仅保存地址，索引文件和数据文件是分开的。===> 辅助索引和主键索引没有区别，只是使用允许重复
  • innodb===> 聚簇索引—数据文件本身便是索引文件—叶节点包括了完整的数据记录
  • InnoDB 要求表必须有主键(MyISAM 可以没有)。如果么有显式指定会自动选择，此外，主键尽量的选择自增主键，B树的维持更加的方便
    • 自增主键可选数据类型，bigint==> 如果达到极限了会报错的，不慌 =====> 对比uuid
    • 主键索引很高效，此外会有辅助索引，其数据域存储的是主键，再使用一次主键检索
  • 索引的底层数据结构：
    • B+树
    • hash
    • R+树
    • 对比：B+树，B树，平衡二叉树
      • 平衡二叉树的树太高了，导致磁盘io的数目太多，
      • b树搜索结束可能不再叶子结点，插入或是删除时需要进行分裂等操作。
      • b+树关键字有序，且都在叶子结点。非叶子结点可以存储更多的关键字，顺序访问指针串起来，方便于区间查找。
      • hash不能范围查找，hash碰撞

大数据量如何优化

  • 处理的思路，查询优化，分区，分库，分表，redis 分布式大数据平台
  • 查询优化：
    • 索引尽量建立在order by where 上
    • 避免使用null，可以设置默认值0，少用varchar变长，select * ===> 具体的名字
    • ！= < > 也会让其不使用索引使用全局操作 不要在索引列上使用函数操作
    • in 避免使用 尽量用between and 。
    • 模糊查询，可以采用全文搜索，fulltext。
    • where = 的右边处理，例where num=100*2
    • exits 代替in
    • 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，一个表索引数量尽量不要超过6个
  • 分区
  • 物理存储的位置，按照时间拆表，
  • mysql对应的buffersize 官方推荐是内存的75%
  • 可以做缓存
  • explain查看执行计划，修改索引
  • 开启慢查询日志，查看是哪些sql的执行慢了

数据库索引建立条件

  • 最好是自增主键===> 因为我们的数据库，底层实现相关
  • 限制索引数目。
  • 1NF:原子性 字段不可再分,否则就不是关系数据库;
  • 2NF:唯一性 一个表只说明一个事物;
  • 3NF:每列都与主键有直接关系，不存在传递依赖;
  • 字段不可拆分，字段依赖于主键，字段之间不存在相互依赖
  • 为经常出现在关键字order by、group by、distinct后面的字段，建立索引。
  • 经常用作查询的字段建立索引。
  • union 或是 连接操作的字段上
  • 限制索引的数量 复合索引应该在某些位置

nginx 负载均衡策略

  • iphash
  • 权重
  • fair 按照响应时间
  • 轮询
  • url_hash

设计模式

  • 设计模式6大原则