MongoDB Index Types How, when and where should they be used?

下载 0

苏寒

发布于

1612

人观看

#信息技术

MongoDB有12种索引类型。你知道每种方法是如何工作的，还是应该在何时使用？本次讨论将为您提供这些知识，并帮助您了解索引如何影响性能、存储甚至数据的切分。我们还将讨论一些可靠的索引操作实践，以及一些诸如TTL之类您可能不知道存在的设置。本次网络研讨会的内容将使您成为摇滚明星！

展开查看详情

7 .How do indexes work? The Index This is an illustration, in the real world, of what an index looks like. If you were the guy in the last picture, what would you do? Would you read all the books or simply check the indexes? Library catalog 7 © 2017 Percona

8 .Indexes in MongoDB bigcollection (~ 100000 documents) - No Index db.bigcollection.find({name : 'PERCONA'}).explain(true) "executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 350, "totalKeysExamined" : 0, "totalDocsExamined" : 1000000, "executionStages" : { "stage" : "COLLSCAN", "filter" : { "name" : { "$eq" : "PERCONA" } }, "nReturned" : 0, 8 © 2017 Percona

9 .Indexes in MongoDB smallcollection (100 documents) - No Index db.smallcollection.find({name : 'PERCONA' }).explain(true) "executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 100, "executionStages" : { "stage" : "COLLSCAN", "filter" : { "name" : { "$eq" : "PERCONA" } }, "nReturned" : 0, 9 © 2017 Percona

10 .Indexes in MongoDB bigcollection (~ 100000 documents) With Indexes db.bigcollection.find({name : 'PERCONA' }).explain(true) "executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 0, "executionStages" : { "stage" : "FETCH", "nReturned" : 0, "executionTimeMillisEstimate" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 0, "executionTimeMillisEstimate" : 0, 10 © 2017 Percona

11 .Indexes in MongoDB smallcollection (100 documents) - With Indexes db.smallcollection.find({name : 'PERCONA' }).explain(true) "executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 0, "executionStages" : { "stage" : "FETCH", "nReturned" : 0, "executionTimeMillisEstimate" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 0, "executionTimeMillisEstimate" : 0, "works" : 1, 11 © 2017 Percona

13 .Costs of maintaining indexes: ● Each Index adds cost to the writing process. ● Writes will occur in real time –– there is no "delay" when updating indexes. ● A lot of indexes will probably slow down the write performance. 13 © 2017 Percona

15 .Indexes in MongoDB The index types are: _id GeoIndexes (geoHaystack, 2d spherical, flat 2d indexes) Single Field MultiKey indexes Compound Indexes Hash indexes Text Sparse TTL indexes Unique Partial (filtered) 15 © 2017 Percona

16 .Types of Indexes ● _id is the default primary key in MongoDB. ● _id is not a clustered index and the database must perform another operation so as to read all the values of the document. 16 © 2017 Percona

17 .Types of Indexes ● Single field is also called simple index and it indexes only one field at a time. ● Creating a single field index will help the query optimizer find the desired document quickly. ● A high cardinality field is a good candidate to be indexed. 17 © 2017 Percona

18 .Types of Indexes ● When single field is not enough by itself, the compound indexes help the execution plan to filter as many documents as possible. ● If we filter by name and lastname and only the name field is indexed, we may need to open a lot of documents to read the lastname. On the other hand, if lastname is already indexed, there is no need to do so. ● Use the higher cardinality field as first field in the btree. 18 © 2017 Percona

19 .Types of Indexes ● Multikey Indexes are used when the field value is an array; ● For each key in the array there is an entry with its values. ● For the document { likes : ['mongodb', 'mysql'] }, the multikey will generate 2 different index keys pointing to the same document. m mongodb mysql 19 © 2017 Percona

21 .Types of Indexes ● There are plenty of GeoIndexes. MongoDB offers rich geo algorithms; ● For flat calculations, we can use 2d indexes; ● It supports 2d sphere; ● We can also use geoHaystack in order to get responses - This will compare small squares instead of reading whole collection: It is very similar to geohashes. 21 © 2017 Percona

22 .Types of Indexes ● Text indexes behave similarly to the single field index, but there are some extended properties such as collations and case insensitive. We can also give weight to words when running a search. 22 © 2017 Percona

23 .Types of Indexes ● Sparse and Partial indexes. ● Although they sound very similar, sparse indexes only create index keys in documents that do have the field. The filtered index will create an index only for a field value that matches the argument. E.g { 'age' : {$gte : 10 }} 23 © 2017 Percona

25 .Types of Indexes ● TTL indexes are very useful. Every minute mongodb will run a "TTL round", where all the documents matching time to live will be removed from the database. ● It is very useful, but when you are creating a TTL in a big collection, it is better to first delete the expired documents, otherwise the database load will increase significantly. 25 © 2017 Percona

26 .Quick Review ● Indexes are good for you if well used. ● Avoid creating more indexes than required. ● Use the right index for your application. Indexes use space in the disk and memory. Indexing all the fields in one collection is useless in 90% of the cases. ● There is no magic, fast processors and a good amount of memory will decrease the response time. 26 © 2017 Percona

28 . DATABASE PERFORMANCE Database Performance Matters MATTERS

29 .Percona Live Europe Call for Papers & Registration are Open! Championing Open Source Databases ▪ MySQL, MongoDB, Open Source Databases ▪ Time Series Databases, PostgreSQL, RocksDB ▪ Developers, Business/Case Studies, Operations ▪ September 25-27th, 2017 ▪ Radisson Blu Royal Hotel, Dublin, Ireland Submit Your Proposal by July 17th! www.percona.com/live/e17 29 © 2017 Percona

0点赞

0收藏

0下载