Sparse Indexes vs unique index
wxh:PRIMARY> db.coll.ensureIndex({id2:1} ,{unique:true})
{
"err" : "E11000 duplicate key error index: test.coll.$id2_1 dup key: { : null }",
"code" : 11000,
"n" : 0,
"lastOp" : Timestamp(1390372177, 1),
"connectionId" : 1818,
"ok" : 1
}
發現在id2上建立唯一索引建不上去,原來mongodb會把null值作為一個“真”值處理,那麼就不允許一個欄位上有超過2個null值存在。
這種情況下,可以透過Sparse Indexes解決這個問題
wxh:PRIMARY> db.coll.ensureIndex({id2:1} ,{unique:true ,"sparse":true})
wxh:PRIMARY> db.coll.stats()
{
"ns" : "test.coll",
"count" : 3,
"size" : 112,
"avgObjSize" : 37.333333333333336,
"storageSize" : 4096,
"numExtents" : 1,
"nindexes" : 2,
"lastExtentSize" : 4096,
"paddingFactor" : 1,
"systemFlags" : 1,
"userFlags" : 0,
"totalIndexSize" : 16352,
"indexSizes" : {
"_id_" : 8176,
"id2_1" : 8176
},
"ok" : 1
}
Sparse Indexes可能導致的問題:
> db.foo.find({"x" : {"$ne" : 2}})
{ "_id" : 0 }
{ "_id" : 1, "x" : 1 }
{ "_id" : 3, "x" : 3 }
如果你在x上建立了Sparse Indexes索引,那麼查詢的結果就會返回:
> db.foo.find({"x" : {"$ne" : 2}})
{ "_id" : 1, "x" : 1 }
{ "_id" : 3, "x" : 3 }
這是由於{ "_id" : 0 }並不包含在Sparse Indexes索引裡,而查詢的計劃卻走了索引掃描
{
"err" : "E11000 duplicate key error index: test.coll.$id2_1 dup key: { : null }",
"code" : 11000,
"n" : 0,
"lastOp" : Timestamp(1390372177, 1),
"connectionId" : 1818,
"ok" : 1
}
發現在id2上建立唯一索引建不上去,原來mongodb會把null值作為一個“真”值處理,那麼就不允許一個欄位上有超過2個null值存在。
這種情況下,可以透過Sparse Indexes解決這個問題
wxh:PRIMARY> db.coll.ensureIndex({id2:1} ,{unique:true ,"sparse":true})
wxh:PRIMARY> db.coll.stats()
{
"ns" : "test.coll",
"count" : 3,
"size" : 112,
"avgObjSize" : 37.333333333333336,
"storageSize" : 4096,
"numExtents" : 1,
"nindexes" : 2,
"lastExtentSize" : 4096,
"paddingFactor" : 1,
"systemFlags" : 1,
"userFlags" : 0,
"totalIndexSize" : 16352,
"indexSizes" : {
"_id_" : 8176,
"id2_1" : 8176
},
"ok" : 1
}
Sparse Indexes可能導致的問題:
> db.foo.find({"x" : {"$ne" : 2}})
{ "_id" : 0 }
{ "_id" : 1, "x" : 1 }
{ "_id" : 3, "x" : 3 }
如果你在x上建立了Sparse Indexes索引,那麼查詢的結果就會返回:
> db.foo.find({"x" : {"$ne" : 2}})
{ "_id" : 1, "x" : 1 }
{ "_id" : 3, "x" : 3 }
這是由於{ "_id" : 0 }並不包含在Sparse Indexes索引裡,而查詢的計劃卻走了索引掃描
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-1074062/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Unique and Nonunique Indexes (195)Index
- Index Unique Scan (213)Index
- pk 、unique index 和 index 區別Index
- INDEX UNIQUE SCAN,INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- Unique Index和Normal Index差異經典對比IndexORM
- 再說Unique Index和Normal Index行為差異IndexORM
- Bitmap Indexes on Index-Organized Tables (232)IndexZed
- unique index與primary key的區別Index
- Some indexes or index partitions of table have been marked unusableIndex
- Data Warehouse Guide閱讀筆記(六):unique constraint & unique indexGUIIDE筆記AIIndex
- 重建索引index rebuild online vs offline vs index coalesce vs index shrik space索引IndexRebuild
- [20171211]UNIQUE LOCAL(Partitioned)IndexIndex
- 高效的SQL(Index unique scan最優化)SQLIndex優化
- Secondary Indexes on Index-Organized Tables (231)IndexZed
- 唯一性索引(Unique Index)與普通索引(Normal Index)差異(上)索引IndexORM
- 唯一性索引(Unique Index)與普通索引(Normal Index)差異(中)索引IndexORM
- 唯一性索引(Unique Index)與普通索引(Normal Index)差異(下)索引IndexORM
- 索引唯一性掃描(INDEX UNIQUE SCAN)索引Index
- 在什麼情況下用index unique scansIndex
- Index Full Scan vs Index Fast Full ScanIndexAST
- ORA-02429: cannot drop index used for enforcement of unique/primary keyIndex
- Oracle 19c Concepts(03):Indexes and Index-Organized TablesOracleIndexZed
- 【SQL優化】LIKE vs INDEXSQL優化Index
- oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的區別OracleIndexMySql
- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys foundIndex
- Range Sparse Net
- OGG How to handle / replicate tables with no (without) primary key (PK) or unique index (UI) (UPI) [IndexUI
- 主鍵(PK)與非空唯一索引(Unique Index)的區別索引Index
- best practice of rebuild your partition table local index online by using: "update indexes"RebuildIndex
- Oracle vs PostgreSQL Develop(31) - Index Only ScanOracleSQLdevIndex
- dba_ind_partitions中index的紀錄和dba_indexes是否重複Index
- B-tree Indexes on UROWID Columns for Heap- and Index-Organized Tables (235)IndexZed
- PostgreSQL DBA(119) - pgAdmin(LIMIT:Index Scan vs Bitmap Index Scan)SQLMITIndex
- PostgreSQL DBA(177) - Serializability Isolation(Index vs NonIndex)SQLIndex
- Oracle vs PostgreSQL Develop(30) - Index&Case whenOracleSQLdevIndex
- Rebuild IndexesRebuildIndex
- ORACLE INDEXESOracleIndex
- ORA-00001 : Unique Constraint Violated caused by DATE VS Timestamp(6)AI