`
student_lp
  • 浏览: 437987 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论
阅读更多

    高效的选择和使用索引有很多方式,其中有些是针对特殊案例的优化方法,有些则是针对特定行为的优化。使用哪个索引,以及如何评估选择不同索引的性能影响的技巧。

一、独立的列

    通常会看到一些查询不当地使用索引,或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的,则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分,也不能是函数的参数。例如:

    select ... where to_days(current_date) - to_days(date_col) <=10;

二、前缀索引和索引选择

    有时候需要索引很长的字符列,这会让索引变得很大且慢。一个策略是前面提到过的模拟哈希索引,但是这还是不够的。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指,不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

    一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能。对于blob、text或者很长的varchar类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长。前缀应该足够长,以使得前缀索引的选择性接近于索引整个列。换句话说,前缀的“基数”应该接近于完整列的“基数”。通过计算,选择适合的前缀长度。

注意:前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有缺点,MySQL无法使用前缀索引做order by和Group by,也无法使用前缀索引做覆盖扫描。

三、多列索引

    对多列索引,常有一个错误的理解,为每个列创建独立的索引,或者按照错误的顺序创建多列索引。先来看为每个列创建独立的索引,这种索引策略,一般是由于人们听到一些专家诸如“把where条件里面的列都建上索引”这种模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引,其性能比起真正优秀的索引可能差几个数量级。有时如果无法设计一个“三星”索引,那么就不如忽略掉where子句,集中精力优化索引列的顺序,或者创建一个全覆盖索引。

     在多个列上建立的单列索引大部分情况下并不能提高MySQL的查询性能。mysql5.0和更新版本引入了一种叫“索引合并”的策略,一定程度上可以使用表上的多个单列索引来定位制定的行。有些情况下,查询能够同时使用单列索引进行扫描,并将结果进行合并,这种算法有三个变种:OR条件的联合,AND条件的相交,组合前两种情况的联合及相交。

    索引合并策略有时候是一种优化的结果,但实际上更多时候说明了表上的索引建的很糟糕, 应该通过explain好好检查一下查询和表的结构:

  • 当出现服务器对多个索引做相交操作时(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。
  • 当服务器需要对多个索引做联合操作时(通常有多个OR条件),通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回的大量数据的时候。
  • 更重要的是,优化器不会把这些计算到“查询成本”中,优化器只关心随机页面的读取。这会使得查询的成本被“低估”,导致该执行计划还不如直接走全表扫描。

四、选择合适的索引顺序

    通常最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要。 在一个多列B-Tree索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,等等。索引,索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的oder by、group by和distinct等字句的查询需求。所以多列索引的顺序至关重要。

    如何选择索引的列顺序有一个经验法则:如果不需要考虑排序和分组时,将选择性最高的列放在前面通常很好,这时索引的作用只是用于优化where条件的查询,选择性比较好。但是,性能不只是依赖于所有索引列的选择性,也和查询条件的具体值有关,也就是和值得分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要你根据那些运行频率最高的查询来调整索引列的顺序,让这种情况下索引的选择性能最高。

五、注意采用聚簇索引和覆盖索引

详解聚簇索引》和《详解覆盖索引

六、使用索引扫描来做排序

    mysql有两种方式可以生成有序的结果:通过排序操作,或者按索引顺序扫描。如果explain出来的type列的值为“index",则说明mysql使用了索引扫描来做排序(不要和extra列的using index搞混了)。扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不会覆盖查询所需的全部列,那就不得不扫描一条索引记录就都会表查询一次对应的行。这基本上都是随机I/O,因为索引顺序读取数据的速度通常要比顺序的全表扫描慢,尤其是在I/O密集型的工作负载时。

    mysql可以使用同一个索引既满足排序,又用于查找行。因此,如果可能,设计索引时应该尽可能的同时满足这两种任务,这样是最好的。只有当索引顺序和order by字句的顺序完全一致,并且所有列的排序方向都一样时,mysql才能够使用索引来对结果做排序如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一个表时,才能使用索引排序。order by子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求,否则,mysql都需要执行排序操作,而无法利用索引排序。

注意:有一种情况下order by子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。如果where子句或者join子句对这些列指定了常量,就可以”弥补“索引的不足。

七、压缩(前缀压缩)索引

    MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中,这在某些情况下能极大的提高性能。默认只压缩字符串,但通过参数设置也可以对整数做压缩。

    MyISAM压缩每个索引块的方法是,先完全保存索引块中的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同前缀部分,把这部分存储起来即可。

    压缩块使用了更少的空间,代价是某些操作可以更慢。因为每个值得压缩前缀都依赖前面的值,索引MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错,但是如果是倒序扫描--例如order by desc--就不是很好了。

    测试表明,对于cpu密集型应用,因为扫描需要随机查找,压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在cpu内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间,如果是I/O密集型应用,对某些查询带来的好处会比成本多很多。

八、冗余和重复索引

    mysql允许在相同列上创建多个索引,无论是有意还是无意的。mysql需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个的进行考虑,这回影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引,发现以后也应该立即移除。

   冗余索引和重复索引有一些不同,如果创建了索引(a,b),在创建索引(a)就是冗余索引,因为这是前一个索引的前缀索引。因为索引(a,b)也可以当做索引(a)来使用。但是如果在创建索引(b,a),则不是冗余索引,索引(b)也不是,因为b不是索引(a,b)的最左前缀列。另外,其他不同类型的索引也不会是B-Tree索引的冗余索引,而无论覆盖索引列是什么。

    冗余索引通常发生在添加新索引的时候。大多数情况下都不需要冗余索引,应该尽可能扩展已有的索引而不是新创建索引。但也有时候处于性能方面的考虑需要冗余索引,因为扩展已有的索引会导致其变得太大,从而影响其他使用该索引的查询性能。

九、索引和锁

    索引可以让查询锁定更少的行。如果你得查询从不访问那些不需要的行,那么就会锁定更少的行,从两个方面来看这对性能都有好处。首先,虽然InnoDB的行锁效率很高,内存使用也很少,但是锁定行的时候仍然会带来额外开销;其次,锁定超过需要的行会增加锁争并减少并发性。

    InnoDB只有在访问行的时候才会对其加锁,而索引能减少InnoDB访问的行数,从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行,那么在innoDB检索到数据并返回给服务器层以后,mysql服务器才能应用where子句。这时已经无法避免锁定行了;InnoDB已经锁住了这些行,到适当的时候才释放。在mysql5.1和更新的版本中,InnoDB可以在服务器端过滤掉行后就释放锁,但是在早期的mysql版本中,InnoDB只有在事务提交后才能释放掉锁。

    关于InnoDB、索引和锁有一些很少有人知道的细节:InnoDB在二级索引上使用共享锁,但访问主键索引需要排他锁。这消除了使用覆盖索引的可能性,并且使得select for update 比lock in share mode或非锁定查询要慢很多。

     

分享到:
评论

相关推荐

    高性能网页索引器 论文

    ### 高性能网页索引器JU_Indexer的实现与优化 #### 一、系统架构与设计理念 **高性能网页索引器JU_Indexer**,作为吉林大学计算机科学与技术学院的一项研究成果,专注于解决网络搜索引擎核心组件——网页索引器的...

    Lucene创建与搜索索引

    ### Lucene创建与搜索索引 #### 一、Lucene简介 Lucene是Apache软件基金会下的一个开源全文搜索引擎工具包,由Java编写而成。它提供了一整套完整的文本索引和搜索机制,包括分词、索引、搜索等功能,并且性能高效...

    oracle 创建和删除索引

    ### Oracle 创建和删除索引详解 #### 一、Oracle索引概述 在Oracle数据库中,索引是一种重要的数据结构,用于提高数据检索速度。...在实际应用中,还需要根据具体的业务需求和数据特性来调整索引策略。

    MySql练习4:创建学生表和成绩表索引并查看索引.zip

    在MySQL数据库管理中,索引是一种非常重要的概念,它能够显著提升数据查询的效率。本练习主要涵盖了如何在学生...在实际应用中,根据业务需求和数据特性选择合适的索引策略,是数据库管理员和开发人员必备的技能之一。

    Oracle 创建索引的基本规则

    ### Oracle 创建索引的基本规则 在Oracle数据库管理中,创建合适的索引对于提高查询效率、减少数据...通过遵循上述基本原则,可以根据具体的应用场景灵活选择和调整索引策略,从而实现最佳的查询性能和资源利用率。

    创建和优化索引 优化方法

    【创建和优化索引】 创建索引是数据库性能优化的关键步骤,它可以帮助快速定位和检索数据,从而提高查询效率。...定期分析查询性能,监控数据库健康状态,并根据需要调整索引策略,是数据库管理员的重要职责。

    ORACLE索引与高性能SQL介绍

    Oracle数据库的索引是提升SQL查询...总之,理解并有效地运用Oracle的索引策略,可以显著提高SQL查询的执行效率,优化数据库性能,降低系统的资源消耗,这对于任何Oracle数据库管理员和开发人员来说都是至关重要的技能。

    ORACLE索引介绍与高性能SQL优化

    索引的设计和使用是高性能SQL优化的关键。如果一个表中没有合适的索引,数据库在执行查询操作时可能需要全表扫描,这会消耗大量的系统资源,尤其是磁盘I/O资源。而通过在适当的数据列上建立索引,可以将全表扫描转化...

    创建硬盘文件索引 NTFS MTF的极速方式

    总的来说,创建硬盘文件索引是一项涉及文件系统底层知识和高性能编程技巧的任务。通过掌握C#与NTFS交互的方法,结合适当的数据结构和算法,我们可以构建出一个高效且实用的全盘文件索引系统。这不仅有助于快速定位...

    ORACLE索引介绍与高性能SQL优化.pdf

    Oracle索引是数据库管理系统Oracle中的一种重要数据结构,它的主要作用是提高数据查询的效率,减少I/O操作...通过这种方式,Oracle用户可以根据业务需求和数据特性,灵活地设计和优化索引策略,以实现高性能的SQL查询。

    分区索引,本地索引,全局索引的区别

    本地索引是一种特殊的分区索引,它与表的分区紧密相关,即索引的分区策略完全遵循表的分区策略。 ##### 1. 特点: - **分区机制**:本地索引的分区键等同于表的分区键,分区数也等同于表的分区数。 - **前缀索引**...

    MySQL索引与优化:原理、策略及高级应用

    内容概要:本文详细介绍了MySQL中索引的基本概念、创建和管理索引的策略、查询优化技术以及性能调优实践。首先,解释了数据库和表的设计原则,包括规范化、数据类型选择和存储引擎选择。接着,介绍了索引的基本概念...

    性能加速器:MySQL中索引的创建与优化

    因此,在实际应用中,需要定期检查索引的有效性,并根据实际情况调整索引策略,包括优化现有索引或删除不必要的索引。 #### 六、使用`EXPLAIN`分析查询 在创建索引之前,使用 `EXPLAIN` 命令分析查询是一个很好的...

    MongoDB索引的创建docx.pdf

    MongoDB 是一个流行的开源文档数据库系统,以其灵活性和高性能而受到广泛的欢迎。索引是数据库管理系统中的关键元素,它们能够显著提升查询性能,特别是在大型数据集上。在 MongoDB 中,索引的创建对于优化查询效率...

    索引合并策略

    索引合并是优化数据检索性能的关键技术之一,尤其在处理大量数据时,如Lucene这样的全文检索库,索引合并策略显得尤为重要。本文将详细探讨索引合并的原理以及如何在Lucene中实现这一策略。 索引合并,顾名思义,是...

    Oracle创建索引要做到三个适当.doc

    在Oracle数据库管理中,合理创建索引是一项至关重要的任务,它直接影响着数据库的查询性能和整体效率。...数据库管理员应持续监控数据库的运行状况,定期审查索引策略,以确保索引始终能够有效地支持数据库的高效运行。

    mysql性能优化之索引优化

    MySQL性能优化中的索引优化是提升数据库查询效率的关键技术。索引是一种特殊的数据...在实践中,开发者需要根据具体的应用场景和数据特性,结合监控和分析工具,持续调整和优化索引策略,以实现数据库的最佳运行状态。

    高性能MySQL_ch03_架构优化和索引.pdf

    根据提供的标题“高性能MySQL_ch03_架构优化和索引.pdf”以及描述“高性能MySQL_ch03_架构优化和索引.pdf”,我们可以推断出这份文档主要关注的是MySQL数据库的性能优化,特别是针对架构和索引这两方面进行深入探讨...

Global site tag (gtag.js) - Google Analytics