哈希索引:能做等值,不能做范围

  test> db.t.getIndexes().filter(i => i.key.k == "hashed" || i.key.u == "hashed").map(i => ({name: i.name, key: i.key}))
  [
    { name: 'k_hashed', key: { k: 'hashed' } },
    { name: 'u_hashed', key: { u: 'hashed' } }
  ]
  test> [1, 2, 3, 4, 5].map(v => v + " → " + convertShardKeyToHashed(v))
  [
    '1 → 5902408780260971510',
    '2 → 848411777775835583',
    '3 → -1529429926180645992',
    '4 → 2433984061343616404',
    '5 → 4470791281878691347'
  ]
  test> ["user1", "user2"].map(v => v + " → " + convertShardKeyToHashed(v))
  [ 'user1 → 801843209332933044', 'user2 → -4095265838481657615' ]

  test> db.t.find({k: 12345}).hint("k_hashed")
  {
    plan: 'FETCH > IXSCAN(k_hashed)',
    nReturned: 1,
    keysExamined: 1,
    docsExamined: 1,
    ms: 6
  }
  # indexBounds
  test> var e = db.t.find({k: 12345}).hint("k_hashed").explain(); var s = e.queryPlanner.winningPlan; while (s.inputStage) s = s.inputStage; JSON.stringify(s.indexBounds)
  {"k":["[7147702579911805439, 7147702579911805439]"]}
  # $in: [1, 2, 3] 的 indexBounds
  test> var e = db.t.find({k: {$in: [1, 2, 3]}}).hint("k_hashed").explain(); var s = e.queryPlanner.winningPlan; while (s.inputStage) s = s.inputStage; JSON.stringify(s.indexBounds)
  {"k":["[-1529429926180645992, -1529429926180645992]","[848411777775835583, 848411777775835583]","[5902408780260971510, 5902408780260971510]"]}

  test> hint("k_hashed") 的范围查询
  {
    plan: 'FETCH > IXSCAN(k_hashed)',
    nReturned: 100,
    keysExamined: 200000,
    docsExamined: 200000,
    ms: 142
  }
  test> hint("k_range") 的同一个范围查询
  {
    plan: 'FETCH > IXSCAN(k_range)',
    nReturned: 100,
    keysExamined: 100,
    docsExamined: 100,
    ms: 2
  }
  test> 删掉普通索引后不 hint
  {
    plan: 'COLLSCAN',
    nReturned: 100,
    keysExamined: 0,
    docsExamined: 200000,
    ms: 30
  }
  test> sort({k: 1}) 用哈希索引
  {
    plan: 'SORT > FETCH > IXSCAN(k_hashed)',
    nReturned: 5,
    keysExamined: 200000,
    docsExamined: 200000,
    ms: 141
  }

  test> createIndex({k: "hashed"}, {unique: true})
  Currently hashed indexes cannot guarantee uniqueness. Use a regular index.
  test> 数组字段上建哈希索引
  Error: hashed indexes do not currently support array values. Found array at path: k
  test> createIndex({a: "hashed", b: "hashed"})
  A maximum of one index field is allowed to be hashed but found 2 for 'key' { a: "hashed", b: "hashed" }
  test> createIndex({a: 1, b: "hashed"})    // 只有一个哈希字段的复合索引
  ab

  test> [2.1, 2.3, 2.9, 3].map(v => v + " → " + convertShardKeyToHashed(v))
  [
    '2.1 → 848411777775835583',
    '2.3 → 848411777775835583',
    '2.9 → 848411777775835583',
    '3 → -1529429926180645992'
  ]
  test> db.f.find({x: 2.3}).hint("x_hashed")    // 键里同一个哈希有 3 条
  {
    plan: 'FETCH > IXSCAN(x_hashed)',
    nReturned: 1,
    keysExamined: 3,
    docsExamined: 3,
    ms: 0
  }
  test> 结果仍然只有 x = 2.3 的文档
  [ 2 ]

  # 聚合里的 $toHashedIndexKey 算出的值与 convertShardKeyToHashed 一致
  test> db.t.aggregate([{$match: {_id: {$lt: 3}}}, {$project: {_id: 0, k: 1, h: {$toHashedIndexKey: "$k"}}}])
  [
    { k: 0, h: Long('4854801880128277513') },
    { k: 1, h: Long('5902408780260971510') },
    { k: 2, h: Long('848411777775835583') }
  ]
  前 8 个连续整数 0..7 的哈希的符号与量级: ['+4.9e18', '+5.9e18', '+0.8e18', '-1.5e18', '+2.4e18', '+4.5e18', '-6.5e18', '+9.2e18']
  4000 个连续整数的哈希落在 8 等分的 64 位空间里的个数: [482, 506, 505, 488, 506, 507, 528, 478](期望每份 500)