On the Index Design Principles of mysql and the Differences of Common Indexes

Index Definition: A separate, disk-based database structure that contains reference pointers to all records in a table.

Design principles of database index:

In order to use indexes more efficiently, you must consider which fields and what types of indexes to create when you create them.
So what are the principles of index design?

1. Choose a uniqueness index

The uniqueness index has a value of uniqueness, which makes it easier to determine a record through the index.
For example, the number in the student table is a uniqueness field. Establishing a unique index for this field can quickly determine the information of a student.
If you use a name, you may have the same name, which reduces the query speed.

2. Indexing fields that often require sorting, grouping, and federation

Fields for operations such as ORDER BY, GROUP BY, DISTINCT, and UNION are often required, and sorting operations waste a lot of time.
If you index it, you can effectively avoid sorting operations.

3. Indexing fields that are often used as query criteria

If a field is frequently used as a query condition, the query speed of that field will affect the query speed of the whole table. Therefore,
Indexing such fields can improve the query speed of the whole table.

4. Limit the number of indexes

The number of indexes is not as good as possible. Each index needs to occupy disk space, and the more indexes, the larger the disk space required.
When modifying a table, it is troublesome to reconstruct and update the index. The more indexes, the more time it takes to update the table.

5. Try to use indexes with less data

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

6. Try to use prefixes for indexing

If the value of the index field is very long, it is best to index it with the prefix of the value. For example, fields of type TEXT and BLOG for full-text retrieval
It would 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 rarely used

After the data in the table is greatly updated, or the way the data is used is changed, the original indexes may no longer be needed. Database management
You should periodically find these indexes and delete them to reduce the impact of the indexes on update operations.

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

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

Tips related to mysql index:

1. Fields that are 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. Fields defined as foreign keys by foreign key constraints;

4. Fields used to join tables in queries;

5. Fields frequently used as a basis for sorting (fields of order by);

2. Indexes take up disk space, and creating unnecessary indexes will only lead to waste.

3. The creation of indexes must consider the operation mode of data.

1. The content rarely changes and is often queried. Create several indexes for it so-called;

2. For tables with frequent and routine changes, it is necessary to carefully create indexes that are really necessary;

4. The difference between primary key and unique key

1. A domain/domain group that is Primary Key cannot be null. And Unique Key can.

2. There can be only one Primary Key in a table, and multiple Unique Key can exist simultaneously.

The greater difference is in logical design. Primary Key1 is used as record identification in logical design, which is also set
The original intention of Primary Key, while Unique Key is only to ensure the uniqueness of domains/groups of domains.

5. Composite and Single 1 Indexes

Compound index refers to a multi-field joint index. When querying, it is often necessary to combine these fields as a condition before querying

Only 1 index mainly uses the primary key ID index, and the storage structure order is the same as the physical structure 1

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

First sort by a, then sort by a, so when you look up a or ab,

You can use this index. But when you only look up b, the index is not helpful to you. You may be able to jump to find it.

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

Add and delete indexes:

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

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

3. Tables that are often connected with other tables should be indexed on the connected fields;

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

5. The index should be built on the field with high selectivity;

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

7. The establishment of composite index needs careful analysis; Try to consider using a single-field index instead:

A, correctly select the main column field in the composite index, 1 is the field with better selectivity;

B, do several fields of a composite index often appear in an AND form in an Where clause at the same time? Are there few or no single-field queries? If so, a composite index can be established; Otherwise, consider single-field index;

C, decomposed into multiple single-field indexes if the fields contained in the composite index often appear separately in the Where clause;

D, if the composite index contains more than 3 fields, carefully consider its necessity and consider reducing the number of composite fields;

E, if there are both single-field indexes and composite indexes on these fields, 1 can delete the composite index;

8. Do not build too many indexes for tables with frequent data operations;

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

These are some common judgments when establishing indexes. In a word, the establishment of indexes must be careful, and the necessity of each index should be carefully analyzed and there should be a basis for establishment. Because there are too many indexes and inadequate, incorrect indexes are not good for performance: every index built on a table increases storage overhead, and indexes also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes, in the case of single-field indexes, 1 is generally worthless; On the contrary, it will also reduce the performance when data is added and deleted, especially for frequently updated tables, which will have a greater negative impact