一、选择利于高效查询的数据类型

  • 数据类型的选择，会在许多方面影响查询性能。

多用数字运算，少用字符串运算

  • 数字运算通常比字符串运算更快。
  • 以比较运算为例。数字之间的比较，可以在一个运算里完成。而字符串之间的比较，则 需要进行多次字节与字节，或字符与字符的比较才能完成，而且字符串越长，比较的次数越多。
  • 如果字符串类型列的取值个数有限，那么可以选用ENUM或SET类型，这样可以获得数 字运算所带来的好处。这些类型在内部是以数字形式表示的，处理效率更高。
  • 一起来看看字符串的其他表示方法。例如，以点记号表示的IP地址，如192.168.0.4，通常就是一个字符串。但这种IP地址很容易转换成整数形式，即可以把构成IP地址的4组数字依次存入INT UNSIGNED类型的4个字节里。整数不仅节约存储空间，而且还可以加快检索速度。但从另一方面来看，把IP地址表示成INT值又会给模式匹配运算带来困难，例如，你想要査找给定子网里的IP地址，就要用到模式匹配。或许，你还可以用位掩码运 算来做这样的事情。这类问题说明：不能只能考虑空间问题，还必须根据数据的具体用途和用法，为它们选择一种最合适的表示形式。(对于IP地址这个示例，不管你做何选择，INET_ATON()和INET_NTOA()函数都可以帮你完成两种表示形式之间的转换)。

当较小类型够用时，就不用较大类型

  • MySQL处理较小类型的速度比较大类型快。尤其是对于字符串，其处理时间与长度有直接关系。
  • 选用较小类型的另一个好处是，可以让整个表变得更小，从而减少在磁盘读写方面的开 销。对于那些带索引的列，较短小的值还能进一步提升性能。除了索引能加快査询速度以外，短键值的处理速度也较长键值的快。
  • 对于使用固定长度数据类型的列，应该根据其取值范围选用最小的类型。如果MEDIUMINT够用，就不选BIGINT。如果FLOAT足以满足精度要求，就不选DOUBLE。如果选用固定长度的CHAR列，则不要把它们设置得太长。如果存储在列里的最长值有40个字符，那么就把它定义成CHAR(40)，而不要定义为CHAR(255)。
  • 对于可变长度的类型，选用较小的类型仍可以节约空间。BLOB类型会使用2个字节记录 值的长度，而LONGBLOB类型会使用4个字节。如果要存储的值的长度没有超过64KB，那么选用BLOB可以让每个值节省2个字节。(对于TEXT类型，也有类似的考虑)。

把数据列声明成NOT NULL

  • 如果数据列为NOT NULL，那么MySQL对它的处理速度也会更快。这是因为在査询处理期间，不再需要检查该列的值是否为NULL。这样可以让你编写出更为简单的査询，因为 不用再把NULL当作一种特例去检査，并且更简单的査询处理起来通常会更快。

考虑使用ENUM列

  • 如果字符串列的基数低(即差异值的个数很少)，则可以考虑把它转换成ENUM列。ENUM值的处理速度很快，因为它们的内部表示形式皆为数字。

使用PROCEDURE ANALYSE()

  • 运行PROCEDURE ANALYSE()，可以看到许多关于表里各列的信息：
SELECT * FROM tbl_name PROCEDURE ANALYES();
SELECT * FROM tbl_name PROCEDURE ANALYES(16,256);
  • 输出结果里的一列，对适用于表里各个列的优化数据类型给出了建议。
  • 第二个示例会告知PROCEDURE ANALYSE()不要建议这样的ENUM类型：它包含的值 超过16个，或者总长度超过256个字节(可以根据实际情况更改这些值)。如果不加这些限制，那么输出结果可能会很长；对于ENUM类型的定义，其可读性很差。
  • 基于PROCEDURE ANALYSE()的输出，你可能会发现，可以修改一下自己的表，利用效率更高的类型。想要更改列的类型，可以使用ALTER TABLE。

整理表碎片

  • 对于那些频繁修改的表，尤其是那些包含有可变长度数据列的表，往往会产生大量碎片。碎片是有害的，因为它会造成存储表的磁盘块空间的浪费。
  • 随着时间的推移，你必须读取更多的磁盘块，才能获得有效的行，而这无疑会降低性能。包含可变长度行的表都会产生碎片，但是BLOB或TEXT列受到的影响特别显著，因为它们的大小变化非常大。
  • 定期使用OPTIMIZE TABLE，可以消除或减少碎片化的MylSAM或InnoDB表里的空间浪 费，并有助于防止性能降低。
  • 适用于各种存储引擎的碎片整理方法是：先用mysqldump转储表，然后再利用这个转储文件重建它：
mysqldump db_name tbl_name > dump.sql
mysql db_name < dump.sql

把数据压缩到BLOB或TEXT列

  • 使用BLOB或TEXT列来存储那些可在应用程序里对其进行压缩和解压缩的数据，能够达 到使用单个检索操作(而非多个操作)找出所有内容的目的。
  • 此办法特别适用于存储那些难以用标准表结构表示的数据，或者那些会随时间变化的数据。
  • 例如有一个表，这个表中存储了Web问卷里各个题目的答案。当需要往那个问卷里增加新题目时，可以用ALTER TABLE来往表里增加列。这类问题的另一种解决方案是：让负责处理Web表单的应用程序把答案数据压缩成某种结构的数据，然后再把它插到单个的BLOB或TEXT列里。例如，可以使用XML来表示各个问卷的答案，然后把这个XML字符串存储到一个TEXT列里。这种做法会增加客户端应用的负担，需要客户端对数据进行编码(当从表中检索行时，还需要对数据进行解码)，但是它大大简化了表的结构，并且在修改问卷时不需要更改表的结构。
  • 另一方面，BLOB和TEXT值也会带来一些麻烦，尤其是在执行大量的DELETE或 UPDATE操作时。删除这些值会在表里留下大量的空白，而这些空白在将来会被一个行或者长短不同的多个行所填补。可以使用前面讨论过的碎片处理技术来解决这个问题。

使用合成索引

  • 合成索引列有时很有用。
  • 一种做法是，先根据表里的其他列计算出一个散列值，把它存储到一个单独的列里。然后通过搜索散列值来检索行。不过，这个技术只适用于精确匹配型査询。(散列值对于使用像"<"或">="这样的运算符实现的范围搜索毫无用处)。散列值可以通过MD5()函数来生成。也可以选择使用函数SHA1()或CRC32()。当然，还可以在应用程序里用你自己的算法来计算散列值。请记住，数字型散列值的存储效率非常高。如果选用的散列算法有可能生成带有尾部空格的字符串，那么请不要选用那些会自动去除尾部空格的数据类型来存储它们。
  • 合成散列索引对BLOB和TEXT列非常有用。与直接搜索BLOB或TEXT列自身相比，把散列值当作标识符值进行査找，可以让查找速度更快。

避免检索很大的BLOB或TEXT值，除非迫不得已

  • 例如，只有在确定WHERE子句所限制的结果就是你想要找的那些行的时候，才可以使用检索所有行的SELECT *査询。否则，你可能会毫无目的地通过网络拉取一些很大的值。此时，把BL0B或TEXT值的散列标识符信息存储在合成索引列中，会很有帮助。可以先搜索列，以确定想要的行；然后再从这些行里把那些BLOB或TEXT值检索出来。

把BLOB或TEXT列剥离出来形成一个单独的表

  • 在某些场合，把表里的BLOB或TEXT列剥离出来，存入一个附表，可能会更有意义，前提是，这样做可以让你把这个表的其他列转换成行固定长度的格式。这样能够减少主表里的碎片，并且可以让你享受到行固定长度所带来的好处。
  • 此外，它还能让你在主表上运行SELECT *査询，不用通过网络拉取大的BLOB或TEXT值 。
二、选择利于高效查询的表存储格式
  • 有些存储引擎实现了多种存储格式，其中每一种都有其自己的性能特性。下列建议为选择合适的格式提供了一些一般性的指导，但对于某个具体的应用，最好的做法是执行测试，以进一步验证某种格式是否真的比另一个更优秀。
  • 如果所有列的长度固定，那么MylSAM存储引擎默认会使用固定长度的行，但如果任何一列的长度都是可变的，那么它会使用可变长度的行。对于字符串列，可以通过是选用CHAR还是VARCHAR（或者是选用BINARY，还是选用VARBINARY）来改变这一情况。在空间和时间之间要多做综合考虑。对于最大长度为n的列，CHAR在每一行都要占用n个字符空间。而VARCHAR平均占用的空间只有它的一半，因此如果空间很紧张，则需要使用VARCHAR列。如果速度是主要关心的内容，并且可以负担空间代价，那么可以使用(固定长度的)CHAR列，因为MylSAM处理固定长度的行的速度，比变长的行快。
  • 对于那些会频繁修改，并因此而产生大量碎片的表，更是如此：
    • 对于变长行，由于各行的大小不一，因此当执行过多的删除或更新操作时，会产生更多的碎片。为维护好性能，你需要定期运行OPTIMIZE TABLE。对于固定长度的行，这一点不是问题。
    • 当表崩溃时，具有固定长度行的表更易于重建。对于固定长度的行，每一行的开始位置都是确定的，因为它们都是行大小的倍数，而对于变长行，情况则有所不同。这个问题与査询处理的性能无关，但是它可以加速表的修复过程。
  • 由于MEMORY表使用的是固定长度的行，而且其中的CHAR和VARCHAR列都被隐式地当作CHAR来对待，因此不管你选择哪一个都没关系。
  • InnoDB并不会区别对待固定长度列和可变长度列（所有行都使用一个指向列值的头指针 ），所以使用CHAR列在本质上不会比使用VARCHAR列更简单。因此，主要的性能因素在于行占用的存储量。
  • 对于最大长度为n的列，VARCHAR平均占用的空间比CHAR更少，并且减少了存储量以及为处理行而发生的磁盘I/O数量。即使对于NULL值，CHAR也会占用n个字符空间，因 此，对于有许多NULL值的列，使用VARCHAR的优势会更加明显。
  • 当创建InnoDB表时，请选择其特性与存储在表中的数据完美匹配的行存储格式：
    • 默认情况下，InnoDB会使用COMPACT行格式。这种选择适合于大部分的情形。
    • 对于包含重复数据的表，使用COMPRESSED行格式，能带来更多的好处。这种表占用的空间更少(从而减少了读取它的那些I/O操作的数量)，而节省下来的读取数据的时间，远远超过解压它所需的CPU时间。压缩格式对于存储随机值或已压缩过的值的表没有用。
    • 对于带有长BLOB或TEXT值的表，DYNAMIC行格式最有效。
  • 想要为新的InnoDB表指定行格式，可以使用ROW_FORMAT表选项。例如:
CREATE TABLE t1(...)ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
  • 想要检査已有表的行存储格式，可以使用SHOW TABLE STATUS。
  • 想要更改表的格式，可以使用ALTER TABLE：
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
  • 行格式COMPRESSED和DYNAMIC需要Barracuda文件格式，而它又要求正确设置系统 变量innodb_file_per_table和innodb_file_format。（可以参阅后面"配置InnoDB存储引擎"文章）。
三、高效加载数据
  • 在大部分时间里，你最关心的问题很可能是对SELECT语句的优化，因为它们是最常用的査询 语句，并且想要弄明白如何优化它们并不总是件简单的事情。相比之下，将数据加载到数据库 则较为简单。
  • 尽管如此，还是有许多策略可用于提高数据加载的效率。这些基本原则如下所述：
    • 把数据从缓存刷新到磁盘的次数越少，那么数据加载的速度就越快。因此，批量加载的效率比单行加载的效率更高，因为插入的行可以先缓存，然后在加载操作结束时再刷新到磁盘。与逐行刷新到磁盘相比，在加载结束时一次性刷新，能够显著地减少磁盘I/O操作。
    • 表的索引越少，加载速度越快。如果有多个索引，那么不仅要把行的内容添加到表里，而且要修改每个索引，这样才能反映出有新的行加入。因此，请不要创建毫无必要的引索，或者，如果这些索引已存在，请删除它们。
    • 与长SQL语句相比，短语句的数据加载速度更快。这是因为它们在服务器上的解析操作会更少，并且它们可以更加快速地通过网络从客户端发送到服务器。
  • 在上面这几个因素中，有的看上去微不足道(尤其是最后一个)。但是如果加载的数据量很大，那么即使很小的效率影响因素也会造成很大的影响。依据这里讨论的一般性原则，我们可以得出几个有关如何快速加载数据的实用结论。
  • LOAD DATA(包括各种形式)的效率比INSERT的高，因为它是批量加载行。服务器一次只能解析和解释一条语句，而不会同时处理几条语句。另外，只有在所有行都处理完之后，索引才会需要刷新，而不是在每个行处理完之后，便立即去刷新。
  • LOAD DATA在不带LOCAL的情况下效率会更高。如果不带LOCAL，那么数据文件必须加载到服务器上，并且必须要有FILE权限，这时，服务器可以直接从磁盘读取这个文件。
  • 对于LOAD DATA LOCAL，客户端程序会先读取文件，然后通过网络将它发送到服务器。因此，这种方式会慢一些。
  • 如果必须使用INSERT，那么请尝试使用那种允许在单条语句里插入多个行的格式： 
INSERT INTO tbl_name VALUES(...),(...),...;
  • 在这种语句里，指定的行越多，效果越好。这样可以减少总的所需要语句数量，并大大减少索引刷新的次数。与前面讨论过的那条原则(即短语句处理起来比长语句更快)相比，这条原则看上去与之存在矛盾。但实际上并不矛盾。这里强调的是：与使用一组等效的插入单行的 INSERT语句相比，使用单条可插入多个行的INSERT语句，总体上花费的时间会更短，而且服务器在处理多行插入语句时，需要的索引刷新次数会更少。
  • 如果使用mysqldump程序来生成数据库备份文件，那么它会默认生成可插入多行的INSERT语句：启用--opt(优化)选项，此选项会打开--extended-insert选项，产生可插入多行的INSERT语句，此外，还会启用一些其他选项，使得在重新加载转储文件时，能更高效地处理转储文件。
  • 要避免在mysqldump程序里使用--complete-insert选项。否则，最终生成的INSERT语句将是针对单行的，与可插入多行的语句相比，它们更长，且需要执行更多的解析处理。
  • 如果必须使用多条INSERT语句，则请尽可能把它们分组，以减少刷新索引的次数。
  • 对于支持事务的存储引擎，则可以在单个事务里执行这些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;
  • 无论哪种情况，你都能获得同样的好处：只有在所有语句都执行完以后，MySQL才会刷新一次索引；而不是在每个INSERT语句执行完之后都刷新一次。当在自动提交模式下，或者表未被锁定时，才会出现第二种情况。
  • 对于MylSAM表，减少索引刷新次数的另一种策略是，使用DELAY_KEY_WRITE表选项。
  • 使用这个选项，行便可以像往常一样被立刻写入数据文件，但键缓存只有在必要时才刷新一次，而不是每次插入之后都会立刻刷新。
  • 为了在服务器范围的基础上使用延迟索引刷新，可以先将delay_key_write系统变量设置成ALL，然后再启动raysqld。此时，服务器会把表的索引块写入操作，延迟到为了给其他索引值腾出空间而必须把存储块刷新到磁盘为止，或者延迟到执行FLUSH TABLES语 句为止，或者延迟到表被关闭为止。
  • 如果对MylSAM表使用"键写入延迟"功能，那么当服务器意外关机时，可能会导致索引值 的丢失。这并不是一个致命问题，因为MylSAM表的索引可以根据其数据行进行修复。
  • 为了确保真的进行修复，在启动服务器时，可以把myisam_recover_options系统变量设置成某个包含FORCE选项的值。这个选项会强制服务器在打开MylSAM表时对它们进行检査，并根据需要自动修复它们。
  • 对于复制机制中的从服务器，你可能会把delay_key_write设置成ALL，以延迟所有MylSAM表的索引刷新，不管它们在主服务器上是如何创建的。
  • 使用压缩的客户端/服务器协议，可以减少通过网络传送的数据量。对于大部分的MySQL客户 端程序，都可以使用--compress命令行选项来指定。通常情况下，这种做法只适合用在传输速率较低的网络上，因为压缩过程会占用一部分处理器的时间。
  • 让MySQL插入默认值。也就是说，不在INSERT语句里指定各个列，让其随意分配为默认值。平均起来看，你的语句会更短一些，从而可以减少通过网络发往服务器的字符数。此外，因为这些语句包含的值较少，所以服务器执行解析和值转换的操作也会较少。
  • 对于MylSAM表，如果你需要将大量的数据加载到某个新表里，那么可以先创建不带索引的表，然后再创建索引。一次性创建全部的索引要比逐行修改它们更快一些。对于已带有索引的表，如果事先删除索引或关闭它，事后再重建索引或重新激活它，那么数据的加载速度可能会更快。
  • 如果要删除和重建索引，可以使用DROP INDEX和CREATE INDEX语句，或者使用ALTER TABLE语句与索引有关的形式。
  • 如果要关闭索引或重新激活它，那么可以使用ALTER TABLE的DISABLE KEYS和ENABLE KEYS形式，它们可以分别打开和关闭表的非唯一性索引：
ALTER TABLE tbl_name DISABLE KEYS;
... 多条用于加载内容的语句 ...
ALTER TABLE tbl_name ENABLE KEYS;
  • 如果使用LOAD DATA语句来把数据加载到一个空白的MylSAM表里，那么服务器会自动执行索引关闭和激活。另外，mysqldump默认也会添加ALTER TABLE语句。
  • 对于InnoDB表，删除和添加附表的速度很快，因此在加载大量数据之前可以考虑这样做。这种做法并不适用于主(集群)索引，因此请不要删除和添加那个索引。
  • 如果你正在考虑使用这种删除或关闭索引的策略来把数据加载到表，那么请对总体情况进行仔细评估 ，看看是否能从中获得某些好处 。如果只是加载少量的数据到某个大表里，那么与不做任何特殊处理只是加载数据相比，重建索引可能会花费更长的时间。
  • 前面所讨论的那些数据加载原则，也适用于混合査询环境：其中涉及需要执行多种不同类型操作的多个客户端程序。例如，你通常会希望尽量避免对那些经常会变化(即写入)的表，长时间运行SELECT査询。因为那样做会造成多个写入者之间的竞争，会降低性能。如果大部分的写入动作都是INSERT操作，那么这个问题也许能这样解决：先把各个新行添加到一个辅助表里，然后再将 这 些行定期地添加到主表。如果你需要能够立即访问新行，那么就不能使用这种方法。但是，如果你能保证在较短时间内不去访问它们，那么使用辅助表会有两个好处。 首先，可以减少发生在主表上的多条SELECT査询语句之间的竞争，让它们执行得更快。其次，从辅助表里把行批量加载到主表，总体来讲，会比单独加载各行更省时，因为大批量加载操作的速度更快。
  • 此策略的一种应用场景是：你想要把Web页面的访问记录从Web服务器写入MySQL数据库。此时，确定哪些条目需要立即进入主表，可能就不是一件需要优先考虑的事情。
四、调度、锁定和并发
  • 前面的重点是如何加快单个査询的速度。下面将深入了解MySQL的调度策略，以及存储引擎锁定层在多个客户端之间的并发性方面所具有的常规影响。当需要针对具体应用选择存储引擎时 ，可以参考下面的信息。对于给定应用里处于主导地位的査询类型，可能会有一种存储引擎比其他引擎更适合用于处理它。
  • 为了方便讨论，我们把负责检索(操作语句为SELECT)的客户端定义为读取者，把修改表(操作语句为DELETE、INSERT、REPLACE或UPDATE)的客户端定义为写入者。
  • MySQL的调度策略总结如下：
    • 写入的优先级比读取的高。
    • 表的写入操作一次只能进行一个，多个写入请求按其到达的先后顺序依次处理。
    • 可以同时处理多个对同一个表的读取操作。
  • InnoDB存储引擎利用行级的锁定操作，实现这种调度策略，但InnoDB只有在必要时才会锁定行。在许多情况下，如只读的操作完成之时，InnoDB根本不使用锁定操作。
  • 存储引擎MylSAM、MERGE和MEMORY，实现锁定操作的层级有所不同，使用了表锁定。因此，就竞争管理而言，它们具有完全不同的性能特征。只要客户端访问表，就必须要先锁定它。当这个客户端完成了对表的操作时，才会解除锁定。执行LOCK TABLES和UNLOCK TABLE语句可以显式地获得和解除锁定。但在通常情况下，服务器的锁定管理器都可以在需要时自动获得锁定，而在不需要时，解除它们。
  • 所需的锁定类型取决于客户端执行的是写入操作，还是读取操作：
    • 为把数据写入表，客户端必须具有对表进行互斥访问的锁定。在写入操作处理的过 程中，表处于一种不一致的状态，因为其中的数据行可能正在被删除、添加或更改，并且，为了保持匹配，表上的所有索引可能也需要随之更新。当表在不断变化时，允许其他客户端访问它，则会引发许多问题。允许两个客户端同时对一个表进行写入，显然是件很糟的事情，因为该表很快就会变得混乱不堪，最终导致无法使用。另外，允许客户端从一个不断变化的表中读取数据，也不是件好事，因为表有可能就在正被读取的那个位置进行更改，而此时的读取结果也会不准确。
    • 为从表里读取数据，客户端必须锁定它，以防止其他客户端把数据写入表，或者在读取数据期间修改它。但是，这个锁定操作不需要互斥访问。因为读取数据并不会更改表，所以一个读取者没有理由禁止其他读取者访问这个表。因此，读取锁定可以让其他客户端同时读取表的数据。
  • 存储引擎使用的锁定级别，对客户端之间的并发性有显著的影响。假设有两个客户端，它们 各自都想要在给定的表里更新某行。为完成这个更新，每个客户端都会需要一个写入锁定。此时，InnoDB表的并发性明显要比MylSAM表的并发性好很多。对于InnoDB表，只要两个客户 端不会同时更新同一行，那么两个更新操作就可以同时处理。对于MylSAM表，其存储引擎会为第一个客户端获得表锁定，从而导致第二个客户端阻塞，一直到第一个客户端操作完成为止。
  • 一般情况下，更精细的锁定会有更好的并发性，因为如果各个客户端使用的是表的不同部分，那么可以让更多的客户端同时使用这个表。实际的影响是：不同的存储引擎适合于不同的査询语句混合情况。
    • 当有许多更新操作时，InnoDB表可以提供更好的性能。因为完成锁定操作的级别是行级，而非表级，所以表被锁定的范围相对较小。这种做法可以减少锁定竞争，从而增强并发性。
    • MylSAM表的检索速度极快。但是，在有多个检索和更改操作混杂在一起的环境里，特别 是当检索需要运行较长时间时，使用表级锁定可能会引发问题。在这些情况下，更新操作可能需要等待很长时间才能得到处理。
  • 就死锁防止而言，表锁定能比更精细的锁定带来更多的好处：
    • 使用表锁定，不会出现死锁问题。服务器可以通过査看语句来确定需要哪些表，并提前把它们全部都锁定。
    • 对于InnoDB 表，可能会出现死锁问题，因为在事务开始的时候，该存储引擎还没有获得所有必要的锁定。 事实上，在事务处理过程中，只有在必要时才需要获得锁定。因此，有可能出现这种情况，即两个査询都获得了锁定，然后它们会试图进一步获得这样的锁定：它们每一个都在期望那些已占用的锁定被解除。结果，每一个客户端都在它能继续下一步操作之前，占据着其他客户端所 需要的锁定。这就会导致死锁，并且只有服务器中止其中一个事务才能解决此问题。