Where语句设置不当导致索引失效

作者: Victor, 　出处:IT专家网,　责任编辑: 包春林,　

2009-04-23 11:08

　　虽然说索引在使用上可能有种种限制，但是还是在数据库设计中被充分利用。因为在大部分情况下索引还是被用来提高数据库性能的一个工具。不过有些数据库工程师往往会犯一些低级的错误，导致索引失效。如在Where条件子句中设置了不合适的条件，从而在查询等操作时导致原先在表中设置的索引不起作用。笔者以前也多次犯过类似的错误。笔者今天在这里就抛砖引玉，把这些常见的问题总结一下。希望后来的人能够尽量少犯这些错误。

　　错误一：在Where子句中使用函数。

　　如现在在销售订单表中，有一个订单日期字段，其存储的数据为年月日。假设现在用户需要统计数据，需要统计2009年第一季度每隔月的各个业务员的接单情况。由于在销售订单中没有存储年与月份的数据，而只有订单日期数据，那么就需要利用Extract函数从订单日期字段中获取年份与月份字段，然后再查询处各个业务员在2009年第一季度每个月的销售订单明细。下面的Select语句就是查询2009年1月份各个业务员的接单情况。

　　Select 业务员，订单日期，销售订单号码，客户名称，订单金额

　　Where Extract(yyyy,订单日期)=2009 and Extract(mouth,订单日期)=1

　　但是此时就需要在Where条件语句中采用Extract函数。这是Oracle数据库系统提供的从日期型字段中抽取年或者月份的函数。如果原先在这个日期字段上建立了索引(不是函数索引)，那么此时会对数据库的查询产生什么影响呢?

　　通常情况下，如果不使用基于函数的索引，那么当SQL语句在的Where子句中队存在索引的列使用函数时，这会让数据库的优化器忽略掉这些索引。也就是说，这种情况下即使只存在着少量的复合条件的信息，数据库仍然会对这张表进行全表扫描，以获取相关的数据。这主要是因为这些索引实际上已经改变了被索引列的值。如一些常见的函数，如SUBSTR、Extract等函数，都会改变索引列的值。此时数据库系统也就无法使用已被函数引用(此时列的值已经发生改变)的索引和列。也即是说，如果在Where子句的条件语句中，采用了函数的话，则即使列采用了索引(不是函数索引)，就会让设置在这个列上索引失效。此时数据库就会对这个表进行全表扫描。这个结果可能是一些数据库管理员始料未及的。

　　那么该如何避免这种情况呢?最简单的方法，就是数据库管理员在数据库设计的时候就预计到在以后操作中，可能要在Where子句中要使用函数，此时就可以把这个列上的索引设置为函数索引。通常情况下，只要建立了函数索引，则即使在Where语句中采用了函数，这个列上的索引仍然有效。在查询中就可以避免全表扫描。因为函数索引实际上存储了预先计算过的值。也就是说，在索引表中，其实已经存储了年度与月份的值。而不是存储具体的订单日期。那么此时在查询时，数据库就会直接对应索引表中的年度与月份的值。为此索引就不会因为采用了函数而失效。

　　错误二：不匹配的数据类型。

　　在数据库中，有些数据类型虽然不同，但是数据库会自动进行转换。如现在在一张用户信息表中，可能有公民的身份证号码字段，这个字段的类型为字符型。通常情况下，为这个字符类型的字段赋值时需要加入单引号。但是如果把一个纯数字的字符串赋值给一个字符型的字段时，可以不用加单引号。因为此时数据库系统会自动把这串数字转换为字符型数据。现在数据库在这表中已经给这个身份证号码字段设置了索引。如果现在用户在对这个表进行查询时，所采用的Where条件语句为 Where 身份证号码=123456789900。此时数据库会如何查询呢?

　　笔者要非常悲痛的告诉大家，此时数据库会忽略掉设置在身份证号码字段上的索引，而采用全表扫描。类似的比较不匹配的数据类型，会导致设置在表中字段上的索引失效，这是很多数据库管理员经常容易犯的错误。Oracle数据库系统在数据类型字段上的兼容性，虽然提高了用户操作数据的便利性，但是毋庸置疑的也给用户留下不少的麻烦。就拿上面这个例子来说，数据库优化器会对以上这个条件语句进行一些转换，如可能会换成：

　　To_number(身份证号码) =123456789900

　　也就是说，会在身份证号码字段前面隐性的加入一个函数，把身份证号码转换为数字型。然后再与后面提供的身份证号码进行比对。此时就相当于对索引列采用了函数，跟上面提到的第一个错误类似。当Where条件语句中采用了函数，则即使这个列中设置了索引(不是函数索引)，则数据库优化器也会忽略掉这个索引。此时即使一个身份证号码在数据库中只有一条记录，数据库仍然需要进行全表扫描。

　　由于类似的错误很隐蔽，故一些经验不深的数据库管理员与程序开发人员经常会犯这个错误。那么该如何避免这种情况呢?其实只要了解有这种风险的存在，那么在处理起来也是比较简单的。如只需要在查询的时候把Where语句写成Where 身份证号码=’123456789900’即可，即加入单引号，表示输入的条件是一个字符数据类型即可。此时两者的数据类型一致，数据库就不会利用数据类型转换函数了。不过有时候终端用户并不会这么配合，每次输入身份证号码查询的时候，还利用单引号。此时程序开发人员应该把这个单引号在程序设计中实现。即终端用户只需要输入18位的身份证号码即可，不需要输入单引号。而应用程序在把这个身份证号码传递给数据库系统的时候，应用程序会先给其加上单引号，然后再传递给数据库系统进行查询。为此这个单引号对用户来说就是透明的。

　　另外虽然可以修改数据库中的身份证字段的数据类型，把其设置为数字型即可。但是通常情况下不建议这么做。因为有些老的身份证号码中含有字符，针对这些身份证号码就不好存储。而且有时候在身份证查询中也只需要进行模糊查询，如只知道出身地与出生年月日，来查询身份证号码。如果是数据类型的字段的话，则在实现模糊查询的时候会遇到问题。所以遇到这种情况，最好的处理方式就是应用程序在传递传输的时候，强制加入单引号。从而防止因为比较不匹配的数据类型而导致的全表扫描。

　　错误三：在Where子句中使用IS NULL或者IS NOT NULL。

　　在数据库设计的时候，允许某些字段为非空。而即使某个字段允许为非空，数据库仍然允许在这个字段上建立索引。但是这种情况下，使用索引就是一个很危险的事情。因为一不小心，就可能使得这个索引失效，在查询时需要用到全表扫描。如在以上这个表中，用户需要查询身份证号码为空的纪录，以方便用户补全身份证号码。此时用户就需要用到以下这个条件语句：WHERE 身份证号码 IS NULL。通过这个语句可以查询出所有身份证号码为空的纪录。但是，在Where子句中如果使用IS NULL或者IS NOT NULL等条件语句的话，会让在这个列上的索引失效。为此如果在几百万的信息中，如果只有两条记录没有身份证号码，则此事数据库仍然需要进行全表扫描，以查找相关的信息。这主要是因为普通情况下，如果一个字段为空，而且又在这个字段上设置了索引的话，则这个索引的值不会保存在索引表中。因为根本无法保存。为什么呢?因为空值(NULL)在数据库中是一个很特殊的值。其NULL不等于‘’，甚至不等于NULL。

　　所以在允许NULL字段上建立索引要特别注意这个情况。为了避免这种情况笔者有几个建议。如允许身份证这个字段为NULL，那么最好在这个字段上建立位图索引。因为创建位图索引时，数据库系统会对整个表进行索引，并为索引列的每个取值建立一个位图，包括NULL字段。所以说位图索引通常对于NULL字段的搜索有独到之处。但是位图索引通常情况下是用在基数比较小的情况，即重复数值比较多时。而对于身份证号码的话，基本上都是唯一的，也就是说基数很大，此时并不适合采用位图索引。既然不能够采用位图索引，那么就最好能够给这个字段设置默认值。如可以把这个字段默认设置为0。当没有输入身份证号而保存这个资料的时候，则数据库中以字符0表示。如此在以后想查询身份证号码为空的纪录时，只需要输入0，而不需要用IS NULL，这就可以避免全表扫描了。当然如果对身份证字段能够实现非空限制那时最好的了。

评论

1 楼 yuxiangtong 2010-08-17  

不错，学习了