10.3 使你的数据尽可能小
最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小。这能给出巨大的改进，因为磁盘读入较快并且通常也用较少的主存储器。如果在更小的列上做索引，索引也占据较少的资源。
你能用下面的技术使表的性能更好并且使存储空间最小：
尽可能地使用最有效(最小)的类型。MySQL有很多节省磁盘空间和内存的专业化类型。
如果可能使表更小，使用较小的整数类型。例如，MEDIUMINT经常比INT好一些。
如果可能，声明列为NOT NULL。它使任何事情更快而且你为每列节省一位。注意如果在你的应用序中你确实需要NULL，你应该毫无疑问使用它，只是避免缺省地在所有列上有它。
如果你没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。见10.6 选择一种表类型。
每张桌子应该有尽可能短的主索引。这使一行的辨认容易而有效。
对每个表，你必须决定使用哪种存储/索引方法。见9.4 MySQL表类型。也可参见10.6 选择一种表类型。
只创建你确实需要的索引。索引对检索有好处但是当你需要快速存储东西时就变得糟糕。如果你主要通过搜索列的组合来存取一个表，以它们做一个索引。第一个索引部分应该是最常用的列。如果
你总是使用许多列，你应该首先以更多的副本使用列以获得更好的列索引压缩。
如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。MySQL支持在一个字符列的一部分上的索引。更短的索引更快，不仅因为他们占较少的磁盘空间而且因为他们将在索引缓存中给你更多的命中率并且因此有更少磁盘寻道。见10.2.3 调节服务器参数。
在一些情形下，分割一个经常被扫描进2个表的表是有益的。特别是如果它是一个动态格式的表并且它可能使一个能用来扫描后找出相关行的较小静态格式的表。
10.4 MySQL索引的使用
索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。
所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。见7.27 CREATE INDEX句法。
索引用于：
快速找出匹配一个WHERE子句的行。
当执行联结时，从其他表检索行。
对特定的索引列找出MAX()或MIN()值。
如果排序或分组在一个可用键的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。
在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。
假定你发出下列SELECT语句：
mysql>; SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个3行列索引(col1,col2,col3)，你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。
如果列不构成索引的最左面前缀，MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句：
mysql>; SELECT * FROM tbl_name WHERE col1=val1;
mysql>; SELECT * FROM tbl_name WHERE col2=val2;
mysql>; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果一个索引存在于(col1、col2、col3)上，只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前缀。
如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串，MySQL也为LIKE比较使用索引。例如，下列SELECT语句使用索引：
mysql>; select * from tbl_name where key_col LIKE “atrick%”;
mysql>; select * from tbl_name where key_col LIKE “at%_ck%”;
在第一条语句中，只考虑有”atrick” <= key_col < "atricl"的行。在第二条语句中，只考虑有"at" <= key_col < "au"的行。
下列SELECT语句将不使用索引：
mysql>; select * from tbl_name where key_col LIKE “%Patrick%”;
mysql>; select * from tbl_name where key_col LIKE other_col;
在第一条语句中，LIKE值以一个通配符字符开始。在第二条语句中，LIKE值不是一个常数。
如果 column_name 是一个索引，使用column_name IS NULL的搜索将使用索引。
MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列：=、>;、>;=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的LIKE的列。
任何不跨越的在WHERE子句的所有AND层次的索引不用来优化询问。
下列WHERE子句使用索引：
… WHERE index_part1=1 AND index_part2=2
… WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
… WHERE index_part1='hello' AND index_part_3=5
/* optimized like “index_part1='hello'” */
这些WHERE子句不使用索引：
… WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
… WHERE index=1 OR A=10 /* No index */
… WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */
10.5 存取或更新数据的查询速度
首先，一件事情影响所有的询问。你有的许可系统设置越复杂，你得到更多的开销。
如果你不让任何GRANT语句执行，MySQL将稍微优化许可检查。因此如果你有很大量，值得花时间来避免授权，否则更多的许可检查有更大的开销。
如果你的问题是与一些明显的MySQL函数有关，你总能在MySQL客户中计算其时间：
mysql>; select benchmark(1000000,1+1);
+————————+
| benchmark(1000000,1+1) |
+————————+
| 0 |
+————————+
1 row in set (0.32 sec)
上面显示MySQL能在PentiumII 400MHz上以0.32秒执行1,000,000个+表达式。
所有MySQL函数应该被高度优化，但是以可能有一些例外并且benchmark(loop_count,expression)是找出是否你的查询有问题的一个极好工具。
10.5.1 估计查询性能
在大多数情况下，你能通过计算磁盘寻道估计性能。对小的表，你通常能在1次磁盘寻道中找到行(因为这个索引可能被缓冲)。对更大的表，你能估计它(使用 B++ 树索引)，你将需要：log(row_count)/log(index_block_length/3*2/(index_length + data_pointer_length))+1次寻道找到行。
在MySQL中，索引块通常是1024个字节且数据指针通常是4个字节，这对一个有一个索引长度为3(中等整数)的 500,000 行的表给你：log(500,000)/log(1024/3*2/(3+4)) + 1= 4 次寻道。
象上面的索引将要求大约 500,000 * 7 * 3/2 = 5.2M，(假设索引缓冲区被充满到2/3(它是典型的))，你将可能在内存中有索引的大部分并且你将可能仅需要1-2调用从OS读数据来找出行。
然而对于写，你将需要 4 次寻道请求(如上)来找到在哪儿存放新索引并且通常需2次寻道更新这个索引并且写入行。
注意，上述不味着你的应用程序将缓慢地以 N log N 退化！当表格变得更大时，只要一切被OS或SQL服务器缓冲，事情将仅仅或多或少地更慢。在数据变得太大不能被缓冲后，事情将开始变得更慢直到你的应用程序仅仅受磁盘寻道限制(它以N log N增加)。为了避免这个增加，索引缓冲随数据增加而增加。见10.2.3 调节服务器参数。
10.5.2 SELECT查询的速度
总的来说，当你想要使一个较慢的SELECT … WHERE更快，检查的第一件事情是你是否能增加一个索引。见10.4 MySQL 索引的使用。在不同表之间的所有引用通常应该用索引完成。你可以使用EXPLAIN来确定哪个索引用于一条SELECT语句。见7.22 EXPLAIN句法(得到关于一条SELECT的信息)。
一些一般的建议：
为了帮助MySQL更好地优化查询，在它已经装载了相关数据后，在一个表上运行myisamchk –analyze。这为每一个更新一个值，指出有相同值地平均行数（当然，对唯一索引，这总是1。）
为了根据一个索引排序一个索引和数据，使用myisamchk –sort-index –sort-records=1（如果你想要在索引1上排序)。如果你有一个唯一索引，你想要根据该索引地次序读取所有的记录，这是使它更快的一个好方法。然而注意，这个排序没有被最佳地编写，并且对一个大表将花很长时间！
10.5.3 MySQL怎样优化WHERE子句
where优化被放在SELECT中，因为他们最主要在那里使用里，但是同样的优化被用于DELETE和UPDATE语句。
也要注意，本节是不完全的。MySQL确实作了许多优化而我们没有时间全部记录他们。
由MySQL实施的一些优化列在下面：
删除不必要的括号：
((a AND b) AND c OR (((a AND b) AND (c AND d))))
->; (a AND b AND c) OR (a AND b AND c AND d)
常数调入：
(a->; b>;5 AND b=c AND a=5
删除常数条件(因常数调入所需)：
(B>;=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
->; B=5 OR B=6
索引使用的常数表达式仅计算一次。
在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对任何NOT NULL表达式也这样做。
无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。
如果你不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。
为每个子联结(sub join)，构造一个更简单的WHERE以得到一个更快的WHERE计算并且也尽快跳过记录。
所有常数的表在查询中的任何其他表前被首先读出。一个常数的表是：
一个空表或一个有1行的表。
与在一个UNIQUE索引、或一个PRIMARY KEY的WHERE子句一起使用的表，这里所有的索引部分使用一个常数表达式并且索引部分被定义为NOT NULL。
所有下列的表用作常数表：
mysql>; SELECT * FROM t WHERE primary_key=1;
mysql>; SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
对联结表的最好联结组合是通过尝试所有可能性来找到。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当廉洁时，该表首先被选中。
如果有一个ORDER BY子句和一个不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含不是来自联结队列中的第一个表的其他表的列，创建一个临时表。
如果你使用SQL_SMALL_RESULT，MySQL将使用一个在内存中的表。
因为DISTINCT被变换到在所有的列上的一个GROUP BY，DISTINCT与ORDER BY结合也将在许多情况下需要一张临时表。
每个表的索引被查询并且使用跨越少于30% 的行的索引。如果这样的索引没能找到，使用一个快速的表扫描。
在一些情况，MySQL能从索引中读出行，甚至不咨询数据文件。如果索引使用的所有列是数字的，那么只有索引树被用来解答查询。
在每个记录被输出前，那些不匹配HAVING子句的行被跳过。
下面是一些很快的查询例子：
mysql>; SELECT COUNT(*) FROM tbl_name;
mysql>; SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql>; SELECT MAX(key_part2) FROM tbl_name
WHERE key_part_1=constant;
mysql>; SELECT … FROM tbl_name
ORDER BY key_part1,key_part2,… LIMIT 10;
mysql>; SELECT … FROM tbl_name
ORDER BY key_part1 DESC,key_part2 DESC,… LIMIT 10;
下列查询仅使用索引树就可解决(假设索引列是数字的)：
mysql>; SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql>; SELECT COUNT(*) FROM tbl_name
WHERE key_part1=val1 AND key_part2=val2;
mysql>; SELECT key_part2 FROM tbl_name GROUP BY key_part1;
下列查询使用索引以排序顺序检索，不用一次另外的排序：
mysql>; SELECT … FROM tbl_name ORDER BY key_part1,key_part2,…
mysql>; SELECT … FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,…
10.5.4 MySQL怎样优化LEFT JOIN
在MySQL中，A LEFT JOIN B实现如下：
表B被设置为依赖于表A。
表A被设置为依赖于所有用在LEFT JOIN条件的表(除B外)。
所有LEFT JOIN条件被移到WHERE子句中。
进行所有标准的联结优化，除了一个表总是在所有它依赖的表之后被读取。如果有一个循环依赖，MySQL将发出一个错误。
进行所有标准的WHERE优化。
如果在A中有一行匹配WHERE子句，但是在B中没有任何行匹配LEFT JOIN条件，那么在B中生成所有列设置为NULL的一行。
如果你使用LEFT JOIN来找出在某些表中不存在的行并且在WHERE部分你有下列测试：column_name IS NULL，这里column_name 被声明为NOT NULL的列，那么MySQL在它已经找到了匹配LEFT JOIN条件的一行后，将停止在更多的行后寻找(对一特定的键组合)。
10.5.5 MySQL怎样优化LIMIT
在一些情况中，当你使用LIMIT #而不使用HAVING时，MySQL将以不同方式处理查询。
如果你用LIMIT只选择一些行，当MySQL一般比较喜欢做完整的表扫描时，它将在一些情况下使用索引。
如果你使用LIMIT #与ORDER BY，MySQL一旦找到了第一个 # 行，将结束排序而不是排序整个表。
当结合LIMIT #和DISTINCT时，MySQL一旦找到#个唯一的行，它将停止。
在一些情况下，一个GROUP BY能通过顺序读取键(或在键上做排序)来解决，并然后计算摘要直到键值改变。在这种情况下，LIMIT #将不计算任何不必要的GROUP。
只要MySQL已经发送了第一个#行到客户，它将放弃查询。
LIMIT 0将总是快速返回一个空集合。这对检查查询并且得到结果列的列类型是有用的。
临时表的大小使用LIMIT #计算需要多少空间来解决查询。
10.5.6 INSERT查询的速度
插入一个记录的时间由下列组成：
连接：(3)
发送查询给服务器：(2)
分析查询：(2)
插入记录：（1 x 记录大小）
插入索引：（1 x 索引）
关闭：(1)
这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。
表的大小以N log N (B 树)的速度减慢索引的插入。
加快插入的一些方法：
如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。
如果你从不同客户插入很多行，你能通过用INSERT DELAYED语句得到更高的速度。见7.14 INSERT句法。
注意，用MyISAM，如果在表中没有删除的行，能在SELECT:s正在运行的同时插入行。
当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。见7.16 LOAD DATA INFILE句法。
当表有很多索引时，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列过程：
有选择地用CREATE TABLE创建表。例如使用mysql或Perl-DBI。
执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。
使用myisamchk –keys-used=0 -rq /path/to/db/tbl_name。这将从表中删除所有索引的使用。
用LOAD DATA INFILE把数据插入到表中，这将不更新任何索引，因此很快。
如果你有myisampack并且想要压缩表，在它上面运行myisampack。见10.6.3 压缩表的特征。
用myisamchk -r -q /path/to/db/tbl_name再创建索引。这将在将它写入磁盘前在内存中创建索引树，并且它更快，因为避免大量磁盘寻道。结果索引树也被完美地平衡。
执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。
这个过程将被构造进在MySQL的某个未来版本的LOAD DATA INFILE。
你可以锁定你的表以加速插入。
mysql>; LOCK TABLES a WRITE;
mysql>; INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql>; INSERT INTO a VALUES (8,26),(6,29);
mysql>; UNLOCK TABLES;
主要的速度差别是索引缓冲区仅被清洗到磁盘上一次，在所有INSERT语句完成后。一般有与有不同的INSERT语句那样夺的索引缓冲区清洗。如果你能用一个单个语句插入所有的行，锁定就不需要。锁定也将降低多连接测试的整体时间，但是对某些线程最大等待时间将上升(因为他们等待锁)。例如：
thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts
如果你不使用锁定，2、3和4将在1和5前完成。如果你使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。因为INSERT, UPDATE和DELETE操作在MySQL中是很快的，通过为多于大约5次连续不断地插入或更新一行的东西加锁，你将获得更好的整体性能。如果你做很多一行的插入，你可以做一个LOCK TABLES，偶尔随后做一个UNLOCK TABLES(大约每1000行)以允许另外的线程存取表。这仍然将导致获得好的性能。当然，LOAD DATA INFILE对装载数据仍然是更快的。
为了对LOAD DATA INFILE和INSERT得到一些更快的速度，扩大关键字缓冲区。见10.2.3 调节服务器参数。
10.5.7 UPDATE查询的速度
更改查询被优化为有一个写开销的一个SELECT查询。写速度依赖于被更新数据大小和被更新索引的数量。
使更改更快的另一个方法是推迟更改并且然后一行一行地做很多更改。如果你锁定表，做一行一行地很多更改比一次做一个快。
注意，动态记录格式的更改一个较长总长的记录，可能切开记录。因此如果你经常这样做，时不时地OPTIMIZE TABLE是非常重要的。见7.9 OPTIMIZE TABLE句法。
10.5.8 DELETE查询的速度
删除一个记录的时间精确地与索引数量成正比。为了更快速地删除记录，你可以增加索引缓存的大小。见10.2.3 调节服务器参数。
从一个表删除所有行比删除行的一大部分也要得多。