What does mysql index mean?

What is an index?

MySQL’s official definition of index is: Index (Index) is a data structure that helps MySQL obtain data efficiently. We can simply understand it as: a data structure that can be quickly searched and sorted. Mysql index mainly has two structures: B Tree index and Hash index. The index we usually refer to, unless otherwise specified, generally refers to an index organized in a B-tree structure (B Tree index).

Types of indexes

There are many types of indexes, such as hash, BTREE, full-text index, etc. In fact, no matter what type, they are all for convenience in specific business scenarios. Algorithms for finding data quickly.

For example, hash index, key-value form, the simplest, book table of contents is similar.

If there is a book, we know the title of the article and we need to find this article to read. If there is no table of contents, we have to look through the entire book to find the title.

But if the title is alone Extract it as the key and use the page number as the value to quickly find the content.

Obviously, the table of contents at the beginning of the book takes up several pages, and building an index also consumes resources.

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

Just like the table of contents of Xinhua Dictionary is different from the table of contents of ordinary books, Xinhua Dictionary can quickly find the page on which a word is located through pinyin or radical query methods. This is also a kind of "index"

There are also full-text indexes, etc., which are not described here. For example, you can study the algorithms used by solr and elasticsearch.

Index concept

The essence of index: index is Replication of field values ​​in the database table. This field is called the key of the index.

The index is also a table. This table stores the primary key and index fields and points to the records of the entity table.

Index Often implemented through complex data structures (doubly linked lists, B-trees/B-trees, hashes)

MyISAM storage engine tables support primary indexes, and InnoDB storage engine tables support clustered indexes (primary indexes) and non-clustered indexes. Cluster index (auxiliary index) index optimization uses

Advantages and disadvantages of index

@Advantages:

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

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

The connection conditions of table connections can be accelerated Tables are directly connected to tables

@Disadvantages:

But excessive use of indexes will cause index abuse, increase the retrieval speed, and reduce the update speed of the table

For example : When adding, deleting, modifying, and querying the table, MySQL must not only save the data, but also save the index file. Creating the index will occupy the disk space of the index file

It will take time to create and maintain the index, as the data Increase with the increase in quantity

The above is the detailed content of What does mysql index mean?. For more information, please follow other related articles on the PHP Chinese website!