数据库索引的原理非常简单,但在复杂的表中真正能正确使用索引的人很少,即使是专业的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
压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。
分享到:
相关推荐
合理创建和使用索引 提高Oracle查询效率.pdf
在Oracle数据库中,创建索引虽然比较简单。但是要合理的创建索引则比较困难了。
针对大数据量的情况优化数据库表配置,合理设置索引,提升搜索效率
针对使用MySQL的索引,我们之前介绍过索引的最左前缀规则,索引覆盖,唯一索引和普通索引的使用以及优化器选择索引等概念,今天我们讨论下如何更合理的给字符串创建索引。 如何更好的创建字符串索引 我们知道,...
说起 MySQL 的查询优化,相信大家收藏了一堆奇技淫巧:不能使用 SELECT *、不使用 NULL 字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧?是否理解它背后的工作原理?在实际场景下...
*、不使用NULL字段、合理创建索引、为字段选择合适的数据类型.....你是否真的理解这些优化技巧?是否理解其背后的工作原理?在实际场景下性能真有提升吗?我想未必。因而理解这些优化建议背后的原理就尤为重要,希望...
它通过创建索引列和索引对象来实现,可以加快WHERE子句中涉及索引的查询速度。 在进行索引优化时,有几个关键点需要注意。首先是选择合适的索引列,通常选择经常用于查询的列作为索引列,尽量避免使用长文本或二...
如何选择索引,如何创建高效实用的索引以及如何利用索引优化SQL等等。内容尽在其中, 希望爱学习的小伙伴,一起奋发进步,希望开发小伙伴能够更深层次的理解和了解索引, 合理利用索引来高效服务于我们系统。
创建索引时,你需要确保该索引是应用在SQL查询语的条件(一般作为WHERE 子句的条件)实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。上面都在说使用索引的好处,但过多的使用索引将会造成...
创建索引:在经常被查询的列上创建索引可以加快查询速度。但过多或不必要的索引会增加写操作的开销,因此需要权衡利弊。 避免使用SELECT *:只选择需要的字段而不是全部字段,可以减少数据传输量,提高查询速度。 ...
索引的优点和缺点 为什么要创建索引呢?这是因为,创建索引可以大大提高系统的性能。第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。第二,可以大大加快数据的检索速度,这也是创建索引的最主要...
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。 上面都在说使用索引的好处,但过多的使用索引将...
关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能...
为什么要创建索引呢?这是由于,创建索引能够大大提高系统的性能。 第一,通过创建性索引,能够保证数据库表中每一行数据的性。 第二,能够大大加快 数据的检索速度,这也是创建索引的基本的原因。 第三,...
应该将索引设置在经常用于查询条件的字段上,尽量避免在大字段或者不稳定字段上创建索引。另外,索引的复合列也可以提高查询效率,但要注意避免创建过多复合索引,以免影响更新和插入操作的性能。 此外,定期对索引...
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度...创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上,索引也是一张表,该表保存了主键