MySQL表结构的优化和设计

服务器

浏览数:245

2019-7-22

仅供自己学习  
结论写在前面:
1、给字段选取最合适的数据类型
2、数据类型的宽度尽可能的小
3、给where条件的字段设置索引
4、允许部分数据冗余
5、字段要尽可能的设置为not null,特别是使用了索引的字段
6、选取合适的数据库引擎  
选择正确的数据类型,对于提高性能至关重要。
下面给出几种原则,有利于帮助你选择何种类型。 1、更小通常更好。   使用最小的数据类型。——更少的磁盘空间,内存和CPU缓存。而且需要的CPU的周期也更少。   2、简单就好。   整数代价小于字符。——因为字符集和排序规则使字符比较更复杂。   1>mysql内建类型(如timestamp,date)优于使用字符串保存。   2>使用整数保存ip地址。   3、尽量避免NULL——如果计划对列进行索引,尽量避免把列设置为NULL   尽可能把字段定义为NOT NULL。——可以放置一个默认值,如‘’,0,特殊字符串。   原因:     (1)MYSQL难以优化NULL列。NULL列会使索引,索引统计和值更加复杂。     (2)NULL列需要更多的存储空间,还需要在MYSQL内部进行特殊处理。     (3)NULL列加索引,每条记录都需要一个额外的字节,还导致MyISAM中固定大小的索引变成可变大小的索引。

决定列的数据类型,我们应该遵循下面两步。

第一步、大致决定数据类型。——判断是数字,字符串还是时间等。这通常很直观。

第二步、确定特定的类型。

  很多数据类型能够保存同类型的数据,但是我们要发现,他们在存储的范围,精度和物理空间之间的差别(磁盘或内存空间)。如:datetime和timestamp能保存同样类型的数据:日期和时间。——timestamp使用datetime一半的空间;能保存时区;拥有特殊的自动更新能力。 

整数   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到128

3、MYSQL对整数类型定义宽度,比如int(1)和int(22)对于存储和计算是一样的。只规定了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只有对小数进行精确计算的时候才使用它,如保存金融数据。  
字符串类型  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操作,最后放入磁盘)或取值。——这对排序或使用基于内存的临时表尤其不好。

作者:Cheney.rain