专栏序
对于关系型数据库来说,表的设计 以及 SQL 的写法 尤为重要,占据性能的 90% 都不为过,所以这次专门针对这两大类知识领域,为您抽丝剥茧详细分析。
本专栏语言通俗易懂,选取大量示例为您详细说明个中奥妙~
面向的对象:
- MySQL DBA
- MySQL 开发者
- 想从其他数据库转到 MySQL 的同学
第 01 期:MySQL 数据类型的艺术
那今天的开篇即为 MySQL 的数据类型,在这里主要包含了两部分:
MySQL 字段类型的选择,字段类型定义的合适与否直接影响到磁盘空间的占用以及 MySQL 性能极致的发挥。
MySQL 数据类型在存储程序中的应用,如存储函数,存储过程,触发器等。
MySQL 数据类型介绍
MySQL 数据类型细分下来,大概有以下几类:
- 数值,典型代表为 tinyint,int,bigint
- 浮点/定点,典型代表为 float,double,decimal 以及相关的同义词
- 字符串,典型代表为 char,varchar
- 时间日期,典型代表为 date,datetime,time,timestamp
- 二进制,典型代表为 binary,varbinary
- 位类型
- 枚举类型
- 集合类型
以下内容,我们在另一篇文章介绍
- 大对象,比如 text,blob
- json 文档类型
一、数值类型(不是数据类型,别看错了)
如果用来存放整数,根据范围的不同,选择不同的类型。
以上是几个整数选型的例子。整数的应用范围最广泛,可以用来存储数字,也可以用来存储时间戳,还可以用来存储其他类型转换为数字后的编码,如 IPv4 等。
示例 1
用 int32 来存放 IPv4 地址,比单纯用字符串节省空间。表 x1,字段 ipaddr,利用函数 inet_aton,检索的话用函数 inet_ntoa。
mysql-(ytt/3305)->create table x1( ipaddr int unsigned);Query OK, 0 rows affected (0.04 sec)mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.171'));Query OK, 1 row affected (0.01 sec)mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.172'));Query OK, 1 row affected (0.01 sec)mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.173'));Query OK, 1 row affected (0.01 sec)mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.174'));Query OK, 1 row affected (0.01 sec)
更改表结构,加一个虚拟列来转换字段 ipaddr。
mysql-(ytt/3305)->alter table x1 add column ->ipaddr_real varchar(20) generated always as (inet_ntoa(ipaddr)) virtual;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql-(ytt/3305)->select * from x1;+------------+---------------+| ipaddr | ipaddr_real |+------------+---------------+| 3232236203 | 192.168.2.171 || 3232236204 | 192.168.2.172 || 3232236205 | 192.168.2.173 || 3232236206 | 192.168.2.174 |+------------+---------------+4 rows in set (0.00 sec)
示例 2
简单的对比占用磁盘空间大小,我定义了三张表 t1,t2,t3。字段分别为 tinyint,int32,int64。每张表数据相同,记录数为 2000W 行。
mysql-(ytt/3305)->CALL `get_schema_records`();+------------+------------+------------+| t1 records | t2 records | t3 records |+------------+------------+------------+| 20000000 | 20000000 | 20000000 |+------------+------------+------------+1 row in set (1.89 sec)Query OK, 0 rows affected (1.89 sec)
查看磁盘空间占用,t3 占用最大,t1 占用最小。所以说如果整数存储范围有固定上限,并且未来也没有必要扩容的话,建议选择最小的类型,当然了对其他类型也适用。
root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl总用量 3.0G3541825 861M -rw-r----- 1 mysql mysql 860M 12月 10 11:36 t1.ibd3541820 989M -rw-r----- 1 mysql mysql 988M 12月 10 11:38 t2.ibd3541823 1.2G -rw-r----- 1 mysql mysql 1.2G 12月 10 11:39 t3.ibd
二、浮点数 / 定点数
先说 浮点数