MySQL 日志查询不使用索引和 UNION 误报或错误？

Question

使用此参数激活慢查询日志:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 10
log-queries-not-using-indexes

现在看来 MySQL 只记录没有索引的查询

这是一个日志样本

# Time: 130709 15:33:54
# User@Host: xxxxxx[yyyyyy] @ localhost []
# Query_time: 0.000467  Lock_time: 0.000105 Rows_sent: 1  Rows_examined: 0
SET timestamp=1373376834;
SELECT count(`id`) as `totale` FROM ( SELECT `id` FROM `gg_evento` WHERE `team_home` = 51620 AND `status_home` = 3 AND `status_guest` = 3 UNION ALL SELECT `id` FROM `gg_evento` WHERE `team_guest` = 51620 AND `status_home` = 3 AND `status_guest` = 3) AS sq2 WHERE 1 LIMIT 1;

但是，此查询已被优化。

mysql> explain SELECT count(`id`) as `totale` FROM ( SELECT `id` FROM `gg_evento` WHERE `team_home` = 51620 AND `status_home` = 3 AND `status_guest` = 3 UNION ALL SELECT `id` FROM `gg_evento` WHERE `team_guest` = 51620 AND `status_home` = 3 AND `status_guest` = 3) AS sq2 WHERE 1 LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: gg_evento
         type: ref
possible_keys: fk_evento_team_home,IDX_PARTITE_NON_TERMINATE,IDX_CARICA_SFIDANTI
          key: fk_evento_team_home
      key_len: 4
          ref: 
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: gg_evento
         type: ref
possible_keys: IDX_TOTALE_GIOCATE_GUEST
          key: IDX_TOTALE_GIOCATE_GUEST
      key_len: 13
          ref: 
         rows: 1
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: 
4 rows in set (0.00 sec)

我在 MySQL 文档中看到这个警告:

This option does not necessarily mean that no index is used

但是……

1. 我不明白记录的查询是否正确，或者我有可以修复的优化问题
2. 如果这个查询是误报，有没有办法避免 mysql 记录它？

似乎 MySQL 将所有包含 UNION 的查询记录为无索引查询，我搜索了它但没有找到任何东西。

有什么想法吗？提前致谢米歇尔

Answer 1

你可以试试 min_examined_row_limit ( https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_min_examined_row_limit )

正如您的示例所示“Rows_examined: 0”，设置 a/m 变量可以抑制日志记录。

set global  min_examined_row_limit = 2;