MySQL uses the procedure analyze() function to optimize the table structure

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

I don’t know if you have ever been depressed about what type of fields to choose for the MySQL table structure? Or are you confused about what length to choose for a MySQL field? In the following article, I will introduce to you a martial arts secret that I only learned recently. Friends who are interested can take a look below.

Introduction

The procedure analyze() function is MySQL’s built-in field type that performs statistical analysis on MySQL field values ​​and gives suggestions.

Syntax

procesure analyse(max_elements,max_memory)

max_elements

Specifies the maximum value of non-duplicate values ​​in each column. When this value is exceeded, MySQL will not recommend the enum type.

max_memory

analyse() finds the maximum memory size used for all distinct values ​​for each column.

Practical Exercise

# 对t1表所有的列进行分析
wing@3306>show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                   |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1  | CREATE TABLE `t1` (
 `id` int(11) DEFAULT NULL,
 `name` varchar(16) DEFAULT NULL,
 `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
wing@3306>select * from t1 procedure analyse(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)

Summary

The above is all about the procedure analyze() function in MySQL. For more related content, please pay attention to the PHP Chinese website ( www.php.cn)!