目录
数据类型的优化总则
MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管 存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。
更小的通常更好
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通 常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周 期也更少。
但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类 型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就 选择你认为不会超过范围的最小类型。（如果系统不是很忙或者存储的数据量不多， 或者是在可以轻易修改设计的早期阶段，那之后修改数据类型也比较容易）。
简单就好
简单数据类型的操作通常需要更少的CPU周期。
1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
2、使用mysql自建类型而不是字符串来存储日期和时间
3、用整型存储IP地址
尽量避免NULL
很多表都包含可为NULL (空值）的列，即使应用程序并不需要保存NULL也是如此， 这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL, 除非真 的需要存储NULL值。
如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使 得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在 MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额 外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的 索引）变成可变大小的索引。
通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有 必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是， 如果计划在列上建索引，就应该尽扯避免设计成可为NULL的列。
当然也有例外，例如值得一提的是，lnnoDB使用单独的位(bit)存储NULL值，所 以对于稀疏数据有很好的空间效率。但这一点不适用于MyISAM。
数据类型选择流程
在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。这通 常是很简单的，但是我们会提到一些特殊的不是那么直观的案例。
下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储 的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。 相同大类型的不同子类型数据有时也有一些特殊的行为和属性。
例如，DATETIME和TIMESAMP列都可以存储相同类型的数据：时间和日期，精确到秒。
然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自 动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会 成为障碍。
MySQL为了兼容性支持很多别名，例如INTEGER、BOOL, 以及NUMERIC。它们都只是别名。这些别名可能令人不解，但不会影响性能。如果建表 时采用数据类型的别名，然后用SHOW CREATE TABLE检查，会发现MySQL报告的是基 本类型，而不是别名。
具体数据类型选择
整数类型
有两种类型的数字：整数(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, 而TINYINT的存储范围是-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类型支持精确 计算。MySQL 4.1以及更早版本则使用浮点运算来实现DECIAML的计算，这样做会因为 精度损失导致一些奇怪的结果。在这些版本的MySQL中，DECIMAL只是一个“存储类型”。
因为CPU不支持对DECIMAL的直接计算，所以在MySQL 5.0以及更高版本中，MySQL 服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所 以浮点运算明显更快。
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的 最大位数。这会影响列的空间消耗。MySQL 5.0和更高版本将数字打包保存到一个二进 制字符串中（每4个字节存9个数字）。例如，DECIMAL(18,9)小数点两边将各存储9个 数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节， 小数点本身占1个字节。
MySQL 5.0和更高版本中的DECIMAL类型允许最多65个数字。而早期的MySQL版本中 这个限制是254个数字，并且保存为未压缩的字符串（每个数字一个字节）。然而，这些（早 期）版本实际上井不能在计算中使用这么大的数字，因为DECIMAL只是一种存储格式， 在计算中DECIMAL会转换为DOUBLE类型。
有多种方法可以指定浮点列所需要的精度，这会使得MySQL悄悄选择不同的数据类型， 或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型， 不指定精度。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字 节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样， 能选择的只是存储类型； MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL。例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替 DECIMAL, 将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财 务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里， 这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
字符串类型
MySQL支持多种字符串类型，每种类型还有很多变种。这些数据类型在4.1和5.0版本发生了很大的变化，使得情况更加复杂。从MySQL 4.1开始，每个字符串列可以定义自 己的字符集和排序规则，或者说校对规则(collation)。这些东西会很大程度上影响性能。
1、char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串
2、varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性
3、text不设置长度，当不知道属性的最大长度时，适合用text 按照查询速度：char>varchar>text
VARCHAR和CHAR类型
VARCHAR和CHAR是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是怎么 存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。下面的描述假设使用的存 储引擎是InnoDB和／或者MyISAM。如果使用的不是这两种存储引擎，请参考所使用 的存储引擎的文档。
先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意，存储引擎存储CHAR或者 VARCHAR值的方式在内存中和在磁盘上可能不一样，所以MySQL服务器从存储引擎读 出的值可能需要转换为另一种存储格式。下面是关于两种类型的一些比较。
VARCHAR
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型 更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。有 一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用 定长存储，这会很浪费空间。
VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或 等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集， 一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息。
注意：varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。更长的列会消耗更多的内存，因为MYSQL通常会分配固 定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟 糕。在利用磁盘临时表进行排序时也同样糟糕。
VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在 UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用 的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引 擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新 数据。
下面这些情况下使用VARCHAR是合适的：
1、存储长度波动较大的数据，如：文章，有的会很短有的会很长
2、字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度
3、适合保存多字节字符，如：汉字，特殊字符等，UTF8这种东西
在5.0或者更高版本，MySQL在存储和检索时会保留末尾空格。但在4.1或更老 的版本，MySQL会剔除末尾空格。
varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。
InnoDB则更灵活，它可以把过长的VARCHAR存储为BLOB。
CHAR
CHAR类型是定长的，并且最大长度是255： MySQL总是根据定义的字符串长度分配足够的空间。
当存储 CHAR值时，MySQL会删除所有的末尾空格（在MySQL 4.1和更老版本中VARCHAR 也是这样实现的，也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区 别只是在存储格式上）。CHAR值会根据需要采用空格进行填充以方便比较。
检索效率、写效率会比varchar高，以空间换时间
CHAR适合的场景
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适 合存储密码的MDS值，因为这是一个定长的值。
对于经常变更的数据，CHAR也比 VARCHAR更好，因为定长的CHAR类型不容易产生碎片。
对于非常短的列，CHAR比 VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果 采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有 一个记录长度的额外字节。
CHAR类型的这些行为可能有一点难以理解，下面通过一个具体的例子来说明。首先，我 们创建一张只有一个CHAR(10)字段的表并且往里面插入一些值：
数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和 变长的字符串。Memory引擎只支持定长的行，即使有变长字段也会根据最大长度分配最大空间。不过，填充和截取空格的行为在不同存储引擎都是一样的，因为这是在 MySQL服务器层进行处理的。
与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY, 它们存储的是二进制字符串。 二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。 填充也不一样： MySQL填充BINARY采用的是\0 (零字节）而不是空格，在检索时也不 会去掉填充值.
当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些 类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较 BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比 较比字符比较简单很多，所以也就更快。
BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
注意：尽量使用大文件的文件地址，而不是将大文件存起来！！！所以慎用！！！
实际上,它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT, SMALLTEXT,TEXT, MEDIUMTEXT, LONGTEXT ,对应的二进制类型是TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB, BLOB是SMALLBLOB的同义词, TEXT是SMALLTEXT的同义词。
与其他类型不同, MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时, InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1 - 4个字节存储一个指针，然后在外部 存储区域存储实际的值。
BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字 符集，而TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max sort_ length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可 以减小max_sort_ length的配置，或者使用ORDER BY SUSTRING(column, length)。
MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除 排序。
磁盘临时表和文件排序
因为Memory引擎不支持BLOB和TEXT类型，所以，如果查询使用了BLOB或TEXT 列并且需要使用隐式临时表，将不得不使用MylSAM磁盘临时表，即使只有几行 数据也是如此(Percona Server的Memory引擎支持BLOB和TEXT类型，但直到本 书写作之际，同样的场景下还是需要使用磁盘临时表）。
这会导致严重的性能开销。即使配置MySQL将临时表存储在内存块设备上(RAM Disk), 依然需要许多昂贵的系统调用。
最好的解决方案是尽量避免使用BLOB和TEXT类型。如果实在无法避免，有一个技 巧是在所有用到BLOB字段的地方都使用SUBSTRING(column, length)将列值转换为 字符串（在ORDER BY子句中也适用），这样就可以使用内存临时表了。但是要确保 截取的子字符串足够短，不会使临时表的大小超过max_heap_table_size或tmp_ table_size, 超过以后MySQL会将内存临时表转换为MyISAM磁盘临时表。
最坏情况下的长度分配对于排序的时候也是一样的，所以这一招对于内存中创建大 临时表和文件排序，以及在磁盘上创建大临时表和文件排序
最低0.47元/天 解锁文章
303
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
