MySQL:了解 WHERE 子句应用模式

Question

我正在阅读 High Performance MySQL .

我想理解:下面的短语

In general, MySQL can apply a WHERE clause in three ways, from best to worst:

1. Apply the conditions to the index lookup operation to eliminate non matching rows. This happens at the storage engine layer.
2. Use a covering index (“Using index” in the Extra column) to avoid row accesses, and filter out non matching rows after retrieving each result from the index. This happens at the server layer, but it doesn’t require reading rows from the table.
3. Retrieve rows from the table, then filter non matching rows (“Using where” in the Extra column). This happens at the server layer and requires the server to read rows from the table before it can filter them.

我的理解:假设我有

table
|-id     <- PK
|-field1 <- 1st B-Tree index
|-field2 <- 2nd B-Tree index
|-field3 <- not indexed

• 第一种情况:SELECT field1 FROM table WHERE id IN (@id1, @id2, ...)
• 第三种情况:SELECT * FROM table WHERE field3 = 'blabla'

问题:我不明白我们从哪里得到第二个案例。

*问题:下一个查询属于哪些情况？

1. SELECT field1, field2 FROM table WHERE field1 = 'blabla'
2. SELECT field1 FROM table WHERE field1 = 'blabla' AND field2 = 'blabla'
3. 我们从哪里得到第二个案例？

Answer 1

第一种情况:PK 查找。不需要解释

您的问题:

1:索引查找。 Mysql 将评估索引(在它看来)是否比全表扫描更快，并且会这样做。如果索引中的值不同(即使没有由唯一约束强制执行)，则可以与情况一一样快

2:MySQL 将检查哪个索引(在它看来)更适合这种情况并使用它。 MySQL 只能为每个 join/where 子句使用一个索引。如果您在 field1 和 field2 上有一个索引(即 index(filed1,field2))，它将使用那个索引。