Optimization of MySQL Database

1. The design of the table conforms to the three paradigms
   2. Each column property is an indivisible property value, ensuring the atomicity of each column
      

      Student Number name Gender contact
      20080901 Three men Email:[email protected],phone:88886666
      20080902 li si female email:[email protected],phone:66668888

      The above table does not conform to the first paradigm: the Contact field can be re-divided, so the change to correct is:

      Student number name Sex email phone
      20080901 three men [email protected] 88886666
      20080902 li si female [email protected] 66668888

   4. Each non-primary property is determined by the entire primary key function and cannot be determined by a part of the primary key, which is to eliminate partial dependencies. The second paradigm can reduce insert exceptions, delete exceptions, and modify exceptions
      

      For example (students choose a timetable):
      Student curriculum teacher teacher Title textbook class time
      John Doe Spring Zhang teacher Java lecturer "Spring in the" 301 08:00
      Zhang San struts yang teacher Java lecturer "Struts in Action" 302 13:30

      Here (students, courses) can determine teacher, teacher title, textbook, Classroom and class time, so you can put (students, courses) as the primary key. However, the textbook is not entirely dependent on (students, courses), only take out the course to determine the teaching material, because a course, must have designated a teaching material. This is called incomplete dependency, or partial dependency. In this case, the second paradigm is not satisfied.

      After the change, select the timetable:
      Student Curriculum teacher Teacher Title class time
      John Doe Spring Zhang teacher Java Lecturer 301 08:00
      Zhang San Struts yang teacher Java Lecturer 302 13:30

      Curriculum:
      Course Materials
      Spring "Spring in the Bible"
      Struts in Action

      
      
      
   6. Data cannot have a transitive relationship, that is, no attribute is directly related to the primary key, not an indirect relationship.
      

      such as Student table (school number, name, age, gender, institution, institution address, college phone)

      Such a table structure, there is the above relationship. Study number------(university address, college phone)

      Such a table structure, we should take apart, as follows.

      (Student number, name, age, gender, institution)--(institution, institution address, school phone)

3. By adding an appropriate index, the index has a large impact on the database query speed and must be indexed, such as: Primary key index, unique index, normal index, full-text index
5. Add appropriate stored procedures, triggers, transactions, and so on.
   

   The trigger is a special kind of stored procedure, the trigger is automatically executed by triggering the event, and the stored procedure can be called directly by the stored procedure name, and the main function of the trigger is that it can realize complex referential integrity and data consistency which cannot be guaranteed by the primary key and foreign key. There is also the ability to enforce constraints and cascade operations.

7. Read/write separation (master-slave database)
9. Optimization of SQL statements
   
   1. Use specific fields instead of * numbers to avoid full table queries
   2. Avoid using the! = or <> operator in the WHERE clause, so that the full table is scanned
   3. Avoid null values for fields in the WHERE clause,
   4. Avoid using or in the WHERE clause to join conditions
   5. A like fuzzy query can also cause a full table scan, considering full-text retrieval
   6. In and not in also to avoid the use, this will also cause a full table scan, can use between without in
   7. You can use LEFT join instead of subquery
11. Sub-table Partitioning: Sub-table (Put a large table participle multiple tables), partition (the allocation of a table in the different areas of storage)
13. Hardware upgrade to MySQL server

Optimization of MySQL Database