我的慢速查询日志充满了以下查询时间和已检查行的查询：
# Query_time: 26.370100 Lock_time: 0.000213 Rows_sent: 0 Rows_examined: 30976475
如果我将日志中的确切查询复制并粘贴到phpmyadmin中并运行它，结果将立即出现，即使尝试对该查询执行EXPLAIN也不会发现索引中存在缺陷或结构不良。
据我所知，由于某种原因，一小部分查询无法使用索引，并且在测试期间尝试重现该事件几乎是不可能的。
在大多数情况下，我应如何防止偶尔出现的缓慢查询？
-编辑1-
我的创建表是：
CREATE TABLE msgs (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
sender text NOT NULL,
receiver text NOT NULL,
cont blob NOT NULL,
img text NOT NULL,
orient text NOT NULL,
d_t datetime NOT NULL,
convo text NOT NULL,
u_code text NOT NULL,
viewed datetime NOT NULL,
stat int(11) NOT NULL,
device text NOT NULL,
addr text NOT NULL,
PRIMARY KEY (id),
KEY msg_u_code (`u_code`(24)),
KEY receiver (`receiver`(24)),
KEY sender (`sender`(24)),
KEY img (`img`(28)),
KEY convo (`convo`(49))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE usrs (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
usr_name text NOT NULL,
img text NOT NULL,
orient text NOT NULL,
`password` text NOT NULL,
u_code text NOT NULL,
d_t datetime NOT NULL,
stat int(11) NOT NULL,
device text NOT NULL,
addr text NOT NULL,
PRIMARY KEY (id),
KEY img (`img`(28)),
KEY usr_code (`u_code`(24))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我的慢查询日志条目是：
# Time: 171115 6:26:37
# User@Host: xxx[xxx] @ localhost []
# Thread_id: 25524888 Schema: xxx QC_hit: No
# Query_time: 32.423430 Lock_time: 0.000425 Rows_sent: 1 Rows_examined: 30998008
# Rows_affected: 0
use xxx;
SET timestamp=1510723597;
select msg_cont, msg_u_code, msg_d_t, msg_viewed, usr_u_code, usr_name from
(select
msgs.id as msg_id,
msgs.cont as msg_cont,
msgs.u_code as msg_u_code,
msgs.d_t as msg_d_t,
msgs.viewed as msg_viewed,
usrs.u_code as usr_u_code,
usrs.usr_name as usr_name
from msgs
left join usrs on msgs.sender = usrs.u_code
where msgs.convo = 'aaaaaaaaaabfbaghdgcigfid_aaaaaaaaaabeiaccjfhjfach'
and (msgs.sender = 'aaaaaaaaaabfbaghdgcigfid'
or msgs.receiver = 'aaaaaaaaaabfbaghdgcigfid'
)
and msgs.stat = '1'
and usrs.stat = '1'
and usrs.u_code not in('aaaaaaaaaabfaagfbgggiejh',
'aaaaaaaaaabfabgbjdfjigbd',
...... !!!!![here go 400 more usr_u_codes]!!!!!
)
and msgs.id > 30997997
) a order by msg_id asc;
注意，该查询平均应在not in函数中包含400个元素。
-编辑2-
最佳答案
您可能已打开“查询缓存”。它捕获查询及其结果集。当您再次运行完全相同的查询时，它只回显已保存的结果集，而不是重新评估它。
可以通过在语句中仅添加一个空格或说SELECT SQL_NO_CACHE ...来避免QC。
为了进一步讨论为什么查询需要查看3100万行，我们来看一下查询和SHOW CREATE TABLE。
后查询
我想首先关注ON msgs.sender = usrs.u_code和“前缀”索引。
根据样本值，似乎sender，u_code和其他几列可能总是在很小的长度下？如果这是真的，那么
从TEXT更改为VARCHAR(nn)，其中nn是一些实际限制；
删除前缀（例如：KEY sender (sender(24))-> KEY (sender)。
这些更改将使JOIN更加有效，从而提高性能。如果这还不够，请返回以获取更多建议。