Index, can effectively improve the efficiency of our database search, all kinds of database optimization in the article there are related knowledge points to back, but the simple entry is actually very easy to forget.

So Songo would like to talk about the proper use of indexes in a few articles, combined with some specific examples to help you understand index optimization. This is a small series, there may be several articles, today is the first article.

1. The index column is independent

When we use indexed columns as search criteria, we need to make sure that the index is not in the expression and does not contain various operations in the index.

Let me take a simple example. Suppose I have the following table:

SQL > alter table user select * from user where user = ‘user’;

Let’s compare the following two queries:

You can see:

  1. The first type is ALL to indicate a full table scan (no index). The second type is ref, indicating that the data is searched through the index. In general, when equivalent matching occurs, the type is ref.
  2. The second key specifies which index MySQL uses to optimize the query; Rows shows the number of rows MySQL has to read in order to find the desired value.
  3. The first Extra isUsing whereThis means that the search needs to be judged (filtered) at the server layer, meaning that the storage engine layer cannot return data that meets the criteria (of course, there is no need to return the table, because there is no index).

MySQL does not automatically parse the first expression, resulting in the failure of the first index, although age-1=98 and age=99 are logically the same. Therefore, we should not write expressions in where conditions, not just those above, but also expressions that use built-in functions. We should try to simplify where conditions as much as possible.

Select * from user where birthday is the last year; select * from user where birthday is the last year;

In this figure, I give two different query ideas:

  1. If birthday plus a year is greater than the current time, the user was born within the last year.
  2. If the current date is subtracted from a year and the time is less than birthday, birthday is in a year.

According to the above explain results, it is obvious that the first solution does not use the index, and carries out the full table scan; The second scheme uses an index and reads only two rows. MySQL is unable to use the index because the first method performs a function on the index column.

2. Overwrite indexes skillfully

In general, we do not recommend using SELECT * directly in queries. There are many problems with using SELECT *, one of which is the inability to override a scan using an index.

So I want you to understand what an overwrite index is.

What is a “back table” in MySQL? In this article, Songo tells us that indexes can be divided into clustered indexes and non-clustered indexes according to the physical storage mode.

What we call primary key indexes are Clustered indexes; All indexes except the primary key Index are called non-primary key indexes. Non-primary key indexes are also called Secondary indexes or Secondary indexes.

For both primary and non-primary key indexes, the data structure is B+Tree. The only difference is that the contents stored in the leaf nodes are different:

  • The leaf node of the primary key index stores a complete row of data.
  • Leaf nodes that are not primary key indexes store primary key values and index column values.

That’s the big difference between the two.

Therefore, if a non-primary key index is used in the search, two B+ trees will be searched. The first search for B+Tree will get the primary key value and then search for B+Tree with the primary key index. This process is called table-back. However, if the search field happens to be on the leaf of the secondary index, is there no need to return to the table? So let’s verify that.

Suppose I have the following table:

CREATE TABLE `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `username` (`username`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

Id is the primary key, username and address are compound indexes.

This table has three entries:

Let’s do a simple test, starting with the following SQL:

 explain select username,address from user2 where username='javaboy';
Copy the code

In this SQL query, we query the fields username and address. Because these two fields are compound indexes, they are stored in the leaf node of the secondary index B+Tree. After searching for username, we can get the value of address, so there is no need to query back to the table. Notice that Using index in Extra means just that.

Using index refers to the use of index overwrite scan to return records. Filter unwanted records directly from the index and return hit results. This is done at the MySQL server layer, but without going back to the table to query records.

SQL > alter table select * from table where id = 1; SQL > alter table select * from table where id = 1;

explain select username,address,id from user2 where username='javaboy';
Copy the code

And you can see that’s what we thought.

What if I add gender? If gender is not stored in the leaf node of the secondary index, then we need to return the table query:

explain select gender from user2 where username='javaboy';
Copy the code

Mysql > alter table Extra; mysql > alter table Extra; mysql > alter table Extra;

This is overwrite index, skillfully use overwrite index, can avoid back table, improve query efficiency. Avoid select * at this point (because it is generally not possible to create a composite index for all fields).

Ok, I don’t know if you can understand it, next article we continue ~