Create an index for the MySQL database

Some time ago, a very advancedProgramI was surprised to ask what I call index. I think it will not be a small result, because there are thousands of developers (probably most of them using MySQL) none of them have received formal training on databases. Although they have done some development for customers, they do not know much about how to create appropriate indexes for databases, so I started thinking about writing a related article.

The most common case is to create an index for the field that appears in the WHERE clause. For the sake of convenience, we should first create the following table.

CodeCodeAs follows:

Create Table mytable ( Id serial primary key, Category_id int not null default 0, User_id int not null default 0, Adddate int not null default 0 );

It's easy, but it is enough to explain this problem. If you use statements similar to the following in queries:

Select * From mytable where category_id = 1;

The most direct response is to create a simple index for category_id:

Create index mytable_categoryid
On mytable (category_id );

OK, OK? Don't be happy. What if you have more than one choice? For example:

Select * From mytable where category_id = 1 and user_id = 2;

Your first possible response is to create an index for user_id. No. This is not the best method. You can create multiple indexes.

Create index mytable_categoryid_userid on mytable (category_id, user_id );

Have you noticed my habits in naming? I use "Table name_field 1 name_field 2 name. You will soon know why I did this.

Now you have created an index for an appropriate field. However, it is a bit difficult. You may ask, will the database actually use these indexes? Test it. For most databases, this is very easy. You only need to use the explain command:

Explain

Select * From mytable
Where category_id = 1 and user_id = 2;

This is what calls s 7.1 returns (exactly as I expected)

Notice: query plan:

Index scan using mytable_categoryid_userid on
Mytable (cost = 0. 00 .. 2.02 rows = 1 width = 16)

Explain

The above is the ipvs data. We can see that the database uses an index (a good start) during query, and it uses the second index I created. See the benefits of my naming above. You will immediately know that it uses the appropriate index.

Next, let's make it a little more complex. What if there is an order by clause? Believe it or not, most databases will benefit from the index when using order.

Select * From mytable
Where category_id = 1 and user_id = 2
Order by adddate DESC;

A little confused, right? Just like creating an index for a field in the WHERE clause, it also creates an index for the field in the order by clause:

Create index mytable_categoryid_userid_adddate
On mytable (category_id, user_id, adddate );

Note: "mytable_categoryid_userid_adddate" will be truncated

"Mytable_categoryid_userid_addda"

Create

Explain select * From mytable
Where category_id = 1 and user_id = 2
Order by adddate DESC;

Notice: query plan:

Sort (cost = 2. 03 .. 2.03 rows = 1 width = 16)
-> Index scan using mytable_categoryid_userid_addda
On mytable (cost = 0. 00 .. 2.02 rows = 1 width = 16)

Explain

Let's look at the explain output. It seems a little scary. The database has done more sorting that we don't need. Now we know how the performance is damaged, it seems that we are a little optimistic about the operation of the database itself, so let's give the database a little more tips.

In order to skip the sorting step, we do not need other indexes. We just need to change the query statement slightly. Postgres is used here. We will give the database an extra prompt-Add the field in the where statement to the order by statement. This is only a technical process, and it is not necessary, because in fact, there is no sorting operation on the other two fields, but if you add, postgres will know what it should do.

Explain select * From mytable
Where category_id = 1 and user_id = 2
Order by category_id DESC, user_id DESC, adddate DESC;

Notice: query plan:

Index scan backward using
Mytable_categoryid_userid_addda on mytable
(Cost = 0. 00 .. 2.02 rows = 1 width = 16)

Explain

Now we use the expected index, and it is quite intelligent. We know that we can start to read the index, thus avoiding any sorting.

The above is a little more detailed. However, if your database is huge and your daily page requests reach millions, I think you will benefit a lot. However, if you want to perform more complex queries, such as combining multiple tables for query, especially when the where restriction clause contains fields from more than one table, what should you do? I usually try to avoid this practice, because the database should combine all the items in each table, and then exclude the unsuitable rows, which may cause great overhead.

If it cannot be avoided, you should check each table to be combined and use the above policy to create an index, and then use the explain command to verify whether the expected index is used. If yes, OK. If not, you may need to create a temporary table to combine them and use appropriate indexes.

Note that creating too many indexes will affect the update and insertion speed, because it needs to update each index file as well. For a table that often needs to be updated and inserted, there is no need to create an index for a rarely used where clause. For a small table, the sorting overhead is not very high, there is no need to create another index.

The above is just a few basic things. In fact, there are a lot of knowledge in it. By explaining, we cannot determine whether this method is optimal, each database has its own optimizer. Although it may not be well-developed, they will compare which method is faster during query. In some cases, it may not be faster to create an index. For example, when an index is placed in a non-contiguous bucket, this will increase the read burden on the disk. Therefore, the actual environment should be used to determine which one is the best.

In the beginning, if the table is not large and there is no need to make an index, my opinion is to make an index only when necessary, and some commands can be used to optimize the table, for example, MySQL can use "optimize table ".

To sum up, you should have some basic concepts about how to create an appropriate index for the database.