MySQL优化笔记02 — 列类型的选择

忘是亡心i 2022-03-15 03:44 231阅读 0赞

### **1、列选择原则** ###

**字段类型优先级排序：**

整型 > date,time > enum,char > varchar > blob

**列的特点分析:**

整型: 定长,没有国家/地区之分,没有字符集的差异

time: 定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;

enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化

char: 定长, 考虑字符集和(排序)校对集

varchar: 不定长, 要考虑字符集的转换与排序时的校对集,速度慢.

text/Blob: 无法使用内存临时表

**附: 关于date/time的选择,大师的明确意见**

[http://www.xaprb.com/blog/2014/01/30/timestamps-in-mysql][http_www.xaprb.com_blog_2014_01_30_timestamps-in-mysql]

**性别:  以utf8为例**

char(1) , 3个字长字节

enum(‘男’,’女’);  // 内部转成数字来存,多了一个转换过程

tinyint() ,  // 0 1 2 // 定长1个字节.

### 2、够用就行,不要慷慨 (如smallint,varchar(N)) ###

**原因:** 大的字段浪费内存,影响速度,

以年龄为例 tinyint unsigned not null, 可以存储255岁, 足够. 用int浪费了3个字节

以varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存

### 3、尽量避免用NULL() ###

**原因:**

NULL不利于索引,要用特殊的字节来标注.

在磁盘上占据空间其实更大.

**实验:**

可以建立2张字段相同的表, 一个允许为null, 一个不允许为Null, 各加入1万条, 查看索引文件的大小.

可以发现, 为null的索引要大些.

(mysql5.5里,关于null已经做了优化,大小区别已不明显)

**另外:** null也不便于查询,

where 列名=null;

where 列名!=null; 都查不到值,

where 列名 is null, 或is not null 才可以查询.

create table dictnn (
    
    id int,
    
    word varchar(14) not null default '',
    
    key(word)
    
    )engine myisam charset utf8;

create table dictyn (
    
    id int,
    
    word varchar(14),
    
    key(word)
    
    )engine myisam charset utf8;

alter table dictnn disable keys;
    
    alter table dictyn disable keys;

insert into dictnn select id,if(id%2,word,'') from dict limit 10000;
    
    insert into dictyn select id,if(id%2,word,null) from dict limit 10000;

alter table dictnn enable keys;
    
    alter table dictyn enable keys;

**Enum列的说明**

1: enum列在内部是用整型来储存的

2: enum列与enum列相关联速度最快

3: enum列比(var)char 的弱势---在碰到与char关联时, 要转化. 要花时间.

4: 优势在于,当char非常长时,enum依然是整型固定长度.

当查询的数据量越大时,enum的优势越明显.

5: enum与char/varchar关联 ,因为要转化,速度要比enum->enum,char->char要慢,

但有时也这样用-----就是在数据量特别大时,可以节省IO.

**实验:**

create table t2 (
    
    id int,
    
    gender enum('man','woman'),
    
    key(gender)
    
    )engine myisam charset utf8;

create table t3 (
    
    id int,
    
    gender char(5) not null default '',
    
    key(gender)
    
    )engine myisam charset utf8;

alter table t2 disable keys;
    
    alter table t3 disable keys;

insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
    
    insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;

alter table t2 enable keys;
    
    alter table t3 enable keys;

select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender
    
    select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender

如果t2表的优势不明显, 加大t3的gender列, char(15), char(20)...

随着t3 gender列的变大, t2表优势逐渐明显.

**原因：**

无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长,内部都是用整型表示, 在内存中产生的数据大小不变,

而char型,却在内存中产生的数据越来越多.

**总结: enum 和enum类型关联速度比较快**

Enum 类型 节省了IO

[http_www.xaprb.com_blog_2014_01_30_timestamps-in-mysql]: http://www.xaprb.com/blog/2014/01/30/timestamps-in-mysql/

MySQL优化笔记02 — 列类型的选择

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL列的类型

相关 MySQL性能优化(二)：选择优化的数据类型

相关 MySQL复习笔记(02)：MySQL数据类型汇总及选择参考

相关 mysql 存储数据类型的选择_十七、MySQL基础系列笔记之选择合适的数据类型

相关 mysql设计数据表类型选择_mysql表设计----列类型介绍

相关 MySQL常见数据类型的深入理解以及数据类型的选择优化

相关 MySQL 请选择合适的列！

相关 mysql优化_建表字段类型varchar，char的选择

相关 MySQL优化笔记02 — 列类型的选择

相关《高性能MySQL》笔记——MySQL建表数据类型的选择