当你作为数据库设计者，应寻找最有效的方式来来组织你的数据库的结构，表的结构，还有列的结构。尽量优化应用程序代码，当你最小化 I/O，将相关的项目放在一起，以及提前计划的数据库设计，随着数据量的增加，数据库的性能却依旧保持不会降低才达到了设计初衷。一个有效的数据库设计能便于团队成员编写高性能的应用程序代码，并使数据库可能会一如既往地应用进化和重写。
设计表时尽量减少表所占空间，这能减少大部分数据在硬盘上的读写获得极大改善。执行sql的时候，对于同一个sql，操作更小的表普遍会花费更少的内存开销。同时，这样的表的索引也小，执行更快。当然这也和表的存储引擎有关，这里先不说innoDB和其他引擎带来的差异。
1.表的字段优化
1.1 精简数据字段类型
尽可能的使用最精简的数据字段类型。mysql针对保存在硬盘和内存的数据类型有很多而且很专业。例如，当大小允许够用的时候使用mediumint代替int，一般来说mediumint占用的空间为int的四分之三(三个字节)。
1.2 阐明字段的NOT NULL
尽可能的阐明字段的NOT NULL属性，这样SQL解释执行的时候不必对于每个值是否为空进行检测而增加额外的开销。如果需求允许为空呢，那就只能避免每一列都为空的情况。
1.3 数字和字符的处理
存储数据的时候应当根据MySQL支持的相对应数据类型设计字段。例如座位号为21，虽然可以用char存储，最好还是用数字类型，方便SQL内部操作和存储管理。当表中的blog字段数据大小小于8kb的时候，建议使用varchar代替，这样在操作的时候可以使用memory存储引擎加快处理速度。
1.4 字段分离
字段使用频率肯定是有区别的，ID，主键，索引这些的使用领率往往开销很大，但是MySQL读取数据的时候是按照一块block来读取的，一个block包含很多行。所以尽量减少行的size(行的size=各个字段的大小合)，我们可以精简字段，也可以拆分常用和不常用的字段，这样一来，常用的在一个表经常调用修改而其他不常用的在另外一个表里相当安静。
2.表的行优化
2.1 COMPACT和REDUNDANT
使用InnoDB 引擎的表在存储的时候非常紧凑，一般采用ROW_FORMAT=COMPACT(老版本可能还可选冗余型的REDUNDANT)。这样的行存储方式大约减少20%的空间开销，但也会对于某些操作的执行增加些CPU的开销，所以CPU有限制的不建议使用ROW_FORMAT=COMPACT。打个比方，当使用冗余型的REDUNDANT，对于utf-8字符集来说，N个char类型占三*N个字节，但是现在很多主流编程语言都是用单字节的utf-8编码，这样会多出了2*N字节。使用COMPACT的话空间使用为N到3*N之间的代价。
2.2 COMPRESSED
有没有占用更少的行存储方式，肯定有:COMPRESSED 。在MyISAM 和InnoDB 中都可以设定ROW_FORMAT=COMPRESSED ,但是有点区别，前者只能读，后者是可读和可写的。
2.3 Fixed和Dynamic
在mysql中,若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的ROW_FORMAT是Fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。
若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，即该表的ROW_FORMAT是Dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。
所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。
3.索引优化
4.关于表与表之间的优化
表间优化无他，使用join的建议
4.1 在某些情况下，可以将表一分为二进行自身join进行扫描；或者当表为动态表的时候，采用较小的静态表对动态表进行join会有不错的效果。
4.2 在俩个表join的时候对于相同的字段应该尽量声明相同的数据信息，以便加快join速度。
4.3 简化连接的列名称长度，以便在跨表连接的时候简化连接查询。再有比如user表有name字段，使用name而不是user_name。如果要移植数据到其他mysql服务器，保证其长度少于18个字符。
5.数据库设计标准的一些建议
5.1 通常情况下，尽量保持所有数据冗余(第三范式的数据库理论中提到)。而不是重复冗长的值，例如姓名和地址，它们对应有唯一的 Id(在另外一张表)，尽量跨最少的表，通过join方式或者外键关联的方式拿到另外表中的Id字段，来保证第三范式。
可能有人要问为什么要这么设计，明显数据会有冗余，而且复杂化。原因很简单，通常把一个数据库分成两个或多个表并定义表之间的关系以做到数据隔离，添加、删除和修改某个字段只需要在一个表中进行，接着可以通过定义的关系传递到数据库中剩余的表中(和分层思想的意义所在很相似)。这样我们可以消除很多错误或垃圾数据出现的机会并减轻更新信息所必要的工作量。
5.2 当mysql的处理速度比硬盘空间或者维护副本的成本重要的时候，可以适当的不满足数据库原理中的范式要求，毕竟东西死的人是活的，
现在经常见到很多什么云应用的数据库设计，可能所有表的索引都可以没有或者所有的字段默认建一个index，再有甚者有多少种where就建多少个index。假如一个用户对于数据库的开销达百万级别，那数据库设计标准就显得繁琐而且任务重了，只要合理操作，保证数据正确正常即可，不必自己折腾自己。
5.3 有快速的重要信息可以通过创建摘要表来提升处理速度。
6.使用PROCEDURE ANALYSE分析结构
6.1
语法：
SELECT … FROM … WHERE … PROCEDURE ANALYSE([max_elements,[max_memory]])
举例：
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
ANALYSE()函数是针对查询结果的每一列进行分析，并给出建议。
max_elements：默认为256的distinct类型取值，即查询结果每一列最大取值集合的大小。
max_memory；默认为8192，每一列所有不同值都存在情况下可能分配的最大的内存数量大小。
6.2 现有一个表ip如下
mysql> desc ip;
+——-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————–+——+—–+———+——-+
| id | int(11) | NO | PRI | NULL | |
| ip | varchar(20) | NO | | NULL | |
| port | mediumint(4) | NO | | NULL | |
+——-+————–+——+—–+———+——-+
3 rows in set
里面的数据为：
mysql> select * from ip;
+—-+—————–+——+
| id | ip | port |
+—-+—————–+——+
| 1 | 183.207.228.122 | 80 |
| 2 | 121.14.4.122 | 80 |
| 3 | 121.14.3.82 | 80 |
| 4 | 121.14.2.252 | 80 |
+—-+—————–+——+
使用ANALYSE分析
mysql> select * from ip PROCEDURE ANALYSE(1);
+————–+————–+—————–+————+————+——————+——-+————————-+——–+——————————————————————————+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std | Optimal_fieldtype |
+————–+————–+—————–+————+————+——————+——-+————————-+——–+——————————————————————————+
| test.ip.id | 1 | 4 | 1 | 1 | 0 | 0 | 2.5000 | 1.1180 | TINYINT(1) UNSIGNED NOT NULL |
| test.ip.ip | 121.14.2.252 | 183.207.228.122 | 11 | 15 | 0 | 0 | 12.5000 | NULL | ENUM(‘121.14.2.252’,’121.14.3.82’,’121.14.4.122’,’183.207.228.122’) NOT NULL |
| test.ip.port | 80 | 80 | 2 | 2 | 0 | 0 | 80.0000 | 0.0000 | TINYINT(2) UNSIGNED NOT NULL |
+————–+————–+—————–+————+————+——————+——-+————————-+——–+——————————————————————————+
3 rows in set
可以很清楚的看到数值分布情况，数值平均长度还有MySQL推荐的数据类型，根据需求选择合适的修改即可。
扫一扫
469
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
