MySQL数据库技术（09)[MySQL防范]

? 2.3 挑选列的范例
? ? 上一节描写了各种可供挑选的MySQL 的列范例及其属性,以及它们可存储的各种值,所占用的存储空间等等.但是在实际成立一个表时怎样决意用哪些范例呢?本节谈论在做出决意前应考虑的各种因素.最"常用"的列范例是串范例.可将任何数据存储为串,因为数和日期都可以串的情势表示.但是为什么不将全部列都定义为串从而完毕这里的谈论呢?让我们来看一个简单的例子.假定有一些看起来像数的值.可将它们表示为串,但应当这样做吗?这样做会发生什么事?
? ? 有一桩事不可避免,那就是大概要利用更多的空间,因为较串来说,数的存储更为有效.我们大概已经注意到,由于数和串处理方法的差别,查询后果也有所差别.比方,数的排序与串的排序就有所差别.数2 小于数11,但串"2"按字典次序大于" 11".可用以下数值内容的列来搞清这个问题:

? ? 将零加到该列强迫得出一个数值,但是这样公道吗?普通大概不公道.将该列作为数而不是串具有几个重要的含义.它对每个列值实施串到数的转换,这是低效的.并且将该列的值转换为计算后果阻碍MySQL 利用该列上的索引,降低了今后的查询速度.假如这些值一开始就是作为数值存储的,那么这些性能上的降低都不会呈现.采取一种表示而不用另一种的简单挑选实际上并不简单,它在存储需求、查询效率以及处理性能等方面城市产生重要的影响.
? ? 前面的例子阐明,在挑选列范例时,有以下几个问题需求考虑:
? ? ■ 列中存储何种范例的值?这是一个显而易见的问题,但必须肯定.可将任何范例的值表示为串,特别当对数值利用更为符合的范例大概得到更好的性能时（日期和时间值也是这样）.可见,对要处理的值的范例举行评价不一定是件微不足道的事,分外在数据是别人的数据时更是如此.假如正在为其他人成立一个表,搞清列中要存储的值的范例极其重要,必须提充足多的问题以便得到作出决意的充沛的信息.
? ? ■ 列值有特定的取值范围吗?假如它们是整数,它们老是非负值吗?假如这样,可采取UNSIGNED 范例.假如它们是串,总能从定长值集合选出它们吗?假如这样, E N U M或SET 是很符合的范例.在范例的取值范围与所用的存储量之间存在折衷.需有一个多"大"的范例?关于数,假如其取值范围有限,可以挑选较小的范例,对取值范围几近无限的数,应当挑选较大的范例.关于串,可以使它们短也可以使它们长,但假如但愿存储的值只含不到10 个字符,就不该该选用C H A R ( 2 5 5 ).
? ? ■ 性能与效率问题是什么?有些范例比别的一些范例的处理效率高.数值运算普通比串的运算快.短串比长串运行更快,并且磁盘损耗更小.定长范例比可变长范例的性能更好.
? ? ■ 但愿对值举行什么样的对比?关于串,其对比可以是辨别大小写的,也可以不辨别大小写.其挑选也会影响排序,因为它是基于对比的.
? ? ■ 筹划对摆列行索引吗?假如筹划对摆列行索引,那么将会影响您对列范例的挑选,因为有的MySQL 版本不答应对某些范例举行索引,比方不能对BLOB 和TEXT 范例举行索引.并且有的MySQL 版本要求定义索引列为NOT NULL 的,这使您不能利用NULL 值.
? ? 目前让我们来更具体地考虑这些问题.这里要指出的是:在成立表时,但愿作出尽大概好的列范例挑选,但假如所作的挑选其实际并非最佳的,这也不会带来多大的问题.可用A LTER TABLE 将本来挑选的范例转换为更好的范例.在发现数据所含的值比原假想的大时,可像将SMALLINT 改换成MEDIUMINT 那样简单地对范例举行改换.有时这种改换也大概很复杂,比方将CHAR 范例改换成具有特定值集的ENUM 范例.在MySQL 3.23 及今后的版本中,可以利用PROCEDURE ANALYSE( ) 来得到表列的信息,诸如最小值和最大值以及举荐的覆盖列中值的取值范围的最佳范例.这有助于肯定利用更小的范例,从而改良触及该表的查询的性能,并削减存储该表所需的空间量.
? ? 2.3.1 列中存储何种范例的值
? ? 在决意列的范例时,首先应当考虑该列的值范例,因为这关于所挑选的范例来说具有最为明显的意义.普通,在数值列中存储数,在串列中存储串,在日期和时间列中存储日期和时间.假如数值有小数部份,那么应当用浮点列范例而不是整数范例,如此等等.有时也存
在例外,不可一概而论.主如果为了有意义地挑选范例,应当理解所用数据的特点.假如您打算存储自己的数据,大约对若何存储它们会有自己很好的设法.但是,假如其他人请您为
? ? 他们建一个表,决意列范例有时会很艰难.这不像处理自己的数据那么简单.应当充分地发问,搞清表实际应当包含何种范例的值.
假若有人奉告您,某列需求记录"降雨量".那是一个数吗?大概它"主要"是一个数值,即,普通是但不老是编码成一个数吗?比方,在看电视新闻时,气象预报普通包含降雨量.有时是一个数（如" 0 . 2 5"英寸的雨量）,但是有时是"微量( t r a c e )"降雨,意思是"雨根本就不大".这对气象预报很符合,但在数据库中怎样存储?有大概需求将"微量"量化为一个数,以便能用数值列范例来记录降雨量,大概需求利用串,以便可以记录"微量"这个词.大概可以提出某种更为复杂的安置,利用一个数值列和一个串列,假如填充一个列就让另一个列为N U L L.很明显,大概的话,应当避免最后这种挑选;最后这种挑选使表难于理解,使查询更为艰难.我们普通尽大概以数值情势存储全部的行,并且只为了显示的需求才对它们举行转换.比方,假如小于0.01 英寸的非零降雨量被视为微量,那么可以以下挑选列值:

? ? 关于金钱的计算,需求处理元和分部份.这仿佛像浮点值,但F L O AT 和DOUBLE 简单呈现舍入错误,除了只需求大致切确的记录外,这些范例大概不合适.因为人们对自己的钱都是很敏感的,最好是用一种能供应完善的切确性的范例,比方:
? ? ■ 将钱表示为DECIMAL(M, 2) 范例,挑选M 为合适于所需取值范围的最大宽度.这给出具有两位小数精度的浮点值.DECIMAL 的长处是将值表示为一个串,并且不简单呈现舍入错误.不利之处是串运算比内部存储为数的值上的运算效率差.
? ? ■ 可在内部用整数范例来表示全部的钱值.其长处是内部用整数来计算,这样会非常快.不利之处是在输入或输出时需求操纵乘或除100 对值举行转换.有些数据明显是数值的,但必须决意是利用浮点范例还是利用整数范例.应当搞清楚所用的单位是什么以及需求什么样的精度.整个单元的精度都够吗?大概需求表示小数的单元吗?这将有助于您在整数列和浮点数列之间举行辨别.比方,假如您正表示权重,那么假如记录的值为英磅,可以利用一个整形列.假如但愿记录小数部份,就应当利用浮点列.在有的情形下,乃至会利用多个字段,比方:假如但愿按照磅和盎司记录权重,则可以利用多个列.
? ? 高度（h e i g h t）是别的一种数值范例,有以下几种表示办法:
? ? ■ 诸如"6 英尺2 英寸"可表示为" 6 - 2"这样一个串.这种情势具有简单察看和理解的长处（当然比" 74 英寸更好理解"）,但是这种值很难用于数学运算,如求和或取平均值.
? ? ■ 一个数值字段表示英尺,另一个数值字段表示英寸.这样的表示举行数值运算相对简单,但两个字段比一个字段难于利用.
? ? ■ 只用一个表示英寸的数值段.这是数据库最简单处理的方法,但是这种方法意义最不明确.不过要记着,不一定要用与您惯常利用的那种格局来表示值.可以用M y S Q L的函数将值转换为看上去意义明显的值.因此,最后这种表示办法大概是表示高度的最好办法.
? ? 假如需求存储日期信息,需求包含时间吗?即,它们永久都需求包含时间吗? MySQL 不供应具有可选时间部份的日期范例: D ATE 可不包含时间,而D ATETIME 必须包含时间.假如时间确切是可选的,那么可用一个D ATE 列记录日期,一个TIME 列记录时间.答应TIME 列为NULL 并注释为"无时间":

? ? 在用基于日期信息的主-细目关系衔接两个表时,决意能否需求时间值分外重要.假定您正在举行一项研究,包含一些对进入您的办公室的人举行测试的标题.在一个尺度的初步测试集之后,您大概会在同一天举行几个额外的测试,测试的挑选视初步测试后果而定.您大概会操纵一个主-细目关系来表示这些信息,此中标题的标识信息和尺度的初步测试存储在一个主记录中,而其他测试保存为帮助细目表的行.然后基于标题ID 与举行测试的日期将这两个表衔接到一同.
? ? 在这种情形下必须答复的问题是,能否可以只用日期,大概能否需求既利用日期又利用时间.这个问题依靠于一个标题能否可以在同一天投入测试历程不止一次.假如是这样,那么应当记录时间（比方说,记录测试历程开始的时间）,大概用D ATETIME 列,大概辨别用
DATE 和TIME 列（二者都必须填写）.假如一个标题一天测试了两次,没有时间值就不能将该标题的细目记录与得当的主记录举行关联.
我曾经听过有人声称"我不需求时间;我从不在同一天把一道题测试两次".有时他们是对的,但是我也看到过这些人后来在录入同一天测试多次的标题的数据后,反过来考虑怎样避免细目记录与错误的主记录像混.很抱愧,这时已经太迟了!有时可以在表中增添TIME 列来处理这个问题,不幸的是,除非有某些独立的数据源,如原书面记录,不然很难整理现有记录.此外,没办法消除细目记录的歧义,以便将它们关联到符合的主记录上.即便有独立的信息源,这样做也是非常乱的,极大概使已经编写来操纵表的利用程序出问题.最好是向表的拥有者阐明问题并保证在成立他们的表之前举行很好的描写.
? ? 有时具有一些不完好的数据,这会干扰列范例的挑选.假如举行家谱研究,需求记录诞生日期和死亡日期,有时会发现所能汇集到的数据中只是某人诞生或死亡的年份,但没有切当的日期.假如利用D ATE 列,除非有完好的日期值,不然不能输入日期.假如但愿可以记
录所具有的任何信息,即便不完好也保存,那么大概必须保存独立的年、月、日字段.这样便可以输入所具有的日期成员并将没有的部份设为N U L L.在MySQL 3.23 及今后的版本中,还答应D ATE 的日为0 大概月和日部份为0.这样"模糊"的日期可用来表示不完好的日期值.
? ? 2.3.2 列值有特定的取值范围吗
? ? 假如已经决意从通用类别上挑选一种列范例,那么考虑想要表示的值的取值范围会有助于将您的挑选缩减到该类别中特定的范例上.假定但愿存储整数值.这些整数值的取值范围为0 到1 0 0 0,那么可以利用从SMALLINT 到BIGINT 的全部范例.假如这些整数值的取值
范围最多为2 000 000,则不能利用S M A L L I N T,其挑选范围从MEDIUMINT 到B I G I N T.需求从这个大概的挑选范围中选取一种范例.当然,可以简单地为想要存储的值挑选最大的范例（如上述例子中挑选B I G I N T）.但是,普通应当为所要存储的值挑选足以存储它的最小的范例.这样做,可以最小化表占用的存储量,得到最好的性能,因为普通较小列的处理对比大列的快.假如不知道所要表示的值的取值范围,那么必须举行猜想或利用BIGINT 以对付最坏的情形.（请注意,假如举行猜想时利用了一个太小的范例,工作不会白做;今后可以操纵A LTER TABLE 来将此列改成更大一些的范例.）
? ? 在第1章中,我们为学分保存筹划成立了一个score 表,它有一个记录查验和测试学分的score 列.为了谈论简单起见,成立该表时利用了INT 范例,但目前可以看出,假如学分在0到100 的取值范围内,更好的挑选应当是TINYINT UNSIGNED,因为所用的存储空间较小.数据的取值范围还影响列范例的属性.假如该数据从不为负,可以利用UNSIGNED 属性;不然就不能用它.
? ? 串范例没有数值列那样的"取值范围",但它们有长度,需求知道该串可以利用的列最大长度.假如串短于2 5 6个字符,可以利用C H A R、VA R C H A R、TINYTEXT 或TINYBLOB 等范例.假如想要更长的串,可以利用TEXT 或BLOB 范例,而CHAR 和VARCHAR 不再是
选项.关于用来表示某个固定值调集的串列,可以考虑利用ENUM 或SET 列范例.它们大概是很好的选项,因为它们在内部是用数来表示的.这两个范例上的运算是数值化的,因此,比其他的串范例效率更高.它们还比其他串范例紧凑、节俭空间.在描写必须处理的值的范围时,最好的术语是"老是"和"决不"（如"老是小于1 0 0 0"或"决不为负"）,因为它们能更精确地约束列范例的挑选.但在未确证之前,要慎用这两个术语.分外是与其他人谈他们的数据,而他们开始乱花这两个术语时要注意.在有人说"老是"或"决不"时,一定要搞清他们说的确切是这个含义.有时人们说自己的数据老是有某种特定的性质,而其真正的含义是"几近老是".
? ? 比方,假定您为某些人计划一个表,而他们奉告您,"我们的测试学分老是0 到1 0 0".按照这个描写,您挑选了TINYINT 范例并使它为UNSIGNED 的,因为值老是非负的.但是,您发现编码录入数据库的人有时用－ 1 来表示"学生因病缺席".呀,他们没奉告您这事.大概可以用NULL 来表示－1,但假如不能,必须记录－ 1,这样就不能用UNSIGNED 列了（只好用A LTER TABLE 来补偿!）.有时关于这些情形的谈论可通过提一些简单的问题来简化,如问:曾经有过例外吗?假如曾经有过例外情形,即便是只有一次,也必须考虑.您会发现,和您谈论数据库计划的人老是认为,假如例外不常常发生,那么就没什么关系.但是在成立数据库时,就不能这样想了.需求提的问题并非例外呈现有多频繁,而是有没有例外?假若有,必须考虑进去.
? ? 2.3.3 性能与效率问题
? ? 列范例的挑选会在几个方面影响查询性能.假如记着下几节谈论的普通原则,将可以选出有助于MySQL 有效处理表的列范例.
? ? 1. 数值与串的运算
? ? 数值运算普通比串运算更快.比方对比运算,可在单一运算中对数举行对比.而串运算触及几个逐字节的对比,假如串更长的话,这种对比还要多.假如串列的值数目有限,应当操纵ENUM 或SET 范例来得到数值运算的优胜性.这两种范例在内部是用数表示的,可更为有效地举行处理.比方替换串的表示.有时可用数来表示串值以改良其性能.比方,为了用点分四位数（d o t t e d - q u a d）表示法来表示IP 号,如1 9 2 . 1 6 8 . 0 . 4,可以利用串.但是也可以通过用四字节的UNSIGNED 范例的每个字节存储四位数的每个部份,将IP 号转换为整数情势.这便可以节俭空间又可加快查找速度.但另一方面,将IP 号表示为INT 值会使诸如查找某个子网的号码这样的情势匹配难于完成.因此,不能只考虑空间问题;必须按照操纵这些值做什么来决意哪类表示更合适.
? ? 2. 更小的范例与更大的范例
? ? 更小的范例比更大的范例处理要快得多.首先,它们占用的空间较小,且触及的磁盘活动开销也少.关于串,其处理时间与串长度直接相关.普通情形下,较小的表处理更快,因为查询处理需求的磁盘I/O 少.关于定长范例的列,应当挑选最小的范例,只要能存储所需范围的值便可.比方,假如MEDIUMINT 够用,就不要挑选B I G I N T.假如只需求F L O AT 精度,就不该该挑选D O U B L E.关于可变长范例,也仍旧可以节俭空间.一个BLOB 范例的值用2 字节记录值的长度,而一个LONGBLOB 则用4 字节记录其值的长度.假如存储的值长度永久不会超越6 4 K B,利用BLOB 将使每个值节俭2 字节（当然,关于TEXT 范例也可以做近似的考虑）.
? ? 3. 定长与可变长范例
? ? 定长范例普通比可变长范例处理得更快:
? ? ■ 关于可变长列,由于记录大小差别,在其上举行很多删除和更改将会使表中的碎片更多.需求按期运行OPTIMIZE TABLE 以保持性能.而定长列就没有这个问题.
? ? ■ 在呈现表崩溃时,定长列的表易于重新构造,因为每个记录的开始位置是肯定的.可变长列就没有这种便利.这不是一个与查询处理有关的性能问题,但它一定能加快表的修复历程.假如表中有可变长的列,将它们转换为定长列可以改良性能,因为定长记录易于处理.在试图这样做之前,应当考虑下列问题:
? ? ■ 利用定长列触及某种折衷.它们更快,但占用的空间更多.CHAR(n) 范例列的每个值总要占用n 个字节（即便空串也是如此）,因为在表中存储时,值的长度不够将在右边补空格.而VARCHAR(N) 范例的列所占空间较少,因为只给它们分配存储每个值所需求的空间,每个值再加一个字节用于记录其长度.因此,假如在CHAR 和VARCHAR列之间举行挑选,需求对时间与空间作出折衷.假如速度是主要关心的因素,则操纵CHAR 列来获得定长列的性能上风.假如空间是关键,应当利用VARCHAR 列.
? ? ■ 不能只转换一个可变长列;必须对它们全部举行转换.并且必须利用一个ALTE RTABLE 语句同时全部转换,不然转换将不起作用.
? ? ■ 有时不能利用定长范例,即便想这样做也不行.比方关于比255 字符长的串,没有定长范例.
? ? 4. 可索引范例
? ? 索引能加快查询速度,因此,应当挑选可索引的范例.
? ? 5. NULL 与NOT NULL 范例
? ? 假如定义一列为NOT NULL,其处理更快,因为MySQL 在查询处理中没必要查抄该列的值弄清它能否为N U L L,表中每行还能节俭一位.避免列中有NULL 可以使查询更简单,因为不需求将NULL 作为一种特别情形来考虑.普通,查询越简单,处理就越快.所给出的性能原则有时是彼此冲突的.比方,按照MySQL 能对行定位这一方面来说,包含CHAR 列的定长行比包含VARCHAR 列的可变长行处理快.但另一方面,它也将占用更多的空间,因此,会招致更多的磁盘活动.从这个概念来看, VARCHAR 大概会更快.作为一个经验法则,可假定定长列能改进性能,即便它占用更多的空间也如此.关于某个特别的关键利用,大概会但愿以定长和可变长两种方法实现一个表,并举行某些测试以决意哪类方法对您的特定利用来说更快.
? ? 2.3.4 但愿对值举行什么样的对比
? ? 按照定义串的方法,可以使串范例以辨别大小写或不辨别大小写的方法举行对比和排序.表2-14 示出不辨别大小写的每个范例及其等价的辨别大小写范例.按照列定义中给不给出关键字B I N A RY,有的范例（C H A R、VA R C H A R）是二进制编码或非二进制编码的.其他范例（B L O B、T E X T）的"二进制化"隐含在范例名中.

? ? 请注意,二进制（辨别大小写）范例仅在对比和排序行为上差别于呼应的非二进制（不辨别大小写）范例.肆意串范例都可以包含肆意种类的数据.分外是, TEXT 范例固然在列范例名中称为"T E X T（文本）",但它可以很好地存储二进制数据.假如但愿利用一个在对比时既辨别大小写,又可不辨别大小写的列.可在但愿举行辨别大小写的对比时,操纵B I N A RY 关键字强迫串作为二进制串值.比方,假如my_col 为一个CHAR 列,可按差别的方法对其举行对比:
? ? my_col = "A B C" 不辨别大小写
? ? BINARY my_col ="A B C" 辨别大小写
? ? my_col = BINARY"A B C" 辨别大小写
? ? 假若有一个但愿以非字典次序存储的串值,可考虑利用ENUM 列.ENUM 值的排序是按照列定义中所列出列举值的次序举行的,因此可以使这些值以肆意想要的次序排序.
? ? 2.3.5 筹划对摆列行索引吗
? ? 利用索引可更有效地处理查询.索引的挑选是第4 章中的一个主题,但普通原则是将WHERE 子句顶用来挑选行的列用于索引.假如您要对某摆列行索引或将该列包含在多列索引中,则在范例的挑选上大概会有限定.在早于3.23.2 版的MySQL 发行版中,索引列必须定义为NOT NULL,并且不能对BLOB 或TEXT 范例举行索引.这些限制在MySQL 3.23.2 版中都撤消了,但假如您正利用一个更早的版本,不能或不肯进级,那么必须顺从这些约束.不过在下列情形中可以绕过它们:
? ? ■ 假如可以指定某个值作为专用的值,那么可以将其作为与NULL 相同的东西对待.关于DATE 列,可以指定"0000 - 00 - 00"表示"无日期".在串列中,可以指定空串代表"缺值".在数值列中,假如该列普通只存储非负值,则可以利用- 1.
? ? ■ 不能对BLOB 或TEXT 范例举行索引,但假如串不超越255 它符,可以利用等价的VARCHAR 列范例并对其举行索引.可VARCHAR(255) BINARY 用于BLOB 值,将VARCHAR(255) 用于TEXT 值.
? ? 2.3.6 列范例挑选问题的彼此关联程度
? ? 不要认为列范例的挑选是彼此独立的.比方,数值的取值范围与存储大小有关;在增大取值的范围时,需求更多的存储空间,这会影响性能.别的,考虑挑选利用A U TO _INCREMENT 来成立一个存放唯一序列号的列有何含义.这个挑选有几个后果,它们触及列
的范例、索引和NULL 的利用,现列出以下:
? ? ■ A U TO_INCREMENT 是一个应当只用于整数范例的列属性.它将您的挑选限定在TINYINT 到BIGINT 之上.
? ? ■ A U TO_INCREMENT 列应当举行索引,从而当前最大的序列号可以很快就肯定,不用对表举行全部扫描.此外,为了避免序列号被重用,索引号必须是唯一的.这表示必须将列定义为P R I M A RY KEY 或定义为UNIQUE 索引.
? ? ■ 假如所用的MySQL 版本早于3 . 2 3 . 2,则索引列不能包含NULL 值,因此,必须定义列为NOT NULL.全部这一切表示,不能像以下这样只定义一个A U TO_INCREMENT 列:

? ? 利用A U TO_INCREMENT 得到的另一个后果是,由于它是用来生成一个正值序列的,因此,最好将A U TO_INCREMENT 列定义为UNSIGNED:
　　以上是“MySQL数据库技术（09)[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：