AGE [1，2，3]对1和3之间的AGE [英] AGE [1, 2, 3] vs. AGE BETWEEN 1 AND 3

问题描述

我有一个InnoDB表和 age 列的索引，就像这样

I have an InnoDB table and an index on age column just like this

   CREATE TABLE Person (

      ....
      age int(11) not null;
      key (age);
      ....
   ) engine=InnoDB;

我只想知道这些查询背后的真实情况：

I just want to know the real things behind these queries:

SELECT * FROM Person WHERE age IN [1, 2, 3];

和

SELECT * FROM Person WHERE age BETWEEN 1 AND 3;

正如我所学到的，第一个MySQL将使用列年龄上的索引，而第二个一个它不能使用。是吗？

As what I've learnt, the first one MySQL will use the index on column age while the second one it can't use. Is it? Which is better for performance?

推荐答案

这两个查询都会使用索引。

Both queries will use an index.

查询A将翻译为：

select * from person where age = 1 or age = 2 or age 3;

查询B将翻译为

select * from person where age >= 1 and age <= 3;

因此查询A将使用OR进行3个测试。

Query B will do 2测试使用AND。

So query A will do 3 tests using OR.
Query B will do 2 tests using AND.

查询B更快。

使用 AND 比使用 OR 的查询更快。

同样，查询B的测试次数较少，因为它正在测试一个范围，它可以更容易地排除不想要的结果。

In general queries using AND are faster than queries using OR.
Also Query B is doing fewer tests and because it's testing a range it can more easily exclude results that it does not want.

这篇关于AGE [1，2，3]对1和3之间的AGE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！