数据库索引的原理非常简单,但在复杂的表中真正能正确使用索引的人很少,即使是专业的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 压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。