mysql范围索引 [英] mysql range index

问题描述

我有一个非常简单的选择，像这样:

I have very simple select like this:

SELECT * FROM table 
  WHERE column1 IN (5, 20, 30);

在column1上的

设置了索引，说明查询使用了索引后，一切看起来都可以.

on column1 is seted index, after explaining query is index used, all looks to be ok.

但是如果范围内的值超过三个，则如下所示:

but if there are more than three values in range, like this:

  SELECT * FROM table 
      WHERE column1 IN (5, 20, 30, 40);

索引未使用，并且select通过所有记录运行.难道我做错了什么?谢谢

index is not used and select runs thru all records. Am I doing something wrong? thanks

推荐答案

MySql认为表中有几行?

How many rows does MySql think there are in the table?

Mysql通常(通常是正确的！)认为顺序扫描行会更快，而不是通过索引来处理更复杂的访问.

Mysql often (usually correctly!) assumes it will be quicker to do a sequential scan of the rows, rather than mess around with the more complex access via an index.

不同的DBMS之间存在差异，但折衷点大约是行的30％.

It varies from DBMS to DBMS but the tradeoff point is somewhere about 30% of the rows.

IE.如果优化器期望选择超过30％的行，它将顺序扫描整个表，因为通常这比通过索引进行大量直接访问要快.

IE. If the optimiser expects more than 30% of the rows to be selected it will sequentially scan the whole table as this is usually faster than doing lots of direct access via indexes.

这篇关于mysql范围索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！