MongoDB Indexing issues

Source: Internet
Author: User

Definitions and principles of indexes

The index of database is a kind of data structure that sorts specific data in database to improve the efficiency of query and change of database. An implementation of an index typically uses a B-tree or a + + tree.
For example, there is currently a table with 100w data, the table structure is as follows

   create table person   (        name char(15) not null,        age int not null,        city varchar(20) not null,        primary key(name)             )

The primary key is the name of the user. Assuming that the user is living in a limited city (say 100), now, if you want to query the city= "Shanghai" users, then if there is no index, you need to scan the entire table, traverse 100w times, and match the corresponding cities field, if City= "Shanghai" It is very inefficient to take out. If you create an index on the City field, because city has a limited value (100), and the city-based value builds a B-tree, then if you want to find city= "Shanghai", you basically need only log2 (N), or up to 9 times, to find all city= " SHANGHAI, "the man.

However, although indexes can speed up query performance for a database, additional space is required to hold index data, and it becomes less efficient when inserting and updating data (you need to update the index table at the same time). Therefore, the creation of the index table needs to be based on the actual business situation, not the more index table the better.

Type unique index of the index

A unique index guarantees that the specified value for each document in the collection has a unique value, which is created as follows:
db.xxx.ensureIndex({"username":1},{"unique":true}
However, when you insert, MongoDB does not check the unique values of the document, so in order to avoid unique key duplication, you need to use a secure insert to check the corresponding GetLastError

Geo-Spatial Index

The index that is used to find the nearest point in the geographic location, how to create it:
db.xxx.ensureIndex{"gps":"2d"}
The GPS must be a pair of values, containing two elements, which are valid in the following form:

{“gps”: [0, 100]}{"gps":{"x":0, "y":100}}

And the corresponding fields can be customized, not necessarily x, Y, query, can be based on Whithin,Center and other options to query

Index creation Rules:
    • Create an index based on the actual query scenario, consider which keys are needed, whether you need a federated index, what the index direction is for each key, and so on
    • If you need to sort the collection, calling Sort,mongodb on a key that does not have an index will extract all the data into memory for sorting, with limited data size. Therefore, it is recommended that you set the index for the sort data reasonably
    • Good at using explain to view index usage for queries
    • When creating an index, it is best to create it in the background (specify Background:true when created), otherwise MongoDB blocks all requests until the index is created

MongoDB Indexing issues

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.