认识达内从这里开始

MySQL数据库开发架构是大多数软件开发程序员都需要熟练掌握的一个编程技术，而本文我们就通过案例分析来简单了解一下，数据库优化常用方法都有哪些。

在基于表驱动的信息管理系统(MIS)中，基本表的设计规范是三范式(3NF)。三范式的基本特征是非主键属性只依赖于主键属性。基于三范式的数据库表设计具有很多优点：一是消除了冗余数据，节省了磁盘存储空间;二是有良好的数据完整性限制，即基于主外键的参照完整限制和基于主键的实体完整性限制，这使得数据容易维护，也容易移植和更新;三是数据的可逆性好，在做连接(Join)查询或者合并表时不遗漏、也不重复;四是因消除了冗余数据(冗余列)，在查询(Select)时每个数据页存的数据行就多，这样就有效地减少了逻辑I/O，每个Cash存的页面就多，也减少物理I/O;五是对大多数事务(Transaction)而言，运行性能好;六是物理设计(PhysicalDesign)的机动性较大，能满足日益增长的用户需求。

在基本表设计中，表的主键、外键、索引设计占有非常重要的地位，但系统设计人员往往只注重于满足用户要求，而没有从系统优化的高度来认识和重视它们。实际上，它们与系统的运行性能密切相关。现在从系统数据库优化角度讨论这些基本概念及其重要意义：

(1)主键(PrimaryKey)：主键被用于复杂的SQL语句时，频繁地在数据访问中被用到。一个表只有一个主键。主键应该有固定值(不能为Null或缺省值，要有相对稳定性)，不含代码信息，易访问。把常用(众所周知)的列作为主键才有意义。短主键佳(小于25bytes)，主键的长短影响索引的大小，索引的大小影响索引页的大小，从而影响磁盘I/O。主键分为自然主键和人为主键。自然主键由实体的属性构成，自然主键可以是复合性的，在形成复合主键时，主键列不能太多，复合主键使得Join*作复杂化、也增加了外键表的大小。人为主键是，在没有合适的自然属性键、或自然属性复杂或灵敏度高时，人为形成的。人为主键一般是整型值(满足小化要求)，没有实际意义，也略微增加了表的大小;但减少了把它作为外键的表的大小。

(2)外键(ForeignKey)：外键的作用是建立关系型数据库中表之间的关系(参照完整性)，主键只能从独立的实体迁移到非独立的实体，成为后者的一个属性，被称为外键。

(3)索引(Index)：利用索引优化系统性能是显而易见的，对所有常用于查询中的Where子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样减少数据存取时间;利用索引可以优化或排除耗时的分类*作;把数据分散到不同的页面上，就分散了插入的数据;主键自动建立了索引，因此索引也能确保数据的性(即实体完整性);索引码越小，定位就越直接;新建的索引效能好，因此定期更新索引非常必要。索引也有代价：有空间开销，建立它也要花费时间，在进行Insert、Delete和Update*作时，也有维护代价。索引有两种：聚族索引和非聚族索引。一个表只能有一个聚族索引，可有多个非聚族索引。使用聚族索引查询数据要比使用非聚族索引快。在建索引前，应利用数据库系统函数估算索引的大小。

①聚族索引(ClusteredIndex)：聚族索引的数据页按物理有序储存，占用空间小。选择策略是，被用于Where子句的列：包括范围查询、模糊查询或高度重复的列(连续磁盘扫描);被用于连接Join*作的列;被用于Orderby和Groupby子句的列。聚族索引不利于插入*作，另外没有必要用主键建聚族索引。

②非聚族索引(NonclusteredIndex)：与聚族索引相比，占用空间大，而且效率低。选择策略是，被用于Where子句的列：包括范围查询、模糊查询(在没有聚族索引时)、主键或外键列、点(指针类)或小范围(返回的结果域小于整表数据的20%)查询;被用于连接Join*作的列、主键列(范围查询);被用于Orderby和Groupby子句的列;需要被覆盖的列。对只读表建多个非聚族索引有利。索引也有其弊端，一是创建索引要耗费时间，二是索引要占有大量磁盘空间，三是增加了维护代价(在修改带索引的数据列时索引会减缓修改速度)。那么，在哪种情况下不建索引呢?对于小表(数据小于5页)、小到中表(不直接访问单行数据或结果集不用排序)、单值域(返回值密集)、索引列值太长(大于20bitys)、容易变化的列、高度重复的列、Null值列，对没有被用于Where子语句和Join查询的列都不能建索引。另外，对主要用于数据录入的，尽可能少建索引。当然，也要防止建立无效索引，当Where语句中多于5个条件时，维护索引的开销大于索引的效益，这时，建立临时表存储有关数据更有效。

批量导入数据时的注意事项：在实际应用中，大批量的计算(如电信话单计费)用C语言程序做，这种基于主外键关系数据计算而得的批量数据(文本文件)，可利用系统的自身功能函数(如Sybase的BCP命令)快速批量导入，在导入数据库表时，可先删除相应库表的索引，这有利于加快导入速度，减少导入时间。在导入后再重建索引以便优化查询。

(4)锁：锁是并行处理的重要机制，能保持数据并发的一致性，即按事务进行处理;系统利用锁，保证数据完整性。因此，我们避免不了死锁，但在设计时可以充分考虑如何避免长事务，减少排它锁时间，减少在事务中与用户的交互，杜绝让用户控制事务的长短;要避免批量数据同时执行，尤其是耗时并用到相同的数据表。锁的征用：一个表同时只能有一个排它锁，一个用户用时，其它用户在等待。若用户数增加，则Server的性能下降，出现“假死”现象。如何避免死锁呢?从页级锁到行级锁，减少了锁征用;给小表增加无效记录，从页级锁到行级锁没有影响，若在同一页内竞争有影响，可选择合适的聚族索引把数据分配到不同的页面;创建冗余表;保持事务简短;同一批处理应该没有网络交互。

(5)查询优化规则：在访问数据库表的数据(AccessData)时，要尽可能避免排序(Sort)、连接(Join)和相关子查询*作。经验告诉我们，在优化查询时，必须做到：

①尽可能少的行;

②避免排序或为尽可能少的行排序，若要做大量数据排序，好将相关数据放在临时表中*作;用简单的键(列)排序，如整型或短字符串排序;

③避免表内的相关子查询;

④避免在Where子句中使用复杂的表达式或非起始的子字符串、用长字符串连接;

⑤在Where子句中多使用“与”(And)连接，少使用“或”(Or)连接;

⑥利用临时数据库。在查询多表、有多个连接、查询复杂、数据要过滤时，可以建临时表(索引)以减少I/O。但缺点是增加了空间开销。

除非每个列都有索引支持，否则在有连接的查询时分别找出两个动态索引，放在工作表中重新排序。

【免责声明】：本内容转载于网络，转载目的在于传递信息。文章内容为作者个人意见，本平台对文中陈述、观点保持中立，不对所包含内容的准确性、可靠性与完整性提供形式地保证。请读者仅作参考。更多内容请加抖音太原达内IT培训学习了解。