为什么排序时不使用索引？
我有这个表结构：
CREATE TABLE users
(
uid bigint NOT NULL,
first_name character varying,
last_name character varying,
email character varying,
login_count integer,
CONSTRAINT users_pkey PRIMARY KEY (uid)
)
使用这个索引：
CREATE INDEX users__login_count
ON users
USING btree
(login_count DESC NULLS LAST);
login_count列可能由NULL值组成，我需要选择按login_count降序排列的所有用户，并且需要NULL位于末尾。
不幸的是这个查询：
SELECT * FROM users ORDER BY login_count DESC LIMIT 30;
不会使用索引，所以空值在开始，为什么？
I have this table structure:
CREATE TABLE users
(
uid bigint NOT NULL,
first_name character varying,
last_name character varying,
email character varying,
login_count integer,
CONSTRAINT users_pkey PRIMARY KEY (uid)
)
with this index:
CREATE INDEX users__login_count
ON users
USING btree
(login_count DESC NULLS LAST);
The login_count column may consists of NULL values and i need to select all users ordered descending by login_count and need NULLs to be at the end.
Unfortunately this query:
SELECT * FROM users ORDER BY login_count DESC LIMIT 30;
won't use the index, so NULLs are at the beggining, why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
索引的定义方式不会改变查询的含义。为了使用索引，索引的顺序必须与查询的顺序相匹配。
话虽如此，MySQL 似乎不支持
nulls last。尝试：How an index is defined does not change the meaning of a query. In order for the index to be used, the ordering of the index has to match the ordering of the query.
Having said that, it doesn't appear that MySQL supports
nulls last. Try:您的查询实际上是
ORDER BY login_count DESCNULLS FIRSTLIMIT 30，如下所述此处。在此页面上，它描述了索引如何满足排序：所以你的索引是相同的 - 它可以满足
ASC NULLS FIRST和DESC NULLS LAST，但你的查询是DESC NULLS首先。Your query is effectively
ORDER BY login_count DESCNULLS FIRSTLIMIT 30as explained here. On this page it describes how an index can satisfy an ordering:So your index is the same - it can satisfy
ASC NULLS FIRSTandDESC NULLS LAST, but your query isDESC NULLS FIRST.