数据结构设计（表，索引的创建）的说明

author：skate
time：2012/05/24

 

数据结构设计（表，索引的创建）的说明

表的设计

1.数据类型的选择

1) 数字类型：分为整数数据类型和浮点数据类型，它们之间的区别是取值范围不同，存储空间也各不相同

TINYINT：1字节 非常小的正整数，带符号：-128~127，不带符号：0~255
SMALLINT：2字节 小整数，带符号：-32768~32767，不带符号：0~65535
MEDIUMINT：3字节 中等大小的整数，带符号：-8388608~8388607，不带符号：0~16777215
INT：4字节 标准整数，带符号：-2147483648~2147483647，不带符号：0~4294967295
BIGINT：8字节 大整数，带符号：-9223372036854775808~9233372036854775807，不带符 号：0~18446744073709551615

对于数据量比较大的库，应该详细区分TINYINT/INT/BIGINT(经常用到的类型)，为了节省存储空间和提高数据库处理效率，
应根据应用数据的取值范围来选择一个最适合的数据列类型。

FLOAT(M,D)：4字节 单精度浮点数，最小非零值：+-1.175494351E-38，最大非零值：+-3.402823466E+38
DOUBLE(M,D)：8字节 双精度浮点数，最小非零值：+-2.2250738585072014E-308，最大非零值：+-1.7976931348623157E+308
DECIMAL(M,D)：M+2字节 以字符串形式表示的浮点数，它的取值范围可变，由M和D的值决定。

对于浮点数据列，存入的数值会被该列定义的小数位进行四舍五入，但DECIMAL 与FLOAT和DOUBLE的区别是：DECIMAL类型的
值是以字符串的形式被储存起来的，它的小数位数是固定的。它的优点是，不会象FLOAT和DOUBLE类型数据列那样进行四舍
五入而产生误差，所以很适合用于财务计算；而它的缺点是：由于它的存储格式不同，CPU不能对它进行直接运算，从而影
响运算效率。DECIMAL(M，D)总共要占用M+2个字节。

尽量不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使
用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。

 

数值类数据列的属性：
ZEROFILL：适用于所有数值类数据列类型，作用是，如果数值的宽度小于定义的显示宽度，则在数值前填充0。
UNSIGNED：不允许数据列出现负数，在确定列部委负数时，强烈需要这个属性。
AUTO_INCREMENT：属性可生成独一无二的数字序列。只对整数类的数据列有效。
NULL和NOT NULL：设置数据列是否可为空，在应用不需要null时，强烈建议给列设置默认值。
DEFAULT：可为数据列指定默认值。

TINYINT>SMALLINT>MEDIUMINT>INT>BIGINT>DECIMAL(存储空间逐渐变大,而性能却逐渐变小)。

 

2) 字符类型
BINARY(M)， CHAR[(M)]： M字节 M字节
VARBINARY(M)， VARCHAR[(M)]： M字节 L+1字节
TINYBLOD，TINYTEXT： 2^8-1字节 L+1字节
BLOB，TEXT： 2^16-1字节 L+2
MEDIUMBLOB，MEDIUMTEXT： 2^24-1字节 L+3
LONGBLOB，LONGTEXT： 2^32-1字节 L+4
ENUM('value1','value2',...)： 65535个成员 1或2字节
SET('value1','value2',...)： 64个成员 1,2,3,4或8字节

L+1、L+2是表示数据列是可变长度的，它占用的空间会根据数据行的增减而改变。数据行的总长度取决于
存放在这些数据列里的数据值的长度。L+1或L+2里多出来的字节是用来保存数据值的长度的。在对长度可
变的数据进行处理时，MySQL要把数据内容和数据长度都保存起来。

 

字符串类型的值被保存为一组连续的字节序列，并会根据它们容纳的是二进制字符串还是非二进制字符而被区别对待为字节或者字符：

二进制字符串被视为一个连续的字节序列，与字符集无关。MySQL把BLOB数据列和带BINARY属性的CHAR和VARCHAR数据列里的数据 当作二进制值。
非二进制字符串被视为一个连续排列的字符序列。与字符集有关。MySQL把TEXT列与不带BINARY属性的CHAR和VARCHAR数据列里的数据当作二进制值对待。

非二进制字符串，即我们通常所说的字符串，是按字符在字符集中先后次序进行比较和排序的。而二进制字符串
因为与字符集无关，所以不以字符顺序排序， 而是以字节的二进制值作为比较和排序的依据。

二进制字符串与字符集无关，所以无论按字符计算还是按字节计算，二进制字符串的长度都是一样的。所以VARCHAR(20)并不表示它最多能容纳20个字符，而是表示它最多只能容纳可以用20个字节表示出来的字符。对于单字节字符集，每个字符只占用一个字节，所以这两者的长度是一样的，但对于多字节字符集，它能容纳的字符个数肯定少于20个。

 

CHAR和VARCHAR是最常用的两种字符串类型，它们之间的区别是：
A.CHAR是固定长度的，每个值占用相同的字节，不够的位数MySQL会在它的右边用空格字符补足。
B.VARCHAR是一种可变长度的类型，每个值占用其刚好的字节数再加上一个用来记录其长度的字节,即L+1字节。

如何选择CHAR和VARCHAR，这里给出两个原则：

A.  如果数据都有相同的长度，选用VARCHAR会多占用空间，因为有一位用来存储其长度。如果数据长短不一， 选用VARCHAR能节省存储空间。而CHAR不论字符长短都需占用相同的空间，即使是空值也不例外。
B. 如果长度出入不大，而且是使用MyISAM或ISAM类型的表，则用CHAR会比VARCHAR好，因为MyISAM和ISAM类型的表对处理固定长度的行的效率高。经过我的测试，innodb处理char也比varchar的性能高。

 

BLOB和TEXT
BLOB是二进制字符串，TEXT是非二进制字符串。两者都可存放大容量的信息，而且可以创建前缀索引。所以删
除和修改操作容易在数据表里产生大量的碎片，需定期运行OPTIMIZE TABLE以减少碎片和提高性能。

ENUM和SET

ENUM和SET都是比较特殊的字符串数据列类型，它们的取值范围是一个预先定义好的列表。ENUM或SET数据列的
取值只能从这个列表中进行选 择。ENUM和SET的主要区别是，ENUM只能取单值，SET可取多值；ENUM和SET的值
是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。

 

总的来说，为字符列选择类型时，尽量不要使用 TEXT 数据类型，lob类型更是要坚决杜绝，它的处理方式决定
了它的性能要低于char或者是varchar类型的处理。定长字段，建议使用CHAR类型，不定长字段尽量使用 VARCHAR，
且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会
有不一样的存储处理。对于状态字段，可以尝试使用ENUM来存放，因为可以极大的降低存储空间，而且即使需要
增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝
试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。

3) 日期,时间型数据列类型

DATE：(1000-01-01~9999-12-31) 3字节(MySQL3.23版以前是4字节 ) 0000-00-00
TIME： (-838:59:59~838:59:59) 3字节 00:00:00
DATETIME: (1000-01-01 00:00:00~9999-12-31 23:59:59) 8字节 0000-00-00 00:00:00
TIMESTAMP: (19700101000000~2037年的某个时刻) 4字节 00000000000000
YEAR: (YEAR(4)：1901~2155 YEAR(2)：1970~2069) 1字节 0000

DATATIME里的时间值和TIME值是有区别的，DATATIME里的时间值代表的是几点几分，TIME值代表的是所花费的时间。
当向TIME数据列插值时，需用时间的完整写法，如12分30秒要写成“00:12:30”。

如果把一个NULL值插入TIMESTAMP列，这个数据列就将自动取值为当前的日期和时间。
在创建和修改数据行时，如果没有明确对TIMESTAMP数据列进行赋值，则它就会自动取值为当前的日期和时间。如果
行中有多个TIMESTAMP列，只有第一个会自动取值,其他timestamp列不变。

总的来说，尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME类型的一半。对于只需要精确到某一天的数据
类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少

2. 适当拆分/适当冗余
A.当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候
  都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样
  做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内
  存中的缓存命中率。
B.被频繁引用且只能通过Join 2张（或者更多）大表的方式才能得到的独立小字段,这样的场景由于每次Join仅仅只
  是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来
  优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新

3.尽量使用 NOT NULL
NULL类型比较特殊，使SQL难优化。虽然 MySQL NULL类型和Oracle的NULL 有差异，会进入索引中，但如果是一个组
合索引，那么这个NULL类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用
额外的存放空间。

eg：
mysql> CREATE TABLE child(
    -> child_id INT UNSIGNED NOT NULL,
    -> parent_id INT UNSIGNED,
    -> PRIMARY KEY(child_id),
    -> INDEX(parent_id));
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from child where parent_id=42;
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | child | ref  | parent_id     | parent_id | 5       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql>

parent_id列为空，相应的索引键长为5

mysql分析下parent_id列的建议类型
mysql> SELECT parent_id FROM child PROCEDURE ANALYSE(5,2000)\G
*************************** 1. row ***************************
             Field_name: test.child.parent_id
              Min_value: NULL
              Max_value: NULL
             Min_length: 0
             Max_length: 0
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 0.0
                    Std: 0.0
      Optimal_fieldtype: CHAR(0) NOT NULL 
1 row in set (0.00 sec)

更改parent_id列不为空
mysql> ALTER TABLE child MODIFY parent_id INT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from child where parent_id=42;
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | child | ref  | parent_id     | parent_id | 4       | const |    1 | Using index |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

这时再看parent_id相应的索引键长为4了，所以为null的列，在索引里会占有空间的

 

4.索引的优化
索引需要额外的维护成本、访问成本和空间成本，所以创建索引一定要谨慎，使单个索引尽量覆盖多的sql，更新频率比较
高的表要控制索引的数量。

A.对于非常大更新量的数据，索引的维护成本会非常高，如果其检索需求很少，而且对检索效率并没有非常高的要求的时候，
  并不建议创建索引，或者是尽量减少索引。
B.对于数据量极小到通过索引检索还不如直接遍历来得快的数据，也并不适合使用索引。
C.应该尽量让查找条件尽可能多的在索引中，尽可能通过索引完成所有过滤，回表只是取出额外的数据字段。
D.字段的顺序对组合索引效率有至关重要的作用，过滤效果越好的字段需要更靠前
E.需要读取的数据量占整个数据量的比例较大或者说索引的过滤效果并不是太好的时候，使用索引并不一定优于全表扫描。
F.在实际使用过程中，一次数据访问一般只能利用1个索引，这一点在索引创建过程中一定要注意，不是说一条SQL语句中
  Where子句里面每个条件都有索引能对应上就可以了.

 

------end-----