Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the working principle of mysql index". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

How the index works

The easiest way to understand how indexing works in MySQL is to take a look at the index section of a book: for example, if you want to find a topic in a book, you will first look at the book's index catalogue, find the corresponding chapter and page number, and then you can quickly find what you want to read.

In MySQL, the storage engine uses the index in a similar way, first looks up the corresponding value in the index, then finds the corresponding data row according to the matching index record, and finally returns the data result set to the client.

Type of index

In MySQL, we usually refer to the following types of indexes:

A regular index, also known as a regular index (index or key), can routinely improve query efficiency. There can be multiple regular indexes in a data table. The conventional index is the most commonly used index type. If the type of index is not clearly specified, the index we are talking about refers to the conventional index.

Primary key index (Primary Key), also referred to as primary key. It can improve query efficiency and provide uniqueness constraints. There can be only one primary key in a table. The field marked as auto-growing must be the primary key, but the primary key is not necessarily auto-growing. Generally, the primary key is defined on a meaningless field (such as: number), and the data type of the primary key is preferably numeric.

Unique index (Unique Key), which can improve query efficiency and provide uniqueness constraints. There can be multiple unique indexes in a table.

Full-text index (Full Text), which can improve the query efficiency of full-text search, generally uses Sphinx instead. However, Sphinx does not support Chinese retrieval. Coreseek is a full-text search engine that supports Chinese, also known as Sphinx with the function of Chinese word segmentation. In the actual project, we use Coreseek.

Foreign key index (Foreign Key), referred to as foreign key, can improve query efficiency, and foreign key will be automatically associated with the corresponding primary key of other tables. The main function of foreign keys is to ensure the consistency and integrity of records.

Note: only tables of the InnoDB storage engine support foreign keys. If the foreign key field does not specify an index name, it is automatically generated. If you want to delete a record in a parent table, such as a classification table, you must first delete the corresponding record in a child table (a table with a foreign key, such as an article table), or an error will occur. When creating a table, you can set foreign keys to the fields, such as foreign key (cate_id) references cms_cate (id). Because the efficiency of foreign keys is not very good, it is not recommended to use foreign keys, but we should use the idea of foreign keys to ensure the consistency and integrity of the data.

Location of the database index

The default is that the database files in here (database installation directory\ data\ a database) are not like sqlserver. Database files have suffixes.

For example:

Data file:. Myd

Index file:. MYI

Table definition file:. Frm

This is the end of the content of "how mysql Index works". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.