MySQL之性能剖析(一)

MySQL之架构设计与历史

转换表的引擎

• 1.ALTER TABLE
  将表从一个引擎修改为另一个引擎最简单的办法是使用ALTER TABLE语句。下面的语句将mytable的引擎修改为InnoDB:

mysql>ALTER TABLE mytable ENGINE = InnoDB;

上述语法可以使用任何存储引擎。但有一个问题:需要执行很长时间。MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O能力，同时原表上回加上读锁。所以，在繁忙的表上执行此操作要特别小心。一个替代方案是采用导入与导出的方法，手工进行表的复制。如果转换表的存储引擎，将会失去和原引擎相关的所有特性。例如，将一张InnoDB表转换为MyISAM,然后再转换回InnoDB，原InnoDB表上所有的外键将丢失。

• 2.导出与导入
  为了更好地控制转换的过程，可以使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在相同的表ing，即使它们使用的是不同的存储引擎。同时要注意mysqldump默认回自动再CREATE TABLE语句前加上DROP TABLE语句，不注意这一点可能回导致数据丢失.
• 3.创建与查询(CREATE 和SELECT)
  第三种转换的技术综合了第一种方法的高效和第二种方法的安全。不需要导出整个表的数据，而是先创建一个新的存储引擎的表，然后利用INSERT…SELECT语法来导数据:

mysql>START TRANSACTION;
mysql>INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
mysql>COMMIT;

这样操作完成以后，新表是原表的一个全量复制，原表还在，如果需要可以删除原表。如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。

服务器性能剖析

概述

最常碰到的三个性能相关的服务请求是:如何确认服务器是否达到了性能最佳的状态、找出某条语句为什么执行不够快、诊断被人们描述成"停顿"、“堆积"或者"卡死"的某些间歇性疑难故障。
问10个人关于性能的问题，可能会得到10个不同的回答,比如"每秒查询次数”、“CPU利用率”、"可扩展性"之类。这其实也没有什么问题，每个人在不同场景下对性能有不同的理解，但是比较正式的定义是完成某件任务所需要的时间度量，换句话说，性能即响应时间，这是一个非常重要的原则。通过任务和时间而不是资源来测试性能。数据库服务器的目的是执行SQL语句，所以它关注的任务是查询或者语句，如SEELCT、UPDATE、DELETE等。数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。

还有另外一个问题:什么是优化?暂时不讨论这个问题，而是假设性能优化就是在一定的工作负载下尽可能地降低响应时间。很多人对此很迷茫。假如你认为性能优化是降低CPU利用率，那么可以减少对资源的使用。但这是一个陷阱，资源是用来消耗并用来工作的，所以有时候消耗更多的资源能够加快查询速度。很多时候使用老版本InnoDB引擎的MySQL升级到新版本后，CPU利用率会上升得很厉害，这并不代表性能出现了问题，反而说明新版本得InnoDB对资源的利用率上升了。查询的响应时间则更能体现升级后的性能是不是变得更好。版本升级有时候会带来一些bug，比如不能利用某些索引从而导致CPU利用率上升。CPU利用率只是一种现象，而不是很好的可度量的目标。

同样，如果把性能优化仅仅堪称是提升每秒查询量，这其实只是吞吐量优化。吞吐量的提升可以看作性能优化的副产品，对查询的优化可以让服务器每秒执行更多的查询，因为每条查询执行的时间更短了(吞吐量的定义是单位时间内的查询数量，这正好是前面对性能定义的倒数)。
所以如果目标是降低响应时间，那么就需要理解为什么服务器执行查询需要这么多时间，然后去减少或者消除哪些对获得查询结果来说不必要的工作。也就是说，先要搞清楚时间花在哪里。这就引申出优化的第二个原则:无法测量就无法有效地优化，所以第一步应该测量时间花在哪里
很多人在优化时，都将精力放在修改一些东西上，却很少去进行精确地测量。我们地做法完全相反，将花费非常多，甚至90%的时间来测量响应时间花在哪里。如果通过测量没有找到答案，那要么是测量的方式错了，要么是测量得不够完整。如果测量了系统中完整而且正确得数据，性能问题一般都能暴露出来，对症下要得解决方案也就比较明了。测量是一项很有挑战性的工作，并且分析结果也同样有挑战性，测出时间花在哪里，和知道为什么花在那里，是两码事。
前面提到需要合适的测量范围，其实合理的测量范围是说只测量需要优化的活动。有两种比较常见的情况会导致不合适的测量:

• 1.在错误的时间启动和停止测量
• 2.测量的是聚合后的信息，而不是目标互动本身。

例如，一个常见的错误是先看慢查询，然后又去排查整个服务器的情况来判断问题在那里。如果确认有慢查询，那么就应该测量慢查询，而不是测量整个服务器。测量的应该是从慢查询的开始到结束的时间，而不是查询之前或查询之后的时间。
完成一项任务所需要的时间可以分成两部分:执行时间和等待时间。如果要优化任务的执行时间，最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务、降低子任务的执行频率或者提升子任务的效率。而优化任务的等待时间则相对要复杂一些，因为等待有可能是由其他系统间接影响导致，任务之间也可能由于争用磁盘或者CPU资源而相互影响。根据时间是花在执行还是等待上的不同，诊断也需要不同的工具和技术

对应用程序进行性能剖析

对任何需要消耗时间的任务都可以做性能pixie，当然也包括应用程序。实际上，剖析应用程序一般比剖析数据库服务器容易，而且回报更多。虽然前面都是针对MySQL服务器的剖析，但对系统进行性能剖析还是建议自上而下地进行，这样可以追踪自用户发起到服务器响应的整个流程。虽然性能问题大多数情况下都和数据库有关，但应用导致的性能问题也不少。性能瓶颈可能有很多影响因素:

• 1.外部资源，比如调用了外部的Web服务或者搜索引擎
• 2.应用需要处理大量的数据，比如分析一个超大的XML文件
• 3.在循环中执行昂贵的操作，比如滥用正则表达式
• 4.使用了低效的算法，比如使用暴力搜索算法(naive search algorithm)来查找列表中的项

幸运的是，确定MySQL的问题没有这么复杂，只需要一款应用程序的剖析工具即可(作为回报，一旦拥有这样的工具，就可以从一开始就写出高效的代码)。
建议在所有的新项目中都考虑包含性能剖析得代码。往已有的项目中假如性能剖析代码也许很困难，新项目就简单一些。

性能剖析本身会导致服务器变慢吗？

说"是的"，是因为性能剖析确实会导致应用慢一点;说"不是"，是因为性能剖析可以帮助应用运行得更快。
性能剖析和定期检测都会带来额外开销。问题在于这部分得开销有多少，并且由此获得得收益是否能够抵消这些开销。大多数设计和构建过高性能应用程序得人相信，应该尽可能地测量一切可以测量地地方，并且接受这些测量带来的额外开销，这些开销应该被当称应用程序的一部分。Oracle的性能优化大师Tom Kyte曾被问到Oracle中的测量点的开销，他的回答是，测量点至少为性能优化贡献了10%。对此我们深表赞同，而且大多数应用并不需要要每天都运行详细的性能测量，所以实际贡献甚至要超过10%。即使不同这个观点，为应用构建一些可以永久使用的轻量级的性能剖析也是有意义的。如果系统没有每天变化的性能统计，则碰到无法提前预知的性能瓶颈就是一件头痛的事情，发现问题的时候，如果有历史数据，则这些历史数据价值是无限的。而且性能数据还可以帮助规划好硬件采购、资源分配、以及预测周期性的性能尖峰。那么何谓"轻量级"的性能剖析？比如可以为所有的SQL语句计时，加上脚本总时间统计，这样做的代价不高，而且不需要在每次页面查看(page view)时都执行.如果流量趋势比较稳定，随机采样也可以，随机采用可以通过在应用程序中设置实现:

<?php
$profiling_enabled = rand(0,100) >99;
?>

这样只有1%的会话会执行性能采样，来帮助定位一些严重的问题。这种策略在生产环境中尤其有用，可以发现一些其他方法无法发现的问题。