软件开发性能优化系列之表设计、主键、索引设计

树表设计：

      树状表都是使用ID和IDParent两个字段来表示树关系。对树进行查找只能使用自关联方式，不光写法麻烦而且记录多的时候查询性能会非常差。建议在设计树表的时候可以考虑加入treePath字段，记载到该节点记录需要经历的树路径。虽然会增加Insert和Update的成本。但是对查询树关系非常有帮助。可以避免大部分的自关联查询。

分区表：

      记录超过一百万的表要考虑是否需要使用分区表。对于能够明确确定分区字段，并且经常通过分区访问记录的。分区表会提高查询性能。

冗余字段：

一些关系查询只查询类似Code、Name等很少的字段。可以考虑将频繁需要关联查询的这种字段冗余到主表中。这种表设计会要求同步更新两个字表，对于大部分查询，会减少表的关联，提高查询性能。对于自定义项引用自由项的需求，使用这种冗余设计能保证报表查询的便利，能避免联查表提高了查询效率。

Code、Name字段长度

     如果业务表中的Code、Name需要建立唯一索引，那Code长度小于nvarchar(32),Name长度小于nvarchar(200)比较合适，不要超过255，避免超过索引键长度900bytes的限制

字段命名

      字段命名试用版不要使用SQLServer关键字，此问题虽然和性能无关，但却是最容易引发BUG的因素。如果在开发阶段不杜绝这一问题，以后再修改会增加很多代码修改等连带成本。

无主键、索引或者没有查询索引无效，是产品查询慢的最常见问题，以下是数据库表主键和索引设计的主要原则

1、主键

主键ID，主键既是约束也是索引，同时也用于对象缓存的键值。

2、索引

       *组合或者引用关系的子表（数据量较大的时候），需要在关联主表的列上建立非聚集索引（如订单明细表中的产品ID字段、订单明细表中关联的订单ID字段）

      *索引键的大小不能超过900个字节，当列表的大小超过900个字节或者若干列的和超过900个字节时，数据库将报错。

      *表中如果建有大量索引将会影响INSERT、UPDATE和DELETE语句的性能，因为在表中的数据更改时，所有的索引都将必须进行适当的调整。需要避免对经常更新的表进行过多的索引，并且索引应保持较窄，就是说：列要尽可能的少。

      *为经常用于查询的谓词创建索引，如用于下拉参照快速查找的code、name等。在平台现有下拉参照的查询sql语句中的like条件语句要改成不带前置通配符。还有需要关注Order By和Group By谓词的索引设计，Order By和Group By的谓词是需要排序的，某些情况下为Order By和Group By的谓词建立索引，会避免查询时的排序动作。

      *对于内容基本重复的列，比如只有1和0，禁止建立索引，因为该索引选择性极差，在特定的情况下会误导优化器做出错误的选择，导致查询速度极大下降。

      *当一个索引有多个列构成时，应注意将选择性强的列放在前面。仅仅前后次序的不同，性能上就可能出现数量级的差异。

      *对小表进行索引可能不能产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长，设计索引时需要考虑表的大小。记录数不大于100的表不要建立索引。频繁操作的小数量表不建议建立索引（记录数不大于5000条）