Where is the mysql index?

The location of the mysql index is in [database installation directory\data\databasename]. An index is a data structure used by storage engines to quickly find records. Indexes are critical to good performance.

Introduction to indexes in MySQL

(Video tutorial recommendation: mysql video tutorial)

In MySQL, the index (index) is also called "key (key)", which is a data structure used by the storage engine to quickly find records.

Indexes are very critical for good performance, especially when 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. Creating a truly optimal index often requires rewriting SQL query statements.

How indexes work

To understand how indexes work in MySQL, the easiest way is to take a look at the index part of a book: For example, if you want When looking for a certain topic in a book, you usually first look at the index of the book. After finding the corresponding chapter and page number, you can quickly find the content you want to read.

In MySQL, the storage engine uses indexes in a similar way. It first searches for the corresponding value in the index, then finds the corresponding data row based on the matching index record, and finally returns the data result set to the client. .

Types of indexes

In MySQL, the index types we usually refer to include the following:

Regular index, also called ordinary index Index (index or key), which can generally improve query efficiency. There can be multiple regular indexes in a data table. Conventional index is the most commonly used index type. If the type of index is not explicitly specified, the index we refer to refers to a conventional index.

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

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

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

Foreign Key (Foreign Key), referred to as foreign key, can improve query efficiency. Foreign keys will automatically be associated with the corresponding primary keys 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 will be automatically generated. If you want to delete records in the parent table (such as the classification table), you must first delete the corresponding records in the child table (the table with foreign keys, such as the article table), otherwise an error will occur. When creating a table, you can set foreign keys for fields, such as foreign key(cate_id) references cms_cate(id). Since the efficiency of foreign keys is not very good, it is not recommended to use foreign keys, but we must use the idea of ​​​​foreign keys. to ensure data consistency and integrity.

The location of the database index

The default is here (database installation directory\data\a certain database). Unlike sqlserver, the database files have suffixes

For example:

Data file: .myd

Index file: .MYI

Table definition file: .frm

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