mysql - mysql中的连接表没有正确使用索引？

Question

我有四个表，我正在尝试连接并将结果输出到一个新表。我的代码如下所示:

create table tbl  
select a.dte, a.permno, (ret - rf) f0_xs_ret, (xs_ret - (betav*xs_mkt)) f0_resid, mkt_cap     last_year_mkt_cap, betav beta_value
from a inner join b using (dte)
inner join c on (year(a.dte) = c.yr and a.permno = c.permno)
inner join  d on (a.permno = d.permno and year(a.dte)-1 = year(d.dte));

所有表都有多个索引，对于表a，(dte, permno)标识唯一记录，对于表b， dte id 是唯一记录，对于表c，(yr, permno) id 是唯一记录，对于表d >, (dte, permno) id 一条唯一记录。查询的 select 部分的解释是:

+----+-------------+-------+--------+-------------------+---------+---------+----------    ------------------------+--------+-------------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref                                  | rows   | Extra             |
+----+-------------+-------+--------+-------------------+---------+---------+----------    ------------------------+--------+-------------------+
|  1 | SIMPLE      | d     | ALL    | idx1              | NULL    | NULL    | NULL                                 | 264129 |                   | 
|  1 | SIMPLE      | c     | ref    | idx2              | idx2    | 4       |     achernya.d.permno                |     16 |                   | 
|  1 | SIMPLE      | b     | ALL    | PRIMARY,idx2      | NULL    | NULL    | NULL                                 |  12336 | Using join buffer | 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,idx1,idx2 | PRIMARY | 7       |    achernya.b.dte,achernya.d.permno |      1 | Using where       | 
+----+-------------+-------+--------+-------------------+---------+---------+----------------------------------+--------+-------------------+

为什么mysql要读取这么多行来处理这个东西？如果我正确地阅读了此内容，它必须读取 (264129*16*12336) 行，这应该需要一个月的时间。

有人可以解释一下这里发生了什么吗？

Answer 1

MySQL 必须读取行，因为您使用函数作为连接条件。 dte 上的索引无助于解析查询中的 YEAR(dte)。如果您想加快速度，请将年份放入其自己的列中以在联接中使用，并将索引移动到该列，即使这意味着一些非规范化。

对于索引中您不应用函数的其他列，如果索引不会提供太多好处，或者它们不是索引中最左边的列并且您不使用它们，则可能不会使用它们不要在连接条件中使用该索引的最左边的前缀。

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.)

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html