Mysql_ advantages and disadvantages of building indexes

http://blog.csdn.net/superit401/article/details/51291603

Advantages and disadvantages of building indexes:

Why do you 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, the speed of data retrieval can be greatly accelerated, which is the main reason for creating indexes.
Thirdly, the connection between tables and tables can be accelerated, especially in terms of achieving referential integrity of the data.
Finally, when using grouping and sorting clauses for data retrieval, it is also possible to significantly reduce the time of grouping and sorting in queries.
By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Perhaps someone will ask: There are so many advantages to adding indexes, why not create an index for each column in the table? Although this kind of thought has its rationality, but also has its one-sidedness. Although indexes have many advantages, it is very unwise to add indexes to each column in a table.

This is because increasing the index also has a number of disadvantages:

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

Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain physical space. If you are building a clustered index, you will need more space.

Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

What fields are appropriate for creating an index:

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 an index on, and on which columns you cannot create an index.

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

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

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

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

IV. Create an index on a column that is often required to search by scope, because the index is sorted and its specified range is continuous;

Create an index on a column that is often ordered, because the index is sorted so that the query can use the sorting of the index to speed up the sorting query time;

The index is often used to create indexes on the columns in the WHERE clause, which speeds up the judgment of the condition.

Indexing is generally established by the Where condition of the Select, for example: The condition of the select is where F1 and F2, so if we have a resume index on the field F1 or field F2, it is only useful to have the index on the field F1 and F2 at the same time.

What fields do not fit to create an index:

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

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

does not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.
Second, you should not increase the index for columns that have only a few data values. This is because these columns have very few values, such as the gender column of the personnel table,

In the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large in scale.

Increasing the index does not significantly speed up the retrieval.
Third, for those columns defined as text, the image and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or has very little value.
The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting.

When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance.

Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.

How to create an index:

1. Create index, such as name of CREATE INDEX < index > on table_name (List of columns);
2, modify the table, such as ALTER TABLE table_name ADD index[index name] (List of columns);
3. Specify the index when creating the table, such as CREATE TABLE table_name ([...], index [name of indexed] (List of columns));

To view the index in a table method:

Show index from TABLE_NAME; View Index

Types of indexes and examples of creation:

1.PRIMARY key (primary key index)

Mysql> ALTER TABLE TABLE_NAME add PRIMARY KEY (' column ')

2.UNIQUE or unique KEY (unique index)

Mysql> ALTER TABLE table_name add unique (' column ')

3.FULLTEXT (full-text index)
Mysql> ALTER TABLE TABLE_NAME ADD FULLTEXT (' column ')

4.INDEX (normal index)
Mysql> ALTER TABLE table_name ADD index index_name (' column ')

5. Multi-column index (clustered index)
mysql> ALTER TABLE ' table_name ' Add index index_name (' Column1 ', ' column2 ', ' column3 ')

Mysql_ advantages and disadvantages of building indexes