This article brings you a summary of Mysql database optimization methods. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
There is no end to learning, and database optimization is divided into various aspects. Here, I have made a relatively complete summary and shared it with colleagues who are working or studying.
Database optimization is divided into the following seven major aspects:
1 , The design of the table must conform to Three Normal Forms (Appropriate inverse three normal forms can also be);
2. Add appropriate indexes, indexes have a great impact on query speed , indexes must be added (primary key index, unique index, ordinary index, full-text index);
3. Add appropriate stored procedures, triggers, transactions, etc.;
4 , read and write separation (master-slave database);
5. Some optimizations of sql statements (sql statements with relatively slow query execution speed);
6. Table partitioning (Table splitting: Divide a large table into multiple tables. Partition: Allocate the contents of a table to different areas for storage );
7. Upgrade the mysql server hardware.
Next I will explain the optimization method in detail.
First and third normal form
First normal form:
Atomicity: The fields in the table cannot be divided any more. As long as it is a relational database, it will automatically meet the first normal form
Relational database (with the concept of rows and columns): mysql , sql server, oracle, db2, infomix, sybase, postgresql, when designing, first have the library->table->field->specific record (content): when storing data, the fields must be designed.
Non-relational database (generally referred to as nosql database): memcache, redis, momgodb, etc.
Second Normal Form:
There are no identical records in a table, which can be solved by using a primary key
Third Normal Form:
Redundant data cannot be stored in the table
Reverse three paradigm design:
ID | Album name | Album views |
Life | Photo | 100|
Work Photo | 100 |
Photo Name | Album ID | Views | |
---|---|---|---|
My puppy | 1 | 49 | |
My kitten | 1 | 51 | |
My colleagues | 2 | 100 |
The above is the detailed content of Summary of Mysql database optimization methods (must read). For more information, please follow other related articles on the PHP Chinese website!