选择查询和 'order by' 子句的 MySQL 问题

Question

我们遇到了一个针对 MySQL 数据库 (InnoDB) 的 SELECT 查询的奇怪问题。

以下查询错误地返回了 1 条匹配记录:

select `ID` 
from `AccessTables` 
where `numTableID` = 14 
    AND `numUserCatID` IN (7,253) 
    AND (`numUpdateCat` = 2 OR `numUpdateItems` = 2) 
order by `ID` asc 
limit 1

而以下查询正确返回没有匹配的记录:

select `ID` 
from `AccessTables` 
where `numTableID` = 14 
    AND `numUserCatID` IN (7,253) 
    AND (`numUpdateCat` = 2 OR `numUpdateItems` = 2) 
limit 1

如您所见，这些查询之间的唯一区别是“order by”子句。

查询中请求的 ID 列是表的自动生成的主键列。

第一个查询中返回的记录是如果“or”子句周围没有方括号就会找到的记录。但是查询的那部分周围有括号，所以我不明白为什么要在这里返回这条记录。然后仅当查询中有“order by”子句时。

正在使用的MySQL版本是:MySQL Server: 5.5.32-MariaDB-log

这里的任何人都可以阐明这个问题吗？提前致谢。

(编辑:省略括号确实会返回一行，但这与第一个查询返回的行不同)

 insert  into `AccessTables`(`ID`,`numUserCatID`,`numTableID`,`numUpdateCat`,`numPublishCat`,`numUpdateItems`,`dateInsert`,`dateUpdate`,`numInsertAuthorID`,`numUpdateAuthorID`,`numViewItems`) values (71,15,14,0,0,2,'2008-03-13 23:38:47','2013-04-04 09:34:36',0,513,2);

(编辑编号 2:没有 MariaDB，但是...... http://sqlfiddle.com/#!2/2a922/8 )

编辑编号。 3，针对真正的 MariaDB 事物运行这些查询:

查询 1:

EXPLAIN EXTENDED SELECT `ID` 
FROM `AccessTables` 
WHERE `numTableID` = 14 
    AND `numUserCatID` IN (7,253) 
    AND (`numUpdateCat` = 2 OR `numUpdateItems` = 2) 
ORDER BY `ID` ASC 
LIMIT 1;

输出:

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"<br />
"1" "SIMPLE"    "AccessTables"  "range" "numUserCatID,numTableID,numUpdateCat,numUpdateItems"   "numTableID"    "8" \N  "136"   "11.03" "Using where"

查询2:

EXPLAIN EXTENDED SELECT `ID` 
FROM `AccessTables` 
WHERE `numTableID` = 14 
    AND `numUserCatID` IN (7,253) 
    AND (`numUpdateCat` = 2 OR `numUpdateItems` = 2) 
LIMIT 1;

输出:

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"<br />
"1" "SIMPLE"    "AccessTables"  "range" "numUserCatID,numTableID,numUpdateCat,numUpdateItems"   "numUserCatID"  "8" \N  "20"    "75.00" "Using index condition; Using where"

查询3:

EXPLAIN EXTENDED SELECT `ID` 
FROM `AccessTables` 
WHERE `numTableID` = 14 
    AND (numUserCatID = 7 OR numUserCatID = 253) 
    AND (`numUpdateCat` = 2 OR `numUpdateItems` = 2) 
ORDER BY `ID` ASC 
LIMIT 1;

输出:

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"<br />
"1" "SIMPLE"    "AccessTables"  "range" "numUserCatID,numTableID,numUpdateCat,numUpdateItems"   "numTableID"    "8" \N  "136"   "11.03" "Using where"

编辑号码。 4:删除“limit 1”与删除“order by”的结果相同:未找到任何行。

查询 4:

EXPLAIN EXTENDED SELECT `ID` 
FROM `AccessTables` 
WHERE `numTableID` = 14 
    AND `numUserCatID` IN (7,253) 
    AND (`numUpdateCat` = 2 OR `numUpdateItems` = 2) 
ORDER BY `ID` ASC;

输出:

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"<br />
"1" "SIMPLE"    "AccessTables"  "range" "numUserCatID,numTableID,numUpdateCat,numUpdateItems"   "numUserCatID"  "8" \N  "20"    "75.00" "Using index condition; Using where; Using filesort"

因此，返回正确结果(找到 0 条记录)的查询似乎使用 numUserCatID 上的索引，而返回错误结果(找到 1 条记录)的查询似乎使用 numTableID 上的索引。

奇怪...!

编辑号码。 5:
按另一列排序，例如。 dateInsert(这是一个日期/时间戳，表示记录被插入表的时刻)，也会更改查询结果。
然后再次没有返回记录，使用的索引再次是 numUserCatID 上的索引。

我们使用“按 ID asc 排序”是因为我们假设 ID 始终代表记录插入数据库的顺序。
但 dateInsert 在我们的案例中的作用基本相同。

如果使用常规键列而不是主键进行排序，大型数据库中是否会有性能损失？

Answer 1

如果这真的发生了，那就是一个错误。“真的”是指这些是您发送到数据库服务器的确切查询，并且基础表没有同时更新时间。

这里有一个类似(但不完全相同)的问题: MDEV-2662

请将问题报告给 MariaDB 团队。

---

要解决眼前的问题，请尝试重写查询，例如不使用 IN，替换；

AND numUserCatID IN (7,253) 

与:

AND (numUserCatID = 7 OR numUserCatID = 253)

并检查您是否得到相同的错误结果。