Database index Principle

Database index Principle

An index is a structure that sorts the values of one or more columns in a database table (for example, the last name (lname) column of the employee table. If you want to search for him or her by the name of a specific employee, the index will help you get information faster than searching all rows in the table.

The database index uses the B + tree. If you query the content of two tables:

Select * from a, B where a. id = B. id;

You only need to create an index on the id of Table B. Because there is no condition for "Left Table a" in the query, it is all output, so the index of Table a is not used;

A table creates a composite index for two columns. Only when both columns are used, the index is taken. Otherwise, the index will not be taken. However, creating a single index will occupy more space.