在mysql中连接表时没有正确使用索引?
问题描述:
我有四个表,我试图加入并输出结果到一个新表。我的代码如下所示:在mysql中连接表时没有正确使用索引?
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)
确定一个唯一记录。该解释从查询的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)
行,这应该需要一个好月份。
可能有人请解释一下这是怎么回事呢?
答
MySQL有,因为你使用的功能为您的加盟条件,读取行。 dte
上的索引无助于在查询中解析YEAR(dte)
。如果你想快速做到这一点,那么把年份放在自己的列中用于连接并将索引移动到该列,即使这意味着一些非规范化。
至于在索引中的其他列,你不适用功能,他们可以不使用,如果该索引将不会提供多少好处,或者他们是不是在索引中最左边的列和你不在连接条件中不使用该索引的最左边的前缀。
有时MySQL不使用索引,即使有索引可用。出现这种情况的一种情况是,优化程序估计使用索引需要MySQL访问表中非常大部分的行。 (在这种情况下,表扫描可能会更快,因为它需要较少的追求。)
哦 - 我看到的。我的理解是,对于第一个表中读取的每一行,它必须读取另一个表中的16 * 12336行。我认为它会简单地沿着第一个表的行,然后对第一行的每一行按顺序读取其他行。是不正确的? – Alex 2012-08-15 03:26:37
你是否确定这是理解行列的方法?我了解了通过允许mysql转到表的子集来缩小要扫描的行数量的索引,但是它仍然必须读取我原始表中每行的整个子集? – Alex 2012-08-15 03:32:55
我读这个网上:“omputing行进行检查是比较复杂的,是采取从各行的估计行数频繁方法联接和繁殖他们......”他接着说,这是不准确等,但他并没有说任何关于添加东西的事情。你有什么资料可以查看吗?如果这只是一个总和然后我的查询将已经 – Alex 2012-08-15 03:39:07