Shulou(Shulou.com)06/01 Report--

In this issue, the editor will introduce the index types of mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Mysql currently has the following main index types:

Full-text,HASH,B-Tree,R-Tree .

Detailed introduction:

1 、 Full-text

It is a full-text index, which is mainly used to find keywords in the text, rather than directly comparing with the values in the index. The full-text index is very different from other indexes in that it is more like a search engine than a simple parameter match for a where statement. Fulltext indexes are used with match against operations, rather than normal where statements plus like. It can be used in create table,alter table and create index, but currently only full-text indexes can be created on char and varchar,text columns. It is worth mentioning that when the amount of data is large, it is much faster to put the data into a table without a global index, and then create a fulltext index with CREATE index than to create a fulltext for a table and then write the data.

2 、 HASH

Because HASH is unique (almost 100% unique) and in the form of similar key-value pairs, it is suitable for use as an index.

HASH indexes can be located at once and do not need to be searched layer by layer like a tree index, so it is extremely efficient. However, this efficiency is conditional, that is, it is efficient only under the conditions of "=" and "in", but it is still not efficient for range queries, sorting, and combinatorial indexes.

3 、 B-TREE

B-Tree index is the most frequently used index type in MySQL database, and all storage engines except Archive storage engine support B-Tree index. Not only in MySQL, but also in many other database management systems, B-Tree index is also the most important index type. This is mainly because the storage structure of B-Tree index has excellent performance in database data retrieval.

4 、 RTREE

R-Tree index may be a type of index that we rarely see in other databases, which is mainly used to solve the problem of spatial data retrieval. In MySQL, GEOMETRY, a data type for storing spatial information, is supported and is based on the OpenGIS specification. In previous versions of MySQL5.0.16, only the MyISAM storage engine supported this data type, but starting with the MySQL5.0.16 version, the BDB,Innodb,NDBCluster and Archive storage engines also began to support this data type. Of course, although a variety of storage engines began to support GEOMETRY data types, only later did the MyISAM storage engine support R-Tree indexes. The above is the introduction of the index type of mysql shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.