MySQL Learning note-Understanding Index

1. An index is a directory of a book that, when queried for a row of data in a table, can quickly locate the row and return it as a result set based on the index. In the MySQL directory, there is a data directory that contains all the structures, data, and indexes in the database. The. frm file holds the structure of the table. The MyD stores data,. Myi is the index file. The data is stored in a binary form. MyD file: The Myi file stores data in an efficient structure, such as a balanced binary tree, where the data is positioned quickly when the number is queried.

2. The index improves query speed, but decreases the speed of additions and deletions, because the index file is always modified whenever the data is modified. It is generally useful to index a column with a high query frequency and a lower repetition rate.

3. Type of index:

(1) Normal index key

(2) uniquely indexed unique key

(3) Primary key index primary key

(4) Full-text index fulltext

4. When indexing is established, you can set the length of the index, such as the first few characters of a column. such as key email (email (10))

5. Multi-column indexes are the values of two or more columns as a monolithic index. A redundant index is one column where there may be multiple indexes.

MySQL Learning note-Understanding Index