MYSQL Performance Tuning and architecture design-instance analysis of factors affecting MYSQL Performance-MySQL-mysql tutorial

MYSQL Performance Tuning and architecture design-instance analysis of factors affecting MYSQL Performance

Requirement overview

A simple discussion zone system requires users, user groups, and group discussion areas.

Brief analysis

1) tables that need to store user data;

2) tables that need to store group information and user-group relationships;

3) tables that need to store discussion information

Solution

Original Solution 1:

Four tables are used to store the user, user group, user-group relationship, and information about the discussion posts of each group.

User table

Field

Type

Null

Key

Default

Extra

Id

Int (11)

NO

Nick_name

Varchar (32)

NO

NULL

Password

Char (64)

YES

NULL

Email

Varchar (32)

NO

NULL

Status

Varchar (16)

NO

NULL

Sexuality

Char (1)

NO

NULL

Msn

Varchar (32)

YES

NULL

Sign

Varchar (64)

YES

NULL

Brithday

Date

YES

NULL

Holobby

Varchar (64)

YES

NULL

Location

Varchar (64)

YES

NULL

Description

Varchar (1024)

YES

NULL

Group table

Field

Type

Null

Key

Default

Extra

Id

Int (11)

NO

Gmt_create

Datetime

NO

NULL

Gmt_modified

Datetime

NO

NULL

Name

Varchar (32)

NO

NULL

Status

Varchar (16)

NO

NULL

Description

Varchar (1024)

YES

NULL

User_group relational table

Field

Type

Null

Key

Default

Extra

User_id

Int (11)

NO

MUL

NULL

Group_id

Int (11)

NO

MUL

NULL

User_type

Int (11)

NO

NULL

Gmt_create

Datetime

NO

NULL

Gmt_modified

Datetime

NO

NULL

Status

Varchar (16)

NO

NULL

Group_message discussion group post table

Field

Type

Null

Key

Default

Extra

Id

Int (11)

NO

NULL

Gmt_create

Datetime

NO

NULL

Gmt_modified

Datetime

NO

NULL

Group_id

Int (11)

NO

NULL

User_id

Int (11)

NO

NULL

Subject

Varchar (128)

NO

NULL

Content

Text

YES

NULL

Solution 2 after optimization is as follows:

The user table is divided into the user table and the user_profile table.

The group_message discussion group table is divided into group_message discussion group and group_message_content.

User table

Field

Type

Null

Key

Default

Extra

Id

Int (11)

NO

Nick_name

Varchar (32)

NO

NULL

Password

Char (64)

YES

NULL

Email

Varchar (32)

NO

NULL

Status

Varchar (16)

NO

NULL

User_profile user attribute table

Field

Type

Null

Key

Default

Extra

Id

Int (11)

NO

Sexuality

Char (1)

NO

NULL

Msn

Varchar (32)

YES

NULL

Sign

Varchar (64)

YES

NULL

Brithday

Date

YES

NULL

Holobby

Varchar (64)

YES

NULL

Location

Varchar (64)

YES

NULL

Description

Varchar (1024)

YES

NULL

Group_message discussion group post table

Field

Type

Null

Key

Default

Extra

Id

Int (11)

NO

NULL

Gmt_create

Datetime

NO

NULL

Gmt_modified

Datetime

NO

NULL

Group_id

Int (11)

NO

NULL

User_id

Int (11)

NO

NULL

Subject

Varchar (128)

NO

NULL

Author

Varchar (32)

NO

NULL

Group_message_content post content table

Field

Type

Null

Key

Default

Extra

Group_msg_id

Int (11)

NO

Content

Text

NO

NULL

Analysis considerations:

1. In practice, the most visited page in a discussion board system should be the post title list page. The most important information on the post title list page is from the group_message table. at the same time, the author behind the post title is generally displayed by user name (nickname. Therefore:

1) follow the first solution:

SELECT t. id, t. subject, user. id, u. nick_name

FROM

(

SELECT id, user_id, subject

FROM group_message

WHERE group_id =?

Order by gmt_modified desc limit 20

) T, user u

WHERE t. user_id = u. id

2) follow the second solution:

SELECT t. id, t. subject, t. user_id, t. author

FROM group_message t

HWERE group_id =?

Order by gmt_modified desc limit 20

When we compare the two queries, we can clearly see who is better and who is worse.

2. because the group_message table in solution 1 also contains a large field 'content', the information stored in this field occupies most of the storage space of the entire table, however, the most frequently displayed Query in 1 does not require the information stored in this field. Therefore, Query reads a large amount of data that has no significance. Therefore, you need to separate the content field and store it in the group_message_content post content table.

Author "evil John's blog"

BitsCN.com