8 ways to optimize a MySQL database

8 ways to optimize a MySQL database

Font: [Increase decrease] Type: Reprint time: 2013-11-07 I want to comment

This article optimizes MySQL database by 8 methods: Create INDEX, compound index, index does not contain NULL value column, use short index, sort index problem, like statement action, do not operate on column, do not use not in and <> operation

1. Create an index
Indexing is especially important for queries that are the primary application. A lot of times the performance problem is simply because we forgot to add an index, or we didn't add a more efficient index. If you do not index, then find any even a specific data will be a full table scan, if a table of large amounts of data and meet the results of a few, then the non-index will cause a fatal performance degradation. However, it is not always possible to index, for example, gender may be only two values, index not only have no advantage, but also affect the speed of the update, which is called over-index.
2. Composite Index
For example, there is a statement like this: SELECT * from users where area= ' Beijing ' and age=22;
If we were to create a single index on area and age, the MySQL query could use only one index at a time, so while the full table scan was a lot more efficient when it was relatively non-indexed, it would be more efficient to create a composite index on the area or age two column. If we create a composite index (area, age, salary), then it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left-prefix attribute. Therefore, when creating a composite index, the columns that are most commonly used as constraints should be placed on the leftmost, decreasing in turn.
3, the index does not contain columns with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.
4. Use short index
Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.
5. Sorted index Issues
The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
6. Like statement operation
It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.
7. Do not perform calculations on columns
SELECT * from the users where year (adddate) <2007;
The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to
SELECT * from users where adddate< ' 2007-01-01 ';
8. Do not use not in and <> operation
None in and <> do not use the index for full table scanning. Not in can be replaced by not exists, id<>3 can be replaced with id>3 or id<3.

8 ways to optimize a MySQL database