what do you usually consider when adding an index?
I usually add an index by querying a few fields in the where clause and adding an index to those fields.
what do you usually consider when adding an index?
I usually add an index by querying a few fields in the where clause and adding an index to those fields.
it's a bit blind to index whatever fields the where condition has. There have been several books on index design, which can not be explained clearly in a few words. List a few points:
1. Considering the distribution of column values, if the selectivity is not good, then indexing is likely to be ineffective and will only increase DML overhead;
2. It is your practice to overwrite the index, avoid going back to the table, and get the query results directly from the index, but if every SQL builds an overlay index on it, consider the pressure on DML caused by too many indexes and the redundancy of the index;
3. Note that tables that are updated frequently should not have too many indexes;
4. Design the index according to the amount of data in the table and the execution plan
.
after indexing, you should also pay attention to whether the writing of SQL leads to index failure, such as type mismatch, index columns for functional processing or operation,% prefix wildcard, leading columns that do not contain multi-column indexes, and so on.