Design of MySQL database optimization table and slow query location

I. Aspects of database optimization inclusion
Database optimization is a kind of comprehensive technology. There is not a way to improve the efficiency of the database very much. But through a variety of improvements. This makes the database performance improved.

Mainly includes:
1. Rationalization of the design of the table (3 paradigm)
2. Add the appropriate index to the table. How to use an index
3, Sub-table technology (horizontal cutting, vertical cutting)
4, time to clear the data garbage, timed defragmentation
5. Multi-use stored procedures and triggers
6. Optimization of MySQL Configuration
7. Read/write separation
8, MySQLServer hardware upgrade.


Second, the design of the database
Steps:
1. Collect information: Communicate with the relevant personnel of the system. Fully understand the tasks that the database needs to complete

2, identify the entity: a detailed existence of the object, noun.
For example: users, posts, replies, plates

3. Identity attributes

4. Identification relationship:
1) Single-to-one: Two table primary keys are public fields
2) One-to-many: primary key and non-primary key building relationship
3) Many-to-one: non-primary key and primary key building relationship
4) Many-to-many: non-primary key and non-primary key building relationships
5. Turn the E-R chart into a table
1) Turn the entity into a table and convert the attribute to a field
2) Assuming that a suitable field is not found for the key, we add a self-active growth column to the key.
3) Establish the right relationship


Third, data normalization


When the table is designed, it is not the most reasonable structure. We need to normalize the table (we normalize the table by the 3 paradigm)
Satisfying the first normal form first--satisfying the second normal form--and satisfying the third normal form


1NF: Ensure the atomicity of each column
The first paradigm is used to normalize all fields, and all fields cannot be divided.

Note: For example, address this field. Assume no subtotals, no sorting. acts as a string only. Then we do not dismantle (anti-3 paradigm)

2NF: The non-key field must depend on the primary key field

Assuming that a relationship satisfies 1NF and that other columns other than the primary key depend on the primary key, the second normal form (2NF) is satisfied.

3NF: Eliminate delivery dependencies

In the non-primary key field. Suppose a field can deduce that there is also a field, which is called transitive dependency.

Iv. the relationship between standardization and performance

1, in order to meet a certain business objectives. Database performance is more important than normalizing a database

By adding additional fields to a given table, you can reduce the amount of time needed to search for information from it.

By inserting computed columns (for example, score scores) in a given table for easy querying

2, the normalization of the same time need to consider the performance of the database

Five, positioning slow query

Find SQL statements that are slow to query.

increase, delete, change, check

The query accounted for 90%, adding or removing the total accounted for 10%

MySQL database some queries about the state

Use "Show status"

Instruction description

Show status query some execution status of MySQL database

Show status like ' Com_insert '; Shows how many insertions were run

Show status like ' Com_update '; Shows how many update operations have been run

Show status like ' Com_delete '; Show how many delete operations were run

Show status like ' Com_select '; Shows how many query operations were run

Show status like ' uptime '; Show how long MySQL started, assuming that the time is very long, the database table storage engine is a lot of myisam, when the attention to defragmentation.

Show [Session | global] status like ..., assuming not to write, by default is the session level, want to query from MySQL start until now, with global.

Show Slow Query

Show variables like ' Long_query_time ' shows the time of slow query, default is 10 seconds

Set long_query_time=0.5 sets the time for the slow query to 0.5 seconds, so that the test

Show status like ' Slow_queries ' shows the time of slow query, default is 10 seconds

Locate slow query (turn on slow query log)

Once the slow query log is turned on. Log file location in the My.ini file to find, by default, MySQL will not log slow query

How to turn on slow query:

1. Close the current MySQL service: net stop MySQL

2, start through Safe mode will write log.

3. Turn off MySQL with Safe mode activated

4, set the time of the slow query to 0.5 seconds, set long_query_time=0.5

5. Select * FROM table where xx= ' 00 ';

6, go to the slow query log to find the corresponding SQL statement.

Inverse three paradigms:

The higher the paradigm. Data redundancy is less, but sometimes the efficiency is lower, in order to improve the efficiency of execution, can appropriately make data redundancy.

Design of MySQL database optimization table and slow query location