A brief introduction to the index design principles of MySQL and the differences between common indexes

The following editor will bring you a brief discussion of mysqlindexdesign principlesand the differences between common indexes. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

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

Design principles for database indexes:

In order to make the use of indexes more efficient, when creating an index, you must consider which fields to create the index on and what type of index to create. index.
So what are the index design principles?

1. Select a unique index

The value of the unique index is unique, and a record can be determined more quickly through the index.
For example, the middle school ID in the student table is a unique field. Establishing a unique index for this field can quickly determine a student's information.
If you use a name, there may be the same name, which will slow down the query speed.

2. Create indexes for fields that often require sorting, grouping and union operations

For fields that often require operations such as ORDER BY, GROUP BY, DISTINCT and UNION, the sorting operation will A lot of time wasted.
If you create an index for it, you can effectively avoid the sort operation.

3. Create indexes 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 will affect the query speed of the entire table. Therefore,
Creating an index for such a field can improve the query speed of the entire table.

4. Limit the number of indexes

The more indexes, the better. Each index requires disk space. The more indexes, the more disk space is required.
When modifying the table, it is troublesome to reconstruct and update the index. The more indexes, the more time-consuming it becomes to update the table.

5. Try to use an index with a small amount of data

If the index value is very long, the query speed will be affected. For example, a full-text search for a CHAR (100) type field will definitely take more time than a CHAR (10) type field.

6. Try to use prefixes to index

If the value of the index field is very long, it is best to use the prefix of the value to index. For example, full-text search for TEXT and BLOG type fields will be a waste of time. If only the first few characters of the field are retrieved, the retrieval speed can be improved.

7. Delete indexes that are no longer used or are rarely usedAfter the data in the table is heavily updated, or the way the data is used is changed, some of the original indexes may no longer be needed. Database administrators should regularly find these indexes and delete them to reduce the impact of the indexes on update operations.

8. Small tables should not be indexed; when they contain a large number of columns and do not need to search for non-null values, you can consider not building an index

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

mysql indexRelated tips:

1. Fields often used to filter records .

1. primary key field, the system automatically creates the index of the primary key; 2. unique key field, the system automatically creates the corresponding index;

3. foreign key constraint Fields defined as foreign keys;

4. Fields used to connect tables in queries;

5. Fields often used as the basis for sorting (order by fields);

2. Indexes will occupy disk space, and creating unnecessary indexes will only cause waste.

#3. The creation of indexes must consider the way the data is operated.

1. The content rarely changes and is often queried, so it doesn’t matter if you create a few more indexes for it;

2. Tables that change frequently and routinely For example, you need to carefully create the necessary indexes;

4. The difference between primary key and unique key

1. As Primary The domain/domain group of Key cannot be null. And Unique Key can.

2. There can only be one Primary Key in a table, and multiple Unique Keys can exist at the same time.

The bigger difference is in the logical design. Primary Key is generally used as a record identifier in logical design. This is also the original intention of setting
Primary Key, while Unique Key is only to ensure the uniqueness of the domain/domain group.

5. Composite index and single index

Composite index refers to a multi-field joint index. These fields are often combined when querying Query the conditions again

The unique index is mainly indexed by the primary key ID, and the storage structure sequence is consistent with the physical structure

For example: create index idx on tbl(a,b)

Sort by a first, and similarly sort a by b, so when you check a or ab,

can use this index. But when you only check b, the index is not very helpful to you. .Maybe you can jump to search.

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

Instances of adding and deleting indexes:

1. The primary key of the table, Foreign keys must have indexes;

2. Tables with data volume exceeding 300w should have indexes;

3. Tables that are often connected to other tables must be connected before Indexes should be established on the fields;

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

5. Indexes It should be built on highly selective fields;

6. Indexes should be built on small fields. For large text fields or even super long fields, do not build indexes;

7. Composite index The establishment requires careful analysis; try to consider using a single-field index instead:

A. Correctly select the main column field in the composite index, which is generally a field with better selectivity;

B. Do several fields of a composite index often appear in the Where clause in an AND manner at the same time? Are there few or no single field queries? If so, you can create a composite index; otherwise, consider a single-field index;

C. If the fields included in the composite index often appear alone in the Where clause, break it into multiple single-field indexes;

D. If the compound index contains more than 3 fields, carefully consider the necessity and consider reducing the number of compound fields;

E. If there are both single-field indexes and these several Composite indexes on fields can generally be deleted;

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

9. Delete useless indexes to avoid executing Plan to have a negative impact;

The above are some common judgments when establishing an index. In a word, the establishment of indexes must be cautious, and the necessity of each index should be carefully analyzed and there must be a basis for establishment. Because too many indexes and insufficient or incorrect indexes are not beneficial to performance: each index created on the table will increase storage overhead, and the index will also increase processing overhead for insert, delete, and update operations. In addition, too many compound indexes are generally of no value when there are single-field indexes; on the contrary, they will also reduce the performance when data is added and deleted, especially for frequently updated tables, the negative impact is even greater big

The above is the detailed content of A brief introduction to the index design principles of MySQL and the differences between common indexes. For more information, please follow other related articles on the PHP Chinese website!