两个explain语句看起来几乎完全相同。然而，第一个查询运行1.234秒，第二个查询运行23.282秒。
-- Query 1
-- Duration / Fetch 1.234 sec/ 0.000 sec
select t.taxpayerId, ifnull(t.name, '') name, ifnull(t.name2, '') name2, t.address, t.address2, t.city, t.state, t.zip,t.inBankruptcy, ifnull(bal.balance,0) amountDue, t.emailAddress, t.blocked, ifnull(t.comments, '') as comments, serial, ifnull(p.address, 0), t.statusCode
from Taxpayer t
left join Parcel p on p.taxpayer_id = t.id
left join PersonalProperty pp on pp.taxpayer_id = t.id
left join (select t.taxpayerID, sum(b.balance) balance from Bill b join TaxpayerHistory t on t.id = b.taxpayer_id where b.balance <> 0 group by t.taxpayerID ) bal on bal.taxpayerId = t.taxpayerId
where 1
and (p.mapNumber like '1205100%' or t.id in ((select th.taxpayer_id from Bill b join TaxpayerHistory th on th.id = b.taxpayer_id join ParcelHistory ph on ph.id = b.parcel_id where ph.mapNumber like '1205100%')))
and t.name like '%%'
group by t.taxpayerId
order by p.mapNumber;
-- Query 2
-- Duration / Fetch 23.282 sec/ 0.000 sec
select t.taxpayerId, ifnull(t.name, '') name, ifnull(t.name2, '') name2, t.address, t.address2, t.city, t.state, t.zip,t.inBankruptcy, ifnull(bal.balance,0) amountDue, t.emailAddress, t.blocked, ifnull(t.comments, '') as comments, serial, ifnull(p.address, 0), t.statusCode
from Taxpayer t
left join Parcel p on p.taxpayer_id = t.id
left join TaxpayerHistory th on t.id = th.taxpayer_id
left join Bill b on th.id = b.taxpayer_id
left join ParcelHistory ph on b.parcel_id = ph.id
left join PersonalProperty pp on pp.taxpayer_id = t.id
left join (select t.taxpayerID, sum(b.balance) balance from Bill b join TaxpayerHistory t on t.id = b.taxpayer_id where b.balance <> 0 group by t.taxpayerID ) bal on bal.taxpayerId = t.taxpayerId
where 1
and (p.mapNumber like '1205100%' or ph.mapNumber like '1205100%')
and t.name like '%%'
group by t.taxpayerId
order by p.mapNumber
-- Query 1 EXPLAIN
+---+------------+----------+------+---------------------------------------+-----------+--------+-------------------------+-------+--------------------------------------------------------------------+
|id |select_type |table |type |possible_keys |key |key_len | ref |rows | Extra |
+---+------------+----------+------+---------------------------------------+-----------+--------+-------------------------+-------+--------------------------------------------------------------------+
|1 |PRIMARY |t |ALL |IX_TaxpayerID_Name,IX_TaxpayerID_Only | null |null |null |171799 |Using where; Using temporary; Using filesort |
|1 |PRIMARY |p |ref |FK8E0FEC |FK8E0FEC |9 |taxpackage.t.id |1 |Using where |
|1 |PRIMARY |pp |ref |FKE06C6C |FKE06C6C |9 |taxpackage.t.id |1 |null |
|1 |PRIMARY |<derived2>|ref |<auto_key0> |<auto_key0>|258 |taxpackage.t.taxpayerId |10 |null |
|3 |SUBQUERY |ph |range |PRIMARY,map |map |258 |null |17 |Using where; Using index |
|3 |SUBQUERY |b |ref |parcel_id,taxpayer_id,XIC_Parcel_Search|parcel_id |9 |taxpackage.ph.id |1 |Using where |
|3 |SUBQUERY |th |eq_ref|PRIMARY,taxpayer_id,IX_hist_id_vs_id |PRIMARY |8 |taxpackage.b.taxpayer_id |1 |null |
|2 |DERIVED |b |range |taxpayer_id,balance,XIC_Parcel_Search |balance |9 |null |100204 |Using index condition; Using where; Using temporary; Using filesort |
|2 |DERIVED |t |eq_ref|PRIMARY,taxpayerId,account |PRIMARY |8 |taxpackage.b.taxpayer_id |1 |null |
+---+------------+----------+------+---------------------------------------+-----------+--------+-------------------------+-------+--------------------------------------------------------------------+
-- Query 2 EXPLAIN
+---+------------+----------+------+---------------------------------------+-----------+--------+-------------------------+-------+--------------------------------------------------------------------+
|id |select_type |table |type |possible_keys |key |key_len | ref |rows | Extra |
+---+------------+----------+------+---------------------------------------+-----------+--------+-------------------------+-------+--------------------------------------------------------------------+
|1 |PRIMARY |t |ALL |IX_TaxpayerID_Name,IX_TaxpayerID_Only | null |null |null |171799 |Using where; Using temporary; Using filesort |
|1 |PRIMARY |p |ref |FK8E0FEC |FK8E0FEC |9 |taxpackage.t.id |1 |null |
|1 |PRIMARY |th |ref |taxpayer_id,IX_hist_id_vs_id |taxpayer_id|9 |taxpackage.t.id |7 |Using index |
|1 |PRIMARY |b |ref |taxpayer_id,XIC_Parcel_Search |taxpayer_id|9 |taxpackage.th.id |1 |null |
|3 |PRIMARY |ph |eq_ref|PRIMARY |PRIMARY |8 |taxpackage.b.parcel_id |1 |Using where |
|3 |PRIMARY |pp |ref |FK8E0FEC |FK8E0FEC |9 |taxpackage.t.id |1 |null |
|3 |PRIMARY |<derived2>|ref |<auto_kev0> |<auto_kev0>|258 |taxpackage.t.taxpayerId |10 |null |
|2 |DERIVED |b |range |taxpayer_id,balance,XIC_Parcel_Search |balance |9 |null |97322 |Using index condition; Using where; Using temporary; Using filesort |
|2 |DERIVED |t |eq_ref|PRIMARY,taxpayerId,account |PRIMARY |8 |taxpackage.b.taxpayer_id |1 |null |
+---+------------+----------+------+---------------------------------------+-----------+--------+-------------------------+-------+--------------------------------------------------------------------+
这没有任何意义。EXPLAIN结果之间几乎没有差异。它们用同样的方式搜索几乎相同数量的行。在查询2中如果我换出
ph.mapNumber like '1205100%'
并替换为
t.id in ((select th.taxpayer_id from Bill b join TaxpayerHistory th on th.id = b.taxpayer_id join ParcelHistory ph on ph.id = b.parcel_id where ph.mapNumber like '1205100%')))
查询1中的，则持续时间降为1.328秒。尽管这样做会导致搜索的行数从97322增加到100204。
是否有一些已知的规则我遗漏了，或者是否有一个最佳实践方法，我没有看到它正在发生?如果有人能解释一下，我将非常感激。
一些观察:
or t.id in ((select -- IN() and IN(SELECT.. will kill performance
and t.name like '%%' -- MySQL cannot use an index on LIKE '%...'
group
by t.taxpayerId
-- in the absence of any aggregating functions, a GROUP BY clause is never appropriate
此外，为了有机会分析查询，始终限定所有列名，并为所有相关表提供SHOW CREATE TABLE语句。