What is the cost of creating, deleting and using Mysql indexes?

1. Creation and deletion of indexes in Mysql

The innodb and myisam engines will automatically create indexes for primary keys or columns with UNIQUE attributes.

If you want to index other columns, you need to indicate it explicitly.

1. Create an index when creating a table

CREATE TABLE 表名 (
    各个列的信息...,
    (KEY/INDEX) 索引名 (需要被索引的单个列或多个列)
)

Note that either KEY or INDEX can be used as synonyms.

For example, when creating the index_demo table, add a joint index for the c2 and c3 columns:

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);

The name of the index created here is idx_c2_c3. Although the index name can be arbitrarily named, it is recommended to use idx_ is the prefix, followed by the column name of the index to be resumed, and multiple columns are separated by underscores.

2. Add an index when modifying the table structure

ALTER TABLE 表名 ADD (KEY/INDEX) 索引名 (需要被索引的单个列或多个列);

3. Delete the index when modifying the table structure

ALTER TABLE 表名 DROP (KEY/INDEX) 索引名;

Now delete the initially built index idx_c2_c3, you can write like this:

ALTER TABLE index_demo DROP INDEX idx_c2_c3;

2. The cost of index

Although indexes are a good thing, they cannot be created arbitrarily.

1. The cost of space

This is very obvious.

• Every time an index is created, a B-tree must be built for it.

• Each node of each B-tree is a data page, which takes up 16kb of storage space by default.

• A large B-tree is composed of many data pages.

So, this will take up a lot of storage space.

2. Time cost

Time-consuming to maintain the sequence

Whenever adding, deleting or modifying data in the table, each B-tree index needs to be modified.

We know:

• B The nodes at each level in the tree form a doubly linked list in ascending order of the index column values.

• Whether it is a leaf node or an internal node, the internal records are in order of the value of the index column, forming a singly linked list.

Therefore, when adding, deleting, or modifying operations, it may cause damage to the sorting of nodes and records. Then the storage engine will need extra time to perform operations such as page splitting and recycling to maintain Sorting of nodes and records.

If a lot of indexes are created, you can imagine that the B-tree corresponding to each index must undergo related maintenance operations, which will bring much consumption.

Time-consuming execution plan analysis

Before executing the query statement, an execution plan will first be generated.

Generally speaking, a query statement uses at most one secondary index during execution. When generating the execution plan, it is necessary to calculate the cost of using different indexes to execute the query, and finally select the lowest index. Inquire.

If too many indexes are created, it may take too much time to perform cost analysis, thus affecting the execution performance of query statements.

The above is the detailed content of What is the cost of creating, deleting and using Mysql indexes?. For more information, please follow other related articles on the PHP Chinese website!