mysql 性能优化思路

为什么要性能优化，因为会带来一些负面影响，影响网站或应用的使用体验

image

优化的目的是降低响应时间。 理解的性能优化也是各种各样的，比如提高CPU的使用率，就是另一种优化目标，而我们这里的性能优化是特指提高响应速度。

image

下面思考一下mysql为什么会慢？

image

查询为什么会慢？

每一条sql 都是由一系列的子任务组成，每个子任务都会消耗一定的时间。

所以优化的整体思想应该是： 1、消除一些子任务

2、减少子任务的执行次数

3、让子任务运行的更快

image

执行sql的过程

1. 客户端发送mysql 到服务器

2.服务器优先查询缓存，命中了则直接返回

3.服务器对sql 进行解析，预处理，再由优化器生成执行计划

4.mysql 更具执行计划，调用存储引擎的API 来执行查询

5.返回结果，并且更新到缓存中

通信协议: 客户端从服务器取数，看起来是一个拉数据的过程，实际上是服务器向客户端推送数据的过程。有个形象的比喻“从消防栓喝水”，

只能等待没水了，客户端才能停止，所以这样是我们在必要的时候一定要在查询中加上LIMIT的原因。

缓存： 缓存放在一个引用表中，通过一个哈希值引用，哈希值的构成因数有

• 查询本身

• 要查询的数据库

• 客户端协议版本 等

不能命中缓存的情况

• Sql 任意一点不同都不会命中同一个缓存，比如 空格，注解，所以使用统一的编码规范就显得非常重要。

• sql 中包含一些不确认数据，比如 NOW() ，DATE() ，变量 等等，所以我们再查询的时候 ，使用 写好的日期，比使用 mysql的函数，性能更高

• 表数据发生变化，所以mysql 的缓存在对一些更新频率地的表非常的有效，对于更新频繁的表来说没有太大的意义。为什么要这么设计，因为简单。很多语言的mysql 驱动，都是支持我们选择是否使用缓存来执行sql语言的，大多数的时候，我们没有细分表来使用而已。

解析器 和 预处理器：会对MySQL的语句进行解析，生成一棵对接的“解析树”，它会校验语法工规则

• 错误的关键字

• 关键词顺序

• 名字别名是否有歧义等等

• 验证用户权限

优化器： 一个查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到最好的执行计划。他是计算执行成本的考量的。依赖一个 成本计算公式，有一些“因子”，这些因子都是一些统计信息，比如每个表或者索引的页面个数，索引的基数，索引和数据行的长度，索引分布情况，算出成本，比如需要做多个数据页的随机查询。

优化器选择有时并不正确，有一些原因会导致错误

• 统计信息不准确

• 页的分布， 碎片

• 并发，锁

• 全文索引 match（特殊，存在全文索引的时候，会使用全文索引）

执行计划： 生成一棵指令树

执行引擎: 调用存储引擎实现的接口来完成，“handler API”，每个表都有一个 hanndel 实例，然后提供了查询表信息，表名，列名，索引统计新等等的实现方法

image

Show processlist 可以让我们很快的了解到“谁在持球”，在一台繁忙的服务器上或者高峰期的时候，我们往往可以看到一些不正常的状态，这时候就可以使用 profile，explain 来详细的诊断哪里出了问题。

explain 就不做详述了，大家都经常使用，简单的介绍 profile的使用。

image image

总结一下：mysql 性能优化的总体思路

image

调优的成本和效果

image image