大表查询是不是索引越多越好?（表的索引怎么看）

在我负责项目期间，我们设有巡检团队，定期对系统进行"望闻问切"式的检查。他们的职责是确保系统运行良好，查看日志中的异常信息，并提出优化查询的建议。原本，这样的巡检能够弥补系统的不足，提升其性能。然而，随着巡检次数的增多和人员水平的起伏，巡检逐渐沦为形式主义。巡检团队的建议也变得千篇一律，如SQL查询慢了，就建议对A字段加索引。

起初，我们也按照这些建议进行修改。但有一天，一个批量处理业务竟然耗时近两小时才完成。我们开始深入分析，最终发现是因为索引过多导致数据插入效率低下。这次事件让我深思：数据库索引应该如何使用才能恰到好处？以下是我对数据库索引的一些理解和知识。

1.数据库索引特点

数据库中常见的索引类型包括B树索引、哈希索引、位图索引等。不同的数据库系统支持的索引类型也不同，需要根据具体的数据库系统来确定使用何种类型的索引。

Oracle数据库使用B-trees存储索引，来加速数据访问。若没有索引，你必须顺序扫描数据来找到所需的记录，这可能需要很长时间。B-trees是一种自平衡的树形数据结构，它可以在O(log n)的时间内查找、插入和删除记录。

B树是一种多路搜索树，它的每个节点可以有多个子节点，而且子节点的键值从小到大排序。B树的特点有：

• B树的查询次数与磁盘I/O次数相同，因此B树适合用于外存存储。
• B树的非叶子节点只存储索引，不存储数据，因此B树的查询速度非常快。
• B树的叶子节点使用链表相连，因此B树支持范围查询。

B+树是B树的一种变体，它在B树的基础上进行了改进，使得每个节点可以存储更多的关键字和数据，从而提高了查询效率。B+树的特点有：

• B+树的内部节点不存储数据，只存储索引信息，叶子节点存储数据，且叶子节点使用链表相连。这样的结构使得B+树更适合于磁盘存储，因为磁盘IO是相对较慢的，使用B+树可以减少磁盘IO次数，提高查询效率。
• B+树支持范围查询和排序操作。
• B+树的结构对并发访问有很好的支持。

2.索引建立该如何选择

选择给表建立索引的方法因数据库而异，但是以下是一些常见的原则：

• 选择频繁查询的字段：一张表的字段总会有冷热之分，很明显那些频繁使用的字段更适合为它创建索引。
• 在where和on条件出现的字段优先创建索引：在where和on条件中出现的字段是最常用的查询条件，因此这些字段应该优先创建索引。
• 选择唯一性索引：唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快地确定某个学生的信息。
• 避免过多的索引：过多的索引会导致查询性能下降，因为每次查询时都需要扫描所有的索引。
• 索引在适合较多数据时不如全表扫描高效，比如：

表中记录为10万条

一个BLOCK中在100条数据，则表大小为1000个BLOCK，多块读一次读16个BLOCK，则需要60多个IO。

如果满足索引的记录数为1000条，占记录数的1%，如果要通过rowid回到表中取其

他字段的值，则发生1000次IO，由于IO是瓶颈，显然不如全表扫描高效！

但是基于规则的优化器RBO(rule based optimizer)，当表中存在索引时，将不管不顾，就用索引，哪怕全表扫描更合适！这就是为什么数据库抛弃RBO，目前只支持CBO(COST based optimizer)的原因，CBO会计算谁更划算！更合理和高效！

• 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
• 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
• 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

3.不走索引的情况

在SQL中有很多陷阱会使一些索引无法使用，下面讨论一些常见的问题:

（1）使用不等于操作符（<>、!=)

下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。

select cust_Id,cust_name from customers where cust_rating <>'aa';

把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

select cust_Id,cust_name from customers where cust _rating <'aa' or cust_rating > 'aa';

特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。

（2） 使用IS NULL 或IS NOT NULL

使用IS NULL或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。

（3）使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，

使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引)

select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81',

把上面的语句改成下面的语句，这样就可以通过索引进行直找。

select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);

（4）比较不匹配的数据类型（隐式转换)

也是比较难于发现的性能问题之一。 注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。

下面的语句将执行全表扫描:

select bank_name,address,city,state,zip from banks where account_number = 990354;

Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用，改成下面的查询就可以使用索引:

select bank_name,address,city,state,zip from banks where account_number ='990354';

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描"

4.索引过多的弊端

当然，正如文章开头所提到的，一个表的索引过多会产生很多弊端。

• 建立索引的字段越多，数据量大的时候，文件就会越大，查找数据就会变慢。
• 一个索引会在 update 或 insert 时增加一次 I/O，对于操作系统底层来说是非常损耗性能的。
• 索引过多一定情况下会导致索引文件过大（指数增长），系统在寻址时查询时间增长。
• 索引过多会增加维护成本和复杂性，因为需要维护和管理更多的索引。
• 索引过多可能会导致查询优化器无法选择最优的查询计划，从而影响查询效率。

总的来说，我们需要根据实际的业务需求和数据量，以及数据库的性能特性，来合理设计和调整索引。