mysql调优--从哪几个方面入手

简介

一般涉及mysql调优，可以从几个方面入手，分别是硬件、mysql系统配置、表结构优化、sql语句及索引。下面简单分析一下每个方面我们能够做什么，sql语句和索引是我们调优最常见的手段，在其他文章有记载，这里主要分析其他三个方面。

硬件

我们业务上常见场景

• 数据库数据量大
• 每次需要访问的数据量较小
• 客户端与数据库交互频繁
• 数据离散程度高
• 并发程度高

这种时候选取能力强劲的cpu，以及能够顶得住频繁交互的网络设备。

另一种常见的业务场景是数据仓库，用来做报表统计等功能，特点是

• 数据量超大
• 每次访问数据量大
• 客户端与数据库交互次数少
• 并发程度低

因此选择硬件对cpu要求比较低，但是硬盘容量要大，报表统计计算时间长，也许还可以做集群部署，将统计任务拆分为多个子任务进行并行统计。

mysql系统配置

针对配置的优化，其实在高性能mysql这本书里面有分析。下面看看几个常见的。

• query_cache_size 查询缓存大小对于热点数据进行缓存可以提升某些查询的效率，适当的大小配置可以缓存更多数据，提升查询效率。

• sort_buffer_size在需要排序操作时，分配指定大小的内存用以进行排序，太小的话会需要进行磁盘io导致性能下降。但是这个变量不能随便设置为过大，一般操作是在配置文件中设置小一点，然后如果实在需要较大的排序空间时，在执行sql的时候，加上以下语句单独设置大小即可。

  set @@session.sort_buffer_size := <value>；
  //执行语句
  set @@session.sort_buffer_size := DEFAULT
  

• read_buffer_szie查询时分配该大小的内存作为缓存，一次性分配

• join_buffer_size：表关联缓冲，可以设置一个全局值，也可以为每个线程单独设置，同样地关联缓冲太小也可能造成磁盘io，从而性能下降。

一般来说配置修改是不需要经常变动的，做优化都是先把表结构优化和sql索引优化完成先才考虑配置优化。配置修改要慎重。

表结构优化

• 数据类型优化。这个是最显而易见的，按照业务需求选择合适的数据类型可以显著减少存储空间使用，提交磁盘读写效率。
• 减少一个表中不必要的列，mysql存储引擎api工作时会涉及一个行列转换的过程，过多的列会提高mysql存储引擎的工作代价，cpu负载显著提高。
• 太多的关联，“实体-属性-值”的设计模式，导致查询的时候需要关联太多的表，影响查询性能。如果能显著减少关联，可接受范围内可以对表里面的列进行冗余，减少关联。
• 物化视图，预先统计查询好我们需要的热点查询，我们在查询时只需要查询视图即可，背后实际的查询工作由视图完成。

sql语句及索引

索引生效的情况有下面：

• 全值精确匹配
• 匹配最左前缀
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列（最左），并且另一列匹配范围
• 只访问索引的查询（覆盖索引）

索引好处

• 索引可以大大减少服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机io变为顺序io

单列索引：最好是常用与检索、离散性高、长度适当的列

多列索引：建立合适的联合索引，如果经常用多个单列索引进行检索，就需要考虑联合索引，联合索引的列顺序优先将选择性高的放前面

聚簇索引

好处

• 数据访问更快，直接从索引树叶子节点获得数据。
• 覆盖索引扫描可以直接使用页节点下的主键值
• 聚集数据，可以减少磁盘io

缺点

• 提高了io密集型应用性能，但是如果数据全部在内存中，就没有优势
• 插入性能严重依赖插入顺序，主键顺序插入是最好的方式，否则聚簇索引树调整结构代价很高
• 更新聚簇索引的代价很高
• 二级索引可能很大
• 二级索引检索需要检索二次

覆盖索引

• 只需要访问二级索引，不需要访问数据，二级索引远比聚簇索引树小，容易放入内存，且极大减少数据访问量

• 对于io密集型的范围查询，io次数比随机读取要少

• explain的时候如果extra是using index，表示使用了覆盖索引。

特定语句的优化

mysql中有许多特定的语句可以按照套路进行优化提升性能。

• count语句。在业务上看能否使用近似值，可以的话可以使用explain的rows或者程序对count进行进行缓存定时刷新。myisam引擎统计全表count（*）速度很快，因为不需要再次扫描统计，可以利用这个值做差值运算，减少统计需要扫描的行数。
• 关联查询优化。被关联列上一定要建立索引；确保group by和order by字段没有分布在两个表，否则不能使用索引完成排序。
• group by和distinct优化。group by和distinct的时候，如果没有显式地使用order by指定排序列，mysql会使用group by和distinct列排序，如果group by和distinct不关心顺序，可以使用order by null，不再进行排序，避免出现filesort。
• 优化limit分页。limit分页使用扫描偏移量大小的数据，然后只截图部分，非常耗费性能。可以尝试以下手段：
  • 使用一个覆盖索引子查询分页，查询出符合条件的主键id，然后与原表关联查询出所有数据，覆盖索引扫描大大减少扫描页面。
  • 记录上一页的最终位置id，查找下一页时，直接使用范围查询，从这个id开始查找数据。