Detailed explanation of how to use mysql to create indexes and analysis of advantages and disadvantages_MySQL

Foreword

Index is a data structure that helps MySQL obtain data efficiently. It's critical to high performance, but it's often forgotten or misunderstood. Indexes become more important when the data is larger. A database with a small scale and light load can have good performance even without indexes, but when the data increases, the performance will drop quickly.

Why create an index?

This is because creating indexes can greatly improve the performance of the system.

First, by creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.

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

Third, it can speed up the connection between tables, which is particularly meaningful in achieving referential integrity of data.

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

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

Some people may ask: There are so many advantages to adding indexes, why not create an index for every column in the table? Although this idea is reasonable, it is also one-sided. Although indexes have many advantages, it is very unwise to add an index to every column in the table.

This is because increasing indexes also has many disadvantages:

First, creating and maintaining indexes takes time, and this time increases as the amount of data increases.

Second, indexes need to occupy physical space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If you want to create a clustered index, the space required will be larger.

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

What kind of fields are suitable for creating indexes:

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

In general, indexes should be created on these columns, for example:

First, you can speed up the search on columns that are often searched;

Second, on the column that is the primary key, enforce the uniqueness of the column and the arrangement structure of the data in the organization table;

Third, for columns that are often used in connections, these columns are mainly foreign keys, which can speed up the connection;

Fourth, create an index on columns that often need to be searched based on range, because the index has been sorted and its specified range is continuous;

Fifth, create an index on the columns that often need to be sorted, because the index has been sorted, so the query can use the sorting of the index to speed up the sorting query time;

Sixth, create indexes on columns frequently used in WHERE clauses to speed up the judgment of conditions.

Creating an index is generally based on the where condition of select. For example: the condition of select is wheref1andf2, then it is useless if we index the resume on field f1 or field f2. It is only useful to create an index on fields f1 and f2 at the same time. wait.

What kind of fields are not suitable for index creation:

Also, there are some columns for which indexes should not be created. Generally speaking, these columns that should not be indexed have the following characteristics:

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, they are indexed or not indexed,

It does not improve query speed. On the contrary, due to the addition of indexes, the maintenance speed of the system is reduced and the space requirements are increased.

Second, indexes should not be increased for columns with few data values. This is because, since these columns have very few values, such as the gender column of the personnel table,

In the results of the query, 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 need to be searched in the table.

Increasing the index does not significantly speed up retrieval.

Third, indexes should not be added to columns defined as text, image and bit data types. This is because the data volume of these columns is either quite large or has very few values.

Fourth, when the modification performance is far greater than the retrieval performance, the index should not be created. This is because modification performance and retrieval performance are contradictory to each other.

When increasing the index, the retrieval performance will be improved, but the modification performance will be reduced. When reducing indexes, modification performance will increase and retrieval performance will decrease.

Therefore, when modification performance is much greater than retrieval performance, indexes should not be created.

How to create an index:

1. Create an index, for example createindex6bdc9436bb2f0d48acfe1b965ab3a1d0ontable_name (list of columns);

2. Modify the table, such as altertabletable_nameaddindex [name of index] (list of columns);

3. Specify the index when creating the table, such as createtabletable_name([...],INDEX[name of index](list of columns));

How to view the index in the table:

showindexfromtable_name;View index

Types of indexes and creation examples:

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 (normal 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, query speed can be improved for tables with a large number of records. However, indexes take up space, so you can refer to this article when building an index, which may be helpful to you.