MySQL Performance Tuning Index

Original: http://bbs.landingbj.com/t-0-245452-1.html

For the optimization of the index, we first need to find the appropriate field, the second create the index to find the appropriate order, the third to find the appropriate proportion, and four is to do the appropriate maintenance.

First you need to find the right field. Methods are: Improve filtering, reduce the index of the update split, avoid invalid index, non-forced foreign key.

In the real world, optimized indexes are primarily considered in the following ways:

Sets the default value for the indexed field;

Do not allow fields with NULL to enter the combined index;

The probability of removing the index of a low-filtering field is worse;

The operation cannot be done on the indexed field, it will fail;

Avoid frequently updated fields to enter the index, increasing the IO burden;

Overwrite the index as much as possible because MySQL sorting is inefficient;

The second creation index finds the appropriate order. Methods there were early filtering and reduced sorting.

In the real world, optimized indexes are primarily considered in the following ways:

The higher the filter, the more forward the field needs to be.

Core SQL Overlay index to ensure maximum efficiency;

The sorting field enters the index without interfering with the filter;

Multi-SQL comprehensive consideration, re-use index;

The third is to find the appropriate proportions by controlling the index length, especially the longer string fields.

In the real world, optimized indexes are primarily considered in the following ways:

The character field prefix index must be returned to the table fetch data;

The entire field is recommended without returning the data to the table;

The four is to do appropriate maintenance, the method is to regularly maintain the presence of frequent additions and deletions of the field index.

In the real world, optimized indexes are primarily considered in the following ways:

Maintain (Rebuild) indexes on non-core tables on a monthly basis, if possible;

The index on the core table is maintained on a per-year basis, if available.

In the process of being a DBA, it is not known how important the optimization of an index is to a system. Remember the important one: the index is not much, but to find the most appropriate.

MySQL Performance Tuning Index