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

This article mainly introduces the meaning of mysql index, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

MySQL officially defines index as: index (Index) is a data structure that helps MySQL to obtain data efficiently. We can simply understand it as: quickly find a sorted data structure. There are two main structures of Mysql index: B+Tree index and Hash index. The index we usually talk about, if not specifically specified, generally refers to the index organized by the B-tree structure (B+Tree index).

Type of index

There are many types of indexes, such as hash, BTREE, full-text index, etc., in fact, no matter what type, it is a convenient and fast algorithm to find data in specific business scenarios.

For example, the hash index, the key-value form, and the simplest, the catalogue of books is similar.

If there is a book, we know that the title of the article is to find this article to read, if there is no catalogue, we have to go through the whole book to find the title.

But if the title is extracted separately as key, the page number as value can quickly find the content.

Obviously, the catalogue at the beginning of the book takes up a few pages, and indexing consumes resources.

BTREE index is another algorithm, and it is more efficient to use corresponding indexes in different business scenarios.

Just like the catalogue of Xinhua dictionary is different from that of ordinary books, Xinhua dictionary can quickly find out which page the word is on by means of pinyin or partial radical query, which is also a kind of "index".

There are also full-text indexing, which are not described here. For example, the algorithms used by solr and elasticsearch can be studied.

Index concept

The essence of the index: an index is the replication of field values in a database table, and this field is called the keyword of the index.

An index is also a table that holds the primary key and index fields and points to the record of the entity table

Indexes are often implemented through complex data structures (two-way linked list, B + tree / B tree, hash).

The tables of MyISAM storage engine support primary index, and the tables of InnoDB storage engine support clustered index (primary index) and non-clustered index (secondary index).

Advantages and disadvantages of index

@ advantages:

The establishment of the index of MySQL is very important for the efficient operation of MySQL, which can greatly improve the retrieval speed of MySQL.

Data retrieval in grouping and sorting words can reduce the time spent on grouping and sorting in query time (database records will be reordered)

The connection condition of the connection in the table can be directly connected to the accelerometer.

@ disadvantages:

However, excessive use of indexes will lead to index abuse, improve the retrieval speed, and reduce the update speed of the table.

For example, when adding, deleting, modifying and querying a table, MySQL should not only save the data, but also save the index file, and establish an index file that will take up disk space.

It takes time to create and maintain indexes, which increases as the amount of data increases.

Thank you for reading this article carefully. I hope the editor will share what the mysql index means to help everyone. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.