MySQL监控及优化

1. explain命令

   查看查询优化器如何决定查询的主要方法。即查看sql的执行效率，帮助我们分析select语句，让我们知道查询效率低下的原因，从而改进我们的查询。用法就是sql语句前加explain。

    Explain具有局限性，它不会考虑触发器、存储过程或函数、缓存对查询结果的影响，不会考虑mysql执行查询所做的特定优化，基于统计信息的估算，并非精确值。

1. profile 命令

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。

开启profile：set @@profiling = 1; #开启后仅对当前sesion有效

查看profile是否开启：select @@profiling； #1为开启，0位关闭

查看当前已经被记录的sql：show profiles;

查看profile结果：show profile for query n; #n为show profile 中的query_id

show profile可以看到sql执行计划中每步的执行时间，以及cpu、内存、io的消耗:show profile cpu for query 1;#查看cpu消耗

   show profile block io for query 1;#查看io消耗

   show profile memory  for query 1;#查看cpu

也可以一起写:show profile cpu ,block io for query 1;

1. SQL优化的目标

1）减少IO次数：

IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

2）降低CPU计算：

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标。

1. 优化SQL的方法

明确了优化目标之后，我们需要确定达到我们目标的方法。对于 SQL 语句来说，达到上述2个目标的方法其实只有一个，那就是改变 SQL 的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标。

1. SQL优化的基本原则

1）尽量少join：

MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

2）尽量少排序：

排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。对于MySQL来说，减少排序有多种办法，比如：通过利用索引来排序的方式进行优化；减少参与排序的记录条数；非必要不对数据进行排序。

3）尽量避免select *：

    select * 一般会造成全表扫描，显示所有列，select需要的字段即可。

4）尽量用join 代替子查询：

虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。

5）尽量用union all 代替union：

union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。

6）禁用外键

7）避免大sql：

一个SQL只能在一个CPU上运行；高并发环境中，大SQL容易影响性能问题；可能一个大SQL把数据库搞死；拆分SQL。

8）保持事务的短小精悍：

即开即用，用完即关；无关操作剔出事务，减少资源占用；保持一致性的前提下，拆分事务。

9）避免大批量更新：

    避开高峰，白天限制速度，加sleep。

10）避免类型转换：

11）避免取过量数据，建议使用limit

12）避免在SQL语句中进行数学运算、函数计算、逻辑判断等操作

13）避免OR：同一字段，推荐in；不同字段，推荐union。

14）优先优化高并发的SQL，而不是执行效率低的某些大SQL

15）从全局出发优化，而不是片面调整

16）尽可能对每一条运行在数据库中的SQL进行explain

17）优化要从整个业务逻辑上进行：

     针对数据库问题进行的优化，首先要考虑不查或少查数据库。如果查询不可避免，可考虑两种优化方式：避免磁盘IO，让查询在内存中完成；通过sql和索引的调整，让MySQL用更高效的方式查询。索引设计原则：利用最左前缀、不要过度索引。

1. MySQL监控工具

1）Spotlight on mysql

2）客户端工具，有漂亮的ui界面，可以监控到mysql的io、qcache、连接数、buffer pool等等，还有预警的功能

3）Lepus，一个开源的国产监控平台，可以监控到mysql的慢查询、 qcache、连接数、buffer pool等等，可以同时监控多台，配置多个实例即可

4）Zabbix，也是一个开源的监控平台，和lepus类似，配置比较复杂

MySQL监控及优化