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

Editor to share with you the working principle of the mysql index, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

A brief introduction to the Index in MySQL

In MySQL, index, also known as "key", is a data structure used by the storage engine to quickly find records.

Indexes are critical to good performance, especially as the amount of data in the table becomes larger and larger, the impact of indexes on performance becomes more and more important.

Index optimization should be the most effective means to optimize query performance, and creating a truly optimal index often requires rewriting SQL query statements.

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

The above is all the contents of the article "how mysql Index works". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.