[面试项]如何进行数据库调优?对数据库调优了解多少?

数据库调优

tag:数据库;调优
数据库的调优结合产品的生命周期,可以分为以下几个阶段:

• 开发态
• 运行态
  • 常规巡检
  • 突发异常

开发态

开发态对sql的检视可以结合敏捷开发中的MR/QC等动作完成,从下述的方向入手

增:

主要针对批量插入的优化,部分插件使用了循环insert的方式减少了代码复杂度,但增加了和数据库的交互,因此需要使用批量结构

• 批量插入
  • 逻辑批量(foreach):避免该模式,但少量数据时可采用
  • batch模式:优先使用真正的batch模式,多values插入,batch模式下也要看是否超过缓存大小
• 应用层的优化,如JDBC,减少mybatis的各种插件的过滤拦截

删除

• 逻辑删除:产生历史数据问题,尤其是高频写入表,如日志表或操作记录等,需定期转移,或分库分表
• 物理删除:推荐,但不安全,需要根据具体场景,如关联表尽量物理删除

改

• 是否通过主键修改:增加修改的检索效率,减少影响范围
• 是否是范围修改:避免该类修改,会引起索引rebuild,并产生大量脏页
• 是否涉及索引修改:结合是否全量修改而定,尽量减少索引字段修改,避免索引重建
• 是否全量修改:避免全量修改,按需修改,从应用层开始,提供更为精细的修改粒度,而非全量字段修改

查

• 是否子查询嵌套
  • 子查询会产生临时表,占用更大的内容,尽量避免
  • 子查询会忽略索引,因此除去大偏移量过滤等,尽量少用子查询,其原则是,如果无助于减少查询体量或增加查询效率,不要用
• 是否不正确的连接方式
  • 全连接会生成笛卡尔集
  • ?内连接on条件和where条件是否最好都加上?
• 是否保证最大的where过滤
  • 善用where,以求通过条件筛选出和主题相关的最少数据
• 函数使用:函数意味额外的计算和IO,非必要不适用
  • 聚合函数的使用
  • 转义函数的使用
  • 分组函数的使用
• 是否存在不必要连接/字段/计算
  • 非过滤表,仅用来关联extra信息,可以采用JVM服务内存聚合的方式,而非sql查询
  • 非使用字段,导致携带多余数据;导致索引回表
  • 是否可以使用JVM层面而非数据库层面的聚合
• 是否正确的使用索引和建立索引
  • where字段
  • like函数
• 是否正确的使用了关键字
  • in与exist:大量使用in;少量使用exist
  • or and
  • between and
  • where
  • partition
  • group
  • having
• explain执行计划的检视
  • index的使用;尽量使用主键索引;尽量命中索引
  • sort的方式:避免磁盘sort;
  • 临时表的使用等

运行态

常规巡检

• 运维监控组件:慢查询/大SQL预警(阈值/告警)
  • CPU监控:较高CPU负载的sq,过多的计算可能
  • IO监控:较大IO的sql,过大的数据可能
• 开启慢查询日志,针对慢查询单个分析
• 累积性异常:
  • 数据量达到阈值
    • 磁盘满了:扩容,主从高可用,使用动态扩缩容机制
    • sql开始低效:数据量达到量级需要分库分表等
      • 应用层分库,根据分片路由到不同的库表中
      • 数据库主动分片,使用partition能力,将不同的数据存入不同的分区
      • 数据转移,历史数据的转移
      • 使用聚合库,如clickhouse或非结构化的数据库ES等
    • 线程挂起,transaction变为read only,一般由上述原因引起

突发异常

• 瞬时突发
  • 网络问题
    • 缓存,减少网络连接
  • 缓存:减少数据库访问,保证可用性
  • 扩容,使用平台能力,自动达到阈值进行扩容
  • 主从高可用