我正在尝试优化查询,但是当我在子查询中使用变量时,未使用索引。
set @dh = '2018-01-17 23:59:59'
...
inner join cons c1 on c1.idcons = xx.maxcons
left join conslog clog on clog.idconslog = (select max(clt.idconslog)
from conslog clt
where clt.idcons = c1.idcons
and clt.date_create <= @dh)
...
我得到
explain
+----+-------------+-------+------+---------------+-----+---------+-----+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----+---------+-----+----------+----------------------------------------------------+
| 1 | PRIMARY | clog | ALL | | | | | 40978775 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+-----+---------+-----+----------+----------------------------------------------------+
如果不是使用变量,而是运行查询,将其替换为字符串,例如:
...
inner join cons c1 on c1.idcons = xx.maxcons
left join conslog clog on clog.idconslog = (select max(clt.idconslog)
from conslog clt
where clt.idcons = c1.idcons
and clt.date_create <= '2018-01-17 23:59:59')
...
explain
给我:+----+-------------+-------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | clog | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------+------+-------------+
我在SO上检查了其他答案,尝试将变量convert_tz转换为UTC,使用timestamp(date,time),date_format创建它。
我的想法不多了。
date_create的类型为:
date_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
为什么会这样?自从我使用的是PK的idcons以来,为什么必须检查这么多行?
谢谢您的帮助
最佳答案
取而代之的是,为了简化仅获取所需记录的过程。我认为clog.idcons = c1.idcons可能有所帮助。
我认为这也可能有助于将嵌套查询更改为使用clog,因为那是与嵌套=相关联的查询。
left join conslog clog on clog.idcons=c1.idcons and clog.idconsumolog = (select max(clt.idconslog)
from conslog clt
where clog.idcons = clt.idcons
and clt.date_create <= @dh)