Mysql uses the procedure analyse () function to optimize the table structure _mysql

Brief introduction

procedure analyse()The function is the MySQL built-in MySQL field values for statistical analysis after the proposed field type.

Grammar

Procesure Analyse (Max_elements,max_memory)

Max_elements

Specifies the maximum value of a distinct value for each column, and when this value is exceeded, MySQL does not recommend the enum type.

Max_memory

analyse()Find out the maximum memory size for each column with all distinct values.

Practical Exercises

# Analyze all columns of the T1 table wing@3306>show create table T1; +-------+------------------------------------------------------------------------------------------------------ ---------------------------------------------------+
| Table |
Create Table | +-------+------------------------------------------------------------------------------------------------------ ---------------------------------------------------+
| T1 | CREATE TABLE ' t1 ' (' id ' int () default null, ' name ' varchar () default NULL, ' score ' int (one) default null) engine=i
Nnodb DEFAULT Charset=utf8 | +-------+------------------------------------------------------------------------------------------------------ ---------------------------------------------------+ 1 row in Set (0.00 sec) Wing@3306>select * T1 procedure Analy
SE (4); +---------------+-----------+-----------+------------+------------+------------------+-------+----------------- --------+-------------+--------------------------------+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | STD |
Optimal_fieldtype | +---------------+-----------+-----------+------------+------------+------------------+-------+----------------- --------+-------------+--------------------------------+
| Wing.t1.id | 1 |     200000 |     1 |        6 |   0 | 0 | 100000.5000 | 116099.2790 | Mediumint (6) UNSIGNED not NULL | | Wing.t1.name | 000jxc6v |     ZZZNMKCX |     8 |        8 |   0 | 0 | 8.0000 | NULL | CHAR (8) not NULL | | Wing.t1.score | 1 |     100 |     1 |        3 |   0 | 0 | 50.4889 | 28.8768 |
TINYINT (3) UNSIGNED not NULL | +---------------+-----------+-----------+------------+------------+------------------+-------+----------------- --------+-------------+--------------------------------+ 3 rows in Set (0.14 sec)

Summarize

The above is about the MySQL procedure analyse () function of the entire content, I hope the content of this article for everyone's study or work can bring certain help, if there are questions you can message exchange.