Usage of mysql database Indexes

Index

You can use indexes to quickly access specific information in the database tutorial table. 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 a specific employee by name, the index will help you get the information faster than all rows in the table that must be searched.

The Index provides pointers to the data values stored in the specified column of the table, and then sorts these pointers according to the sort order you specify. The database uses an index in a similar way as you use an index in a book: it searches for an index to find a specific value, and then returns the pointer to the row containing the value.

In the database graph, you can create, edit, or delete each index type on the index/Key Attribute page of the selected table. When you save the table to which the index is attached or the relational graph of the table is saved, the index is saved in the database. For more information, see create an index.

Note: not all databases use indexes in the same way. For more information, see Database Server considerations or database documentation.
As a general rule, an index must be created on a table only when data in the index column is frequently queried. Indexes occupy disk space and speed up adding, deleting, and updating rows. In most cases, the speed advantage of indexing for data retrieval is much higher than that of indexing.

Index Column
You can create an index based on a single or multiple column in a database table. Multiple-column indexes enable you to differentiate rows with the same value in one of the columns.

If you often search for two or more columns at the same time or sort by two or more columns, the index is also helpful. For example, if you often set a criterion for the first and second columns in the same query, it makes sense to create multiple columns of indexes in these two columns.

Determine the validity of the index:

Check the WHERE and JOIN clauses of the query. Each column in any clause is an object that can be selected by the index.
Test the new index to check its impact on running query performance.
Consider the number of indexes created on the table. It is best to avoid having many indexes on a single table.
Check the definitions of indexes created on the table. It is best to avoid overlapping indexes that contain shared columns.
Check the number of unique data values in a column and compare the quantity with the number of rows in the table. The comparison result is the selectivity of the column, which helps to determine whether the column is suitable for creating an index. If so, determine the index type.
Index type
Based on the functions of the database, you can create three indexes in the Database Designer: unique index, primary key index, and clustered index. For more information about the index functions supported by the database, see the database documentation.

Tip: although the unique index helps to locate information, we recommend that you use primary keys or unique constraints to obtain the best performance results.
Unique Index
A unique index is an index that does not allow any two rows to have the same index value.

When duplicate key values exist in existing data, most databases do not allow you to save the newly created unique index with the table. The database may also prevent adding new data that will create duplicate key values in the table. For example, if the employee's last name (lname) in the employee table creates a unique index, neither employee can have the same name.

Primary Key Index

A database table often has a column or a combination of columns. Its Values uniquely identify each row in the table. This column is called the primary key of the table.

When you define a primary key for a table in the database relationship diagram, the primary key index is automatically created. The primary key index is a specific type of unique index. This index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to data.

Clustered Index

In the clustered index, the physical order of the row in the table is the same as the logic (INDEX) Order of the key value. A table can contain only one clustered index.

If an index is not a clustered index, the physical sequence of the row in the table does not match the logical sequence of the key value. Compared with non-clustered indexes, clustered indexes generally provide faster data access speeds.

 

Establishment Method and precautions

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.

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

);

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. What if you have more than one selection condition? 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;

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 take a look at the EXPLAIN output. 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, give the database 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 ".