|
一， 数据库对象命名规范 表名，字段名，视图名，存储过程名，函数名，触发器名称统一使用大写字母命名。使用最接近数据库对象含义的英文单词，单词组合，或缩写来命名，尽可能做得见名知意。 表名： 以T_开头，比如：T_COMMONCONTENT 字段名：大写英文单词或缩写 视图名：以V_开头。 存储过程名：以P_开头 函数名：以F_开头 触发器名：以TR_开头 索引名： 以IDX_开头
二， 数据库设计 2.1 数据类型选择 选择原则：在满足需求的前提下，尽可能选择范围较小的数据类型来定义字段。涉及货币的字段都选择精确度高的decimal定长数据类型来表示。按以下优先级来选择数据类型：
高优先级 低优先级
TINYINT--------->BIGINT---àCHAR-----àVARCHAR-------àTEXT-----àLONGTEXT
在满足业务定义需求的前提下，能选择整型的就用整型，位数短的用短整型，如果位数不够就用长整型，位数逐步增加。不能用整型的优先选用字符型（CHAR）,字符型不够选用变长字符型（VARCHAR）,最后才考虑选用文本型（TEXT）。对只有一位长度的字段，比如状态值等，统一使用TINYINT类型。总之，在数据类型的选择上，做到宁短勿长，这样即能节省存储空间，又能提高处理速度。
以下是各种整数类型的存储字节数和数值表达范围：
2.2 数据库设计
2.2.1 主外键定义
每个表都要求定义主键和外键约束，通过外键的定义，可以保障数据的一致性和完整性。定义外键时，统一采用ON DELETE SET NULL ON UPDATE SET NULL方式。这种方式在删除被参照表的数据时，数据库将自动把参照表中的相关记录的相关字段置空。这样的好处是，当参照表的记录被删除时，子表记录可以完整的保存下来。
定义主键健外键例子：
CREATE TABLE customerinfo ( CustomerID INT NOT NULL , PRIMARY KEY ( CustomerID ) ) TYPE = INNODB;
CREATE TABLE salesinfo ( SalesID INT NOT NULL, CustomerID INT NOT NULL, PRIMARY KEY(CustomerID, SalesID), FOREIGN KEY (CustomerID) REFERENCES customerinfo (CustomerID) ON DELETE SET NULL ON UPDATE SET NULL ) TYPE = INNODB;
2.2.2 数据表中字段顺序
在数据表的设计时统一规定各字段依以下顺序设置：
主键---------->同步字段(version)----------->外键--------->其它字段
2.2.3 索引创建问题
索引对大数据量表查询来说，比较重要，过多过少的索引，不合理的索引都会直接影响到SQL的查询性能，建议到测试时统一由数据库支持人员创建。
2.2.4 表拆分问题 在进行数据库表设计时，尽可能使SQL查询的表关联数不要超过3个，如果表关连太多，可能需要考虑使用冗余字段来减少表关联的个数。关联表太多的SQL查询，很可能效率比较差并且优化困难。 如果设计的表包括大数据量的TEXT或BLOB类型字段，尽量把大数据量大的TEXT（BLOB）字段拆成单独的表，避免导致整个表的查询都慢。
2.2.5 数据库接口定义 表设计统一采用PowerDesigner工具完成，除设计本模块的表外，还需要标出外模块的参照表或有数据关系的表，建议用虚线标出外模块相关的表。
三， SQL编写规范 3.1 SQL简化和减少数据库查询次数 在满足业务需求的前提下，尽可能使编写的SQL简单，不要选择多余的字段和不必要的嵌套查询，越简单的SQL，查询性能可能越好，也越好进行性能优化。
比如： 不要用SELECT *：SELECT语句中写出必要的要选择的全部列名，增强语句可读性，避免不必要的选择；SELECT *增加了对所有字段的依赖，当表增加了字段后，有可能发生错误；此外还可能增加了数据的流量，查询了一些实际不需要的字段。 其它SQL查询也要求选择真正需要选择的字段，避免选择出多余的字段。 另外，尽可能减少数据库操作的次数，建议一次性查出你需要的结果集，先放到应用内存中进行遍历处理。
3.2 索引使用问题 3.2.1 在查询列上避免使用表达式
在WHERE中，数据库函数、计算表达式等等，要尽可能将放在等号右边。否则会使所比较的字段上的索引失效； SELECT ＊ FROM service_promotion WHERE TO_CHAR(gmt_modified,’yyyy-mm-dd’) = ‘20001-09-01’;
而应使用：
SELECT * FROM service_promotion WHERE gmt_modified >= DATE_FORMAT('2009-07-26 20:49:33','%Y-%m-%d'); AND gmt_modified < DATE_FORMAT('2009-07-26 20:49:33','%Y-%m-%d');
3.2.2 查询条件列类型的隐含转换 尽量注意比较值与查询列数据类型的一致性(int与int比较、char与char比较)，避免使用数据库的类型自动转换功能,比如： SELECT * FROM category WHERE id = ‘123’; -- id在表中定义为int类型
3.2.3 Like查询
在进行模糊查询时，不要使用WHERE columnname like ‘%字符串%’这样的查询形式，这样的查询语句不能使用列上所建的索引，当数据量稍微有点大就会导致有严重的性能问题。如果确实有这样的需求，应该考虑用别的方式实现。这个问题需要在表设计的时候就考虑到。
3.2.4 排序，分组查询
大量的排序操作将严重影响系统性能，所以尽量减少order by和group by排序操作。如果有大数据量的表确实存在大范围的分组查询求和运算，建议通过建立临时统计表，通过触发器或后台作业来完成统计数据的计算。对大数据量表的排序查询，除可以在排序列上建立索引外，应该尽量通过表设计加条件减少排序范围来实现排序操作。 对于进行分组求和操作的SQL，可以通过增加选择项来避免排序，比如,在sql语句的末尾增加order by null选项避免进行资源消耗量极大的排序操作。
Select id,sum(moneys) from sales2 group by id order by null;
3.2.5 视图，存储过程，函数，触发器的使用 复杂，重复性使用率高的SQL查询语句，建议定义成视图（VIEW）来使用，好处是将来可以在数据库后台进行修改，优化等维护，同时也可以简化应用端的代码编写。对操作任务量大，比如定时性的数据统计，计算任务，建议编写成存储过程，函数来实现。尽量少用触发器，特别是使用带有大量数据操作任务的触发器，那样的触发器会降低表的更新速度。而且大量使用触发器会给数据库的维护带来更大的难度。
3.2.6 使用批操作提高数据插入效率 如果数据插入量比较大，建议写成批量操作的形式，将能大大提高数据库插入的效率，比如：
INSERT INTO `T_VISITIP` VALUES (38094,'10.10.10.0',10,'2007-02- 28'), (38095,'10.10.10.1',10,'2007-02- 28'), (38096,'10.10.10.2',10,'2007-02- 28'), (38097,'10.10.10.3',10,'2007-02- 28') , ………………………………..;
begin; INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES (8,26),(6,29); ……………………… commit;
3.2.7 慎用union或union all 慎用union或union all，特别是合并后再进行排序操作的SQL,碰到数据量比较大时，进行优化会非常困难。
3.2.8 优化嵌套查询 使用连接（JOIN）来代替子查询(Sub-Queries) ，在有的情况下可能能大大提高sql执行的速度。
子查询： SELECT * FROM customerinfo WHERE CustomerID in (SELECT CustomerID FROM salesinfo ) 改写成： SELECT Ci.* FROM customerinfo ci, salesinfo sf WHERE ci. CustomerID=sf. CustomerID
四， 数据库有用知识 4.1 中文排序，大小写字母排序 如果排序时想区分大小写，并按照中文的首字母排序，请这样定义你的字符字段类型，比如：
CREATE TABLE `test1` ( `a` int(11) default NULL, `b` varbinary(30) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
排序例子： mysql> select * from test1 order by b; +------+----------+ | a | b | +------+----------+ | 1 | 1111 | | 1 | 22222 | | 1 | AA | | 1 | Ab | | 1 | Az | | 1 | aA | | 1 | aa | | 1 | 阿拉斯加 | | 1 | 地震 | | 1 | 丫头 | | 1 | 中国 | +------+----------+ 11 rows in set (0.00 sec)
4.2 字符集选择 Z-SHOP数据库将使用UTF-8多语言字符集，该字符集可以存储支持多种语言。如果是linux系统，可以在mysql配置文件/etc/my.cnf的[mysqld]部分添加 default-character-set=utf8
重启mysql数据库后，在服务器上创建的表使用的就是utf8字符集。如果是windows系统，mysql的配置文件是my.ini。客户端一般可以选用gbk字符集就能满足开发要求了。
4.3 存储引擎选择 Z-SHOP数据库将主要使用INNODB存储引擎，如果是linux系统，可以在mysql配置文件/etc/my.cnf的[mysqld]部分添加 default-storage-engine=innodb 重启mysql数据库后，在服务器上创建的表使用的就是innodb存储引擎。如果是windows系统，mysql的配置文件是my.ini。 |
|
|
