Why create an index?
This is because creating indexes can greatly improve the performance of the system.
First of all, 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 the retrieval of data, which is also the main reason for creating indexes.
Third, it can speed up the connection between tables, which is particularly meaningful in realizing the referential integrity of data.
Fourthly, when using the grouping sort clause for data retrieval, it can also significantly reduce the time of grouping sort in the query.
Fifth, by using the index, we can use the optimized hidden device to improve the performance of the system during the query process.
Some people may ask: adding an index has so many benefits, why not create an index for each column in the table? This idea is reasonable, but it is also one-sided. Although indexes have many advantages, it is unwise to add indexes to every column in a table.
This is because increasing the index also has many disadvantages:
First of all, it takes time to create and maintain indexes, which increases with the increase of data volume.
Second, the index needs to occupy physical space. In addition to the data table occupying data space, each index also occupies a certain physical space. If you want to build a clustered index, you need more space.
Thirdly, when adding, deleting and modifying the data in the table, the index should also be maintained dynamically, which reduces the speed of data maintenance.
Which fields are suitable for indexing:
Indexes are built on some columns of database tables. Therefore, when creating an index, you should carefully consider which columns can be indexed and which columns cannot be indexed.
In general, you should create indexes on these columns, for example:
First, you can speed up the search on the columns that need to be searched frequently;
Second, on the column as the primary key, force the uniqueness of the column and the arrangement structure of the data in the organization table;
Third, in the columns that are often used for connection, these columns are mainly foreign keys, which can speed up the connection;
Fourth, create indexes on columns that often need to be searched by range, because indexes have been sorted and their specified ranges are continuous;
Fifth, create an index on the column that needs to be sorted frequently, because the index has been sorted, so that the query can use the sorting of the index to speed up the sorting query time;
Sixthly, create indexes on the columns frequently used in the WHERE clause to speed up the judgment of conditions.
Generally, the index is established according to the where condition of select, for example, the condition of select is where f 1 and f2, so it is useless for us to index resumes in field f 1 or field f2, and it is only useful to index resumes in field f 1 and f2 at the same time.
Which fields are not suitable for indexing:
Similarly, indexes should not be created for some columns. Generally speaking, these columns that should not be indexed have the following characteristics:
First, you should not create indexes on columns that are rarely used or referenced in queries. This is because, because these columns are rarely used, they are indexed or not.
It didn't improve the query speed. On the contrary, due to the increase of index, the maintenance speed of the system is reduced and the space requirement is increased.
Second, indexes should not be added to columns with few data values. This is because these columns have few values, such as the gender column 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 data rows that need to be searched in the table account for a large proportion.
Increasing the index can't obviously speed up the retrieval.
Third, indexes should not be added to columns defined as text, p_w_picpath and bit data types. This is because the amount of data in these columns is either quite large or has few values.
Fourth, when the modification performance is much greater than the retrieval performance, references should not be created. This is because the modification performance and retrieval performance are contradictory.
When the index increases, the retrieval performance will improve, but the modification performance will decrease. When the index decreases, the modification performance will increase and the retrieval performance will decrease.
Therefore, when the modification performance is far greater than the retrieval performance, the index should not be created.
How to create an index:
1, create an index, such as table_name (list of columns);
2. Modify the table, such as alter table _ name add index [(list of columns)];
3. Specify an index when creating a table, such as create table table _ name ([...], index[ name of index] (list of columns);
How to view indexes in a table:
Displays the index of table_name; View index
Index types and creation examples:
1. Primary key (primary key index)
Mysql & gtalter table table_name Add primary key (column).
2. Unique or unique key (unique index)
mysql & gtalter table table _ name add unique(` column `)
3. Full text (full text index)
mysql & gtalter table table _ name add full text(` column `)
4. Index (general index)
mysql & gtalter table table _ name add index index _ name(` column `)
5. Multi-column index (clustered index)
mysql & gtalter table ` table _ name ` add index index _ name(` column 1 `，` column2 `，` column3 `)
To modify an index in a table:
Alter table tablename deletes the primary key and adds the primary key (fileda, filedb).