Eight ways to optimize the Mysql database
- 2020-06-01 11:11:53
- OfStack
1. Create an index
Indexes are especially important for query-dominated applications. Many times performance issues are simply caused by forgetting to add an index, or not adding a more efficient index. If not indexed, finding any one particular piece of data will result in a full table scan. If a table has a large amount of data and few qualified results, then not indexed will cause fatal performance degradation. For example, gender may only have two values. Not only does indexing have no advantage, but it also affects the update speed, which is called over-indexing.
2. Composite index
For example, one statement goes like this: select * from users where area='beijing' and age=22;
If we create a single index on area and age respectively, since mysql queries can only use one index at a time, it is much more efficient to scan the full table without indexes, but it is more efficient to create a composite index on area and age columns. If we create a composite index (area, age, salary), then we create three indexes (area,age,salary), (area,age), and (area), which is called the best left prefix feature. Therefore, when creating a composite index, we should place the columns that are most commonly used as constraints on the leftmost, decreasing in order.
3. The index will not contain columns with NULL values
As long as a column contains an NULL value, it will not be included in the index, and as long as a column in a composite index contains an NULL value, it will be invalid for the composite index. So let's not design the database with NULL as the default value for the field.
4. Use a short index
Index a string, specifying a prefix length if possible. For example, if you have a column of CHAR(255), do not index the entire column if the first 10 or 20 characters are all 1. Short indexes not only improve query speed but also save disk space and I/O operations.
5, sort the index problem
The mysql query USES only one index, so if the index is already used in the where clause, the columns in order by will not use the index. So the database default sort can meet the requirements do not use the sort operation; Try not to include multiple column sorts, and it is best to create composite indexes for these columns if necessary.
6. like statement operation
1. like operation is generally not encouraged. If necessary, how to use it is also a problem. like "%aaa%" does not use indexes, while like "aaa%" does.
7. Do not operate on columns
select * from users where YEAR(adddate) < 2007;
The operation will be performed on each row, which will cause the index to fail for a full table scan, so we can change to
select * from users where adddate < '2007-01-01';
8. NOT IN and IN are not used < > operation
NOT IN and < > None of the operations will use the index to perform a full table scan. NOT IN can be replaced by NOT EXISTS, id < > 3 can use id > 3 or id < Instead of 3.
Indexes are especially important for query-dominated applications. Many times performance issues are simply caused by forgetting to add an index, or not adding a more efficient index. If not indexed, finding any one particular piece of data will result in a full table scan. If a table has a large amount of data and few qualified results, then not indexed will cause fatal performance degradation. For example, gender may only have two values. Not only does indexing have no advantage, but it also affects the update speed, which is called over-indexing.
2. Composite index
For example, one statement goes like this: select * from users where area='beijing' and age=22;
If we create a single index on area and age respectively, since mysql queries can only use one index at a time, it is much more efficient to scan the full table without indexes, but it is more efficient to create a composite index on area and age columns. If we create a composite index (area, age, salary), then we create three indexes (area,age,salary), (area,age), and (area), which is called the best left prefix feature. Therefore, when creating a composite index, we should place the columns that are most commonly used as constraints on the leftmost, decreasing in order.
3. The index will not contain columns with NULL values
As long as a column contains an NULL value, it will not be included in the index, and as long as a column in a composite index contains an NULL value, it will be invalid for the composite index. So let's not design the database with NULL as the default value for the field.
4. Use a short index
Index a string, specifying a prefix length if possible. For example, if you have a column of CHAR(255), do not index the entire column if the first 10 or 20 characters are all 1. Short indexes not only improve query speed but also save disk space and I/O operations.
5, sort the index problem
The mysql query USES only one index, so if the index is already used in the where clause, the columns in order by will not use the index. So the database default sort can meet the requirements do not use the sort operation; Try not to include multiple column sorts, and it is best to create composite indexes for these columns if necessary.
6. like statement operation
1. like operation is generally not encouraged. If necessary, how to use it is also a problem. like "%aaa%" does not use indexes, while like "aaa%" does.
7. Do not operate on columns
select * from users where YEAR(adddate) < 2007;
The operation will be performed on each row, which will cause the index to fail for a full table scan, so we can change to
select * from users where adddate < '2007-01-01';
8. NOT IN and IN are not used < > operation
NOT IN and < > None of the operations will use the index to perform a full table scan. NOT IN can be replaced by NOT EXISTS, id < > 3 can use id > 3 or id < Instead of 3.