MySQL调优 优化需要考虑哪些方面

时间:2022-12-13 08:04:28

MySQL调优 优化需要考虑哪些方面

 

优化目标与方向定位

  • 总体目标:使得响应时间更快,吞吐量更大。 (throughout --- 吞吐量:单位时间内处理事务的数量)

  • 如何找到需要优化的地方

    • 使用反馈。比如做出一些操作后导致效率降低

    • 分析日志。

    • 监控服务器资源。系统,内存,I/O

    • 监控数据库运行状况

 

可优化维度

设计优化

  • 选择适合的DBMS
  • 对表恰当的设计
    • 尽量遵循第三范式。减少冗余的同时减少增删改时出错的可能。
    • 适当地"反范式",以空间换时间,提高多表联查的效率。
    • 选择恰当的字段类型。尽量选择数据类型,尽量选择字符长度小的字符类型。

查询优化

  • 对SQL查询进行逻辑优化 --- 就是使用恰当的SQL语句让查询速度更快
    • 比如“小表驱动大表”的EXISTS和IN
    • 比如子查询优化,简化查询条件等等
  • 对SQL查询进行物理优化 --- 就是通过索引或表链接的方式进行优化,本质上是对Server层优化器和执行器进行“人工辅助”,人为地减轻优化器和执行器的压力。
    • 索引
      • 为表设计精简且高效的索引 --- 索引不是越多越好,索引需要占据存储空间,过多的索引也会提高优化器选择索引的难度。比如字段内数据重复度高时不建立索引,如性别
      • 若在where中对索引字段进行了表达式计算,会造成该字段索引失效。
      • 设计联合索引时选择恰当的顺序 --- 最左前缀原则
    • 表连接
      • 单表:全表扫描或局部扫描
      • 两表:合并连接,HASH连接,嵌套循环连接
      • 多表:连接顺序

 

外置缓存

数据都是存放在数据库(磁盘)中的,在有使用需要的时候就会将磁盘数据调入内存。但当用户量增大时,使用大量数据,频繁读取磁盘会消耗大量资源。因此我们可以事先将常用的数据放入内存中来提高查询效率。

  • 键值存储数据库 Redis 和 Memcached 等
  • Redis 支持持久化且支持的数据类型和数据结构比Memcached多。Memcached仅进行内存存储且仅支持键值对存储。
  • 对于查询响应要求高的场景可以考虑上述内存数据库,不过增加的开发人员的工作量。

 

库级优化

一般来说现在常见的关系型数据库单表可以存储亿级的数据量。

当数据量达到亿级以上时可以采用以下方案进行库级优化。

  • 读写分离:使用主从数据库代替单一数据库,降低单一数据库时的负载。主库完成写操作,从库完成读操作。
  • 分库分表
    • 垂直切分
      • 垂直分库:数据表过多时,对表进行划分,将相关联的数据表存放在一个库中
      • 垂直分表:数据表列较多时,对列进行划分并拆分成多个表,将经常一起使用的列存入一张表中
    • 水平切分
      • 表中数据量达到亿级以上时,在保持相同的表结构的情况下,将表按照某一属性拆分成不同小表。
  • 分库分表也会增加维护和使用成本,要加以平衡。