为什么在这种情况下MySQL不能使用索引?

1 - PRIMARY used in a secondary index, e.g. secondary index on (PRIMARY,column1)

1 -主索引用于辅助索引，例如辅助索引on(主索引，列1)

2 - I'm aware mysql cannot continue using the rest of an index as soon as one part was used for a range scan, however: IN (...,...,...) is not considered a range, is it? Yes, it is a range, but I've read on mysqlperformanceblog.com that IN behaves differently than BETWEEN according to the use of index.

2 -我知道mysql不能在使用一部分进行范围扫描后继续使用其他索引，但是:IN(…，…，…)不被认为是范围，是吗?是的，这是一个范围，但是我在mysqlperformanceblog.com上读到过，根据索引的使用，IN的行为与BETWEEN不同。

Could anyone confirm those two points? Or tell me why this is not possible? Or how it could be possible?

谁能证实这两点吗?或者告诉我为什么这是不可能的?或者这怎么可能呢?

UPDATE:

更新:

Links:
http://www.mysqlperformanceblog.com/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/
http://www.mysqlperformanceblog.com/2006/08/14/mysql-followup-on-union-for-query-optimization-query-profiling/comment-page-1/#comment-952521

链接:http://www.mysqlperformanceblog.com/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/ http://www.mysqlperformanceblog.com/2006/08/14/mysql-followup-on-union-for-query-optimization-query-profiling/comment-page-1/评论- 952521

UPDATE 2: example of nested SELECT:

更新2:嵌套选择示例:

SELECT * FROM user_d1 uo 
WHERE EXISTS (
    SELECT 1 FROM `user_d1` ui
    WHERE ui.birthdate BETWEEN '1990-05-04' AND '1991-05-04'
    AND ui.id=uo.id
)    
ORDER BY uo.timestamp_lastonline DESC
LIMIT 20

So, the outer SELECT uses timestamp_lastonline for sorting, the inner either PK to connect with the outer or birthdate for filtering.

因此，外部选择使用timestamp_lastonline进行排序，内部的PK与外部或生日连接进行过滤。

What other options rather than this query are there if MySQL cannot use index on a range scan and for sorting?

如果MySQL不能在范围扫描和排序中使用索引，还有什么选项可以替代这个查询呢?

3 个解决方案

#1

The column(s) of the primary key can certainly be used in a secondary index, but it's not often worthwhile. The primary key guarantees uniqueness, so any columns listed after it cannot be used for range lookups. The only time it will help is when a query can use the index alone

主键的列当然可以用于辅助索引中，但通常不值得使用。主键保证惟一性，因此在它之后列出的任何列都不能用于范围查找。唯一的帮助是当查询可以单独使用索引时。

As for your nested select, the extra complication should not beat the simplest query:

对于嵌套选择，额外的复杂性不应该超过最简单的查询:

SELECT * FROM user_d1 uo 
WHERE uo.birthdate BETWEEN '1990-05-04' AND '1991-05-04'
ORDER BY uo.timestamp_lastonline DESC
LIMIT 20

MySQL will choose between a birthdate index or a timestamp_lastonline index based on which it feels will have the best chance of scanning fewer rows. In either case, the column should be the first one in the index. The birthdate index will also carry a sorting penalty, but might be worthwhile if a large number of recent users will have birth dates outside of that range.

MySQL将在出生日期索引和timestamp_lastonline索引之间进行选择，基于这些索引，它认为最有可能扫描更少的行。无论哪种情况，列都应该是索引中的第一个。出生日期索引也将包含排序惩罚，但如果最近大量用户的出生日期超出这个范围，那么可能是值得的。

If you wish to control the order, or potentially improve performance, a (timestamp_lastonline, birthdate) or (birthdate, timestamp_lastonline) index might help. If it doesn't, and you really need to select based on the birthdate first, then you should select from the inner query instead of filtering on it:

如果您希望控制订单，或者可能提高性能，可以使用一个(timestamp_lastonline、birthdate)或(birthdate、timestamp_lastonline)索引。如果没有，你真的需要先根据出生日期进行选择，那么你应该从内部查询中进行选择，而不是对其进行过滤:

SELECT * FROM (
    SELECT * FROM user_d1 ui
    WHERE ui.birthdate BETWEEN '1990-05-04' AND '1991-05-04'
) as uo
ORDER BY uo.timestamp_lastonline DESC
LIMIT 20

Even then, MySQL's optimizer might choose to rewrite your query if it finds a timestamp_lastonline index but no birthdate index.

即便如此，如果MySQL的优化器找到一个timestamp_lastonline索引而没有生日索引，那么它可能会选择重写查询。

And yes, IN (..., ..., ...) behaves differently than BETWEEN. Only the latter can effectively use a range scan over an index; the former would look up each item individually.

是的,在(…,……与两者之间的行为不同。只有后者才能有效地对索引进行范围扫描;前者会分别查找每个条目。

#2

2.IN will obviously differ from BETWEEN. If you have an index on that column, BETWEEN will need to get the starting point and it's all done. If you have IN, it will look for a matching value in the index value by value thus it will look for the values as many times as there are values compared to BETWEEN's one time look.

2。IN will显然不同于BETWEEN。如果你在那一列上有一个索引，BETWEEN将需要得到起始点并且它已经完成。如果你有，它会在索引值中按值查找匹配值，因此它会查找值的次数和它们之间的一次查找次数一样多。

#3

yes @Andrius_Naruševičius is right the IN statement is merely shorthand for EQUALS OR EQUALS OR EQUALS has no inherent order whatsoever where as BETWEEN is a comparison operator with an implicit greater than or less than and therefore absolutely loves indexes

是的@Andrius_Naruševičius是正确的声明只是简称= =或=之间已经没有任何固有的秩序,是一个比较运算符,一个隐式大于或小于,因此绝对爱索引
I honestly have no idea what you are talking about, but it does seem you are asking a good question I just have no notion what it is :-). Are you saying that a primary key cannot contain a second index? because it absolutely can. The primary key never needs to be indexed because it is ALWAYS indexed automatically, so if you are getting an error/warn (I assume you are?) about supplementary indices then it's not the second, third index causing it it's the PRIMARY KEY not needing it, and you mentioning that probably is the error. Having said that I have no idea what question you asked - it's my answer to my best guess as to your actual question.

老实说，我不知道你在说什么，但看起来你问了一个好问题，我只是不知道那是什么。您是说主键不能包含第二个索引吗?因为它绝对可以。主键不需要索引,因为它总是自动索引,所以,如果你得到一个错误/警告(我猜你是谁?)补充指标就没有第二个,第三个指数造成它的主键不需要它,而你提到这可能是错误的。我说过我不知道你问了什么问题——这是我对你的实际问题的最好猜测。

#1