|
《Mysql实例MySQL 请选择合适的列》要点：
本文介绍了Mysql实例MySQL 请选择合适的列，希望对您有用。如果有疑问，可以联系我们。
思维导图
点击图片,可查看大图.
MYSQL学习介绍
?
情况：如果你的表结构设计不良或你的索引设计不佳,那么请你优化你的表结构设计和给予合适的索引,这样你的查询性能就能提高几个数量级.――数据越大,索引的价值越能体现出来.
?
我们要提高性能,需要考虑的因素：
1、设计架构
2、设计索引
3、评估查询性能
?
MYSQL学习
今天要讲的是表列的设计,暂不谈索引设计.我会在下一章讲索引设计.
?
选择数据类型
?
选择正确的数据类型,对于提高性能至关重要.
下面给出几种原则,有利于帮助你选择何种类型.
?
1、更小通常更好.
使用最小的数据类型.――更少的磁盘空间,内存和CPU缓存.而且需要的CPU的周期也更少.
?
2、简单就好.
整数代价小于字符.――因为字符集和排序规则使字符比较更复杂.
1>mysql内建类型(如timestamp,date)优于使用字符串保存.
2>使用整数保存ip地址.
?
3、尽量避免NULL――如果计划对列进行索引,尽量避免把列设置为NULL
尽可能把字段定义为NOT NULL.――可以放置一个默认值,如‘',特殊字符串.
原因：
(1)MYSQL难以优化NULL列.NULL列会使索引,索引统计和值更加复杂.
(2)NULL列需要更多的存储空间,还需要在MYSQL内部进行特殊处理.
(3)NULL列加索引,每条记录都需要一个额外的字节,还导致MyISAM中固定大小的索引变成可变大小的索引.
?
MYSQL学习
决定列的数据类型,我们应该遵循下面两步.MYSQL学习
第一步、大致决定数据类型.――判断是数字,字符串还是时间等.这通常很直观.MYSQL学习
第二步、确定特定的类型.MYSQL学习
很多数据类型能够保存同类型的数据,但是我们要发现,他们在存储的范围,精度和物理空间之间的差别(磁盘或内存空间).如：datetime和timestamp能保存同样类型的数据：日期和时间.――timestamp使用datetime一半的空间;能保存时区;拥有特殊的自动更新能力.?MYSQL学习
MYSQL学习
品味数据类型
?
整数
?
1、存储类型――数据范围为-2^(n-1)到2^(n-1)-1,这里的n是所需存储空间的位数.
? 类型名称 占用位数 ?? 数据范围 TINYINT? 8? -2^7 ~ 2^7-1? SMALLINT? 16 ? -2^15~2^15-1 ? MEDIUMINT ? 24 ? -2^23~2^23-1? INT? 32? -2^31~2^23-1? BIGINT ?64?? -2^63~2^63-1
?2、unsigned属性表示不允许负数,并大致把正上限提高了一倍.如TINYINT UNSIGNED保存的范围为0到255而不是-127到128MYSQL学习
?MYSQL学习
3、MYSQL对整数类型定义宽度,比如int(1)和int(22)对于存储和计算是一样的.只规定了MYSQL的交互工具(如命令行客户端)用来显示字符的个数.MYSQL学习
?MYSQL学习
MYSQL学习
实数
?
实数有分数部分(小数部分).
存储类型：FLOAT和DOUBLE,DECIMAL.
占用大小：FLOAT 4个字节,DOUBLE 8个字节.DECIMAL受到MYSQL版本影响,早期版本254个数字,5.0以上65个数字.
区别：1、FLOAT和DOUBLE支持标准浮点运算进行近似计算.
2、DECIMAL进行DECIMAL运算,CPU并不支持对它进行直接计算.浮点运算会快一点,因为计算直接在CPU上进行.
? 3、DECIMAL只是一个存储格式,在计算时会被转换为DOUBLE类型.
4、DECIMAL(18,9)使用9个字节,小数点前4个字节,小数点1个字节,小数点后4个字节.
5、DECIMAL只有对小数进行精确计算的时候才使用它,如保存金融数据.
?
MYSQL学习
?
字符串类型
?
1、varchar
(1)保存可变长字符串.
理解：比固定长度占用更少的存储空间,因为它只占用自己需要的空间.例外情况：使用ROW_FORMAT=FIXED创建的MyISAM表,它为每行使用固定长度的空间,可能会造成浪费.
(2)存储长度信息.如果定义的列小于或等于255,则使用1个字节存储长度值,假设使用latin1字符集,如varchar(10)将占用11个字节的存储空间.反过来,varchar(1000),则占用1002个字节的存储空间.
(3)节约空间,对性能有帮助.
(4)5.0版本以上,无论是取值还是保存,MySQL都会保留字符串末尾的空格.
只分配真正需要的空间
使用varchar(5)和varchar(200)保存'hello'占用空间是一样的.――这里应该指的是磁盘上的空间.
那么使用较短列有何优势?――巨大的优势
较大的列会使用更多的内存,因为MySQL通常会分配固定大小的内存块(如varchar(200)会用200个字符大小的内存空间)来保存值(然后对值进行trim操作,最后放入磁盘)或取值.――这对排序或使用基于内存的临时表尤其不好.
MYSQL学习
2、char
(1)固定长度.
(2)保存值时,去掉末尾的空格.
MYSQL学习
? ? ? ? ? ? 
? 咱们再看看varcharMYSQL学习
MYSQL学习
(3)char常用于很短字符串或长度近似相同的字符串的时候很有用.如存储用户密码的MD5哈希值,它的长度总是一样的.
char优于varchar的地方?
1>> 对于经常改变的值,char优于varchar,因为固定长度行不容易产生碎片.――当最长长度远大于平均长度,并且很少发生更新的时候,通常适合使用varchar.
2>>对于很短的列,char的效率也是高于varchar的.如对于单字节字符集(如latin1),char(1)只会占用1个字节,而varchar(2)会占用2个字节(有一个字节用来存储长度的信息).
?
MYSQL学习
?
?3、text
用于保存大量数据.
(1)InnoDB在它们较大的时候会使用“外部”存储区域来进行保存.――所以需要足够的外部存储空间来保存实际的值.
(2)排序方式不同于其他字符类型,不会按照完整长度进行排序,而只是按照max_sort_length规定的前若干个字节进行排序.
?
MYSQL学习
MYSQL学习
4、使用ENUM代替字符串类型
(1)ENUM列可以存储65 535个不同的字符串.
(2)以紧凑方式保存.根据列表中值的数量,把它们压缩到1到2个字节中.
(3)MySQL在内部把每个值都保存为整数,以表示值在列表中的位置.
(4)保留了一份“查找表”,来表示整数和字符串在表的.frm文件中的映射关系.
(5)ENUM字符列是固定的,添加、删除字符串须使用ALTER TABLE.
(6)使用案例：权限表中使用ENUM来保存Y值和N值.
?使用方法：
MYSQL学习
? ? ? ? ? ?
??MYSQL学习
MYSQL学习
在对enum列使用order by的时候,是按数字排序的,而不是字符串排序.MYSQL学习
MYSQL学习
日期和时间类型MYSQL学习
DATETIME：保存大范围的值.封装格式：YYYYMMDDHHMMSS.――与时区无关,使用8字节存储空间.
TIMESTAMP：保存自1970年1月1日午夜(格林尼治标准时间)以来的秒数.――使用4字节存储空间.MYSQL学习
通常使用TIMESTAMP,它比DATETIME更节约空间.有时人们把Unix的时间戳保存为整数值,但是这通常没有任何好处.――这种格式处理起来不太方便,我们并不推荐它.MYSQL学习
??
?经验交谈
?
1、我们在为列选择数据类型的时候,不仅要考虑存储类型大小,还要考虑MySQL如何对它们进行计算和比较.例如：MySQL在内部把ENUM和SET类型保存为整数,但是在比较的时候把它们转换为字符串.
?
2、我们要在相关表中使用同样的类型,类型之间要精确匹配,包括诸如UNSIGNED这样的属性.
?
3、混合不同的数据类型会导致性能问题,即使没有性能问题,隐式的类型转换也能导致难以察觉的错误.
?
4、选择最小的数据类型要考虑将来留出的增长空间.如,中国的省份,我们知道不会有成千上万个,因此不必用INT.TINYINT就足够了,它比INT小3字节.
?
5、整数通常是最佳的数据类型,因为它速度快,并且能使用AUTO_INCREMENT.
?
6、要尽可能避免字符串做为列的数据类型,因为它们占用了很多空间并且通常必整数类型要慢.MyISAM默认情况下为字符串使用了压缩索引,这使查找更为缓慢.
?
总结
?
若有错误,望请大侠指教一、二,不胜感激!
?
参考文献:《高性能MYSQL》 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
