Selection principles of index keywords

Selection principles of index keywords

1. A field in the table is worth the higher degree of discretization. The more suitable the field is for selecting the index keyword. The primary key field and the uniqueness constraint field are suitable for selecting the index keyword because the values of these fields are discrete. Especially when the primary key field creates an index, the value of cardinality (base, set potential) is equal to the number of rows in the table. MySQL considers primary key constraints and uniqueness constraints comprehensively. When a database user creates a primary key constraint, MySQL automatically creates a primary index and the index name is primary. When a database user creates a unique index, mySQL automatically creates a unique index. By default, the index name is the field name of the unique index.

2. fields that occupy less storage space are more suitable for selecting keywords for indexing. For example, an integer field occupies less storage space than a string, so it is more suitable for selecting an index keyword.

3. Fixed fields in the bucket are more suitable for selecting keywords for indexing. Compared with text fields, char fields are more suitable for selecting index keywords.

4. The fields frequently used in the WHERE clause should be indexed, the grouping or sorting fields should be indexed, and the connection fields of the two tables should be indexed.

5. Frequently updated fields are not suitable for creating indexes. fields that do not appear in the WHERE clause should not be created.

6. The leftmost prefix principle.

7. Try to use prefix indexes.

The purpose of introducing indexes is to improve the data check efficiency. Therefore, the selection of index keywords is closely related to the SELECT statement. This statement has two meanings: first, the design of the SELECT statement can determine the design of the index, and the design of the index also affects the design of the SELECT statement. For example, principles 1 and 2 can affect the design of select statements. The WHERE clause and group by clause in select statements can also affect the design of indexes. The connection fields of the two tables should be indexed. Once the foreign key constraint is created, MySQL will automatically create the index corresponding to the foreign key, this is because the foreign key field is usually the connection field of two tables.

Composite Index has another advantage, which is embodied by the concept of leftmost prefixing. Assume that a composite index (the index name is fname_lname_address) is created for multiple fields in a table (such as fristname, lastname, and address ). when the where query condition is a combination of the following fields, MySQL uses the fname_lname_address index. In other cases, the fname_lname_address index cannot be used. It can be understood that a composite index (firstname, lastname, address) is equivalent to three indexes (firstname, llastname, age), (firstname, lastname), and (firstname. Based on the prefix principle, try to avoid duplicate indexes. For example, after the fname_lname_address index is created, you do not need to create an index on the first_name sub-segment.

 

If the storage engine of the database table is MyISAM, MySQL automatically creates a primary key index when the primary key constraint is created. If the storage engine of the database table is InnoDB, MySQL automatically creates a clustered index when the primary key constraint is created.

MySQL also supports full-text indexing (Fulltext). When querying string information with a large amount of data, full-text indexing can greatly improve the string retrieval efficiency. Note that full-text indexes can only be created on char, varchar, or text string fields, and full-text indexes do not support prefix indexes.