合理创建索引

数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人很少，即使是专业的DBA 也不一定能完全做到最优。

索引会大大增加表记录的DML(INSERT,UPDATE,DELETE) 开销，正确的索引可以让性能提升100 ，1000 倍以上，不合理的索引也可能会让性能下降100 倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。

索引有哪些种类？

常见的索引有B-TREE 索引、位图索引、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少，这里不深入介绍。B-TREE 索引包括很多扩展类型，如组合索引、反向索引、函数索引等等，以下是B-TREE 索引的简单介绍：

B-TREE 索引也称为平衡树索引(Balance Tree) ，它是一种按字段排好序的树形目录结构，主要用于提升查询性能和唯一约束支持。B-TREE 索引的内容包括根节点、分支节点、叶子节点。

叶子节点内容： 索引字段内容+ 表记录ROWID

根节点，分支节点内容： 当一个数据块中不能放下所有索引字段数据时，就会形成树形的根节点或分支节点，根节点与分支节点保存了索引树的顺序及各层级间的引用关系。

 

在什么字段上建索引？

建立必要的索引

    总纲只有一句话：建立必要的索引，这就是后面内容基础。这点看似容易实际却很难。难就难在如何判断哪些索引足必要的，哪些又是不必要的。判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。具体到方法上，就必须熟悉数据库应用程序巾的所有SQL 语句，从中统计出常用的能对性能有影响的部分SQL ，分析、归纳出作为Where 条件子句的字段及其组合方式：在这一基础上可以初步判断出哪些表的哪些宁段应该建立索引。其次，必须熟悉应用程序。必须了解哪些表足数据操作频繁的表：哪些表经常与其他表进行连接；哪些表的数据可能很大；对于数据是大的表，其巾各个字段的数据分布情况如何；等等。对于满足以上条件的这些表，必须重点关注，因为在这些表上的索引，将对SQL 语句的性能产生举足轻重的影响。建立索引常用的规则如下：

    1 、表的主键、外键必须有索引：

    2 、数据最超过300 的表应该有索引：

    3 、经常与其他表进行连接的表，在连接字段上应该建立索引；

    4 、经常出现在Where 子句中的字段，特别是大表的字段，应该建立索引；

    5 、索引应该建在选择性高的字段上；通过字段条件可筛选的记录集很小

    6 、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引：

    7 、复合索引的建立需要进行仔细分析：尽最考虑用单字段索引代替：

    8 、频繁进行数据操作的表，不要建立太多的索引

以下是一些字段是否需要建B-TREE 索引的经验分类：

1 、 需要建索引的字段

主键、外键和有对象或身份表示意义的字段，如 CODE,USERNAME

2 、索引慎用字段, 需要进行数据分布及使用场景详细评估

日期、年月、状态标志、类型、区域( 如COUNTRY,PROVINCE,CITY) 、操作人员( 如CREATOR,AUDITOR) 、数值( 如SCORE) 、长字符( 如ADDRESS)

3 、不适合建索引的字段

描述备注( 如: MEMO) 、大字段( 如：FILE_CONTENT)

索引对DML(INSERT,UPDATE,DELETE) 附加的开销

这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：

索引对于Insert 性能降低56%

索引对于Update 性能降低47%

索引对于Delete 性能降低29%

因此对于写IO 压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。