Talking about the index design principles of mysql and the differences between common indexes, mysql Design Principles

Talking about the index design principles of mysql and the differences between common indexes, mysql Design Principles

Index definition: a separate database structure stored on a disk. It contains reference pointers to all records in a data table.

Database index design principles:

To make indexes more efficient, you must consider the fields on which indexes are created and what types of indexes are created.
What is the index design principle?

1. Select a unique index

The value of a unique index is unique and can be used to determine a record more quickly.
For example, the student ID in the student table is a unique field. Creating a unique index for this field can quickly determine the information of a student.
If the name is used, the same name may exist, thus reducing the query speed.

2. Create an index for fields that often require sorting, grouping, and Union operations

Fields that require operations such as order by, group by, DISTINCT, and UNION are often time-consuming.
If you create an index for it, you can effectively avoid sorting operations.

3. Create an index for fields that are often used as query Conditions

If a field is often used as a query condition, the query speed of this field affects the query speed of the entire table. Therefore,
Creating indexes for such fields can improve the query speed of the entire table.

4. Limit the number of Indexes

The more indexes, the better. Each index occupies disk space. The more indexes, the larger the disk space required.
It is difficult to reconstruct and update indexes when modifying tables. The more indexes, the more time it takes to update the table.

5. Try to use indexes with a small amount of data

If the index value is long, the query speed will be affected. For example, full text for a CHAR (100) type field
It takes more time to retrieve a CHAR (10) field.

6. Try to use the prefix for indexing.

If the index field value is long, it is best to use the value prefix to index. For example, TEXT and BLOG fields are used for full-TEXT search.
It will be a waste of time. If you retrieve only the first several characters of a field, the search speed can be improved.

7. Delete indexes that are no longer in use or rarely used

After the data in the table is updated in large quantities or the usage of the data is changed, some of the original indexes may no longer be needed. Database Management
Members should regularly identify and delete these indexes to reduce the impact of indexes on update operations.

8. Indexes should not be created for small tables. When a large number of columns are contained and no non-null values need to be searched, you can choose not to create indexes.

----------------------------------------------------------

Basic knowledge about mysql indexes:

1. Fields frequently used to filter records.

1. primary key field. The system automatically creates a primary key index;
2. The unique key field. The system automatically creates the corresponding index;
3. the field defined by the foreign key constraint as the foreign key;

4. fields used to connect to the table in the query;

5. It is often used as the benchmark field for sorting (order by field;

2. Indexes occupy disk space, and creating unnecessary indexes is a waste.

3. Data operations must be taken into account when creating indexes.

1. The content is rarely changed and often queried. It is not called to create more indexes for it;

2. Regular, regular table changes require careful creation of necessary indexes;

Iv. Differences between primary key and unique key

1. The domain/domain group used as the Primary Key cannot be null. The Unique Key is acceptable.

2. A table can have only one Primary Key, and multiple Unique keys can exist at the same time.

The bigger difference lies in the logic design. The Primary Key is generally used as the record identifier in the logic design, which is also set
The original intention of Primary Key, and Unique Key is only to ensure the uniqueness of the domain/domain group.

V. Compound index and single index

Composite index refers to multi-field joint index. During query, these fields are often combined as conditions before query.

The unique index mainly uses the primary key ID index, and the storage structure order is consistent with the physical structure.

For example, create index idx on tbl (a, B)

Sort by a first, and sort by B in the same order as a. so when you query a or AB,

This index can be used. However, when you only query B, the index does not help you much. You may be able to skip the search.

---------------------------------------------

Add and delete indexes:

1. The table's primary key and foreign key must have an index;

2. Indexes should be available for tables with a data volume of more than million;

3. For tables that are often connected to other tables, indexes should be created on the connection fields;

4. fields that frequently appear in the Where clause, especially those in large tables, should be indexed;

5. The index should be built on highly selective fields;

6. Indexes should be built on small fields. Do not create indexes for large text fields or even extra-long fields;

7. The establishment of a composite index requires careful analysis. Try to replace it with a single field index:

A. correctly select the primary column field in the composite index, which is generally A highly selective field;

B. Do the fields of the composite index often appear in the Where clause in the AND mode? Are there very few or even no single-field queries? If yes, a composite index can be created; otherwise, a single field index is considered;

C. If the fields contained in the composite index are often contained in the Where clause, they are divided into multiple single-field indexes;

D. If the composite index contains more than three fields, consider the necessity and reduce the composite fields;

E. If there are both single-field indexes and composite indexes on these fields, you can delete composite indexes;

8. Do not create too many indexes for tables that frequently perform data operations;

9. Delete useless indexes to avoid negative impact on the execution plan;

The above are some general judgment bases for indexing.In a word, the establishment of indexes must be careful. The necessity of each index should be carefully analyzed and a basis should be established. Because too many indexes and inadequate or incorrect indexes have no benefits for performance: Each index created on a table increases storage overhead, indexes also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes generally have no value when there is a single-field index. On the contrary, they also reduce the performance when data is added and deleted, especially for frequently updated tables, the negative impact is greater.

The above discussion about the index design principles of mysql and the differences between common indexes are all the content that I have shared with you. I hope to give you a reference and support for the customer's house.