SQL优化技术及应用

徐新静（天津天铁冶金集团有限公司计控电讯厂，河北省涉县 056404）

SQL优化技术及应用

徐新静（天津天铁冶金集团有限公司计控电讯厂，河北省涉县 056404）

随着信息化进程的加快，管理工作的逐步细化，SQL的优化直接影响着系统的运行状态和速度。根据软件设计开发过程中数据库设计和程序开发的技术和经验，通过实例总结了数据库访问SQL优化的方法和技巧，及在项目中的应用。通过优化，提高了系统运行速度，减少了系统故障，用户反馈良好。

数据库 主键 外键 索引 关联 优化

1 前言

随着信息化技术在天铁的逐步推广应用，信息化已经覆盖了包括进出厂物资的计量、设备材料及备品备件的采购发放、生产过程的控制、产成品的销售等产、供、销的各个环节。随着管理的进一步细化，数据量逐步增加，要快速有效地访问这些数据，对这些数据进行综合的分析，并且做到并发共享使用这些数据，就要想方设法优化数据结构和访问的SQL语句，提高其运行效率。

2 SQL优化方法及应用

抛却数据库系统的因素来优化SQL，不外乎从数据库设计和程序实现两个方面进行优化。在工作中同样的数据库服务器，由于不同的设计理念造成了不同的数据库设计、不同的访问速度，结果是大相径庭。怎样提高数据访问速度对一个系统程序员来说至关重要，也是一个应用系统成败的关键。

2.1 数据库设计阶段的优化

2.1.1 建表

建表时要基本满足三个范式。即：表的记录要求有惟一标识，以确定实体的惟一性；表的字段要求属性具有原子性，不可再分解；对字段冗余的约束，即表里的每一个字段都不能由别的字段衍生和计算得出。但在实际工作中可根据实际情况设置字段冗余，以达到空间换时间的效果。如称重表中有毛重、皮重、净重三个字段，净重可由毛重减去皮重获得，在大多数情况下我们关心的是进出厂货物的净重，如果每次查询、数据分析都由计算得出净重，必然耗费时间，所以增加了净重这个冗余字段以提高速度，同时也降低了SQL语句的复杂度。

例：select车号，日期，净重from＜称重表＞where净重＞60

Select车号，日期，毛重–皮重from＜称重表＞where毛重–皮重＞60

2.1.2 主键的选择

一个表只有一个主键，主键能唯一确定一个实体，其他字段就是主键所确定实体的一些属性、特点，它们之间没有相互依赖关系。主键将频繁地用于增、删、改、查询等SQL语句，主键的选择至关重要。主键一般以常用的列作为主键，主键尽可能短；对于复合主键，需要几个字段联合做主键时，主键列要尽可能地少，实在不行就人为增加一单一的主键列，因为主键的长短将影响索引的大小和外建表的大小，导致增加磁盘的I/O和表关联的难度。

2.1.3 外键

外键的作用是建立和加强两个表数据之间的链接，即关系型数据库中的表间关系,它实现了数据库表间的参照完整性约束，外键约束不仅控制存储在外键表中的数据，还可以控制对主键表中数据的修改。如果不通过外键建立这种表间关系，那么在表上进行记录的增删改时就会使数据的完整性遭到破坏。

螺纹紧固件是将汽车的部件连接起来的装置，是汽车维修技术人员接触最多的零件，现代汽车中使用了数百种紧固件。常见的紧固件如图1所示，拧紧螺栓时在螺栓上引起的力如图2所示，作用在被紧固件上的力为夹紧力。常用螺纹标准是米(公)制螺纹，少部分从美国、英国来的进口车可能用寸制螺纹(UNC，UNF)，此外车上部分传感器和螺塞还使用圆柱管螺纹和圆锥管螺纹。

例：销售系统订单表中有订单号、订货品种、订货量、订货用户编号……客户表中有订货用户编号、地址、税号、银行账号、联系方式……在客户表中订货用户编号是主键，而在订单表中订货用户编号是外键。如果删除客户表中的某个客户，必须保证订单表中没有该客户的订单，否则破坏了数据的完整性，找不到该订单的客户信息，系统无法进行下一步的发货、结算等一系列的工作。

2.1.4 索引

索引就像字典的目录，利用索引能提高数据访问的速度就不难理解了。建立索引的目的是加快对表中记录的查找或排序。但是为表设置索引是要付出代价的，它不但增加了数据库的存储空间，而且在插入和修改数据时要花费时间来维护索引，所以并不是索引越多越好，弄不好会事倍功半。这里建议在经常需要用于where子句条件、排序、分组以及多表关联的列上建立索引。而对于查询几率小、数据值少（性别取值只有男女两个）、字段数据量大（text、image等数据类型）的列则不适合建立索引。

在使用索引时要注意查询条件列的顺序和索引的关系。聚合索引条件放在前面，其次是非聚合索引，最后是普通字段条件，条件语句中用到复合索引中的起始字段才能显著提高速度，如果仅用复合索引的非起始列则没什么效果。

2.1.5 表的分割

表的字段数目过多或者根据实际情况预测表的记录数非常大，那么要考虑表的分割。对于前者可按照字段使用的频度进行垂直分割，后者按时间或记录的完整与否等规则实施水平分割。

公司产销系统在运行半年以后，一些履历表中的记录数已达到相当的规模，有数百万条之多，而且还会逐日增加，如果有多用户并发操作，同时进行增、改、查询等操作，这时就出现速度超慢或干脆服务吊死现象。为了解决这个问题，对履历表按照一定规则进行了水平分割，运行表只保留最近N天的数据，而把N天以前的数据放到历史表中，一般情况下只对运行表进行各种操作，速度会有很大提高，如果需要查询历史数据时从历史表中查询即可，不会影响运行表而影响现场工作。

2.2 程序设计阶段的优化

我们在使用SQL语句时往往关注于结果的正确与否，而忽略了其速度和成本。在一些小的应用系统中，由于数据量小、数据访问不是很频繁、并发用户少等原因，SQL语句的优劣基本体现不出来，而对于数据量大、数据关系复杂的数据库环境，优劣立现，尤其是Where子句优化，它的优化在很大程度上就是尽量减少全表扫描。我在工作实践中总结出来，按照下面的一些规则对SQL进行适当的优化后，其运行速度有了明显地提高。

在select语句中一定要明确选取的字段，不要把没用的字段列入字段表，更不能用“select*from表名”这样的语句。

在公司产销系统中，有几个画面总提示数据字段总数超出界定范围，但实际并没有超出，经过分析SQL语句发现，这些画面所涉及的表其字段多达100多个，有的甚至上200个，而画面所取的字段不过一二十个，这时如果用select*from表名where＜条件＞这样不但浪费了大量的I/O资源、服务器资源、数据查询的速度也大打折扣，并且因字段太多而前台画面报错。

对于同一SQL语句，其字段列表、where子句条件尽量按规范和顺序填写。字段列要写在运算符的左边，值写运算符右边，如：where年龄＞22和where 22＜年龄两个语句含义和结果一样，执行效率却不一样，如果年龄字段有索引的话，后者因不符合规范，不但增加了语句分析的次数，还放弃索引进行全表扫描，降低了执行效率。另外，同样的语句按规范和顺序书写，第一次用这个语句时可能耗时长，但再次调用该语句时速度大大加快，否则增加了数据库系统分析语句所需的时间和服务器资源占用。例如：在一个有900多万条的一个Oracle数据表里运行“select字段列表from表名where条件1 and条件2”查询语句，第一次运行耗时10.687 s，第二次运行时仅用2.218 s。

通过case、decode等一些函数减少对数据库访问的次数。例在雇员表employee中有雇员编号bh,出生日期birth两个字段，要求统计各个年龄段的人数。

2.2.2 操作符的优化

减少IN、NOT IN操作符的使用，对于能转换成多表连接的要转换成多表连接。因为ORACLE遇到IN时会试图将IN操作符转换成多个表的连接，用IN操作符将增加转换时间。

避免对索引列NULL值进行判断，对NULL值的判断将产生全表扫描，不如在数据库设计时给字段设置默认值，没有默认值的尽量用其它更优的等价语句替代。

“不等于”操作符不用索引，它的处理将会产生全表扫描，对于已建立索引的列建议将“＜＞”或“！=”操作符改用其他操作符替代。

例如：字段名a为已建立索引列，建议将a＜＞0改为(a＜0 or a＞0)

避免在操作符的左边对索引列进行各种运算，那样系统将放弃索引而进行全表扫描。

尽量使用UNION ALL操作符，而不是UNION操作符。

2.2.3 其它

要尽量避免大的事务，尤其不能在事务中间与用户交互。大的事务占用资源多，耗时长，还常常是独占资源状态，这样影响其他事务的响应速度，如果事务中间有与用户交互信息，这时如果用户不操作，事务将一直占用资源到用户操作为止，容易发生死锁。

3 结束语

在项目设计和后续的维护过程中，通过对这些优化技术和方法的合理运用，提高了系统运行速度，减少了系统故障，用户反馈良好。尤其是公司销售系统，在初期运行阶段经常报数据溢出或查询吊死错误，通过对选取字段的控制和大数据量表的水平分割，问题得到解决，系统运行两年来没有再出现类似问题，查询的平均响应时间也缩短了30%左右。另外SQL的优化，各种数据库之间都是互通的，但也不仅相同，有些还与所采用的优化器的不同而需区别对待，在工作中还要根据实际情况来具体对待。

SQL Optimizing Technology and Application

Xu Xinjing

With informatization progress acceleration and management gradual specifying,SQL optimization directly affects the running state and speed of the system.Basing on the technology and experience of database design and program development,the author summarizes the method and techniques of database access SQL optimization and describes its application in projects by example.After optimization,the system ran with higher speed and less failure and

good response from the customer.

database,main key,external key,index,connection,optimization

（收稿 2010－12－10责编赵实鸣）

徐新静，女，1996年毕业于河北科技大学计算机及应用专业，高级工程师。现在天铁集团计控电讯厂计算机中心从事软件开发工作。