MySQL数据库技巧MySQL优化之数据类型的使用.doc

MySQL数据库技巧MySQL优化之数据类型的使用有助于效率的类型选择1、使你的数据尽可能小最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小。这能给出巨大的改进，因为磁盘读入较快并且通常也用较少的主存储器。如果在更小的列上做索引，索引也占据较少的资源。你能用下面的技术使表的性能更好并且使存储空间最小：尽可能地使用最有效(最小)的类型。MySQL有很多节省磁盘空间和内存的专业化类型。如果可能使表更小，使用较小的整数类型。例如，MEDIUMINT经常比INT好一些。如果可能，声明列为NOT NULL。它使任何事情更快而且你为每列节省一位。注意如果在你的应用程序中你确实需要NULL，你应该毫无疑问使用它，只是避免缺省地在所有列上有它。2、使用定长列，不使用可变长列这条准则对被经常修改，从而容易产生碎片的表来说特别重要。例如，应该选择 CHAR 列而不选择 VARCHAR 列。所要权衡的是使用定长列时，表所占用的空间更多，但如果能够承担这种空间的耗费，使用定长行将比使用可变长的行处理快得多。3、将列定义为 NOT NULL这样处理更快，所需空间更少。而且有时还能简化查询，因为不需要检查是否存在特例 NULL。4、考虑使用 ENUM 列如果有一个只含有限数目的特定值的列，那么应该考虑将其转换为 ENUM 列。ENUM 列的值可以更快地处理，因为它们在内部是以数值表示的。有关BLOB和TEXT类型1、使用BLOB和TEXT类型的优点用 BLOB 存储应用程序中包装或未包装的数据，有可能使原来需要几个检索操作才能完成的数据检索得以在单个检索操作中完成。而且还对存储标准表结构不易表示的数据或随时间变化的数据有帮助。2、使用BLOB和TEXT类型的可能弊端另一方面，BLOB 值也有自己的固有问题，特别是在进行大量的 DELETE 或 UPDATE 操作时更是如此。删除 BLOB 会在表中留下一个大空白，在以后将需用一个记录或可能是不同大小的多个记录来填充。除非有必要，否则应避免检索较大的 BLOB 或 TEXT 值。例如，除非肯定WHERE 子句能够将结果恰好限制在所想要的行上，否则 SELECT * 查询不是一个好办法。这样做可能会将非常大的 BLOB 值无目的地从网络上拖过来。这是存储在另一列中的 BLOB 标识信息很有用的另一种情形。可以搜索该列以确定想要的行，然后从限定的行中检索 BLOB 值。3、必要的准则对容易产生碎片的表使用 OPTIMIZE TABLE大量进行修改的表，特别是那些含有可变长列的表，容易产生碎片。碎片不好，因为它在存储表的磁盘块中产生不使用的空间。随着时间的增长，必须读取更多的块才能取到有效的行，从而降低了性能。任意具有可变长行的表都存在这个问题，但这个问题对 BLOB 列更为突出，因为它们尺寸的变化非常大。经常使用 OPTIMIZE TABLE 有助于保持性能不下降。使用多列索引多列索引列有时很有用。一种技术是根据其他列建立一个散列值，并将其存储在一个独立的列中，然后可通过搜索散列值找到行。这只对精确匹配的查询有效。(散列值对具有诸如“=”这样的操作符的范围搜索没有用处)。在MySQL 3.23版及以上版本中，散列值可利用 MD5( ) 函数产生。散列索引对 BLOB 列特别有用。有一事要注意，在 MySQL 3.23.2 以前的版本中，不能索引 BLOB 类型。甚至是在 3.23.2 或更新的版本中，利用散列值作为标识值来查找 BLOB 值也比搜索 BLOB 列本身更快。将 BLOB 值隔离在一个独立的表中在某些情况下，将 BLOB 列从表中移出放入另一个副表可能具有一定的意义，条件是移出 BLOB 列后可将表转换为定长行格式。这样会减少主表中的碎片，而且能利用定长行的性能优势。使用ANALYSE过程检查表列如果使用的是 MySQL 3.23 或更新的版本，应该执行 PROCEDURE ANALYSE( )，查看它所提供的关于表中列的信息ANALYSE(max elements,max memory)它检验来自你的查询的结果并返回结果的分析。max elements(缺省256)是analyse将注意的每列不同值的最大数量。这被ANALYSE用来检查最佳的列类型是否应该是ENUM类型。max memory(缺省8192)是在analyse尝试寻找所有不同值的时候应该分配给每列的最大内存量。SELECT . FROM . WHERE . PROCEDURE ANALYSE(max elements,max memory)例如：mysqlSELECT * FROM student PROCEDURE ANALYSE();mysqlSELECT * FROM student PROCEDURE ANALYSE(16,256);相应输出中有一列是关于表中每列的最佳列类型的建议。第二个例子要求 PROCEDURE ANALYSE( ) 不要建议含有多于 16 个值或取多于 256 字节的 ENUM 类型(可根据需要更改这些值)。如果没有这样的限制，输出可能会很长;ENUM 的定义也会很难阅读。根据 PROCEDURE ANALYSE( ) 的输出，会发现可以对表进行更改以利用更有效的类型。如果希望更改值类型，使用 ALTER TABLE 语句即可。数据库 数据类型1) 整数型 整数包括bigint、int、smallint和tinyint，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。 l bigint：大整数，数范围为 -263 (-9223372036854775808)263-1 (9223372036854775807) ，其精度为19，小数位数为0，长度为8字节。 l int：整数，数范围为 -231 (-2,147,483,648) 231 - 1 (2,147,483,647) ，其精度为10，小数位数为0，长度为4字节。 l smallint：短整数，数范围为 -215 (-32768) 215 - 1 (32767) ，其精度为5，小数位数为0，长度为2字节。 l tinyint：微短整数，数范围为 0255，长度为1字节，其精度为3，小数位数为0，长度为1字节。 (2) 精确整数型 精确整数型数据由整数部分和小数部分构成，其所有的数字都是有效位，能够以完整的精度存储十进制数。精确整数型包括decimal 和 numeric两类。从功能上说两者完全等价，两者的唯一区别在于decimal不能用于带有identity关键字的列。 声明精确整数型数据的格式是numeric | decimal(p,s)，其中p为精度，s为小数位数，s的缺省值为0。例如指定某列为精确整数型，精度为6，小数位数为3，即decimal(6,3)，那么若向某记录的该列赋值56.342689时，该列实际存储的是56.3427。 decimal和numeric可存储从 -1038 +1 到 1038 1 的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。 l 精度为19时，存储字节长度为5； l 精度为1019时，存储字节长度为9； l 精度为2028时，存储字节长度为13； l 精度为2938时，存储字节长度为17。 例如若有声明numeric(8,3)，则存储该类型数据需5字节，而若有声明numeric(22,5)，则存储该类型数据需13字节。 注意：声明精确整数型数据时，其小数位数必须小于精度；在给精确整数型数据赋值时，必须使所赋数据的整数部分位数不大于列的整数部分的长度。 (3) 浮点型 浮点型也称近似数值型。顾名思义，这种类型不能提供精确表示数据的精度，使用这种类型来存储某些数值时，有可能会损失一些精度，所以它可用于处理取值范围非常大且对精确度要求不是十分高的数值量，如一些统计量。 有两种近似数值数据类型：float(n)和real，两者通常都使用科学计数法表示数据，即形为：尾数E阶数，如5.6432E20，-2.98E10，1.287659E-9等。 l real：使用4字节存储数据，表数范围为-3.40E + 38 到 3.40E + 38，数据精度为7位有效数字。 l float：定义中的n取值范围是153，用于指示其精度和存储大小。当n在124之间时，实际上是定义了一个real型数据，存储长度为4字节，精度为7位有效数字；当n在2553之间时，存储长度为8字节，精度为15位有效数字。当缺省n时，代表n在2553之间。float型数据的数范围为- 1.79E+308 到 1.79E+308。 (4) 货币型 SQL Server提供了两个专门用于处理货币的数据类型：money和smallmoney，它们用十进制数表示货币值。 l money：数据的数范围为-263 (-922337203685477.5808)263-1 (922337203685477.5807) ，其精度为19，小数位数为4，长度为8字节。money的数的范围与bigint相同，不同的只是money型有4位小数，实际上，money就是按照整数进行运算的，只是将小数点固定在末4位。 l smallmoney：数范围为 231 (-2,147,48.3648) 231 - 1 (2,147,48.3647) ，其精度为10，小数位数为4，长度为4字节。可见smallmoney与int的关系就如同money与bigint的关系。 当向表中插入money或smallmoney类型的值时，必须在数据前面加上货币表示符号（$），并且数据中间不能有逗号（,）；若货币值为负数，需要在符号$的后面加上负号（-）。例如：$15000.32，$680，$-20000.9088都是正确的货币数据表示形式。 (5) 位型 SQL Server中的位（bit）型数据相当于其他语言中的逻辑型数据，它只存储0和1，长度为一个字节。但要注意，SQL Server对表中bit类型列的存储作了优化：如果一个表中有不多于 8 个的bit列，这些列将作为一个字节存储，如果表中有 9 到 16 个 bit 列，这些列将作为两个字节存储，更多列的情况依此类推。 当为bit类型数据赋0时，其值为0，而赋非0（如100）时，其值为1。 若表中某列为bit类型数据，那么该列不允许为空值（有关空值概念本节稍后即做介绍），并且不允许对其建立索引。 (6) 字符型 字符型数据用于存储字符串，字符串中可包括字母、数字和其它特殊符号（如#、&等等）。在输入字符串时，需将串中的符号用单引号或双引号括起来，如abc、”Abc dump.sql% mysql db_name dump.sql把数据打包放入BLOB或TEXT数据列。使用BLOB或TEXT数据列存储打包（pack）的数据，并在应用程式中进行解包（unpack），使你能够在一次检索操作中得到需要的所有信息，而不必进行多次检索。他对那些非常难用标准的数据表结构表现的数据值和频繁变化的数据值也是有帮助的。解决这个问题的另一种方法是让那些处理Web窗体的应用程式把数据打包成某种数据结构，然后把他插入到单个BLOB或TEXT数据列中。例如，你能使用XML表示调查表回复，把那些XML字符串存储在TEXT数据列中。由于要对数据进行编码（从数据表中检索数据的时候还需要解码），他会增加客户端的开销，不过能简化数据结构，而且他还消除了那些因为改动了调查表的内容而必须改动数据表结构的需求。另一方面，BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下非常大的空洞，以后填入这些空洞的记录可能长度不同（前面讨论的OPTIMIZE TABLE提出解决这个问题的一些建议）。使用合成的（synthetic）索引。合成的索引列在某些时候是有用的。一种办法是根据其他的列的内容建立一个散列值，并把这个值存储在独立的数据列中。接下来你就能通过检索散列值找到数据行了。不过，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似=等范围搜索操作符是没有用处的）。我们能使用MD5()函数生成散列值，也能使用SHA1()或CRC32()，或使用自己的应用程式逻辑来计算散列值。请记住数值型散列值能非常高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把他们存储在CHAR或VARCHAR列中，他们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快非常多。在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT *查询就不是非常好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。这也是BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你能搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索BLOB或TEXT值。把BLOB或TEXT列分离到独立的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，能让你把原数据表中的数据列转换为固定长度的数据行格式，那么他就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。他还使你在主数据表上运行SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。 高效率地载入数据在大多数情况下，你所关注的是SELECT查询的优化，因为SELECT查询是最常见的查询类型，而且怎么优化他们又不是太简单。和此形成对比，把数据载入数据库的操作就相对直接了。然而，你仍然能利用某些策略来改善数据载入操作的效率。基本的原理如下所示： 批量载入比单行载入的效率高，因为在每条记录被载入后，键缓存（key cache）不用刷新（flush）；能在这批记录的末尾刷新键缓存。键缓存刷新的频率减少得越多，数据载入的速度就越快。 没有索引的数据表的载入速度比有索引的要快一些。如果存在索引，不仅要把记录添加到数据文件中，还必须修改索引来反映新增的记录。 较短的SQL语句比较长的SQL语句快，因为他们所涉及到服务器端分析过程较少，同时通过网络把他们从客户端发送到服务器上的速度也更快。其中有些因素看起来是次要的（尤其是最后一个），不过如果你载入的数据非常多，那么即使非常小的效率差异也会导致一定的性能差别。我们能从前面的一般原理得出几条怎么快速载入数据的实践结论： LOAD DATA（所有形式的）比INSERT效率高，因为他是批量载入数据行的。服务器只需要分析和解释一条语句，而不是多条语句。同样，索引只需要在所有的数据行被处理过之后才刷新，而不是每行刷新一次。 不带LOCAL的LOAD DATA比带有LOCAL的LOAD DATA的速度要快。不带LOCAL的时候，文件必须位于服务器上，而且你必须拥有FILE权限，不过服务器却能直接从磁盘上读取文件。使用LOAD DATA LOCAL的时候，客户端读取文件并通过网络把他发送给服务器，速度慢一些。 如果你必须使用INSERT，那么试着使用在一个语句中指定多个数据行的形式：INSERT INTO tbl_name VALUES(.),(.),. ;在这个语句中指定的数据行越多，效果就越好。这会减少必要的语句数量，并最小化索引刷新的次数。这一条结论看起来和前面所讨论的语句越短，执行速度越快相矛盾，不过实际上并不矛盾。这儿所讨论的是同时插入多个数据行的一个INSERT语句所花费的开销比功能相同的多个单行INSERT语句的花费的开销要小一些，并且多行语句消耗的索引刷新开销也少一些。如果你使用mysqldump生成数据库备份文件，那么MySQL 4.1会默认地生成多行INSERT语句：他会激活-opt (优化)选项，而这个选项会激活-extended-insert选项，该选项生成多行INSERT语句，还存在其他一些选项也能使数据被载入的时候，转储文件被处理的效率更高。对于MySQL 4.1以前的版本，你能明确地指定-opt或-extended-insert选项。 使用mysqldump的时候要避免使用-complete-insert选项；他生成的INSERT语句是每个数据行一条语句的，语句总共会非常长，比多行语句需要的分析操作更多。 如果你必须使用INSERT语句，那么在可能的情况下，对他们进行分组以减少索引的刷新。对于事务性的存储引擎，在单个事务中提交，而不是在自动提交（autocommit）模式下提交INSERT语句能实现这样的功能：START TRANSACTION;INSERT INTO tbl_name . ;INSERT INTO tbl_name . ;INSERT INTO tbl_name . ;COMMIT;对于非事务性的存储引擎，获取数据表上的写入锁，他被锁定的时候提交INSERT语句：LOCK TABLES tbl_name WRITE;INSERT INTO tbl_name . ;INSERT INTO tbl_name . ;INSERT INTO tbl_name . ;UNLOCK TABLES;无论采用哪种方法，你得到的好处都是相同的：索引在所有的语句都被执行之后才刷新一次，而不是每个INSERT语句刷新一次索引。后面介绍了在自动提交模式下或数据表没有被锁定的时候发生的情况。 对于MyISAM数据表，减少索引刷新的另外一个策略是使用DELAYED_KEY_WRITE表选项。使用这个选项的时候，数据行会像平常相同即时写入数据文件中，不过键缓存只是偶尔刷新一次，而不是在每次插入操作之后都需要刷新。如果要在服务器上全方面地使用延迟索引刷新，那么就需要使用-delay-key-write选项来启动mysqld。在这种情况下，每个数据表的索引块写入操作都会被延迟，直到这些数据块必须为其他的索引值提供空间、或执行了FLUSH TABLES命令、或数据表被关闭的时候才执行操作。如果你选择了对MyISAM数据表使用延迟键写入，那么不正常的服务器关闭可能会引起索引值的丢失。这不是致命的问题，因为MyISAM索引能依据数据行来进行修复，不过如果想让修复过程出现，你就必须使用-myisam-recover=FORCE选项来启动服务器。这个选项会使服务器在打开MyISAM数据表的时候检查他们，如果有必要就自动地修复他们。对于复制（replication）从属服务器，你可能希望使用-delay-key-write=ALL来延迟所有的MyISAM数据表索引的刷新，不管在主服务器上最初是怎么建立他们的。 使用压缩的客户端/服务器协议来减少网络上数据传输的数量。对于大多数MySQL客户端来说，我们都能使用-compress命令行选项来指定他。通常，这个选项只是在较慢的网络上使用，这是因为压缩操作会花费大量的处理器时间。 让MySQL替你插入默认值。也就是说，无论怎么都不要给INSERT语句中那些能赋予默认值的列指定值。平均起来，你的语句更短，减少了通过网络发送到服务器的字符数量。此外，由于语句包含的值较少，服务器执行的分析和值转换操作也较少。 对于MyISAM数据表，如果你必须把大量的数据载入一个新表，最佳建立不带索引的表，载入数据，然后建立索引，这样的工作次序的速度要快一些。一次性地建立索引比每行都更新索引的速度要快一些。对于已带有索引的表，如果预先删除或禁止索引，后来再重新建立或激活索引，那么数据载入的速度也要快一些。这些策略不能应用于InnoDB或BDB表，他们没有对分离的索引建立过程进行优化。如果你考虑使用删除或禁止索引的策略，把数据载入MyISAM数据表，那么在评估获得的优势的时候，就需要考虑整个环境。如果你把少量的数据载入大型的数据表中，那么在没有所有特别准备工作的情况下，重新建立索引花费的时间可能比载入数据的时间还要长。要删除并且重新建立索引，需要使用DROP INDEX和CREATE INDEX，或使用和索引相关的ALTER TABLE。禁止和激活索引有两种办法： 你可用使用ALTER TABLE的DISABLE KEYS和ENABLE KEYS形式：ALTER TABLE tbl_name DISABLE KEYS;ALTER TABLE tbl_name ENABLE KEYS;这些语句关闭或打开表中非唯一（non-unique）索引的更新过程。ALTER TABLE的DISABLE KEYS和ENABLE KEYS子句是索引禁止和激活操作的推荐方法，因为服务器也是这样操作的（如果你使