Benefits of indexing

The index allows the server to navigate across the SU to the specified location. But this disease is not the only function of the index, as you can see from the current location, the index has some additional effects depending on the data structure in which the index is created.

The most common is the B-tree index, which stores data according to attributes, so MySQL can be used to do order by and group by operations. Because the data is ordered, B-tree also stores the related column values together. Finally, because the actual column values are stored in the index, some query values use the index to complete the query. According to the characteristics, the following three advantages are summed up in the index:

The 1 index greatly reduces the amount of data that the server will scan.

The 2 index can help the server avoid sorting and staging tables.

The 3 index can turn random io into sequential io.

TIPS: Index is the best solution:

Indexing is not always the best tool. In general, the index is valid only if the index helps the storage engine start finding the benefits of records that outweigh the extra work it brings. For very small tables, most of the time the full table scan is more efficient. Indexes are very effective for medium to large tables. But for extra-large tables, the cost of resumes and use will increase. In this case, you need a technique that can directly differentiate the set of data that a query needs, rather than matching a single record. For example, you can use partitioning technology (later).

If the number of tables is particularly large, a metadata information table can be created to query for some of the features that need to be used. For example, to perform queries that need to aggregate multiple applications for data that is distributed across multiple tables, you need to record the metadata of the "That user information is stored in that table" so that the tables that do not contain the specified user information can be ignored directly at query time. For large systems, this is a common technique. In fact, Infobright is using a similar implementation. For terabytes of data, one by one of a single record is not very large, so block-level metadata is often used to replace the index.

Benefits of indexing