MySQL database index

MySQL database index

Advantages of creating an index in MySQL:

1. Greatly speed up data retrieval;

2. Create a unique index to ensure the uniqueness of each row of data in the database table;

3. Accelerate the connection between tables;

4. When you use grouping and sorting clauses to retrieve data, you can significantly reduce the time for grouping and sorting in queries.

Disadvantages of indexing:

1. Indexes occupy physical space.

2. When adding, deleting, and modifying data in a table, the index must also be dynamically maintained, reducing the Data Maintenance speed.

When should I create an index:

1. You can speed up the search on columns that frequently need to be searched;

2. Force the uniqueness of the column as the primary key and the data arrangement structure in the organization table;

3. Frequently Used 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;

5. 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.

Features of indexes should not be created:

1. 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 is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.

2. Indexes should not be added to columns with 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, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed.

3. Indexes should not be added for columns defined as text, image, or bit data. This is because the data volume of these columns is either large or small, which is not conducive to the use of indexes.

4. When the modification performance is much higher 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 modification operations are far more than search operations, you should not create an index.

How to create an index for MYSQL:

Three common indexes: unique index, primary key index, and clustered index.

1. Add the primary key (primary key index)

Mysql> alter table 'table _ name' add primary key ('column ')

2. add UNIQUE (UNIQUE index)

Mysql> alter table 'table _ name' add unique (
'Column'
)

3. Add an INDEX (Common INDEX)

Mysql> alter table 'table _ name' add index index_name ('column ')

4. Add FULLTEXT (full-text index)

Mysql> alter table 'table _ name' add fulltext ('column ')

5. Add multi-column Indexes

Mysql> alter table 'table _ name' add index index_name ('column1 ', 'column2', 'column3 ')

# The ideal of life is for ideal life! #

This article permanently updates the link address: