有关查询的问题，例如 select max(B) from t where A=123
我想知道MySQL会如何处理该语句？如果A列、B列都被索引。
我想有两种方法可以做到。
a.选择 t 中 A==123 的所有记录作为临时结果 b.从临时结果中找到最大 B 并返回。 时间复杂度可能是 O(lgN + m)。
一步获得记录，即T(N) = O(lgN)？
提前致谢。
I wonder how the MySQL will deal with the statement? If both Column A, B are indexed.
I suppose there will be two ways to do.
a. Select all records from t that A==123 as a temp result
b. find the max B one from the temp result and return.
The time complexity might be O(lgN + m).Get the record in one step, in other word, T(N) = O(lgN)?
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我的直觉告诉我，除非 B 可为空并且 B 填充稀疏（非常稀疏，低至 1% 或更低，并且编号小于每个索引键 A 的平均值数的 10%），这样检查 B按降序排列然后检查这些记录上的 A=123 是值得的，对于此查询，MySql 不会在 B 上有索引栏。
它很可能只使用 A（如果 A 有足够的选择性），从表中检索记录，按 B 降序排序并返回结果。
这意味着你的第一种情况，O(N + m)。 N 与表大小成正比，这也是统计上平均有多少条记录满足 A={any x}
My instinct would tell me that unless B is nullable and B is sparsely populated (really sparse, as low as 1% or lower as well as numbering less than 10% of the average number of values per index key A), such that inspecting B in descending order then checking for A=123 on those records is worthwhile, MySql won't have a bar of the index on B for this query.
More than likely it will just use A (if A is selective enough), retrieve from the table the records, sort by B descending and return the result.
This would mean your 1st case, O(N + m). N is directly proportional to table size, which is also statistically how many records on average would satisfy A={any x}