mysql - 优化涉及百万行的mysql查询

Question

在一个项目中，我有一个包含两个大表的数据库，“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 |
+----+-------------+-------+--------+-------------------------------------------+---------------------+---------+---------------------------+-------+-------------+

但是执行时间并没有太大变化...

有什么想法吗？

Answer 1

正如 Strawberry 所指出的，通过在 NOT NULL 的 where 子句中使用“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 )

这样查询可以直接从索引中获取所有结果，而不必转到原始数据页面来限定其他字段。