MySQL understands the indexing and indexing principles

MySQL understands the indexing and indexing principles

[Understanding indexes]

To understand indexes, you must first understand how data is stored on the hard disk. Different storage engines may adopt different measures. For example, the MySQL client uses MyISAM by default, and the engine creates separate files for each table.

Whether or not a separate file is created for each table, the operating system reads data from the hard disk to the memory, which is always in the unit of page. Therefore, to obtain a specific record, you must read the page containing the record.

When obtaining a record, MySQL can use two different methods: one-by-one scan or index access. Assume that the first method is used to obtain records with the id of 1234, and the first 1233 records must be accessed sequentially and sequentially. Besides, I/O overhead of each data page read must be considered. If an index is used, you can quickly read the target page to obtain the target Record Based on the page indicated by the index and the location of the record on the page.

Except for getting specific rows, using hash is very convenient. In other cases, both (by default) use B tree to build indexes. Tree B is a balanced multi-Cross-tree. The number of records stored on each node depends on the space occupied by the value. This is the same as the number of records stored on each data page. The values in the node are arranged in non-descending order, and the values in the node are always less than or equal to the values in the nodes pointing to it.

When MySQL uses the B-tree to construct an index, the leaf points to a specific page and record. And a leaf has a pointer pointing to the next leaf.

Note the following before using indexes:

(1) only set indexes for the fields to be queried by where and order by to avoid meaningless hard disk overhead;

(2) composite indexes support prefix indexes;

(3) When updating a table, MySQL will automatically update the index to maintain a tree balance if adding or deleting records;

I. Importance of Indexes

The index is used to quickly find rows with a specific value in a column. If no index is used, MySQL must start with 1st records and read the entire table until related rows are found. The larger the table, the more time it takes. If the column to be queried in the table has an index, MySQL can quickly find the data file in the middle of a location, and there is no need to read all the data. Note that if you want to access most rows, sequential reading is much faster, because disk search is avoided.

There are three types of indexes:

Index ---- normal index, data can be repeated

Unique ---- unique index, which must be unique for all records

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

MySQL uses indexes for <, <=, =,>,> =, BETWEEN, IN, and like statements not starting with %.

Ii. Principles

1. If there is too little data in a single table, indexing will affect the speed.

2. When filtering conditions such as 'ORDER BY' and 'group by' after 'where', it is best to add an index to the following fields. Select indexes such as primary key, UNIQUE, and INDEX based on the actual situation, but the more indexes, the better.

3. join queries, subqueries, and other multi-Table operations require indexed joined Fields

This article permanently updates the link address: