Various types of MySQL indexes

What is an index?

An index is a data structure that a database storage engine uses to quickly find specified data.

We can use the Xinhua Dictionary as an analogy: if the detailed explanation of each character in the Xinhua Dictionary is the record of the table in the database, then the directory sorted by radicals or pinyin is the index, which allows us to quickly find the location of the detailed explanation of a certain character.

In MySQL, the storage engine also uses a similar method, first finding the corresponding value in the index, and then finding the location of the record in the corresponding table based on the matching index value.

Why are indexes asked in interviews?

The reason why indexes are often asked in interviews is that indexes are the key to good database performance and the most effective means of query optimization. Indexes can easily improve query performance by several orders of magnitude.

However, bad indexes will also affect query performance. As the amount of data in the table increases, the impact of the index on performance will increase. When the amount of data is small and the complexity is low, the impact of bad indexes on performance may not be obvious, but when the amount of data gradually increases, performance will drop sharply.

Types of Indexes

After the previous introduction, let's get to the point and learn about the index types supported by MySQL, as well as their principles and usage.

Different types of indexes can provide better performance for different scenarios. In MySQL, indexes are implemented at the storage engine level, not at the server level. As we all know, MySQL supports multiple types of storage engines. Therefore, the implementation of indexes in different storage engines is not the same, and not all types of indexes are supported by all storage engines. Even if multiple storage engines support the same type of index, its underlying implementation may be different.

B-Tree Index

B-Tree indexes are supported by most MySQL storage engines. When we discuss indexes, if the type is not specifically stated, we are probably referring to B-Tree indexes. We use the term B-Tree because MySQL uses this keyword in create table and other statements.

However, different storage engines may use different data structures and algorithms at the bottom layer. For example, the InnoDB storage engine uses the B+Tree structure internally, while the NDB cluster storage engine uses the T-Tree structure internally. Different storage engines use B-Tree indexes in different ways, and the performance may also be different. For example, the InnoDB index stores the original data format, while the MyISAM storage engine uses prefix compression technology to make the index smaller. The rows of the InnoDB index store the primary key references of the data rows, while the rows of the MyISAM storage engine index store the physical locations of the data rows.

The principle of B-Tree index

B-Tree indexes can speed up access to data because the required data can be quickly retrieved without a full table scan. So how does the B-Tree index do this? Let's take a look at how InnoDB's B-Tree index works through a simple example:

CREATE TABLE `om_address` (
 `province_name` varchar(255) NOT NULL COMMENT '省',
 `city_name` varchar(255) NOT NULL COMMENT '市',
 `district_name` varchar(255) NOT NULL COMMENT 'District',
 `detailed_address` varchar(255) NULL DEFAULT NULL COMMENT 'Detailed address',
 INDEX `index_province_city_district`(`province_name`, `city_name`, `district_name`) USING BTREE
)ENGINE = InnoDB;

There are four fields in this table, representing province, city, district and detailed address, and a B-Tree index, which includes the three fields of province, city and district. Because all values ​​of the index are stored in order, that is, the left subtree of the node is smaller than the current node, and the right subtree of the node is larger than the current node. Then when querying data, start searching from the root node of the index, and search the subtree according to the index value of the current node until the corresponding index value is found, or it is not found at all.

Usage of B-Tree Index

According to the characteristics of B-Tree index, it can be used for full value matching, value range matching and leftmost prefix matching.

• Full value matching refers to matching all fields in the index, for example, querying data for Nangang District, Harbin City, Heilongjiang Province.
• Value range matching refers to matching a certain range of fields in the index, but the previous fields must be fully matched. For example, the first field, province_name, fully matches the province name, and the second field, city_name, matches the range of city names.
• The leftmost prefix match refers to matching a certain beginning part of the field in the index, but it must satisfy the full match of the previous field. For example: the first field province_name is the name of the province, Inner Mongolia, and the second field city_name is the name of the city that starts with "呼".
  

Hash Index

Hash indexes are implemented based on hash tables and are used to accurately match the data pointed to by the index. The storage engine calculates a hash code for all index fields of each row of data. The hash code is a relatively small value, and the hash codes calculated for different data are generally different. The hash index stores the hash code and a pointer to the data row.

In MySQL, only the Memory storage engine supports hash indexes, which is also the default index type for the Memory storage engine. In addition, hash indexes are also used in the InnoDB storage engine, called adaptive hash indexes. When certain indexes are used very frequently, the InnoDB storage engine will create a hash index based on the B-Tree index in memory, so that the B-Tree index also has the advantage of fast hash search.

Because the hash index only needs to store the hash value of the corresponding data, the index structure is very compact, occupies little space, and the query speed is also very fast. However, hash indexes only support full value equality queries and cannot support index field range matching or partial index field matching.

Spatial Data Index

Spatial data index (R-Tree) is mainly used for storing geographic data. It indexes data from all dimensions and can effectively use any dimension for combined query during query. Currently, the MyISAM storage engine supports spatial data indexing, but MySQL's GIS-related functions must be used to maintain data.

In MySQL, spatial indexes can only be created on spatial data types, such as GEOMETRY, POINT, LINESTRING, etc.

Full-text index

Full-text indexing does not directly compare the values ​​in the index like the indexes introduced previously, but directly compares the keywords in the searched text. It is similar to what a search engine does, not a simple where condition match.

On the same field, you can create a full-text index and a B-Tree index at the same time without any conflict. Full-text indexes are applicable to match and against operations, not ordinary where condition operations. In MySQL, full-text indexes can only be created on fields of type CHAR, VARCHAR, or TEXT.

Summarize

An index is a data structure used by the database storage engine to quickly find specified data. It includes B-Tree index, hash index, spatial data index, and full-text index. B-Tree index is the most commonly used one. The InnoDB storage engine uses the B+Tree structure internally. Hash index is based on hash table and is used to accurately match the data pointed to by the index. Spatial data index indexes data from all dimensions, and any dimension can be effectively used for combined query during query. Full-text index directly compares keywords in the searched text, similar to a search engine.

The above is a detailed introduction to the various types of MySQL indexes. For more information about MySQL index types, please pay attention to other related articles on 123WORDPRESS.COM!