分类： Mysql/postgreSQL
2008-05-19 18:41:16
下面的例子展示了如何通过 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;
在这个例子中，先做以下假设：
要比较的字段定义如下：
| Table | Column | Column Type |
tt |
ActualPC |
CHAR(10) |
tt |
AssignedPC |
CHAR(10) |
tt |
ClientID |
CHAR(10) |
et |
EMPLOYID |
CHAR(15) |
do |
CUSTNMBR |
CHAR(15) |
数据表的索引如下：
| Table | Index |
tt |
ActualPC |
tt |
AssignedPC |
tt |
ClientID |
et |
EMPLOYID (primary key) |
do |
CUSTNMBR (primary key) |
tt.ActualPC 的值是不均匀分布的。
在任何优化措施未采取之前，经过 EXPLAIN 分析的结果显示如下：
table type possible_keys key key_len ref rows Extra
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, NULL NULL NULL 3872
ClientID,
ActualPC
range checked for each record (key map: 35)
由于字段 type 的对于每个表值都是 ALL，这个结果意味着MySQL对所有的表做一个迪卡尔积；这就是说，每条记录的组合。这将需要花很长的时间，因为需要扫描每个表总记录数乘积的总和。在这情况下，它的积是 74 * 2135 * 74 * 3872 = 45,268,558,720 条记录。如果数据表更大的话，你可以想象一下需要多长的时间。
在这里有个问题是当字段定义一样的时候，MySQL就可以在这些字段上更快的是用索引（对 ISAM 类型的表来说，除非字段定义完全一样，否则不会使用索引）。在这个前提下，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, NULL NULL NULL 3872 Using
ClientID, where
ActualPC
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, ActualPC 15 et.EMPLOYID 52 Using
ClientID, where
ActualPC
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 NULL NULL NULL 3872 Using
ClientID, where
ActualPC
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
分句中用不同的次序列出各个表。
（t114）
