14 优化实例

本篇例子显示出一个多表JOIN如何能使用EXPLAIN提供的信息逐步被优化。

假定你有下面所示的SELECT语句，计划使用EXPLAIN来检查它：

EXPLAIN 
    SELECT
        tt.TicketNumber,
        tt.TimeIn,
        tt.ProjectReference,
        tt.EstimatedShipDate,
        tt.ActualShipDate,
        tt.ClientID,
        tt.ServiceCodes,
        tt.RepetitiveID,
        tt.CurrentProcess,
        tt.CurrentDPPerson,
        tt.RecordVolume,
        tt.DPPrinted,
        et.COUNTRY,
        et_1.COUNTRY,
        do.CUSTNAME
    FROM
        tt,
        et,
        et AS et_1,
        do
    WHERE
        tt.SubmitTime IS NULL
    AND tt.ActualPC = et.EMPLOYID
    AND tt.AssignedPC = et_1.EMPLOYID
    AND tt.ClientID = do.CUSTNMBR;

• 对于这个例子,假定被比较的列声明如下：

表	列	列类型
tt	ActualPC	CHAR(10)
tt	AssignedPC	CHAR(10)
tt	ClientID	CHAR(10)
et	EMPLOYID	CHAR(15)
do	CUSTNMBR	CHAR(15)

• 表有下面的索引：

表	索引
tt	ActualPC
tt	AssignedPC
tt	ClientID
et	EMPLOYID(主键)
do	CUSTNMBR(主键)

-tt.ActualPC值不是均匀分布的。

table	type	possible_keys	key	key_len	ref	rows
et	ALL	PRIMARY	NULL	NULL	NULL	74
do	ALL	PRIMARY	NULL	NULL	NULL	2135
et_1	ALL	PRIMARY	NULL	NULL	NULL	74
tt	ALL	AssignedPC,ClientID, ActualPC	NULL	NULL	NULL	3872

因为type对每张表是ALL，这个输出显示MySQL正在对所有表产生一个笛卡尔乘积；即每一个行的组合！这将花相当长的时间，因为必须检查每张表的行数的乘积！对于一个实例，这是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大，你只能想象它将花多长时间……

这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。在本文中，VARCHAR和CHAR是相同的，除非它们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15)，长度不匹配。

为了修正在列长度上的不同，使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)，再执行EXPLAIN语句产生这个结果：

table	type	possible_keys	key	key_len	ref	rows	Extra
tt	ALL	AssignedPC,ClientID, ActualPC	NULL	NULL	NULL	3872	Using where
do	ALL	PRIMARY	NULL	NULL	NULL	2135	range checked for each record (key map: 1)
et_1	ALL	PRIMARY	NULL	NULL	NULL	74	range checked for each record (key map: 1)
et	eq_ref	PRIMARY	PRIMARY	15	tt.ActualPC	1	--

这不是完美的，但是好一些了：rows值的乘积少了一个因子74。这个版本在几秒内执行完。

第2种方法能消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列的长度失配问题：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),MODIFY ClientID   VARCHAR(15);

EXPLAIN产生的输出显示在下面：

table	type	possible_keys	key	key_len	ref	rows	Extra
et	ALL	PRIMARY	NULL	NULL	NULL	74
tt	ref	AssignedPC,ClientID, ActualPC	ActualPC	15	et.EMPLOYID	52	Using where
et_1	eq_ref	PRIMARY	PRIMARY	15	tt.AssignedPC	1
do	eq_ref	PRIMARY	PRIMARY	15	tt.ClientID	1

剩下的问题是，默认情况，MySQL假设在tt.ActualPC列的值是均匀分布的，并且对tt表不是这样。幸好，很容易告诉MySQL来分析关键字分布：

mysql> ANALYZE TABLE tt；

现在联接是“完美”的了，而且EXPLAIN产生这个结果：

table	type	possible_keys	key	key_len	ref	rows	Extra
tt	ALL	AssignedPC ,ClientID, ActualPC	NULL	NULL	NULL	3872	Using where
et	eq_ref	PRIMARY	PRIMARY	15	tt.ActualPC	1
et_1	eq_ref	PRIMARY	PRIMARY	15	tt.AssignedPC	1
do	eq_ref	PRIMARY	PRIMARY	15	tt.ClientID	1

注意在从EXPLAIN输出的rows列是一个来自MySQL联接优化器的“教育猜测”。你应该检查数字是否接近事实。如果不是，可以通过在SELECT语句里面使用STRAIGHT_JOIN并且试着在FROM子句以不同的次序列出表，可能得到更好的性能。