Describes how to use mysql to create an index and its advantages and disadvantages.

Describes how to use mysql to create an index and its advantages and disadvantages.

Preface

Index is a data structure that helps MySQL efficiently obtain data. It is critical for high performance, but people often forget or misunderstand it. The more data the index is, the more important the index is. Databases with small sizes and low loads can have good performance even if they do not have indexes. However, when data increases, the performance decreases rapidly.

Why create an index?

This is because creating an index can greatly improve the system performance.

First, you can create a unique index to ensure the uniqueness of each row of data in the database table.

Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.

Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity.

Fourth, when using grouping and sorting clauses for data retrieval, it can also significantly reduce the time for grouping and sorting in queries.

Fifth, by using indexes, you can use the optimizer during the query process to improve system performance.

Some may ask: why not create an index for each column in the table because increasing Indexes has so many advantages? Although such an idea has its own rationality, it also has its own one-sidedness. Although indexes have many advantages, it is unwise to add indexes to every column in the table.

This is because adding Indexes has many disadvantages:

First, it takes time to create and maintain indexes. This time increases with the increase of data volume.

2. Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. If you want to create a clustered index, you need more space.

Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.

What fields are suitable for creating indexes:

Indexes are created on certain columns in the database table. Therefore, when creating an index, you should carefully consider which columns can create an index and which Columns cannot create an index.

In general, you should create an index on these columns, for example:

First, you can speed up the search on columns that frequently need to be searched;

Second, in the column as the primary key, force the uniqueness of the column and the data arrangement structure in the organization table;

Third, these columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up the connection;

4. Create an index on a column that often needs to be searched by range. The specified range is continuous because the index has been sorted;

Fifth, create an index on the columns that frequently need to be sorted. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;

6. Create an index on the columns in the WHERE clause frequently to speed up condition judgment.

Index creation is generally based on the where condition of select. For example, if the select condition is wheref1andf2, it is useless to create a resume index on the f1 or f2 field, it is useful only when both the f1 and f2 fields are indexed.

What fields are not suitable for index creation:

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following features:

First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there are indexes or no indexes,

Does not increase the query speed. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.

Second, indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table,

In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, a large proportion of the data rows to be searched in the table.

Adding indexes does not significantly accelerate the search speed.

Third, indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small.

Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent.

When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced.

Therefore, when the modification performance is much higher than the retrieval performance, you should not create an index.

How to create an index:

1. Create an index, for example, createindex <index Name> ontable_name (column list );

2. modify a table, such as altertabletable_nameaddindex [index name] (column list );

3. Specify an INDEX when creating a table, for example, createtabletable_name ([...], INDEX [INDEX name] (column list ));

How to view indexes in a table:

Showindexfromtable_name; view the index

Index type and creation example:

1. PRIMARYKEY (primary key index)

mysql>altertabletable_nameaddprimarykey(`column`)

2. UNIQUE or UNIQUEKEY (UNIQUE index)

mysql>altertabletable_nameaddunique(`column`)

3. FULLTEXT (full-text index)

mysql>altertabletable_nameaddfulltext(`column`)

4. INDEX (Common INDEX)

mysql>altertabletable_nameaddindexindex_name(`column`)

5. Multi-column index (clustered index)

mysql>altertable`table_name`addindexindex_name(`column1`,`column2`,`column3`)

Modify the index in the table:

altertabletablenamedropprimarykey,addprimarykey(fileda,filedb)

Summary

With indexes, the query speed can be improved for tables with a large number of records. However, indexes occupy space. Therefore, you can refer to this document when creating indexes, which may be helpful to you.