MySQL之性能剖析和Schema与数据类型优化(一)

性能剖析总结

• 1.定义性能最有效的方法是响应时间
• 2.如果无法测量就无法有效地优化，所以性能优化工作需要基于高质量、全方位及完整的响应时间测量
• 3.测量的最佳开始点是应用程序，而不是数据库。即使问题出在底层的数据库，借助良好的测量也可以很容易地发现问题
• 4.大多数系统无法完整地测量，测量有时候也会有错误的结果。但也可以想办法绕过一些限制，并得到好的结果(但是要能意识到所使用的方法的缺陷和不确定性在哪里)
• 5.完整的测量会产生大量需要分析的数据，所以需要用到剖析器。这是最佳的工具，可以帮助将重要的问题冒泡到前面，这样就可以决定从哪里开始分析会比较好
• 6.剖析报告是一种汇总信息，掩盖和丢弃了太多细节。而且它不会告诉你缺少了什么，所以完全依赖剖析报告也是不明智的
• 7.有两种消耗时间的操作:工作或者等待。大多数剖析器只能测量因为工作而消耗的时间，所以等待分析有时候是很有用的补充，尤其是当CPU利用率很低但工作却一直无法完成的时候
• 8.优化和提升是两回事。当继续提升的成本超过收益的时候，应当停止优化
• 9.注意你的直觉，但应该只根据直觉来指导解决问题的思路，而不是用于确定系统的问题。决策应当尽量基于数据而不是感觉。

总的来说，解决性能问题的方法首先是要澄清问题，然后选择合适的技术来解答这些问题。如果你想尝试提升服务器的总体性能，那么一个比较好的七点是将所有查询记录到日志中，然后利用pt-query-digest工具生成系统级别的剖析报告。如果是要追查某些性能低下的查询，记录和剖析得方法也会有帮助。可以把精力放在寻找哪些消耗时间最多的、导致了糟糕的用户体验的，或者那些高度变化的，抑或有奇怪的响应时间直方图的查询。当找到了这些"坏"查询时，要钻取pt-query-digest报告中包含的该查询的详细信息，或者使用SHOW PROFILE及其他诸如EXPLAIN这样的工具。

如果找不到这些查询性能低下的原因，那么也可能时遇到了服务器级别的性能问题。这是，可以较高精度测量和回直服务器状态计数器的细节信息。如果通过这样的分析重现了问题，则应该通过同样的数据制定一个可靠的触发条件，来收集更多的诊断数据。多花费一点时间来确定可靠的触发条件，尽量避免漏检或者误报。如果已经可以捕获故障活动期间的数据，但还是无法找到其根本原因，则要么尝试捕获更多的数据，要么尝试寻求帮助。
我们无法完整地测量工作系统，但说到底它们都是某种状态机，所以只要足够细心，逻辑清晰并且坚持下去，通常来说都能得到想要的结果。要注意的时不要把原因和结果搞混了，而且在确认问题之前也不要随便针对系统做变动。
理论上纯粹的自顶向下的方法分析和详尽的测量只是理想的情况，而我们常常需要处理的是真实系统。真实系统是复杂且无法充分测量的，所以我们只能根据情况尽力而为。使用诸如pt-query-digest和MySQL企业监控器的查询分析其这样的工具并不完美，通常都不会给出问题根源的直接证据。但真的掌握了以后，已经足以完成大部分的优化诊断工作了。

Schema与数据类型优化

概述

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。例如，反范式的设计可以加快某些类型的查询，但同时可能使另一些类型的查询变慢。比如添加计数器和汇总表是一种很好的优化查询的方式，但这些表的维护成本可能会很高。MySQL独有的特性和实现细节对性能的影响也很大。

选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

• 1.更好的通常更好
  一般情况下，应该尽量使用可以正确存储数据更小的数据类型(例如只需要存0~200,tinyint unsigned更好)。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
  但是要确保没有嘀咕需要存储的值范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。(如果系统不是很忙或者存储的数据量不多，或者是在可以轻易修改设计的早期阶段，那之后修改数据类型也比较容易)
• 2.简单就好
  简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。有两个例子:一个是应该使用mySQL的内建类型(date,time,datetime)而不是字符串来存储日期和时间，另外一个是应该使用整型存储IP地址。
• 3.尽量避免NULL
  很多表都包含可为NULL(空值)的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULl的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。
  通常把可为NULl的列改为NOT NULL带来的性能提升比较小，所以(调优时)没有必要首先在现有schema中查找并修改掉这种情况，除非确定这回导致问题。但是，如果计划在列上键索引，就应该尽量避免设计成可为NULL的列。
  当然也有例外，例如值得一提的时，InnoDB使用单独的位(bit)存储NULL值，所以对于稀疏数据(很多值为NULL，只有少数行的列有非NULL值)有很好的空间效率。但这一点不适用于MyISAM

在为列选择数据类型时，第一步需要确定合适的大类型:数字、字符串、时间等。这通常是很简单的。但是我们会提到一些特殊的不是那么直观的例子。
下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，这是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间(磁盘和内存空间)不同，相同大类型的不同子类型数据有时也有一些特殊的行为和属性。
然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为阻碍。

整数类型

有两种类型的数字:整数(whole number)和实数(real number)。如果存储整数，可以使用这几种整数类型:TINYINT,SMALLINT, MEDIUMINT,INT,BIGINT.分别使用8，16，24，32，64存储空间。它们可以存储的值的范围从-2^(N-1)到2(N-1)-1,其中N是存储空间的位数。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高已被。例如TINYINT UNSIGNED可以存储的范围是0_{255,而TINYINY的存储范围是-128}127.
有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。你的选择决定MySQL是怎么在内存和磁盘中保存数据的，然而，整数计算一半使用64位的BIGINT整数，即使在32位环境也是如此。(一些聚合函数是例外，它们使用DECIMAL或者DOUBLE进行计算)。
MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的，他不会限制值得合法范围，只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。如果需要直到浮点运算时怎么计算的，则需要研究所使用的平台的浮点数的具体实现。DECIMAL类型用于存储精确的小数。在MySQL 5.0和更高版本，DECIMAL类型支持精确计算。MySQL4.1及更早版本则使用浮点运算来实现DECIMAL的计算，这样做会因为精度损失导致一些奇怪的结果。在这些版本的MySQL中，DECIMAL只是一个"存储类型"。
因为CPU不支持对DECIMAL的直接计算，所以在MySQL5.0以及更高版本中，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。
浮点和DECIMAL类型都可以指定精度。对DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如DECIMAL(18,9)小数点两边将个存储9个数字，一共使用9个字节:小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。
MySQL5.0和更高版本中的DECIMAL类型允许最多65个数字。而早期的MySQL版本中，这个限制时254个数字，并且保存为未压缩的字符串(每个数字一个字节)。然而，这些(早期)版本实际上并不能在计算中使用这么大的数字，因为DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE类型。
有多种方法可以指定浮点列所需要的精度，这回使得MySQL悄悄选择不同的数据类型，或者在存储时对值进行取舍。这些精度定义时非标准的，所以建议只指定数据类型，不指定精度。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE只能用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型;MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以高铝使用BIGINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样就可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题