我有两个几乎相同的查询。为什么一个人跑得这么快？

Question

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                                                                |
+---+------------+----------+------+---------------------------------------+-----------+--------+-------------------------+-------+--------------------------------------------------------------------+    

这没有任何意义。解释结果之间实际上没有区别。它们以相同的方式搜索几乎相同数量的行。在查询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。

有没有我遗漏的一些已知规则，或者有没有我看不到的最佳实践方法？如果有人能阐明这一点，我将不胜感激。

Answer 1

一些观察结果：

    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语句。