High performance MySQL reading notes fifth-creating high-performance Indexes 1

Create high-Performance Indexes 1

An index is a data structure used by the storage engine to quickly find records, which is the basic function of an index.

Indexes are important for good performance.

Index optimization should be the most effective way to optimize query performance, index can easily improve query performance several orders of magnitude, "optimal" index sometimes better than a "good" index performance two orders of magnitude, creating a truly "optimal" index often need to rewrite the query.

I. Types of indexes

1.B Tree Index

When people talk about the index, if there is no specific type, then most of it is the B-tree index, he uses the B-tree data structure to store the data.

Query types that can use the B-Tree index: full-value matching, matching the leftmost prefix, matching column prefixes, matching range values, exactly matching one column and range matching another column, and accessing only the indexed query.

Limit for B-number tree indexes: If you do not start the search by the leftmost column of the index, you cannot use the index, you cannot skip the columns in the index, and if there is a range query for a column in the query, none of its right columns can use index-optimized queries.

2. Hash index

In MySQL, only the memory engine shows support for hash indexes.

The hash index contains only the hash and row pointers, not the field values, so you cannot use the values in the index to avoid reading the rows.

Hash index data is not stored in the order of index values, so it cannot be used for sorting.

The hash index also does not support partial indexed column matching lookups, because the hash index always computes the hash value using the entire contents of the indexed column.

Hash indexes only support equivalent comparison queries.

The data that accesses the hash index is very fast, unless there are many hash conflicts.

Some index maintenance operations can be costly if there is a lot of hash conflicts.

The InnoDB engine has a special feature called an Adaptive hash Index, and when InnoDB notices that certain index values are used very frequently, he creates a hash index on top of the B-tree index in memory.

If the storage engine does not support hash indexes, you can simulate the creation of hash indexes like InnoDB, which can be facilitated by the use of a few hash indexes, such as the ability to create indexes for extra-long keys with only a small index.

3. Spatial Data Index (R-Tree)

The MyISAM table supports spatial indexes and can be used as a geographic data store.

4. Full-Text Indexing

A full-text index is a special type of index that looks for keywords in text instead of comparing values in indexes. There is no conflict between creating a full-text index and a value-based B-tree index on the same column, and the full-text index applies to the match against operation instead of the normal where condition operation.

5. Other index categories

There are also a number of third-party storage engines that use different types of data structures to store indexes, such as tokudb using a fractal tree index (fractal tree indexes).

Second, the advantages of the index

The index allows the server to quickly navigate to the specified location of the table. But this is not the only function of the index.

1. The index greatly reduces the amount of data the server needs to scan.

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

3. The index can turn random I/O into sequential I/O.

High performance MySQL reading notes fifth-creating high-performance Indexes 1