我有,在一个项目中,有两个大表的数据库,"terminosnoticia"有4亿行,"noticia"有300万行。我有一个查询,我想让它更轻(它花费从10到400秒):
SELECT noticia_id, termino_id
FROM noticia
LEFT JOIN terminosnoticia on terminosnoticia.noticia_id=noticia.id AND termino_id IN (7818,12345)
WHERE noticia.fecha BETWEEN '2016-09-16 00:00' AND '2016-09-16 10:00'
AND noticia_id is not null AND termino_id is not null;`
我必须探索的唯一可行的解决方案是将数据库非规范化,以便在大表中包含'fecha'字段,但是,这将增加索引大小。
解释计划:
+----+-------------+-----------------+--------+-----------------------+------------+---------+-----------------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+--------+-----------------------+------------+---------+-----------------------------------------+-------+-------------+
| 1 | SIMPLE | terminosnoticia | ref | noticia_id,termino_id | termino_id | 4 | const | 58480 | Using where |
| 1 | SIMPLE | noticia | eq_ref | PRIMARY,fecha | PRIMARY | 4 | db_resumenes.terminosnoticia.noticia_id | 1 | Using where |
+----+-------------+-----------------+--------+-----------------------+------------+---------+-----------------------------------------+-------+-------------+
按照建议修改查询并创建索引,现在的解释计划是:
+----+-------------+-------+--------+-------------------------------------------+---------------------+---------+---------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------+---------------------+---------+---------------------------+-------+-------------+
| 1 | SIMPLE | T | ref | noticia_id,termino_id,terminosnoticia_cpx | terminosnoticia_cpx | 4 | const | 60600 | Using index |
| 1 | SIMPLE | N | eq_ref | PRIMARY,fecha | PRIMARY | 4 | db_resumenes.T.noticia_id | 1 | Using where |
+----+-------------+-------+--------+-------------------------------------------+---------------------+---------+---------------------------+-------+-------------+
但是执行时间变化不大…
任何想法?
正如Strawberry指出的那样,通过在where子句中为NOT NULL添加"AND"与普通的INNER JOIN相同,可以简化为。
SELECT
N.id as noticia_id,
T.termino_id
FROM
noticia N USING INDEX (fecha)
JOIN terminosnoticia T
on N.id = T.noticia_id
AND T.termino_id IN (7818,12345)
WHERE
N.fecha BETWEEN '2016-09-16 00:00' AND '2016-09-16 10:00'
现在,说了这么多,并应用别名,我建议以下覆盖索引为
table index
Noticia ( fecha, id )
terminosnoticia ( noticia_id, termino_id )
这样,查询就可以直接从索引中获得所有结果,而不必去原始数据页来限定其他字段。
假设noticia_id
是noticia
的主键,我将添加以下索引:
create index noticia_fecha_idx on noticia(fecha);
create index terminosnoticia_id_noticia_idx on terminosnoticia(noticia_id);
再试一次查询。
一定要包含查询的当前执行计划。它可能会帮助你解决这个问题。
试试这个:
SELECT tbl1.noticia_id, tbl1.termino_id FROM
( SELECT FROM terminosnoticia WHERE
terminosnoticia.termino_id IN (7818,12345)
AND terminosnoticia.noticia_id is not null
) tbl1 INNER JOIN
( SELECT id FROM noticia
WHERE noticia.fecha
BETWEEN '2016-09-16 00:00' AND '2016-09-16 10:00'
) tbl2 ON tbl1.id=tbl2.noticia.id
我们假设noticia_id
和termino_id
是terminosnoticia
表中的列。(如果所有列引用都是限定的,并带有表名或短表别名,则无需猜测。)
为什么这是一个外连接?WHERE子句中的谓词将排除terminosnoticia
列中具有NULL值的行。这将否定连接的"外部性"。
如果我们将其写为内部连接,那么WHERE子句中的谓词是多余的。我们已经知道noticia_id
不会是NULL(如果它满足ON子句中的相等谓词)。termino_id
也是一样,如果它等于in列表中的值,则不会为NULL。
我相信这个查询将返回一个等价的结果:
SELECT t.noticia_id
, t.termino_id
FROM noticia n
JOIN terminosnoticia t
ON t.noticia_id = n.id
AND t.termino_id IN (7818,12345)
WHERE n.fecha BETWEEN '2016-09-16 00:00' AND '2016-09-16 10:00'
现在剩下的是弄清楚是否存在隐式数据类型转换。
我们没有看到termino_id
的数据类型。我们不知道它是否被定义为数值。如果不是,这是一个坏消息,因为MySQL将不得不对表中的每一行执行转换为数字,因此它可以与数字字面量进行比较。
我们没有看到noticia_id
的数据类型,也没有看到它与noticia
表中的id
列的数据类型是否匹配。
我们也没有看到fecha
的数据类型。根据between谓词中的字符串字面值,它看起来很可能是DATETIME或TIMESTAMP。但这只是猜测。我们不知道,因为我们没有表定义。
一旦我们验证了没有任何隐式数据类型转换会咬我们…
对于具有内连接的查询(如上所述),合理性能的最佳选择可能是MySQL有效地使用覆盖索引。(覆盖索引的允许MySQL直接从索引块中满足查询,而不需要在底层表中查找页面。)
正如DRApp的答案已经指出的,对于这个特定的查询,覆盖索引的最佳候选将是:
... ON noticia (fecha, id)
... ON terminosnoticia (noticia_id, termino_id)
如果一个索引的前导列的顺序相同,那么这个索引也是合适的,并且会使这些索引变得冗余。
添加这些索引将使其他索引冗余。
第一个索引对于... ON noticia (fecha)
来说是多余的。假设索引没有强制UNIQUE约束,它可以被删除。任何有效使用该索引的查询都可以使用新索引,因为fecha
是新索引中的首列。
... ON terminosnoticia (noticia_id)
也是冗余的。同样,假设它不是唯一索引,强制unique约束,该索引也可以被删除。