MySQL Explain 的行數與慢查詢日誌不同

我在慢查詢日誌中有這個條目：

# User@Host: user[host] @  [ip]
# Thread_id: 1514428  Schema: db  Last_errno: 0  Killed: 0
# Query_time: 2.795454  Lock_time: 0.000116  Rows_sent: 15  Rows_examined: 65207  Rows_affected: 0  Rows_read: 65207
# Bytsent: 26618
SET timestamp=1407511874;

select off.*,translated_title,translated_description 
from ephpb2b_products off  USE INDEX(id_viewed)  
 INNER JOIN ephpb2b_members mem 
   ON  off.uid = mem.id 
 Left Join ephpb2b_product_language_new pol 
   ON  off.id = pol.offer_id                                         
   and pol.language='en'
where off.approved=1 
order by off.viewed  
LIMIT 15; 

當我解釋這個查詢時，它絕對沒問題。

mysql> explain select off.*,translated_title,translated_description from ephpb2b_products off  USE INDEX(id_viewed)  INNER JOIN ephpb2b_members mem ON off.uid = mem.id Left Join ephpb2b_product_language_new pol ON off.id = pol.offer_id and pol.language='en' where off.approved=1 order by off.viewed  LIMIT 15;

+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
| id | select_type | table | type   | possible_keys           | key         | key_len | ref                       | rows | Extra       |
+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | off   | index  | NULL                    | id_viewed   | 4       | NULL                      |    3 | Using where |
|  1 | SIMPLE      | mem   | eq_ref | PRIMARY                 | PRIMARY     | 4       | db.off.uid |    1 | Using index |
|  1 | SIMPLE      | pol   | ref    | offer_id,id_language | offer_id | 5       | db.off.id  |    4 |             |
+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
3 rows in set (0.17 sec)

如何優化此查詢？為什麼解釋顯示 3 行和慢查詢日誌說它檢查了 65207 行。

為了回答這個問題，你必須了解解釋上的行列是什麼意思，以及基於統計的計算和執行後統計的區別。

當您執行解釋時，行列將告訴您，對於每個表訪問，將使用預期的過濾器檢查多少行。有兩種計算方法：索引潛水（通常應該為您提供準確的結果）或使用每個引擎獨立儲存的近似統計數據- 每個表最多 5.6 個。雖然可以使用第一種方法（單個索引列上的簡單過濾器）時首選方法，但在許多情況下，只能使用近似值 - 否則，查詢優化器將花費與查詢執行本身一樣多的時間。

在任何情況下，這些行都是每個表訪問要讀取（不返回）的計算行。即使它是精確的（並且很多時候有幾個數量級的差異，但對於優化器來說仍然足夠好），它也不能預測整個連接中訪問的實際行數。例如，如果你連接表A（準確讀取X行）和表B（準確讀取Y行），在 orderA -> B中，實際讀取的行數將為：X + # of rows returned by A (<=X) multiplied by Y，因為標準 mysql 僅支持嵌套循環連接。

慢速日誌（如處理程序統計資訊或其他分析機制）會告訴您處理和發送的實際行數，因為這些統計資訊是在執行後收集的，因此是準確的。

關於您的特定情況，EXPLAIN這是罪魁禍首，因為它表明第一次訪問只會掃描 3 行，而實際上它可能正在執行完整索引掃描（因為它僅使用鍵進行排序），後來會成倍增加對於執行的每個連接。不信解釋。您可以使用：

FLUSH STATUS;
-- Execute your query here
SHOW STATUS like 'Hand%';

檢查行操作的實際數量和種類（PK 訪問、引用、索引掃描、表掃描）。我會用它來單獨測試每個表的訪問。

如需更具體的幫助，我們需要每個表的表結構和每個過濾條件的近似選擇性。

+1 到 @junus 以獲取有關EXPLAIN、慢查詢日誌和檢查的行的解釋。

關於***“如何優化查詢”***：

products假設和表之間存在顯式外鍵關係，members它們之間的連接：

ephpb2b_products off  
 INNER JOIN ephpb2b_members mem 
   ON  off.uid = mem.id 

可以轉換為LEFT JOIN. FK 將確保這兩個查詢是 100% 等效的。考慮到這一點，whereandorder子句：

WHERE off.approved=1
ORDER BY off.viewed  
LIMIT 15

只使用基表***（***products _ ：FROM

limit``join

SELECT 
   off.*, 
   pol.translated_title, 
   pol.translated_description 
FROM
   ( SELECT p.*                                 -- first limit
     FROM ephpb2b_products AS p
     WHERE p.approved=1
     ORDER BY p.viewed  
     LIMIT 15  
   ) AS off 
 LEFT JOIN                                      -- then join
   ephpb2b_members AS mem 
     ON  off.uid = mem.id 
 LEFT JOIN 
   ephpb2b_product_language_new AS pol 
     ON  pol.language = 'en'                                         
     AND pol.offer_id = off.id   
ORDER BY 
   off.viewed ;                         -- no LIMIT required here 

使用 on 索引ephpb2b_products (approved, viewed)，子查詢將非常有效。執行計劃的其餘部分無關緊要，因為只涉及 15 行（我假設您在連接列中有索引）。

一個額外的 index onphpb2b_product_language_new (language, offer_id)也可能進一步提高效率（但不要盲目添加這個，先測試。上面的 index 和重寫可能足以大幅提高速度。）

引用自：https://dba.stackexchange.com/questions/73520