MySQL understanding index, add index principle

[Understanding Index]

To understand an index, you first need to understand how the data is stored on your hard disk. Different storage engines may not take the same action, for example, the MySQL client defaults to MyISAM, which creates a separate file for each table.

Regardless of whether a separate file is created for each table, the operating system reads data from the hard disk into memory and is always in pages. Therefore, if you want to get a specific record, you need to read the page that contains the record.

MySQL can take a single scan or index to access two different methods when getting a record. Assuming that the first method is taken, to obtain a record with ID 1234, you need to access the first 1233 records sequentially and sequentially. Not only that, you also need to consider the IO overhead of reading data pages every time. If you take an index, you can quickly read the target page and get the target record based on the page that the index points to and the location of the record in the page.

In addition to using hash in the case of obtaining a specific row, it is very fast to use the B-tree in other cases (the default) to build the index. The B-Tree is a balanced multi-fork tree, and how many values each node holds depends on the amount of space the value occupies, and how many records are stored on each page. The values in the nodes are arranged in a non-descending order, and the values in the nodes are always less than or equal to the values in the node that points to it.

When MySQL constructs an index using a B-tree, the leaves point to specific pages and records. And a leaf has a pointer pointing to the next leaf.

The use of indexes requires attention:

⑴ only sets the index of where and order by fields that require querying, avoiding meaningless drive overhead;

⑵ Combined index supports prefix index;

⑶ Update the table, such as adding and deleting records, MySQL will automatically update the index, keep the tree balance;

First, the importance of the index

Indexes are used to quickly find rows that have a specific value in a column. Without an index, MySQL must start with the 1th record and then read the entire table until the relevant rows are found. The larger the table, the more time it takes. If there is an index to the column queried in the table, MySQL can quickly reach a location to find the middle of the data file, and there is no need to look at all the data. Note If you need to access most of the rows, sequential reads are much faster because at this point we avoid disk searches.

The indexes are divided into three categories:

Index----Normal indexes, data can be duplicated

Unique----single index, unique index, requiring all records to be unique

Primary key----PRIMARY key index, that is, the corresponding column must be the primary key on the basis of the unique index

The index is used in MySQL for <,<=,=,>,>=,between,in and like statements that do not begin with% _.

Two, the principle

　　 1, the single table data is too small, the index will affect the speed

After the 2,where conditions, order by, and group BY, etc. are filtered, the following fields are best indexed. According to the actual situation, choose primary KEY, UNIQUE, index index, but not the more the better, to moderate

3, union query, subquery and other multi-table operations when the connection field to be indexed

MySQL understanding index, add index principle