Mysql optimization-Procedure_Analyse optimizes table structure

one. Preface

PROCEDURE ANALYSE() can be used as an auxiliary reference for analysis statements when optimizing the table structure.

Using this statement, MySQL helps you analyze your fields and their actual data, and will give you some useful suggestions.

[These suggestions will only become useful if there is actual data in the table, because making some big decisions requires data as a basis. ]

#For example, if you create an INT field as your primary key, but there is not much data, then PROCEDURE ANALYSE() will suggest that you change this field The type is changed to MEDIUMINT. Or you're using a VARCHAR field, and since there's not much data, you might get a message asking you to change it to ENUM suggestions. These suggestions are all possible because there is not enough data, so the decision-making is not accurate enough.

When designing a table, we sometimes always think about what type to use for a certain field. A novice-level PHPer will have considerable experience and can type it out directly. In fact, we can also use procedure analyze() during later tuning; analyze the table structure to see the field suggestions that MySQL gives us, and adjust some based on the actual situation. Field type (this is already very trivial, general applications cannot be so detailed, and the database we designed based on experience can basically meet the application.

2. Example

procesure analyze(max_elements,max_memory)

max_elements: Specify the maximum value of non-duplicate values ​​in each column , when this value is exceeded, MySQL will not recommend the enum type

##max_memory: Find all unique values ​​for each column. The maximum memory size used. ##mysql> select * from br_task procedure analyze(1,256) \G;

#3. Analysis

Taking the analysis of line 4 as an example, it can be seen that

##br_Task .task_name field, minimum value of

column: 121 new

Maximum value of column: 韩-soft文-国产-银典See

Minimum length: 3 bytes

Maximum length: 52 bytes

Average length: 24.1852

Optimization suggestion: Change the data type of the

VARCHAR(52) NOT NULL. It's important to note that these are just suggestions. These suggestions will only become accurate as you add more data to your table. Remember, you are the one who makes the final decision.

The above is the content of Mysql optimization-Procedure_Analyse optimization table structure. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!