【面试题】业务增长-数据库性能优化思路

DBC 24 0

面试官:这边有个数据库-单表1千万数据,未来1年还会增长多500万,性能比较慢,说下你的优化思路

  • 思路
    • 千万不要一上来就说分库分表,这个是最忌讳的事项
    • 一定要根据实际情况分析,两个角度思考
      • 不分库分表
        • 软优化
          • 数据库参数调优
          • 分析慢查询SQL语句,分析执行计划,进行sql改写和程序改写
          • 优化数据库索引结构
          • 优化数据表结构优化
          • 引入NOSQL和程序架构调整
        • 硬优化
          • 提升系统硬件(更快的IO、更多的内存):带宽、CPU、硬盘
      • 分库分表
        • 根据业务情况而定,选择合适的分库分表策略(没有通用的策略)
          • 外卖、物流、电商领域
        • 先看只分表是否满足业务的需求和未来增长
          • 数据库分表能够解决单表数据量很大的时,数据查询的效率问题,
          • 无法给数据库的并发操作带来效率上的提高,分表的实质还是在一个数据库上进行的操作,受数据库IO性能的限制
        • 如果单分表满足不了需求,再分库分表一起
  • 结论
    • 在数据量及访问压力不是特别大的情况,首先考虑缓存、读写分离、索引技术等方案
    • 如果数据量极大,且业务持续增长快,再考虑分库分表方案

一、Mysql数据库分库分表后带来的优点和缺点

  • 分库分表解决的现状问题
    • 解决数据库本身瓶颈
      • 连接数: 连接数过多时,就会出现‘too many connections’的错误,访问量太大或者数据库设置的最大连接数太小的原因
      • Mysql默认的最大连接数为100.可以修改,而mysql服务允许的最大连接数为16384
      • 数据库分表可以解决单表海量数据的查询性能问题
      • 数据库分库可以解决单台数据库的并发访问压力问题
  • 解决系统本身IO、CPU瓶颈
    • 磁盘读写IO瓶颈,热点数据太多,尽管使用了数据库本身缓存,但是依旧有大量IO,导致sql执行速度慢
    • 网络IO瓶颈,请求的数据太多,数据传输大,网络带宽不够,链路响应时间变长
    • CPU瓶颈,尤其在基础数据量大单机复杂SQL计算,SQL语句执行占用CPU使用率高,也有扫描行数大、锁冲突、锁等待等原因
  • 带来新的问题
    • 问题一:跨节点数据库Join关联查询和多维度查询
      • 数据库切分前,多表关联查询,可以通过sql join进行实现
      • 分库分表后,数据可能分布在不同的节点上,sql join带来的问题就比较麻烦
    • 不同维度查看数据,利用的partitionKey是不一样的
      • 例如
        • 订单表 的partionKey是user_id,用户查看自己的订单列表方便
        • 但商家查看自己店铺的订单列表就麻烦,分布在不同数据节点
    • 问题二:分库操作带来的分布式事务问题
      • 操作内容同时分布在不同库中,不可避免会带来跨库事务问题,即分布式事务
    • 问题三:执行的SQL排序、翻页、函数计算问题
      • 分库后,数据分布再不同的节点上, 跨节点多库进行查询时,会出现limit分页、order by排序等问题
      • 而且当排序字段非分片字段时,更加复杂了,要在不同的分片节点中将数据进行排序并返回,然后将不同分片返回的结果集进行汇总和再次排序(也会带来更多的CPU/IO资源损耗)
    • 问题四:数据库全局主键重复问题
      • 常规表的id是使用自增id进行实现,分库分表后,由于表中数据同时存在不同数据库中,如果用自增id,则会出现冲突问题
    • 问题五:容量规划,分库分表后二次扩容问题
      • 业务发展快,初次分库分表后,满足不了数据存储,导致需要多次扩容
    • 问题六:分库分表技术选型问题
      • 市场分库分表中间件相对较多,框架各有各的优势与短板,应该如何选择
        • 更多问题。。。

二、海量数据处理之Mysql【垂直分表-垂直分库】

  • 需求:商品表字段太多,每个字段访问频次不一样,浪费了IO资源,需要进行优化
  • 垂直分表介绍
    • 也就是“大表拆小表”,基于列字段进行的
    • 拆分原则一般是表中的字段较多,将不常用的或者数据较大,长度较长的拆分到“扩展表 如text类型字段
    • 访问频次低、字段大的商品描述信息单独存放在一张表中;
    • 访问频次较高的商品基本信息单独放在一张表中
    • 垂直拆分原则
      • 把不常用的字段单独放在一张表;
      • 把text,blob等大字段拆分出来放在附表中;
      • 业务经常组合查询的列放在一张表中
    • 例子:商品详情一般是拆分主表和附表
  • 需求:C端项目里面,单个数据库的CPU、内存长期处于90%+的利用率,数据库连接经常不够,需要进行优化
  • 垂直分库讲解
    • 垂直分库针对的是一个系统中的不同业务进行拆分, 数据库的连接资源比较宝贵且单机处理能力也有限
    • 没拆分之前全部都是落到单一的库上的,单库处理能力成为瓶颈,还有磁盘空间,内存,tps等限制
    • 拆分之后,避免不同库竞争同一个物理机的CPU、内存、网络IO、磁盘,所以在高并发场景下,垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈
    • 垂直分库可以更好解决业务层面的耦合,业务清晰,且方便管理和维护
    • 一般从单体项目升级改造为微服务项目,就是垂直分库
  • 问题:垂直分库分表可以提高并发,但是依然没有解决单表数据量过大的问题

三、海量数据处理之Mysql【水平分表-水平分库】

  • 需求:当一张表的数据达到几千万时,查询一次所花的时间长,需要进行优化,缩短查询时间
  • 都是大表拆小表
    • 垂直分表:表结构拆分
    • 水平分表:数据拆分
  • 水平分表
    • 把一个表的数据分到一个数据库的多张表中,每个表只有这个表的部分数据
    • 核心是把一个大表,分割N个小表,每个表的结构是一样的,数据不一样,全部表的数据合起来就是全部数据
    • 针对数据量巨大的单张表(比如订单表),按照某种规则(RANGE,HASH取模等),切分到多张表里面去
    • 但是这些表还是在同一个库中,所以单数据库操作还是有IO瓶颈,主要是解决单表数据量过大的问题
    • 减少锁表时间,没分表前,如果是DDL(create/alter/add等)语句,当需要添加一列的时候mysql会锁表,期间所有的读写操作只能等待
  • 需求:高并发的项目中,水平分表后依旧在单个库上面,1个数据库资源瓶颈 CPU/内存/带宽等限制导致响应慢,需要进行优化
  • 水平分库
    • 把同个表的数据按照一定规则分到不同的数据库中,数据库在不同的服务器上
    • 水平分库是把不同表拆到不同数据库中,它是对数据行的拆分,不影响表结构
    • 每个库的结构都一样,但每个库的数据都不一样,没有交集,所有库的并集就是全量数据
    • 水平分库的粒度,比水平分表更大

 

发表评论 取消回复
表情 图片 链接 代码

分享