A brief discussion on MySQL index design principles and the differences between common indexes

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

Database index design principles:

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

1. Select unique index

The value of a unique index is unique, and you can use this index to identify 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 a student's information.
If you use the full name, there may be duplicate names, which will slow down the search.

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

Fields that often require operations such as ORDER BY, GROUP BY, DISTINCT, and UNION will waste a lot of time in sorting operations.
If you create an index for it, you can effectively avoid sorting operations.

3. Create indexes for fields that are often used as query conditions

If a field is frequently 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 you have, the more disk space you need.
When modifying a table, rebuilding and updating the index is troublesome. The more indexes there are, the more time consuming it becomes to update the table.

5. Try to use indexes with less data

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

6. Try to use prefix indexing

If the value of the indexed field is very long, it is better to use a prefix of the value to index. For example, for TEXT and BLOG type fields, full-text search will be a waste of time. If you only search the first few characters of a field, this can increase the search speed.

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

When the data in the table is updated in large quantities or the way the data is used is changed, some of the original indexes may no longer be needed. Database administrators should regularly identify these indexes and delete them to reduce the impact of the indexes on update operations.

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

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

Tips on MySQL indexing:

1. Fields that are often used to filter records.

1. Primary key field, the system automatically creates the primary key index;
2. For unique key fields, the system automatically creates corresponding indexes;
3. The fields defined as foreign keys by the foreign key constraint;

4. Fields used to join tables in queries;

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

2. Indexes will take up disk space, and creating unnecessary indexes will only result in a waste.

3. The creation of indexes must take into account how the data is operated.

1. The content rarely changes but is frequently queried, so it doesn’t matter if you create several indexes for it;

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

4. The difference between primary key and unique key

1. The domain or domain group used as the Primary Key cannot be null. But Unique Key can.

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

The biggest difference is in the logic design. Primary Key is generally used as a record identifier in the logic design, which is also the setting
The original purpose of Primary Key is to ensure the uniqueness of domain/domain group.

5. Composite index and single index

Composite index refers to a multi-field joint index. When querying, these fields are often combined as conditions for querying.

The unique index is mainly indexed by the primary key ID, 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 if a is the same, sort by b, so when you search for a or ab,

You can use this index. But when you only search for b, the index is not very helpful. You may be able to skip the search.

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

Adding and removing indexes:

1. The primary key and foreign key of the table must have indexes;

2. Tables with more than 3 million data volumes should have indexes;

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

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

5. Indexes should be built on fields with high selectivity;

6. Indexes should be built on small fields. Do not build indexes on large text fields or even very long fields.

7. The establishment of composite indexes requires careful analysis; try to consider using single-field indexes instead:

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

B. Do several fields of a composite index often appear in the Where clause in AND mode at the same time? Are there few or no single-field queries? If yes, 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, decompose it into multiple single-field indexes;

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

E. If there is a single-field index and a composite index on these fields, you can generally delete the composite index;

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 common criteria for judging when establishing indexes. In a nutshell, the establishment of indexes must be cautious, the necessity of each index should be carefully analyzed, and there must be a basis for its establishment. Because too many indexes and insufficient or incorrect indexes are of no benefit to performance: each index created on a table increases storage overhead, and the index also increases processing overhead for insert, delete, and update operations. In addition, too many composite indexes are generally useless when there are single-field indexes. On the contrary, they will reduce the performance when adding and deleting data, especially for tables that are frequently updated.

The above article briefly discusses the index design principles of MySQL and the differences between common indexes. This is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.