Start learning MySQL optimization technology today

with the deepening of the work, more and more feel the importance of knowledge of database optimization, so download the Hanshunping MySQL optimization technology video , where the full after-school notes, which may be interspersed with some of their own work experience (thanks to teacher Han).

MySQL optimization is not an action, but a full set of actions, including:

1. Rationalization of the design of the table

2. Add an appropriate index

3. Sub-table technology (horizontal sub-table, vertical sub-table)

4. Read and write separation technology

5. Use stored procedures for commonly used SQL statements (eliminates DBMS compilation of SQL, but reduces portability)

6. Modify the MySQL configuration (My.ini), such as MySQL maximum concurrency, cache and other configuration

7. Defragment, periodically erase useless data

7. Hardware Upgrade

The design of the table is generally in accordance with 3NF

     The first paradigm: 1NF is an atomic constraint on attributes, requiring that the attribute (column) be atomic, non-decomposed, (as long as the relational database satisfies 1NF), the effect is that the value of a field, has been able to fully express the meaning of the field, do not need to decompose into multiple fields to combine the expression, For example, a member of the table has a field address address, can be filled out as "China Wuhan South Lake District xxx Street xxx", which is already a complete address, do not need to re-decomposition of the address field
The second paradigm: 2NF is a unique constraint on records, requiring records to have a unique identity, that is, the uniqueness of the entity. Generally we design the table, the first field is the self-increment ID, so this uniqueness is very good to meet
The third paradigm: 3NF is a constraint on field redundancy, which requires no redundancy in the field. What is redundant, for example have class table, inside has class_id,class_name, and belong to Grade Department three field, student table inside have st_id,st_name,class_id, pass student table of Class_ ID can be in the class table to obtain the corresponding grade department, there is no need to add a department field in the Student table (of course, sometimes the actual development of this design will improve efficiency, sometimes we need to improve the efficiency of operation, we must reduce the paradigm standard, proper retention of redundant data , this is sometimes felt in real-world development: Instead of having to look up the fields of another table each time you take a complete piece of data, you might as well integrate the fields directly into a single table. This depends on the specific situation)

Follow up on the following study

This article is from the "I want to be a great god" blog, please make sure to keep this source http://8210504.blog.51cto.com/8200504/1585252

Start learning MySQL optimization technology today