Detailed analysis of Mysql indexing using methods and advantages and disadvantages of _mysql

Objective

Index is the data structure that helps MySQL get data efficiently. It is critical for high performance, but people often forget or misunderstand it. Indexes are more important when the data is larger. Small, lightweight databases can have good performance even without indexes, but when data is increased, performance is reduced very quickly.

Why do you want to create an index?

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

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

Second, you can greatly speed up the retrieval of data, which is the main reason to create indexes.

Third, you can accelerate the connection between tables and tables, especially in the realization of the referential integrity of the data is particularly meaningful.

Finally, when you use grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in a query.

Through the use of indexes, the optimization of the hidden device can be used to improve the performance of the system during the query process.

Someone might ask: Why do you not create an index for each column in the table because there are so many advantages to adding an index? This kind of idea certainly has its rationality, but also has its one-sidedness. Although indexes have many advantages, it is unwise to add indexes to each column in the table.

This is because there are a number of downside aspects to adding indexes:

First, it takes time to create indexes and maintain indexes, which increases as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies a certain physical space. If you want to set up a clustered index, the space you need is larger.

Third, when the data in the table to add, delete and modify the time, the index will also be dynamic maintenance, which reduces the data maintenance speed.

What fields are suitable for creating indexes:

Indexes are built on top of some columns in a database table. Therefore, when you create an index, you should carefully consider which columns you can create indexes on, and on which columns you cannot create indexes.

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

First, in the frequently need to search the column, can speed up the search speed;

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

Third, often used in connected columns, these columns are mainly some foreign keys, can speed up the connection speed;

The index is created on columns that often need to be searched according to scope, because the index is already sorted and its specified range is contiguous;

The index is created on columns that often need to be sorted, because the index is sorted so that the query can use the sort of index to speed up the sorting query time;

The speed at which a condition is judged is accelerated by creating an index on a column that is often used in the WHERE clause.

Indexing is generally based on the where condition of the Select, for example: The condition of the select is WHEREF1ANDF2, so if we are not using the CV index on the field F1 or field F2, it is only useful to index the fields F1 and F2 at the same time.

What kind of field is not appropriate to create an index:

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

First, you should not create indexes 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 improve query speed. On the contrary, because of the addition of indexes, it reduces the maintenance speed of the system and increases the space requirement.

Second, you should not add indexes to columns that have very few data values. This is because, because these columns have very few values, such as the gender column of the personnel table,

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

Adding indexes does not significantly speed up the retrieval.

Third, columns that are defined as text,image and bit data types should not be added to the index. This is because these columns have either a large amount of data or a very small number of values.

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

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

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

How to create an index:

1, create an index, such as the name of the createindex< index >ontable_name (List of columns);

2, modify the table, such as the name of the altertabletable_nameaddindex[index (List of columns);

3. Specify the index when creating the table, such as Createtabletable_name ([...],index[index name] (List of columns));

How to view indexes in a table:

showindexfromtable_name; viewing indexes

Types of indexes and examples of creation:

1.PRIMARYKEY (primary key index)

Mysql>altertabletable_nameaddprimarykey (' column ')

2.UNIQUE or UniqueKey (unique index)

Mysql>altertabletable_nameaddunique (' column ')

3.FULLTEXT (Full-text indexing)

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 ')

To modify an index in a table:

Altertabletablenamedropprimarykey,addprimarykey (Fileda,filedb)

Summarize

With an index, you can increase the query speed for tables with a large number of records. But the index is space-consuming, so it may be helpful for you to refer to this article when indexing is in place.