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

 

优化目标与方向定位

• 总体目标：使得响应时间更快，吞吐量更大。 （throughout --- 吞吐量：单位时间内处理事务的数量）

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

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

  • 分析日志。

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

  • 监控数据库运行状况

 

可优化维度

设计优化

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

查询优化

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

 

外置缓存

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

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

 

库级优化

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

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

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