Index: An index is a data structure used by the storage engine to quickly locate records. Again, this is the basic function of the index.
Indexes are important for good performance, especially when the volume of data is increasing, and good indexing design becomes increasingly important. If the index design is unreasonable, performance will decrease.
Index optimization should be the most effective means of query optimization, the index can easily improve query performance several orders of magnitude. When you modify an index, you typically need to rewrite the query.
Working mechanism of the index:
For example: SELECT * FROM student where id = 5;
If an index is created on the ID of the student table. When you look up a record, the id=5 row is found on the index, and the corresponding data row is found based on the matching index record.
An index can contain the values of one or more columns, and the order of the columns is important when you include values for multiple columns, because MySQL can only use the leftmost prefix column efficiently.
It is very different to create an index that contains two columns and to create two indexes that contain only one column.
Type of index:
Indexes are implemented at the storage engine layer rather than at the server level, so there is no uniform standard for indexes, and different storage engines have different underlying implementations for indexes.
1) B-tree index: Use the B-TREE data structure to store the information, which means that all values are stored sequentially. B-trees can speed up data access because the storage engine no longer needs to retrieve the required data from a full-table scan, but instead starts searching from the root node of the index. The parent node stores the value of the node and a pointer to the child node, and the storage engine selects the appropriate pointer to the next level node by comparing the value of the node and the value to find, eventually either finding the data or the data does not exist.
Because B-Tree indexes are ordered, they can be used for order by operations in queries in addition to query by value, and can be used to sort the index definitions.
Limit for B-Tree indexes: ① You cannot use an index if you do not start by the leftmost column of the index. For example, to create an index created in the order of three fields of last name, first name, date of birth, this index cannot be used to find the person named Makai or a particular date, because neither column is the leftmost column of the index. Similarly, you cannot find the person whose last name ends with a letter;
② cannot skip columns in the index. For example, if you look for a person whose surname is Smith and was born in 94, only the first column of the index is used.
③ If a query contains a query scope for a column, none of its right columns can use index-optimized lookups. For example: query where last_name = ' Smith ' and first_name like ' j% ' and birthday = ' 1976-11-11 ', this query will only use the first two columns of the index.
Therefore, the order of the indexes is very important.
1. Index of database optimization
