1, create an index
is dominant for queries For applications, the index is particularly important. In many cases, the performance problem is simply because we forgot to add an index, or because we didn’t add a more effective index. If no index is added, then a full table scan will be performed to find any specific data. If the amount of data in a table is large and there are few eligible results, then no index will cause fatal performance degradation . However, it is not necessary to build an index under all circumstances. For example, gender may only have two values. Not only does indexing have no advantage, but it also affects the update speed. This is called excessive indexing.
2, composite index
For example, there is a sentence like this: select * from users where area=’beijing’ and age=22;
If we create a single index on area and age, since mysql query can only use one index at a time, so although this is relatively not done The full table scan improves a lot of efficiency when indexing, but if you create a composite index on the two columns of area and age, it will bring higher efficiency. If we create a composite index of (area, age, salary), it is actually equivalent to creating three indexes (area, age, salary), (area, age), (area), which is called the best left prefix characteristic. Therefore, when we create a composite index, we should put the most frequently used as a constraint on the leftmost column, in descending order.
3, the index will not include columns with NULL values
As long as the column contains NULL values, it will not be included in the index. As long as one column in the composite index contains NULL values, then this column is invalid for this composite index. So we don’t let the default value of the field be NULL when designing the database.
4, use short index
indexes the list, and a prefix length should be specified if possible. For example, if there is a CHAR(255) column, if the multi-value is unique within the first 10 or 20 characters, then do not index the entire column. Short index can not only improve query speed but also save disk space and I/O operations.
5, the index problem of sorting
mysql query uses only one index, so if an index is already used in the where clause, the column in the order by will not use the index. Therefore, do not use the sort operation when the database default sorting can meet the requirements; try not to include multiple column sorts, and if necessary, it is best to create a composite index for these columns.
6, like statement operation
Under normal circumstances, the use of like operations is not encouraged. If it must be used, how to use it is also a problem. Like “%aaa%” will not use index but like “aaa%” can use index.
7, do not perform calculations on columns
select * from users where YEAR(adddate)
8, do not use NOT IN and operation
NOT IN and operations will not use the index and will perform a full table scan. NOT IN can be replaced by NOT EXISTS, id != 3 can be used id>3 or id <3
More Technical information can be concerned: gzitcast