优化你的老版本MySQL
2012-10-09 17:14:29 
阿炯
数据表的处理
主要内容有，选择表的类型，打开尽量少的表，锁定表与查询速度的关系以及如何优化表以达到提高查询速度的目的。由于数据的录入和清除操作，很容易在表的数据文件重产生碎片，对于拥有大型表的数据库，应该经常使用myisamchk维护程序来清除碎片，优化表时要选择一个好的时机，尽量在没有用户访问时优化表。
选择合适的表的类型，防止数据文件中产生碎块，同样可以大大优化检索的速度，是数据库的性能最大化。
选择一种表类型
用MySQL，当前(版本 3 .23)你能从一个速度观点在4可用表的格式之间选择。
·静态MyISAM
这种格式是最简单且最安全的格式，它也是在磁盘格式最快的。速度来自于数据能在磁盘上被找到的难易方式。当所定有一个索引和静态格式的东西时，它很简单，只是行长度乘以行数量。而且在扫描一张表时，用每次磁盘读取来读入常数个记录是很容易的。安全性来自于如果当写入一个静态MyISAM文件时，你的计算机崩溃，myisamchk能很容易指出每行在哪儿开始和结束，因此它通常能回收所有记录，除了部分被写入的那个。注意，在MySQL中，所有索引总能被重建。
·动态MyISAM
这种格式有点复杂，因为每一行必须有一个头说明它有多长。当一个记录在更改时变长时，它也可以在多于一个位置上结束。你能使用 OPTIMIZE table或myisamchk整理一张表。如果你在同一个表中有象某些VARCHAR或BLOB列那样存取/改变的静态数据，将动态列移入另外一个表以避免碎片可能是一个好主意。
·压缩MyISAM
这是一个只读类型，用可选的myisampack工具生成。
·内存(HEAP 堆)
这种表格式对小型/中型查找表十分有用。对拷贝/创建一个常用的查找表(用联结)到一个(也许临时)HEAP表有可能加快多个表联结。假定我们想要做下列联结，用同样数据可能要几倍时间。
SELECT tab1.a, tab3.a FROM tab1, tab2, tab3
WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;
为了加速它，我们可用tab2和tab3的联结创建一张临时表，因为用相同列( tab1.a )查找。这里是创建该表和结果选择的命令。
CREATE TEMPORARY TABLE test TYPE=HEAP
SELECT tab2.a as a2, tab3.a as a3 FROM tab2, tab3 WHERE tab2.a = tab3.a and c = 0;
SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;
SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;
静态(定长)表的特点
这是缺省格式。它用在表不包含VARCHAR、BLOB或TEXT列时候。所有的CHAR、NUMERIC和DECIMAL列充填到列宽度。非常快、容易缓冲、容易在崩溃后重建，因为记录位于固定的位置。
不必被重新组织(用myisamchk)，除非一个巨量的记录被删除并且你想要归还空闲磁盘空间给操作系统，通常比动态表需要更多的磁盘空间。
动态表的特点
如果表包含任何VARCHAR、BLOB或TEXT列，使用该格式。所有字符串列是动态的(除了那些长度不到4的列)。每个记录前置一个位图，对字符串列指出哪个列是空的('')，或对数字列哪个是零(这不同于包含NULL值的列)。如果字符串列在删除尾部空白后有零长度，或数字列有零值，它在位图中标记并且不保存到磁盘上。非空字符串存储为一个长度字节加字符串内容。
通常比定长表占更多的磁盘空间，每个记录仅使用所需的空间。如果一个记录变得更大，它按需要被切开多段，这导致记录碎片。
如果你与超过行长度的信息更新行，行将被分段。在这种情况中，你可能必须时时运行myisamchk -r以使性能更好。使用myisamchk -ei tbl_name做一些统计。
在崩溃后不容易重建，因为一个记录可以是分很多段并且一个连接(碎片)可以丢失。对动态尺寸记录的期望行长度是：
3
+ (number of columns + 7) / 8
+ (number of char columns)
+ packed size of numeric columns
+ length of strings
+ (number of NULL columns + 7) / 8
对每个连接有6个字节的惩罚。无论何时更改引起记录的增大，一个动态记录被链接。每个新链接将至少是20个字节，因此下一增大将可能在同一链连中。如果不是，将有另外一个链接。你可以用myisamchk -ed检查有多少链接。所有的链接可以用 myisamchk -r 删除。
压缩表的特点
一张用myisampack实用程序制作的只读表。所有具有MySQL扩展电子邮件支持的客户可以为其内部使用保留一个myisampack拷贝。
解压缩代码存在于所有MySQL分发，以便甚至没有myisampack的客户能读取用myisampack压缩的表，占据很小的磁盘空间，使磁盘使用量减到最小。
每个记录被单独压缩(很小的存取开销)。对一个记录的头是定长的(1-3 字节)，取决于表中最大的记录。每列以不同方式被压缩。一些压缩类型是：
通常对每列有一张不同的哈夫曼表。
后缀空白压缩。
前缀空白压缩。
用值0的数字使用1位存储。
如果整数列的值有一个小范围，列使用最小的可能类型来存储。例如，如果所有的值在0到255的范围，一个BIGINT列(8个字节)可以作为一个TINYINT列(1字节)存储。
如果列仅有可能值的一个小集合，列类型被变换到ENUM。
列可以使用上面的压缩方法的组合。
能处理定长或动态长度的记录，然而不能处理BLOB或TEXT列。
能用myisamchk解压缩。
MySQL能支持不同的索引类型，但是一般的类型是ISAM。这是一个B树索引并且你能粗略地为索引文件计算大小为(key_length+4)*0.67，在所有的键上的总和。(这是对最坏情况，当所有键以排序顺序被插入时。)
字符串索引是空白压缩的。如果第一个索引部分是一个字符串，它也将压缩前缀。如果字符串列有很多尾部空白或是一个总不能用到全长的VARCHAR列，空白压缩使索引文件更小。如果很多字符串有相同的前缀，前缀压缩是有帮助的。
MySQL内存表的特点
堆表仅存在于内存中，因此如果mysqld被关掉或崩溃，它们将丢失，但是因为它们是很快，不管怎样它们是有用的。
MySQL内部的HEAP表使用没有溢出区的100%动态哈希并且没有与删除有关的问题。
你只能通过使用在堆表中的一个索引的用等式存取东西(通常用=操作符)。
堆表的缺点是：
·你要为你想要同时使用的所有堆表需要足够的额外内存。
·你不能在索引的一个部分上搜索。
·你不能顺序搜索下一个条目(即使用这个索引做一个ORDER BY)。
·MySQL也不能算出在2个值之间大概有多少行。这被优化器使用来决定使用哪个索引，但是在另一方面甚至不需要磁盘寻道。
数据库表的数量的问题
在同一个数据库中创建大量数据库表的缺点是,如果你在一个目录中有许多文件，打开、关闭和创建操作将会很慢。如果你执行在许多不同表上的 SELECT语句，当表缓存满时，将有一点开销，因为对每个必须打开的表，另外一个必须被关闭。你可以通过使表缓冲更大些来减少这个开销。
为什么有这么多打开的表？
当你运行mysqladmin status时，你将看见象这样的一些东西：
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
如果你仅有6个表，这可能有点令人困惑。
MySQL是多线程的，因此它可以同时在同一个表上有许多询问。为了是2个线程在同一个文件上有不同状态的问题减到最小，表由每个并发进程独立地打开。这为数据文件消耗一些内存和一个额外的文件描述符。索引文件描述符在所有线程之间共享。
数据库表级锁定的问题
前面的内容主要将精力集中在使个别的查询更快上。MySQL 还允许影响语句的调度特性，这样会使来自几个客户机的查询更好地协作，从而单个客户机不会被锁定太长的时间。更改调度特性还能保证特定的查询处理得更快。我们先来看一下 MySQL 的缺省调度策略，然后来看看为改变这个策略可使用什么样的选项。出于讨论的目的，假设执行检索(SELECT)的客户机程序为读取程序。执行修改表操作(DELETE，INSERT，REPLACE 或 UPDATE)的另一个客户机程序为写入程序。
MySQL 的基本调度策略可总结如下：
·写入请求应按其到达的次序进行处理。
·写入具有比读取更高的优先权。
1、对此一个主要的问题如下：
·一个客户发出一个花很长时间运行的SELECT。
·然后其他客户在一个使用的表上发出一个UPDATE；这个客户将等待直到SELECT完成。
·另一个客户在同一个表上发出另一个SELECT语句；因为UPDATE比SELECT有更高的优先级，该SELECT将等待UPDATE的完成。它也将等待第一个SELECT完成！
对这个问题的一些可能的解决方案是：
·试着使SELECT语句运行得更快；你可能必须创建一些摘要(summary)表做到这点。
·用--low-priority-updates启动mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下，在先前情形的最后的SELECT语句将在INSERT语句前执行。
·你可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级。
·为max_write_lock_count指定一个低值来启动mysqld使得在一定数量的WRITE锁定后给出READ锁定。
·通过使用SQL命令：SET SQL_LOW_PRIORITY_UPDATES=1，你可从一个特定线程指定所有的更改应该由用低优先级完成。见SET OPTION句法。
·你可以用HIGH_PRIORITY属性指明一个特定SELECT是很重要的。见SELECT句法。
·如果你有关于INSERT结合SELECT的问题，切换到使用新的MyISAM表，因为它们支持并发的SELECT和INSERT。
·如果你主要混合INSERT和SELECT语句，DELAYED属性的INSERT将可能解决你的问题。INSERT句法。
·如果你有关于SELECT和DELETE的问题，LIMIT选项的DELETE可以帮助你。见DELETE句法。
2、INSERT DELAYED 在客户机方的作用
如果其他客户机可能执行冗长的 SELECT 语句，而且您不希望等待插入完成，此时 INSERT DELAYED 很有用。发布 INSERT DELAYED 的客户机可以更快地继续执行，因为服务器只是简单地将要插入的行插入。
不过应该对正常的 INSERT 和 INSERT DELAYED 性能之间的差异有所认识。如果 INSERT DELAYED 存在语法错误，则向客户机发出一个错误，如果正常，便不发出信息。例如，在此语句返回时，不能相信所取得的 AUTO_INCREMENT 值。也得不到惟一索引上的重复数目的计数。之所以这样是因为此插入操作在实际的插入完成前返回了一个状态。其他还表示，如果 INSERT DELAYED 语句的行在等待插入中被排队，并且服务器崩溃或被终止(用 kill -9)，那么这些行将丢失。正常的 TERM 终止不会这样，服务器会在退出前将这些行插入。
在表锁的帮助下实现调度策略。客户机程序无论何时要访问表，都必须首先获得该表的锁。可以直接用 LOCK TABLES 来完成这项工作，但一般服务器的锁管理器会在需要时自动获得锁。在客户机结束对表的处理时，可释放表上的锁。直接获得的锁可用 UNLOCK TABLES 释放，但服务器也会自动释放它所获得的锁。
执行写操作的客户机必须对表具有独占访问的锁。在写操作进行中，由于正在对表进行数据记录的删除、增加或更改，所以该表处于不一致状态，而且该表上的索引也可能需要作相应的更新。如果表处于不断变化中，此时允许其他客户机访问该表会出问题。让两个客户机同时写同一个表显然不好，因为这样会很快使该表不可用。允许客户机读不断变化的表也不是件好事，因为可能在读该表的那一刻正好正在对它进行更改，其结果是不正确的。
执行读取操作的客户机必须有一把防止其他客户机写该表的锁，以保证读表的过程中表不出现变化。不过，该锁无需对读取操作提供独占访问。此锁还允许其他客户机同时对表进行读取。读取不会更改表，所有没必要阻止其它客户机对该表进行读取。
MySQL 允许借助几个查询限修饰符对其调度策略施加影响。其中之一是 DELETE、INSERT、LOAD DATA、REPLACE 和 UPDATE 语句的 LOW_PRIORITY 关键字。另一个是 SELECT 语句的 HIGH_PRIORITY 关键字。第三个是 INSERT 和 REPLACE 语句的 DELAYED 关键字。
LOW_PRIORITY 关键字按如下影响调度。一般情况下，如果某个表的写入操作在表正被读取时到达，写入程序被阻塞，直到读取程序完成，因为一旦某个查询开始，就不能中断。如果另一读取请求在写入程序等待时到达，此读取程序也被阻塞，因为缺省的调度策略为写入程序具有比读取程序高的优先级。在第一个读取程序结束时，写入程序继续，在此写入程序结束时，第二个读取程序开始。
如果写入请求为 LOW_PRIORITY 的请求，则不将该写入操作视为具有比读取操作优先级高的操作。在此情形下，如果第二个读取请求在写入程序等待时到达，则让第二个读取操作排在等待的写入操作之前。仅当没有其他读取请求时，才允许写入程序执行。这种调度的更改从理论上说，其含义为 LOW_PRIORITY 写入可能会永远被阻塞。当正在处理前面的读取请求时，只要另一个读取请求到达，这个新的请求允许排在 LOW_PRIORITY 写入之前。
SELECT 查询的 HIGH_PRIORITY 关键字作用类似。它使 SELECT 插在正在等待的写入操作之前，即使该写入操作具有正常的优先级。
INSERT 的 DELAYED 修饰符作用如下，在表的一个 INSERT DELAYED 请求到达时，服务器将相应的行放入一个队列，并立即返回一个状态到客户机程序，以便该客户机程序可以继续执行，即使这些行尚未插入表中。如果读取程序正在对表进行读取，那么队列中的行挂起。在没有读取时，服务器开始开始插入延迟行队列中的行。服务器不时地停下来看看是否有新的读取请求到达，并进行等待。如果是这样，延迟行队列将挂起，并允许读取程序继续。在没有其他的读取操作时，服务器再次开始插入延迟行。这个过程一直进行到延迟行队列空为止。
此调度修饰符并非出现在所有 MySQL 版本中。下面的表列出了这些修饰符和支持这些修饰符的 MySQL 版本。可利用此表来判断所使用的 MySQL 版本具有什么样的功能：
语句类型 开始出现的版本
DELETE LOW_PRIOrITY 3.22.5
INSERT LOW+PRIOrITY 3.22.5
INSERT DELAYED 3.22.15
LOAD DATA LOW_PRIORITY 3.23.0
LOCK TABLES ... LOW_PRIORITY 3.22.8
REPLACE LOW_PRIORITY 3.22.5
REPLACE DELAYED 3.22.15
SELECT ... HIGH_PRIORITY 3.22.9
UPDATE LOW_PRIORITY 3.22.5
SET SQL_LOW_PRIORITY_UPDATES 3.22.5
对表进行优化
对表的长期使用，由于记录的删除与插入，会在表的数据文件产生碎片，下面的几种情况尤其会有产生碎片的效果：
·使用VARCHAR类型
频繁使用可变长列的结果就是使表非常容易产生碎片，如果空间允许，尽量采用定长类型CHAR。
·使用类型BLOB和TEXT
特别是进行大量的 DELETE 或 UPDATE 操作时更是如此。删除 BLOB 会在表中留下一个大空白，在以后将需用一个记录或可能是不同大小的多个记录来填充。
·删除了一个表的大部分，这同样会在表中留下大量空白。
为了消除表的碎片对性能的影响，需要对表进行优化。在第七章我们介绍了优化表的方法：
1、使用SQL语句OPTIMIZE
OPTIMIZE TABLE tbl_name
2、使用修复程序myisamchk或isamchk
----------------------------------
数据类型的使用
有助于效率的类型选择
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]])
例如：
mysql>SELECT * FROM student PROCEDURE ANALYSE();
mysql>SELECT * FROM student PROCEDURE ANALYSE(16,256);
相应输出中有一列是关于表中每列的最佳列类型的建议。第二个例子要求 PROCEDURE ANALYSE( ) 不要建议含有多于 16 个值或取多于 256 字节的 ENUM 类型(可根据需要更改这些值)。如果没有这样的限制，输出可能会很长；ENUM 的定义也会很难阅读。
根据 PROCEDURE ANALYSE( ) 的输出，会发现可以对表进行更改以利用更有效的类型。如果希望更改值类型，使用 ALTER TABLE 语句即可。
---------------------------
索引的使用
索引在优化查询中的作用，包括了索引优化查询的原理，索引是优化查询的最常用也是最有效的的方法，一个数据表，尤其是容量很大的表，建立合适的索引，会使查询的速度提高很大。
MySQL索引的使用
我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。在 MySQL 的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。
本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论 MySQL 的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。(虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。)
索引对单个表查询的影响
索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000 行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。
例如对下面这样的一个student表：
mysql>SELECT * FROM student
+------+---------+---------+---------+---------+
| id | name | english | chinese | history |
+------+---------+---------+---------+---------+
| 12 | Tom | 66 | 93 | 67 |
| 56 | Paul | 78 | 52 | 75 |
| 10 | Marry | 54 | 89 | 74 |
| 4 | Tina | 99 | 83 | 48 |
| 39 | William | 43 | 96 | 52 |
| 74 | Stone | 42 | 40 | 61 |
| 86 | Smith | 49 | 85 | 78 |
| 37 | Black | 49 | 63 | 47 |
| 89 | White | 94 | 31 | 52 |
+------+---------+---------+---------+---------+
这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生：
mysql>SELECT name,english FROM student WHERE english<60;
+---------+---------+
| name | english |
+---------+---------+
| Marry | 54 |
| William | 43 |
| Stone | 42 |
| Smith | 49 |
| Black | 49 |
+---------+---------+
其中，WHERE从句不得不匹配每个记录，以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。
如果，我们为english列创建一个索引：
mysql>ALTER TABLE student ADD INDEX (english) ;
+-------------------+
| index for english |
+-------------------+
| 42 |
| 43 |
| 49 |
| 49 |
| 54 |
| 66 |
| 78 |
| 94 |
| 99 |
+-------------------+
如上表，此索引存储在索引文件中，包含表中每行的english列值，但此索引是在 english的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，因此在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描(如二分查找法)。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。
因此在执行下述查询
mysql>SELECT name,english FROM user WHERE english<60;
其结果为：
+---------+---------+
| name | english |
+---------+---------+
| Stone | 42 |
| William | 43 |
| Smith | 49 |
| Black | 49 |
| Marry | 54 |
+---------+---------+
你应该可以发现，这个结果与未索引english列之前的不同，它是排序的，原因正式如上所述。
索引对多个表查询的影响
前面的讨论描述了单表查询中索引的好处，其中使用索引消除了全表扫描，极大地加快了搜索的速度。在执行涉及多个表的连接查询时，索引甚至会更有价值。在单个表的查询中，每列需要查看的值的数目就是表中行的数目。而在多个表的查询中，可能的组合数目极大，因为这个数目为各表中行数之积。
假如有三个未索引的表 t1、t2、t3，分别只包含列 c1、c2、c3，每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示：
此查询的结果应该为 1000 行，每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000(十亿)，比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时，将会怎样？很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：
1) 如下从表 t1 中选择第一行，查看此行所包含的值。
2) 使用表 t2 上的索引，直接跳到 t2 中与来自 t1 的值匹配的行。类似，利用表 t3 上的索引，直接跳到 t3 中与来自 t1 的值匹配的行。
3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。
在此情形下，我们仍然对表 t1 执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。
如上所述，MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。
多列索引对查询的影响
假定你发出下列SELECT语句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
你可以这样创建一个多列索引：
mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);
而你应该这样创建分开的单行列索引：
mysql>ALTER TABLE tble_name ADD INDEX(col1);
mysql>ALTER TABLE tble_name ADD INDEX(col1);
如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个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 "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";
在第一条语句中，只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中，只考虑有"Pat" <= key_col < "Pau"的行。
下列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层次使用索引，将不使用来索引优化查询。为了能够使用索引优化查询，必须把一个多列索引的前缀使用在一个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 */
MySQL索引的作用
所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。CREATE INDEX句法。
索引用于：
快速找出匹配一个WHERE子句的行。
在多个表的查询时，执行连接时加快了与其他表中的行匹配的行的搜索。
对特定的索引列找出MAX()或MIN()值。
如果排序或分组在一个可用索引的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。
在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。
索引的弊端
一般情况下，如果 MySQL 能够知道怎样用索引来更快地处理查询，它就会这样做。这表示，在大多数情况下，如果您不对表进行索引，则损害的是您自己的利益。可以看出，作者描绘了索引的诸多好处。但有不利之处吗？是的，有。实际上这些缺点被优点所掩盖了，但应该对它们有所了解。
首先，索引文件要占磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，索引文件加快了检索，但增加了插入和删除，以及更新索引列中的值的时间(即，降低了大多数涉及写入的操作的时间)，因为写操作不仅涉及数据行，而且还常常涉及索引。一个表拥有的索引越多，则写操作的平均性能下降就越大。在8.4.4节记录装载和修改的速度中，我们将更为详细地介绍这些性能问题，并讨论怎样解决。
选择索引的准则
创建索引的语法已经在4.5索引属性中进行了介绍。这里，我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则：
1、搜索的索引列，不一定是所要选择的列
换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列，例如：
SELECT
col_a ←不适合作索引列
FROM
Tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c ←适合作索引列
WHERE
col_d = expr ←适合作索引列
当然，所选择的列和用于 WHERE 子句的列也可能是相同的。关键是，列出现在选择列表中不是该列应该索引的标志。
出现在连接子句中的列或出现在形如 col1 = col2 的表达式中的列是很适合索引的列。查询中的 col_b 和 col_c 就是这样的例子。如果 MySQL 能利用连接列来优化一个查询，表示它通过消除全表扫描相当可观地减少了表行的组合。
2、使用惟一索引
考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处(不管搜索哪个值，都会得出大约一半的行)。
3、使用短索引
如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的，因为这个索引中不会有许多不同的值。)
4、利用最左前缀
在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同，索引一个列的前缀是利用该的前 n 个字符作为索引值。)
假如一个表在分别名为 state、city 和 zip 的三个列上有一个索引。索引中的行是按 state/city/zip 的次序存放的，因此，索引中的行也会自动按 state/city 的顺序和 state 的顺序存放。这表示，即使在查询中只指定 state 值或只指定 state 和 city 的值，MySQL 也可以利用索引。因此，此索引可用来搜索下列的列组合：
MySQL 不能使用不涉及左前缀的搜索。例如，如果按 city 或 zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个 zip 代码(索引中的列1和列3)，则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。
5、不要过度索引
不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。
如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。
6、考虑在列上进行的比较类型
索引可用于“<”、“<=”、“=”、“>=”、“>”和 BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时(如 STRCMP( ))，对其进行索引没有价值。
总结
本节介绍了索引在优化查询中的作用，包括了索引优化查询的原理，索引在各种情况的检索中的益处，也包括索引的的弊端：增加了存储的空间，使装载数据变慢。
索引是优化查询的最常用也是最有效的的方法，一个数据表，尤其是容量很大的表，建立合适的索引，会使查询的速度提高很大。
----------------------------------------
MySQL的查询性能优化
你可以手工使用EXPLAIN语句检查SQL查询的效率。另外，还讲述了一些优化SQL语句的原则，主要是检索记录和装载数据时如何优化SQL语句的原则。
使用EXPLAIN语句检查SQL语句
当你在一条SELECT语句前放上关键词EXPLAIN，MySQL解释它将如何处理SELECT，提供有关表如何联结和以什么次序联结的信息。
借助于EXPLAIN，你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT。
EXPLAIN tbl_name
or EXPLAIN SELECT select_options
EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。
从EXPLAIN的输出包括下面列：
·table
输出的行所引用的表。
· type
联结类型。各种类型的信息在下面给出。
不同的联结类型列在下面，以最好到最差类型的次序：
system const eq_ref ref range index ALL possible_keys
· key
key列显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。
· key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL，长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。
· ref
ref列显示哪个列或常数与key一起用于从表中选择行。
· rows
rows列显示MySQL相信它必须检验以执行查询的行数。
·Extra
如果Extra列包括文字Only index，这意味着信息只用索引树中的信息检索出的。通常，这比扫描整个表要快。如果Extra列包括文字where used，它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。
通过相乘EXPLAIN输出的rows行的所有值，你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你MySQL必须检验多少行以执行查询。
例如，下面一个全连接：
mysql> EXPLAIN SELECT student.name From student,pet
-> WHERE student.name=pet.owner;
其结论为：
+---------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+------+---------+------+------+------------+
| student | ALL | NULL | NULL | NULL | NULL | 13 | |
| pet | ALL | NULL | NULL | NULL | NULL | 9 | where used |
+---------+------+---------------+------+---------+------+------+------------+
SELECT 查询的速度
总的来说，当你想要使一个较慢的SELECT ... WHERE更快，检查的第一件事情是你是否能增加一个索引。在不同表之间的所有引用通常应该用索引完成。你可以使用EXPLAIN来确定哪个索引用于一条SELECT语句。
一些一般的建议：
·为了帮助MySQL更好地优化查询，在它已经装载了相关数据后，在一个表上运行myisamchk --analyze。这为每一个更新一个值，指出有相同值地平均行数(当然，对唯一索引，这总是1。)
· 为了根据一个索引排序一个索引和数据，使用myisamchk --sort-index --sort-records=1(如果你想要在索引1上排序)。如果你有一个唯一索引，你想要根据该索引地次序读取所有的记录，这是使它更快的一个好方法。然而注意，这个排序没有被最佳地编写，并且对一个大表将花很长时间！
MySQL怎样优化WHERE子句
where优化被放在SELECT中，因为他们最主要在那里使用里，但是同样的优化被用于DELETE和UPDATE语句。也要注意，本节是不完全的。MySQL确实作了许多优化而我们没有时间全部记录他们。由MySQL实施的一些优化列在下面：
1、删除不必要的括号：
((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)
2、常数调入：
(a-> b>5 AND b=c AND a=5
3、删除常数条件(因常数调入所需)：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
4、索引使用的常数表达式仅计算一次。
5、在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对任何NOT NULL表达式也这样做。
6、无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。
7、如果你不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。
8、为每个子联结(sub join)，构造一个更简单的WHERE以得到一个更快的WHERE计算并且也尽快跳过记录。
9、所有常数的表在查询中的任何其他表前被首先读出。一个常数的表是：
·一个空表或一个有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;
10、对联结表的最好联结组合是通过尝试所有可能性来找到:(。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当廉洁时，该表首先被选中。
11、如果有一个ORDER BY子句和一个不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含不是来自联结队列中的第一个表的其他表的列，创建一个临时表。
12、如果你使用SQL_SMALL_RESULT，MySQL将使用一个在内存中的表。
13、因为DISTINCT被变换到在所有的列上的一个GROUP BY，DISTINCT与ORDER BY结合也将在许多情况下需要一张临时表。
14、每个表的索引被查询并且使用跨越少于30% 的行的索引。如果这样的索引没能找到，使用一个快速的表扫描。
15、在一些情况下，MySQL能从索引中读出行，甚至不咨询数据文件。如果索引使用的所有列是数字的，那么只有索引树被用来解答查询。
16、在每个记录被输出前，那些不匹配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,...
MySQL怎样优化LEFT JOIN
在MySQL中，A LEFT JOIN B实现如下：
1、表B被设置为依赖于表A。
2、表A被设置为依赖于所有用在LEFT JOIN条件的表(除B外)。
3、所有LEFT JOIN条件被移到WHERE子句中。
4、进行所有标准的联结优化，除了一个表总是在所有它依赖的表之后被读取。如果有一个循环依赖，MySQL将发出一个错误。
5、进行所有标准的WHERE优化。
6、如果在A中有一行匹配WHERE子句，但是在B中没有任何行匹配LEFT JOIN条件，那么在B中生成所有列设置为NULL的一行。
7、如果你使用LEFT JOIN来找出在某些表中不存在的行并且在WHERE部分你有下列测试：column_name IS NULL，这里column_name 被声明为NOT NULL的列，那么MySQL在它已经找到了匹配LEFT JOIN条件的一行后，将停止在更多的行后寻找(对一特定的键组合)。
MySQL怎样优化LIMIT
在一些情况中，当你使用LIMIT #而不使用HAVING时，MySQL将以不同方式处理查询。
1、如果你用LIMIT只选择一些行，当MySQL一般比较喜欢做完整的表扫描时，它将在一些情况下使用索引。
2、如果你使用LIMIT #与ORDER BY，MySQL一旦找到了第一个 # 行，将结束排序而不是排序整个表。
3、当结合LIMIT #和DISTINCT时，MySQL一旦找到#个唯一的行，它将停止。
4、在一些情况下，一个GROUP BY能通过顺序读取键(或在键上做排序)来解决，并然后计算摘要直到键值改变。在这种情况下，LIMIT #将不计算任何不必要的GROUP。
5、只要MySQL已经发送了第一个#行到客户，它将放弃查询。
6、LIMIT 0将总是快速返回一个空集合。这对检查查询并且得到结果列的列类型是有用的。
7、临时表的大小使用LIMIT #计算需要多少空间来解决查询。
记录转载和修改的速度
很多时候关心的是优化 SELECT 查询，因为它们是最常用的查询，而且确定怎样优化它们并不总是直截了当。相对来说，将数据装入数据库是直截了当的。然而，也存在可用来改善数据装载操作效率的策略，其基本原理如下：
·成批装载较单行装载更快，因为在装载每个记录后，不需要刷新索引高速缓存；可在成批记录装入后才刷新
·在表无索引时装载比索引后装载更快。如果有索引，不仅必须增加记录到数据文件，而且还要修改每个索引以反映增加了的新记录。
·较短的 SQL 语句比较长的 SQL 语句要快，因为它们涉及服务器方的分析较少，而且还因为将它们通过网络从客户机发送到服务器更快。
这些因素中有一些似乎微不足道(特别是最后一个因素)，但如果要装载大量的数据，即使是很小的因素也会产生很大的不同结果。
INSERT查询的速度
插入一个记录的时间由下列组成：
·连接：(3)
·发送查询给服务器：(2)
·分析查询：(2)
·插入记录：(1 x 记录大小)
·插入索引：(1 x 索引)
·关闭：(1)
这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。
表的大小以N log N (B 树)的速度减慢索引的插入。
加快插入的一些方法：
·如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。
·如果你从不同客户插入很多行，你能通过使用INSERT DELAYED语句得到更高的速度。
·注意，用MyISAM，如果在表中没有删除的行，能在SELECT:s正在运行的同时插入行。
·当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。
·当表有很多索引时，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列过程：
1、有选择地用CREATE TABLE创建表。例如使用mysql或Perl-DBI。
2、执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。
3、使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中删除所有索引的使用。
4、用LOAD DATA INFILE把数据插入到表中，这将不更新任何索引，因此很快。
5、如果你有myisampack并且想要压缩表，在它上面运行myisampack。
6、用myisamchk -r -q /path/to/db/tbl_name再创建索引。这将在将它写入磁盘前在内存中创建索引树，并且它更快，因为避免大量磁盘寻道。结果索引树也被完美地平衡。
7、执行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得到一些更快的速度，扩大关键字缓冲区。
UPDATE查询的速度
更改查询被优化为有一个写开销的一个SELECT查询。写速度依赖于被更新数据大小和被更新索引的数量。
使更改更快的另一个方法是推迟更改并且然后一行一行地做很多更改。如果你锁定表，做一行一行地很多更改比一次做一个快。
注意，动态记录格式的更改一个较长总长的记录，可能切开记录。因此如果你经常这样做，时不时地OPTIMIZE TABLE是非常重要的。
DELETE查询的速度
删除一个记录的时间精确地与索引数量成正比。为了更快速地删除记录，你可以增加索引缓存的大小。从一个表删除所有行比删除行的一大部分也要得多。
索引对有效装载数据的影响
如果表是索引的，则可利用批量插入(LOAD DATA 或多行的 INSERT 语句)来减少索引的开销。这样会最小化索引更新的影响，因为索引只需要在所有行处理过时才进行刷新，而不是在每行处理后就刷新。
·如果需要将大量数据装入一个新表，应该创建该表且在未索引时装载，装载数据后才创建索引，这样做较快。一次创建索引(而不是每行修改一次索引)较快。
·如果在装载之前删除或禁用索引，装入数据后再重新创建或启用索引可能使装载更快。
·如果想对数据装载使用删除或禁用策略，一定要做一些实验，看这样做是否值得(如果将少量数据装入一个大表中，重建和索引所花费的时间可能比装载数据的时间还要长)。
可用 DROP INDEX 和 CREATE INDEX 来删除和重建索引。
另一种可供选择的方法是利用 myisamchk 或 isamchk 禁用和启用索引。这需要在 MySQL 服务器主机上有一个帐户，并对表文件有写入权。为了禁用表索引，可进入相应的数据库目录，执行下列命令之一：
shell>myisamchk --keys-used=0 tbl_name
shell>isamchk --keys-used=0 tbl_name
对具有 .MYI 扩展名的索引文件的 MyISAM 表使用 myisamchk，对具有 .ISM 扩展名的索引文件的 ISAM 表使用 isamchk。在向表中装入数据后，按如下激活索引：
shell>myisamchk --recover --quick --keys-used=0 tbl_name
shell>isamchk --recover --quick --keys-used=0 tbl_name
n 为表具有的索引数目。可用 --description 选项调用相应的实用程序得出这个值：
shell>myisamchk --discription tbl_name
$isamchk --discription tbl_name
如果决定使用索引禁用和激活，应该使用第13章中介绍的表修复锁定协议以阻止服务器同时更改锁(虽然此时不对表进行修复，但要对它像表修复过程一样进行修改，因此需要使用相同的锁定协议)。
上述数据装载原理也适用于与需要执行不同操作的客户机有关的固定查询。例如，一般希望避免在频繁更新的表上长时间运行 SELECT 查询。长时间运行 SELECT 查询会产生大量争用，并降低写入程序的性能。一种可能的解决方法为，如果执行写入的主要是 INSERT 操作，那么先将记录存入一个临时表，然后定期地将这些记录加入主表中。如果需要立即访问新记录，这不是一个可行的方法。但只要能在一个较短的时间内不访问它们，就可以使用这个方法。使用临时表有两个方面的好处。首先，它减少了与主表上 SELECT 查询语句的争用，因此，执行更快。其次，从临时表将记录装入主表的总时间较分别装载记录的总时间少；相应的索引高速缓存只需在每个批量装载结束时进行刷新，而不是在每行装载后刷新。
这个策略的一个应用是进入 Web 服务器的Web 页访问 MySQL 数据库。在此情形下，可能没有保证记录立即进入主表的较高权限。
如果数据并不完全是那种在系统非正常关闭事件中插入的单个记录，那么减少索引刷新的另一策略是使用 MyISAM 表的 DELAYED_KEY_WRITE 表创建选项(如果将 MySQL 用于某些数据录入工作时可能会出现这种情况)。此选项使索引高速缓存只偶尔刷新，而不是在每次插入后都要刷新。
如果希望在服务器范围内利用延迟索引刷新，只要利用 --delayed-key-write 选项启动 mysqld 即可。在此情形下，索引块写操作延迟到必须刷新块以便为其他索引值腾出空间为止，或延迟到执行了一个 flush-tables 命令后，或延迟到该索引表关闭。
应用的设计要点
==============
如果MySQL应用占用的CPU超过10%就应该考虑优化了。
1. 如果这个服务可以被其他非数据库应用代替(比如很多基于数据库的计数器完全可以用WEB日志统计代替)最好将其禁用。
非用数据库不可吗？虽然数据库的确可以简化很多应用的结构设计，但本身也是一个系统资源消耗比较大的应用。在某些情况下文本，DBM比数据库是更好的选择，比如：很多应用如果没有很高的实时统计需求的话，完全可以先记录到文件日志中，定期的导入到数据库中做后续统计分析。如果还是需要记录简单的二维键-值对应结构的话可以使用类似于DBM的HEAP类型表。因为HEAP表全部在内存中存取，效率非常高，但服务器突然断电时有可能出现数据丢失，所以非常适 合存储在线用户信息，日志等临时数据。即使需要使用数据库的，应用如果没有太复杂的数据完整性需求的化，完全可以不使用那些支持外键的商业数据库，比如 MySQL。只有非常需要完整的商业逻辑和事务完整性的时候才需要Oracle这样的大型数据库。对于高负载应用来说完全可以把日志文件，DBM，MySQL等轻量级方式做前端数据采集格式，然后用Oracle MSSQL DB2 Sybase等做数据库仓库以完成复杂的数据库挖掘分析工作。
2. 数据库服务的主要瓶颈：单个服务的连接数
对于一个应用来说，如果数据库表结构的设计能够按照数据库原理的范式来设计的话，并且已经使用了最新版本的MySQL，并且按照比较优化的方式运行了，那 么最后的主要瓶颈一般在于单个服务的连接数，即使一个数据库可以支持并发500个连接，最好也不要把应用用到这个地步，因为并发连接数过多数据库服务本身 用于调度的线程的开销也会非常大了。所以如果应用允许的话：让一台机器多跑几个MySQL服务分担。将服务均衡的规划到多个MySQL服务端口上：比如 app_1 ==> 3301 app_2 ==> 3302...app_9 ==> 3309。一个1G内存的机器跑上10个MySQL是很正常的。让10个MySQLD承担1000个并发连接效率要比让2个MySQLD承担1000个效 率高的多。当然，这样也会带来一些应用编程上的复杂度；
3. 使用单独的数据库服务器(不要让数据库和前台WEB服务抢内存)，MySQL拥有更多的内存就可能能有效的进行结果集的缓存；在前面的启动脚本中有一个-O key_buffer=32M参数就是用于将缺省的8M索引缓存增加到32M(当然对于)
4. 应用尽量使用PCONNECT和polling机制，用于节省MySQL服务建立连接的开销，但也会造成MySQL并发链接数过多(每个HTTPD都会对应一个MySQL线程)；
5. 表的横向拆分：让最常被访问的10%的数据放在一个小表里，90%的历史数据放在一个归档表里(所谓：快慢表)，数据中间通过定期“搬家”和定期删除无效数 据来节省，毕竟大部分应用(比如论坛)访问2个月前数据的几率会非常少，而且价值也不是很高。这样对于应用来说总是在一个比较小的结果级中进行数据选择，比较有利于数据的缓存，不要指望MySQL中对单表记录条数在10万级以上还有比较高的效率。而且有时候数据没有必要做那么精确，比如一个快表中查到了某个人发表的文章有60条结果，快表和慢表的比例是1:20，那么就可以简单的估计这个人一共发表了1200篇。Google的搜索结果数也是一样：对于很多上十万的结果数，后面很多的数字都是通过一定的算法估计出来的。
6. 数据库字段设计：表的纵向拆分(过渡范化)：将所有的定长字段(char, int等)放在一个表里，所有的变长字段(varchar,text,blob等)放在另外一个表里，2个表之间通过主键关联，这样，定长字段表可以得到 很大的优化(这样可以使用HEAP表类型，数据完全在内存中存取)，这里也说明另外一个原则，对于我们来说，尽量使用定长字段可以通过空间的损失换取访问效率的提高。在MySQL4中也出现了支持外键和事务的InnoDB类型表，标准的MyISAM格式表和基于HASH结构的HEAP内存表，MySQL之所以支持多种表类型，实际上是针对不同应用提供了不同的优化方式；
7. 仔细的检查应用的索引设计：可以在服务启动参数中加入 --log-slow-queries[=file]用于跟踪分析应用瓶颈，对于跟踪服务瓶颈最简单的方法就是用MySQL的status查看MySQL服务的运行统计和show processlist来查看当前服务中正在运行的SQL，如果某个SQL经常出现在PROCESS LIST中，一。有可能被查询的此时非常多，二，里面有影响查询的字段没有索引，三，返回的结果数过多数据库正在排序(SORTING)；所以做一个脚本：比如每2秒运行以下show processlist;把结果输出到文件中，看到底是什么查询在吃CPU。
8. 全文检索：如果相应字段没有做全文索引的话，全文检索将是一个非常消耗CPU的功能，因为全文检索是用不上一般数据库的索引的，所以要进行相应字段记录遍历。关于全文索引可以参考一下基于Java的全文索引引擎lucene的介绍。
9. 前台应用的记录缓存：比如一个经常使用数据库认证，如果需要有更新用户最后登陆时间的操作，最好记录更新后就把用户放到一个缓存中(设置2个小时后过期)，这样如果用户在2个小时内再次使用到登陆，就直接从缓存里认证，避免了过于频繁的数据库操作。
10. 查询优先的表应该尽可能为where和order by字句中的字段加上索引，数据库更新插入优先的应用索引越少越好。
总之：对于任何数据库单表记录超过100万条优化都是比较困难的，关键是要把应用能够转化成数据库比较擅长的数据上限内。也就是把复杂需求简化成比较成熟的解决方案内。
主要内容有，选择表的类型，打开尽量少的表，锁定表与查询速度的关系以及如何优化表以达到提高查询速度的目的。由于数据的录入和清除操作，很容易在表的数据文件重产生碎片，对于拥有大型表的数据库，应该经常使用myisamchk维护程序来清除碎片，优化表时要选择一个好的时机，尽量在没有用户访问时优化表。
选择合适的表的类型，防止数据文件中产生碎块，同样可以大大优化检索的速度，是数据库的性能最大化。
选择一种表类型
用MySQL，当前(版本 3 .23)你能从一个速度观点在4可用表的格式之间选择。
·静态MyISAM
这种格式是最简单且最安全的格式，它也是在磁盘格式最快的。速度来自于数据能在磁盘上被找到的难易方式。当所定有一个索引和静态格式的东西时，它很简单，只是行长度乘以行数量。而且在扫描一张表时，用每次磁盘读取来读入常数个记录是很容易的。安全性来自于如果当写入一个静态MyISAM文件时，你的计算机崩溃，myisamchk能很容易指出每行在哪儿开始和结束，因此它通常能回收所有记录，除了部分被写入的那个。注意，在MySQL中，所有索引总能被重建。
·动态MyISAM
这种格式有点复杂，因为每一行必须有一个头说明它有多长。当一个记录在更改时变长时，它也可以在多于一个位置上结束。你能使用 OPTIMIZE table或myisamchk整理一张表。如果你在同一个表中有象某些VARCHAR或BLOB列那样存取/改变的静态数据，将动态列移入另外一个表以避免碎片可能是一个好主意。
·压缩MyISAM
这是一个只读类型，用可选的myisampack工具生成。
·内存(HEAP 堆)
这种表格式对小型/中型查找表十分有用。对拷贝/创建一个常用的查找表(用联结)到一个(也许临时)HEAP表有可能加快多个表联结。假定我们想要做下列联结，用同样数据可能要几倍时间。
SELECT tab1.a, tab3.a FROM tab1, tab2, tab3
WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;
为了加速它，我们可用tab2和tab3的联结创建一张临时表，因为用相同列( tab1.a )查找。这里是创建该表和结果选择的命令。
CREATE TEMPORARY TABLE test TYPE=HEAP
SELECT tab2.a as a2, tab3.a as a3 FROM tab2, tab3 WHERE tab2.a = tab3.a and c = 0;
SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;
SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;
静态(定长)表的特点
这是缺省格式。它用在表不包含VARCHAR、BLOB或TEXT列时候。所有的CHAR、NUMERIC和DECIMAL列充填到列宽度。非常快、容易缓冲、容易在崩溃后重建，因为记录位于固定的位置。
不必被重新组织(用myisamchk)，除非一个巨量的记录被删除并且你想要归还空闲磁盘空间给操作系统，通常比动态表需要更多的磁盘空间。
动态表的特点
如果表包含任何VARCHAR、BLOB或TEXT列，使用该格式。所有字符串列是动态的(除了那些长度不到4的列)。每个记录前置一个位图，对字符串列指出哪个列是空的('')，或对数字列哪个是零(这不同于包含NULL值的列)。如果字符串列在删除尾部空白后有零长度，或数字列有零值，它在位图中标记并且不保存到磁盘上。非空字符串存储为一个长度字节加字符串内容。
通常比定长表占更多的磁盘空间，每个记录仅使用所需的空间。如果一个记录变得更大，它按需要被切开多段，这导致记录碎片。
如果你与超过行长度的信息更新行，行将被分段。在这种情况中，你可能必须时时运行myisamchk -r以使性能更好。使用myisamchk -ei tbl_name做一些统计。
在崩溃后不容易重建，因为一个记录可以是分很多段并且一个连接(碎片)可以丢失。对动态尺寸记录的期望行长度是：
3
+ (number of columns + 7) / 8
+ (number of char columns)
+ packed size of numeric columns
+ length of strings
+ (number of NULL columns + 7) / 8
对每个连接有6个字节的惩罚。无论何时更改引起记录的增大，一个动态记录被链接。每个新链接将至少是20个字节，因此下一增大将可能在同一链连中。如果不是，将有另外一个链接。你可以用myisamchk -ed检查有多少链接。所有的链接可以用 myisamchk -r 删除。
压缩表的特点
一张用myisampack实用程序制作的只读表。所有具有MySQL扩展电子邮件支持的客户可以为其内部使用保留一个myisampack拷贝。
解压缩代码存在于所有MySQL分发，以便甚至没有myisampack的客户能读取用myisampack压缩的表，占据很小的磁盘空间，使磁盘使用量减到最小。
每个记录被单独压缩(很小的存取开销)。对一个记录的头是定长的(1-3 字节)，取决于表中最大的记录。每列以不同方式被压缩。一些压缩类型是：
通常对每列有一张不同的哈夫曼表。
后缀空白压缩。
前缀空白压缩。
用值0的数字使用1位存储。
如果整数列的值有一个小范围，列使用最小的可能类型来存储。例如，如果所有的值在0到255的范围，一个BIGINT列(8个字节)可以作为一个TINYINT列(1字节)存储。
如果列仅有可能值的一个小集合，列类型被变换到ENUM。
列可以使用上面的压缩方法的组合。
能处理定长或动态长度的记录，然而不能处理BLOB或TEXT列。
能用myisamchk解压缩。
MySQL能支持不同的索引类型，但是一般的类型是ISAM。这是一个B树索引并且你能粗略地为索引文件计算大小为(key_length+4)*0.67，在所有的键上的总和。(这是对最坏情况，当所有键以排序顺序被插入时。)
字符串索引是空白压缩的。如果第一个索引部分是一个字符串，它也将压缩前缀。如果字符串列有很多尾部空白或是一个总不能用到全长的VARCHAR列，空白压缩使索引文件更小。如果很多字符串有相同的前缀，前缀压缩是有帮助的。
MySQL内存表的特点
堆表仅存在于内存中，因此如果mysqld被关掉或崩溃，它们将丢失，但是因为它们是很快，不管怎样它们是有用的。
MySQL内部的HEAP表使用没有溢出区的100%动态哈希并且没有与删除有关的问题。
你只能通过使用在堆表中的一个索引的用等式存取东西(通常用=操作符)。
堆表的缺点是：
·你要为你想要同时使用的所有堆表需要足够的额外内存。
·你不能在索引的一个部分上搜索。
·你不能顺序搜索下一个条目(即使用这个索引做一个ORDER BY)。
·MySQL也不能算出在2个值之间大概有多少行。这被优化器使用来决定使用哪个索引，但是在另一方面甚至不需要磁盘寻道。
数据库表的数量的问题
在同一个数据库中创建大量数据库表的缺点是,如果你在一个目录中有许多文件，打开、关闭和创建操作将会很慢。如果你执行在许多不同表上的 SELECT语句，当表缓存满时，将有一点开销，因为对每个必须打开的表，另外一个必须被关闭。你可以通过使表缓冲更大些来减少这个开销。
为什么有这么多打开的表？
当你运行mysqladmin status时，你将看见象这样的一些东西：
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
如果你仅有6个表，这可能有点令人困惑。
MySQL是多线程的，因此它可以同时在同一个表上有许多询问。为了是2个线程在同一个文件上有不同状态的问题减到最小，表由每个并发进程独立地打开。这为数据文件消耗一些内存和一个额外的文件描述符。索引文件描述符在所有线程之间共享。
数据库表级锁定的问题
前面的内容主要将精力集中在使个别的查询更快上。MySQL 还允许影响语句的调度特性，这样会使来自几个客户机的查询更好地协作，从而单个客户机不会被锁定太长的时间。更改调度特性还能保证特定的查询处理得更快。我们先来看一下 MySQL 的缺省调度策略，然后来看看为改变这个策略可使用什么样的选项。出于讨论的目的，假设执行检索(SELECT)的客户机程序为读取程序。执行修改表操作(DELETE，INSERT，REPLACE 或 UPDATE)的另一个客户机程序为写入程序。
MySQL 的基本调度策略可总结如下：
·写入请求应按其到达的次序进行处理。
·写入具有比读取更高的优先权。
1、对此一个主要的问题如下：
·一个客户发出一个花很长时间运行的SELECT。
·然后其他客户在一个使用的表上发出一个UPDATE；这个客户将等待直到SELECT完成。
·另一个客户在同一个表上发出另一个SELECT语句；因为UPDATE比SELECT有更高的优先级，该SELECT将等待UPDATE的完成。它也将等待第一个SELECT完成！
对这个问题的一些可能的解决方案是：
·试着使SELECT语句运行得更快；你可能必须创建一些摘要(summary)表做到这点。
·用--low-priority-updates启动mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下，在先前情形的最后的SELECT语句将在INSERT语句前执行。
·你可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级。
·为max_write_lock_count指定一个低值来启动mysqld使得在一定数量的WRITE锁定后给出READ锁定。
·通过使用SQL命令：SET SQL_LOW_PRIORITY_UPDATES=1，你可从一个特定线程指定所有的更改应该由用低优先级完成。见SET OPTION句法。
·你可以用HIGH_PRIORITY属性指明一个特定SELECT是很重要的。见SELECT句法。
·如果你有关于INSERT结合SELECT的问题，切换到使用新的MyISAM表，因为它们支持并发的SELECT和INSERT。
·如果你主要混合INSERT和SELECT语句，DELAYED属性的INSERT将可能解决你的问题。INSERT句法。
·如果你有关于SELECT和DELETE的问题，LIMIT选项的DELETE可以帮助你。见DELETE句法。
2、INSERT DELAYED 在客户机方的作用
如果其他客户机可能执行冗长的 SELECT 语句，而且您不希望等待插入完成，此时 INSERT DELAYED 很有用。发布 INSERT DELAYED 的客户机可以更快地继续执行，因为服务器只是简单地将要插入的行插入。
不过应该对正常的 INSERT 和 INSERT DELAYED 性能之间的差异有所认识。如果 INSERT DELAYED 存在语法错误，则向客户机发出一个错误，如果正常，便不发出信息。例如，在此语句返回时，不能相信所取得的 AUTO_INCREMENT 值。也得不到惟一索引上的重复数目的计数。之所以这样是因为此插入操作在实际的插入完成前返回了一个状态。其他还表示，如果 INSERT DELAYED 语句的行在等待插入中被排队，并且服务器崩溃或被终止(用 kill -9)，那么这些行将丢失。正常的 TERM 终止不会这样，服务器会在退出前将这些行插入。
在表锁的帮助下实现调度策略。客户机程序无论何时要访问表，都必须首先获得该表的锁。可以直接用 LOCK TABLES 来完成这项工作，但一般服务器的锁管理器会在需要时自动获得锁。在客户机结束对表的处理时，可释放表上的锁。直接获得的锁可用 UNLOCK TABLES 释放，但服务器也会自动释放它所获得的锁。
执行写操作的客户机必须对表具有独占访问的锁。在写操作进行中，由于正在对表进行数据记录的删除、增加或更改，所以该表处于不一致状态，而且该表上的索引也可能需要作相应的更新。如果表处于不断变化中，此时允许其他客户机访问该表会出问题。让两个客户机同时写同一个表显然不好，因为这样会很快使该表不可用。允许客户机读不断变化的表也不是件好事，因为可能在读该表的那一刻正好正在对它进行更改，其结果是不正确的。
执行读取操作的客户机必须有一把防止其他客户机写该表的锁，以保证读表的过程中表不出现变化。不过，该锁无需对读取操作提供独占访问。此锁还允许其他客户机同时对表进行读取。读取不会更改表，所有没必要阻止其它客户机对该表进行读取。
MySQL 允许借助几个查询限修饰符对其调度策略施加影响。其中之一是 DELETE、INSERT、LOAD DATA、REPLACE 和 UPDATE 语句的 LOW_PRIORITY 关键字。另一个是 SELECT 语句的 HIGH_PRIORITY 关键字。第三个是 INSERT 和 REPLACE 语句的 DELAYED 关键字。
LOW_PRIORITY 关键字按如下影响调度。一般情况下，如果某个表的写入操作在表正被读取时到达，写入程序被阻塞，直到读取程序完成，因为一旦某个查询开始，就不能中断。如果另一读取请求在写入程序等待时到达，此读取程序也被阻塞，因为缺省的调度策略为写入程序具有比读取程序高的优先级。在第一个读取程序结束时，写入程序继续，在此写入程序结束时，第二个读取程序开始。
如果写入请求为 LOW_PRIORITY 的请求，则不将该写入操作视为具有比读取操作优先级高的操作。在此情形下，如果第二个读取请求在写入程序等待时到达，则让第二个读取操作排在等待的写入操作之前。仅当没有其他读取请求时，才允许写入程序执行。这种调度的更改从理论上说，其含义为 LOW_PRIORITY 写入可能会永远被阻塞。当正在处理前面的读取请求时，只要另一个读取请求到达，这个新的请求允许排在 LOW_PRIORITY 写入之前。
SELECT 查询的 HIGH_PRIORITY 关键字作用类似。它使 SELECT 插在正在等待的写入操作之前，即使该写入操作具有正常的优先级。
INSERT 的 DELAYED 修饰符作用如下，在表的一个 INSERT DELAYED 请求到达时，服务器将相应的行放入一个队列，并立即返回一个状态到客户机程序，以便该客户机程序可以继续执行，即使这些行尚未插入表中。如果读取程序正在对表进行读取，那么队列中的行挂起。在没有读取时，服务器开始开始插入延迟行队列中的行。服务器不时地停下来看看是否有新的读取请求到达，并进行等待。如果是这样，延迟行队列将挂起，并允许读取程序继续。在没有其他的读取操作时，服务器再次开始插入延迟行。这个过程一直进行到延迟行队列空为止。
此调度修饰符并非出现在所有 MySQL 版本中。下面的表列出了这些修饰符和支持这些修饰符的 MySQL 版本。可利用此表来判断所使用的 MySQL 版本具有什么样的功能：
语句类型 开始出现的版本
DELETE LOW_PRIOrITY 3.22.5
INSERT LOW+PRIOrITY 3.22.5
INSERT DELAYED 3.22.15
LOAD DATA LOW_PRIORITY 3.23.0
LOCK TABLES ... LOW_PRIORITY 3.22.8
REPLACE LOW_PRIORITY 3.22.5
REPLACE DELAYED 3.22.15
SELECT ... HIGH_PRIORITY 3.22.9
UPDATE LOW_PRIORITY 3.22.5
SET SQL_LOW_PRIORITY_UPDATES 3.22.5
对表进行优化
对表的长期使用，由于记录的删除与插入，会在表的数据文件产生碎片，下面的几种情况尤其会有产生碎片的效果：
·使用VARCHAR类型
频繁使用可变长列的结果就是使表非常容易产生碎片，如果空间允许，尽量采用定长类型CHAR。
·使用类型BLOB和TEXT
特别是进行大量的 DELETE 或 UPDATE 操作时更是如此。删除 BLOB 会在表中留下一个大空白，在以后将需用一个记录或可能是不同大小的多个记录来填充。
·删除了一个表的大部分，这同样会在表中留下大量空白。
为了消除表的碎片对性能的影响，需要对表进行优化。在第七章我们介绍了优化表的方法：
1、使用SQL语句OPTIMIZE
OPTIMIZE TABLE tbl_name
2、使用修复程序myisamchk或isamchk
----------------------------------
数据类型的使用
有助于效率的类型选择
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]])
例如：
mysql>SELECT * FROM student PROCEDURE ANALYSE();
mysql>SELECT * FROM student PROCEDURE ANALYSE(16,256);
相应输出中有一列是关于表中每列的最佳列类型的建议。第二个例子要求 PROCEDURE ANALYSE( ) 不要建议含有多于 16 个值或取多于 256 字节的 ENUM 类型(可根据需要更改这些值)。如果没有这样的限制，输出可能会很长；ENUM 的定义也会很难阅读。
根据 PROCEDURE ANALYSE( ) 的输出，会发现可以对表进行更改以利用更有效的类型。如果希望更改值类型，使用 ALTER TABLE 语句即可。
---------------------------
索引的使用
索引在优化查询中的作用，包括了索引优化查询的原理，索引是优化查询的最常用也是最有效的的方法，一个数据表，尤其是容量很大的表，建立合适的索引，会使查询的速度提高很大。
MySQL索引的使用
我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。在 MySQL 的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。
本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论 MySQL 的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。(虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。)
索引对单个表查询的影响
索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000 行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。
例如对下面这样的一个student表：
mysql>SELECT * FROM student
+------+---------+---------+---------+---------+
| id | name | english | chinese | history |
+------+---------+---------+---------+---------+
| 12 | Tom | 66 | 93 | 67 |
| 56 | Paul | 78 | 52 | 75 |
| 10 | Marry | 54 | 89 | 74 |
| 4 | Tina | 99 | 83 | 48 |
| 39 | William | 43 | 96 | 52 |
| 74 | Stone | 42 | 40 | 61 |
| 86 | Smith | 49 | 85 | 78 |
| 37 | Black | 49 | 63 | 47 |
| 89 | White | 94 | 31 | 52 |
+------+---------+---------+---------+---------+
这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生：
mysql>SELECT name,english FROM student WHERE english<60;
+---------+---------+
| name | english |
+---------+---------+
| Marry | 54 |
| William | 43 |
| Stone | 42 |
| Smith | 49 |
| Black | 49 |
+---------+---------+
其中，WHERE从句不得不匹配每个记录，以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。
如果，我们为english列创建一个索引：
mysql>ALTER TABLE student ADD INDEX (english) ;
+-------------------+
| index for english |
+-------------------+
| 42 |
| 43 |
| 49 |
| 49 |
| 54 |
| 66 |
| 78 |
| 94 |
| 99 |
+-------------------+
如上表，此索引存储在索引文件中，包含表中每行的english列值，但此索引是在 english的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，因此在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描(如二分查找法)。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。
因此在执行下述查询
mysql>SELECT name,english FROM user WHERE english<60;
其结果为：
+---------+---------+
| name | english |
+---------+---------+
| Stone | 42 |
| William | 43 |
| Smith | 49 |
| Black | 49 |
| Marry | 54 |
+---------+---------+
你应该可以发现，这个结果与未索引english列之前的不同，它是排序的，原因正式如上所述。
索引对多个表查询的影响
前面的讨论描述了单表查询中索引的好处，其中使用索引消除了全表扫描，极大地加快了搜索的速度。在执行涉及多个表的连接查询时，索引甚至会更有价值。在单个表的查询中，每列需要查看的值的数目就是表中行的数目。而在多个表的查询中，可能的组合数目极大，因为这个数目为各表中行数之积。
假如有三个未索引的表 t1、t2、t3，分别只包含列 c1、c2、c3，每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示：
此查询的结果应该为 1000 行，每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000(十亿)，比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时，将会怎样？很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：
1) 如下从表 t1 中选择第一行，查看此行所包含的值。
2) 使用表 t2 上的索引，直接跳到 t2 中与来自 t1 的值匹配的行。类似，利用表 t3 上的索引，直接跳到 t3 中与来自 t1 的值匹配的行。
3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。
在此情形下，我们仍然对表 t1 执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。
如上所述，MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。
多列索引对查询的影响
假定你发出下列SELECT语句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
你可以这样创建一个多列索引：
mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);
而你应该这样创建分开的单行列索引：
mysql>ALTER TABLE tble_name ADD INDEX(col1);
mysql>ALTER TABLE tble_name ADD INDEX(col1);
如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个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 "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";
在第一条语句中，只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中，只考虑有"Pat" <= key_col < "Pau"的行。
下列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层次使用索引，将不使用来索引优化查询。为了能够使用索引优化查询，必须把一个多列索引的前缀使用在一个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 */
MySQL索引的作用
所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。CREATE INDEX句法。
索引用于：
快速找出匹配一个WHERE子句的行。
在多个表的查询时，执行连接时加快了与其他表中的行匹配的行的搜索。
对特定的索引列找出MAX()或MIN()值。
如果排序或分组在一个可用索引的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。
在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。
索引的弊端
一般情况下，如果 MySQL 能够知道怎样用索引来更快地处理查询，它就会这样做。这表示，在大多数情况下，如果您不对表进行索引，则损害的是您自己的利益。可以看出，作者描绘了索引的诸多好处。但有不利之处吗？是的，有。实际上这些缺点被优点所掩盖了，但应该对它们有所了解。
首先，索引文件要占磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，索引文件加快了检索，但增加了插入和删除，以及更新索引列中的值的时间(即，降低了大多数涉及写入的操作的时间)，因为写操作不仅涉及数据行，而且还常常涉及索引。一个表拥有的索引越多，则写操作的平均性能下降就越大。在8.4.4节记录装载和修改的速度中，我们将更为详细地介绍这些性能问题，并讨论怎样解决。
选择索引的准则
创建索引的语法已经在4.5索引属性中进行了介绍。这里，我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则：
1、搜索的索引列，不一定是所要选择的列
换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列，例如：
SELECT
col_a ←不适合作索引列
FROM
Tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c ←适合作索引列
WHERE
col_d = expr ←适合作索引列
当然，所选择的列和用于 WHERE 子句的列也可能是相同的。关键是，列出现在选择列表中不是该列应该索引的标志。
出现在连接子句中的列或出现在形如 col1 = col2 的表达式中的列是很适合索引的列。查询中的 col_b 和 col_c 就是这样的例子。如果 MySQL 能利用连接列来优化一个查询，表示它通过消除全表扫描相当可观地减少了表行的组合。
2、使用惟一索引
考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处(不管搜索哪个值，都会得出大约一半的行)。
3、使用短索引
如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的，因为这个索引中不会有许多不同的值。)
4、利用最左前缀
在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同，索引一个列的前缀是利用该的前 n 个字符作为索引值。)
假如一个表在分别名为 state、city 和 zip 的三个列上有一个索引。索引中的行是按 state/city/zip 的次序存放的，因此，索引中的行也会自动按 state/city 的顺序和 state 的顺序存放。这表示，即使在查询中只指定 state 值或只指定 state 和 city 的值，MySQL 也可以利用索引。因此，此索引可用来搜索下列的列组合：
MySQL 不能使用不涉及左前缀的搜索。例如，如果按 city 或 zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个 zip 代码(索引中的列1和列3)，则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。
5、不要过度索引
不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。
如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。
6、考虑在列上进行的比较类型
索引可用于“<”、“<=”、“=”、“>=”、“>”和 BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时(如 STRCMP( ))，对其进行索引没有价值。
总结
本节介绍了索引在优化查询中的作用，包括了索引优化查询的原理，索引在各种情况的检索中的益处，也包括索引的的弊端：增加了存储的空间，使装载数据变慢。
索引是优化查询的最常用也是最有效的的方法，一个数据表，尤其是容量很大的表，建立合适的索引，会使查询的速度提高很大。
----------------------------------------
MySQL的查询性能优化
你可以手工使用EXPLAIN语句检查SQL查询的效率。另外，还讲述了一些优化SQL语句的原则，主要是检索记录和装载数据时如何优化SQL语句的原则。
使用EXPLAIN语句检查SQL语句
当你在一条SELECT语句前放上关键词EXPLAIN，MySQL解释它将如何处理SELECT，提供有关表如何联结和以什么次序联结的信息。
借助于EXPLAIN，你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT。
EXPLAIN tbl_name
or EXPLAIN SELECT select_options
EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。
从EXPLAIN的输出包括下面列：
·table
输出的行所引用的表。
· type
联结类型。各种类型的信息在下面给出。
不同的联结类型列在下面，以最好到最差类型的次序：
system const eq_ref ref range index ALL possible_keys
· key
key列显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。
· key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL，长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。
· ref
ref列显示哪个列或常数与key一起用于从表中选择行。
· rows
rows列显示MySQL相信它必须检验以执行查询的行数。
·Extra
如果Extra列包括文字Only index，这意味着信息只用索引树中的信息检索出的。通常，这比扫描整个表要快。如果Extra列包括文字where used，它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。
通过相乘EXPLAIN输出的rows行的所有值，你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你MySQL必须检验多少行以执行查询。
例如，下面一个全连接：
mysql> EXPLAIN SELECT student.name From student,pet
-> WHERE student.name=pet.owner;
其结论为：
+---------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+------+---------+------+------+------------+
| student | ALL | NULL | NULL | NULL | NULL | 13 | |
| pet | ALL | NULL | NULL | NULL | NULL | 9 | where used |
+---------+------+---------------+------+---------+------+------+------------+
SELECT 查询的速度
总的来说，当你想要使一个较慢的SELECT ... WHERE更快，检查的第一件事情是你是否能增加一个索引。在不同表之间的所有引用通常应该用索引完成。你可以使用EXPLAIN来确定哪个索引用于一条SELECT语句。
一些一般的建议：
·为了帮助MySQL更好地优化查询，在它已经装载了相关数据后，在一个表上运行myisamchk --analyze。这为每一个更新一个值，指出有相同值地平均行数(当然，对唯一索引，这总是1。)
· 为了根据一个索引排序一个索引和数据，使用myisamchk --sort-index --sort-records=1(如果你想要在索引1上排序)。如果你有一个唯一索引，你想要根据该索引地次序读取所有的记录，这是使它更快的一个好方法。然而注意，这个排序没有被最佳地编写，并且对一个大表将花很长时间！
MySQL怎样优化WHERE子句
where优化被放在SELECT中，因为他们最主要在那里使用里，但是同样的优化被用于DELETE和UPDATE语句。也要注意，本节是不完全的。MySQL确实作了许多优化而我们没有时间全部记录他们。由MySQL实施的一些优化列在下面：
1、删除不必要的括号：
((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)
2、常数调入：
(a-> b>5 AND b=c AND a=5
3、删除常数条件(因常数调入所需)：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
4、索引使用的常数表达式仅计算一次。
5、在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对任何NOT NULL表达式也这样做。
6、无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。
7、如果你不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。
8、为每个子联结(sub join)，构造一个更简单的WHERE以得到一个更快的WHERE计算并且也尽快跳过记录。
9、所有常数的表在查询中的任何其他表前被首先读出。一个常数的表是：
·一个空表或一个有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;
10、对联结表的最好联结组合是通过尝试所有可能性来找到:(。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当廉洁时，该表首先被选中。
11、如果有一个ORDER BY子句和一个不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含不是来自联结队列中的第一个表的其他表的列，创建一个临时表。
12、如果你使用SQL_SMALL_RESULT，MySQL将使用一个在内存中的表。
13、因为DISTINCT被变换到在所有的列上的一个GROUP BY，DISTINCT与ORDER BY结合也将在许多情况下需要一张临时表。
14、每个表的索引被查询并且使用跨越少于30% 的行的索引。如果这样的索引没能找到，使用一个快速的表扫描。
15、在一些情况下，MySQL能从索引中读出行，甚至不咨询数据文件。如果索引使用的所有列是数字的，那么只有索引树被用来解答查询。
16、在每个记录被输出前，那些不匹配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,...
MySQL怎样优化LEFT JOIN
在MySQL中，A LEFT JOIN B实现如下：
1、表B被设置为依赖于表A。
2、表A被设置为依赖于所有用在LEFT JOIN条件的表(除B外)。
3、所有LEFT JOIN条件被移到WHERE子句中。
4、进行所有标准的联结优化，除了一个表总是在所有它依赖的表之后被读取。如果有一个循环依赖，MySQL将发出一个错误。
5、进行所有标准的WHERE优化。
6、如果在A中有一行匹配WHERE子句，但是在B中没有任何行匹配LEFT JOIN条件，那么在B中生成所有列设置为NULL的一行。
7、如果你使用LEFT JOIN来找出在某些表中不存在的行并且在WHERE部分你有下列测试：column_name IS NULL，这里column_name 被声明为NOT NULL的列，那么MySQL在它已经找到了匹配LEFT JOIN条件的一行后，将停止在更多的行后寻找(对一特定的键组合)。
MySQL怎样优化LIMIT
在一些情况中，当你使用LIMIT #而不使用HAVING时，MySQL将以不同方式处理查询。
1、如果你用LIMIT只选择一些行，当MySQL一般比较喜欢做完整的表扫描时，它将在一些情况下使用索引。
2、如果你使用LIMIT #与ORDER BY，MySQL一旦找到了第一个 # 行，将结束排序而不是排序整个表。
3、当结合LIMIT #和DISTINCT时，MySQL一旦找到#个唯一的行，它将停止。
4、在一些情况下，一个GROUP BY能通过顺序读取键(或在键上做排序)来解决，并然后计算摘要直到键值改变。在这种情况下，LIMIT #将不计算任何不必要的GROUP。
5、只要MySQL已经发送了第一个#行到客户，它将放弃查询。
6、LIMIT 0将总是快速返回一个空集合。这对检查查询并且得到结果列的列类型是有用的。
7、临时表的大小使用LIMIT #计算需要多少空间来解决查询。
记录转载和修改的速度
很多时候关心的是优化 SELECT 查询，因为它们是最常用的查询，而且确定怎样优化它们并不总是直截了当。相对来说，将数据装入数据库是直截了当的。然而，也存在可用来改善数据装载操作效率的策略，其基本原理如下：
·成批装载较单行装载更快，因为在装载每个记录后，不需要刷新索引高速缓存；可在成批记录装入后才刷新
·在表无索引时装载比索引后装载更快。如果有索引，不仅必须增加记录到数据文件，而且还要修改每个索引以反映增加了的新记录。
·较短的 SQL 语句比较长的 SQL 语句要快，因为它们涉及服务器方的分析较少，而且还因为将它们通过网络从客户机发送到服务器更快。
这些因素中有一些似乎微不足道(特别是最后一个因素)，但如果要装载大量的数据，即使是很小的因素也会产生很大的不同结果。
INSERT查询的速度
插入一个记录的时间由下列组成：
·连接：(3)
·发送查询给服务器：(2)
·分析查询：(2)
·插入记录：(1 x 记录大小)
·插入索引：(1 x 索引)
·关闭：(1)
这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。
表的大小以N log N (B 树)的速度减慢索引的插入。
加快插入的一些方法：
·如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。
·如果你从不同客户插入很多行，你能通过使用INSERT DELAYED语句得到更高的速度。
·注意，用MyISAM，如果在表中没有删除的行，能在SELECT:s正在运行的同时插入行。
·当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。
·当表有很多索引时，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列过程：
1、有选择地用CREATE TABLE创建表。例如使用mysql或Perl-DBI。
2、执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。
3、使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中删除所有索引的使用。
4、用LOAD DATA INFILE把数据插入到表中，这将不更新任何索引，因此很快。
5、如果你有myisampack并且想要压缩表，在它上面运行myisampack。
6、用myisamchk -r -q /path/to/db/tbl_name再创建索引。这将在将它写入磁盘前在内存中创建索引树，并且它更快，因为避免大量磁盘寻道。结果索引树也被完美地平衡。
7、执行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得到一些更快的速度，扩大关键字缓冲区。
UPDATE查询的速度
更改查询被优化为有一个写开销的一个SELECT查询。写速度依赖于被更新数据大小和被更新索引的数量。
使更改更快的另一个方法是推迟更改并且然后一行一行地做很多更改。如果你锁定表，做一行一行地很多更改比一次做一个快。
注意，动态记录格式的更改一个较长总长的记录，可能切开记录。因此如果你经常这样做，时不时地OPTIMIZE TABLE是非常重要的。
DELETE查询的速度
删除一个记录的时间精确地与索引数量成正比。为了更快速地删除记录，你可以增加索引缓存的大小。从一个表删除所有行比删除行的一大部分也要得多。
索引对有效装载数据的影响
如果表是索引的，则可利用批量插入(LOAD DATA 或多行的 INSERT 语句)来减少索引的开销。这样会最小化索引更新的影响，因为索引只需要在所有行处理过时才进行刷新，而不是在每行处理后就刷新。
·如果需要将大量数据装入一个新表，应该创建该表且在未索引时装载，装载数据后才创建索引，这样做较快。一次创建索引(而不是每行修改一次索引)较快。
·如果在装载之前删除或禁用索引，装入数据后再重新创建或启用索引可能使装载更快。
·如果想对数据装载使用删除或禁用策略，一定要做一些实验，看这样做是否值得(如果将少量数据装入一个大表中，重建和索引所花费的时间可能比装载数据的时间还要长)。
可用 DROP INDEX 和 CREATE INDEX 来删除和重建索引。
另一种可供选择的方法是利用 myisamchk 或 isamchk 禁用和启用索引。这需要在 MySQL 服务器主机上有一个帐户，并对表文件有写入权。为了禁用表索引，可进入相应的数据库目录，执行下列命令之一：
shell>myisamchk --keys-used=0 tbl_name
shell>isamchk --keys-used=0 tbl_name
对具有 .MYI 扩展名的索引文件的 MyISAM 表使用 myisamchk，对具有 .ISM 扩展名的索引文件的 ISAM 表使用 isamchk。在向表中装入数据后，按如下激活索引：
shell>myisamchk --recover --quick --keys-used=0 tbl_name
shell>isamchk --recover --quick --keys-used=0 tbl_name
n 为表具有的索引数目。可用 --description 选项调用相应的实用程序得出这个值：
shell>myisamchk --discription tbl_name
$isamchk --discription tbl_name
如果决定使用索引禁用和激活，应该使用第13章中介绍的表修复锁定协议以阻止服务器同时更改锁(虽然此时不对表进行修复，但要对它像表修复过程一样进行修改，因此需要使用相同的锁定协议)。
上述数据装载原理也适用于与需要执行不同操作的客户机有关的固定查询。例如，一般希望避免在频繁更新的表上长时间运行 SELECT 查询。长时间运行 SELECT 查询会产生大量争用，并降低写入程序的性能。一种可能的解决方法为，如果执行写入的主要是 INSERT 操作，那么先将记录存入一个临时表，然后定期地将这些记录加入主表中。如果需要立即访问新记录，这不是一个可行的方法。但只要能在一个较短的时间内不访问它们，就可以使用这个方法。使用临时表有两个方面的好处。首先，它减少了与主表上 SELECT 查询语句的争用，因此，执行更快。其次，从临时表将记录装入主表的总时间较分别装载记录的总时间少；相应的索引高速缓存只需在每个批量装载结束时进行刷新，而不是在每行装载后刷新。
这个策略的一个应用是进入 Web 服务器的Web 页访问 MySQL 数据库。在此情形下，可能没有保证记录立即进入主表的较高权限。
如果数据并不完全是那种在系统非正常关闭事件中插入的单个记录，那么减少索引刷新的另一策略是使用 MyISAM 表的 DELAYED_KEY_WRITE 表创建选项(如果将 MySQL 用于某些数据录入工作时可能会出现这种情况)。此选项使索引高速缓存只偶尔刷新，而不是在每次插入后都要刷新。
如果希望在服务器范围内利用延迟索引刷新，只要利用 --delayed-key-write 选项启动 mysqld 即可。在此情形下，索引块写操作延迟到必须刷新块以便为其他索引值腾出空间为止，或延迟到执行了一个 flush-tables 命令后，或延迟到该索引表关闭。
应用的设计要点
==============
如果MySQL应用占用的CPU超过10%就应该考虑优化了。
1. 如果这个服务可以被其他非数据库应用代替(比如很多基于数据库的计数器完全可以用WEB日志统计代替)最好将其禁用。
非用数据库不可吗？虽然数据库的确可以简化很多应用的结构设计，但本身也是一个系统资源消耗比较大的应用。在某些情况下文本，DBM比数据库是更好的选择，比如：很多应用如果没有很高的实时统计需求的话，完全可以先记录到文件日志中，定期的导入到数据库中做后续统计分析。如果还是需要记录简单的二维键-值对应结构的话可以使用类似于DBM的HEAP类型表。因为HEAP表全部在内存中存取，效率非常高，但服务器突然断电时有可能出现数据丢失，所以非常适 合存储在线用户信息，日志等临时数据。即使需要使用数据库的，应用如果没有太复杂的数据完整性需求的化，完全可以不使用那些支持外键的商业数据库，比如 MySQL。只有非常需要完整的商业逻辑和事务完整性的时候才需要Oracle这样的大型数据库。对于高负载应用来说完全可以把日志文件，DBM，MySQL等轻量级方式做前端数据采集格式，然后用Oracle MSSQL DB2 Sybase等做数据库仓库以完成复杂的数据库挖掘分析工作。
2. 数据库服务的主要瓶颈：单个服务的连接数
对于一个应用来说，如果数据库表结构的设计能够按照数据库原理的范式来设计的话，并且已经使用了最新版本的MySQL，并且按照比较优化的方式运行了，那 么最后的主要瓶颈一般在于单个服务的连接数，即使一个数据库可以支持并发500个连接，最好也不要把应用用到这个地步，因为并发连接数过多数据库服务本身 用于调度的线程的开销也会非常大了。所以如果应用允许的话：让一台机器多跑几个MySQL服务分担。将服务均衡的规划到多个MySQL服务端口上：比如 app_1 ==> 3301 app_2 ==> 3302...app_9 ==> 3309。一个1G内存的机器跑上10个MySQL是很正常的。让10个MySQLD承担1000个并发连接效率要比让2个MySQLD承担1000个效 率高的多。当然，这样也会带来一些应用编程上的复杂度；
3. 使用单独的数据库服务器(不要让数据库和前台WEB服务抢内存)，MySQL拥有更多的内存就可能能有效的进行结果集的缓存；在前面的启动脚本中有一个-O key_buffer=32M参数就是用于将缺省的8M索引缓存增加到32M(当然对于)
4. 应用尽量使用PCONNECT和polling机制，用于节省MySQL服务建立连接的开销，但也会造成MySQL并发链接数过多(每个HTTPD都会对应一个MySQL线程)；
5. 表的横向拆分：让最常被访问的10%的数据放在一个小表里，90%的历史数据放在一个归档表里(所谓：快慢表)，数据中间通过定期“搬家”和定期删除无效数 据来节省，毕竟大部分应用(比如论坛)访问2个月前数据的几率会非常少，而且价值也不是很高。这样对于应用来说总是在一个比较小的结果级中进行数据选择，比较有利于数据的缓存，不要指望MySQL中对单表记录条数在10万级以上还有比较高的效率。而且有时候数据没有必要做那么精确，比如一个快表中查到了某个人发表的文章有60条结果，快表和慢表的比例是1:20，那么就可以简单的估计这个人一共发表了1200篇。Google的搜索结果数也是一样：对于很多上十万的结果数，后面很多的数字都是通过一定的算法估计出来的。
6. 数据库字段设计：表的纵向拆分(过渡范化)：将所有的定长字段(char, int等)放在一个表里，所有的变长字段(varchar,text,blob等)放在另外一个表里，2个表之间通过主键关联，这样，定长字段表可以得到 很大的优化(这样可以使用HEAP表类型，数据完全在内存中存取)，这里也说明另外一个原则，对于我们来说，尽量使用定长字段可以通过空间的损失换取访问效率的提高。在MySQL4中也出现了支持外键和事务的InnoDB类型表，标准的MyISAM格式表和基于HASH结构的HEAP内存表，MySQL之所以支持多种表类型，实际上是针对不同应用提供了不同的优化方式；
7. 仔细的检查应用的索引设计：可以在服务启动参数中加入 --log-slow-queries[=file]用于跟踪分析应用瓶颈，对于跟踪服务瓶颈最简单的方法就是用MySQL的status查看MySQL服务的运行统计和show processlist来查看当前服务中正在运行的SQL，如果某个SQL经常出现在PROCESS LIST中，一。有可能被查询的此时非常多，二，里面有影响查询的字段没有索引，三，返回的结果数过多数据库正在排序(SORTING)；所以做一个脚本：比如每2秒运行以下show processlist;把结果输出到文件中，看到底是什么查询在吃CPU。
8. 全文检索：如果相应字段没有做全文索引的话，全文检索将是一个非常消耗CPU的功能，因为全文检索是用不上一般数据库的索引的，所以要进行相应字段记录遍历。关于全文索引可以参考一下基于Java的全文索引引擎lucene的介绍。
9. 前台应用的记录缓存：比如一个经常使用数据库认证，如果需要有更新用户最后登陆时间的操作，最好记录更新后就把用户放到一个缓存中(设置2个小时后过期)，这样如果用户在2个小时内再次使用到登陆，就直接从缓存里认证，避免了过于频繁的数据库操作。
10. 查询优先的表应该尽可能为where和order by字句中的字段加上索引，数据库更新插入优先的应用索引越少越好。
总之：对于任何数据库单表记录超过100万条优化都是比较困难的，关键是要把应用能够转化成数据库比较擅长的数据上限内。也就是把复杂需求简化成比较成熟的解决方案内。