MySQL查询优化技术讲座

MySQL查询优化技术讲座

数据库管理系统实现了理论上的概念，但是这种在实际硬件设备上的实现受到了实际物理条件的约束。其结果是，查询需要花费一些时间–有时候需要很长的时间。本期专题的内容就是帮助你找到如何让自己的等待时间最短的方法。

【文章概述】

使用索引

　　索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用，但是一般来说引起最大性能差异的都是索引的正确使用。因此我首先讲述是什么索引以及索引是怎么样提高查询性能的。同时还要讨论在某些环境中索引可能降低性能，并选择提供了数据表的索引一些指导方针。

MySQL查询优化器

　　了解一些优化器的知识，作为对如何建立索引的补充，对我们是有好处的，因为这样你才能更好地利用自己所建立的索引。某些编写查询的方法实际上让索引不起作用，在一般情况下你应该避免这种情形的发生。这一讲中将讨论MySQL查询优化器，我将试图找到执行具有最高效率查询的方法。

数据类型与效率

　　在大多数情况下，你所关注的是SELECT查询的优化，因为SELECT查询是最常见的查询类型，而且如何优化它们又不是太简单。与此形成对比，把数据载入数据库的操作就相对直接了。然而，你仍然可以利用某些策略来改善数据载入操作的效率。

调度和锁定的问题

　　前面的部分主要是聚焦于如何让单独的查询执行的速度更快。MySQL还允许你改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作。改变优先级还可以确保特定类型的查询被处理得更快。这一部分讲解MySQL的默认的调度策略和可以用来影响这些策略的选项。它还谈到了并发性插入操作的使用和存储引擎锁定层次对客户端的并发性的影响。

管理员的优化措施

　　前面的部分中讲解的优化措施都是没有特权的MySQL用户能够执行的。可以控制MySQL服务器或计算机的系统管理员能够执行额外的优化措施。

【 文章正文】

MySQL查询优化技术系列讲座之：使用索引

索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用，但是一般来说引起最大性能差异的都是索引的正确使用。在MySQL邮件列表中，人们经常询问那些让查询运行得更快的方法。在大多数情况下，我们应该怀疑数据表上有没有索引，并且通常在添加索引之后立即解决了问题。当然，并不总是这样简单就可以解决问题的，因为优化技术本来就并非总是简单的。然而，如果没有使用索引，在很多情况下，你试图使用其它的方法来提高性能都是在浪费时间。首先使用索引来获取最大的性能提高，接着再看其它的技术是否有用。

　　这一部分讲述了索引是什么以及索引是怎么样提高查询性能的。它还讨论了在某些环境中索引可能降低性能，并为你明智地选择数据表的索引提供了一些指导方针。在下一部分中我们将讨论MySQL查询优化器，它试图找到执行查询的效率最高的方法。了解一些优化器的知识，作为对如何建立索引的补充，对我们是有好处的，因为这样你才能更好地利用自己所建立的索引。某些编写查询的方法实际上让索引不起作用，在一般情况下你应该避免这种情形的发生。

　　索引的优点

　　让我们开始了解索引是如何工作的，首先有一个不带索引的数据表。不带索引的表仅仅是一个无序的数据行集合。例如，图1显示的ad表就是不带索引的表，因此如果需要查找某个特定的公司，就必须检查表中的每个数据行看它是否与目标值相匹配。这会导致一次完全的数据表扫描，这个过程会很慢，如果这个表很大，但是只包含少量的符合条件的记录，那么效率会非常低。

图1：无索引的ad表

　　图2是同样的一张数据表，但是增加了对ad表的company_num数据列的索引。这个索引包含了ad表中的每个数据行的条目，但是索引的条目是按照company_num值排序的。现在，我们不是逐行查看以搜寻匹配的数据项，而是使用索引。假设我们查找公司13的所有数据行。我们开始扫描索引并找到了该公司的三个值。接着我们碰到了公司14的索引值，它比我们正在搜寻的值大。索引值是排过序的，因此当我们读取了包含14的索引记录的时候，我们就知道再也不会有更多的匹配记录，可以结束查询操作了。因此使用索引获得的功效是：我们找到了匹配的数据行在哪儿终止，并能够忽略其它的数据行。另一个功效来自使用定位算法查找第一条匹配的条目，而不需要从索引头开始执行线性扫描（例如，二分搜索就比线性扫描要快一些）。通过使用这种方法，我们可以快速地定位第一个匹配的值，节省了大量的搜索时间。数据库使用了多种技术来快速地定位索引值，但是在本文中我们不关心这些技术。重点是它们能够实现，并且索引是个好东西。

图2：索引后的ad表

　　你可能要问，我们为什么不对数据行进行排序从而省掉索引？这样不是也能实现同样的搜索速度的改善吗？是的，如果表只有一个索引，这样做也可能达到相同的效果。但是你可能添加第二个索引，那么就无法一次使用两种不同方法对数据行进行排序了（例如，你可能希望在顾客名称上建立一个索引，在顾客ID 号或电话号码上建立另外一个索引）。把与数据行相分离的条目作为索引解决了这个问题，允许我们创建多个索引。此外，索引中的行一般也比数据行短一些。当你插入或删除新的值的时候，移动较短的索引值比移动较长数据行的排序次序更加容易。

　　不同的MySQL存储引擎的索引实现的具体细节信息是不同的。例如，对于MyISAM数据表，该表的数据行保存在一个数据文件中，索引值保存在索引文件中。一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。索引文件中的每个索引都包含一个排序的键记录（它用于快速地访问数据文件）数组。

　　与此形成对照的是，BDB和 InnoDB存储引擎没有使用这种方法来分离数据行和索引值，尽管它们也把索引作为排序后的值集合进行操作。在默认情况下，BDB引擎使用单个文件存储数据和索引值。InnoDB使用单个数据表空间（tablespace），在表空间中管理所有InnoDB表的数据和索引存储。我们可以把InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一个表空间文件中。
前面的讨论描述了单个表查询环境下的索引的优点，在这种情况下，通过减少对整个表的扫描，使用索引明显地提高了搜索的速度。当你运行涉及多表联结（jion）查询的时候，索引的价值就更高了。在单表查询中，你需要在每个数据列上检查的值的数量是表中数据行的数量。在多表查询中，这个数量可能大幅度上升，因为这个数量是这些表中数据行的数量所产生的。

　　假设你拥有三个未索引的表t1、t2和t3，每个表都分别包含数据列i1、i2和i3，并且每个表都包含了1000条数据行，其序号从1到1000。查找某些值匹配的数据行组合的查询可能如下所示：

SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;

　　这个查询的结果应该是1000行，每个数据行包含三个相等的值。如果在没有索引的情况下处理这个查询，那么如果我们不对这些表进行全部地扫描，我们是没有办法知道哪些数据行含有哪些值的。因此你必须尝试所有的组合来查找符合WHERE条件的记录。可能的组合的数量是1000 x 1000 x 1000（10亿！），它是匹配记录的数量的一百万倍。这就浪费了大量的工作。这个例子显示，如果没有使用索引，随着表的记录不断增长，处理这些表的联结所花费的时间增长得更快，导致性能很差。我们可以通过索引这些数据表来显著地提高速度，因为索引让查询采用如下所示的方式来处理：

　　1．选择表t1中的第一行并查看该数据行的值。

　　2．使用表t2上的索引，直接定位到与t1的值匹配的数据行。类似地，使用表t3上的索引，直接定位到与表t2的值匹配的数据行。

　　3．处理表t1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。

　　在这种情况下，我们仍然对表t1执行了完整的扫描，但是我们可以在t2和t3上执行索引查找，从这些表中直接地获取数据行。理论上采用这种方式运行上面的查询会快一百万倍。当然这个例子是为了得出结论来人为建立的。然而，它解决的问题却是现实的，给没有索引的表添加索引通常会获得惊人的性能提高。

　　MySQL有几种使用索引的方式：

　　· 如上所述，索引被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。

　　· 对于使用了MIN()或MAX()函数的查询，索引数据列中最小或最大值可以很快地找到，不用检查每个数据行。

　　· MySQL利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

　　· 有时候MySQL会利用索引来读取查询得到的所有信息。假设你选择了MyISAM表中的被索引的数值列，那么就不需要从该数据表中选择其它的数据列。在这种情况下，MySQL从索引文件中读取索引值，它所得到的值与读取数据文件得到的值是相同的。没有必要两次读取相同的值，因此没有必要考虑数据文件。

　　索引的代价

　　一般来说，如果MySQL能够找到方法，利用索引来更快地处理查询，它就会这样做。这意味着，对于大多数情况，如果你没有对表进行索引，就会使性能受到损害。这就是我所描绘的索引优点的美景。但是它有缺点吗？有的，它在时间和空间上都有开销。在实践中，索引的优点的价值一般会超过这些缺点，但是你也应该知道到底有一些什么缺点。

　　首先，索引加快了检索的速度，但是减慢了插入和删除的速度，同时还减慢了更新被索引的数据列中的值的速度。也就是说，索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入数据行，还需要改变所有的索引。数据表带有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大。在本文的”高效率载入数据”部分中，我们将更细致地了解这些现象并找出处理方法。

　　其次，索引会花费磁盘空间，多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制：

　　· 对于MyISAM表，频繁地索引可能引起索引文件比数据文件更快地达到最大限制。

　　· 对于BDB表，它把数据和索引值一起存储在同一个文件中，添加索引引起这种表更快地达到最大文件限制。

　　· 在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池，因此添加索引会更快地耗尽表空间中的存储。但是，与MyISAM和BDB表使用的文件不同，InnoDB共享表空间并不受操作系统的文件大小限制，因为我们可以把它配置成使用多个文件。只要有额外的磁盘空间，你就可以通过添加新组件来扩展表空间。

　　使用单独表空间的InnoDB表与BDB表受到的约束是一样的，因为它的数据和索引值都存储在单个文件中。

　　这些要素的实际含义是：如果你不需要使用特殊的索引帮助查询执行得更快，就不要建立索引。

　　选择索引

　　假设你已经知道了建立索引的语法，但是语法不会告诉你数据表应该如何索引。这要求我们考虑数据表的使用方式。这一部分指导你如何识别出用于索引的备选数据列，以及如何最好地建立索引：

　　用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说，用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、 ORDER BY或GROUP BY子句中的列。仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列：

SELECT
col_a <- 不是备选列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 备选列
WHERE
col_d = expr; <- 备选列

　　当然，显示的数据列与WHERE子句中使用的数据列也可能相同。我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。

　　Join子句或WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在的表－行组合。

　　考虑数据列的基数（cardinality）。基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是 4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有”M”和”F”两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是”30%”。现在查询优化器更加复杂，把其它一些因素也考虑进去了，因此这个百分比并不是MySQL决定选择使用扫描还是索引的唯一因素。

　　索引较短的值。尽可能地使用较小的数据类型。例如，如果MEDIUMINT足够保存你需要存储的值，就不要使用BIGINT数据列。如果你的值不会长于25个字符，就不要使用CHAR(100)。较小的值通过几个方面改善了索引的处理速度：

　　· 较短的值可以更快地进行比较，因此索引的查找速度更快了。

　　· 较小的值导致较小的索引，需要更少的磁盘I/O。

　　· 使用较短的键值的时候，键缓存中的索引块（block）可以保存更多的键值。MySQL可以在内存中一次保持更多的键，在不需要从磁盘读取额外的索引块的情况下，提高键值定位的可能性。

　　对于InnoDB和BDB等使用聚簇索引（clustered index）的存储引擎来说，保持主键（primary key）短小的优势更突出。聚簇索引中数据行和主键值存储在一起（聚簇在一起）。其它的索引都是次级索引；它们存储主键值和次级索引值。次级索引屈从主键值，它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中，因此如果主键值很长，每个次级索引就需要更多的额外空间。

　　索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。例如，如果有CHAR(200) 数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间，并且可能使你的查询速度更快。通过索引较短的值，你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大，因为如果这样操作，那么在索引中不会有太多的唯一值。

　　你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

　　使用最左（leftmost）前缀。建立多列复合索引的时候，你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用，因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为”最左前缀”（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。

　　假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照 state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：

state, city, zip
state, city
state

　　MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

　　不要过多地索引。不要认为”索引越多，性能越高”，不要对每个数据列都进行索引。我们在前面提到过，每个额外的索引都会花费更多的磁盘空间，并降低写操作的性能。当你修改表的内容的时候，索引就必须被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就没有必要减小表的修改操作的速度。此外，为检索操作生成执行计划的时候，MySQL会考虑索引。建立额外的索引会给查询优化器增加更多的工作量。如果索引太多，有可能（未必）出现MySQL选择最优索引失败的情况。维护自己必须的索引可以帮助查询优化器来避免这类错误。

　　如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了（例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引）。

　　让索引类型与你所执行的比较的类型相匹配。在你建立索引的时候，大多数存储引擎会选择它们将使用的索引实现。例如，InnoDB通常使用B树索引。 MySQL也使用B树索引，它只在三维数据类型上使用R树索引。但是，MEMORY存储引擎支持散列索引和B树索引，并允许你选择使用哪种索引。为了选择索引类型，需要考虑在索引数据列上将执行的比较操作类型：

　　· 对于散列（hash）索引，会在每个数据列值上应用散列函数。生成的结果散列值存储在索引中，并用于执行查询。散列函数实现的算法类似于为不同的输入值生成不同的散列值。使用散列值的好处是散列值比原始值的比较效率更高。散列索引用于执行=或<=>操作等精确匹配的时候速度非常快。但是对于查询一个值的范围效果就非常差了：

id < 30
weight BETWEEN 100 AND 150

　　· B树索引可以用于高效率地执行精确的或者基于范围（使用操作<、<=、=、>=、>、<>、!=和BETWEEN）的比较。B树索引也可以用于LIKE模式匹配，前提是该模式以文字串而不是通配符开头。

　　如果你使用的MEMORY数据表只进行精确值查询，散列索引是很好的选择。这是MEMORY表使用的默认的索引类型，因此你不需要特意指定。如果你希望在MEMORY表上执行基于范围的比较，应该使用B树索引。为了指定这种索引类型，需要给索引定义添加USING BTREE。例如：

CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

　　如果你希望执行的语句的类型允许，单个MEMORY表可以同时拥有散列索引和B树索引，即使在同一个数据列上。

　　有些类型的比较不能使用索引。如果你只是通过把值传递到函数（例如STRCMP()）中来执行比较操作，那么对它进行索引就没有价值。服务器必须计算出每个数据行的函数值，它会排除数据列上索引的使用。

　　使用慢查询（slow-query）日志来识别执行情况较差的查询。这个日志可以帮助你找出从索引中受益的查询。你可以直接查看日志（它是文本文件），或者使用mysqldumpslow工具来统计它的内容。如果某个给定的查询多次出现在”慢查询”日志中，这就是一个线索，某个查询可能没有优化编写。你可以重新编写它，使它运行得更快。你要记住，在评估”慢查询”日志的时候，”慢”是根据实际时间测定的，在负载较大的服务器上”慢查询”日志中出现的查询会多一些。

MySQL查询优化系列讲座之：查询优化器

当你提交一个查询的时候，MySQL会分析它，看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段，可以查看MySQL参考手册。

　　当然，MySQL查询优化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查询，那么无论数据表有多大，MySQL执行它的速度都会非常快：

SELECT * FROM tbl_name WHERE 0;

　　在这个例子中，MySQL查看WHERE子句，认识到没有符合查询条件的数据行，因此根本就不考虑搜索数据表。你可以通过提供一个 EXPLAIN语句看到这种情况，这个语句让MySQL显示自己执行的但实际上没有真正地执行的SELECT查询的一些信息。如果要使用EXPLAIN，只需要在EXPLAIN单词放在SELECT语句的前面：

mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE

　　通常情况下，EXPLAIN返回的信息比上面的信息要多一些，还包括用于扫描数据表的索引、使用的联结类型、每张数据表中估计需要检查的数据行数量等非空（NULL）信息。

　　优化器是如何工作的

　　MySQL查询优化器有几个目标，但是其中最主要的目标是尽可能地使用索引，并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交 SELECT语句查找数据行，而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快，那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试，查询就可以执行地更快。假设你的查询检验了两个数据列，每个列上都有索引：

SELECT col3 FROM mytable
WHERE col1 = ’some value’ AND col2 = ’some other value’;

　　假设col1上的测试匹配了900个数据行，col2上的测试匹配了300个数据行，而同时进行的测试只得到了30个数据行。先测试Col1 会有900个数据行，需要检查它们找到其中的30个与col2中的值匹配记录，其中就有870次是失败了。先测试col2会有300个数据行，需要检查它们找到其中的30个与col1中的值匹配的记录，只有270次是失败的，因此需要的计算和磁盘I/O更少。其结果是，优化器会先测试col2，因为这样做开销更小。

　　你可以通过下面一个指导帮助优化器更好地利用索引：

　　尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候，请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。例如，INT与BIGINT是不同的。CHAR(10)被认为是CHAR(10)或VARCHAR(10)，但是与CHAR(12)或VARCHAR(12)不同。如果你所比较的数据列的类型不同，那么可以使用 ALTER TABLE来修改其中一个，使它们的类型相匹配。

　　尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列，MySQL就不会使用索引，因为它必须计算出每个数据行的表达式值。有时候这种情况无法避免，但是很多情况下你可以重新编写一个查询让索引列独立地出现。

　　下面的WHERE子句显示了这种情况。它们的功能相同，但是对于优化目标来说就有很大差异了：

WHERE mycol < 4 / 2
WHERE mycol * 2 < 4

　　对于第一行，优化器把表达式4/2简化为2，接着使用mycol上的索引来快速地查找小于2的值。对于第二个表达式，MySQL必须检索出每个数据行的mycol值，乘以2，接着把结果与4进行比较。在这种情况下，不会使用索引。数据列中的每个值都必须被检索到，这样才能计算出比较表达式左边的值。

　　我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询，就不会使用这个索引：

SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;

　　这个表达式不会把1990与索引列进行比较；它会把1990与该数据列计算出来的值比较，而每个数据行都必须计算出这个值。其结果是，没有使用date_col上的索引，因为执行这样的查询需要全表扫描。怎么解决这个问题呢？只需要使用文本日期，接着就可以使用date_col上的索引来查找列中匹配的值了：

WHERE date_col < ’1990-01-01’

　　但是，假设你没有特定的日期。你可能希望找到一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多种方法–它们的效率并不同。下面就有三种：

WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

　　对于第一行，不会用到索引，因为每个数据行都必须检索以计算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和 TO_DAYS(CURDATE())都是常量，因此在处理查询之前，比较表达式的右边可以被优化器一次性计算出来，而不需要每个数据行都计算一次。但是 date_col列仍然出现在函数调用中，它阻止了索引的使用。第三行是这几个中最好的。同样，在执行查询之前，比较表达式的右边可以作为常量一次性计算出来，但是现在它的值是一个日期。这个值可以直接与date_col值进行比较，再也不需要转换成天数了。在这种情况下，会使用索引。

　　在LIKE模式的开头不要使用通配符。有些字符串搜索使用如下所示的WHERE子句：

WHERE col_name LIKE ’%string%’

　　如果你希望找到那些出现在数据列的任何位置的字符串，这个语句就是对的。但是不要因为习惯而简单地把”%”放在字符串的两边。如果你在查找出现在数据列开头的字符串，就删掉前面的”%”。假设你要查找那些类似MacGregor或MacDougall等以”Mac”开头的名字。在这种情况下， WHERE子句如下所示：

WHERE last_name LIKE ’Mac%’

　　优化器查看该模式中词首的文本，并使用索引找到那些与下面的表达式匹配的数据行。下面的表达式是使用last_name索引的另一种形式：

WHERE last_name >= ’Mac’ AND last_name < ’Mad’

　　这种优化不能应用于使用了REGEXP操作符的模式匹配。REGEXP表达式永远不会被优化。

　　帮助优化器更好的判断索引的效率。在默认情况下，当你把索引列的值与常量进行比较的时候，优化器会假设键值在索引内部是均匀分布的。在决定进行常量比较是否使用索引的时候，优化器会快速地检查索引，估计出会用到多少个实体（entry）。对应MyISAM、InnoDB和BDB数据表来说，你可以使用ANALYZE TABLE让服务器执行对键值的分析。它会为优化器提供更好的信息。

　　使用EXPLAIN验证优化器的操作。EXPLAIN语句可以告诉你是否使用了索引。当你试图用另外的方式编写语句或检查添加索引是否会提高查询执行效率的时候，这些信息对你是有帮助的。

　　在必要的时候给优化器一些提示。正常情况下，MySQL优化器自由地决定扫描数据表的次序来最快地检索数据行。在有些场合中优化器没有作出最佳选择。如果你察觉这种现象发生了，就可以使用STRAIGHT_JOIN关键字来重载优化器的选择。带有STRAIGHT_JOIN的联结类似于交叉联结，但是强迫数据表按照FROM子句中指定的次序来联结。

　　在SELECT语句中有两个地方可以指定STRAIGHT_JOIN。你可以在SELECT关键字和选择列表之间的位置指定，这样会对语句中所有的交叉联结产生影响；你也可以在FROM子句中指定。下面的两个语句功能相同：

SELECT STRAIGHT_JOIN … FROM t1, t2, t3 … ;
SELECT … FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 … ;

　　分别在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的情况下运行这个查询；MySQL可能因为什么原因没有按照你认为最好的次序使用索引（你可以使用EXPLAIN来检查MySQL处理每个语句的执行计划）。

　　你还可以使用FORCE INDEX、USE INDEX或IGNORE INDEX来指导服务器如何使用索引。

　　利用优化器更加完善的区域。MySQL可以执行联结和子查询，但是子查询是最近才支持的，是在MySQL 4.1中添加的。因而在很多情况下，优化器对联结操作的调整比对子查询的调整要好一些。当你的子查询执行地很慢的时候，这就是一条实际的提示。有一些子查询可以使用逻辑上相等的联结来重新表达。在可行的情况下，你可以把子查询重新改写为联结，看是否执行地快一些。

　　测试查询的备用形式，多次运行。当你测试查询的备用形式的时候（例如，子查询与等同的联结操作对比），每种方式都应该多次运行。如果两种形式都只运行了一次，那么你通常会发现第二个查询比第一个快，这是因为第一个查询得到的信息仍然保留在缓存中，以至于第二个查询没有真正地从磁盘上读取数据。你还应该在系统负载相对平稳的时候运行查询，以避免系统中其它的事务影响结果。

　　避免过度地使用MySQL自动类型转换。MySQL会执行自动的类型转换，但是如果你能够避免这种转换操作，你得到的性能就更好了。例如，如果num_col是整型数据列，那么下面这些查询将返回相同的结果：

SELECT * FROM mytbl WHERE num_col = 4;
SELECT * FROM mytbl WHERE num_col = ’4’;

　　但是第二个查询涉及到了类型转换。转换操作本身为了把整型和字符串型转换为双精度型进行比较，使性能恶化了。更严重的情况是，如果num_col是索引的，那么涉及到类型转换的比较操作不会使用索引。

　　相反类型的比较操作（把字符串列与数值比较）也会阻止索引的使用。假设你编写了如下所示的查询：

SELECT * FROM mytbl WHERE str_col = 4;

　　在这个例子中，不会使用str_col上的索引，因为在把str_col中的字符串值转换成数值的时候，可能有很多值等于4（例如’4’、’4.0’和’4th’）。分辨哪些值符合要求的唯一办法是读取每个数据行并执行比较操作。

　　使用EXPLAIN来检查优化器的操作

　　EXPLAIN对于了解优化器生成的、用于处理语句的执行计划的内部信息是很有帮助的。在这一部分中，我们将解释EXPLAIN的两种用途：

　　· 查看采用不同的方式编写的查询是否影响了索引的使用。

　　· 查看向数据表添加索引对优化器生成高效率执行计划的能力的影响。

　　这一部分只讨论与示例相关的EXPLAIN输入字段。

　　前面，在”优化器是如何工作的”部分中我们得出的观点是，你编写表达式的方式将决定优化器是否能使用可用的索引。特别是上面的讨论使用了下面三个逻辑相等的WHERE子句的例子，只有第三个允许使用索引：

WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

　　EXPLAIN允许你查看编写表达式的某种方式是否比另外的方式好一些。为了看到结果，让我们分别用这三个WHERE子句搜索成员表中过期的数据列值，把cutoff值设为30天。为了看到索引的使用和表达式编写方式之间的关系，我们首先对expiration列进行索引：

mysql> ALTER TABLE member ADD INDEX (expiration);

　　接着在每个表达式形式上使用EXPLAIN，看优化器生成了什么样的执行计划：

mysql> EXPLAIN SELECT * FROM MEMBER
-> WHERE TO_DAYS(expiration) - TO_DAYS(CURDATE()) < 30\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MEMBER
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 102
Extra: Using where
mysql> EXPLAIN SELECT * FROM MEMBER
-> WHERE TO_DAYS(expiration) < 30 + TO_DAYS(CURDATE())\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MEMBER
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 102
Extra: Using where
mysql> EXPLAIN SELECT * FROM MEMBER
-> WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MEMBER
type: range
possible_keys: expiration
key: expiration
key_len: 4
ref: NULL
rows: 6
Extra: Using where

　　上面的结果显示，前面两个语句没有使用索引。类型（type）值表明了将如何从数据表中读取信息。ALL意味着”将检查所有的记录”。也就是说，它会执行全表扫描，没有利用索引。每个与键相关的列都是NULL也表明没有使用索引。

　　与此形成对比的是，第三个语句的结果显示，采用这种方式编写的WHERE子句，优化器可以使用expiration列上的索引：

　　· 类型（type）值表明它可以使用索引来搜索特定范围的值（小于右边表达式给定的值）。

　　· 可能键（possible_keys）和键（key）值显示expiration上的索引已经被考虑作为备选索引，并且它也是真正使用的索引。

　　· 行数（rows）值显示优化器估计自己需要检查6个数据行来处理该查询。这比前面两个执行计划的102小很多。

　　EXPLAIN的第二种用途是查看添加索引是否能帮助优化器更高效率地执行语句。我将使用两个未被索引的数据表。它足够显示建立索引的效率。相同的规则可以应用于涉及多表的更加复杂的联结操作。

　　假设我们有两个数据表t1和t2，每个有1000行，包含的值从1到1000。下面的查询查找出两个表中值相同的数据行：

mysql> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
+——+——+
| i1 | i2 |
+——+——+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
…

　　两个表都没有索引的时候，EXPLAIN产生下面的结果：

mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where

　　类型列中的ALL表明要进行检查所有数据行的全表扫描。可能键列中的NULL表明没有找到用于提高查询速度的备选索引（键、键长度和参考列都是NULL也是因为缺少合适的索引）。Using where表明使用WHERE子句中的信息来识别合格的数据行。

　　这段信息告诉我们，优化器没有为提高执行查询的效率找到任何有用的信息：

　　· 它将对t1表进行全表扫描。

　　· 对于t1中的每一行，它将执行t2的全表扫描，使用WHERE子句中的信息识别出合格的行。

　　行数值显示了优化器估计的每个阶段查询需要检查的行数。T1的估计值是1000，因为1000可以完成全表扫描。相似地，t2的估计值也是1000，但是这个值是对于t1的每一行的。换句话说，优化器所估计的处理该查询所需要检查的数据行组合的数量是1000×1000，也就是一百万。这会造成很大的浪费，因为实际上只有1000个组合符合WHERE子句的条件。

　　为了使这个查询的效率更高，给其中一个联结列添加索引并重新执行EXPLAIN语句：

mysql> ALTER TABLE t2 ADD INDEX (i2);
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 10
Extra: Using where; Using index

　　我们可以看到性能提高了。T1的输出没有改变（表明还是需要进行全表扫描），但是优化器处理t2的方式就有所不同了：

　　· 类型从ALL改变为ref，意味着可以使用参考值（来自t1的值）来执行索引查找，定位t2中合格的数据行。

　　· 参考值在参考（ref）字段中给出了：sampdb.t1.i1。

　　· 行数值从1000降低到了10，显示出优化器相信对于t1中的每一行，它只需要检查t2中的10行（这是一个悲观的估计值。实际上，在t2中只有一行与 t1中数据行匹配。我们在后面会看到如何帮助优化器改善这个估计值）。数据行组合的全部估计值使1000×10＝10000。它比前面的没有索引的时候估计出来的一百万好多了。

　　对t1进行索引有价值吗？实际上，对于这个特定的联结操作，扫描一张表是必要的，因此没有必要对t1建立索引。如果你想看到效果，可以索引t1.i1并再次运行EXPLAIN：

mysql> ALTER TABLE t1 ADD INDEX (i1);
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: i1
key: i1
key_len: 5
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 10
Extra: Using where; Using index

　　上面的输出与前面的EXPLAIN的输出相似，但是添加索引对t1的输出有一些改变。类型从NULL改成了index，附加（Extra）从空的改成了Using index。这些改变表明，尽管对索引的值仍然需要执行全表扫描，但是优化器还是可以直接从索引文件中读取值，根据不需要使用数据文件。你可以从 MyISAM表中看到这类结果，在这种情况下，优化器知道自己只询问索引文件就能够得到所有需要的信息。对于InnoDB 和BDB表也有这样的结果，在这种情况下优化器可以单独使用索引中的信息而不用搜索数据行。

　　我们可以运行ANALYZE TABLE使优化器进一步优化估计值。这会引起服务器生成键值的静态分布。分析上面的表并再次运行EXPLAIN得到了更好的估计值：

mysql> ANALYZE TABLE t1, t2;
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: i1
key: i1
key_len: 5
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 1
Extra: Using where; Using index

　　在这种情况下，优化器估计在t2中与t1的每个值匹配的数据行只有一个。

　　重载优化过程

　　这个过程听起来多余，但是有时候你还是希望去掉某些MySQL优化行为的：

　　重载优化器的表联结次序。使用STRAIGHT_JOIN强迫优化器按照特定的次序使用数据表。在这样操作的时候，你必须对数据表进行排序，这样才能保证第一张表是被选择的行数最少的表。如果你不能确定被选择行数最少的是哪一张表，那么就把行数最多的放到第一的位置。换句话说，试着对表进行排序，使最有约束力的选择出现在最前面。你对可能的备选数据行缩小地越早，执行查询的性能就越好。请确保在带有STRAIGHT_JOIN和不带 STRAIGHT_JOIN的时候分别执行该查询。有时候由于某些原因的存在，优化器没有按照你认定的方式联结数据表，STRAIGHT_JOIN也可能没有实际的帮助作用。

　　另一个可能性是在联结的数据表列表中的某个表的后面使用FORCE INDEX、USE INDEX和IGNORE INDEX调节符来告诉MySQL如何使用索引。这在优化器没有做出正确选择的时候是有用处的。

　　以最小的代价清空一张表。当需要完全地清空一张MyISAM数据表的时候，最快的方法是删除它并利用它的.frm文件中存储的脚本来重新建立它。使用TRUNCATE TABLE语句实现：

TRUNCATE TABLE tbl_name;

　　通过重新建立MyISAM数据表来清空它的这种服务器优化措施使该操作非常快，因为不需要单独地逐行删除。

　　但是TRUNCATE TABLE也带来了一些副作用，在某些环境中是不符合要求的：

　　· TRUNCATE TABLE不一定能够计算出被删除的数据列的精确数量。如果你需要这个数值，请使用不带WHERE子句的DELETE语句：

DELETE FROM tbl_name;

　　· 但是，通过重新建立来清空数据表，它可能会把序号的起始值设置为1。为了避免这种情况，请使用”不优化的”全表DELETE语句，它带有一个恒为真的WHERE子句：

DELETE FROM tbl_name WHERE 1;

　　添加WHERE子句会强迫MySQL进行逐行删除，因为它必须计算出每一行的值来判断是否能够删除它。这个语句执行的速度很慢，但是它却保留了当前的AUTO_INCREMENT序号。

MySQL查询优化系列讲座之：数据类型与效率

这一部分提供了如何选择数据类型来帮助提高查询运行速度的一些指导：

　　在可以使用短数据列的时候就不要用长的。如果你有一个固定长度的CHAR数据列，那么就不要让它的长度超出实际需要。如果你在数据列中存储的最长的值有40个字符，就不要定义成CHAR(255)，而应该定义成CHAR(40)。如果你能够用MEDIUMINT代替BIGINT，那么你的数据表就小一些（磁盘I/O少一些），在计算过程中，值的处理速度也快一些。如果数据列被索引了，那么使用较短的值带来的性能提高更加显著。不仅索引可以提高查询速度，而且短的索引值也比长的索引值处理起来要快一些。

　　如果你可以选择数据行的存储格式，那么应该使用最适合存储引擎的那种。对于 MyISAM数据表，最好使用固定长度的数据列代替可变长度的数据列。例如，让所有的字符列用CHAR类型代替VARCHAR类型。权衡得失，我们会发现数据表使用了更多的磁盘空间，但是如果你能够提供额外的空间，那么固定长度的数据行被处理的速度比可变长度的数据行要快一些。对于那些被频繁修改的表来说，这一点尤其突出，因为在那些情况下，性能更容易受到磁盘碎片的影响。

　　· 在使用可变长度的数据行的时候，由于记录长度不同，在多次执行删除和更新操作之后，数据表的碎片要多一些。你必须使用OPTIMIZE TABLE来定期维护其性能。固定长度的数据行没有这个问题。

　　· 如果出现数据表崩溃的情况，那么数据行长度固定的表更容易重新构造。使用固定长度数据行的时候，每个记录的开始位置都可以被检测到，因为这些位置都是固定记录长度的倍数，但是使用可变长度数据行的时候就不一定了。这不是与查询处理的性能相关的问题，但是它一定能够加快数据表的修复速度。

　　尽管把MyISAM数据表转换成使用固定长度的数据列可以提高性能，但是你首先需要考虑下面