Summary of MySQL database optimization method

First, the database design

Moderation of the inverse paradigm, the attention is moderate

We all know the three paradigms, the model based on the three-normal paradigm is the most efficient way to save data and the easiest mode to extend. When we develop applications, we design databases to the maximum extent possible, especially for OLTP systems, and the three paradigms are rules that must be followed. Of course, the biggest problem with the three paradigms is that queries often need to join many tables, resulting in low query efficiency. So sometimes based on performance considerations, we need to deliberately violate the three paradigm, moderate to do redundancy, in order to improve the efficiency of the query purpose. Note that the counter paradigm here is moderate and must provide sufficient justification for this practice. Here's a bad example:

Here, in order to improve the retrieval efficiency of the student activity record, the unit name is redundant to the student activity record. The unit information has 500 records, while the student activity record has approximately 2 million data quantity in a year. If the Student Activity record table is not redundant this unit name field, which contains only three int fields and one timestamp field, occupies only 16 bytes, is a very small table. And the redundancy of a varchar (32) of the field is the original 3 times times, the corresponding retrieval of more than so much I/O. And the record number is very different, 2000000, resulting in the update of a unit name and update 4,000 redundant records. Thus, this redundancy is simply counterproductive.

Here's a good redundancy.

As you can see, [student test scores] are redundant, and this score can be aggregated by [score]. In the "Student test scores", one test a student only one record, and in the "scoring situation", a student in the paper a small question a small question a record, roughly calculate the proportion is about 1:100. And the test score is not easy to change, update the frequency is not high, so that this redundancy is relatively good.

Indexing is an appropriate setting

1, create an index

Indexing is especially important for queries as a major application. Most of the time the performance problem is simply because we forgot to add the index, or we didn't add a more efficient index. If you do not index, then look for any even just a specific data will be a full table scan, if a table of large amounts of data and meet the conditions of the result is very small, then no index can cause fatal performance degradation. But it is not always necessary to build an index, such as the gender may only have two values, the index not only has no advantage, but also affect the update speed, which is called an excessive 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, because the MySQL query can only use one index at a time, the full table scan is a lot more efficient when it is relatively non indexed, but creating a composite index on the area, the age two column can be more efficient. If we create a composite index (area, age, salary), it's actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix feature. So when we create a composite index, we should place the columns that are most commonly used as constraints to the left, decreasing in descending order.
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 one column in the composite index contains null values. So we don't want the default value of the field to be null when designing the database.
4, using short index

Index A string column, if possible, to specify a prefix length. For example, if you have a column with char (255), if most values are unique within the first 10 or 20 characters, do not index the entire column. Short indexing can not only improve query speed but also save disk space and I/O operations.
5, sort of indexing problem

The MySQL query uses only one index, so the columns in the order by are not indexed if the index is already used in the WHERE clause. Therefore, do not use sort operations when the database default sort meets the requirements, and try not to include sorting of multiple columns, preferably if you need to create a composite index for these columns.
6,like Statement Actions

It is generally discouraged to use like operations, and how to use them is also a problem if not used. Like "%aaa%" does not use indexes and like "aaa%" can use indexes.

7, do not perform operations on the column

SELECT * from users where year (adddate) <2007;
will be performed on each row, which will cause the index to fail with a full table scan, so we can change it to
SELECT * from users where adddate< ' 2007-01-01 ';

8, do not use not in and <> operations

None in and <> operations do not use the index to perform a full table scan. Not in can be substituted by not exists, id<>3 can be replaced with id>3 or id<3.