Methods and key points of database tuning

Today, the editor shares with you the methods and key points of database tuning, which many people do not quite understand. Today, in order to let you know more about the database, the editor summed up the following contents. Let's look down together. I'm sure you'll get something.

Database bottlenecks generally lie in IO and CPU

1. Use less group by and order by

2. Sort by index (do not use index for all fields, because it is time-consuming for insert and update to reconstruct the index)

3. Avoid select *

4. Use less join

5. For join and subquery, use join instead of subquery.

6. Use less or

7. Replace union with union all

8. Filter as early as possible, for example, when paging data is queried by multiple tables, it is best to filter on one table and join with other tables.

9. Avoid type conversion

10. Mysql has its own cache mechanism, so you don't need to use memcache to do it!

11. Deal with repetitive and inconsistent data as your biggest enemy

Be careful to split your data by delimiters, they violate the "field can no longer be divided", such as a field content is physics/maths.

Solution: move these fields to another table and use foreign keys to connect

13. All fields must be fully dependent on the primary key rather than partially

14. If performance is key, don't stubbornly avoid redundancy

For the design of the database, we do not need to take the three paradigms as the dead rule, as the guiding ideology. The first thing you need to understand when designing a database is what db is for. Is it oriented to things? Or for statistical analysis? Of course, the object-oriented CRUD (create,read,update,delete) is the majority, while the statistics are query and analysis. The design of the two databases is bound to be different, because the emphasis is different.

The above is the method of database tuning and a brief introduction of the main points, of course, the detailed use of the above differences have to be used by everyone to understand. If you want to know more, welcome to follow the industry information channel!