数据库类型空间效率探索(四)-tinyint与enum与set
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了数据库类型空间效率探索(四)-tinyint与enum与set,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9426字,纯文字阅读大概需要14分钟。
内容图文
![数据库类型空间效率探索(四)-tinyint与enum与set](/upload/InfoBanner/zyjiaocheng/483/cc62c428c1c741b0ba67287211f61219.jpg)
mysql> select count(*) from userinfo;
+----------+
| count(*) |
+----------+
| 115597 |
+----------+
1 row in set (0.00 sec)
mysql> select concat(truncate(sum(data_length)/1024/1024,3),‘MB‘) as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,3),‘MB‘) as max_data_leng
th,
-> concat(truncate(sum(data_free)/1024/1024,3),‘MB‘) as data_free,
-> concat(truncate(sum(index_length)/1024/1024,3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 21.477MB | 268435455.999MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
以下测试tinyint
mysql> ALTER TABLE `userinfo`
-> ADD COLUMN `type` tinyint NOT NULL DEFAULT 0 COMMENT ‘反应类型‘ AFTER `i
ntegral`;
Query OK, 115597 rows affected (0.54 sec)
Records: 115597 Duplicates: 0 Warnings: 0
mysql> select concat(truncate(sum(data_length)/1024/1024,3),‘MB‘) as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,3),‘MB‘) as max_data_leng
th,
-> concat(truncate(sum(data_free)/1024/1024,3),‘MB‘) as data_free,
-> concat(truncate(sum(index_length)/1024/1024,3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 21.477MB | 268435455.999MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
mysql> insert into userinfo(app,imei,type) values(‘‘,‘0‘,43);
Query OK, 1 row affected (0.00 sec)
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘
-> ;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 21.478MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.00 sec)
mysql> update userinfo set type=30;
Query OK, 115598 rows affected (2.70 sec)
Rows matched: 115598 Changed: 115598 Warnings: 0
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 22.038MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.00 sec)
以下测试enum
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 21.478MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
mysql> ALTER TABLE `userinfo`
-> ADD COLUMN `type` enum(‘未知‘,‘化合‘,‘分解‘,‘置换‘,‘复分解‘,‘取代‘,‘加成‘
,‘消去‘,‘加聚‘,‘酯化‘,‘水解‘,‘聚合‘,‘缩聚‘,‘吸热‘,‘放热‘,‘氧化‘,‘还原‘) AFTER `i
ntegral`;
Query OK, 115597 rows affected (0.63 sec)
Records: 115597 Duplicates: 0 Warnings: 0
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 21.694MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.00 sec)
mysql> update userinfo set type=‘复分解‘;
Query OK, 115597 rows affected (2.54 sec)
Rows matched: 115597 Changed: 115597 Warnings: 0
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 21.694MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.00 sec)
以下测试set
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘
-> ;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 21.478MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE `userinfo`
-> ADD COLUMN `type` set(‘未知‘,‘化合‘,‘分解‘,‘置换‘,‘复分解‘,‘取代‘,‘加成‘,
‘消去‘,‘加聚‘,‘酯化‘,‘水解‘,‘聚合‘,‘缩聚‘,‘吸热‘,‘放热‘,‘氧化‘,‘还原‘) AFTER `in
tegral`;
Query OK, 115597 rows affected (0.61 sec)
Records: 115597 Duplicates: 0 Warnings: 0
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 21.590MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.01 sec)
mysql> update userinfo set type=‘加成‘;
Query OK, 115597 rows affected (3.63 sec)
Rows matched: 115597 Changed: 115597 Warnings: 0
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 23.235MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.00 sec)
mysql> update userinfo set type=‘加成,取代,消去,放热‘;
Query OK, 115597 rows affected (4.37 sec)
Rows matched: 115597 Changed: 115597 Warnings: 0
mysql> select concat(round(sum(data_length/1024/1024),3),‘MB‘) as data_size,
-> concat(round(sum(max_data_length/1024/1024),3),‘MB‘) as max_data_length,
-> concat(round(sum(data_free/1024/1024),3),‘MB‘) as data_free,
-> concat(round(sum(index_length/1024/1024),3),‘MB‘) as index_length
-> from information_schema.tables where table_name=‘userinfo‘;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_length |
+-----------+-----------------+-----------+--------------+
| 23.235MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in set (0.01 sec)
数据库类型空间效率探索(四)-tinyint与enum与set
标签:sql set format 库类 truncate ica form nbsp tables
本文系统来源:http://www.cnblogs.com/shixm/p/6881231.html
内容总结
以上是互联网集市为您收集整理的数据库类型空间效率探索(四)-tinyint与enum与set全部内容,希望文章能够帮你解决数据库类型空间效率探索(四)-tinyint与enum与set所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。