Eight things to pay attention to in MySql Indexes

Designing a MySql index can greatly improve your database query efficiency. When designing MySql indexes, there are some issues that deserve our attention:

1. Create a MySql Index
Indexes are especially important for applications where queries account for the majority. In many cases, the performance problem is very simple because we forget to add an index, or we have not added a more effective index. If you do not add
If the index is used, a full table scan will be performed to search for any specific data. If a table has a large amount of data and few results meet the conditions, if no index is added, the performance will be fatal.
Drop. However, it is not always necessary to create an index. For example, there may be only two values for gender. Creating an index not only has no advantages, but also affects the update speed. This is called excessive index.
2. Composite Index
For example, there is a statement like this: select * from users where area = 'beijing' and age = 22;
If we create a single index on the area and age respectively, because mysql queries can only use one index at a time, the full table scan improves the efficiency even if the index is not used.
Rate, but creating a composite index on the "area" and "age" columns will lead to higher efficiency. If we create an (area, age,
Salary) composite index, which is equivalent to creating three indexes (area, age, salary), (area, age), and (area). This is called the best left prefix.
Features. Therefore, when creating a composite index, we should place the columns that are most commonly used as restrictions on the leftmost and decrease in turn.
3. The index does not contain columns with NULL values.
As long as a column contains a NULL value, it will not be included in the index. If a column in the composite index contains a NULL value, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL during database design.
4. Use short Indexes
Index the string and column in MySql. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.
5. Sorting index problems
Mysql queries only use one MySql index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.
6. like statement operation
Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes.
7. Do not perform operations on columns
Select * from users where
YEAR (adddate)
8. Do NOT use not in and operations
Neither the not in nor the operation will use the index to scan the entire table. Not in can be replaced by not exists, while id3 can use id> 3 or id
 

How to view MySQL Indexes

MySQL index type Summary

MySQL index experience

Limitations and precautions for Mysql Indexes

Three statuses of mysql triggers