MySQL寻找一个不错的索引

我有这张桌子(简化版)

create table completions (
  id int(11) not null auto_increment,
  completed_at datetime default null,
  is_mongo_synced tinyint(1) default '0',
  primary key (id),
  key index_completions_on_completed_at_and_is_mongo_synced_and_id (completed_at,is_mongo_synced,id),
) engine=innodb auto_increment=4785424 default charset=utf8 collate=utf8_unicode_ci;

尺寸:

select count(*) from completions; -- => 4817574

现在我尝试执行此查询:

select completions.* 
from completions  
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  order by completions.id asc limit 10;

它需要9 分钟.

我看到没有使用任何索引,explain extend返回此信息:

id: 1 
select_type: SIMPLE
table: completions 
type: index 
possible_keys: index_completions_on_completed_at_and_is_mongo_synced_and_id  
key: PRIMARY 
key_len: 4 
ref: NULL  
rows: 20  
filtered: 11616415.00 
Extra: Using where

如果我强制索引:

select completions.* 
from completions  
force index(index_completions_on_completed_at_and_is_mongo_synced_and_id)
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  order by completions.id asc limit 10;

需要1,22s,这要好得多.的explain extend回报:

id: 1
select_type: SIMPLE
table: completions
type: range
possible_keys: index_completions_on_completed_at_and_is_mongo_synced_and_id
key: index_completions_on_completed_at_and_is_mongo_synced_and_id
key_len: 6
ref: null
rows: 2323334
filtered: 100
Extra: Using index condition; Using filesort

现在如果我通过以下方式缩小查询范围completions.id:

select completions.* 
from completions  
force index(index_completions_on_completed_at_and_is_mongo_synced_and_id)
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 2000000
  order by completions.id asc limit 10;

它需要1,31s,仍然很好.的explain extend回报:

id: 1
select_type: SIMPLE
table: completions
type: range
possible_keys: index_completions_on_completed_at_and_is_mongo_synced_and_id
key: index_completions_on_completed_at_and_is_mongo_synced_and_id
key_len: 6
ref: null
rows: 2323407
filtered: 100
Extra: Using index condition; Using filesort

关键是如果对于最后一个查询我不强制索引:

select completions.* 
from completions  
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 2000000
  order by completions.id asc limit 10;

它需要85ms,检查它是ms而不是s.的explain extend回报:

id: 1
select_type: SIMPLE
table: completions
type: range
possible_keys: PRIMARYindex_completions_on_completed_at_and_is_mongo_synced_and_id
key: PRIMARY
key_len: 4
ref: null
rows: 2323451
filtered: 100
Extra: Using where

这不仅令我感到疯狂,而且还因为过滤器数量的微小变化,最后一个查询的性能受到很大影响:

select completions.* 
from completions  
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 1600000
  order by completions.id asc limit 10;

需要13秒

我不明白的事情:

为什么在查询B假设使用更精确的索引时,以下查询A比查询B更快:c

查询A:

select completions.* 
from completions  
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 2000000
  order by completions.id asc limit 10;

85ms

查询B:

select completions.* 
from completions  
force index(index_completions_on_completed_at_and_is_mongo_synced_and_id)
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 2000000
  order by completions.id asc limit 10;

1,31s

2.为什么以下查询中的性能差异如此:

查询A:

select completions.* 
from completions  
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 2000000
  order by completions.id asc limit 10;

85ms

查询B:

select completions.* 
from completions  
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 1600000
  order by completions.id asc limit 10;

13S

3.为什么MySQL没有自动使用以下查询的索引:

指数:

key index_completions_on_completed_at_and_is_mongo_synced_and_id (completed_at,is_mongo_synced,id),

查询:

select completions.* 
from completions  
force index(index_completions_on_completed_at_and_is_mongo_synced_and_id)
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 2000000
  order by completions.id asc limit 10;

更新

评论中要求提供更多数据

 select
     completions.is_mongo_synced,
     count(*)
 from completions
 group by completions.is_mongo_synced;

结果:

[
  {
    "is_mongo_synced":0,
    "count(*)":2731921
  },
  {
    "is_mongo_synced":1,
    "count(*)":2087869
  }
]

select completions.* 
from completions  
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 2000000
  limit 10;

544ms

select completions.* 
from completions  
force index(index_completions_on_completed_at_and_is_mongo_synced_and_id)
where 
  (completed_at is not null) 
  and completions.is_mongo_synced = 0 
  and completions.id > 2000000
  limit 10;

314ms

但是,无论如何,我需要订单,因为我正在逐批扫描表.