• MySQL的表的优化和列类型的选择


    列选择原则:

    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/

    性别:  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 才可以查询.

     1 create table dictnn (
     2 id int,
     3 word varchar(14) not null default '',
     4 key(word)
     5 )engine myisam charset utf8;
     6 
     7 create table dictyn (
     8 id int,
     9 word varchar(14),
    10 key(word)
    11 )engine myisam charset utf8;
    12 
    13 alter table dictnn disable keys;
    14 alter table dictyn disable keys;
    15 
    16 insert into dictnn select id,if(id%2,word,'') from dict limit 10000;
    17 insert into dictyn select id,if(id%2,word,null) from dict limit 10000;
    18 
    19 alert table dictnn enable keys;
    20 alter table dictyn enable keys;

    Enum列的说明

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

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

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

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

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

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

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

     1 create table t2 (
     2 id int,
     3 gender enum('man','woman'),
     4 key(gender)
     5 )engine myisam charset utf8;
     6 
     7 create table t3 (
     8 id int,
     9 gender char(5) not null default '',
    10 key(gender)
    11 )engine myisam charset utf8;
    12 
    13 alter table t2 disable keys;
    14 alter table t3 disable keys;
    15 
    16 insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
    17 insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;
    18 
    19 alter table t2 enable keys;
    20 alter table t3 enable keys;
    21 
    22 mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender
    23 mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender

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

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

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

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

         Enum 类型 节省了IO

  • 相关阅读:
    JVM底层原理 内存模型+GC垃圾回收
    新Socket与网络小结
    Redis五大数据结构及基本指令用法
    MySql高级汇总-事务,索引,SQL调优,分库分表,读写分离
    笔试错题整理
    设计模式(思路)
    网络编程
    linux
    基础算法--KMP匹配字符串
    基础算法--整数二分
  • 原文地址:https://www.cnblogs.com/shangzekai/p/4717015.html
Copyright © 2020-2023  润新知