Schema optimization and indexing

An index is a data structure. It can help MySQL get data more efficiently. It's a decisive factor for good performance, but people often forget to use them or don't understand them correctly, so in real-world examples, indexes always cause performance problems. That's why we put the index in the earlier part of the book. Even put the index chapter before the statement optimization.

Indexes (also called keys in MySQL) are especially important when the data is getting bigger. Small amount of data, no high load, in the absence of indexes, the database can also perform well, but as the volume of data, performance degradation is very powerful.

The easiest way to understand the MySQL index is to think about the index of the book. You can easily find what you need through the index of the book. And you can know the page number of the content.

MySQL uses indexes in a similar way. It looks up the index data structure of the value. When it finds a match, it can find the row that contains the match. If you run the statement:

Mysql> SELECT first_name from sakila.actor WHERE actor_id = 5;

There will be an index on the actor_id, so MySQL uses the index to find rows with actor_id 5. In other words, it looks up the value through the index and returns any rows that contain the specified values.

An index contains values from columns or columns that are specified in a table. If you index more than 1 columns, the order of the columns is particularly important. Because MySQL only efficiently searches the index of the leftmost prefix. You will see that it is different to create an index on two columns and create separate indexes for two columns.