MySQL利用procedure analyse()函数优化表结构
作者:daisy 时间:2024-01-17 14:51:00
简介
procedure analyse()
函数是MySQL内置的对MySQL字段值进行统计分析后给出建议的字段类型。
语法
procesure analyse(max_elements,max_memory)
max_elements
指定每列非重复值的最大值,当超过这个值的时候,MySQL不会推荐enum类型。
max_memory
analyse()
为每列找出所有非重复值所采用的最大内存大小。
实战演练
# 对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)
总结
标签:mysql,procedure,analyse()
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python使用Pygame绘制时钟
2022-08-04 14:10:07
![](https://img.aspxhome.com/file/2023/5/105625_0s.jpg)
php生成随机密码的三种方法小结
2023-11-22 04:25:50
vue.js删除动态绑定的radio的指定项
2024-05-09 09:51:48
![](https://img.aspxhome.com/file/2023/2/127172_0s.png)
mysql8.0.11 winx64安装配置方法图文教程(win10)
2024-01-23 07:58:58
![](https://img.aspxhome.com/file/2023/3/105223_0s.png)
phpmyadmin 数据导入导出问题
2007-08-06 15:23:00
利用vue+elementUI实现部分引入组件的方法详解
2023-07-02 16:33:51
SQLServer2005安装提示服务无法启动原因分析及解决
2024-01-16 03:02:26
![](https://img.aspxhome.com/file/2023/6/100006_0s.png)
python3.8.3安装教程及环境配置的详细教程(64-bit)
2023-03-01 05:21:10
![](https://img.aspxhome.com/file/2023/4/133494_0s.jpg)
瀑布流布局代码一例
2023-08-25 07:33:19
JS判断浏览器类型与版本的实现代码
2024-05-13 10:36:32
Go channel实现原理分析
2024-05-05 09:30:29
![](https://img.aspxhome.com/file/2023/2/128652_0s.png)
python 删除excel表格重复行,数据预处理操作
2023-04-19 06:10:33
FSO遍历文件夹目录及目录下文件asp代码
2008-10-10 12:54:00
asp MD5加密方式使用建议
2011-03-30 11:17:00
一篇文章弄懂PHP和HTML的嵌套写法
2023-06-20 04:51:22
![](https://img.aspxhome.com/file/2023/3/55563_0s.png)
一看就懂得Python的math模块
2021-03-28 21:40:32
精简化的产品设计
2008-07-26 12:22:00
[译]2009年海外Web设计风潮(下)
2009-01-23 09:34:00
![](https://img.aspxhome.com/file/UploadPic/20091/23/trend-38-39s.gif)
vue监听路由变化的几种方式小结
2024-05-21 10:28:41
Python协程的四种实现方式总结
2022-12-23 04:41:59
![](https://img.aspxhome.com/file/2023/0/120900_0s.png)