MySQL EXPLAIN實戰優化案例：四表關聯查詢從ALL到ref的躍遷

原始慢查詢

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, ..., 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;

優化前EXPLAIN（節選）

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | tt    | ALL  | NULL          | NULL | NULL    | NULL | 3872   | Using where |
|  1 | SIMPLE      | et    | ALL  | NULL          | NULL | NULL    | NULL |  157   |             |
|  1 | SIMPLE      | et_1  | ALL  | NULL          | NULL | NULL    | NULL |  157   |             |
|  1 | SIMPLE      | do    | ALL  | NULL          | NULL | NULL    | NULL |  322   |             |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

四表全ALL，總掃描行數 ≈ 3872×157×157×322 ≈ 300億！

優化步驟

1. 為tt.SubmitTime、tt.ActualPC、tt.AssignedPC、tt.ClientID建立索引
2. 為et.EMPLOYID、et_1.EMPLOYID、do.CUSTNMBR建立索引
3. 執行ANALYZE TABLE更新統計信息

優化後EXPLAIN

+----+-------------+-------+--------+-------------------+----------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys     | key      | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+-------------------+----------+---------+------------------+------+-------------+
|  1 | SIMPLE      | tt    | ref    | idx_submit        | idx_submit | 5     | const            |   12 | Using where |
|  1 | SIMPLE      | et    | eq_ref | PRIMARY           | PRIMARY  | 4       | tt.ActualPC      |    1 |             |
|  1 | SIMPLE      | et_1  | eq_ref | PRIMARY           | PRIMARY  | 4       | tt.AssignedPC    |    1 |             |
|  1 | SIMPLE      | do    | eq_ref | PRIMARY           | PRIMARY  | 4       | tt.ClientID      |    1 |             |
+----+-------------+-------+--------+-------------------+----------+---------+------------------+------+-------------+

驅動表tt走ref掃描12行，其餘三表eq_ref各1行，總檢查行數 ≈ 12。性能提升超25億倍！