mysql 查询不使用联合所有索引(Rows_sent : 115 Rows_examined: 1008)

Question

我正在尝试简单地“合并”12 个表的所有行。所有表加起来有 115 行。但是，如果我运行下面的查询，则在使用“explain”时会得到以下内容，并且在设置为“log-queries-not-using-indexes”时会在 mysql-slow.log 中得到一个条目:

Rows_sent: 115  Rows_examined: 1008

我有点困惑 mysql 如何检查 1008 行，而它所要做的只是将所有行连接在一起(好吧，“联合”)。任何提示或指示将不胜感激。

这是查询:

(SELECT id, var_lng_1, 0 as tbl_col FROM tbl1 )

UNION ALL 

(SELECT id, var_lng_1, 1 as tbl_col FROM tbl2 )

UNION ALL 

(SELECT id, var_lng_1, 2 as tbl_col FROM tbl3 )

UNION ALL 

(SELECT id, var_lng_1, 3 as tbl_col FROM tbl4 )

UNION ALL 

(SELECT id, var_lng_1, 4 as tbl_col FROM tbl5 )

UNION ALL 

(SELECT id, var_lng_1, 5 as tbl_col FROM tbl6 )

UNION ALL 

(SELECT id, var_lng_1, 6 as tbl_col FROM tbl7 )

UNION ALL 

(SELECT id, var_lng_1, 7 as tbl_col FROM tbl8 )

UNION ALL 

(SELECT id, var_lng_1, 8 as tbl_col FROM tbl9 )

UNION ALL 

(SELECT id, var_lng_1, 9 as tbl_col FROM tbl10 )

UNION ALL 

(SELECT id, var_lng_1, 10 as tbl_col FROM tbl11 )

UNION ALL 

(SELECT id, var_lng_1, 11 as tbl_col FROM tbl12 );

非常感谢任何意见

PS:(以防万一这会产生影响)所有 id 都是 Primary、tiny_int(3)、auto_increment 列。我也尝试使用 id 进行相同的查询(即“从...选择 id”，但这没有什么区别:(

完整解释输出:

[id] => 1  
[select_type] => PRIMARY  
[table] => tbl1  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 7  
[Extra] =>   

[id] => 2  
[select_type] => UNION  
[table] => tbl2  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 18  
[Extra] =>   

[id] => 3  
[select_type] => UNION  
[table] => tbl3  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 8  
[Extra] =>   

[id] => 4  
[select_type] => UNION  
[table] => tbl4  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 10  
[Extra] =>   

[id] => 5  
[select_type] => UNION  
[table] => tbl5  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 11  
[Extra] =>   

[id] => 6  
[select_type] => UNION  
[table] => tbl6  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 14  
[Extra] =>   

[id] => 7  
[select_type] => UNION  
[table] => tbl7  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 10  
[Extra] =>   

[id] => 8  
[select_type] => UNION  
[table] => tbl8  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 6  
[Extra] =>   

[id] => 9  
[select_type] => UNION  
[table] => tbl9  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 3  
[Extra] =>   

[id] => 10  
[select_type] => UNION  
[table] => tbl10  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 2  
[Extra] =>   

[id] => 11  
[select_type] => UNION  
[table] => tbl11  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 6  
[Extra] =>   

[id] => 12  
[select_type] => UNION  
[table] => tbl12  
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] => 20  
[Extra] =>   


[id] =>   
[select_type] => UNION RESULT  
[table] => <union1,2,3,4,5,6,7,8,9,10,11,12>   
[type] => ALL  
[possible_keys] =>   
[key] =>   
[key_len] =>   
[ref] =>   
[rows] =>   
[Extra] => 

Answer 1

您没有 WHERE 子句，因此您将返回所有行，并且使用 UNION ALL(而不是 UNION)可以防止检查不同集合中的重复项。这里需要对每个表进行全表扫描才能产生正确的结果。并且您也没有 ORDER BY 子句。该查询没有任何内容可以从索引中受益。使用索引根本没有帮助。