等考三级数据库设计经验谈3:选择键和索引

【前言】:一个成功的管理系统由【50%业务+50%软件】组成，而50%成功的软件由【25%数据库+25%程序】组成。数据库设计的质量是一个关键。如果把企业的数据比作生命所必需的血液，那么数据库的设计就是应用程序中最重要的部分。关于数据库设计的资料很多，大学学位课程也有专门的讲座。然而，正如我们一再强调的，没有老师比经验更好。于是我总结了自己这些年走的弯路和经验，在网上找了一些在数据库设计方面颇有造诣的专业人士，教你一些数据库设计的技巧和经验。从中挑选了60项技能，编入本文。为方便索引，内容分为五个部分:
第一部分介绍设计一个数据库前的12项基本技能，包括命名规范和定义业务需求(数据库设计经验谈(一))；第二部分介绍了设计数据库表的24个指导技巧，涵盖了表中字段的设计和应避免的常见问题(数据库设计经验谈(二))；这次的第三部分主要介绍键选择和索引，包括10个专门与系统生成主键的正确用法相关的提示，以及何时和如何为性能索引字段等。
第三部分——选择键和索引
数据挖掘要提前计划好
曾经，我工作的一个客户部门要处理8万多条联系信息，填写每个客户的必要数据(这绝对不是一件小事)。我还想确定一批客户作为市场目标。当我从一开始设计表和字段时，我尽量不在主索引中添加太多字段，以加快数据库的运行速度。然后我意识到，特定的群组查询和信息挖掘既不准确也不快速。因此，必须在主索引中重建和合并数据字段。我发现有一个指示计划是非常重要的——当我想创建一个系统类型查找时，为什么要使用数字作为主索引字段？我可以通过传真号码进行搜索，但它对我来说几乎和系统类型一样不重要。通过使用后者作为主字段，在数据库更新后，重新索引和检索数据库要快得多。
操作数据仓库(ods)和数据仓库(dw)这两种环境中的数据索引是不同的。在数据仓库环境中，你应该考虑销售部门如何组织销售活动。他们不是数据库管理员，但是他们决定表中的关键信息。在这里，设计人员或数据库人员应该分析数据库结构，以确定性能和正确输出之间的条件。
使用系统生成的主键
和技巧一是一样的，但是我觉得有必要在这里提醒一下大家。如果在设计数据库时总是使用系统生成的键作为主键，那么实际上就控制了数据库的索引完整性。这样，数据库和非手工机制有效地控制了对每一行存储数据的访问。
使用系统生成的键作为主键还有一个好处:当你拥有一致的键结构时，很容易发现逻辑缺陷。
拆分字段用于索引
要将命名字段与包含字段分开以支持用户定义的报表，请考虑将其他字段(甚至是主键)拆分成它们的组成元素，以便用户可以对它们进行索引。索引将加速sql和报表生成器脚本的执行。例如，当我必须使用类似sql的表达式时，我通常会创建报告，因为案件编号字段不能分解为诸如年份、序列号、案件类型和被告代码等元素。性能也会变差。如果年份和类型字段可以分解成索引字段，这些报告将运行得更快。
关键设计4原则
1。为相关字段创建外键。
2。所有的键都必须是。
3。避免使用组合键。
4。外键总是关联的键字段。
别忘了索引
索引是从数据库中获取数据的有效方法之一。95%的数据库性能问题可以通过索引技术解决。通常，我通常对逻辑主键使用分组索引，对系统键使用非分组索引(作为存储过程)，对任何外键列[字段]使用非分组索引。然而，索引就像盐一样。菜多了就咸了。你要考虑数据库的空空间有多大，表是怎么访问的，这些访问是否主要用于读写。
大多数数据库索引自动创建的主键字段，但是不要忘记索引外键。它们也是常用的键，例如运行查询来显示主表和所有关联表的记录。另外，不要索引memo/note字段，也不要索引大字段(有很多字符)。这样做会让索引占用太多内存空。
不要索引常用的小表
不要为小数据表设置任何键，尤其是在它们经常有插入和删除操作的情况下。这些插入和删除操作的索引维护可能比扫描表空花费更多的时间。不要选择社会安全号(ssn)或id作为键
不要使用ssn或id作为数据库的键。除了隐私原因，需要注意的是，政府越来越不允许ssn或id用于收入以外的用途，ssn或id需要手动输入。千万不要使用手动输入的键作为主键，因为一旦你犯了一个错误，你所能做的就是删除整个记录并从头开始。
我在破解别人程序的时候，看到很多人用ssn或者id做序列号，虽然这样做是违法的。而且大家都知道这是违法的，但是都习惯了。后来随着身份盗窃犯罪的增多，我现在的同事正在痛苦地从大量的数据中删除ssn或者id。
不要使用用户的键
在确定将哪个字段用作表的键时，必须小心用户将要编辑的字段。通常，不要选择用户可编辑的字段作为键。这样做会迫使你采取以下两种措施:
1。创建记录后，对用户编辑字段的行为施加限制。如果这样做，你可能会发现你的应用在业务需求上突然发生变化，用户在需要编辑那些不可编辑的字段时，缺乏足够的灵活性。当用户输入数据后直到保存记录才发现系统有问题时，应该怎么想？删除并重建？如果记录无法重建，是否希望用户离开？
2。提出了一些检测和纠正关键冲突的方法。通常要花一点功夫才能搞定，但是从性能上来说，这样做的成本比较高。此外，键修正可能会迫使您打破数据和业务/用户界面层之间的隔离。
所以还是再提一句老话吧:你的设计要适应用户，而不是让用户适应你的设计。
主键不可更新的原因是在关系模式下，主键实现了不同表之间的关联。例如，customer表有一个主键customerid，而客户的订单存储在另一个表中。订单表的主键可能是订单号或订单号、客户id和日期的组合。无论选择哪种键设置，都需要在order表中存储customerid，以确保可以找到下订单的用户的订单记录。
如果修改customer表中的customerid，必须找出order表中的所有相关记录并进行修改。否则，一些订单将不属于任何客户——数据库的完整性将被破坏。
如果将索引完整性规则应用于表级，那么在不编写大量代码和添加删除记录的情况下，几乎不可能更改数据库中一条记录的键和所有关联记录。这个过程往往错误百出，应该尽量避免。
可选键(候选键)有时可以作为主键
记住，查询数据的不是机器而是人。
如果您有一个可选键，您可以进一步将其用作主键。在这种情况下，您将有能力建立一个强大的索引。这可以避免使用数据库的人必须连接到数据库并正确地过滤数据。这种负载在严格控制域表的数据库中非常明显。如果可选键真的有用的话，已经达到主键的水平了。
我的看法是，如果你有可选的键，比如country表中的state_code，就不要在现有的键上创建不能更改的后续键。你要做的就是创建无价值的数据。如果过度使用表的后续键[alias]来建立这个表关联，那么操作负载确实需要考虑。
不要忘记外键
大多数数据库索引都会自动创建主键字段。但是不要忘记索引外键字段。每当您想要查询主表中的记录及其关联记录时，都会用到它们。还有，不要索引memo/notes字段，不要索引大的文本字段(很多字符)，这样会让你的索引占用大量数据库空。

预览:第四部分将讨论如何确保数据完整性，如何保持数据库清晰和健壮，以及如何将有害数据降至最低。